PostgreSQL proporciona la instrucción WITH que admite el diseño de consultas auxiliares, también conocidas como CTE (Common Table Expressions). Una consulta recursiva es una consulta que hace referencia a una CTE recursiva. Las consultas recursivas son útiles en muchas circunstancias, como para consultar datos jerárquicos como la estructura organizativa, el seguimiento del linaje, etc.
Sintaxis:
WITH RECURSIVE cte_name AS( CTE_query_definition <-- non-recursive term UNION [ALL] CTE_query definition <-- recursive term ) SELECT * FROM cte_name;
Analicemos la sintaxis anterior:
- El término no recursivo es una definición de consulta CTE que forma el conjunto de resultados base de la estructura CTE.
- El término recursivo puede ser una o más definiciones de consulta CTE unidas con el término no recursivo a través del operador UNION o UNION ALL. El término recursivo hace referencia al propio nombre CTE.
- La recursión se detiene cuando no se devuelven filas de la iteración anterior.
Primero, creamos una tabla de muestra usando los siguientes comandos para realizar ejemplos:
CREATE TABLE employees ( employee_id serial PRIMARY KEY, full_name VARCHAR NOT NULL, manager_id INT );
Luego insertamos datos en nuestra tabla de empleados de la siguiente manera:
INSERT INTO employees ( employee_id, full_name, manager_id ) VALUES (1, 'M.S Dhoni', NULL), (2, 'Sachin Tendulkar', 1), (3, 'R. Sharma', 1), (4, 'S. Raina', 1), (5, 'B. Kumar', 1), (6, 'Y. Singh', 2), (7, 'Virender Sehwag ', 2), (8, 'Ajinkya Rahane', 2), (9, 'Shikhar Dhawan', 2), (10, 'Mohammed Shami', 3), (11, 'Shreyas Iyer', 3), (12, 'Mayank Agarwal', 3), (13, 'K. L. Rahul', 3), (14, 'Hardik Pandya', 4), (15, 'Dinesh Karthik', 4), (16, 'Jasprit Bumrah', 7), (17, 'Kuldeep Yadav', 7), (18, 'Yuzvendra Chahal', 8), (19, 'Rishabh Pant', 8), (20, 'Sanju Samson', 8);
Ahora que la tabla está lista, podemos ver algunos ejemplos.
Ejemplo 1:
la siguiente consulta devuelve todos los subordinados del gerente con la identificación 3.
WITH RECURSIVE subordinates AS ( SELECT employee_id, manager_id, full_name FROM employees WHERE employee_id = 3 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;
Producción:
Ejemplo 2:
la siguiente consulta devuelve todos los subordinados del gerente con la identificación 4.
WITH RECURSIVE subordinates AS ( SELECT employee_id, manager_id, full_name FROM employees WHERE employee_id = 4 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;
Producción:
Publicación traducida automáticamente
Artículo escrito por RajuKumar19 y traducido por Barcelona Geeks. The original can be accessed here. Licence: CCBY-SA