PostgreSQL创建Users表触发器报missing FROM-clause错误如何解决

报错原因 #

• NEW/OLD是触发器内置的行记录变量，直接引用对应字段即可，不需要额外加表名前缀，你写的new."Users"."reference"语法不合法，导致数据库找不到Users表的对应引用，触发你看到的报错。
• INSERT类型的触发器只有NEW变量（代表新插入的行），不存在OLD变量（OLD仅在UPDATE、DELETE触发器中可用，代表修改/删除前的旧行），你代码中所有引用OLD的逻辑全部错误。
• 你使用的是AFTER INSERT触发器，触发时新记录已经被数据库自动插入到表中了，不需要你在函数里再写一遍INSERT语句，否则会导致重复插入死循环。
• 老用户积分更新的查询逻辑冗余错误，不需要嵌套子查询，直接匹配Username等于新记录的reference值即可。

修正后的代码 #

触发器函数 #

create or replace function add_reference_points()
returns trigger as 
$BODY$
BEGIN
    -- 仅当新记录的reference字段不为空，且存在对应用户时执行积分增加
    IF NEW."reference" IS NOT NULL AND EXISTS (SELECT 1 FROM "Users" WHERE "Username" = NEW."reference") THEN
        -- 给被推荐的老用户加50积分
        UPDATE "Users"
        SET "points" = "points" + 50
        WHERE "Username" = NEW."reference";
    END IF;
    RETURN NEW;
END;
$BODY$
language plpgsql;

触发器创建语句 #

create trigger reference_points_trigger
after insert 
on "Users"
for each row
execute function add_reference_points();

注：PostgreSQL 11以下版本需将语句中的execute function替换为execute procedure

内容的提问来源于stack exchange，提问作者Shakil Mahmud Shanto