Re: Recursive Parent-Child Function Bottom Up

On 7/26/21 9:55 AM, Alban Hertroys wrote:
>> On 26 Jul 2021, at 17:52, Alban Hertroys <haramrae@gmail.com> wrote:
>> 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
>> ;
> Almost, the null::text in the initial select should of course be '’ in your case, and a unicode quote slipped into
thelast string of that case statement.
 
>
> Alban Hertroys
> --
> If you can't see the forest for the trees,
> cut the trees and you'll find there is no forest.
>
>
>
well actually I find this closer to the target.

with recursive kids (founder, firstoff, descendant )
as (select parent as founder, id as firstoff, id::text
      from tree
      where parent = 0
union all
select k.firstoff, t.id, t.id::text
from kids k join tree t on k.firstoff = t.parent
)
select founder, array_agg(descendant) as descendants
from kids group by founder
order by founder;
  founder |           descendants
---------+-------------------------------
        0 | {273}
      273 | {274,275,277}
      275 | {280}
      277 | {278}
      280 | {281}
      281 | {282,290}
      282 | {283,284,285,286,287,288,289}
      290 | {291,292,293,294,295}
(8 rows)