PostgreSQL触发器如何判断指定列未被纳入UPDATE列表？

核心需求 #

需要在PostgreSQL的INSERT/UPDATE触发器中，仅当指定列（如source）未被显式纳入UPDATE列表时执行逻辑，等效于SQL Server的UPDATE()函数的反向判断——即排除所有显式更新该列的场景（哪怕更新后值未变化）。

解决方案 #

PostgreSQL没有直接提供UPDATE()函数，但可以通过以下两种方法实现需求：

方法1：基于当前查询字符串匹配（简单易用，适合规范场景） #

通过current_query()获取当前执行的SQL语句，用正则表达式判断目标列是否出现在UPDATE的SET子句中。

触发器函数示例：

CREATE OR REPLACE FUNCTION set_auto_source()
RETURNS TRIGGER AS $$
BEGIN
    -- INSERT场景：未指定source列时自动设置
    IF TG_OP = 'INSERT' THEN
        IF NEW.source IS NULL THEN
            NEW.source = 'auto_generated'; -- 替换为你的自动设置逻辑
        END IF;
    -- UPDATE场景：仅当source未被纳入UPDATE列表时自动设置
    ELSIF TG_OP = 'UPDATE' THEN
        -- 匹配是否存在"source = "形式的赋值（忽略大小写和空格）
        IF NOT current_query() ~* '\bsource\s*=' THEN
            NEW.source = 'auto_generated'; -- 替换为你的自动设置逻辑
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

创建触发器：

CREATE TRIGGER trigger_auto_set_source
BEFORE INSERT OR UPDATE ON your_table_name
FOR EACH ROW
EXECUTE FUNCTION set_auto_source();

注意：这种方法依赖SQL语句的写法规范，如果存在别名（如UPDATE t SET t.source = ...）、多行格式化或特殊语法（如SET (source) = (...)），正则表达式可能需要调整。

方法2：解析查询语法树（更可靠，适配复杂场景） #

通过PostgreSQL的pg_parse_query()函数解析当前查询的语法树，精准判断目标列是否被纳入UPDATE的SET子句。

辅助函数：判断列是否被更新

CREATE OR REPLACE FUNCTION is_column_in_update_list(colname text)
RETURNS boolean AS $$
DECLARE
    query_tree pg_node_tree;
    update_stmt pg_update_stmt;
    target_entry pg_target_entry;
BEGIN
    -- 解析当前查询语句
    query_tree := pg_parse_query(current_query());
    
    -- 仅处理UPDATE语句
    IF query_tree[0].kind = 'UpdateStmt' THEN
        update_stmt := query_tree[0]::pg_update_stmt;
        
        -- 遍历SET子句中的所有列
        FOREACH target_entry IN ARRAY update_stmt.targetList LOOP
            IF target_entry.resname = colname THEN
                RETURN true;
            END IF;
        END LOOP;
    END IF;
    RETURN false;
END;
$$ LANGUAGE plpgsql;

触发器函数示例：

CREATE OR REPLACE FUNCTION set_auto_source()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        IF NEW.source IS NULL THEN
            NEW.source = 'auto_generated'; -- 替换为你的自动设置逻辑
        END IF;
    ELSIF TG_OP = 'UPDATE' THEN
        -- 调用辅助函数判断source是否未被纳入UPDATE列表
        IF NOT is_column_in_update_list('source') THEN
            NEW.source = 'auto_generated'; -- 替换为你的自动设置逻辑
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

创建触发器：

CREATE TRIGGER trigger_auto_set_source
BEFORE INSERT OR UPDATE ON your_table_name
FOR EACH ROW
EXECUTE FUNCTION set_auto_source();

优势：不依赖SQL语句的字符串格式，能精准识别各种合法的UPDATE语法，适配复杂场景。

注意事项 #

• 如果使用嵌套触发器或动态SQL，current_query()可能返回外层查询语句，此时语法树解析方法可能失效，需结合业务场景调整。
• 确保触发器函数的执行权限足够，pg_parse_query()需要用户具备相应的系统权限。

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