Re: joins with text search

On Tue, Sep 07, 2010 at 10:42:53PM -0400, pg(at)slatech(dot)com wrote:
> i am currently populating the textsearch column with the following
> command:
>
> UPDATE
> products
> SET
> textsearch=setweight(to_tsvector('english', description), 'A') ||
> setweight(to_tsvector('english', part_number, 'B')
> WHERE
> product_list_id=3
>
>
> Is there a way I can join products.vendor_id with vendors.id and get the
> vendor.name in the textsearch column as well?

Yup, you can either do a join inside the UPDATE, or do a subquery. Here
it's probably easiest to do a correlated subquery:

UPDATE products p SET textsearch=nullif('',
coalesce(setweight(to_tsvector('english', p.description), 'A'),'') ||
coalesce(setweight(to_tsvector('english', p.part_number), 'B'),'') ||
coalesce(setweight(to_tsvector('english', (
SELECT v.name FROM vendors v WHERE v.id = p.vendor_id)), 'A'),''))
WHERE p.product_list_id=3;

I've put the calls to coalesce in so that if you happen to have any
fields missing you'll still get a useful text search column.

I'd also be tempted to remove the "to_tsvector" call from the
part_number, as I doubt it really is an english bit of text. You can
probably just use it as a tsvector literal, probably quoting it first,
maybe something like:

coalesce(setweight(quote_literal(p.part_number)::tsvector,'B'),'')

--
Sam http://samason.me.uk/