PostgreSQL: consulta recursiva mediante CTE

Estrictamente hablando, este proceso es iteración, no recursión, pero RECURSIVO es la terminología elegida por el comité de estándares de SQL.

La estructura general de la consulta recursiva de Postgres contiene,

1. Declaración de selección no recursiva
2. Unión o unión de todos
3. Declaración de selección recursiva

WITH RECURSIVE name_cte AS (
SELECT statement  /* non-recursive statement */
UNION [ALL]
SELECT statement  /*recursive statement referencing the above select statement */
)
SELECT * FROM name_cte;

Cómo funciona una consulta recursiva de Postres:

1. Evalúa sentencias no recursivas y crea una tabla temporal
2. Evaluar términos recursivos y agregarlos a la tabla temporal
3. Repita el paso 2 hasta que la mesa de trabajo esté vacía.

La diferencia entre union y union all es que union all permite duplicados union no permitirá ningún duplicado.

Ejemplo:

WITH RECURSIVE tens AS (
   SELECT 1 as n
 UNION ALL
   SELECT n+1 FROM tens
)
SELECT n FROM tens limit 10;

Este es el ejemplo básico de la consulta recursiva de Postres que imprime los primeros 10 números naturales.

Postres consulta recursiva para hallar el factorial de un número natural:

WITH RECURSIVE fact (n, factorial)
AS (
    SELECT 1 as n, 5 as factorial
union all
    SELECT n+1, factorial*n FROM fact where n < 5
)
SELECT * FROM fact;

Esta consulta genera dos tablas, una con los primeros cinco números naturales y la otra tabla con los cálculos que se realizan para encontrar el factorial.

Podemos imprimir solo la última fila, pero aquí podemos ver cómo se lleva a cabo la iteración y el cálculo.

 Postres consulta recursiva para imprimir series de Fibonacci:

WITH RECURSIVE fibb
AS (
    SELECT 1::bigint as n, 0::bigint as a, 1::bigint as b
UNION ALL
    SELECT n+1, b as a, (a+b) as b FROM fibb
)
SELECT b FROM fibb limit 10;

Esto imprime la serie de Fibonacci hasta 10.

Con la ayuda de la consulta recursiva de Postgres, podemos encontrar la jerarquía organizacional:.

Para crear una tabla:

INSERT INTO employees (
employee_id,
full_name,
manager_id
)
VALUES
(1, 'Abhi', NULL),
(2, 'Bhargav', 1),
(3, 'Chay', 1),
(4, 'Dravid', 1),
(5, 'Erin', 1),
(6, 'Ford', 2),
(7, 'Gagan', 2),
(8, 'Harry', 3),
(9, 'Isaac', 3),
(10, 'Jack', 4),
(11, 'Kiran', 5);

Abhi es el jefe, estará en el primer nivel. Bhargav, Chay, Dravid, Erin están en el siguiente nivel y el resto será el último nivel.

Consulta:

WITH RECURSIVE subordinates AS (
SELECT employee_id, manager_id, full_name, 0 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.full_name, level+1
FROM employees e
INNER JOIN subordinates s ON s.employee_id = e.manager_id
)  
SELECT * FROM subordinates;

La salida será: