PostgreSQL触发器函数中如何检查ID存在性并实现分支逻辑？

1. 能否用SELECT ... INTO给变量赋值？ #

完全可以，这是PL/pgSQL中给变量赋值的常规操作。需要注意：如果查询可能返回多行，必须加上LIMIT 1避免抛出“返回多行”的错误，示例：

SELECT id INTO _organisation_id
FROM public.organisation
WHERE -- 你的匹配条件
LIMIT 1;

2. 无匹配id时变量值是什么？ #

当查询没有返回任何结果时，_organisation_id会被设置为NULL，不管变量有没有初始值。

3. 变量类型声明是否正确？ #

不正确。organisation.id是bigint类型，声明成INT会有类型不兼容风险——如果id值超过INT的最大值（2147483647），会触发转换错误。必须把变量声明为bigint。另外，PL/pgSQL中变量默认允许为NULL，不需要显式声明NULL属性，加了也属于多余操作。

4. 如何检查变量是否已赋值？ #

直接判断变量是否为NULL即可：

• 存在匹配id：_organisation_id IS NOT NULL
• 无匹配id：_organisation_id IS NULL

5. IF/ELSE分支怎么写？ #

用PL/pgSQL标准分支语法，结合NULL判断：

IF _organisation_id IS NOT NULL THEN
    -- 存在id时执行的操作，比如插入关联记录
    INSERT INTO public.user_organisation (user_id, organisation_id)
    VALUES (new.id, _organisation_id);
ELSE
    -- 不存在id时的操作，比如创建新组织
    INSERT INTO public.organisation (name)
    VALUES ('默认组织' || new.id)
    RETURNING id INTO _organisation_id;
    -- 再关联用户和新组织
    INSERT INTO public.user_organisation (user_id, organisation_id)
    VALUES (new.id, _organisation_id);
END IF;

完整的触发函数示例 #

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
DECLARE
    _organisation_id bigint; -- 匹配organisation.id的正确类型
BEGIN
    -- 查询符合条件的组织id
    SELECT id INTO _organisation_id
    FROM public.organisation
    WHERE -- 替换成你的匹配条件，比如邮箱域名匹配
      email_domain = SPLIT_PART(new.email, '@', 2)
    LIMIT 1;

    -- 分支处理
    IF _organisation_id IS NOT NULL THEN
        -- 存在组织：关联用户和组织
        INSERT INTO public.user_organisation (user_id, organisation_id)
        VALUES (new.id, _organisation_id);
    ELSE
        -- 不存在组织：创建新组织并关联
        INSERT INTO public.organisation (name, email_domain)
        VALUES ('默认组织_' || new.id, SPLIT_PART(new.email, '@', 2))
        RETURNING id INTO _organisation_id;
        
        INSERT INTO public.user_organisation (user_id, organisation_id)
        VALUES (new.id, _organisation_id);
    END IF;

    RETURN new;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 创建触发器
CREATE TRIGGER on_new_user
AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

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