PostgreSQL : focus sur les requêtes SQL récursives

En SQL, il existe la syntaxe WITH.

Cette syntaxe permet les "Common Table Expression" (CTE ou Table d' Expression Partagées en français).

En clair, elle permet de créer des vues (vue au sens SGBD) mais locales, c'est à dire utilisables uniquement dans la requête en cours.

Cette syntaxe permet donc d'inclure de manière plus lisible des sous-requêtes :

WITH 
   sous_requete1 AS (SELECT ..... FROM .....),
   sous_requete2 AS (SELECT .... FROM .....)
SELECT * FROM ma_table, sous_requete1, sous_requete2 ....

Cette syntaxe, même si elle permet une grande lisibilité de la requête, n'apporte que peu de fonctionnalité supplémentaire puisque la requête ci-dessus est équivalente à :

SELECT * FROM ma_table, (SELECT ..... FROM ......) AS sous_requete1, (SELECT ..... FROM ......) AS sous_requete2 ......

Par contre, utilisez la car elle permet de structurer la requête et ainsi de rendre beaucoup plus aisée la création de requêtes complexes.

De plus, si on a besoin d'utiliser la même sous requête plusieurs fois dans la requête, l'utilisation du WITH permet d'exprimer la sous-requête une unique fois.

Néanmoins, la syntaxe WITH, bien que déjà très utile, ne se limite pas à çà.

La norme SQL:1999 a permis de transposer en SQL la récursivité en étendant la syntaxe WITH.

Concrètement,en ajoutant le mot clé RECURSIVE, on peut dans l'expression d'une CTE faire appel à elle même.

Pour mieux comprendre voici un exemple :

WITH RECURSIVE t(nombre) AS (
    VALUES (2)
  UNION ALL
    SELECT 2 * nombre FROM t WHERE 2 * nombre < 100
)
SELECT nombre FROM t;

Cet exemple génère la suite des puissances de 2 inférieures à 100.

Une requête recursive s'écrit donc ainsi :

WITH RECURSIVE recursion(liste_champ) AS(
  SELECT liste_champ FROM table
 UNION ALL
  SELECT liste_champ FROM recursion, table WHERE condition
)
SELECT * FROM recursion;

On fait donc une CTE dans laquelle on a une union, la deuxième partie de l'UNION faisant appel à la CTE elle même. Attention aux requêtes infinies !!!! N'hésitez surtout pas à mettre dans la requête d'appel un LIMIT (ci dessus par exemple : SELECT * FROM recursion LIMIT 1000) au cas où, même si votre requête doit impérativement être écrite de manière à éviter toute récursion infinie.

Enchainons avec un exemple un peu plus pratique. Supposons une table employes qui comprend 3 champs : id_employe, nom_employe et id_superieur.

Dans cette table, le champ id_superieur fait référence au supérieur direct de l'employé qui est lui-même un employé. L'utilisation de requêtes récursives va ici nous permettre des choses très pratiques :

WITH RECURSIVE hierarchie(id_employe, nom_employe, id_superieur) AS (
  SELECT id_employe, nom_employe, id_superieur
    FROM employes WHERE id_employe = 3
  UNION ALL
  SELECT e.id_employe, e.nom_employe, e.id_superieur
    FROM hierarchie AS h,employes AS e 
    WHERE h.id_superieur = e.id_employe
)
SELECT * FROM hierarchie;

Cette requête renvoie ici l'arbre hiérachique de l'employé 3.

On aurait aussi pu calculer le nombre d'employés sous les ordres du chef de service 18 :

WITH RECURSIVE souslesordres(id_employe, nom_employe, id_superieur) AS (
 SELECT id_employe, nom_employe, id_superieur
  FROM employes WHERE id_employe = 18
UNION ALL
  SELECT e.id_employe, e.nom_employe, e.id_superieur
  FROM souslesordres  AS ss, employes AS e 
  WHERE e.id_superieur = ss.id_employe
)
SELECT count(*) - 1 FROM souslesordres;

(le moins 1 pour soustraire le chef de service 18 lui-même). 

N'oubliez pas la différence en SQL entre le UNION ALL et le UNION :

Si on avait fait la requête de hierarchie ci-dessus en filtrant sur 2 employés (WHERE id_employe IN(3, 18)) mais de niveaux hiérarchiques différents, nous aurions eu des doublons. L'utilisation d'UNION seul évite les doublons.

On aurait aussi pu appliquer une requête récursive sur une table géométrique pour générer des zones tampons concentriques par exemple :

WITH RECURSIVE zoneTampon(geom) AS(
 SELECT ST_BUFFER(the_geom, 100) FROM ma_table
UNION ALL
 SELECT ST_BUFFER(geom, 100) FROM zoneTampon
)
SELECT * FROM zoneTampon LIMIT 5;

Même avec les exemples assez simples vus ici, on se rend vite compte de la puissance des requêtes récursives.

A vos claviers !!!!

Site officiel : Le site de PostgreSQL
Site officiel : PostgreSQL : documentation du WITH
Autres Liens : Un très bon article sur la récursivité SQL