joins with text search

On Tue, Sep 07, 2010 at 10:42:53PM -0400, pg@slatech.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/