Hey list,
I have a forum. I'm in the process of adding a full-text search. The
forum is split into a couple tables, including forums_posts and
forums_topics. The latter contains only the title of the topic and
some other information, like an ID number. The forums_posts table
contains the body of the post, the topicid it belongs to, and some
other things.
I'd like to index the title of the topic as well as the body of the
posts in a single tsvector column. I'm investigating ways to do this.
I created a tsvector column in forums_topics. I would have to somehow
LEFT JOIN the forums_posts table to get the body of the post.
Something like this:
ALTER TABLE "forums_topics" ADD COLUMN "search_index" tsvector;
UPDATE "forums_posts" SET "search_index" = to_tsvector('english',
coalesce("forums_topics"."subject", '') || ' ' ||
coalesce("forums_posts"."body", '')) FROM "forums_topics" ON
("forums_posts"."topicid" = "forums_topics"."id");
I don't think this would be the correct JOIN in the UPDATE clause. It
would need to be a `topics LEFT JOIN posts` type join. I'm not sure
how to do this properly.
Also, how would a trigger work in this case? tsvector_update_trigger()
does not work across tables, does it? I would have to write my own
procedure in order to correctly use a trigger. I'm not sure how to
begin doing that.
Thanks.