diff options
author | Evgeny Fadeev <evgeny.fadeev@gmail.com> | 2010-04-24 19:11:05 -0400 |
---|---|---|
committer | Evgeny Fadeev <evgeny.fadeev@gmail.com> | 2010-04-24 19:11:05 -0400 |
commit | 3b8821b7e6c00a34fe71b22f011841786bb9d7e5 (patch) | |
tree | d0216d50ede25086efe89aa41e9a4aeac07b1094 /forum/sql_scripts/pg_fts_install.sql | |
parent | 9dac785a3416e3472a69155247ad5d5af58636db (diff) | |
download | askbot-3b8821b7e6c00a34fe71b22f011841786bb9d7e5.tar.gz askbot-3b8821b7e6c00a34fe71b22f011841786bb9d7e5.tar.bz2 askbot-3b8821b7e6c00a34fe71b22f011841786bb9d7e5.zip |
moved some files and directories inside forum from root
Diffstat (limited to 'forum/sql_scripts/pg_fts_install.sql')
-rw-r--r-- | forum/sql_scripts/pg_fts_install.sql | 38 |
1 files changed, 38 insertions, 0 deletions
diff --git a/forum/sql_scripts/pg_fts_install.sql b/forum/sql_scripts/pg_fts_install.sql new file mode 100644 index 00000000..d0655134 --- /dev/null +++ b/forum/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; |