Re: Recursive Parent-Child Function Bottom Up

[Rob Sargent <robjsargent@xxxxxxxxx>]

On 7/26/21 9:55 AM, Alban Hertroys wrote:

> > On 26 Jul 2021, at 17:52, Alban Hertroys <haramrae@xxxxxxxxx> 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.