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(at)gmail(dot)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 the last 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.
>
>
>
this might be what you want?
with recursive fulltree (id, parent, children_ids) as (
    select id, parent, id::text as decsendants
      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 as descendants
      from fulltree f
      join tree t on f.parent = t.id
      where f.parent != 0
)
select * from fulltree order by parent
;

I do think it breaks when there is more than one zero parent.