On Mon, Apr 25, 2011 at 8:38 PM, elij <[email protected]> wrote: > On Mon, Apr 25, 2011 at 7:49 PM, Dan McGee <[email protected]> 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."
