PostgreSQL: use column as ARRAY in a WITH construction

November 4, 2021

I am trying to run the following query (I put in comment a few things I have tried so far):

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;

It works using array[54, 37, 897, 435] but not "nodes". With my trials, I receive a ‘column "nodes" does not exist’. How can I use this ‘node’ column in ‘res’ as an array in ‘viapath’?

>Solution :

You may use a subquery expression, (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;