[SOLVED] Redirect output of one recursive query into another recursive query

Redirect output of one recursive query into another recursive query

I wanted to find the topological sort of a DAG.

CREATE TABLE topo(
v1 int,
v2 int
);

INSERT INTO topo VALUES (1,3),(2,5),(3,4),(4,5),(4,6),(5,7),(6,5),(7,null)

WITH RECURSIVE path(S,d) AS(
 SELECT t1.v1, 0 FROM topo t1 LEFT OUTER JOIN topo AS t2 ON t1.v1=t2.v2 
 WHERE t2.v2 IS NULL
 UNION ALL
 SELECT DISTINCT t1.v2, path.d + 1 FROM path INNER JOIN topo AS t1 ON 
 t1.v1=path.S
)
SELECT S FROM path GROUP BY S ORDER BY MAX(d);

This code gives the output of the topological order of a graph. Now I want to use this output into another recursive query to find the path from one vertex to another.

Can I use the output generated by this code into another recursive query?

Solution

Adding to your existing recursive sql to get path:

WITH RECURSIVE path AS(
 select 
   t1.v1 as start,
   t1.v2 as end,  
   CAST(t1.v1 as VARCHAR(30)) as path 
   0 as depth
 from topo t1 
   left outer join topo as t2 on t1.v1=t2.v2 
 where t2.v2 IS null

 UNION ALL
 select 
   path.start ,
   t1.v2 as end,
   path.path || '>' || t1.v1,       
   path.d + 1 
 from path 
   inner join topo as t1 on t1.v1=path.end
)
SELECT * FROM path

Just adding in a couple of more fields to track what's happening as you traverse your hierarchy. Start will be static from the first query. It will be 1 for every record since that is your starting point. End is whatever node you are currently working. path will concatenate the end node as each new one is found. depth will tell you how far you traversed.