Postgres触发器函数实现动态插入审计表并自定义tstzrange字段值

你可以通过动态获取当前触发触发器的业务表字段列表，过滤掉需要特殊处理的tstzrange类型字段后拼接插入语句，实现触发器的多表复用，完整代码如下：

CREATE OR REPLACE FUNCTION schema.audit_trigger_func()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
    _audit_tablename text; -- 传入的审计表名
    _normal_cols text[]; -- 除timestampzt_range外的普通字段列表
    _insert_col_str text; -- 最终INSERT语句的字段部分
    _insert_val_str text; -- 最终INSERT语句的值占位部分
    _custom_range tstzrange; -- 自定义处理后的范围值
BEGIN
    -- 从触发器参数中获取审计表名，实现多表复用
    _audit_tablename := TG_ARGV[0];
    
    -- 计算需要插入的自定义范围值
    _custom_range := tstzrange(LOWER(OLD.timestampzt_range), LOWER(NEW.timestampzt_range));
    
    -- 动态获取当前业务表的所有普通字段，排除timestampzt_range和系统字段、已删除字段
    SELECT array_agg(quote_ident(attname))
    INTO _normal_cols
    FROM pg_attribute
    WHERE attrelid = TG_RELID -- TG_RELID是当前触发触发器的业务表OID
      AND attnum > 0
      AND NOT attisdropped
      AND attname != 'timestampzt_range';
    
    -- 拼接INSERT的字段列表：普通字段 + 特殊处理的范围字段
    _insert_col_str := array_to_string(_normal_cols, ', ') || ', timestampzt_range';
    
    -- 拼接值占位部分：普通字段取OLD对应值，最后一位是自定义范围值
    _insert_val_str := array_to_string(
        ARRAY(SELECT '$1.' || unnest(_normal_cols)),
        ', '
    ) || ', $2';
    
    -- 执行动态插入
    EXECUTE format(
        'INSERT INTO audit.%I (%s) VALUES (%s)',
        _audit_tablename,
        _insert_col_str,
        _insert_val_str
    ) USING OLD, _custom_range;
    
    RETURN NEW;
END;
$BODY$;

使用方式 #

为不同业务表创建触发器时，通过参数传入对应的审计表名即可，无需修改触发器函数代码：

-- 给user表创建审计触发器，对应审计表user_audit
CREATE TRIGGER trg_user_audit
AFTER UPDATE ON public."user"
FOR EACH ROW
EXECUTE FUNCTION schema.audit_trigger_func('user_audit');

-- 给order表创建审计触发器，对应审计表order_audit
CREATE TRIGGER trg_order_audit
AFTER UPDATE ON public."order"
FOR EACH ROW
EXECUTE FUNCTION schema.audit_trigger_func('order_audit');

注意事项 #

• 需确保所有挂载该触发器的业务表都存在timestampzt_range字段
• 审计表结构需和对应业务表结构完全一致，符合你提到的全库审计表列结构统一的前提
• 该实现自动处理带特殊字符的列名，同时通过参数传值避免SQL注入风险

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