Re: Text search

On 16/03/10 13:49, Richard Huxton wrote:
> 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.

Actually, if it's only attribute names you're interested in you could do
it with xpath

Something like (untested):

ALTER TABLE time_series ADD attr_names text;

UPDATE time_series SET attr_names = array_to_string(
xpath('*/Attribute/Name/text()', external_attributes)
,' '
);

CREATE INDEX fti_attr_names ON time_series USING gin(
to_tsvector('simple', attr_names)
);

SELECT * FROM time_series WHERE
to_tsvector('simple', attr_names)
@@
to_tsquery('simple', 'attribute22');

I'd probably just store the tsvector rather than text unless the text is
of some use in itself.

If you plan to do anything with the attributes it'd still be better to
split them out into their own table though.

--
Richard Huxton
Archonet Ltd