On Sun, Feb 01, 2009 at 09:41:42PM -0600, Kenneth Marshall wrote: > 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
I have added a variation of the above triggers to the wiki page to only pre-parse the first 1MB of attachments/customfields: CREATE FUNCTION attachments_trigger() RETURNS trigger AS $$ begin new.textsearchable := to_tsvector('pg_catalog.english', substring(coalesce(new.subject, '') || coalesce(new.content, '') from 1 for 1000000)); return new; end $$ LANGUAGE plpgsql; CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON attachments FOR EACH ROW EXECUTE PROCEDURE attachments_trigger(); CREATE FUNCTION objectcustomfieldvalues_trigger() RETURNS trigger AS $$ begin new.textsearchable := to_tsvector('pg_catalog.english', substring(coalesce(new.largecontent, '') from 1 for 1000000)); return new; end $$ LANGUAGE plpgsql; CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON objectcustomfieldvalues FOR EACH ROW EXECUTE PROCEDURE objectcustomfieldvalues_trigger(); Please send me any feedback or suggestions. Cheers, Ken PS. The fast full contents searches really rock! _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com