[GIS] Getting multiple shortest paths with PgRouting in one query

Question

I want to run shortest path algorithm on multiple source&target pairs at once and get a result as a table and process it then.

How do I do this? This query does not work:

SELECT a.source, a.target, paths.* 
FROM all_to_all a, shortest_path_astar('...', a.source, a.target, false, false) paths;

ERROR:  function expression in FROM cannot refer to other relations of same query level

(btw, all_to_all does not literally mean all to all, 🙂 it's just a number of random pairs)

This does not work either:

SELECT * 
FROM all_to_all a, (
   SELECT * FROM shortest_path_astar('...', a.source, a.target, false, false) yyy
) AS t2;

Answer 1

Something like

SELECT 
  source, 
  target,
  (SELECT SUM(cost) FROM  -- or whatever you want to do with the routing result
     (SELECT * FROM shortest_path_astar('...',
       source,
       target,
       false,
       false)
     ) AS foo 
  ) AS cost
FROM all_to_all;