How to cast xpath's returning xml array to an int array in Postgres

Question

Is there a way to cast the returning XML array to an INT array when using xpath in Postgres 9.5? It appears to let me assign it to an INT array prior to using it but I cannot figure out how to use it directly in the where clause. Below is an example of how I am currently using it.

CREATE OR REPLACE FUNCTION people_get_by_ids(_xml XML)
RETURNS SETOF people AS
$$
DECLARE
    _ids INT[] = (xpath('/ITEMS/ITEM/VALUE/text()', _xml));
BEGIN
    RETURN QUERY
        SELECT *
        FROM people
        WHERE id = ANY(_ids);
END
$$ LANGUAGE plpgsql STABLE;

I would then call it like so

SELECT * FROM people_get_by_ids('<ITEMS><ITEM><VALUE>488</VALUE></ITEM><ITEM><VALUE>489</VALUE></ITEM></ITEMS>');

It would be nice if there was an simple and performant way to not use the _ids variable and just put the xpath part in the where clause.

I need to do this in several places and plan on creating a function to wrap the (xpath('/ITEMS/ITEM/VALUE/text()', _xml)) part in.

Dzmitry Savinkou · Accepted Answer · 2015-11-10 23:55:17Z

8

I was able to do that with xml[] -> varchar[] -> integer[] cast:

WITH test_xml(data) AS ( VALUES 
  ('<ROOT><INPUT attr="1"/></ROOT>'::XML)           
), int_array AS (
  SELECT (((xpath('//INPUT/@attr',test_xml.data))::varchar[])::integer[]) as value FROM test_xml
)
SELECT value,pg_typeof(value) FROM int_array;

Result is :

 value | pg_typeof 
-------+-----------
 {1}   | integer[]
(1 row)

answered Nov 10, 2015 at 23:55

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Billy Over a year ago

Thank you! I previously tried the cast but was doing ::varchar::integer instead of ::varchar[]::integer[]

Victoria Stuart Over a year ago

See also my answer, here (includes xpath examples): stackoverflow.com/questions/50054073/…

Collectives™ on Stack Overflow

How to cast xpath's returning xml array to an int array in Postgres

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related