PostgreSQL – Consulta recursiva

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: