Skip to Content

Nous adressons toutes nos pensées à la famille de notre ami Jérôme !

http://www.forumsig.org/showthread.php/43488-Disparition-de-Phoenix

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

Commentaires

En passant, je précise

En passant, je précise l'utilisation du LIMIT.

Celui ci se contente de limiter le nombre de lignes en résultat.

Si vous souhaitez empêcher que la récursion ne fasse plus d'un certain nombre de boucles, voici une astuce :

WITH RECURSIVE zoneTampon(geom, nb) AS(
 SELECT ST_BUFFER(the_geom, 100), 1 AS nb FROM ma_table
UNION ALL
 SELECT ST_BUFFER(geom, 100), nb+1 FROM zoneTampon WHERE nb + 1 <=5
)
SELECT * FROM zoneTampon;

Ici, peut importe le nombre de lignes de la table initiale et le nombre de lignes résultat, la récursion ne se fera que 5 fois

RAPPEL : vos requêtes récursives doivent être pensées pour ne pas être infinies !

Problème de UNION récursif

En executant les requêtes suivantes :

drop table if exists result_zone;
WITH RECURSIVE zoneTampon(geom) AS(
SELECT ST_BUFFER(a.geom, 10000) FROM agences a
UNION
SELECT ST_BUFFER(zoneTampon.geom, 10000) FROM zoneTampon
)
SELECT * INTO result_zone FROM zoneTampon LIMIT 5;

Je ne peux obtenir mes cinq zones "tampon", j'obtiens le message d'erreur suivant :
"ERREUR: n'a pas pu implanté le UNION récursif"

La récursivité ne semble donc pas activée, je suis sûr PostgreSQL 9.0 / PostGIS 1.5

Bonjour, la récursivité est

Bonjour, la récursivité est bien activée.

Le problème vient en fait du UNION et de l'utilisation d'une colonne géométrique.

Pour supprimer les doublons (UNION au lieu de UNION ALL), postgresql a besoin de pouvoir comparer les résultats ce qu'il n'est pas capable de faire avec une colonne de géométrie.

Solution, l'emploi de UNION ALL qui ne cherche pas à supprimer d'éventuels doublons.

Donc :

WITH RECURSIVE zoneTampon(geom) AS(
SELECT ST_BUFFER(a.geom, 10000) FROM agences a
UNION ALL
SELECT ST_BUFFER(zoneTampon.geom, 10000) FROM zoneTampon
)
SELECT * FROM zoneTampon LIMIT 5;

 

EDIT : du coup, j'ai changé le UNION de l'article par un UNION ALL dans l'exemple des zones tampons concentriques.

Merci beaucoup pour ces

Merci beaucoup pour ces précisions ! Effectivement j'avais testé avec le UNION ALL et ça fonctionnait mais je ne voyais pas l'origine du blocage...

précision

Petite précision, pour générer la suite des puissances de 2 inférieures à 100, il faut la requête suivante :
WITH RECURSIVE t(nombre) AS (
VALUES (2)
UNION ALL
SELECT nombre * 2 FROM t WHERE nombre * 2 < 100
)
SELECT nombre FROM t

Avec celle qui est donnée on obtient la suite des puissances de deux qui ont comme exposant des puissances de deux.

En effet, merci pour cette

En effet, merci pour cette correction (répercutée dans l'article).

Poster un nouveau commentaire

Le contenu de ce champ sera maintenu privé et ne sera pas affiché publiquement.