PostgreSQL：如何通过触发器保存含逗号的character varying数据？

你的问题出在存储过程处理OLD和NEW行记录的逻辑上——你把整行转成text类型（比如OLD::text），然后用逗号作为分隔符拆分字段值。但当你的character varying字段里包含逗号时，这种拆分就会把单个字段的内容拆成多个元素，导致生成的INSERT语句中列的数量和目标审计表usuarios_actividad不匹配，最终触发错误。

举个直观的例子：如果某个字段值是"Doe, John"，转成text后会自带逗号，STRING_TO_ARRAY会把它拆成两个独立元素，直接破坏了原本的字段对应关系。

我们需要彻底抛弃行转文本再拆分的不可靠方式，改为直接查询表的列结构，逐个获取OLD/NEW中的字段值，这样就能完全规避字段内特殊字符（比如逗号、引号）的干扰。

修改后的存储过程代码如下：

CREATE OR REPLACE FUNCTION process_audit()
RETURNS TRIGGER AS $$
DECLARE
    newtable text;
    col record;
    txtquery text;
    values_list_old text := '';
    values_list_new text := '';
BEGIN
    -- 构建审计表名称
    IF (TG_TABLE_SCHEMA = 'public') THEN
        newtable := TG_TABLE_NAME || '_actividad';
    ELSE
        newtable := TG_TABLE_SCHEMA || '_' || TG_TABLE_NAME || '_actividad';
    END IF;

    -- 确保审计表已创建
    PERFORM creartablaactividad(TG_TABLE_SCHEMA, TG_TABLE_NAME);

    -- 遍历原表所有列，按顺序构建值列表
    FOR col IN 
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_schema = TG_TABLE_SCHEMA 
          AND table_name = TG_TABLE_NAME
        ORDER BY ordinal_position
    LOOP
        -- 拼接OLD值的占位符
        IF TG_OP IN ('DELETE', 'UPDATE') THEN
            IF values_list_old <> '' THEN
                values_list_old := values_list_old || ', ';
            END IF;
            values_list_old := values_list_old || format('$1.%I', col.column_name);
        END IF;

        -- 拼接NEW值的占位符
        IF TG_OP IN ('INSERT', 'UPDATE') THEN
            IF values_list_new <> '' THEN
                values_list_new := values_list_new || ', ';
            END IF;
            values_list_new := values_list_new || format('$2.%I', col.column_name);
        END IF;
    END LOOP;

    -- 根据操作类型执行审计插入
    IF (TG_OP = 'DELETE') THEN
        txtquery := format(
            'INSERT INTO actividad.%I SELECT current_user, now(), ''D'', %s',
            newtable, values_list_old
        );
        EXECUTE txtquery USING OLD, NULL;
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        -- 插入旧值审计记录
        txtquery := format(
            'INSERT INTO actividad.%I SELECT current_user, now(), ''ANT'', %s',
            newtable, values_list_old
        );
        EXECUTE txtquery USING OLD, NULL;

        -- 插入新值审计记录
        txtquery := format(
            'INSERT INTO actividad.%I SELECT current_user, now(), ''U'', %s',
            newtable, values_list_new
        );
        EXECUTE txtquery USING NULL, NEW;
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        txtquery := format(
            'INSERT INTO actividad.%I SELECT current_user, now(), ''I'', %s',
            newtable, values_list_new
        );
        EXECUTE txtquery USING NULL, NEW;
        RETURN NEW;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

• 安全的字段引用：用format()函数和%I占位符处理列名，既能避免SQL注入风险，又能兼容带特殊字符的列名
• 可靠的值传递：通过USING子句直接传递OLD/NEW行记录，不需要手动处理值的转义，完美支持包含逗号、引号等特殊字符的字段值
• 精准的字段对应：从information_schema.columns获取原表列结构并按顺序遍历，确保审计表的列和原表完全对应
• 可选的IP记录：如果你需要记录客户端IP地址，只需把语句中的current_user, now()修改为current_user, inet_client_addr(), now()，同时确保审计表对应位置有inet类型的列（和你原来注释掉的逻辑一致）

请确保你的creartablaactividad函数创建的审计表，其列顺序和原表usuarios完全一致，否则需要调整列的遍历逻辑。修改后的存储过程不会影响已有审计数据，新的审计记录会正确生成。

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