如何通过PostgreSQL视图触发器函数返回新插入记录的生成ID

要让通过vw_movies_info视图插入数据时返回自动生成的id值，只需修改现有的iud_vw_movies_info_func触发器函数，在插入逻辑中通过RETURNING子句捕获生成的id并赋值给NEW对象即可，具体操作如下：

1. 更新触发器函数 #

修改原触发器函数的INSERT分支逻辑，用INSERT ... RETURNING语法把系统生成的id写入NEW.id：

CREATE OR REPLACE FUNCTION public.iud_vw_movies_info_func()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    -- 插入数据到movies表，同时返回生成的id赋值给NEW.id
    INSERT INTO public.movies (title, release_year, genre) -- 替换为你的实际非id字段
    VALUES (NEW.title, NEW.release_year, NEW.genre)
    RETURNING id INTO NEW.id;
    
    RETURN NEW;
  END IF;

  -- 保留原有UPDATE/DELETE逻辑（如果有）
  IF TG_OP = 'UPDATE' THEN
    UPDATE public.movies
    SET title = NEW.title, release_year = NEW.release_year, genre = NEW.genre
    WHERE id = OLD.id;
    RETURN NEW;
  END IF;

  IF TG_OP = 'DELETE' THEN
    DELETE FROM public.movies WHERE id = OLD.id;
    RETURN OLD;
  END IF;
END;
$$ LANGUAGE plpgsql;

2. 测试插入并返回id #

修改完成后，执行视图插入语句时加上RETURNING id，就能直接获取自动生成的id：

INSERT INTO vw_movies_info (title, release_year, genre)
VALUES ('星际穿越', 2014, '科幻')
RETURNING id;

关键注意事项 #

• 不要在INSERT语句中手动指定id值（IDENTITY字段默认由系统生成，手动指定需额外设置OVERRIDING SYSTEM VALUE）
• 确保RETURNING id INTO NEW.id中的id与movies表的IDENTITY字段名一致
• 无需修改已创建的INSTEAD OF触发器，仅更新触发器函数即可生效

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