1 ALTER TABLE forum_posts ADD textsearch tsvector;
3 UPDATE forum_posts fp SET textsearch = setweight(to_tsvector(coalesce(ft.subject,'')), 'A') || setweight(to_tsvector(coalesce(u.username,'')), 'B') || setweight(to_tsvector(coalesce(fp.message,'')), 'D') FROM forum_threads ft, users u WHERE fp.ftid = ft.ftid AND u.uid = fp.uid;
5 CREATE INDEX forum_posts_textsearch_index ON forum_posts USING gin(textsearch);
7 /*CREATE OR REPLACE FUNCTION update_forum_post() RETURNS "trigger"
9 my $query = spi_prepare(q{UPDATE forum_posts fp
10 SET textsearch = setweight(to_tsvector(coalesce(ft.subject,'')), 'A')
11 || setweight(to_tsvector(coalesce(u.username,'')), 'B')
12 || setweight(to_tsvector(coalesce(fp.message,'')), 'D')
13 FROM forum_threads ft, users u
14 WHERE fp.ftid = ft.ftid AND u.uid = fp.uid AND fp.fpid = $1},'int4');
15 spi_exec_prepared($query,$_TD->{new}{fpid});
20 CREATE OR REPLACE FUNCTION update_forum_post() RETURNS "trigger"
25 SELECT setweight(to_tsvector(coalesce(ft.subject,'')), 'A')
26 || setweight(to_tsvector(coalesce(u.username,'')), 'B') AS ts
28 FROM forum_threads ft, users u
29 WHERE NEW.ftid = ft.ftid AND u.uid = NEW.uid;
30 NEW.textsearch := rec.ts
31 || setweight(to_tsvector(coalesce(NEW.message,'')), 'D');
37 CREATE TRIGGER update_forum_post
38 BEFORE INSERT OR UPDATE ON forum_posts
40 EXECUTE PROCEDURE update_forum_post();