PostgreSQL实现msdata表增改存储过程报子查询单列返回错误求解

错误原因 #

• 触发subquery must return only one column的核心问题：IF (select * from msdata where imei = imei1) 里的子查询返回了整行多列数据，而IF判断需要的是布尔值，不能直接用多列结果做判断。
• 逻辑错误1：UPDATE public.msdata SET assetname1 = assetname 字段赋值写反了，你要实现的是把传入的参数assetName1更新到表的assetname字段，不是反过来。
• 逻辑错误2：elseif (select * from msdata where imei != imei1) 条件完全错误，只要表中存在任意一个和当前imei1不相等的记录就会触发插入，完全不符合「不存在则插入」的需求。
• 多余无效语句：开头的PERFORM * from msdata没有任何作用，可以直接删除。

修正后的存储过程代码 #

CREATE OR REPLACE PROCEDURE checkUpdateAdd(imei1 INOUT text, assetName1 INOUT text)
LANGUAGE plpgsql
AS $BODY$
DECLARE
    v_exists BOOLEAN; -- 存储记录是否存在的判断结果
BEGIN
    -- 用EXISTS判断对应imei的记录是否存在，只会返回布尔值，不会有多列问题
    SELECT EXISTS(SELECT 1 FROM msdata WHERE imei = imei1) INTO v_exists;
    
    IF v_exists THEN
        -- 存在则更新assetname字段
        UPDATE public.msdata SET assetname = assetName1 WHERE imei = imei1;
    ELSE
        -- 不存在则插入
        INSERT INTO public.msdata(imei, assetname) VALUES (imei1, assetName1);
    END IF;
END;
$BODY$;

更简洁的实现方案（推荐） #

直接用PostgreSQL原生的INSERT ... ON CONFLICT UPSERT语法，不需要写存储过程就能实现需求，性能更高且逻辑更清晰，前提是你要给imei字段设置唯一约束：

1. 给imei加唯一约束（如果还未设置）

ALTER TABLE msdata ADD UNIQUE (imei);

2. 直接用UPSERT语句实现新增/更新

INSERT INTO msdata(imei, assetname) 
VALUES ('123','abc1')
ON CONFLICT (imei) 
DO UPDATE SET assetname = EXCLUDED.assetname;

调用测试 #

-- 调用修正后的存储过程
CALL checkUpdateAdd('123','abc1');
-- 查看执行结果
SELECT * FROM msdata;

内容的提问来源于stack exchange，提问作者codeLibrary