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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
