7.8. Requêtes WITH (Common Table Expressions)

L'intérêt de SELECT dans WITH est de diviser des requêtes complexes en parties plus simples. Un exemple est:

WITH ventes_regionales AS (
        SELECT region, SUM(montant) AS ventes_totales
        FROM commandes
        GROUP BY region
     ), meilleures_regions AS (
        SELECT region
        FROM ventes_regionales
        WHERE ventes_totales > (SELECT SUM(ventes_totales)/10 FROM ventes_regionales)
     )
SELECT region,
       produit,
       SUM(quantite) AS unites_produit,
       SUM(montant) AS ventes_produit
FROM commandes
WHERE region IN (SELECT region FROM meilleures_regions)
GROUP BY region, produit;

qui affiche les totaux de ventes par produit seulement dans les régions ayant les meilleures ventes. La clause WITH définit deux ordres auxiliaires appelés ventes_regionales et meilleures_regions, où la sortie de ventes_regionales est utilisée dans meilleures_regions et la sortie de meilleures_regions est utilisée dans la requête SELECT primaire. Cet exemple aurait pu être écrit sans WITH, mais aurait alors nécessité deux niveaux de sous-SELECT imbriqués. Les choses sont un peu plus faciles à suivre de cette façon.

Le modificateur optionnel RECURSIVE fait passer WITH du statut de simple aide syntaxique à celui de quelque chose qu'il serait impossible d'accomplir avec du SQL standard. Grâce à RECURSIVE, une requête WITH peut utiliser sa propre sortie. Un exemple très simple se trouve dans cette requête, qui ajoute les nombres de 1 à 100 :

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

La forme générale d'une requête WITH est toujours un terme non récursif, puis UNION (ou UNION ALL), puis un terme récursif. Seul le terme récursif peut contenir une référence à la sortie propre de la requête. Une requête de ce genre est exécutée comme suit :

Dans l'exemple précédent, la table de travail a un seul enregistrement à chaque étape, et il prend les valeurs de 1 à 100 en étapes successives. À la centième étape, il n'y a plus de sortie en raison de la clause WHERE, ce qui met fin à la requête.

Les requêtes récursives sont utilisées généralement pour traiter des données hiérarchiques ou sous forme d'arbres. Cette requête est un exemple utile pour trouver toutes les sous-parties directes et indirectes d'un produit, si seule une table donne toutes les inclusions immédiates :

WITH RECURSIVE parties_incluses(sous_partie, partie, quantite) AS (
    SELECT sous_partie, partie, quantite FROM parties WHERE partie = 'notre_produit'
  UNION ALL
    SELECT p.sous_partie, p.partie, p.quantite
    FROM parties_incluses pr, parties p
    WHERE p.partie = pr.sous_partie
  )
SELECT sous_partie, SUM(quantite) as quantite_totale
FROM parties_incluses
GROUP BY sous_partie

Quand on travaille avec des requêtes récursives, il est important d'être sûr que la partie récursive de la requête finira par ne retourner aucun enregistrement, au risque sinon de voir la requête boucler indéfiniment. Quelquefois, utiliser UNION à la place de UNION ALL peut résoudre le problème en supprimant les enregistrements qui doublonnent ceux déjà retournés. Toutefois, souvent, un cycle ne met pas en jeu des enregistrements de sortie qui sont totalement des doublons : il peut s'avérer nécessaire de vérifier juste un ou quelques champs, afin de s'assurer que le même point a déjà été atteint précédemment. La méthode standard pour gérer ces situations est de calculer un tableau de valeurs déjà visitées. Par exemple, observez la requête suivante, qui parcourt une table graphe en utilisant un champ lien :

WITH RECURSIVE parcourt_graphe(id, lien, donnee, profondeur) AS (
        SELECT g.id, g.lien, g.donnee, 1
        FROM graphe g
      UNION ALL
        SELECT g.id, g.lien, g.donnee, sg.profondeur + 1
        FROM graphe g, parcourt_graphe sg
        WHERE g.id = sg.lien
)
SELECT * FROM parcourt_graphe;
      

Cette requête va boucler si la liaison lien contient des boucles. Parce que nous avons besoin de la sortie « profondeur », simplement remplacer UNION ALL par UNION ne résoudra pas le problème. À la place, nous avons besoin d'identifier si nous avons atteint un enregistrement que nous avons déjà traité pendant notre parcours des liens. Nous ajoutons deux colonnes chemin et boucle à la requête :

WITH RECURSIVE parcourt_graphe(id, lien, donnee, profondeur, chemin, boucle) AS (
        SELECT g.id, g.lien, g.donnee, 1,
          ARRAY[g.id],
          false
        FROM graphe g
      UNION ALL
        SELECT g.id, g.lien, g.donnee, sg.profondeur + 1,
          chemin || g.id,
          g.id = ANY(chemin)
        FROM graphe g, parcourt_graphe sg
        WHERE g.id = sg.lien AND NOT boucle
)
SELECT * FROM parcourt_graphe;
      

En plus de prévenir les boucles, cette valeur de tableau est souvent pratique en elle-même pour représenter le « chemin » pris pour atteindre chaque enregistrement.

De façon plus générale, quand plus d'un champ a besoin d'être vérifié pour identifier une boucle, utilisez un tableau d'enregistrements. Par exemple, si nous avions besoin de comparer les champs f1 et f2 :

WITH RECURSIVE parcourt_graphe(id, lien, donnee, profondeur, chemin, boucle) AS (
        SELECT g.id, g.lien, g.donnee, 1,
          ARRAY[ROW(g.f1, g.f2)],
          false
        FROM graphe g
      UNION ALL
        SELECT g.id, g.lien, g.donnee, sg.profondeur + 1,
          chemin || ROW(g.f1, g.f2),
          ROW(g.f1, g.f2) = ANY(chemin)
        FROM graphe g, parcourt_graphe sg
        WHERE g.id = sg.lien AND NOT boucle
)
SELECT * FROM parcourt_graphe;
      

Si vous n'êtes pas certain qu'une requête puisse boucler, une astuce pratique pour la tester est d'utiliser LIMIT dans la requête parente. Par exemple, cette requête bouclerait indéfiniment sans un LIMIT :

WITH RECURSIVE t(n) AS (
    SELECT 1
  UNION ALL
    SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;
      

Ceci fonctionne parce que l'implémentation de PostgreSQL™ n'évalue que le nombre d'enregistrements de la requête WITH récupérés par la requête parente. L'utilisation de cette astuce en production est déconseillée parce que d'autres systèmes pourraient fonctionner différemment. Par ailleurs, cela ne fonctionnera pas si vous demandez à la requête externe de trier les résultats de la requête récursive, ou si vous les joignez à une autre table, parce dans ces cas, la requête extérieure essaiera habituellement de récupérer toute la sortie de la requête WITH de toute façon.

Une propriété intéressante des requêtes WITH est qu'elles ne sont évaluées qu'une seule fois par exécution de la requête parente ou des requêtes WITH sœurs. Par conséquent, les calculs coûteux qui sont nécessaires à plusieurs endroits peuvent être placés dans une requête WITH pour éviter le travail redondant. Un autre intérêt peut être d'éviter l'exécution multiple d'une fonction ayant des effets de bord. Toutefois, le revers de la médaille est que l'optimiseur est moins capable d'extrapoler les restrictions de la requête parente vers une requête WITH que vers une sous-requête classique. La requête WITH sera généralement exécutée telle quelle, sans suppression d'enregistrements, que la requête parente devra supprimer ensuite. (Mais, comme mentionné précédemment, l'évaluation pourrait s'arrêter rapidement si la (les) référence(s) à la requête ne demande(nt) qu'un nombre limité d'enregistrements).

Les exemples précédents ne montrent que des cas d'utilisation de WITH avec SELECT, mais on peut les attacher de la même façon à un INSERT, UPDATE, ou DELETE. Dans chaque cas, le mécanisme fournit en fait des tables temporaires auxquelles on peut faire référence dans la commande principale.

Vous pouvez utiliser des ordres de modification de données (INSERT, UPDATE, ou DELETE) dans WITH. Cela vous permet d'effectuer plusieurs opérations différentes dans la même requête. Par exemple:

WITH lignes_deplacees AS (
    DELETE FROM produits
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO log_produits
SELECT * FROM lignes_deplacees;
      

Cette requête déplace les enregistrements de produits vers log_produits. Le DELETE du WITH supprime les enregistrements spécifiés de produits, en retournant leurs contenus par la clause RETURNING; puis la requête primaire lit cette sortie et l'insère dans log_produits.

Un point important à noter de l'exemple précédent est que la clause WITH est attachée à l'INSERT, pas au sous-SELECT de l' INSERT. C'est nécessaire parce que les ordres de modification de données ne sont autorisés que dans les clauses WITH qui sont attachées à l'ordre de plus haut niveau. Toutefois, les règles de visibilité normales de WITH s'appliquent, il est donc possible de faire référence à la sortie du WITH dans le sous-SELECT.

Les ordres de modification de données dans WITH ont habituellement des clauses RETURNING (voir Section 6.4, « Renvoyer des données provenant de lignes modifiées »), comme dans l'exemple précédent. C'est la sortie de la clause RETURNING, pas la table cible de l'ordre de modification de données, qui forme la table temporaire à laquelle on pourra faire référence dans le reste de la requête. Si un ordre de modification de données dans WITH n'a pas de clause RETURNING, alors il ne produit pas de table temporaire et ne peut pas être utilisé dans le reste de la requête. Un ordre de ce type sera toutefois exécuté. En voici un exemple (dénué d'intérêt):

WITH t AS (
    DELETE FROM foo
)
DELETE FROM bar;
      

Cet exemple supprimerait tous les éléments des tables foo et bar. Le nombre d'enregistrements retourné au client n'inclurait que les enregistrements supprimés de bar.

Les autoréférences récursives dans les ordres de modification de données ne sont pas autorisées. Dans certains cas, il est possible de contourner cette limitation en faisant référence à la sortie d'un WITH, par exemple:

WITH RECURSIVE pieces_incluses(sous_piece, piece) AS (
    SELECT sous_piece, piece FROM pieces WHERE piece = 'notre_produit'
  UNION ALL
    SELECT p.sous_piece, p.piece
    FROM pieces_incluses pr, pieces p
    WHERE p.piece = pr.sous_piece
  )
DELETE FROM pieces
  WHERE piece IN (SELECT piece FROM pieces_incluses);
      

Cette requête supprimerait toutes les pièces directes et indirectes d'un produit.

Les ordres de modification de données dans WITH sont exécutés exactement une fois, et toujours jusqu'à la fin, indépendamment du fait que la requête primaire lise tout (ou même une partie) de leur sortie. Notez que c'est différent de la règle pour SELECT dans WITH: comme précisé dans la section précédente, l'exécution d'un SELECT n'est poursuivie que tant que la requête primaire consomme sa sortie.

Les sous-requêtes du WITH sont toutes exécutées simultanément et simultanément avec la requête principale. Par conséquent, quand vous utilisez un ordre de modification de données avec WITH, l'ordre dans lequel les mises à jour sont effectuées n'est pas prévisible. Toutes les requêtes sont exécutées dans le même instantané (voyez Chapitre 13, Contrôle d'accès simultané), elles ne peuvent donc pas voir les effets des autres sur les tables cibles. Ceci rend sans importance le problème de l'imprévisibilité de l'ordre des mises à jour, et signifie que RETURNING est la seule façon de communiquer les modifications entre les différentes sous-requêtes WITH et la requête principale. En voici un exemple:

WITH t AS (
    UPDATE produits SET prix = prix * 1.05
    RETURNING *
)
SELECT * FROM produits;
      

Le SELECT externe retournerait les prix originaux avant l'action de UPDATE, alors qu'avec  :

WITH t AS (
    UPDATE produits SET prix = prix * 1.05
    RETURNING *
)
SELECT * FROM t;
      

le SELECT externe retournerait les données mises à jour.

Essayer de mettre à jour le même enregistrement deux fois dans le même ordre n'est pas supporté. Seule une des deux modifications a lieu, mais il n'est pas aisé (et quelquefois impossible) de déterminer laquelle. Ceci s'applique aussi pour la suppression d'un enregistrement qui a déjà été mis à jour dans le même ordre : seule la mise à jour est effectuée. Par conséquent, vous devriez éviter en règle générale de mettre à jour le même enregistrement deux fois en un seul ordre. En particulier, évitez d'écrire des sous-requêtes qui modifieraient les mêmes enregistrements que la requête principale ou une autre sous-requête. Les effets d'un ordre de ce type seraient imprévisibles.

À l'heure actuelle, les tables utilisées comme cibles d'un ordre modifiant les données dans un WITH ne doivent avoir ni règle conditionnelle, ni règle ALSO, ni une règle INSTEAD qui génère plusieurs ordres.