Как написать рекурсивный запрос?

Question

[Дмитрий Быстров]

Есть иерархия категорий:


Нужен запрос, который позволит получить таблицу вида:


где childs - id дочерних категорий, включая родительскую категорию.

Структура таблицы

Структура таблицы

create table landcategory (   
  id serial,
  code varchar(20),
  name varchar(400),  
  parent_id bigint
);

insert into landcategory(code, name, parent_id) values('1', 'Земли населенных пунктов', null);
insert into landcategory(code, name, parent_id) values('1-1', 'Подкатегория 1-1', 1);
insert into landcategory(code, name, parent_id) values('1-2', 'Подкатегория 1-2', 1);
insert into landcategory(code, name, parent_id) values('1-2-1', 'Подкатегория 1-2', 3);

Answer 1

[galaxy]

Ну что-то типа такого (выводит для каждой строки с parent_id = null):

with recursive cat as (
  select id, id top from landcategory where parent_id is null
  union
  select lc.id, top from landcategory lc join cat on (cat.id = lc.parent_id)
)
select lc.*, childs from landcategory lc join (
  select top, array_agg(id) childs from cat
   group by 1
) t on (t.top = lc.id)

https://www.db-fiddle.com/f/9gQNjVwLn3W11pnCcr8NEs/0