PostgreSQL：父表更新时为子表批量插入行分配相同自增ID

1. 核心问题修正 #

你当前的代码存在两个关键问题：

• 触发器为行级（FOR EACH ROW），每更新父表一行就触发一次插入，会导致子表ID逐行自增，不符合同一批更新用相同ID的需求；
• INSERT语句语法错误，SET是UPDATE的语法，插入数据应使用VALUES或SELECT。

2. 完整实现代码 #

假设子表child的ID字段为BIGSERIAL类型（对应默认序列child_id_seq），以下是满足需求的完整代码：

-- 行级触发器函数：生成批次ID并插入子表记录
CREATE OR REPLACE FUNCTION update_child() RETURNS trigger AS
$BODY$
DECLARE
  current_id bigint;
BEGIN
  -- 检查当前会话是否已存在本次更新的批次ID
  IF current_setting('myapp.batch_id', true) IS NULL THEN
    -- 从序列获取新的批次ID
    current_id := nextval('child_id_seq');
    -- 设置会话变量，仅当前语句/事务内有效
    PERFORM set_config('myapp.batch_id', current_id::text, true);
  ELSE
    -- 复用已生成的批次ID
    current_id := current_setting('myapp.batch_id')::bigint;
  END IF;

  -- 插入子表，使用统一批次ID
  INSERT INTO child (My_Date, ID)
  VALUES (NEW.My_Date, current_id);

  RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

-- 语句级触发器函数：更新结束后清除批次ID
CREATE OR REPLACE FUNCTION clear_batch_id() RETURNS trigger AS
$BODY$
BEGIN
  -- 清空会话变量，确保下一次更新生成新ID
  PERFORM set_config('myapp.batch_id', '', true);
  RETURN NULL;
END
$BODY$
LANGUAGE plpgsql;

创建触发器：

-- 行级触发器：父表每更新一行，向子表插入对应记录
CREATE TRIGGER update_child_after_update_row
AFTER UPDATE
ON parent
FOR EACH ROW
EXECUTE PROCEDURE update_child();

-- 语句级触发器：单次更新语句结束后，清除批次ID
CREATE TRIGGER clear_batch_id_after_update_stmt
AFTER UPDATE
ON parent
FOR EACH STATEMENT
EXECUTE PROCEDURE clear_batch_id();

3. 关键逻辑说明 #

• 当执行一次UPDATE语句更新多行时，首次触发update_child函数会生成唯一批次ID并存储在会话变量中，后续行触发时直接复用该ID，因此子表中这批插入的记录ID完全相同；
• 单次UPDATE语句执行完毕后，clear_batch_id函数会清空会话变量，下一次执行UPDATE时会生成新的批次ID；
• 如果子表ID使用SERIAL类型，只需将代码中的bigint改为int；若序列名称不是默认的child_id_seq，可使用pg_get_serial_sequence('child', 'ID')动态获取序列名，替换nextval的参数。

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