Hvordan fungerer CTEer og rekursive spørringer i PostgreSQL?

Question

Accepted Answer

En **CTE** (Common Table Expression), definert med **`WITH`**, er et navngitt midlertidig resultatset som gjør komplekse spørringer lesbare. PostgreSQL støtter fullt ut CTEer, inkludert **rekursive** CTEer (`WITH RECURSIVE`) for traversering av hierarkisk/tre-data.

## Grunnleggende CTEer — lesbar spørringsbygging

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

CTEer bryter ned en kompleks spørring i navngitte, lesbare trinn (kjedede, hver refererer til tidligere) — langt klarere enn nestede underordnede spørringer.

## Rekursive CTEer — traverser hierarkier

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

En **rekursiv CTE** har et grunntilfelle (startradene) og et rekursivt trinn (`UNION ALL` som kobler tilbake til CTEen selv), itererer til ingen nye rader — perfekt for **trær og hierarkier** (organisasjonsdiagrammer, kategoritrær, stykklister, grafésti).

## En merknad om CTE-materialisering (PostgreSQL-spesifikk)

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

Før Postgres 12 ble CTEer alltid "optimiseringsgjerder" (materialisert); nå kan de være inline for bedre ytelse — `MATERIALIZED`/`NOT MATERIALIZED` kontrollerer dette.

## Hvorfor det betyr noe

CTEer og rekursive spørringer er verdifulle PostgreSQL-funksjoner for **lesbarhet og hierarkisk data**, så å forstå dem er nyttig for å skrive vedlikeholdbare komplekse spørringer og håndtere trestrukturert data.

Den daglige fordelen er **lesbarhet**: komplekse spørringer med flere undernivåer av underordnede spørringer blir vanskelig å vedlikeholde, mens CTEer lar deg dekomponere dem til navngitte, sekvensielle logiske trinn (les fra topp til bunn), som dramatisk forbedrer klarhet — som gjør CTEer til et viktig verktøy for å skrive vedlikeholdbar SQL.

Den fremragende evnen er **rekursive CTEer** (`WITH RECURSIVE`), som muliggjør spørring av **hierarkisk og trestrukturert data** (organisasjonskart, kategoritrær, stykklister, tråder med kommentarer, graftraversering) — en klasse spørringer som ellers er svært vanskelig i SQL, som gjør rekursive CTEer til standardløsningen for hierarkisk traversering.

Å forstå strukturen til rekursiv CTE (grunntilfelle + rekursivt trinn som kobler tilbake til seg selv) er nøkkelkunnskap for denne vanlige behovet.

En nyttig PostgreSQL-spesifikk detalj er **CTE-materialiseringsatferd** (Postgres 12+ kan inline CTEer for bedre ytelse, kontrollerbar med `MATERIALIZED`/`NOT MATERIALIZED`) — relevant for optimalisering.

Siden komplekse spørringer er vanlige (og lesbarhet betyr noe for vedlikehold), og siden hierarkisk data (som rekursive CTEer håndterer unikt godt) dukker opp hyppig (organisasjonsstrukturer, nestede kategorier), er det verdifullt, praktisk relevant PostgreSQL-kunnskaper å forstå CTEer og rekursive spørringer som forbedrer spørringens vedlikeholdbarhet og låser opp hierarkisk spørring — en hyppig relevant evne for ren, sofistikert SQL og et vanlig tema, spesielt den rekursive formen for det tilbakevendende behovet for å spørre trestrukturert data.