On 11/11/2007, Nikolay Samokhvalov <[EMAIL PROTECTED]> wrote: > > > On Sep 25, 2007 10:57 AM, Pavel Stehule <[EMAIL PROTECTED]> wrote: > > Hello > > > > Current result from xpath function isn't indexable. It cannot be > > problem with possibility cast it to some base types. > > > > CREATE OR REPLACE FUNCTION xml_list_to_int(xml) > > RETURNS int AS $$ > > SELECT ARRAY(SELECT to_number(($1[i])::text,'999999.99')::int > > FROM generate_series(1, array_upper($1,1)) g(i)) > > $$ LANGUAGE SQL IMMUTABLE; > > > > > > CREATE CAST (xml AS int) WITH FUNCTION xml_list_to_int(xml); > > > > -- now I can build functional index > > CREATE INDEX fx ON foo USING > > GIN((xpath('//id/text()',objednavka_v_xml)::int)); > > > > Does anybody know better solution? > > > > Alternative (and maybe better) approach would be: > - create comparison functions that work in the same way as string > comparison functions do (currently, it's straight forward since XML is > stored as string); > - do NOT create comparison operators to avoid explicit comparing XML values > (to follow standard ways to work with XML and to avoid possible unexpected > behaviors); > - create opclass based on these functions and, therefore, obtain GIN > indexes support for xml; > - describe in the docs, that one can use GIN indexes over XPath > expressions, but should be aware that comparison with non-trivial XML > constants have to be used carefully because of possible problems with > whitespaces, etc (in other words, comparison here is doing letter by letter, > as for varchar). > > If there are no objections I'll send patch for this. > It's good proposal. So only this is solution for indexing. I belive so casting from xml to any others (mainly varchar and numeric can be usefull.
Regards Pavel ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly