diff options
author | hrcerqueira <hrcerqueira@gmail.com> | 2010-02-02 20:30:47 +0000 |
---|---|---|
committer | hrcerqueira <hrcerqueira@gmail.com> | 2010-02-02 20:30:47 +0000 |
commit | b319bd04d698fb02d516765938a3625259405f39 (patch) | |
tree | 2f28a95501da873770532890e3831bfa53a99478 /sql_scripts/pg_fts_install.sql | |
parent | 23245d01db7eeee3243d8eaf33129b50b44dcdb0 (diff) | |
download | askbot-b319bd04d698fb02d516765938a3625259405f39.tar.gz askbot-b319bd04d698fb02d516765938a3625259405f39.tar.bz2 askbot-b319bd04d698fb02d516765938a3625259405f39.zip |
missing files from the last commit
Diffstat (limited to 'sql_scripts/pg_fts_install.sql')
-rw-r--r-- | sql_scripts/pg_fts_install.sql | 80 |
1 files changed, 40 insertions, 40 deletions
diff --git a/sql_scripts/pg_fts_install.sql b/sql_scripts/pg_fts_install.sql index 345c5934..82056338 100644 --- a/sql_scripts/pg_fts_install.sql +++ b/sql_scripts/pg_fts_install.sql @@ -1,41 +1,41 @@ -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; - -SELECT (ts_rank(p.factors, tsv, p.query)) AS "ranking", "question"."id", "question"."title", +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;
+
+SELECT (ts_rank(p.factors, tsv, p.query)) AS "ranking", "question"."id", "question"."title",
"question"."author_id", "question"."added_at", "question"."wiki", "question"."wikified_at", "question"."answer_accepted", "question"."closed", "question"."closed_by_id", "question"."closed_at", "question"."close_reason", "question"."deleted", "question"."deleted_at", "question"."deleted_by_id", "question"."locked", "question"."locked_by_id", "question"."locked_at", "question"."score", "question"."vote_up_count", "question"."vote_down_count", "question"."answer_count", "question"."comment_count", "question"."view_count", "question"."offensive_flag_count", "question"."favourite_count", "question"."last_edited_at", "question"."last_edited_by_id", "question"."last_activity_at", "question"."last_activity_by_id", "question"."tagnames", "question"."summary", "question"."html" FROM "question" , "(SELECT '{0.2, 0.5, 0.75, 1.0}'::float4[] AS factors, plainto_tsquery(false) AS query) p" WHERE "question"."deleted" = E'robots' AND tsv @@ p.query ORDER BY "ranking" DESC LIMIT 21
\ No newline at end of file |