如何修改PostgreSQL触发器函数仅记录变更列的JSON数据？

要实现只存储实际变更的列，不用全量记录新旧JSON，这里提供两种可行方案：

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

先启用PostgreSQL自带的hstore扩展，执行一次即可：

CREATE EXTENSION IF NOT EXISTS hstore;

然后修改你的触发器函数：

CREATE OR REPLACE FUNCTION change_trigger() RETURNS trigger AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO logging.t_history (tabname, schemaname, operation, new_val)
            VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        DECLARE
            changed_new jsonb;
            changed_old jsonb;
        BEGIN
            -- 提取新旧记录中有变化的列
            changed_new := (hstore(NEW) - hstore(OLD))::jsonb;
            changed_old := (hstore(OLD) - hstore(NEW))::jsonb;
            
            -- 仅当存在实际变更时插入记录，过滤无意义的空更新
            IF changed_new <> '{}'::jsonb THEN
                INSERT INTO logging.t_history (tabname, schemaname, operation, new_val, old_val)
                    VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, changed_new, changed_old);
            END IF;
            RETURN NEW;
        END;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO logging.t_history (tabname, schemaname, operation, old_val)
            VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD));
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

关键说明 #

• hstore(NEW) - hstore(OLD)会将新旧记录转为hstore格式，剔除值相同的列，剩下的就是新记录中变更的列，转成jsonb后就是变更后的字段值。
• hstore(OLD) - hstore(NEW)对应这些变更列的旧值，确保历史表只保留真正变化的字段，避免冗余。
• 空判断用来过滤无实际修改的UPDATE操作（比如UPDATE table SET col = col WHERE ...），可根据需求删除。

方案二：不依赖扩展，遍历列实现（通用兼容） #

如果不想启用hstore，也可以通过动态遍历表列对比值，适配所有PostgreSQL版本：

CREATE OR REPLACE FUNCTION change_trigger() RETURNS trigger AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO logging.t_history (tabname, schemaname, operation, new_val)
            VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        DECLARE
            changed_new jsonb := '{}'::jsonb;
            changed_old jsonb := '{}'::jsonb;
            col_name text;
            new_val anyelement;
            old_val anyelement;
        BEGIN
            -- 从系统表获取当前表的所有列名
            FOR col_name IN SELECT column_name FROM information_schema.columns 
                           WHERE table_schema = TG_TABLE_SCHEMA AND table_name = TG_RELNAME LOOP
                -- 用动态SQL获取当前列的新旧值
                EXECUTE format('SELECT $1.%I, $2.%I', col_name, col_name) INTO new_val, old_val USING NEW, OLD;
                
                -- 对比值，IS DISTINCT FROM可正确处理NULL差异
                IF new_val IS DISTINCT FROM old_val THEN
                    changed_new := changed_new || jsonb_build_object(col_name, new_val);
                    changed_old := changed_old || jsonb_build_object(col_name, old_val);
                END IF;
            END LOOP;
            
            IF changed_new <> '{}'::jsonb THEN
                INSERT INTO logging.t_history (tabname, schemaname, operation, new_val, old_val)
                    VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, changed_new, changed_old);
            END IF;
            RETURN NEW;
        END;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO logging.t_history (tabname, schemaname, operation, old_val)
            VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD));
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

关键说明 #

• 从information_schema.columns动态获取列名，无需硬编码，换表使用触发器无需修改代码。
• IS DISTINCT FROM比普通=更可靠，能正确识别NULL值的差异（比如旧值为NULL、新值非NULL的情况）。
• 用动态SQLEXECUTE获取列值，确保支持任意数据类型的列。

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