tsearch_migration.sql

-- Add the new tsvector column
ALTER TABLE articles ADD COLUMN tsv tsvector;

-- Create a function that will generate a tsvector from text data found in both the
-- title and body columns, but give a higher relevancy rating 'A' to the title data  
CREATE FUNCTION articles_generate_tsvector() RETURNS trigger AS $$
  begin
    new.tsv :=
      setweight(to_tsvector('pg_catalog.english', coalesce(new.title,'')), 'A') ||
      setweight(to_tsvector('pg_catalog.english', coalesce(new.body,'')), 'B');
    return new;
  end
$$ LANGUAGE plpgsql;

-- When articles row data is inserted or updated, execute the function
-- that generates the tsvector data for that row
CREATE TRIGGER tsvector_articles_upsert_trigger BEFORE INSERT OR UPDATE
  ON articles
  FOR EACH ROW EXECUTE PROCEDURE articles_generate_tsvector();

-- When the migration is run, create tsvector data for all the existing records
UPDATE articles SET tsv =
  setweight(to_tsvector('pg_catalog.english', coalesce(title,'')), 'A') ||
  setweight(to_tsvector('pg_catalog.english', coalesce(body,'')), 'B');

-- Create an index for the tsv column that is specialised for tsvector data
CREATE INDEX articles_tsv_idx ON articles USING gin(tsv);