Пример Common Table Expressions (CTE) в PostgreSQL

Общие табличные выражения - это конструкции начинающиеся с WITH

Есть таблица categories с полями id, name, parent_id (null если нет родительской категории)

WITH RECURSIVE category_hierarchy AS (

-- Базовый случай: выбор всех категорий без родителей
SELECT
id,
name as parent,
name,
ARRAY[id]::BIGINT[] AS ids, -- массив идентификаторов категорий
ARRAY[]::VARCHAR[] AS names, -- массив названий категорий
1 AS level
FROM categories
WHERE parent_id IS NULL

UNION ALL
-- Рекурсивный случай: выбор дочерних категорий
SELECT
c.id,
ch.parent,
c.name,
ch.ids || c.id, -- добавляем текущий id к массиву
ch.names || c.name, -- добавляем текущее имя к массиву
ch.level + 1
FROM categories c
JOIN category_hierarchy ch ON c.parent_id = ch.id
)
SELECT
id,
parent,
name,
ids,
names,
level
FROM category_hierarchy;

А тут разместим все категории по отдельным столбцам, жёстко прописав максимальный уровень вложенности

WITH RECURSIVE category_hierarchy AS (
SELECT
id,
name,
parent_id,
1 AS level,
ARRAY[id] AS path
FROM categories
WHERE parent_id IS NULL

UNION ALL

SELECT
c.id,
c.name,
c.parent_id,
ch.level + 1 AS level,
ch.path || c.id
FROM categories c
JOIN category_hierarchy ch ON c.parent_id = ch.id
WHERE ch.level < 3
)
SELECT
ch1.id AS root_category_id,
ch1.name AS root_category_name,
ch2.id AS level_2_category_id,
ch2.name AS level_2_category_name,
ch3.id AS level_3_category_id,
ch3.name AS level_3_category_name
FROM category_hierarchy ch1
LEFT JOIN category_hierarchy ch2 ON ch2.parent_id = ch1.id AND ch2.level = 2
LEFT JOIN category_hierarchy ch3 ON ch3.parent_id = ch2.id AND ch3.level = 3
WHERE ch1.level = 1