Kako delujejo CTE-ji in rekurzivne poizvedbe v PostgreSQL-u?

Question

Accepted Answer

**CTE** (Common Table Expression), definiran z **`WITH`**, je poimenovani začasni rezultatski niz, ki naredi kompleksne poizvedbe berljive. PostgreSQL v celoti podpira CTE-je, vključno z **rekurzivnimi** CTE-ji (`WITH RECURSIVE`) za prehajanje po hierarhičnih/drevesnih podatkih.

## Osnovni CTE-ji — berljivo sestavljanje poizvedb

```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-ji razdelijo kompleksno poizvedbo na poimenovane, berljive korake (povezane, vsak se sklicuje na prejšnje) — veliko jasnejše kot gnezdeni podpoizvedki.

## Rekurzivni CTE-ji — prehajanje po hierarhijah

```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
```

**Rekurzivni CTE** ima osnovni primer (začetne vrstice) in rekurzivni korak (`UNION ALL`, ki se ponovno povezuje na sam CTE), iterira, dokler ni novih vrstic — idealen za **drevesa in hierarhije** (organizacijski diagrami, drevesa kategorij, seznami komponent, poti v grafu).

## Opomba o materializaciji CTE-jev (PostgreSQL-specifično)

```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)
```

Pred PostgreSQL 12 so bili CTE-ji vedno "optimization fence" (materializirani); zdaj se lahko vstavijo za boljšo zmogljivost — `MATERIALIZED`/`NOT MATERIALIZED` to nadzira.

## Zakaj je to pomembno

CTE-ji in rekurzivne poizvedbe so vredne PostgreSQL-ove funkcionalnosti za **berljivost in hierarhične podatke**, zato je razumevanje le-teh koristno za pisanje vzdrževalnih kompleksnih poizvedb in obdelavo podatkov v drevesni strukturi.

Vsakodnevna korist je **berljivost**: kompleksne poizvedbe z več nivojih podpoizvedb postanejo težko vzdrževane, medtem ko CTE-ji omogočajo razdelitev na poimenovane, zaporedne logične korake (branje od zgoraj navzdol), kar drastično izboljša jasnost — CTE-je naredijo za pomembno orodje za pisanje vzdrževalnega SQL-a.

Izstopna zmogljivost so **rekurzivni CTE-ji** (`WITH RECURSIVE`), ki omogočajo poizvedovanje **hierarhičnih in drevesnih podatkov** (organizacijski diagrami, drevesa kategorij, seznami komponent, niti komentarjev, prehajanje po grafu) — razred poizvedb, ki so drugače v SQL-u zelo težavni, zato so rekurzivni CTE-ji standardna rešitev za hierarhično prehajanje.

Razumevanje strukture rekurzivnega CTE-ja (osnovni primer + rekurzivni korak, ki se povezuje sam nase) je ključno znanje za to pogosto potrebo.

Koristna PostgreSQL-specifična podrobnost je **obnašanje materializacije CTE-jev** (PostgreSQL 12+ lahko vstavi CTE-je za boljšo zmogljivost, nadzirljivo z `MATERIALIZED`/`NOT MATERIALIZED`) — relevantna za optimizacijo.

Ker so kompleksne poizvedbe pogoste (in berljivost je pomembna za vzdrževanje) in ker se hierarhični podatki (ki jih rekurzivni CTE-ji edinstven dobro obdelajo) pogosto pojavljajo (org strukture, ugnezdene kategorije), je razumevanje CTE-jev in rekurzivnih poizvedb vredno, praktično relevantno PostgreSQL-ovo znanje, ki izboljša vzdrževljivost poizvedb in omogoči hierarhično poizvedovanje — pogosto relevantna zmogljivost za čist, sofisticiran SQL in pogosta tema, posebej rekurzivna oblika za ponavljajočo se potrebo po poizvedovanju podatkov v drevesni strukturi.