Re: tsvector Column Indexing Across Two Tables

On Sun, Sep 6, 2009 at 9:57 PM, APseudoUtopia<apseudoutopia(at)gmail(dot)com> wrote:
> 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.
>

Sorry to post again. I was reading over the documentation and I
discovered that it is possible to concatenate two tsvector's together.
So I can concat the subject tsvector index from the forums_topics
table with the post body in forums_posts.