arrays - PostgreSQL：在 WITH 构造中使用列作为数组

Question

我正在尝试运行以下查询（我评论了到目前为止我尝试过的一些事情）：

WITH
res AS (
    SELECT * ...
), -- res has a column of integers called "node". I need to transform this column into an array to use it in viapath below
nodes AS (
  -- SELECT ARRAY[node] FROM res
  -- SELECT array_agg(node) FROM res
),
viapath AS (
    SELECT * FROM pgr_dijkstraVia(
        'SELECT id, source, target, cost FROM edge_net',
        nodes  -- array[54, 37, 897, 435]
    )
)
SELECT * FROM viapath;

它使用 array[54, 37, 897, 435] 但不是“节点”。通过我的试验，我收到一个“列“节点”不存在”。如何将“res”中的“node”列用作“viapath”中的数组？

标签： arrayspostgresqlcommon-table-expression

Answer 1

您可以使用子查询表达式(select array_agg(node) from res).

WITH
res AS (SELECT * ...),
viapath AS 
(
    SELECT * FROM pgr_dijkstraVia
    (
        'SELECT id, source, target, cost FROM edge_net',
        (select array_agg(node) from res)
    )
)
SELECT * FROM viapath;