Re: Recursive Parent-Child Function Bottom Up

Поиск
Список
Период
Сортировка
От Rob Sargent
Тема Re: Recursive Parent-Child Function Bottom Up
Дата
Msg-id 760c57be-5e75-b400-32cb-54b5240d9504@gmail.com
обсуждение исходный текст
Ответ на Re: Recursive Parent-Child Function Bottom Up  (Alban Hertroys <haramrae@gmail.com>)
Список pgsql-general
Дерево обсуждения
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 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.


		
	


	

					

					





	

	            	В списке pgsql-general по дате отправления: