xml - 如何将 xpath 返回的 xml 数组转换为 Postgres 中的 int 数组

Question

在 Postgres 9.5 中使用 xpath 时，有没有办法将返回的 XML 数组转换为 INT 数组？它似乎让我在使用它之前将它分配给一个 INT 数组，但我不知道如何在 where 子句中直接使用它。下面是我目前如何使用它的示例。

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;

我会这样调用它

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

如果有一种不使用 _ids 变量而只是将 xpath 部分放在 where 子句中的简单且高效的方法，那就太好了。

我需要在多个地方执行此操作，并计划创建一个函数来包装 (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)