How to pass xpath returned xml array to int array in Postgres

Question

Is there a way to pass the returned XML array to an INT array when using xpath in Postgres 9.5? Apparently, I can assign it to the INT array before using it, but I cannot figure out how to use it directly in the where clause. Below is an example of how I am using it now.

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;

Then I would call it that

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 a simple and efficient way not to 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 part (xpath('/ITEMS/ITEM/VALUE/text()', _xml)).

Answer 1

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;

:

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