create table personnel (id int not null primary key, parent_id int, name text not null);
create index personnel_ix_parent on personnel (parent_id);
insert into personnel (id, parent_id, name)
values
(1, null, 'Гендир'),
(2, 1, 'Начальник отдела 1'),
(3, 1, 'Начальник отдела 2'),
(4, 2, 'Начальник сектора 1'),
(5, 2, 'Начальник сектора 2'),
(6, 3, 'Начальник сектора 3'),
(7, 3, 'Начальник сектора 4'),
(8, 4, 'Сотрудник 1'),
(9, 5, 'Сотрудник 2'),
(10, 5, 'Сотрудник 3'),
(11, 7, 'Сотрудник 4'),
(12, 7, 'Сотрудник 5'),
(13, 1, 'Секретарь');
with recursive cte as
(
select id, name, 0 as level
from personnel
where parent_id is null
UNION ALL
select p.id, p.name, c.level+1 level
from personnel p
inner join cte c
on p.parent_id=c.id
) search depth first by id set path
select repeat(' ', level) || name tree
from cte
order by path
;
WITH RECURSIVE per_with_level AS (
SELECT id, 0 parent_id, name, 0 AS lvl
FROM personnel
WHERE parent_id IS NULL
UNION ALL
SELECT child.id, child.parent_id, child.name, parent.lvl + 1 lvl
FROM personnel child
JOIN per_with_level parent ON parent.id = child.parent_id
)
,
maxlvl AS (
SELECT max(lvl) maxlvl FROM per_with_level
),
c_tree AS (
SELECT per_with_level.*,
NULL::JSONB children
FROM per_with_level, maxlvl
WHERE lvl = maxlvl
UNION
(
SELECT (branch_parent).*,
jsonb_agg(branch_child)
FROM (
SELECT branch_parent,
to_jsonb(branch_child) - 'lvl' - 'parent_id' - 'id' AS branch_child
FROM per_with_level branch_parent
JOIN c_tree branch_child ON branch_child.parent_id = branch_parent.id
) branch
GROUP BY branch.branch_parent
UNION
SELECT c.*,
NULL::JSONB
FROM per_with_level c
WHERE NOT EXISTS (SELECT 1
FROM per_with_level hypothetical_child
WHERE hypothetical_child.parent_id = c.id)
)
)
SELECT jsonb_pretty(
array_to_json(
array_agg(
row_to_json(c_tree)::JSONB - 'lvl' - 'parent_id' - 'id'
)
)::JSONB
) AS tree
FROM c_tree
WHERE lvl=0;
Telegram: DB developers channel