CTEs recursivas: jerarquías de profundidad arbitraria

Una auto-UNIÓN (Módulo 5) encuentra sólo un nivel de una jerarquía: hijo → padre. Pero, ¿qué sucede si la jerarquía tiene 3, 5 o n niveles de profundidad? y no sabes de antemano cuantos? Necesitas CTE recursivos: a CTE que se refiere a sí mismo para "ampliar" los resultados de forma iterativa.

En el conjunto de datos ecommerce, la tabla categories tiene 3 raíces (Elettronica, Libri, Casa) y 2 niños cada uno: un árbol de 2 niveles. es un Caso simple pero suficiente para aprender el patrón.

La sintaxis

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;

Cómo funciona en la práctica:

1. PostgreSQL ejecuta el término base una vez.
2. Ejecuta el término recursivo usando las filas recién producidas como "entrada" (no todo el CTE: sólo el incremento).
3. Repite el paso 2 hasta que el término recursivo produzca 0 filas nuevas.
4. El resultado final es el UNION ALL de todos los pasos.

Ejemplo 1: árbol con profundidad y camino

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 primera iteración produce 3 filas (las raíces en el nivel 1). el segundo la iteración encuentra a los 6 niños (nivel 2). La tercera iteración encuentra nada → hecho.

Ejemplo 2: dado un nodo, encontrar su raíz

Una variante "de arriba hacia abajo → de abajo hacia arriba": propagamos la identificación de la raíz hacia abajo a lo largo el árbol.

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;

Pruébalo

Ejercicio de revisión