Re: Recursive Parent-Child Function Bottom Up

> On 26 Jul 2021, at 17:19, Avi Weinberg <AviW(at)gilat(dot)com> wrote:
>
> Hi,
>
> I would like to populate the children_ids column with all the ids of the children recursively (+ grandchildren etc.)
> If I do it top-bottom I will end up doing extra work since there is no need to go all levels down if I can just compute my IMMEDIATE children "children_ids" and just concatenate all their lists.

(…)

> create table tree(id int primary key, parent int, children_ids text);
> insert into tree (id, parent) values
> (273, 0),
> (274, 273),
> (275, 273),
> (277, 273),
> (278, 277),
> (280, 275),
> (281, 280),
> (282, 281),
> (283, 282),
> (284, 282),
> (285, 282),
> (286, 282),
> (287, 282),
> (288, 282),
> (289, 282),
> (290, 281),
> (291, 290),
> (292, 290),
> (293, 290),
> (294, 290),
> (295, 290);

First you need to figure out what your starting set of nodes is, and since you’re going to go bottom-up, those are your leaf nodes. Without any indicators for that though, you’ll have to determine that from a sub-query.

Something like this:

with recursive foo (id, parent, children_ids) as (
select id, parent, null::text
from tree t
where not exists (
select 1 from tree c where c.parent = t.id
)
union all
select t.id, t.parent
, f.id || case f.children_ids when '' then '' else ',’ end || f.children_ids
from foo f
join tree t on f.parent = t.id
where f.parent <> 0
;

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.