Пошук уроків, статей та іншого контенту
Обробляйте ієрархічні дані та послідовності за допомогою рекурсивних Common Table Expressions.
Рекурсивний CTE — це Common Table Expression, який посилається сам на себе. Він дає змогу виконувати ітеративні запити над даними, де кожен рядок може породжувати наступний набір рядків.
Рекурсивні CTE особливо корисні для:
ієрархій працівників і керівників;
категорій товарів;
файлових і каталогових структур;
графів залежностей;
побудови послідовностей;
пошуку предків або нащадків вузла.
Загальна форма має такий вигляд:
WITH RECURSIVE cte_name AS (
-- Початковий набір рядків
SELECT ...
UNION ALL
-- Наступний крок рекурсії
SELECT ...
FROM ...
JOIN cte_name ON ...
WHERE ...
)
SELECT *
FROM cte_name;Рекурсивний CTE складається з двох частин:
Нерекурсивна частина, або anchor query — початкові рядки.
Рекурсивна частина — запит, який використовує результати попередньої ітерації.
PostgreSQL повторює рекурсивну частину, доки вона повертає рядки або доки умова завершення не припинить генерування нових рядків.
Найпростіший приклад — створення чисел від 1 до 10:
WITH RECURSIVE numbers AS (
-- Початкове значення
SELECT 1 AS number
UNION ALL
-- На кожному кроці збільшуємо число на одиницю
SELECT number + 1
FROM numbers
WHERE number < 10
)
SELECT number
FROM numbers
ORDER BY number;Результатом будуть числа від 1 до 10.
Перший крок повертає 1. Потім рекурсивна частина:
отримує 1 і повертає 2;
отримує 2 і повертає 3;
продовжує виконання;
після 10 умова number < 10 стає хибною.
У рекурсивному CTE обов’язково має бути умова завершення. Без неї запит може виконуватися нескінченно або завершитися помилкою через обмеження ресурсів.
Розглянемо таблицю працівників:
CREATE TEMP TABLE employees (
id integer PRIMARY KEY,
name text NOT NULL,
manager_id integer REFERENCES employees(id)
);
INSERT INTO employees (id, name, manager_id)
VALUES
(1, 'Олена', NULL),
(2, 'Андрій', 1),
(3, 'Марія', 1),
(4, 'Ігор', 2),
(5, 'Софія', 2),
(6, 'Дмитро', 3);Зв’язок manager_id вказує на керівника працівника:
Олена
├── Андрій
│ ├── Ігор
│ └── Софія
└── Марія
└── ДмитроЩоб отримати всіх працівників разом із глибиною в ієрархії, почнемо з кореневого працівника, у якого manager_id IS NULL:
WITH RECURSIVE organization AS (
-- Кореневі працівники
SELECT
id,
name,
manager_id,
0 AS depth,
ARRAY[id] AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Пошук безпосередніх підлеглих
SELECT
employee.id,
employee.name,
employee.manager_id,
organization.depth + 1,
organization.path || employee.id
FROM employees AS employee
JOIN organization
ON employee.manager_id = organization.id
)
SELECT
id,
repeat(' ', depth) || name AS employee_name,
depth,
path
FROM organization
ORDER BY path;Приблизний результат:
id | employee_name | depth | path
----+---------------+-------+---------
1 | Олена | 0 | {1}
2 | Андрій | 1 | {1,2}
4 | Ігор | 2 | {1,2,4}
5 | Софія | 2 | {1,2,5}
3 | Марія | 1 | {1,3}
6 | Дмитро | 2 | {1,3,6}Початкова частина:
SELECT
id,
name,
manager_id,
0 AS depth,
ARRAY[id] AS path
FROM employees
WHERE manager_id IS NULLповертає кореневі вузли. Для Олени:
depth дорівнює 0;
path дорівнює {1}.
Рекурсивна частина знаходить працівників, у яких manager_id збігається з id уже знайденого працівника:
JOIN organization
ON employee.manager_id = organization.idДля кожного такого працівника:
глибина збільшується на 1;
його ідентифікатор додається до шляху;
рядок стає джерелом для наступної ітерації.
Масив path використовується не лише для відображення шляху. Сортування за ним дає обхід ієрархії в порядку від батька до нащадків.
Щоб отримати всіх підлеглих працівника з ідентифікатором 2, початковим рядком буде саме цей працівник:
WITH RECURSIVE subordinates AS (
-- Працівник, від якого починається пошук
SELECT
id,
name,
manager_id,
0 AS depth
FROM employees
WHERE id = 2
UNION ALL
-- Пошук підлеглих на наступному рівні
SELECT
employee.id,
employee.name,
employee.manager_id,
subordinates.depth + 1
FROM employees AS employee
JOIN subordinates
ON employee.manager_id = subordinates.id
)
SELECT
id,
name,
depth
FROM subordinates
ORDER BY depth, id;Результат міститиме самого Андрія, а також Ігоря і Софію.
Якщо початковий працівник не повинен входити до результату, його можна виключити зовнішнім запитом:
WITH RECURSIVE subordinates AS (
SELECT
id,
name,
manager_id,
0 AS depth
FROM employees
WHERE id = 2
UNION ALL
SELECT
employee.id,
employee.name,
employee.manager_id,
subordinates.depth + 1
FROM employees AS employee
JOIN subordinates
ON employee.manager_id = subordinates.id
)
SELECT id, name, depth
FROM subordinates
WHERE depth > 0
ORDER BY depth, id;Рекурсія може рухатися не вниз, а вгору ієрархією. Наприклад, щоб знайти керівників працівника з id = 4:
WITH RECURSIVE managers AS (
-- Починаємо з конкретного працівника
SELECT
id,
name,
manager_id,
0 AS distance
FROM employees
WHERE id = 4
UNION ALL
-- Переходимо до керівника поточного працівника
SELECT
manager.id,
manager.name,
manager.manager_id,
managers.distance + 1
FROM employees AS manager
JOIN managers
ON manager.id = managers.manager_id
)
SELECT
id,
name,
distance
FROM managers
ORDER BY distance;У цьому випадку зв’язок у JOIN має зворотний напрямок:
manager.id = managers.manager_idРезультат:
id | name | distance
----+--------+----------
4 | Ігор | 0
2 | Андрій | 1
1 | Олена | 2Ієрархія зазвичай повинна бути деревом, але помилкові дані можуть утворити цикл:
Працівник A → Працівник B → Працівник C → Працівник AЯкщо рекурсивний запит не перевіряє вже відвідані вузли, він може нескінченно обходити такий цикл.
Для контролю використовується масив path із відвіданими ідентифікаторами:
WITH RECURSIVE organization AS (
SELECT
id,
name,
manager_id,
0 AS depth,
ARRAY[id] AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
employee.id,
employee.name,
employee.manager_id,
organization.depth + 1,
organization.path || employee.id
FROM employees AS employee
JOIN organization
ON employee.manager_id = organization.id
WHERE NOT employee.id = ANY(organization.path)
)
SELECT
id,
name,
depth,
path
FROM organization
ORDER BY path;Умова:
WHERE NOT employee.id = ANY(organization.path)означає: додавати працівника лише тоді, коли його id ще не зустрічався в поточному шляху.
Важливо, що перевірка циклів у поточному прикладі захищає обхід від повторного відвідування вузла. Вона не виправляє самі дані й не робить циклічну структуру коректною ієрархією.
UNION ALL і UNIONУ рекурсивних CTE зазвичай використовують UNION ALL:
WITH RECURSIVE numbers AS (
SELECT 1
UNION ALL
SELECT column1 + 1
FROM numbers
WHERE column1 < 5
)
SELECT *
FROM numbers;UNION ALL зберігає всі рядки та не виконує видалення дублікатів на кожному кроці. Це зазвичай швидше й передбачуваніше.
UNION прибирає дублікати. Іноді це може зупинити повторне генерування однакових рядків, але покладатися лише на це як на захист від циклів не варто:
дублікати можуть відрізнятися іншими колонками;
рекурсія може продовжувати генерувати нові рядки;
логіка запиту стає менш очевидною.
Для контролю циклів краще явно зберігати шлях або інший набір відвіданих вузлів.
Колонки початкової та рекурсивної частин повинні мати сумісні типи.
Наприклад, цей запит може спричинити проблему з типом:
WITH RECURSIVE numbers AS (
SELECT 1::integer AS number
UNION ALL
SELECT number + 1
FROM numbers
WHERE number < 10
)
SELECT *
FROM numbers;У цьому прикладі типи сумісні, оскільки number + 1 також має тип integer.
Якщо початкове значення має вузький тип, а рекурсивне значення може вийти за його межі, потрібно явно привести тип:
WITH RECURSIVE numbers AS (
SELECT 1::bigint AS number
UNION ALL
SELECT number + 1
FROM numbers
WHERE number < 1000000
)
SELECT *
FROM numbers;Те саме стосується текстових колонок, масивів, дат та інших значень. Тип результату визначається першою частиною CTE, тому рекурсивне значення має відповідати йому.
Рекурсивні CTE можуть працювати не лише з цілими числами. Наприклад, можна побудувати послідовність дат:
WITH RECURSIVE calendar AS (
-- Початкова дата
SELECT DATE '2026-09-01' AS day
UNION ALL
-- Переходимо до наступного дня
SELECT day + 1
FROM calendar
WHERE day < DATE '2026-09-07'
)
SELECT day
FROM calendar
ORDER BY day;Ключовими елементами залишаються ті самі:
початкове значення;
перехід до наступного значення;
умова завершення.
Рекурсивний CTE генерує рядки рівнями, але порядок безпосереднього результату не слід вважати гарантованим без ORDER BY.
Для керування порядком зручно будувати шлях:
ARRAY[id] AS pathі на кожному кроці додавати новий ідентифікатор:
organization.path || employee.idПотім результат сортується:
ORDER BY pathТак можна отримати порядок, у якому кожен батьківський вузол іде перед його нащадками.
Для ієрархії з текстовими ідентифікаторами можна використовувати масив текстових значень, але числові ідентифікатори зазвичай дають простіше та передбачуваніше сортування.
Рекурсивний CTE може обробити багато рядків, якщо ієрархія глибока або кожен вузол має багато нащадків.
Варто звернути увагу на такі аспекти:
поле, за яким виконується зв’язок, має бути індексованим;
умова рекурсії повинна звужувати набір рядків;
потрібно обмежувати глибину, якщо структура може бути дуже глибокою;
для захисту від циклів слід зберігати шлях або інший набір відвіданих вузлів;
не варто додавати великі непотрібні колонки до рекурсивного CTE.
Для таблиці employees індекс на manager_id може покращити пошук підлеглих:
CREATE INDEX employees_manager_id_idx
ON employees (manager_id);Обмеження глибини можна додати до рекурсивної частини:
WITH RECURSIVE organization AS (
SELECT
id,
name,
manager_id,
0 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
employee.id,
employee.name,
employee.manager_id,
organization.depth + 1
FROM employees AS employee
JOIN organization
ON employee.manager_id = organization.id
WHERE organization.depth < 10
)
SELECT *
FROM organization;Цей запит обійде не більше ніж 11 рівнів, якщо рахувати кореневий рівень як 0.
Небезпечний варіант:
WITH RECURSIVE numbers AS (
SELECT 1
UNION ALL
SELECT number + 1
FROM numbers
)
SELECT *
FROM numbers;Рекурсія не має точки зупинки, тому запит не завершиться нормально.
Потрібна умова:
WHERE number < 10Для пошуку підлеглих використовується зв’язок:
employee.manager_id = parent.idДля пошуку керівників — зворотний:
manager.id = employee.manager_idЯкщо переплутати напрямок, запит повертатиме предків замість нащадків або не знайде потрібних рядків.
Навіть якщо схема формально описує дерево, помилка в даних може створити цикл. Рекурсивний запит повинен мати захист, якщо цілісність ієрархії не гарантована.
ORDER BYБез явного сортування порядок рядків результату не гарантований. Для ієрархічного виведення слід зберігати шлях і сортувати за ним.
Початкова та рекурсивна частини повинні повертати однакову кількість колонок у сумісних типах. Якщо PostgreSQL не може автоматично узгодити типи, використовуйте явне приведення через ::type.
Якщо кожен вузол породжує багато наступних рядків, кількість результатів може швидко зрости. Обмежуйте глибину, фільтруйте початковий набір і перевіряйте план виконання для великих таблиць.
Рекурсивний CTE оголошується через WITH RECURSIVE.
Він складається з початкової та рекурсивної частин.
Початкова частина визначає перший набір рядків.
Рекурсивна частина використовує результати попередніх ітерацій.
Умова завершення необхідна для запобігання нескінченній рекурсії.
Для обходу ієрархій вниз потрібно знаходити рядки через manager_id.
Для обходу вгору потрібно приєднувати батьківський рядок за його id.
Масив path допомагає будувати порядок обходу та виявляти цикли.
UNION ALL зазвичай є стандартним вибором для рекурсивних запитів.
Типи та кількість колонок у двох частинах CTE повинні бути сумісними.