PostgreSQL兼容SQL Server时函数报列不存在错误如何解决

• 第一个报错：PostgreSQL的UPDATE语法规定，SET子句后的列名禁止携带表名前缀，你写的ScheduleAlarmSetting.IsAlarmi会被PG识别为一个包含点号的完整列名，自然不存在。
• 第二个报错：你的函数参数名和ScheduleAlarmSetting表的列名完全重名，去掉表前缀后，PG无法区分标识符指代的是表列还是函数参数，所以报歧义错误。

你只需要调整UPDATE语句的写法，同时明确告知PG重名时的解析规则即可，修改后的完整函数代码如下：

CREATE OR REPLACE FUNCTION schedule_updatealarmsetting(
    userno integer,
    isemail boolean,
    isalarmi boolean,
    ispc boolean,
    ismobile boolean,
    timealarm integer,
    isunuse boolean)
  RETURNS void AS
$BODY$
-- 加这行配置：重名时优先使用变量/参数
#variable_conflict use_variable
BEGIN
    -- check exists
    if(exists(select 1 from ScheduleAlarmSetting A where A.UserNo = userno)) then
    begin
        -- update：SET后不要加表名前缀
        update ScheduleAlarmSetting 
        set IsEmail = IsEmail
        , IsAlarmi = IsAlarmi
        , IsPC = IsPC
        , TimeAlarm = TimeAlarm 
        , IsMobile = IsMobile
        , IsUnuse = IsUnuse
        , ModDate = now()
        , ModUserNo = userno
        where ScheduleAlarmSetting.UserNo = userno;
    end;
    else
    begin
        -- insert
        insert into ScheduleAlarmSetting(UserNo,IsEmail,IsAlarmi,IsPC,IsMobile, TimeAlarm, IsUnuse,RegUserNo,RegDate)
        values (userno
        , IsEmail
        , IsAlarmi
        , IsPC
        , IsMobile
        , TimeAlarm
        , IsUnuse
        , userno
        , now());
    end;
    end if;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION schedule_updatealarmsetting(integer, boolean, boolean, boolean, boolean, integer, boolean)
  OWNER TO postgres;

方案说明 #

• 函数开头的#variable_conflict use_variable是PL/pgSQL的特殊配置指令，专门解决参数/变量和表列重名的歧义问题，指定重名时优先使用参数/变量，不会影响SQL Server侧的逻辑。
• SET子句移除了所有表名前缀，符合PostgreSQL的UPDATE语法要求。
• 所有参数直接引用即可，不需要再加函数名前缀，代码更简洁。

可以用内置的UPSERT语法替换IF EXISTS判断，避免并发写入时的竞态问题，逻辑更精简：

CREATE OR REPLACE FUNCTION schedule_updatealarmsetting(
    userno integer,
    isemail boolean,
    isalarmi boolean,
    ispc boolean,
    ismobile boolean,
    timealarm integer,
    isunuse boolean)
  RETURNS void AS
$BODY$
BEGIN
    INSERT INTO ScheduleAlarmSetting(UserNo,IsEmail,IsAlarmi,IsPC,IsMobile, TimeAlarm, IsUnuse,RegUserNo,RegDate,ModDate,ModUserNo)
    VALUES (userno, isemail, isalarmi, ispc, ismobile, timealarm, isunuse, userno, now(), now(), userno)
    ON CONFLICT (UserNo) DO UPDATE
    SET IsEmail = EXCLUDED.IsEmail,
        IsAlarmi = EXCLUDED.IsAlarmi,
        IsPC = EXCLUDED.IsPC,
        TimeAlarm = EXCLUDED.TimeAlarm,
        IsMobile = EXCLUDED.IsMobile,
        IsUnuse = EXCLUDED.IsUnuse,
        ModDate = now(),
        ModUserNo = EXCLUDED.UserNo;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

这个方案不需要额外处理重名问题，EXCLUDED指代你原本要插入的数据，直接取对应字段赋值即可。

内容的提问来源于stack exchange，提问作者Hong Van Vit