Ir al contenido
Todos los términos del glosario

Expresión de tabla común (CTE)

¿Qué es una CTE (consulta WITH) en PostgreSQL?

Una expresión de tabla común, escrita con WITH, le da un nombre a una subconsulta para que el resto de la sentencia pueda usarla como una tabla. WITH RECURSIVE maneja árboles y jerarquías.

Una expresión de tabla común (CTE) es una sentencia auxiliar en una cláusula WITH que define un resultado temporal con nombre para una sola consulta. Divide una consulta complicada en pasos legibles, y la sentencia auxiliar también puede ser un INSERT, UPDATE o DELETE que modifica datos (para insertar, actualizar o eliminar filas).

WITH RECURSIVE permite que una CTE haga referencia a su propia salida, que es la forma estándar de recorrer jerarquías como líneas de reporte o categorías anidadas. Una CTE no recursiva, sin efectos secundarios y que la consulta referencia una sola vez se integra en la consulta externa para que ambas puedan optimizarse juntas. Agrega MATERIALIZED para que se evalúe por separado, o NOT MATERIALIZED para pedir que se integre.

Ejemplo

El ejemplo parte de un empleado y sube por la cadena de mando con WITH RECURSIVE, y devuelve a cada persona con su distancia respecto al punto de partida.

CREATE TABLE employees (
  id int PRIMARY KEY,
  name text NOT NULL,
  manager_id int REFERENCES employees (id)
);
INSERT INTO employees VALUES (1, 'Ana', NULL), (2, 'Ben', 1), (3, 'Chen', 2), (4, 'Dee', 1);

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS depth FROM employees WHERE id = 3
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM employees e JOIN chain c ON e.id = c.manager_id
)
SELECT name, depth FROM chain ORDER BY depth;

SQL simple para PostgreSQL 16 o 17 estándar, sin extensiones. Ejecútalo en una base de datos vacía que puedas descartar.

Fuentes

Documentación oficial de PostgreSQL (en inglés).

Términos relacionados