Aprenda la consulta recursiva de PostgreSQL Por ejemplo

Resumen: en este tutorial, aprenderá sobre la consulta recursiva de PostgreSQL utilizando expresiones de tabla comunes recursivas o CTE.

Introducción a la consulta recursiva de PostgreSQL

PostgreSQL proporciona la instrucción WITH que le permite construir instrucciones auxiliares para su uso en una consulta.

Estas instrucciones a menudo se conocen como expresiones de tabla comunes o CTE. Las CTE son como tablas temporales que solo existen durante la ejecución de la consulta.

Una consulta recursiva es una consulta que se refiere a un CTE recursivo. Las consultas recursivas son útiles en muchas situaciones, como consultar datos jerárquicos como la estructura organizativa, la lista de materiales, etc.

A continuación se ilustra la sintaxis de un CTE recursivo:

Code language: SQL (Structured Query Language) (sql)

Un CTE recursivo tiene tres elementos:

• Término no recursivo: el término no recursivo es una definición de consulta CTE que forma el conjunto de resultados base de la estructura CTE.
• Término recursivo: el término recursivo es una o más definiciones de consulta CTE unidas al término no recursivo utilizando el operador UNION o UNION ALL. El término recursivo hace referencia al propio nombre de CTE.
• Comprobación de terminación: la recursión se detiene cuando no se devuelven filas de la iteración anterior.

PostgreSQL ejecuta un CTE recursivo en la siguiente secuencia:

1. Ejecute el término no recursivo para crear el conjunto de resultados base (R0).
2. Ejecute el término recursivo con Ri como entrada para devolver el conjunto de resultados Ri + 1 como salida.
3. Repita el paso 2 hasta que se devuelva un conjunto vacío. (comprobación de terminación)
4. Devuelve el conjunto de resultados finales que es una UNIÓN o UNION ALL del conjunto de resultados R0, R1, R Rn

Ejemplo de consulta recursiva de PostgreSQL

Crearemos una nueva tabla para demostrar la consulta recursiva de PostgreSQL.

Code language: SQL (Structured Query Language) (sql)

La tabla employees tiene tres columnas: employee_id, manager_id y full_name. La columna manager_id especifica el id de administrador de un empleado.

La siguiente instrucción inserta datos de muestra en la tabla employees.

Code language: PHP (php)

La siguiente consulta devuelve todos los subordinados del gestor con el id 2.

Code language: SQL (Structured Query Language) (sql)

Cómo funciona:

• El CTE recursivo, subordinados, define un término no recursivo y un término recursivo.
• El término no recursivo devuelve el conjunto de resultados base R0 que es el empleado con el id 2.

Code language: SQL (Structured Query Language) (sql)

El término recursivo devuelve los subordinados directos del id de empleado 2. Este es el resultado de la unión entre la tabla de empleados y los CTE subordinados. La primera iteración del término recursivo devuelve el siguiente conjunto de resultados:

Code language: SQL (Structured Query Language) (sql)

PostgreSQL ejecuta el término recursivo repetidamente. La segunda iteración del miembro recursivo utiliza el conjunto de resultados anterior como valor de entrada y devuelve este conjunto de resultados:

Code language: SQL (Structured Query Language) (sql)

La tercera iteración devuelve un conjunto de resultados vacío porque no hay informes de empleado al empleado con el id 16, 17, 18, 19 y 20.

PostgreSQL devuelve el conjunto de resultados finales que es la unión de todos los conjuntos de resultados en la primera y segunda iteraciones generadas por los términos no recursivos y recursivos.

En este tutorial, ha aprendido a usar los CTE recursivos para construir las consultas recursivas de PostgreSQL.