如何解决PostgreSQL/PL/pgSQL触发器审计日志的字段重复与差异存储问题？

我正在构建一个PL/pgSQL触发器函数，用于将操作事件记录到audit_log表，原代码存在两个核心问题：

1. 需要重复指定要存入审计日志的字段，后续维护成本高；
2. 更新操作时，即使仅个别字段发生变化，仍会存储所有指定字段的完整数据，造成冗余。

以下是针对性的解决方案：

问题1：避免重复指定审计字段 #

通过定义集中管理的字段列表常量，结合动态SQL统一提取OLD/NEW中的目标字段，只需在一处维护字段清单即可。

问题2：仅存储更新时变化的字段 #

遍历指定字段列表，逐个对比新旧值，仅收集发生变化的字段及其对应值，生成精简的差异数据，避免存储无变化的字段内容。

优化后完整代码 #

CREATE OR REPLACE FUNCTION audit_logging()
 RETURNS trigger
 LANGUAGE plpgsql
 AS $function$
DECLARE
  audit_fields text[] := ARRAY['field1', 'field2', 'field3']; -- 仅需在此处维护审计字段列表
  old_diff jsonb := '{}'::jsonb;
  new_diff jsonb := '{}'::jsonb;
  field text;
BEGIN
  -- 处理INSERT操作：记录指定字段的新值
  IF TG_OP = 'INSERT' THEN
    EXECUTE format(
      'SELECT row_to_json((SELECT x FROM (SELECT %s) x))',
      array_to_string(audit_fields, ', ')
    ) USING NEW INTO new_diff;
    
    INSERT INTO audit_log (new_val, type)
    VALUES (new_diff, TG_OP);
  -- 处理DELETE操作：记录指定字段的旧值
  ELSIF TG_OP = 'DELETE' THEN
    EXECUTE format(
      'SELECT row_to_json((SELECT x FROM (SELECT %s) x))',
      array_to_string(audit_fields, ', ')
    ) USING OLD INTO old_diff;
    
    INSERT INTO audit_log (old_val, type)
    VALUES (old_diff, TG_OP);
  -- 处理UPDATE操作：仅记录变化的字段
  ELSIF TG_OP = 'UPDATE' THEN
    -- 先判断整行是否有变化，避免无意义遍历
    IF (OLD).* IS DISTINCT FROM (NEW).* THEN
      FOREACH field IN ARRAY audit_fields LOOP
        IF (OLD).field IS DISTINCT FROM (NEW).field THEN
          old_diff := old_diff || jsonb_build_object(field, (OLD).field);
          new_diff := new_diff || jsonb_build_object(field, (NEW).field);
        END IF;
      END LOOP;
      
      -- 存在差异字段时才插入日志
      IF old_diff <> '{}'::jsonb THEN
        INSERT INTO audit_log (old_val, new_val, type)
        VALUES (old_diff, new_diff, TG_OP);
      END IF;
    END IF;
  END IF;
  
  RETURN NEW;
END;
$function$;

关键优化点说明 #

• 字段集中维护：audit_fields数组统一管理需要审计的字段，新增/删除字段仅需修改此数组，无需多处调整代码。
• 动态SQL提取数据：通过format和array_to_string拼接查询语句，结合USING NEW/OLD传入触发器上下文的记录，避免重复编写字段列表。
• 精准差异收集：使用IS DISTINCT FROM对比字段值（可正确识别NULL与非NULL的差异），仅将变化字段存入JSONB对象，大幅减少冗余数据。
• 性能优化：先判断整行是否有变化，避免无更新时的无效字段遍历。

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