[aur-dev] some notes from converting the aur from mysql to postgresql

[elij]

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."