Re: proposal casting from XML[] to int[], numeric[], text[]

On Sep 25, 2007 10:57 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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.