addFullTextSearchToPostgresTable.sql

START TRANSACTION;

ALTER TABLE public.stories
  ADD COLUMN text_search tsvector;

UPDATE public.stories SET text_search =
  setweight(to_tsvector(author), 'A') ||
  setweight(to_tsvector(title), 'A') ||
  setweight(to_tsvector(description), 'B');

CREATE INDEX index_stories_on_text_search ON public.stories USING GIN(text_search);

CREATE FUNCTION stories_search_trigger() RETURNS trigger AS $$
begin
  new.text_search :=
        setweight(to_tsvector(new.author), 'A') ||
        setweight(to_tsvector(new.title), 'A') ||
        setweight(to_tsvector(new.description), 'B');
  return new;
end
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
  ON public.stories FOR EACH ROW EXECUTE PROCEDURE
  stories_search_trigger();

COMMIT TRANSACTION;