Comment fonctionnent les CTE et les requêtes récursives dans PostgreSQL ?

Question

Accepted Answer

Une **CTE** (Common Table Expression), définie avec **`WITH`**, est un ensemble de résultats nommé et temporaire qui rend les requêtes complexes lisibles. PostgreSQL supporte pleinement les CTE, y compris les CTE **récursifs** (`WITH RECURSIVE`) pour parcourir des données hiérarchiques/arborescentes.

## CTE basiques — construire des requêtes lisibles

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

Les CTE décomposent une requête complexe en étapes nommées et lisibles (chaînées, chacune référençant les précédentes) — bien plus clair que les sous-requêtes imbriquées.

## CTE récursifs — parcourir les hiérarchies

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

Une **CTE récursive** possède un cas de base (les lignes de départ) et une étape récursive (`UNION ALL` se rattachant à la CTE elle-même), itérant jusqu'à ce qu'aucune nouvelle ligne n'apparaisse — parfait pour les **arbres et hiérarchies** (organigrammes, arbres de catégories, nomenclatures, chemins de graphes).

## Une note sur la matérialisation des CTE (spécifique à 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)
```

Avant Postgres 12, les CTE étaient toujours une « barrière d'optimisation » (matérialisées) ; maintenant elles peuvent être intégrées pour de meilleures performances — `MATERIALIZED`/`NOT MATERIALIZED` contrôle cela.

## Pourquoi c'est important

Les CTE et les requêtes récursives sont des fonctionnalités PostgreSQL précieuses pour la **lisibilité et les données hiérarchiques**, il est donc utile de les comprendre pour écrire des requêtes complexes maintenables et gérer des données structurées en arbre.

Le bénéfice quotidien est la **lisibilité** : les requêtes complexes avec plusieurs niveaux de sous-requêtes deviennent difficiles à maintenir, tandis que les CTE vous permettent de les décomposer en étapes logiques nommées et séquentielles (lisibles de haut en bas), améliorant considérablement la clarté — ce qui rend les CTE un outil important pour écrire du SQL maintenable.

La capacité remarquable est les **CTE récursifs** (`WITH RECURSIVE`), qui permettent d'interroger les **données hiérarchiques et structurées en arbre** (organigrammes, arbres de catégories, nomenclatures, commentaires filetés, traversée de graphes) — une classe de requêtes autrement très difficiles en SQL, ce qui rend les CTE récursifs la solution standard pour la traversée hiérarchique.

Comprendre la structure des CTE récursifs (cas de base + étape récursive se rattachant à elle-même) est une connaissance clé pour ce besoin courant.

Un détail utile spécifique à Postgres est le **comportement de matérialisation des CTE** (Postgres 12+ peut intégrer les CTE pour de meilleures performances, contrôlable avec `MATERIALIZED`/`NOT MATERIALIZED`) — pertinent pour l'optimisation.

Comme les requêtes complexes sont courantes (et la lisibilité compte pour la maintenance), et comme les données hiérarchiques (que les CTE récursifs gèrent exceptionnellement bien) apparaissent fréquemment (structures organisationnelles, catégories imbriquées), comprendre les CTE et les requêtes récursives est une connaissance PostgreSQL précieuse et pratiquement pertinente qui améliore la maintenabilité des requêtes et déverrouille les requêtes hiérarchiques — une capacité fréquemment pertinente pour du SQL propre et sophistiqué, et un sujet courant, en particulier la forme récursive pour le besoin récurrent d'interroger des données structurées en arbre.