Documentation PostgreSQL 14.22 » Langage SQL » Requêtes » Requêtes `WITH` (Common Table Expressions)

Précédent | Niveau supérieur | Suivant
7.7. Listes `VALUES` | Sommaire | Chapitre 8. Types de données

## 7.8. Requêtes `WITH` (Common Table Expressions)

`WITH` fournit un moyen d'écrire des ordres auxiliaires pour les utiliser dans des requêtes plus importantes. Ces requêtes, qui sont souvent appelées Common Table Expressions ou CTE, peuvent être vues comme des tables temporaires qui n'existent que pour une requête. Chaque ordre auxiliaire dans une clause `WITH` peut être un `SELECT`, `INSERT`, `UPDATE`, ou `DELETE`; et la clause `WITH` elle-même est attachée à un ordre primaire qui peut lui aussi être un `SELECT`, `INSERT`, `UPDATE`, ou `DELETE`.

### 7.8.1. `SELECT` dans `WITH`

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.

### 7.8.2. Requêtes récursives

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 :

Évaluation de requête récursive

1. Évaluer le terme non récursif. Pour `UNION` (mais pas `UNION ALL`), supprimer les enregistrements en double. Inclure le reste dans le résultat de la requête récursive et le mettre aussi dans une table temporaire de travail (working table.)

2. Tant que la table de travail n'est pas vide, répéter ces étapes :

   1. Évaluer le terme récursif, en substituant à la référence récursive le contenu courant de la table de travail. Pour `UNION` (mais pas `UNION ALL`), supprimer les doublons, ainsi que les enregistrements en doublon des enregistrements déjà obtenus. Inclure les enregistrements restants dans le résultat de la requête récursive, et les mettre aussi dans une table temporaire intermédiaire (intermediate table).

   2. Remplacer le contenu de la table de travail par celui de la table intermédiaire, puis supprimer la table intermédiaire.

### Note

Dans son appellation stricte, ce processus est une itération, pas une récursion, mais `RECURSIVE` est la terminologie choisie par le comité de standardisation de SQL. Alors que `RECURSIVE` autorise la spécification récursive des requêtes, en interne, ce type de requêtes est évalué itérativement.

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 * pr.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

#### 7.8.2.1. Ordre de recherche

Lors du calcul d'un parcours d'arbre en utilisant une requête récursive, vous pourriez vouloir trier les résultats soit en depth-first soit en breadth-first. Ceci peut se faire en calculant une colonne de tri parmi les autres colonnes de données et en l'utilisant pour trier les résultats à la fin. Notez que cela ne contrôle pas réellement dans qel ordre l'évaluation de la requête visite les lignes ; ceci est toujours dépendant de l'implémentation SQL. Cette approche fournit simplement une façon agréable de trier les résultats après coup.

Pour créer un ordre depth-first, nous calculons pour chaque résultat un tableau de lignes que nous avons déjà visité. Par exemple, considérez la requête suivante qui recherche dansune table `tree` en utilisant un champ `link` :

    WITH RECURSIVE search_tree(id, link, data) AS (
        SELECT t.id, t.link, t.data
        FROM tree t
      UNION ALL
        SELECT t.id, t.link, t.data
        FROM tree t, search_tree st
        WHERE t.id = st.link
    )
    SELECT * FROM search_tree;

Pour ajouter l'information de tri depth-first, vous pouvez écrire ceci :

    WITH RECURSIVE search_tree(id, link, data, path) AS (
        SELECT t.id, t.link, t.data, ARRAY[t.id]
        FROM tree t
      UNION ALL
        SELECT t.id, t.link, t.data, path || t.id
        FROM tree t, search_tree st
        WHERE t.id = st.link
    )
    SELECT * FROM search_tree ORDER BY path;

Dans le cas général où plus d'un champ a besoin d'être utilisé pour identifier une ligne, utilisez un tableau de lignes. Par exemple, si vous avez besoin de tracer les champs `f1` et `f2` :

    WITH RECURSIVE search_tree(id, link, data, path) AS (
        SELECT t.id, t.link, t.data, ARRAY[ROW(t.f1, t.f2)]
        FROM tree t
      UNION ALL
        SELECT t.id, t.link, t.data, path || ROW(t.f1, t.f2)
        FROM tree t, search_tree st
        WHERE t.id = st.link
    )
    SELECT * FROM search_tree ORDER BY path;

### Astuce

Omettez la syntaxe `ROW()` dans le cas commun où seulement un champ a besoin d'être tracé. Ceci permet l'utilisation d'un tableau simple plutôt que d'un tableau de type composite, ce qui permet de gagner en efficacité.

Pour créer un ordre breadth-first, vous pouvez utiliser un colonne qui trace la profondeur de la recherche, par exemple :

    WITH RECURSIVE search_tree(id, link, data, depth) AS (
        SELECT t.id, t.link, t.data, 0
        FROM tree t
      UNION ALL
        SELECT t.id, t.link, t.data, depth + 1
        FROM tree t, search_tree st
        WHERE t.id = st.link
    )
    SELECT * FROM search_tree ORDER BY depth;

Pour obtenir un tri stable, ajoutez des colonnes de données comme colonnes secondaires de tri.

### Astuce

L'algorithme d'évaluation de la requête récursive produit sa sortie dans l'ordre de recherche breadth-first. Néanmoins, ceci est un détail d'implémentation et il n'est pas conseillé de se baser deuss. L'ordre des lignes à l'intérieur de chaque niveau n'est pas défini, donc un tri explicite pourrait être désiré dans tous les cas.

Une syntaxe native permet de calculer une colonne de tri depth-first ou breadth-first. Par exemple :

    WITH RECURSIVE search_tree(id, link, data) AS (
        SELECT t.id, t.link, t.data
        FROM tree t
      UNION ALL
        SELECT t.id, t.link, t.data
        FROM tree t, search_tree st
        WHERE t.id = st.link
    ) SEARCH DEPTH FIRST BY id SET ordercol
    SELECT * FROM search_tree ORDER BY ordercol;

    WITH RECURSIVE search_tree(id, link, data) AS (
        SELECT t.id, t.link, t.data
        FROM tree t
      UNION ALL
        SELECT t.id, t.link, t.data
        FROM tree t, search_tree st
        WHERE t.id = st.link
    ) SEARCH BREADTH FIRST BY id SET ordercol
    SELECT * FROM search_tree ORDER BY ordercol;

Cette syntaxe est étendue en interne pour obtenir quelque chose de similaire aux formes écrites manuellement. La clause `SEARCH` indique le type de recherche désiré, la liste des colonnes à tracer pour le tri et un nom de colonnes qui contiendra les données résults pouvant être utilisée pour le tri. Cette colonne sera ajoutée implicitement aux lignes en sortie de la CTE.

#### 7.8.2.2. Détection de cycles

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 de nouveau 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, 0
            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 `is_cycle` et `path` à la requête :

    WITH RECURSIVE search_graph(id, link, data, depth, is_cycle, path) AS (
        SELECT g.id, g.link, g.data, 0,
          false,
          ARRAY[g.id]
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1,
          g.id = ANY(path),
          path || g.id
        FROM graph g, search_graph sg
        WHERE g.id = sg.link AND NOT is_cycle
    )
    SELECT * FROM search_graph;

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 search_graph(id, link, data, depth, is_cycle, path) AS (
        SELECT g.id, g.link, g.data, 0,
          false,
          ARRAY[ROW(g.f1, g.f2)]
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1,
          ROW(g.f1, g.f2) = ANY(path),
          path || ROW(g.f1, g.f2)
        FROM graph g, search_graph sg
        WHERE g.id = sg.link AND NOT is_cycle
    )
    SELECT * FROM search_graph;

### Astuce

Omettez la syntaxe `ROW()` dans le cas courant où un seul champ a besoin d'être testé pour déterminer une boucle. Ceci permet, par l'utilisation d'un tableau simple plutôt que d'un tableau de type composite, de gagner en efficacité.

Il existe une syntaxe interne pour simplifier la détection de cycles. La requête ci-dessus peut aussi être écrite ainsi :

    WITH RECURSIVE search_graph(id, link, data, depth) AS (
        SELECT g.id, g.link, g.data, 1
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1
        FROM graph g, search_graph sg
        WHERE g.id = sg.link
    ) CYCLE id SET is_cycle USING path
    SELECT * FROM search_graph;

et elle sera réécrite en interne sous le forme ci-dessus. La clause `CYCLE` indique tout d'abord la liste des colonnes à tracer pour une détection de cycle, puis le nom de la colonne qui indiquera si un cycle a été détecté, et enfin le nom d'une autre colonne qui tracera le chemin. Les colonnes cycle et chemin seront automatiquement ajoutées aux lignes en sortie de la CTE.

### Astuce

La colonne du chemindu cycle est calculée de la même façon que l'affiche la colonne de tri depth-first dans la section précédente. Une requête peut avoir à la fois une clause `SEARCH` et une clause `CYCLE`, mais une spécification de recherche depth-first et une spécification de recherche de cyle vont créer des calculs redondants, donc il est plus efficace d'utiliser juste la clause `CYCLE` et trier par la colonne du chemin. Si un tri breadth-first est voulu, alors indiquer les deux, `SEARCH` and `CYCLE`, peut être utile.

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.

### 7.8.3. Matérialisation des CTE

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 the optimizer is not able to push restrictions from the parent query down into a multiply-referenced `WITH` query, since that might affect all uses of the `WITH` query's output when it should affect only one. 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).

Néanmoins, si une requête `WITH` est non récursive et qu'elle est libre de tout effet de bord (autrement dit un `SELECT` ne contenant aucune fonction volatile), alors elle peut être intégrée dans la requête parent, permettant ainsi une optimisation de la jointure sur les deux niveaux de la requête. Par défaut, ceci survient si la requête parente fait référence une seule fois à la requête `WITH` mais si elle y fait référence plusieurs fois. Vous pouvez surcharger cette décision en indiquant `MATERIALIZED` pour forcer un calcul séparé de la requête `WITH` ou en spécifiant `NOT MATERIALIZED` pour la forcer pour être intégrée dans la requête parente. Ce dernier choix risque de dupliquer des calculs sur la requête `WITH`, mais cela peut apporter un gain net si chaque utilisation de la requête `WITH` ne nécessite qu'une petite partie de la sortie complète de la requête `WITH`.

Un exemple simple de ces règles est le suivant :

    WITH w AS (
        SELECT * FROM big_table
    )
    SELECT * FROM w WHERE key = 123;

Cette requête `WITH` va être intégrée, produisant le même plan d'exécution que :

    SELECT * FROM big_table WHERE key = 123;

EN particulier, s'il existe un index sur `key`, il sera probablement utilisé pour récupérer les lignes pour lesquelles `key = 123`. D'un autre côté, dans

    WITH w AS (
        SELECT * FROM big_table
    )
    SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
    WHERE w2.key = 123;

la requête `WITH` sera matérialisée, produisant une copie temporaire de `big_table` qui est ensuite jointe avec elle-même -- sans intérêt pour un index. Cette requête sera exécutée bien plus efficacement s'il est écrite ainsi :

    WITH w AS NOT MATERIALIZED (
        SELECT * FROM big_table
    )
    SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
    WHERE w2.key = 123;

pour que les restrictions de la requête parent puissent être appliquées directement aux parcours de `big_table`.

Voici un exemple où `NOT MATERIALIZED` pourrait être indésirable :

    WITH w AS (
        SELECT key, very_expensive_function(val) as f FROM some_table
    )
    SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

Ici, la matérialisation de la requête `WITH` assure que la `very_expensive_function` est évaluée uniquement une fois par ligne de table, et non pas deux fois.

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.

### 7.8.4. Ordres de Modification de Données avec `WITH`

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), 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), 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.