Re: proposal casting from XML[] to int[], numeric[], text[] - Mailing list pgsql-hackers

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@momjian.us>        http://momjian.us EnterpriseDB
http://postgres.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +