如何优化PostgreSQL审计触发器代码并合并多字段更新记录？

1. 调整审计表结构 #

首先更新course_history表，用jsonb字段存储多字段变更信息，避免逐条记录单字段变更：

CREATE TABLE course_history (
    history_id SERIAL PRIMARY KEY,
    course_id INT NOT NULL REFERENCES course(id),
    changes JSONB NOT NULL,
    change_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

如果需要更明确的操作类型区分，可以额外添加operation_type VARCHAR(10)字段（值为INSERT/UPDATE）。

2. 编写通用触发器函数 #

利用PostgreSQL的hstore扩展自动对比新旧行差异，无需逐个字段写IF判断：

-- 先确保hstore扩展已安装
CREATE EXTENSION IF NOT EXISTS hstore;

CREATE OR REPLACE FUNCTION audit_course_changes()
RETURNS TRIGGER AS $$
DECLARE
    diff_hstore HSTORE;
    change_details JSONB;
BEGIN
    -- 仅处理UPDATE操作（INSERT可按需添加审计逻辑）
    IF TG_OP = 'UPDATE' THEN
        -- 计算新旧行的差异，排除主键（假设主键为id）
        diff_hstore := hstore(NEW) - hstore(OLD) - 'id'::TEXT;
        
        -- 仅当存在字段变更时插入审计记录
        IF diff_hstore <> ''::HSTORE THEN
            -- 将差异转换为包含新旧值的结构化JSON
            change_details := jsonb_object_agg(
                key,
                jsonb_build_object(
                    'old_value', (hstore(OLD) -> key)::TEXT,
                    'new_value', (hstore(NEW) -> key)::TEXT
                )
            ) FROM each(diff_hstore);
            
            INSERT INTO course_history (course_id, changes)
            VALUES (NEW.id, change_details);
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

函数说明： #

• hstore(NEW) - hstore(OLD)自动提取所有值发生变更的字段
• 减去主键id是因为主键通常不参与变更审计
• 通过jsonb_object_agg将每个变更字段的新旧值打包成清晰的JSON结构，便于后续查询分析

3. 创建触发器绑定到Course表 #

触发器需覆盖INSERT和UPDATE操作，适配ON CONFLICT触发的更新场景：

CREATE TRIGGER trigger_course_audit
AFTER INSERT OR UPDATE ON course
FOR EACH ROW EXECUTE FUNCTION audit_course_changes();

4. 测试验证 #

执行带ON CONFLICT的INSERT语句：

INSERT INTO course (id, name, credits)
VALUES (1, '数据库基础', 2)
ON CONFLICT (id) DO UPDATE
SET name = '高级数据库', credits = 3;

此时course_history会新增一条记录，changes字段内容类似：

{
  "name": {"old_value": "数据库基础", "new_value": "高级数据库"},
  "credits": {"old_value": "2", "new_value": "3"}
}

实现了单次更新多字段变更合并为单条审计记录的需求。

替代方案（无需hstore） #

如果不想使用hstore，可以用jsonb直接对比：

CREATE OR REPLACE FUNCTION audit_course_changes()
RETURNS TRIGGER AS $$
DECLARE
    change_details JSONB;
BEGIN
    IF TG_OP = 'UPDATE' THEN
        -- 生成新旧行的JSON差异，排除主键
        change_details := jsonb_strip_nulls(to_jsonb(NEW) - to_jsonb(OLD) - 'id'::TEXT);
        
        IF change_details <> '{}'::JSONB THEN
            -- 补充新旧值结构
            change_details := jsonb_object_agg(
                key,
                jsonb_build_object(
                    'old_value', to_jsonb(OLD) -> key,
                    'new_value', to_jsonb(NEW) -> key
                )
            ) FROM jsonb_each(change_details);
            
            INSERT INTO course_history (course_id, changes)
            VALUES (NEW.id, change_details);
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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