On 11/11/2007, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:
>
>
> On Sep 25, 2007 10:57 AM, Pavel Stehule <pavel.stehule@gmail.com> 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
