WITH RECURSIVE (ierarhii și arbori) în PostgreSQL: sintaxă SQL, opțiuni și exemple

Ce face `WITH RECURSIVE (ierarhii și arbori)`

WITH RECURSIVE extinde CTE-urile obișnuite permițând unei expresii să se refere la ea însăși. Este instrumentul standard în PostgreSQL pentru interogarea datelor ierarhice (categorii cu parent_id, organigrame de angajați/manageri, liste de materiale BOM sau grafuri de dependențe).

Un CTE recursiv este format întotdeauna din două părți unite prin UNION ALL (sau UNION): termenul ancoră (nerecursiv, care selectează rădăcina) și termenul recursiv (care face JOIN între tabelul de bază și rezultatul iterat anterior, până când nu mai sunt returnate rânduri noi).

Clauze, operatori și argumente

Clauză / Operator	Descriere
`WITH RECURSIVE nume_cte AS`	Declară faptul că cel puțin un CTE din blocul `WITH` se auto-referențiază.
`Termenul ancoră (înainte de UNION ALL)`	Interogarea inițială care stabilește rândurile de pornire (ex: `WHERE parent_id IS NULL`).
`UNION ALL / UNION`	Combină rândurile fiecărei iterații; `UNION` elimină duplicatele și previne buclele simple.
`Termenul recursiv (după UNION ALL)`	Se leagă de `nume_cte` pentru a găsi copiii (sau părinții) rândurilor din pasul anterior.
`CYCLE col SET is_cycle USING path`	Clauză PostgreSQL 14+ care detectează automat buclele din grafuri fără a scrie manual vectori de vizitare.

Exemple

Exemplul 1: Parcurgerea unui arbore de categorii și construirea căii complete (breadcrumbs)

Pornim de la categoriile rădăcină (parinte_id IS NULL) și coborâm în arbore, calculând adâncimea și calea completă.

WITH RECURSIVE ierarhie_categorii AS (
    -- 1. Termenul ancoră: categoriile principale
    SELECT id, nume, parinte_id, 1 AS nivel, nume::text AS cale
    FROM categorii
    WHERE parinte_id IS NULL

    UNION ALL

    -- 2. Termenul recursiv: subcategoriile
    SELECT c.id, c.nume, c.parinte_id, ic.nivel + 1,
           ic.cale || ' > ' || c.nume
    FROM categorii c
    INNER JOIN ierarhie_categorii ic ON c.parinte_id = ic.id
)
SELECT id, nivel, cale
FROM ierarhie_categorii
ORDER BY cale;

Rezultat interogare

 id | nivel |                   cale                    
----+-------+-------------------------------------------
  1 |     1 | Calculatoare
  2 |     2 | Calculatoare > Laptopuri
  5 |     3 | Calculatoare > Laptopuri > Ultrabook
  3 |     2 | Calculatoare > Componente PC
(4 rows)

Exemplul 2: Aflarea tuturor subordonaților direcți și indirecți ai unui manager

Găsim întreaga structură de raportare care pornește de la directorul tehnic (id = 1), limitând adâncimea la 5 niveluri.

WITH RECURSIVE organigrama AS (
    SELECT id, nume, functie, manager_id, 0 AS distanta
    FROM angajati
    WHERE id = 1

    UNION ALL

    SELECT a.id, a.nume, a.functie, a.manager_id, o.distanta + 1
    FROM angajati a
    INNER JOIN organigrama o ON a.manager_id = o.id
    WHERE o.distanta < 5
)
SELECT id, REPEAT('  ', distanta) || nume AS structura, functie
FROM organigrama;