Re: Text search

On 16/03/10 12:36, Chris Roffler wrote:
> Richard
>
> I tried all that and you can see it on this thread, there are some
> limitations on indexs on xpath work
>
> http://archives.postgresql.org/pgsql-general/2010-03/msg00270.php
> <http://archives.postgresql.org/pgsql-general/2010-03/msg00270.php>

OK - I've read through your other thread and I think you either:
1. Don't want to use a relational database for this (use an XML database
instead)
2. Want to make the data at least slightly relational.

You're trying to index multiple fragments of a value. In your case it
seems to be <name> tags within an XML fragment containing a list of
<attributes>. That's exactly equivalent to trying to get an index search
for '%ABC%' on text.

There's nothing you can do without exposing the structure of your value
to the database.

You could write a custom parser for tsearch so it picked out only the
relevant pieces of XML. That's probably more work than you want though.

You could run an xslt transform over the xml fragments and extract what
you want and then use tsearch to index that, I suppose. Similarly, you
might be able to do the same via xslt and xquery.

Finally, and to my mind most sensibly, if you want to search attributes,
then store attributes. Parse out your XML and have an "attributes" table
(id, name, value, last_changed, changed_by). That's not brilliant
because every value will just be text, but at least each attribute is
its own row.

--
Richard Huxton
Archonet Ltd