Answer the question
In order to leave comments, you need to log in
Is it possible to use a double SELECT in a RECURSIVE CTE?
Hello. I ran into a problem with implementing a database query. The problem lies in the implementation of the double SELECT in recursive queries. More details below.
I have two tables that describe a directed graph (many-to-many relationship) in which the nodes are located at certain positions on the grid. grid_x, grid_y - node coordinates on the grid.
Table nodes
id
grid_x
grid_y
Table edges
id
source
target
WITH RECURSIVE
cte(id, grid_x, grid_y) AS (
SELECT id, grid_x, grid_y
FROM nodes WHERE id = 0
UNION ALL
SELECT current.id, current.grid_x, current.grid_y
FROM nodes AS current, cte AS prev
WHERE current.grid_x = prev.grid_x + 1
AND current.grid_y = prev.grid_y
)
SELECT * FROM cte;
WITH RECURSIVE
cte(id, grid_x, grid_y) AS (
SELECT id, grid_x, grid_y
FROM nodes WHERE id = 0
UNION ALL
SELECT * FROM (
SELECT current.id, current.grid_x, current.grid_y
FROM nodes AS current, cte AS prev
WHERE current.grid_x = prev.grid_x + 1
AND current.grid_y = prev.grid_y
UNION
SELECT c.id, c.grid_x, c.grid_y
FROM nodes AS c, cte AS p, edges AS e
WHERE p.id = e.source AND c.id = e.target
AND here should be checking on existing node at cte
)
)
SELECT * FROM cte;
recursive reference to query "cte" must not appear within a subquery
WITH RECURSIVE
cte(id, grid_x, grid_y) AS (
SELECT id, grid_x, grid_y
FROM nodes WHERE id = 0
UNION ALL
SELECT current.id, current.grid_x, current.grid_y
FROM nodes AS current, edges AS e, cte AS prev
WHERE (current.grid_x = prev.grid_x + 1
AND current.grid_y = prev.grid_y)
OR (prev.id = e.source AND e.target = current.id)
)
SELECT * FROM cte;
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question