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.

Answer 1

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)