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!