Find connected edges in the graph

Question

I have a table with two columns. Each row in this table is the edge in the graph:

v  |  w
-------
a     a
a     b
a     c
b     d
b     b
c     c
e     f
g     e
h     h

I need to collect all edges that are connected together:

result 
-------
a,b,c,d
e,f,g
h

What is the best way to do it? Create a cursor and loop over the table and collect all edges to the temporary table ? Or may be there are functions to make this tasks more convenient way ?

Updated:

create table tt (
    v text,
    w text    
);

insert into tt values('a', 'a');
insert into tt values('a', 'b');
insert into tt values('a', 'c');
insert into tt values('d', 'd');
insert into tt values('b', 'b');
insert into tt values('c', 'c');
insert into tt values('e', 'f');
insert into tt values('g', 'e') ;
insert into tt values('h', 'h') ;

@a_horse_with_no_name There is no starting point. It us undirected graph. — ceth, Oct 17 '19 at 09:04

JNevill · Accepted Answer · 2019-10-16T20:14:41.137

Your best bet would be to use a recursive CTE here:

WITH RECURSIVE reccte AS
(
        SELECT v as parent, w as child
        FROM mytable
        UNION ALL
        SELECT reccte.parent, mytable.w
        FROM reccte INNER JOIN mytable ON reccte.child = mytable.v and mytable.v <> mytable.w
)
SELECT STRING_AGG(DISTINCT child, ',')
FROM (SELECT parent, child FROM reccte UNION SELECT parent, parent FROM reccte) r1
WHERE parent NOT IN (SELECT child FROM reccte r2 WHERE r2.parent <> r1.parent)
GROUP BY parent

Which will spit out:

string_agg 
------------
 a,b,c,d
 e,f,g
 h

That recursive CTE works in two parts. The top bit before the UNION is the starting point for the recursion. We just grab all the records. The second bit after the UNION is the recursive portion where it refers back to itself. Basically it's linking node to node (parent to child) and adding to the result set until it exhausts itself of records.

Lastly there is the SELECT from the recursive CTE where we get all combinations using that union and then drop any records where the parent is another parents children. Then we just STRING_AGG() up the children for each parent and spit out the results.

I have understood the idea, but this query is running endless time ) — ceth, Oct 17 '19 at 08:45
There's the potential that it's cycling endlessly. Are there relationships that go in circles, like a,b,c,a,b,c,a,b,c... ? Might have to add to the second WHERE clause to avoid cycling. — JNevill, Oct 17 '19 at 11:26

Find connected edges in the graph

1 Answers1

Linked