summaryrefslogtreecommitdiffstats
path: root/forum/sql_scripts/pg_fts_install.sql
diff options
context:
space:
mode:
authorEvgeny Fadeev <evgeny.fadeev@gmail.com>2010-04-25 17:15:26 -0400
committerEvgeny Fadeev <evgeny.fadeev@gmail.com>2010-04-25 17:15:26 -0400
commitcc8337da9046bff5243672e20f1dea9c18b00da6 (patch)
tree77ade69869105f1838df5e8616bb994844507cec /forum/sql_scripts/pg_fts_install.sql
parent3122fb8a2599944e623c8e21f285a9e4dd9e132a (diff)
parent02510a462392dd2e9e46e945d51efb374e0dc06f (diff)
downloadaskbot-cc8337da9046bff5243672e20f1dea9c18b00da6.tar.gz
askbot-cc8337da9046bff5243672e20f1dea9c18b00da6.tar.bz2
askbot-cc8337da9046bff5243672e20f1dea9c18b00da6.zip
merged newer ui branch to master
Diffstat (limited to 'forum/sql_scripts/pg_fts_install.sql')
-rw-r--r--forum/sql_scripts/pg_fts_install.sql38
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;