[aur-dev] some notes from converting the aur from mysql to postgresql
elij
elij.mx at gmail.com
Mon Apr 25 23:40:11 EDT 2011
On Mon, Apr 25, 2011 at 8:38 PM, elij <elij.mx at gmail.com> wrote:
> On Mon, Apr 25, 2011 at 7:49 PM, Dan McGee <dpmcgee at 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."
More information about the aur-dev
mailing list