PostgreSQL：如何在FOR EACH STATEMENT触发器中获取更新列列表

我们可以通过对比语句级触发器提供的OLD_TABLE和NEW_TABLE临时快照，筛选出目标可搜索列发生变化的行，仅在这些行上重新生成tsvector，避免无意义的计算开销。

具体实现代码 #

CREATE OR REPLACE FUNCTION update_search_tsvector()
RETURNS TRIGGER AS $$
DECLARE
    -- 定义需要监控的可搜索列列表
    target_cols TEXT[] := ARRAY['title', 'content', 'author'];
    has_relevant_changes BOOLEAN;
BEGIN
    -- 检查当前UPDATE语句是否修改了目标列
    SELECT EXISTS (
        SELECT 1
        FROM OLD_TABLE old_row
        JOIN NEW_TABLE new_row ON old_row.id = new_row.id -- 用表的主键关联新旧行
        WHERE EXISTS (
            SELECT 1
            FROM unnest(target_cols) col
            WHERE old_row.col::TEXT IS DISTINCT FROM new_row.col::TEXT
        )
    ) INTO has_relevant_changes;

    IF has_relevant_changes THEN
        -- 仅更新目标列有变化的行的tsvector字段
        UPDATE your_table t
        SET search_vector = to_tsvector('english', 
            COALESCE(t.title, '') || ' ' || 
            COALESCE(t.content, '') || ' ' || 
            COALESCE(t.author, '')
        )
        FROM NEW_TABLE new_row
        JOIN OLD_TABLE old_row ON new_row.id = old_row.id
        WHERE t.id = new_row.id
        AND EXISTS (
            SELECT 1
            FROM unnest(target_cols) col
            WHERE old_row.col::TEXT IS DISTINCT FROM new_row.col::TEXT
        );
    END IF;

    RETURN NULL; -- 语句级触发器无需返回有效行
END;
$$ LANGUAGE plpgsql;

-- 创建语句级触发器
CREATE TRIGGER trigger_update_search_vector
AFTER UPDATE ON your_table
FOR EACH STATEMENT
EXECUTE FUNCTION update_search_tsvector();

关键逻辑说明 #

1. 快照对比：利用PostgreSQL语句级触发器内置的OLD_TABLE和NEW_TABLE，分别存储更新前后的全量行数据，通过主键关联后逐列检查目标字段是否有变化。
2. 条件执行：先通过EXISTS判断是否存在需要更新的行，避免无意义的UPDATE操作；仅当目标列有变化时，才对对应行重新生成tsvector。
3. 批量友好：针对1-5万行的批量更新场景，这种方式能大幅减少不必要的tsvector计算，降低CPU和IO开销。

注意事项 #

• 确保表存在主键或唯一约束，用于关联OLD_TABLE和NEW_TABLE的行记录。
• 如果目标列是非文本类型，需调整类型转换逻辑（比如old_row.col::TEXT改为对应类型的对比方式）。
• 可根据实际搜索需求调整to_tsvector的配置（比如替换语言模板、调整字段拼接规则）。

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