Expresiones de tabla común y consultas recursivas con WITH

Qué es

WITH name AS (SELECT ...) define un resultado con nombre temporal para la consulta principal; una sentencia puede definir varios, cada uno capaz de referirse a los anteriores. La documentación de PostgreSQL describe la forma recursiva: WITH RECURSIVE t AS (non-recursive term UNION [ALL] recursive term) evalúa el término no recursivo, coloca sus filas en una tabla de trabajo y luego evalúa repetidamente el término recursivo sustituyendo la autorreferencia por la tabla de trabajo hasta que no se devuelve ninguna fila. UNION descarta las filas duplicadas, UNION ALL las conserva. La misma página documenta SEARCH DEPTH FIRST | BREADTH FIRST BY ... SET col para ordenar la salida y CYCLE col SET is_cycle USING path para detenerse ante los bucles, y afirma que una consulta WITH no recursiva y sin efectos secundarios a la que se hace referencia una sola vez se integra en la consulta principal de forma predeterminada, mientras que MATERIALIZED fuerza una evaluación separada y NOT MATERIALIZED fuerza la integración.

Por qué importa

Las jerarquías (categorías, organigramas, comentarios anidados, listas de materiales) no encajan en un número fijo de joins. Una CTE recursiva gestiona una profundidad arbitraria en una sola sentencia; las CTE no recursivas dividen una consulta larga en pasos con nombre que se pueden leer y probar uno por uno.

Cómo aplicarlo

• Cadena de ascendientes: partir del nodo (SELECT id, parent_id, 1 AS depth FROM nodes WHERE id = $1) y unir el término recursivo por parent_id; los descendientes se recorren en sentido contrario. Llevar depth y limitarlo (WHERE depth < 50) como resguardo frente a bucles inesperados.
• Para grafos que puedan contener ciclos, usar UNION o la cláusula CYCLE; UNION ALL sobre un ciclo nunca termina sin un límite.
• Usar SEARCH DEPTH FIRST BY name SET ord junto con ORDER BY ord para imprimir un árbol en orden anidado.
• Las CTE que modifican datos mueven filas en una sola sentencia: WITH moved AS (DELETE FROM live WHERE ... RETURNING *) INSERT INTO archive SELECT * FROM moved.
• Una CTE a la que se hace referencia más de una vez se materializa de forma predeterminada; escribir MATERIALIZED en una CTE de un solo uso solo para forzar una evaluación separada (una función costosa calculada una vez), y NOT MATERIALIZED en una referenciada varias veces cuando cada uso necesita solo una porción pequeña y favorable a los índices, como muestra el ejemplo big_table de la documentación.

Trampas

Una CTE materializada se evalúa tal como está escrita, de modo que el WHERE externo no puede empujarse dentro de ella y los índices de la tabla base no sirven al predicado externo. Cada fila que produce un término recursivo permanece en el resultado, por lo que las filas anchas en árboles grandes resultan costosas. La documentación califica el orden de salida en anchura como un detalle de implementación en el que no hay que confiar, y el orden dentro de un nivel como indefinido; usar SEARCH o un ORDER BY explícito.