如何在PostgreSQL PL/pgSQL触发器中遍历OLD记录字段实现通用变更日志？

当然可以实现通用的字段变更记录触发器，无需硬编码特定表的字段。以下是两种实用方案，适用于任意表的变更日志记录：

方案1：使用hstore扩展（简洁高效） #

hstore可以将记录转为键值对格式，快速对比OLD和NEW的字段差异，是实现通用触发器的首选方式。

前置准备 #

1. 先修正changelog表的关键字冲突（user是PostgreSQL关键字，建议重命名）：

ALTER TABLE changelog RENAME COLUMN "user" TO changed_by;

2. 安装hstore扩展（若未安装）：

CREATE EXTENSION IF NOT EXISTS hstore;

通用触发器函数 #

CREATE OR REPLACE FUNCTION generic_data_history() RETURNS TRIGGER AS
$BODY$
DECLARE
    old_hstore hstore;
    new_hstore hstore;
    diff_hstore hstore;
    change_entries text[];
    change_record record;
BEGIN
    -- 将OLD、NEW记录转为hstore格式
    old_hstore := hstore(OLD);
    new_hstore := hstore(NEW);
    
    -- 提取字段差异（包含NULL值的变化）
    diff_hstore := (old_hstore - new_hstore) || (new_hstore - old_hstore);
    
    -- 无差异则直接返回
    IF diff_hstore = ''::hstore THEN
        RETURN NEW;
    END IF;
    
    -- 拼接变更日志条目
    change_entries := '{}'::text[];
    FOR change_record IN SELECT key, old_hstore->key AS old_val, new_hstore->key AS new_val FROM each(diff_hstore) LOOP
        change_entries := array_append(change_entries, format('%s: %s->%s', 
            change_record.key, 
            COALESCE(change_record.old_val, 'NULL'), 
            COALESCE(change_record.new_val, 'NULL')
        ));
    END LOOP;
    
    -- 插入变更日志
    INSERT INTO changelog(changes, changed_by, changed_on)
    VALUES(array_to_string(change_entries, ', '), current_user, NOW());
    
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

创建触发器 #

针对任意表创建触发器即可，比如你的data表：

CREATE OR REPLACE TRIGGER data_change_trigger 
AFTER UPDATE ON data 
FOR EACH ROW 
EXECUTE PROCEDURE generic_data_history();

方案2：通过系统表动态遍历字段（无扩展依赖） #

如果无法安装hstore扩展，可以通过查询information_schema.columns获取表的字段列表，动态遍历对比OLD和NEW的字段值。

通用触发器函数 #

CREATE OR REPLACE FUNCTION generic_data_history_sys() RETURNS TRIGGER AS
$BODY$
DECLARE
    col_name text;
    old_val text;
    new_val text;
    change_entries text[];
BEGIN
    change_entries := '{}'::text[];
    
    -- 遍历当前表的所有业务字段（排除系统内置字段）
    FOR col_name IN 
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_schema = TG_TABLE_SCHEMA 
          AND table_name = TG_TABLE_NAME 
          AND column_name NOT IN ('oid', 'ctid', 'xmin', 'xmax', 'cmin', 'cmax')
    LOOP
        -- 动态获取OLD、NEW的字段值
        EXECUTE format('SELECT $1.%I, $2.%I', col_name, col_name) INTO old_val, new_val USING OLD, NEW;
        
        -- 对比字段值（处理NULL值的差异）
        IF old_val IS DISTINCT FROM new_val THEN
            change_entries := array_append(change_entries, format('%s: %s->%s', 
                col_name, 
                COALESCE(old_val, 'NULL'), 
                COALESCE(new_val, 'NULL')
            ));
        END IF;
    END LOOP;
    
    -- 有变更则插入日志
    IF array_length(change_entries, 1) > 0 THEN
        INSERT INTO changelog(changes, changed_by, changed_on)
        VALUES(array_to_string(change_entries, ', '), current_user, NOW());
    END IF;
    
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

注意事项 #

• 关键字处理：使用%I格式符自动转义字段名，避免SQL关键字引发的语法错误
• NULL值处理：用IS DISTINCT FROM代替<>，确保NULL值的变化能被正确识别
• 字段过滤：可根据需求在查询系统表时排除不需要记录的字段（比如主键、更新时间戳等）
• 性能对比：hstore方案性能更优，无需查询系统表，适合高并发场景；系统表方案无需额外扩展，兼容性更强

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