Hvordan fungerer CTE'er og rekursive forespørgsler i PostgreSQL?

Question

Accepted Answer

En **CTE** (Common Table Expression), defineret med **`WITH`**, er et navngivet midlertidigt resultatmængde, der gør komplekse forespørgsler læsbare. PostgreSQL understøtter fuldt ud CTE'er, herunder **rekursive** CTE'er (`WITH RECURSIVE`) til at gennemløbe hierarkiske/træstrukturerede data.

## Grundlæggende CTE'er — læsbar forespørgselsopbygning

```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'er deler en kompleks forespørgsel op i navngivne, læsbare trin (kædet sammen, hvor hver refererer til tidligere) — meget klarere end indlejrede underforespørgsler.

## Rekursive CTE'er — gennemløb 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 basistilførsel (startrækkerne) og et rekursivt trin (`UNION ALL` der joiner tilbage til CTE'en selv), itererer indtil ingen nye rækker — perfekt til **træer og hierarkier** (organisationsdiagrammer, kategoritræer, styklister, grafstier).

## En note om CTE-materialisering (PostgreSQL-specifik)

```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 PostgreSQL 12 blev CTE'er altid et "optimeringsbarriære" (materialiseret); nu kan de indlægges for bedre ydeevne — `MATERIALIZED`/`NOT MATERIALIZED` kontrollerer dette.

## Hvorfor det er vigtig

CTE'er og rekursive forespørgsler er værdifulde PostgreSQL-funktioner til **læsbarhed og hierarkiske data**, så det er nyttigt at forstå dem for at skrive vedligeholdelsesvenlige komplekse forespørgsler og håndtere træstrukturerede data.

Den daglige fordel er **læsbarhed**: komplekse forespørgsler med flere indlejringsniveauer for underforespørgsler bliver svære at vedligeholde, mens CTE'er lader dig dekomponere dem i navngivne, sekventielle logiske trin (læs fra top til bund), hvilket dramatisk forbedrer klarhed — hvilket gør CTE'er til et vigtig værktøj for at skrive vedligeholdelsesvenlig SQL.

Den fremragende egenskab er **rekursive CTE'er** (`WITH RECURSIVE`), der muliggør forespørgsler på **hierarkisk og træstrukturerede data** (organisationsdiagrammer, kategoritræer, styklister, sammenhængende kommentarer, grafgennemløb) — en klasse af forespørgsler, der ellers er meget svær i SQL, hvilket gør rekursive CTE'er til standardløsningen til hierarkisk gennemløb.

At forstå den rekursive CTE-struktur (basistilførsel + rekursivt trin der joiner tilbage til sig selv) er vigtig viden til dette almindelige behov.

En nyttig PostgreSQL-specifik detalje er **CTE-materializeringspåvirkning** (PostgreSQL 12+ kan indlægge CTE'er for bedre ydeevne, kontrollerbar med `MATERIALIZED`/`NOT MATERIALIZED`) — relevant for optimering.

Da komplekse forespørgsler er almindelige (og læsbarhed betyder noget for vedligeholdelse), og da hierarkiske data (som rekursive CTE'er håndterer enestående godt) optræder hyppigt (organisationsstrukturer, indlejrede kategorier), er det værdifuldt, praktisk-relevant PostgreSQL-viden at forstå CTE'er og rekursive forespørgsler, som forbedrer forespørgselsvedligeholdelse og låser op for hierarkisk forespørgsler — en hyppigt relevant evne til ren, sofistikeret SQL og et almindeligt emne, især den rekursive form til det tilbagevendende behov for at forespørge træstrukturerede data.