PostgreSQL函数中如何插入无返回行时带默认值的SELECT查询结果？

错误根源 #

• 初始创建public.profiles表的SQL未定义school字段，插入数据时指定不存在的列直接触发报错
• 子查询写法逻辑错误：当没有匹配的邮箱后缀时，子查询返回空行而非NULL值，coalesce函数无法生效，导致插入值缺失
• 表名未加public. schema前缀：security definer类型的函数默认搜索路径不包含public schema，找不到college_emails表
• 注释语法错误：PostgreSQL不支持//作为单行注释符，需使用--
• 权限缺失：触发器执行时没有读取public.college_emails表的权限

修复后完整代码 #

1. 补全profiles表结构 #

先执行以下SQL补全缺失的school字段：

ALTER TABLE public.profiles ADD COLUMN IF NOT EXISTS school text;

如果你是首次建表，用以下完整建表语句：

create table if not exists public.profiles (
    id uuid not null primary key, -- UUID from auth.users
    email text,
    full_name text,
    avatar_url text,
    school text, -- 新增的学校字段
    created_at timestamp with time zone
);

2. 修正后的触发器函数 #

create or replace function public.handle_new_user() 
returns trigger as $$
begin
  insert into public.profiles (id, email, full_name, school, created_at)
  values (
    new.id, 
    new.email, 
    SPLIT_PART(new.email, '@', 1), 
    -- 修正子查询写法，无匹配时返回Other
    coalesce(
      (SELECT college FROM public.college_emails WHERE tag = SPLIT_PART(new.email, '@', 2) LIMIT 1),
      'Other'
    ),
    current_timestamp
  );
  return new;
end;
$$ language plpgsql security definer;

3. 配置权限（必做） #

给触发器执行身份开放college_emails表的读取权限：

GRANT SELECT ON public.college_emails TO anon, authenticated, service_role;

4. 触发器创建语句（原有逻辑无需修改） #

create trigger on_new_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

验证效果 #

当用户使用myname@uci.edu注册时，只要public.college_emails表中存在tag = 'uci.edu'且college = 'University of California Irvine'的记录，public.profiles表会自动写入对应school值，无匹配时自动填充Other。

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