How to write a recursive query?

Question

PostgreSQL

There is a hierarchy of categories:


We need a query that will allow us to get a table of the form:


where childs is the id of child categories, including the parent category.

Table structure

Table structure

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

Well, something like this (outputs for each row with 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