如何在Supabase中通过PostgreSQL触发器与函数更新用户待办计数？

原代码的核心问题 #

• 无WHERE子句导致全表更新：原UPDATE语句未限定目标用户，会把public.user表中所有用户的created_todos字段统一设置为同一个统计值，完全不符合需求。
• 错误的关联字段：auth.users.id是全局用户ID，但触发器触发时应针对当前插入todo所属的用户，需使用NEW.created_by（触发器中NEW代表刚插入的todo记录）。
• 缺少RETURN语句：PostgreSQL触发器函数必须返回值（AFTER触发器返回NEW或OLD均可，但不能省略），否则会直接报错。
• 统计效率低下：每次插入都全表统计todo数量，性能浪费严重，应针对特定用户做定向统计。

修正后的代码 #

1. 修正触发器函数 #

CREATE OR REPLACE FUNCTION public.count_created_todos()
RETURNS TRIGGER AS $$
BEGIN
    -- 仅更新当前插入todo所属用户的统计值
    UPDATE public.user
    SET created_todos = (
        SELECT COUNT(*) FROM public.todo 
        WHERE public.todo.created_by = NEW.created_by
    )
    WHERE public.user.id = NEW.created_by; -- 精准限定要更新的用户
    
    RETURN NEW; -- 触发器函数必须返回值，AFTER触发器返回NEW不影响逻辑
END;
$$ language plpgsql security definer;

2. 重建触发器（扩展触发场景） #

DROP TRIGGER IF EXISTS update_count_created_todos ON public.todo;
CREATE TRIGGER update_count_created_todos 
AFTER INSERT OR DELETE OR UPDATE OF created_by ON public.todo
FOR EACH ROW EXECUTE PROCEDURE count_created_todos();

触发器与函数的协同机制 #

• 触发器是事件触发入口：当public.todo表发生INSERT/DELETE/UPDATE created_by操作时，触发器会自动调用绑定的函数。
• 触发器函数承载业务逻辑：函数可通过NEW（新插入/更新后的记录）、OLD（更新/删除前的记录）获取触发事件的关联数据，这里用NEW.created_by定位到当前todo所属用户，完成定向更新。
• Security Definer的作用：设置该属性后，函数会以创建者权限执行，确保能访问public.user和public.todo表（若普通用户无对应操作权限时）。

Supabase认证相关逻辑说明 #

• auth.users是系统用户表：Supabase认证系统会将所有注册用户存储在auth.users表中，你的public.user属于自定义用户信息表，通常通过id字段与auth.users.id关联（即public.user.id等于auth.users.id）。
• 数据关联规则：public.todo表的created_by字段应存储auth.users.id（也就是public.user.id），这样才能正确关联到对应用户，统计其创建的todo数量。
• 权限控制补充：若要限制普通用户仅能操作自己的统计数据，除依赖security definer外，还可给public.user表添加RLS（行级安全）策略，比如限定用户只能更新自身数据行。

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