Find all associations as a list within postgres adjacency table

Question

Given the following:

CREATE TABLE example (
    example_name varchar(50),
    example_parent varchar(50)
);

insert into example (example_name, example_parent)
values 
('e1', NULL), 
('e2', 'e1'),
('e3', NULL), 
('e4', NULL),
('e5', NULL),
('e6', 'e5'),
('e7', 'e6'),
('e8', 'e7'),
('e9', NULL)
;

Which looks as:

| example_name   | example_parent   |
|:---------------|:-----------------|
| e1             |                  |
| e2             | e1               |
| e3             |                  |
| e4             |                  |
| e5             |                  |
| e6             | e5               |
| e7             | e6               |
| e8             | e7               |
| e9             |                  |

I would like to be able to find all associated values for a particular example_name.

Some examples of what I'd like to have returned for different example_name's:

given e1:

| example_name   | associations   |
|:---------------|:---------------|
| e1             | e2             |

given e8:

| example_name   | associations   |
|:---------------|:---------------|
| e8             | e7, e6, e5     |

given e9:

| example_name   | associations   |
|:---------------|:---------------|
| e9             |                |

Belayer · Accepted Answer · 2022-11-05 19:35:11Z

1

Use a recursive CTE to get the list for the given value(s). Then use string_agg() function in the main query. (see demo)

with recursive hier_examples(name,parent, level) as
     ( select example_name, example_parent, 0
         from example 
        where example_name in (<1 or more comma separated values here>)
      union all 
      select  he.name, ex.example_parent, level+1
        from hier_examples he
        join example       ex 
          on (ex.example_name = he.parent)
    ) --select * from hier_examples;
select name, string_agg(parent, ', ' order by level) associations
  from hier_examples
 group by name
 order by name;

answered Nov 5, 2022 at 19:35

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Find all associations as a list within postgres adjacency table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related