Re: Identifying which column matches a full text search

Ryan Wallace wrote:
> Richard Huxton wrote:
>> Failing that, where I've had many (a dozen) different sources but want 
>> to search them all I've built a textsearch_blocks table with columns to 
>> identify the source and have triggers that keep it up to date.
> 
> Once you've built the text search blocks table, how do you search it? Do you
> perform
> twelve separate queries or can you just do one?

OK, you have a table something like:

fulltext_blocks (  section  varchar(32),  itemid   int4,  words    tsvector,them  PRIMARY KEY (section, itemid)
)

Now assume two of the things I search are "news" and "faqs". I'm 
assuming they've both got a simple serial pkey - if not, "itemid" above 
needs to be text and you'll have to cast.

For each target table (news, faqs) add a trigger that updates 
fulltext_blocks appropriately. This can include weighting title and body 
of a news article.

Then, search the fulltext_blocks table, optionally filtering by section. 
If you're going to have lots of results put the ids into a (perhapd 
temporary) results-table. Then join your results back to the original 
tables with the appropriate UNION (if you need to - it might be you 
fetch results one at a time elsewhere in your app).

SELECT n.id, n.title, n.body
FROM news n JOIN results r ON n.id=r.id
WHERE r.section='news'
UNION ALL
SELECT f.id, f,question, f.answer
FROM faqs f JOIN results r ON f.id=r.id
WHERE r.section='faqs'
;

You'll probably want to set ownership/permissions on the triggers / 
fulltext_blocks table so you can't accidentally update it directly.

In mine I even had a "documents" section which relied on an external 
cron-driven script to strip the first 32k of text out of uploaded 
documents (pdf,word) in addition to user-supplied metadata (title, summary).

Note - this is basically simulating what we could do if you could index 
a view. The fulltext_blocks table is nothing more than a materialised view.

HTH

--   Richard Huxton  Archonet Ltd