CTE recursivas

1. Qué es

Un CTE recursivo es un CTE que se referencia a sí mismo. Sirve para lo único que SQL normal no sabe hacer: recorrer una estructura cuya profundidad no conoces al escribir la consulta.

Los dos casos son siempre los mismos:

1. Jerarquías — un empleado que reporta a un empleado que reporta a otro. Categorías con subcategorías. Piezas compuestas de piezas.
2. Series generadas — todos los días entre dos fechas, aunque no exista ninguna tabla con esos días.

Por qué no basta un JOIN: para saber quién reporta a Fuller directamente, un JOIN sobra. Para saber quién reporta a Fuller a cualquier profundidad, necesitarías un JOIN por nivel — y tendrías que saber cuántos niveles hay antes de escribir la consulta. Si mañana la empresa añade un nivel, la consulta deja de ser correcta sin dar error.

Un CTE recursivo se detiene solo cuando ya no hay más filas que añadir. No necesita saber la profundidad.

2. Sintaxis

WITH RECURSIVE nombre AS (
    SELECT ...            -- caso base: de dónde se parte
  UNION ALL
    SELECT ...            -- paso recursivo: usa `nombre`
    FROM tabla JOIN nombre ON ...
    WHERE <condición de parada>
)
SELECT * FROM nombre;

Cuatro reglas que no se negocian:

• RECURSIVE va después de WITH, no en el nombre del CTE. WITH RECURSIVE a AS (...), b AS (...) — una sola palabra RECURSIVE habilita la recursión para todos los CTE de esa lista.
• Las dos ramas se unen con UNION o UNION ALL, nunca con coma ni con JOIN.
• El caso base va primero y no puede referenciarse a sí mismo.
• Tiene que haber una condición de parada. O el JOIN se queda sin filas que emparejar, o hay un WHERE que corta. Sin ninguna de las dos, la consulta no termina.

2.1 Cómo se ejecuta, paso a paso

Esto es lo que confunde a todo el mundo la primera vez: nombre, dentro del paso recursivo, no es el resultado acumulado. Es solo lo que produjo la iteración anterior.

Iteración 0: se ejecuta el caso base        → tabla de trabajo = {Fuller}
Iteración 1: paso recursivo sobre {Fuller}  → {Davolio, Leverling, Peacock, Buchanan, Callahan}
Iteración 2: paso recursivo sobre esas 5    → {Suyama, King, Dodsworth}
Iteración 3: paso recursivo sobre esas 3    → {}  → para
Resultado final: la unión de todo lo producido = 9 filas

Se para cuando una iteración no produce ninguna fila. Por eso el paso recursivo va contra la tabla de trabajo y no contra el acumulado: si fuera contra el acumulado, nunca se vaciaría.

3. El ejemplo mínimo: contar

Antes de tocar Northwind, el CTE recursivo más pequeño que existe. No lee ninguna tabla:

WITH RECURSIVE contador AS (
    SELECT 1 AS n
  UNION ALL
    SELECT n + 1 FROM contador WHERE n < 5
)
SELECT n FROM contador;

 n
---
 1
 2
 3
 4
 5

Quita el WHERE n < 5 y la consulta no termina nunca. Ese WHERE es lo único que separa esto de un bucle infinito. Escríbelo antes de escribir el resto.

4. Ejemplos sobre Northwind

4.1 El organigrama

employees.reports_to apunta a otro employee_id. Quien tiene NULL está arriba del todo:

SELECT employee_id, first_name||' '||last_name AS nombre, title, reports_to
FROM employees ORDER BY employee_id;

 employee_id |      nombre      |          title           | reports_to
-------------+------------------+--------------------------+------------
           1 | Nancy Davolio    | Sales Representative     |          2
           2 | Andrew Fuller    | Vice President, Sales    |
           3 | Janet Leverling  | Sales Representative     |          2
           4 | Margaret Peacock | Sales Representative     |          2
           5 | Steven Buchanan  | Sales Manager            |          2
           6 | Michael Suyama   | Sales Representative     |          5
           7 | Robert King      | Sales Representative     |          5
           8 | Laura Callahan   | Inside Sales Coordinator |          2
           9 | Anne Dodsworth   | Sales Representative     |          5

Tres niveles: Fuller arriba, cinco personas debajo de él, y tres más debajo de Buchanan.

WITH RECURSIVE arbol AS (
    SELECT employee_id, first_name||' '||last_name AS nombre, reports_to, 1 AS nivel
    FROM employees
    WHERE reports_to IS NULL                       -- caso base: la cúpula
  UNION ALL
    SELECT e.employee_id, e.first_name||' '||e.last_name, e.reports_to, a.nivel + 1
    FROM employees e
    JOIN arbol a ON e.reports_to = a.employee_id   -- paso: los hijos de lo ya encontrado
)
SELECT nivel, employee_id, repeat('    ', nivel - 1) || nombre AS organigrama
FROM arbol ORDER BY nivel, employee_id;

 nivel | employee_id |      organigrama
-------+-------------+------------------------
     1 |           2 | Andrew Fuller
     2 |           1 |     Nancy Davolio
     2 |           3 |     Janet Leverling
     2 |           4 |     Margaret Peacock
     2 |           5 |     Steven Buchanan
     2 |           8 |     Laura Callahan
     3 |           6 |         Michael Suyama
     3 |           7 |         Robert King
     3 |           9 |         Anne Dodsworth

Dos cosas que hacen el trabajo:

• nivel es una columna que te inventas y que se incrementa sola. No existe en la tabla. Nace en 1 en el caso base y cada iteración le suma 1. Es la forma estándar de saber la profundidad.
• repeat(' ', nivel - 1) convierte el número en sangría. El árbol no está en los datos: está en el orden y en los espacios.

4.2 La ruta completa

El mismo patrón, acumulando texto en vez de un número:

WITH RECURSIVE arbol AS (
    SELECT employee_id, first_name||' '||last_name AS nombre, reports_to, 1 AS nivel,
           (first_name||' '||last_name)::text AS ruta
    FROM employees WHERE reports_to IS NULL
  UNION ALL
    SELECT e.employee_id, e.first_name||' '||e.last_name, e.reports_to, a.nivel + 1,
           a.ruta || ' > ' || e.first_name || ' ' || e.last_name
    FROM employees e JOIN arbol a ON e.reports_to = a.employee_id
)
SELECT nivel, nombre, ruta FROM arbol ORDER BY ruta;

 nivel |      nombre      |                       ruta
-------+------------------+--------------------------------------------------
     1 | Andrew Fuller    | Andrew Fuller
     2 | Janet Leverling  | Andrew Fuller > Janet Leverling
     2 | Laura Callahan   | Andrew Fuller > Laura Callahan
     2 | Margaret Peacock | Andrew Fuller > Margaret Peacock
     2 | Nancy Davolio    | Andrew Fuller > Nancy Davolio
     2 | Steven Buchanan  | Andrew Fuller > Steven Buchanan
     3 | Anne Dodsworth   | Andrew Fuller > Steven Buchanan > Anne Dodsworth
     3 | Michael Suyama   | Andrew Fuller > Steven Buchanan > Michael Suyama
     3 | Robert King      | Andrew Fuller > Steven Buchanan > Robert King

ORDER BY ruta ordena el árbol como un árbol, porque el texto de un hijo empieza por el texto de su padre. Es un truco viejo y sigue funcionando.

El ::text del caso base no es decorativo. Sin él, first_name||' '||last_name sale como varchar y el paso recursivo, que produce un texto más largo, choca con el tipo fijado por el caso base. El tipo de cada columna lo decide el caso base, y el paso recursivo tiene que encajar.

4.3 El ejemplo que justifica el tema: ventas de una rama

Un jefe no responde por sus ventas: responde por las de toda su rama. Eso es imposible sin recursión, porque la profundidad de cada rama es distinta.

WITH RECURSIVE arbol AS (
    SELECT employee_id AS raiz, employee_id           -- cada empleado, como raíz de sí mismo
    FROM employees
  UNION ALL
    SELECT a.raiz, e.employee_id                      -- y todos los que cuelgan de él
    FROM employees e JOIN arbol a ON e.reports_to = a.employee_id
),
ventas AS (
    SELECT o.employee_id, sum(od.unit_price*od.quantity*(1-od.discount)) AS total
    FROM orders o JOIN order_details od ON od.order_id = o.order_id
    GROUP BY 1
)
SELECT em.employee_id, em.first_name||' '||em.last_name AS nombre,
       round(coalesce(vp.total, 0), 2)  AS propia,
       round(sum(coalesce(v.total, 0)), 2) AS rama
FROM arbol a
JOIN employees em         ON em.employee_id = a.raiz
LEFT JOIN ventas v        ON v.employee_id  = a.employee_id
LEFT JOIN ventas vp       ON vp.employee_id = a.raiz
GROUP BY em.employee_id, em.first_name, em.last_name, vp.total
ORDER BY rama DESC;

 employee_id |      nombre      |  propia   |    rama
-------------+------------------+-----------+------------
           2 | Andrew Fuller    | 166537.76 | 1265793.04
           5 | Steven Buchanan  |  68792.28 |  344581.71
           4 | Margaret Peacock | 232890.85 |  232890.85
           3 | Janet Leverling  | 202812.84 |  202812.84
           1 | Nancy Davolio    | 192107.60 |  192107.60
           8 | Laura Callahan   | 126862.28 |  126862.28
           7 | Robert King      | 124568.24 |  124568.24
           9 | Anne Dodsworth   |  77308.07 |   77308.07
           6 | Michael Suyama   |  73913.13 |   73913.13

Léelo despacio, porque cambia por completo quién parece estar rindiendo:

• Por venta propia, Peacock (232 890,85) es la primera y Fuller (166 537,76) la sexta. Por rama, Fuller es el primero con 1 265 793,04 — que es exactamente la facturación total de la empresa, porque todo el mundo cuelga de él. Es la comprobación de que la consulta está bien: la rama del nodo raíz tiene que dar el total.
• Buchanan sube del séptimo puesto al segundo. Vende 68 792,28 él mismo, pero su rama mueve 344 581,71: cinco veces más. Un cuadro de mando que solo mire venta propia le da el peor número de la casa a la persona que gestiona la segunda rama del negocio.
• Quien no tiene subordinados tiene propia = rama. Es correcto y conviene verlo: confirma que el caso base incluye a cada empleado como raíz de sí mismo.

El truco del caso base es la clave. No parte de la cúpula, sino de todos los empleados a la vez, cada uno etiquetado como su propia raiz. La recursión propaga esa etiqueta hacia abajo. Así, en una sola pasada, obtienes la rama de los nueve.

4.4 Generar una serie que no existe en ninguna tabla

El otro uso. Aquí no hay jerarquía: la fila siguiente se calcula desde la anterior.

WITH RECURSIVE calendario AS (
    SELECT DATE '1998-05-01' AS dia
  UNION ALL
    SELECT dia + 1 FROM calendario WHERE dia < DATE '1998-05-10'
)
SELECT dia, to_char(dia, 'Dy') AS d FROM calendario;

    dia     |  d
------------+-----
 1998-05-01 | Fri
 1998-05-02 | Sat
 ...
 1998-05-10 | Sun

En PostgreSQL esto casi nunca se escribe así, porque existe generate_series, que hace lo mismo en una línea y más rápido:

SELECT * FROM generate_series(DATE '1998-05-01', DATE '1998-05-10', INTERVAL '1 day');

Merece la pena conocer las dos: generate_series cuando el paso es fijo (un día, una hora, 5 en 5), recursiva cuando el paso depende de la fila anterior — un saldo que se arrastra, una fecha de vencimiento que se calcula desde la anterior, un interés compuesto.

5. Errores comunes

1. Olvidar RECURSIVE. ERROR: relation "contador" does not exist El mensaje no dice "te falta RECURSIVE": dice que la tabla no existe. Sin la palabra, un CTE no se ve a sí mismo y el nombre queda sin resolver.

2. Poner RECURSIVE en el CTE equivocado. Va una sola vez, pegado a WITH, aunque el recursivo sea el tercero de la lista: WITH RECURSIVE a AS (...), b AS (...), c AS (...).

3. No poner condición de parada. La consulta corre hasta que se llene el disco de temporales o la mates con Ctrl+C. Antes de ejecutar una recursiva nueva, ponle un techo: sql WHERE nivel < 10 Se quita después, cuando ya has visto que termina. Cuesta cinco segundos y evita colgar la sesión.

4. Recursión infinita por un ciclo en los datos. El caso peligroso: la consulta está bien escrita y aun así no termina, porque A reporta a B, B a C y C a A. Ninguna iteración se queda sin filas.

sql WITH RECURSIVE ciclico(id, jefe) AS ( VALUES (1,3),(2,1),(3,2) -- 1←3←2←1: un ciclo ), r AS ( SELECT id, jefe, 1 AS paso, ARRAY[id] AS visitados FROM ciclico WHERE id = 1 UNION ALL SELECT c.id, c.jefe, r.paso + 1, r.visitados || c.id FROM ciclico c JOIN r ON c.jefe = r.id WHERE r.paso < 8 -- el techo, sin él no para ) SELECT paso, id, jefe, visitados FROM r; paso | id | jefe | visitados ------+----+------+------------------- 1 | 1 | 3 | {1} 2 | 2 | 1 | {1,2} 3 | 3 | 2 | {1,2,3} 4 | 1 | 3 | {1,2,3,1} 5 | 2 | 1 | {1,2,3,1,2} 6 | 3 | 2 | {1,2,3,1,2,3} 7 | 1 | 3 | {1,2,3,1,2,3,1} 8 | 2 | 1 | {1,2,3,1,2,3,1,2}

Se ve el ciclo dando vueltas: 1, 2, 3, 1, 2, 3…

Desde PostgreSQL 14 hay una cláusula que lo detecta sola, CYCLE:

sql WITH RECURSIVE ciclico(id, jefe) AS ( VALUES (1,3),(2,1),(3,2) ), r AS ( SELECT id, jefe FROM ciclico WHERE id = 1 UNION ALL SELECT c.id, c.jefe FROM ciclico c JOIN r ON c.jefe = r.id ) CYCLE id SET es_ciclo USING ruta SELECT id, jefe, es_ciclo, ruta FROM r; id | jefe | es_ciclo | ruta ----+------+----------+------------------- 1 | 3 | f | {(1)} 2 | 1 | f | {(1),(2)} 3 | 2 | f | {(1),(2),(3)} 1 | 3 | t | {(1),(2),(3),(1)}

CYCLE id SET es_ciclo USING ruta significa: vigila la columna id; cuando repita, marca es_ciclo = true y para esa rama. La columna ruta guarda el camino recorrido. Northwind no tiene ciclos — pero una tabla de organigrama real, mantenida a mano durante años, los tiene más a menudo de lo que nadie admite.

1. Usar UNION en lugar de UNION ALL "para protegerse". UNION elimina duplicados, lo que en un grafo con ciclos hace que la recursión termine. Pero también borra filas legítimamente repetidas y obliga a deduplicar en cada iteración. Usa UNION ALL y controla el ciclo explícitamente con CYCLE o con un array de visitados. Si eliges UNION, que sea una decisión, no un amuleto.

2. Chocar de tipos entre las dos ramas. El caso base fija el tipo de cada columna. SELECT 1 AS n da integer; si el paso recursivo produce un numeric, falla. Se arregla casteando en el caso base: SELECT 1::numeric AS n.

6. Lo que no necesita una recursiva

Vale la pena decirlo, porque el error contrario también existe: una jerarquía de profundidad fija no necesita recursión.

region → territories → employee_territories parece un árbol, y no lo es: son exactamente dos niveles, siempre, garantizados por las claves foráneas.

SELECT r.region_description, count(*) AS territorios
FROM territories t JOIN region r USING (region_id)
GROUP BY 1 ORDER BY 2 DESC;

 region_description | territorios
--------------------+-------------
 Eastern            |          19
 Western            |          15
 Northern           |          11
 Southern           |           8

Cuatro regiones, 53 territorios, dos niveles. Un JOIN normal basta y es más rápido y más legible. La recursiva es para cuando la profundidad es desconocida o variable, no para cuando es simplemente mayor que uno.

7. Diferencias con SQL Server

La diferencia que muerde es la primera. Una recursiva de SQL Server copiada tal cual a PostgreSQL falla con relation does not exist, porque le falta RECURSIVE. Y a la inversa, PostgreSQL no tiene la red de seguridad de MAXRECURSION 100: lo que en SQL Server se corta solo con un error, aquí corre indefinidamente. De ahí la costumbre del techo manual.

8. Ejercicios

1. El organigrama con el nombre del jefe directo en una columna, no su employee_id.
2. Todos los subordinados de Steven Buchanan a cualquier profundidad, sin incluirlo a él, con cuántos saltos hay hasta cada uno.
3. Para cada empleado, cuántos subordinados directos tiene y cuántos en total contando los indirectos.
4. Genera todos los meses entre el primer y el último pedido, y comprueba si algún mes se quedó sin pedidos.
5. Convierte el contador de la sección 3 en una serie que empiece en 100 y baje de 10 en 10 hasta 0. ¿Qué pasa si el paso no divide exactamente al rango?

9. Pregunta de negocio

Operaciones dice que hubo días sin actividad y quiere saber cuántos fueron y si siguen algún patrón.

Los días sin pedidos no están en orders: precisamente lo que los define es no estar. Ahí está el tema entero.