postgres recursive view returning multple rows per input row

Question

I want a recursive view which reports all children of a given group, one per row.

I've tried the basic examples from postgres documentation, and modified to try and return multiple rows per recursive step, but I keep ending up with infinite loops.

Sample Data

create table groups (
       group_id varchar,
       parent_id varchar
       );

insert into groups (group_id, parent_id) values
       ('A',NULL),
       ('B','A'),
       ('C','B'),
       ('D','C'),
       ('E','A')
       ;

--      A
--    /   \
--   E     B
--          \
--           C
--            \
--             D

select * from groups;

group_id |parent_id 
---------|----------
A        |[NULL]    
B        |A         
C        |B         
D        |C
E        |A

I want a recursive view which reports each child of a given group, one per row.

Intended Output

The vertical white-space is just for clarity.

select * from group_recursive;

group_id | parent_id | all_children_ids
---------|-----------|-----------------
A        | NULL      | A        

B        | A         | B
B        | A         | A

E        | A         | E
E        | A         | A

C        | B         | C
C        | B         | B
C        | B         | A

D        | C         | D
D        | C         | C
D        | C         | B
D        | C         | A

select * from group_recursive where all_children_ids = 'A';
A        | NULL      | A
B        | A         | A
E        | A         | A
C        | B         | A
D        | C         | A

select * from group_recursive where all_children_ids = 'B';
B        | A         | B
C        | B         | B
D        | C         | B

select * from group_recursive where all_children_ids = 'D';
D        | C         | D

I have to assume this is possible as a view, but if necessary I could change to defining a function or something.

Any help is appreciated, thanks!

user_10676716 · Answer

You can get all children of a given group with the following query:

with recursive cte as (
    select group_id, parent_id, group_id root_id, 0 lvl from groups where group_id = ?
    union all
    select g.group_id, g.parent_id, c.root_id, c.lvl + 1
    from groups g
    inner join cte c on c.group_id = g.parent_id
)
select * from cte order by lvl, group_id

The question mark should be replaced with the group whose children you want to exhibit. all_children seems like a counter-intuitive column name, so I renamed it root_id. As a bonus I added a column called lvl that indicates the depth of each group relatively to the root.

On the other hand, if you want to generate all paths up from a given node, as shown in your first resultset, you can traverse the tree upwards:

with recursive cte as (
    select group_id, parent_id, group_id root_id, 0 lvl from groups
    union all
    select g.group_id, g.parent_id, c.root_id, c.lvl + 1
    from groups g
    inner join cte c on c.group_id = g.parent_id
)
select group_id, parent_id, root_id from cte order by group_id, parent_id, lvl

Demo on DB Fiddle

postgres recursive view returning multple rows per input row

1 Answers