[HACKERS] proposal casting from XML[] to int[], numeric[], text[]

[Pavel Stehule]

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