I need to iterate a graph using loops using a recursive CTE.
The problem is part of the cycle.
I want if there are cycles, and then the shortest way to choose. This basically means ignoring loops because recursion is the "width of the first".
The following example shows the returned data:
The problem is the comment INSERTthat creates the loop. Obviously, without this request, the request will never end.
I need to return the same data as without a loop.
DROP TABLE IF EXISTS edges;
CREATE TABLE edges(
src integer,
dst integer,
data integer
);
INSERT INTO edges VALUES (1, 2, 1);
INSERT INTO edges VALUES (2, 3, 1);
INSERT INTO edges VALUES (1, 4, 1);
INSERT INTO edges VALUES (4, 5, 1);
INSERT INTO edges VALUES (5, 2, 1);
INSERT INTO edges VALUES (1, 4, 2);
INSERT INTO edges VALUES (4, 5, 2);
INSERT INTO edges VALUES (4, 6, 2);
WITH RECURSIVE paths AS (
SELECT DISTINCT
src as node
, data as data
, 0 as depth
, src::text as path
FROM edges
WHERE
src = 1
UNION ALL
SELECT DISTINCT
edges.dst
, edges.data
, depth + 1
, paths.path || '->' || edges.dst::text
FROM paths
JOIN edges ON edges.src = paths.node AND edges.data = paths.data
)
SELECT * FROM paths;
Return:
node | data | depth | path
------+------+-------+---------------
1 | 1 | 0 | 1
1 | 2 | 0 | 1
2 | 1 | 1 | 1->2
4 | 1 | 1 | 1->4
4 | 2 | 1 | 1->4
3 | 1 | 2 | 1->2->3
5 | 2 | 2 | 1->4->5
6 | 2 | 2 | 1->4->6
5 | 1 | 2 | 1->4->5
2 | 1 | 3 | 1->4->5->2
3 | 1 | 4 | 1->4->5->2->3
(11 rows)