如何在PostgreSQL UPDATE触发器函数中获取变更列集合？

针对未知表结构的场景，要在触发器函数中自动检测OLD与NEW行的差异列，最实用的方式是借助PostgreSQL的JSONB或hstore类型实现动态对比，无需提前知晓表列信息。

方法一：用JSONB返回差异列（推荐） #

将OLD和NEW行转换为JSONB格式，遍历键值对对比差异，最终返回包含变更列的JSONB对象，适配任意表结构：

CREATE OR REPLACE FUNCTION track_changes() RETURNS TRIGGER AS $body$
DECLARE
    old_json jsonb;
    new_json jsonb;
    diff_json jsonb := '{}'::jsonb;
    rec record;
BEGIN
    -- 将行数据转为JSONB
    old_json := to_jsonb(OLD);
    new_json := to_jsonb(NEW);

    -- 遍历所有列，对比新旧值
    FOR rec IN SELECT * FROM jsonb_each_text(new_json) LOOP
        IF old_json->>rec.key IS DISTINCT FROM rec.value THEN
            diff_json := diff_json || jsonb_build_object(rec.key, rec.value);
        END IF;
    END LOOP;

    -- 输出差异（可替换为写入日志表等业务逻辑）
    RAISE NOTICE '变更列: %', diff_json;
    
    -- AFTER触发器返回NULL不影响原操作
    RETURN NULL;
END;
$body$
LANGUAGE plpgsql;

创建触发器时只需指定目标表，无需修改函数：

-- 给students表创建触发器
CREATE TRIGGER update_trigger
AFTER UPDATE ON students
FOR EACH ROW EXECUTE PROCEDURE track_changes();

-- 给其他表复用函数
CREATE TRIGGER orders_update_trigger
AFTER UPDATE ON orders
FOR EACH ROW EXECUTE PROCEDURE track_changes();

方法二：用hstore返回差异列 #

hstore是轻量级的键值对类型，适合简单的差异对比，需先安装扩展：

-- 安装hstore扩展（仅需执行一次）
CREATE EXTENSION IF NOT EXISTS hstore;

CREATE OR REPLACE FUNCTION track_changes_hstore() RETURNS TRIGGER AS $body$
DECLARE
    old_hstore hstore;
    new_hstore hstore;
    diff_hstore hstore;
BEGIN
    old_hstore := hstore(OLD);
    new_hstore := hstore(NEW);
    -- 直接计算差异：new中与old不同的键值
    diff_hstore := new_hstore - old_hstore;
    
    RAISE NOTICE '变更列: %', diff_hstore;
    RETURN NULL;
END;
$body$
LANGUAGE plpgsql;

方案对比 #

• JSONB：支持所有PostgreSQL数据类型，输出格式标准，便于后续解析或存储，推荐用于多数场景。
• hstore：仅支持文本类型，体积更小，适合简单的键值对比场景。

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