By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE mytable (
id int,
name varchar(20),
traversal_ids int[]
);
insert into mytable values
( 1, 'container1', '{1}' ),
( 2, 'container2', '{2}' ),
( 3, 'subcontainer1', '{2,3}' ),
( 4, 'container4', '{4}' ),
( 5, 'subsub', '{2 ,3 ,5}');
CREATE TABLE
INSERT 0 5
with cte as (
select t2.id, t2.name, t1.name as sub_name, t1.id as sub_id
from mytable t1
inner join mytable t2 on t1.id = ANY(t2.traversal_ids)
)
select id, name, '/' || string_agg(sub_name, '/' order by sub_id) as path
from cte
group by id, name
| id | name | path |
|---|---|---|
| 1 | container1 | /container1 |
| 2 | container2 | /container2 |
| 3 | subcontainer1 | /container2/subcontainer1 |
| 4 | container4 | /container4 |
| 5 | subsub | /container2/subcontainer1/subsub |
SELECT 5