Cum funcționează CTE-urile și interogările recursive în PostgreSQL?

Question

Accepted Answer

Un **CTE** (Common Table Expression), definit cu **`WITH`**, este un set de rezultate temporar numit care face ca interogările complexe să fie ușor de citit. PostgreSQL suportă complet CTE-urile, inclusiv CTE-urile **recursive** (`WITH RECURSIVE`) pentru traversarea datelor ierarhice/de tip arbore.

## CTE-uri de bază — construirea interogărilor ușor de citit

```sql
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
order_counts AS (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT u.name, oc.cnt
FROM active_users u
JOIN order_counts oc ON u.id = oc.user_id;   -- compose named steps cleanly
```

CTE-urile descompun o interogare complexă în pași numiți și ușor de citit (înlănțuiți, fiecare referind la cei anteriori) — mult mai clar decât subinterogări imbricate.

## CTE-uri recursive — traversarea ierarhiilor

```sql
-- find all subordinates under employee 1 (an org-chart hierarchy)
WITH RECURSIVE subordinates AS (
  -- BASE CASE: the starting point
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  -- RECURSIVE STEP: find employees whose manager is in the result so far
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;          -- the entire tree under employee 1
```

Un **CTE recursiv** are un caz de bază (rândurile de start) și un pas recursiv (`UNION ALL` care se conectează înapoi la CTE-ul însuși), iterând până când nu mai apar rânduri noi — perfect pentru **arbori și ierarhii** (organigrame, arbori de categorii, structuri de materiale, căi în grafuri).

## O notă despre materializarea CTE (specifică Postgres)

```sql
-- Postgres 12+ may INLINE CTEs (optimize them) by default; force behavior if needed:
WITH x AS MATERIALIZED (...)      -- force materialization (compute once, store)
WITH x AS NOT MATERIALIZED (...)  -- force inlining (optimize into the main query)
```

Înainte de Postgres 12, CTE-urile erau întotdeauna un "gard de optimizare" (materializate); acum pot fi inline pentru performanță mai bună — `MATERIALIZED`/`NOT MATERIALIZED` controlează acest lucru.

## De ce este important

CTE-urile și interogările recursive sunt caracteristici valoroase ale PostgreSQL pentru **lizibilitate și date ierarhice**, deci înțelegerea lor este utilă pentru scrierea interogărilor complexe ușor de întreținut și pentru gestionarea datelor structurate ca arbore.

Beneficiul zilnic este **lizibilitatea**: interogările complexe cu mai multe niveluri de subinterogări devin dificil de întreținut, în timp ce CTE-urile vă permit să le descompuneți în pași logici numiți și secvențiali (ușor de citit de sus în jos), îmbunătățind dramatic claritatea — făcând CTE-urile un instrument important pentru scrierea SQL-ului ușor de întreținut.

Capacitatea de remarcat este **CTE-urile recursive** (`WITH RECURSIVE`), care permit interogarea **datelor ierarhice și structurate ca arbore** (organigrame, arbori de categorii, structuri de materiale, comentarii înlănțuite, traversare de grafuri) — o clasă de interogări altfel foarte dificile în SQL, făcând CTE-urile recursive soluția standard pentru traversarea ierarhică.

Înțelegerea structurii CTE recursive (caz de bază + pas recursiv care se conectează înapoi la sine) este cunoaștere cheie pentru această necesitate frecventă.

Un detaliu util specific Postgres este comportamentul **materializării CTE** (Postgres 12+ poate inline CTE-urile pentru performanță mai bună, controlabil cu `MATERIALIZED`/`NOT MATERIALIZED`) — relevant pentru optimizare.

Deoarece interogările complexe sunt frecvente (și lizibilitatea contează pentru mentenanță), și deoarece datele ierarhice (pe care CTE-urile recursive le gestionează exceptând bine) apar frecvent (structuri organizaționale, categorii imbricate), înțelegerea CTE-urilor și a interogărilor recursive este cunoaștere PostgreSQL valoroasă și practic relevantă care îmbunătățește mentenabilitatea interogărilor și deblochează interogări ierarhice — o capacitate frecvent relevantă pentru SQL-ul curat și sofisticat și un subiect frecvent, în special forma recursivă pentru nevoia recurentă de a interoga date structurate ca arbore.