关系table_name的列table_name不存在

Question

我的项目使用Postgresql和Microsoft SQL Server。所以我不能在Postgresql中更改参数名。在这种情况下，我如何修复错误。

下面是我的函数。

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
    -- check exists
    if(exists(select 1 from ScheduleAlarmSetting A where A.UserNo = Schedule_UpdateAlarmSetting.UserNo)) then
    begin
        -- update
        update ScheduleAlarmSetting 
        set IsEmail = Schedule_UpdateAlarmSetting.IsEmail
        , ScheduleAlarmSetting.IsAlarmi = Schedule_UpdateAlarmSetting.IsAlarmi
        , ScheduleAlarmSetting.IsPC = Schedule_UpdateAlarmSetting.IsPC
        , ScheduleAlarmSetting.TimeAlarm = Schedule_UpdateAlarmSetting.TimeAlarm 
        , ScheduleAlarmSetting.IsMobile = Schedule_UpdateAlarmSetting.IsMobile
        , ScheduleAlarmSetting.IsUnuse = Schedule_UpdateAlarmSetting.IsUnuse
        , ScheduleAlarmSetting.ModDate = now()
        , ScheduleAlarmSetting.ModUserNo = Schedule_UpdateAlarmSetting.UserNo
        where ScheduleAlarmSetting.UserNo = Schedule_UpdateAlarmSetting.UserNo;
    end;
    else
    begin
        -- insert
        insert into ScheduleAlarmSetting(UserNo,IsEmail,IsAlarmi,IsPC,IsMobile, TimeAlarm, IsUnuse,RegUserNo,RegDate)
        values (Schedule_UpdateAlarmSetting.UserNo
        , Schedule_UpdateAlarmSetting.IsEmail
        , Schedule_UpdateAlarmSetting.IsAlarmi
        , Schedule_UpdateAlarmSetting.IsPC
        , Schedule_UpdateAlarmSetting.IsMobile
        , Schedule_UpdateAlarmSetting.TimeAlarm
        , Schedule_UpdateAlarmSetting.IsUnuse
        , Schedule_UpdateAlarmSetting.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;


select * from schedule_updatealarmsetting(1,True, True, True, True,0,false);

错误:关系"schedulealarmsetting“的列"schedulealarmsetting”不存在

当我删除“schedulealarmsetting”。它将显示错误：“列引用"userno”不明确“

Answer 1

在PostgreSQL中，不能在update命令的set命令后使用表别名。而不是这个查询：

update ScheduleAlarmSetting 
set IsEmail = Schedule_UpdateAlarmSetting.IsEmail
, ScheduleAlarmSetting.IsAlarmi = Schedule_UpdateAlarmSetting.IsAlarmi
, ScheduleAlarmSetting.IsPC = Schedule_UpdateAlarmSetting.IsPC
, ScheduleAlarmSetting.TimeAlarm = Schedule_UpdateAlarmSetting.TimeAlarm 
, ScheduleAlarmSetting.IsMobile = Schedule_UpdateAlarmSetting.IsMobile
, ScheduleAlarmSetting.IsUnuse = Schedule_UpdateAlarmSetting.IsUnuse
, ScheduleAlarmSetting.ModDate = now()
, ScheduleAlarmSetting.ModUserNo = Schedule_UpdateAlarmSetting.UserNo
where ScheduleAlarmSetting.UserNo = Schedule_UpdateAlarmSetting.UserNo;

您可以使用以下查询：

update ScheduleAlarmSetting 
set IsEmail = Schedule_UpdateAlarmSetting.IsEmail
, IsAlarmi = Schedule_UpdateAlarmSetting.IsAlarmi
, IsPC = Schedule_UpdateAlarmSetting.IsPC
, TimeAlarm = Schedule_UpdateAlarmSetting.TimeAlarm 
, IsMobile = Schedule_UpdateAlarmSetting.IsMobile
, IsUnuse = Schedule_UpdateAlarmSetting.IsUnuse
, ModDate = now()
, ModUserNo = Schedule_UpdateAlarmSetting.UserNo
where ScheduleAlarmSetting.UserNo = Schedule_UpdateAlarmSetting.UserNo;

Microsoft SQL Server和大多数数据库中的UPDATE命令都是以这种格式写入的，不能在设置后使用别名。