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: [email protected]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
