Firing Postgres triggers on different table columns

Question

CONTENT_TABLE
id | author | timestamp | title | description 
----+-----------------+-----------+----------------+----------------------
(0 rows)

SEARCH_TABLE
id | content_type_id | object_id | tsvector_title | tsvector_description 
----+-----------------+-----------+----------------+----------------------
(0 rows)

I have to fire a trigger when ever CONTENT_TABLE is UPDATED/INSERTED

Something like this:

"CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON course_course FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(SHOULD_BE_THE_COLUMN_OF_SEARCH_TABLE(tsvector_description), 'pg_catalog.english', description);"

• Actually, i have to add tsvector for title and description of the CONTENT_TABLE to the table SEARCH_TABLE tsvector_title and tsvector_description. Can i just fire one trigger for it?

Any sort of help will be appreciated. Thanks in advance.

Answer 1

The default tsvector_update_trigger can't look at another table. But if you write your own (in for example pl/pgsql), you can do that easily. see http://www.postgresql.org/docs/8.4/static/plpgsql-trigger.html. You just need to collect your data and have your function call to_tsvector(), sticking the result in the tsvector column.