Created
April 27, 2011 20:51
-
-
Save scottlowe/945175 to your computer and use it in GitHub Desktop.
SQL required to create a TSVECTOR column with associated triggers and indexes
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment