PostgreSQL触发器函数：插入并返回ID关联用户表的语法咨询

我在Supabase后端有一个触发器函数，新用户注册时触发，原函数代码如下：

CREATE OR REPLACE FUNCTION public.handle_new_user()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
begin
  insert into public.user_profile (id, email, full_name)
  values (new.id, new.email, new.raw_user_meta_data->>'full_name');
  return new;
end;
$function$
;

现在需要先向spending_cap表插入数据，将生成的id存入user_profile表的spending_cap字段，预期逻辑如下：

id = insert into other_table (created_by, somecolumn)
  values (new.id, 100000);
  
  insert into public.user_profile (id, email, full_name, foreign_column)
  values (new.id, new.email, new.raw_user_meta_data->>'full_name', id);
  return new;

同时表结构存在循环外键约束：

create table
  public.user_profile (
    id uuid not null,
    full_name text null,
    email text not null,
    spending_cap integer null,
    constraint profiles_pkey primary key (id),
    constraint user_profile_id_fkey foreign key (id) references auth.users (id),
    constraint user_profile_spending_cap_fkey foreign key (spending_cap) references spending_cap (id)
  ) tablespace pg_default;
  
  
create table
  public.spending_cap (
    id integer generated by default as identity,
    created_by uuid null,
    cap bigint null,
    constraint lobby_pkey primary key (id),
    constraint spending_cap_created_by_fkey foreign key (created_by) references user_profile (id)
  ) tablespace pg_default;

1. PostgreSQL插入并返回ID的语法 #

PostgreSQL中，要获取插入操作生成的ID，使用INSERT ... RETURNING语法结合INTO关键字将返回值赋值给变量，格式如下：

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
RETURNING id INTO variable_name;

2. 循环外键约束的处理逻辑 #

由于user_profile和spending_cap存在循环外键依赖，直接按顺序插入会触发约束错误，需分三步操作：

• 先插入user_profile，暂时将spending_cap设为NULL（表结构已允许该字段为NULL）
• 插入spending_cap，此时created_by可使用新用户的id（即new.id），并返回生成的id
• 更新user_profile的spending_cap字段为刚生成的spending_cap.id

3. 修改后的完整触发器函数 #

CREATE OR REPLACE FUNCTION public.handle_new_user()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
DECLARE
  new_spending_cap_id integer; -- 定义变量存储spending_cap的ID
begin
  -- 1. 先插入user_profile，spending_cap暂时为NULL
  INSERT INTO public.user_profile (id, email, full_name)
  VALUES (new.id, new.email, new.raw_user_meta_data->>'full_name');

  -- 2. 插入spending_cap，返回生成的ID到变量
  INSERT INTO public.spending_cap (created_by, cap)
  VALUES (new.id, 100000)
  RETURNING id INTO new_spending_cap_id;

  -- 3. 更新user_profile的spending_cap字段
  UPDATE public.user_profile
  SET spending_cap = new_spending_cap_id
  WHERE id = new.id;

  return new;
end;
$function$
;

关键说明 #

• 分步骤操作是规避循环外键约束的唯一可行方式，因为两个表互相依赖，无法单次插入满足双方外键要求
• SECURITY DEFINER确保触发器函数有足够权限执行插入和更新操作，需注意权限安全
• new.raw_user_meta_data->>'full_name'是从Supabase auth.users表的用户元数据中提取全名的正确方式

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