CTEs récursives : hiérarchies de profondeur arbitraire

Un self-JOIN (Module 5) trouve un seul niveau d'une hiérarchie : enfant → parent. Mais que se passe-t-il si la hiérarchie est profonde de 3, 5 ou n niveaux, et tu ne sais pas à l'avance combien ? Vous avez besoin de CTE récursifs : a CTE qui se réfère à lui-même pour « étendre » les résultats de manière itérative.

Dans le jeu de données ecommerce, la table categories a 3 racines (Elettronica, Libri, Casa) et 2 enfants chacun — un arbre à 2 niveaux. C'est un cas simple mais suffisant pour apprendre le modèle.

La syntaxe

WITH RECURSIVE nome (col1, col2, …) AS (
  -- (1) Termine BASE: le righe di partenza
  SELECT …
  FROM   …
  WHERE  …

  UNION ALL

  -- (2) Termine RICORSIVO: si appoggia a "nome" stesso per espandere
  SELECT …
  FROM   tabella  t
  JOIN   nome    n  ON …
)
SELECT * FROM nome;

Comment ça marche en pratique :

1. PostgreSQL exécute le terme de base une fois.
2. Il exécute le terme récursif en utilisant les lignes qui viennent d'être produites comme "entrée" (pas l'ensemble du CTE : seulement l'incrément).
3. Il répète l'étape 2 jusqu'à ce que le terme récursif produise 0 nouvelle ligne.
4. Le résultat final est le UNION ALL de toutes les étapes.

Exemple 1 : arbre avec profondeur et chemin

WITH RECURSIVE tree AS (
  -- base: le radici (parent_id IS NULL)
  SELECT id,
         name,
         parent_id,
         1                 AS depth,
         name              AS path
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  -- ricorsivo: per ogni categoria che ha come parent una riga già in tree
  SELECT c.id,
         c.name,
         c.parent_id,
         t.depth + 1,
         t.path || ' > ' || c.name
  FROM   categories c
  JOIN   tree       t ON c.parent_id = t.id
)
SELECT id, name, depth, path
FROM   tree
ORDER BY path;

La première itération produit 3 lignes (les racines au niveau 1). La deuxième l'itération trouve les 6 enfants (niveau 2). La troisième itération trouve rien → fait.

Exemple 2 : étant donné un nœud, trouver sa racine

Une variante "top-down → bottom-up" : on propage l'identifiant de la racine vers le bas l'arbre.

WITH RECURSIVE tree AS (
  SELECT id,
         name,
         parent_id,
         id   AS root_id,
         name AS root_name
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id,
         c.name,
         c.parent_id,
         t.root_id,    -- propago lo stesso id_radice
         t.root_name   -- e nome
  FROM   categories c
  JOIN   tree       t ON c.parent_id = t.id
)
SELECT id, name, root_name
FROM   tree
ORDER BY id;

Essayez-le

Exercice de révision