PostgreSQL通用审计触发器函数报错求助：动态表名与审计数据插入

你的代码存在几个关键问题： #

• 多余的end if;：第一个IF逻辑块结束后多了一个无用的end if;，直接触发语法错误。
• 动态SQL拼接错误：直接将元组(my_action,current_user, now(), row_to_json(old))拼接到SQL字符串中，PostgreSQL无法识别这种写法，同时存在SQL注入风险。
• OLD值为空的情况：当触发INSERT操作时，OLD是NULL，调用row_to_json(old)会报错。
• 表名引用不安全：直接拼接模式名和表名，若名称包含特殊字符会出错，也存在注入风险。

修正后的代码： #

CREATE OR REPLACE FUNCTION audit_function_tr()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
declare
    input_table_name text;
    my_action char(1);
    audit_record json;
begin
    -- 用format安全拼接模式名和表名，%I用于标识符引用，自动处理特殊字符
    input_table_name := format('%I.%I', 'employee', TG_ARGV[0]);
    
    CASE TG_OP
        WHEN 'INSERT' THEN
            my_action := 'I';
            audit_record := row_to_json(NEW);
        WHEN 'DELETE' THEN
            my_action := 'D';
            audit_record := row_to_json(OLD);
        ELSE
            my_action := 'U';
            audit_record := row_to_json(OLD);
    END CASE;

    -- 用format构建安全的动态SQL，通过USING传递参数，彻底避免SQL注入和拼接错误
    EXECUTE format('INSERT INTO %s VALUES ($1, $2, $3, $4)', input_table_name)
    USING my_action, current_user, now(), audit_record;

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$function$;

关键改进点： #

• 用format('%I.%I', ...)处理模式名和表名，自动适配特殊字符、大小写等场景，避免语法错误。
• 使用USING子句传递参数，替代字符串拼接，彻底消除SQL注入风险，同时解决拼接导致的语法问题。
• 针对INSERT操作改用NEW生成JSON记录，避免OLD为NULL的报错。
• 用CASE语句替代多分支IF，逻辑更简洁清晰。
• 移除多余的end if;，修复基础语法错误。

触发器创建示例： #

假设审计表employee.employee_audit包含action char(1)、audit_user text、audit_time timestamp、record json字段，创建触发器时传入审计表名：

CREATE TRIGGER tr_employee_audit
AFTER INSERT OR UPDATE OR DELETE ON public.employee
FOR EACH ROW EXECUTE FUNCTION audit_function_tr('employee_audit');

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