CTE recursive stop condition for loops

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 (3, 2, 1);  -- This entry creates a loop
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 (
  -- For simplicity assume node 1 is the start
  -- we'll have two starting nodes for data = 1 and 2
  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
    -- AND eliminate loops?
)

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)
+3
source share
1 answer
WITH RECURSIVE paths AS (
    -- For simplicity assume node 1 is the start
    -- we'll have two starting nodes for data = 1 and 2
    SELECT DISTINCT
        src           as node
        , data        as data
        , 0           as depth
        , src::text   as path
        , ''          as edgeAdded   
    FROM edges
    WHERE
        src = 1

    UNION ALL

    SELECT DISTINCT
        edges.dst
        , edges.data
        , depth + 1
        , paths.path || '->' || edges.dst::text
        , edges.src::text || '->' || edges.dst::text
    FROM paths
    JOIN edges ON edges.src = paths.node AND edges.data = paths.data
    AND NOT paths.path LIKE '%' || edges.dst::text || '%' 
        -- AND eliminate loops?
)
SELECT * FROM paths;

AND NOT paths.path LIKE '%' || edges.dst::text || '%' , .
http://www.sqlfiddle.com/#!12/086ee/1

+1

All Articles