tsvector.sql

 ALTER TABLE hotels ADD COLUMN searchtext TSVECTOR;

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

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

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


-- When the migration is run, create tsvector data for all the existing records
-- UPDATE hotels SET searchtext =
--    setweight(to_tsvector('pg_catalog.english', coalesce(new.name, '')), 'A') ||
--    setweight(to_tsvector('pg_catalog.english', coalesce(new.address, '')), 'B');