PL/pgSQL触发器如何转换NEW记录类型实现通用INSERT写历史表

问题背景 #

要给数据表实现同表多版本历史留存，核心字段规则：

id uuid
historycounter int8

版本规则：

• 新增数据时historycounter固定为0
• 更新数据时historycounter自增1
• 所有逻辑通过BEFORE INSERT OR UPDATE OR DELETE ON <table> FOR EACH ROW行级触发器实现
  现有更新分支逻辑中，版本号自增逻辑可正常运行，但UPDATE转INSERT写入新版本的逻辑无法生效，要求方案通用，不允许枚举每个表的字段编写语句。
  原有问题代码：

execute format('update %I.%I set historycounter = historycounter + 1 where id = $1', schema_name, tab_name) using old.id;
--  execute format('insert into %I.%I select $1', schema_name, tab_name) using new::text:xx;
execute format('insert into %I.%I values ($1)', schema_name, tab_name) using new.*::text;
return null;

已知触发器内NEW/OLD为record类型，需要找到将record转为对应表行类型完成通用插入的方法。

实现方案 #

原有逻辑的问题 #

两个核心错误导致插入失败、性能差：

1. 在BEFORE触发器内手动执行同表UPDATE语句，会重复触发触发器造成递归调用；BEFORE触发器可以直接修改NEW变量的字段值，返回NEW时数据库会自动执行原更新操作，返回NULL则直接跳过原操作，完全不需要手动写UPDATE。
2. 插入时将record转text、或者用new.*传参的写法不符合PL/pgSQL的动态SQL语法，不需要拆字段，只要把record显式转为目标表的复合类型，就能直接展开为所有字段的值完成插入。

通用触发器函数 #

以下函数不需要针对任何表做字段枚举，所有带id、historycounter字段的表都可以直接绑定使用：

CREATE OR REPLACE FUNCTION public.auto_history_version()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        -- 插入新版本固定版本号为0
        NEW.historycounter := 0;
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        -- 新版本号直接基于OLD自增，不需要手动UPDATE
        NEW.historycounter := OLD.historycounter + 1;
        -- 通用插入：将NEW显式转为当前表的复合类型，展开所有字段插入
        EXECUTE format(
            'INSERT INTO %I.%I SELECT ($1::%I.%I).*',
            TG_TABLE_SCHEMA, TG_TABLE_NAME,
            TG_TABLE_SCHEMA, TG_TABLE_NAME
        ) USING NEW;
        -- 返回NULL，取消原本的UPDATE操作，旧版本数据保留在表中
        RETURN NULL;
    ELSIF TG_OP = 'DELETE' THEN
        -- 不需要留存删除操作的话直接返回OLD即可，要加删除标记可以在这里扩展
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql STABLE;

触发器绑定语句（替换成你的表名即可）：

CREATE TRIGGER trg_your_table_history
BEFORE INSERT OR UPDATE OR DELETE ON your_schema.your_table
FOR EACH ROW EXECUTE FUNCTION public.auto_history_version();

必要配置 #

因为同表存储同一个业务ID的多个历史版本，必须把表的主键从原来的id修改为联合主键(id, historycounter)，否则插入新版本时会触发主键唯一约束冲突：

ALTER TABLE your_schema.your_table DROP CONSTRAINT your_table_pkey;
ALTER TABLE your_schema.your_table ADD PRIMARY KEY (id, historycounter);

内容的提问来源于stack exchange，提问作者Achim Weßling