On Mon, Apr 25, 2011 at 8:38 PM, elij <elij.mx@gmail.com> wrote:
On Mon, Apr 25, 2011 at 7:49 PM, Dan McGee <dpmcgee@gmail.com> wrote:
And case sensitivity would come into play, no?
I built the fulltext index as follows: alter table packages add column tsv tsvector; update packages set tsv = to_tsvector('english', coalesce(lower(name), '') || ' ' || coalesce(lower(description), ''));
then added a trigger to update it for new data:
DROP FUNCTION IF EXISTS package_fulltext_trigger() CASCADE; CREATE FUNCTION package_fulltext_trigger() RETURNS trigger as $$ begin new.tsv := setweight(to_tsvector('english', coalesce(lower(new.name), '')), 'A') || setweight(to_tsvector('english', coalesce(lower(new.description), '')), 'B'); return new; end $$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS packages_tsv_update; CREATE TRIGGER packages_tsv_update BEFORE INSERT OR UPDATE on packages FOR EACH ROW EXECUTE PROCEDURE package_fulltext_trigger();
oops. Forgot to complete that thought.. "thus, it is case insensitive because everything is lowercased before turning it into a fulltext tsvector. The search input would probably need to be lowered as well, for the above implementation."