PostgreSQL中如何结合old.variablename获取列数据？附多表审计触发器需求

嘿，看来你正在搭建通用的PostgreSQL审计触发器，这个需求挺常见的！我来帮你搞定如何通过动态获取的主键列名来提取old行里的对应值～

首先你已经通过information_schema拿到了主键列名并存到prim变量里，这一步没问题。但PostgreSQL的PL/pgSQL里不能直接用old.prim这种写法（因为解析SQL时prim还是变量，不是固定字段名），所以得用下面两种方法来处理：

方法1：使用动态SQL（EXECUTE + format） #

这种方法通过动态构建SQL语句来访问old行的指定字段，能严格匹配字段的原生类型：

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
DECLARE
    prim text;
    pk_value text; -- 如果主键是整数/其他类型，可以改成对应类型，比如int
BEGIN
    -- 先获取当前触发表的单主键列名（假设表是单主键）
    prim := (SELECT c.column_name 
             FROM information_schema.key_column_usage AS c 
             JOIN information_schema.table_constraints AS tc 
               ON c.constraint_name = tc.constraint_name
             WHERE tc.table_name = TG_TABLE_NAME 
               AND tc.constraint_type = 'PRIMARY KEY'
               AND c.table_name = TG_TABLE_NAME
             LIMIT 1);

    -- 动态提取old行的主键值
    EXECUTE format('SELECT $1.%I', prim) INTO pk_value USING old;
    
    -- 这里可以把主键值写入审计表，示例：
    INSERT INTO audit_log (table_name, pk_column, pk_value, change_type, changed_at)
    VALUES (TG_TABLE_NAME, prim, pk_value, TG_OP, now());

    RETURN OLD; -- 根据触发器类型调整，比如INSERT返回NEW
END;
$$ LANGUAGE plpgsql;

这里的关键细节：

• format('%I', prim)会自动转义列名，避免SQL注入和非法标识符问题
• USING old把触发器上下文里的old行传递给动态SQL，$1就代表这个行记录
• 如果主键是数值类型，直接把pk_value的类型改成int/bigint即可，不需要转文本

方法2：借助JSONB转换（更简洁） #

把old行转换成JSONB对象，再通过键名提取值，写法更简单：

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
DECLARE
    prim text;
    pk_value text;
BEGIN
    -- 同样先获取主键列名
    prim := (SELECT c.column_name 
             FROM information_schema.key_column_usage AS c 
             JOIN information_schema.table_constraints AS tc 
               ON c.constraint_name = tc.constraint_name
             WHERE tc.table_name = TG_TABLE_NAME 
               AND tc.constraint_type = 'PRIMARY KEY'
               AND c.table_name = TG_TABLE_NAME
             LIMIT 1);

    -- 用JSONB提取主键值
    pk_value := to_jsonb(old) ->> prim;
    -- 如果要保留原生类型，比如整数：pk_value := (to_jsonb(old) -> prim)::int;
    
    -- 写入审计表
    INSERT INTO audit_log (table_name, pk_column, pk_value, change_type, changed_at)
    VALUES (TG_TABLE_NAME, prim, pk_value, TG_OP, now());

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

这种方法的优势是不需要写动态SQL，代码更直观，适合快速开发。

处理复合主键的情况 #

如果你的表是复合主键（多个主键列），只需要把主键列名存成数组，再批量提取值即可：

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
DECLARE
    prims text[];
    pk_values text[];
BEGIN
    -- 获取所有主键列名（按顺序）
    prims := ARRAY(SELECT c.column_name 
                   FROM information_schema.key_column_usage AS c 
                   JOIN information_schema.table_constraints AS tc 
                     ON c.constraint_name = tc.constraint_name
                   WHERE tc.table_name = TG_TABLE_NAME 
                     AND tc.constraint_type = 'PRIMARY KEY'
                     AND c.table_name = TG_TABLE_NAME
                   ORDER BY c.ordinal_position);

    -- 批量提取每个主键列的值
    pk_values := ARRAY(SELECT to_jsonb(old) ->> col FROM unnest(prims) col);
    
    -- 把多个主键值拼接后存入审计表，或者分别存储
    INSERT INTO audit_log (table_name, pk_columns, pk_values, change_type, changed_at)
    VALUES (TG_TABLE_NAME, array_to_string(prims, ','), array_to_string(pk_values, ','), TG_OP, now());

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

这样不管是单主键还是复合主键，触发器都能通用啦！

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