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

Added to TODO:

* Allow xml arrays to be cast to other data types

http://archives.postgresql.org/pgsql-hackers/2007-09/msg00981.php
http://archives.postgresql.org/pgsql-hackers/2007-10/msg00231.php
http://archives.postgresql.org/pgsql-hackers/2007-11/msg00471.php

---------------------------------------------------------------------------

Pavel Stehule 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?
>
> Regards
> Pavel Stehule
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +