En PostgreSQL, una expresión de tabla común (CTE) es un conjunto de resultados temporal con nombre dentro de una consulta.
Una CTE recursiva le permite realizar recursión dentro de una consulta utilizando la sintaxis WITH RECURSIVE.
Una CTE recursiva suele denominarse consulta recursiva.
A continuación se muestra la sintaxis básica de una CTE recursiva:
WITH RECURSIVE cte_name (column1, column2, ...)
AS(
-- anchor member
SELECT select_list FROM table1 WHERE condition
UNION [ALL]
-- recursive term
SELECT select_list FROM cte_name WHERE recursive_condition
)
SELECT * FROM cte_name;
En esta sintaxis:
cte_name: Especifica el nombre del CTE. Puede hacer referencia a este nombre de CTE en las partes posteriores de la consulta.columna1,columna2, ... Especifique las columnas seleccionadas en los miembros ancla y recursivos. Estas columnas definen la estructura del CTE.- Miembro ancla: Se encarga de formar el conjunto de resultados base de la estructura de la CTE.
- Miembro recursivo: Se refiere al propio nombre del CTE. Se combina con el miembro ancla mediante el operador
UNIONoUNION ALL. condición_recursiva: Es una condición utilizada en el miembro recursivo que determina cómo se detiene la recursión.
PostgreSQL ejecuta una CTE recursiva en la siguiente secuencia:
- Primero, ejecuta el miembro ancla para crear el conjunto de resultados base (R0).
- Segundo, ejecuta el miembro recursivo con Ri como entrada para devolver el conjunto de resultados Ri+1 como salida.
- Tercero, repita el paso 2 hasta que se devuelva un conjunto vacío. (comprobación de terminación)
- Por último, devuelve el conjunto de resultados final que es una [UNION o
UNION ALLde los conjuntos de resultados R0, R1, ... Rn.
Una CTE recursiva puede ser útil cuando se trabaja con estructuras de datos jerárquicas o anidadas, como árboles o grafos.
Ejemplo de consulta recursiva PostgreSQL¶
Veamos un ejemplo de uso de una consulta recursiva.
Creación de una tabla de ejemplo¶
Primero, cree una nueva tabla llamada empleados:
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
full_name VARCHAR NOT NULL,
manager_id INT
);
La tabla de empleados tiene tres columnas: employee_id, full_name y manager_id. La columna manager_id especifica el id de manager de un empleado.
En segundo lugar, inserte algunas filas en la tabla de empleados:
INSERT INTO employees (employee_id, full_name, manager_id)
VALUES
(1, 'Michael North', NULL),
(2, 'Megan Berry', 1),
(3, 'Sarah Berry', 1),
(4, 'Zoe Black', 1),
(5, 'Tim James', 1),
(6, 'Bella Tucker', 2),
(7, 'Ryan Metcalfe', 2),
(8, 'Max Mills', 2),
(9, 'Benjamin Glover', 2),
(10, 'Carolyn Henderson', 3),
(11, 'Nicola Kelly', 3),
(12, 'Alexandra Climo', 3),
(13, 'Dominic King', 3),
(14, 'Leonard Gray', 4),
(15, 'Eric Rampling', 4),
(16, 'Piers Paige', 7),
(17, 'Ryan Henderson', 7),
(18, 'Frank Tucker', 8),
(19, 'Nathan Ferguson', 8),
(20, 'Kevin Rampling', 8);
Ejemplo básico de consulta recursiva PostgreSQL¶
La siguiente sentencia utiliza una CTE recursiva para encontrar todos los subordinados del manager con el id 2.
WITH RECURSIVE subordinates AS (
SELECT
employee_id,
manager_id,
full_name
FROM
employees
WHERE
employee_id = 2
UNION
SELECT
e.employee_id,
e.manager_id,
e.full_name
FROM
employees e
INNER JOIN subordinates s ON s.employee_id = e.manager_id
)
SELECT * FROM subordinates;
Salida:
employee_id | manager_id | full_name
-------------+------------+-----------------
2 | 1 | Megan Berry
6 | 2 | Bella Tucker
7 | 2 | Ryan Metcalfe
8 | 2 | Max Mills
9 | 2 | Benjamin Glover
16 | 7 | Piers Paige
17 | 7 | Ryan Henderson
18 | 8 | Frank Tucker
19 | 8 | Nathan Ferguson
20 | 8 | Kevin Rampling
(10 rows)
Cómo funciona:
- El CTE recursivo
subordinadefine un miembro ancla y un miembro recursivo. - El miembro ancla devuelve el conjunto de resultados base R0 que es el empleado con el id 2.
employee_id | manager_id | full_name
-------------+------------+-------------
2 | 1 | Megan Berry
El miembro recursivo devuelve los subordinados directos del empleado id 2. Este es el resultado de la unión entre la tabla empleados y el CTE subordinados. La primera iteración del término recursivo devuelve el siguiente conjunto de resultados:
employee_id | manager_id | full_name
-------------+------------+-----------------
6 | 2 | Bella Tucker
7 | 2 | Ryan Metcalfe
8 | 2 | Max Mills
9 | 2 | Benjamin Glover
PostgreSQL ejecuta el miembro recursivo repetidamente. La segunda iteración del miembro recursivo utiliza el conjunto de resultados anterior paso como el valor de entrada, y devuelve este conjunto de resultados:
employee_id | manager_id | full_name
-------------+------------+-----------------
16 | 7 | Piers Paige
17 | 7 | Ryan Henderson
18 | 8 | Frank Tucker
19 | 8 | Nathan Ferguson
20 | 8 | Kevin Rampling
La tercera iteración devuelve un conjunto de resultados vacío porque ningún empleado está reportando al empleado con los id 16, 17, 18, 19 y 20.
PostgreSQL devuelve el conjunto de resultados final que es la unión de todos los conjuntos de resultados de la primera y segunda iteraciones generados por los miembros no recursivos y recursivos.
employee_id | manager_id | full_name
-------------+------------+-----------------
2 | 1 | Megan Berry
6 | 2 | Bella Tucker
7 | 2 | Ryan Metcalfe
8 | 2 | Max Mills
9 | 2 | Benjamin Glover
16 | 7 | Piers Paige
17 | 7 | Ryan Henderson
18 | 8 | Frank Tucker
19 | 8 | Nathan Ferguson
20 | 8 | Kevin Rampling
(10 rows)
Resumen¶
- Utilice la sintaxis
WITH RECURSIVEpara definir una consulta recursiva. - Utilice una consulta recursiva para tratar estructuras de datos jerárquicas o anidadas, como árboles o gráficos.