[rt-users] RT full text indexing with PostgreSQL

Kenneth Marshall ktm at rice.edu
Sun Feb 1 22:41:42 EST 2009


I just updated the PostgreSQLFullText pages on the wiki with
the triggers to keep the parsed document columns updated whenever
there is a change to the dependent columns.

Here are the commands for adding the triggers to keep the textsearchable
columns corresponding to attachments.subject/content and
objectcustomfieldvalues.largecontent up to date:

  CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
  ON attachments FOR EACH ROW EXECUTE PROCEDURE
  tsvector_update_trigger(textsearchable, 'pg_catalog.english', subject, content);

  CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
  ON objectcustomfieldvalues FOR EACH ROW EXECUTE PROCEDURE
  tsvector_update_trigger(textsearchable, 'pg_catalog.english', largecontent);

Please let me know if there are any problems and bon voyage.

Ken



More information about the rt-users mailing list