On 7/26/21 9:55 AM, Alban Hertroys wrote:
On 26 Jul 2021, at 17:52, Alban Hertroys <haram...@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 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.
