PostgreSQL触发器函数语法错误排查：首个用户列更新场景

你的函数存在多处PL/pgSQL语法错误，以下是具体问题和修正后的代码：

错误点分析 #

• 条件查询语法错误：原代码中select count(*) from public.user = 1的写法不符合SQL规范，正确的比较方式是先查询计数再和数值对比；另外逻辑有误，要判断是否是首个用户，应该检查插入前表中用户数为0（而非1）。
• NEW行修改方式错误：在BEFORE INSERT触发器中，直接修改NEW记录的字段即可，不需要使用UPDATE语句。
• 数组语法错误：PostgreSQL中数组需用ARRAY[]语法或大括号{}包裹，而非中括号[]。
• 语句缺少分号：PL/pgSQL要求每个语句结尾必须加分号。
• 冗余的DECLARE块：DECLARE后未定义任何变量，可直接移除。

修正后的函数代码 #

CREATE OR REPLACE FUNCTION public.first_user()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    -- 判断当前是否是第一个用户（插入前表中用户数为0）
    IF (SELECT count(*) FROM public.user) = 0 THEN
        NEW.is_verified := true;
        NEW.roles := ARRAY['ROLE_USER', 'ROLE_ADMIN'];
    END IF;
    RETURN NEW;
END;
$function$
;

修正后的触发器代码 #

触发器本身语法问题不大，为避免名称混淆，建议将触发器名称与函数名称区分开：

CREATE TRIGGER trigger_first_user
BEFORE INSERT ON public.user
FOR EACH ROW EXECUTE FUNCTION public.first_user();

额外说明 #

如果你的roles字段是JSON类型而非数组类型，赋值语句需要改为JSON格式：

NEW.roles := '["ROLE_USER", "ROLE_ADMIN"]'::json;

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