Kako funkcioniraju CTE-ovi i rekurzivni upiti u PostgreSQL-u?

Question

Accepted Answer

A **CTE** (Common Table Expression), definiran sa **`WITH`**, je poimenovani privremeni skup rezultata koji čini kompleksne upite čitljivijima. PostgreSQL u potpunosti podržava CTE-ove, uključujući **rekurzivne** CTE-ove (`WITH RECURSIVE`) za prelaženje hijerarhijskih/stablastih podataka.

## Osnovni CTE-ovi — čitljiva gradnja upita

```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-ovi razbijaju kompleksan upit u poimenovane, čitljive korake (povezane, svaki referencira prethodne) — daleko jasniji od ugniježđenih podupita.

## Rekurzivni CTE-ovi — prelaženje hijerarhija

```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 bazni slučaj (početne redove) i rekurzivni korak (`UNION ALL` koji se ponovno spaja na sam CTE), iterira dok nema novih redova — savršen za **stabla i hijerarhije** (organizacijske sheme, stabla kategorija, sastav proizvoda, putanje grafa).

## Napomena o materijalizaciji CTE-a (specifično za 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)
```

Priije Postgres 12, CTE-ovi su bili uvijek "optimization fence" (materijalizirani); sada mogu biti umetnuti za bolju performansu — `MATERIALIZED`/`NOT MATERIALIZED` kontrolira to.

## Zašto je to važno

CTE-ovi i rekurzivni upiti su vrijedne PostgreSQL značajke za **čitljivost i hijerarhijske podatke**, pa je razumijevanje njih korisno za pisanje održivih kompleksnih upita i rukovanje stablastim podacima.

Svakodnevna prednost je **čitljivost**: kompleksni upiti s više razina ugniježđenih podupita postaju teški za održavanje, dok CTE-ovi omogućuju vam da ih razložite u poimenovane, sekvencijalne logičke korake (čitanje odozgo prema dolje), dramatično poboljšavajući jasnoću — čineći CTE-ove važnim alatom za pisanje održivih SQL upita.

Istaknutu sposobnost predstavljaju **rekurzivni CTE-ovi** (`WITH RECURSIVE`), koji omogućuju postavljanje upita **hijerarhijskim i stablastim podacima** (organizacijske sheme, stabla kategorija, sastav proizvoda, niti komentara, prelaženje grafa) — vrsta upita koja je inače vrlo teška u SQL-u, čineći rekurzivne CTE-ove standardnim rješenjem za hijerarhijsko prelaženje.

Razumijevanje strukture rekurzivnog CTE-a (bazni slučaj + rekurzivni korak koji se ponovno spaja na sebe) je ključno znanje za ovu čestu potrebu.

Korisna PostgreSQL-specifična detalj je **ponašanje materijalizacije CTE-a** (Postgres 12+ može umetnuti CTE-ove za bolju performansu, kontrolabilno s `MATERIALIZED`/`NOT MATERIALIZED`) — relevantno za optimizaciju.

Budući da su kompleksni upiti česti (a čitljivost je važna za održavanje), i budući da se hijerarhijski podaci (koje rekurzivni CTE-ovi jedinstveno dobro obrađuju) često pojavljuju (org strukture, ugniježđene kategorije), razumijevanje CTE-ova i rekurzivnih upita je vrijedna, praktično relevantna PostgreSQL znanja koja poboljšava održivost upita i otključava hijerarhijsku upite — često relevantna sposobnost za čiste, sofisticirane SQL upite i čest topic, posebno rekurzivni oblik za ponavljajuću potrebu za upitima stablastim podacima.