Update trigger update tsvector

Question

I have the following tsvector update trigger for the tsvector user_tsv column in a table that allows me to perform a full-text search,

CREATE TRIGGER tsvector_user_update BEFORE INSERT OR UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(user_tsv, 'pg_catalog.english', firstname, surname, email); CREATE INDEX user_index_tsv ON users USING gin(user_tsv); 

Since then I have added another column to the table that I want to include in this trigger, is there a way to update the trigger (as well as the tsvector column) or do I need to remove the column / trigger and re-add them?

PostgreSQL 9.0

Thank you in advance

David

Answer 1

You do not need to drop the column or index, just remove and recreate the trigger and refresh all changed fields.

Drop the trigger using the regular DROP TRIGGER statement, and then recreate it with a new column using tsvector_update_trigger , all in one transaction. Now UPDATE tablename SET user_tsv = ... WHERE new_column IS NOT NULL update column if you have already added columns. Examine the sources of the tsvector update trigger to find the appropriate expression to use in the assignment.