diff options
Diffstat (limited to 'sql_scripts/pg_fts_install.sql')
-rw-r--r-- | sql_scripts/pg_fts_install.sql | 38 |
1 files changed, 38 insertions, 0 deletions
diff --git a/sql_scripts/pg_fts_install.sql b/sql_scripts/pg_fts_install.sql new file mode 100644 index 00000000..d0655134 --- /dev/null +++ b/sql_scripts/pg_fts_install.sql @@ -0,0 +1,38 @@ +ALTER TABLE question ADD COLUMN tsv tsvector; + +CREATE OR REPLACE FUNCTION public.create_plpgsql_language () + RETURNS TEXT + AS $$ + CREATE LANGUAGE plpgsql; + SELECT 'language plpgsql created'::TEXT; + $$ +LANGUAGE 'sql'; + +SELECT CASE WHEN + (SELECT true::BOOLEAN + FROM pg_language + WHERE lanname='plpgsql') + THEN + (SELECT 'language already installed'::TEXT) + ELSE + (SELECT public.create_plpgsql_language()) + END; + +DROP FUNCTION public.create_plpgsql_language (); + +CREATE OR REPLACE FUNCTION set_question_tsv() RETURNS TRIGGER AS $$ +begin + new.tsv := + setweight(to_tsvector('english', coalesce(new.tagnames,'')), 'A') || + setweight(to_tsvector('english', coalesce(new.title,'')), 'B') || + setweight(to_tsvector('english', coalesce(new.summary,'')), 'C'); + RETURN new; +end +$$ LANGUAGE plpgsql; + +CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE +ON question FOR EACH ROW EXECUTE PROCEDURE set_question_tsv(); + +CREATE INDEX blog_entry_tsv ON blog_entry USING gin(body_tsv); + +UPDATE question SET title = title; |