sql_scripts/pg_fts_install.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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;