Apprenez la requête récursive PostgreSQL Par Exemple

Résumé : dans ce tutoriel, vous découvrirez la requête récursive PostgreSQL à l’aide d’expressions de table communes récursives ou de CTE.

Introduction à la requête récursive PostgreSQL

PostgreSQL fournit l’instruction WITH qui vous permet de construire des instructions auxiliaires à utiliser dans une requête.

Ces instructions sont souvent appelées expressions de table courantes ou CTE. Les CTE sont comme des tables temporaires qui n’existent que pendant l’exécution de la requête.

Une requête récursive est une requête qui fait référence à un CTE récursif. Les requêtes récursives sont utiles dans de nombreuses situations telles que l’interrogation de données hiérarchiques telles que la structure organisationnelle, la nomenclature, etc.

Ce qui suit illustre la syntaxe d’un CTE récursif:

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

Un CTE récursif a trois éléments:

• Terme non récursif : le terme non récursif est une définition de requête CTE qui forme l’ensemble de résultats de base de la structure CTE.
• Terme récursif: le terme récursif est une ou plusieurs définitions de requête CTE jointes au terme non récursif à l’aide de l’opérateur UNION ou UNION ALL. Le terme récursif fait référence au nom CTE lui-même.
• Vérification de terminaison : la récursivité s’arrête lorsqu’aucune ligne n’est renvoyée de l’itération précédente.

PostgreSQL exécute un CTE récursif dans la séquence suivante:

1. Exécutez le terme non récursif pour créer l’ensemble de résultats de base (R0).
2. Exécutez un terme récursif avec Ri en entrée pour renvoyer l’ensemble de résultats Ri + 1 en sortie.
3. Répétez l’étape 2 jusqu’à ce qu’un ensemble vide soit renvoyé. (vérification de terminaison)
4. Renvoie le jeu de résultats final qui est une UNION ou UNION ALL du jeu de résultats R0, R1, Rn Rn

Exemple de requête récursive PostgreSQL

Nous allons créer une nouvelle table pour démontrer la requête récursive PostgreSQL.

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

La table employees comporte trois colonnes : employee_id, manager_id et full_name. La colonne manager_id spécifie l’ID de gestionnaire d’un employé.

L’instruction suivante insère des exemples de données dans la table employees.

Code language: PHP (php)

La requête suivante renvoie tous les subordonnés du gestionnaire avec l’id 2.

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

Comment ça marche:

• Le CTE récursif, subordonnés, définit un terme non récursif et un terme récursif.
• Le terme non récursif renvoie l’ensemble de résultats de base R0 qui est l’employé avec l’id 2.

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

Le terme récursif renvoie le ou les subordonnés directs de l’employé id 2. Ceci est le résultat de la jonction entre la table des employés et les subordonnés CTE. La première itération du terme récursif renvoie l’ensemble de résultats suivant:

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

PostgreSQL exécute le terme récursif à plusieurs reprises. La deuxième itération du membre récursif utilise l’ensemble de résultats ci-dessus comme valeur d’entrée et renvoie cet ensemble de résultats:

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

La troisième itération renvoie un ensemble de résultats vide car il n’y a pas d’employé faisant rapport à l’employé avec l’id 16, 17, 18, 19 et 20.

PostgreSQL renvoie l’ensemble de résultats final qui est l’union de tous les ensembles de résultats des première et deuxième itérations générées par les termes non récursifs et récursifs.

Dans ce tutoriel, vous avez appris à utiliser les CTE récursifs pour construire les requêtes récursives PostgreSQL.