sql - Joining parent rows to their children -

i have table consisting of parent children mappings.

e.g

my_table -----+------+---------   id | name | child_id -----+------+---------    |  a1  | b   b  |  b1  | c   b  |  b2  | c   b  |  b3  |   c  |  c1  | d   d  |  d1  |   d  |  d2  | b 

i 'join' them produce output: (rows marked '<--' indicate row should not exist because of stated reason)

desired_table -----+--------+----------+-------------   id |  name  | child_id | visited_ids -----+--------+----------+-------------    | a1, b1 |    c     | {'a', 'b'}    | a1, b2 |    c     | {'a', 'b'}    | a1, b3 |        | {'a', 'b'}  <-- 'a' visited   b  | b1, c1 |    d     | {'b', 'c'}   b  | b2, c1 |    d     | {'b', 'c'}   b  | b3, a1 |    b     | {'b', 'a'}  <-- 'b' visited   c  | c1, d1 |        | {'c', 'd'}   c  | c1, d2 |    b     | {'c', 'd'}   d  | d1, a1 |    b     | {'d', 'a'}   d  | d2, b1 |    c     | {'d', 'b'}   d  | d2, b2 |    c     | {'d', 'b'}   d  | d2, b3 |        | {'d', 'b'} 

the rows in new table repeatedly 'join' my_table again produce output. (rows marked '<--' indicate row should not exist because of stated reason)

desired_table -----+------------+----------+----------------   id |    name    | child_id |   visited_ids -----+------------+----------+----------------    | a1, b1, c1 |    d     | {'a', 'b', 'c'}    | a1, b2, c1 |    d     | {'a', 'b', 'c'}   b  | b1, c1, d1 |        | {'b', 'c', 'd'}   b  | b1, c1, d2 |    b     | {'b', 'c', 'd'}  <-- 'b' visited   b  | b2, c1, d1 |        | {'b', 'c', 'd'}   b  | b2, c1, d2 |    b     | {'b', 'c', 'd'}  <-- 'b' visited   c  | c1, d1, d1 |        | {'c', 'd', 'd'}  <-- 'd' visited   c  | c1, d1, d2 |    b     | {'c', 'd', 'd'}  <-- 'd' visited   c  | c1, d2, b1 |    c     | {'c', 'd', 'b'}  <-- 'c' visited   c  | c1, d2, b2 |    c     | {'c', 'd', 'b'}  <-- 'c' visited   c  | c1, d2, b3 |        | {'c', 'd', 'b'}   d  | d1, a1, b1 |    c     | {'d', 'a', 'b'}   d  | d1, a1, b2 |    c     | {'d', 'a', 'b'}   d  | d1, a1, b3 |        | {'d', 'a', 'b'}   d  | d2, b1, c1 |    d     | {'d', 'b', 'c'}  <-- 'd' visited   d  | d2, b2, c1 |    d     | {'d', 'b', 'c'}  <-- 'd' visited   d  | d2, b3, a1 |    b     | {'d', 'b', 'a'} 

... on , forth. rows can still 'joined' joined until have no more children. rows can no longer joined remain is.

one problem of example is, have endless loop there (a,a1,b) -> (b,b3,a) -> (a,a1,b) bit tricky detect.

but (and peufeu's link) should started:

with recursive hierarchy (id, names, child_id, path) (    select id, array[name], child_id, array[id] path    mapping    id = 'a'     union     select c.id, p.names||c.name, c.child_id, p.path||c.id    mapping c    join hierarchy p on p.child_id = c.id , not (p.path @> (p.path||c.id)) ) select * hierarchy order 1 

it not create "visited_ids" column want though