要使用转换表检查PostgreSQL触发器中的特定列是否发生了更改，您可以遍历每对新旧行，并使用IS DISTINCT FROM运算符比较相关列的值。下面是一个如何在触发器函数中完成此操作的简化示例：

FOR row_old, row_new IN
    SELECT old.*, new.*
    FROM old_table AS old
    JOIN new_table AS new ON old.primary_key = new.primary_key
LOOP
    -- Check if any of the relevant columns have changed
    IF row_old.searchable_column1 IS DISTINCT FROM row_new.searchable_column1
       OR row_old.searchable_column2 IS DISTINCT FROM row_new.searchable_column2
       -- Add more columns as needed
    THEN
        -- Perform tsvector update logic here
    END IF;
END LOOP;

字符串

使用触发器

CREATE OR REPLACE FUNCTION my_trigger_function()
RETURNS TRIGGER AS $$
DECLARE
    row_old RECORD;
    row_new RECORD;
    is_changed BOOLEAN;
BEGIN
    -- Iterate over each pair of old and new rows
    FOR row_old, row_new IN
        SELECT * FROM old_table
        JOIN new_table ON old_table.primary_key = new_table.primary_key
    LOOP
        -- Check if any of the relevant columns have changed
        is_changed := row_old.searchable_column1 IS DISTINCT FROM row_new.searchable_column1
                   OR row_old.searchable_column2 IS DISTINCT FROM row_new.searchable_column2
                   -- Add more columns as needed
                   ;

        -- Update tsvector if there is a change in relevant columns
        IF is_changed THEN
            -- Perform tsvector update logic here
        END IF;
    END LOOP;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER my_trigger
AFTER UPDATE ON my_table
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE FUNCTION my_trigger_function();

型