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

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;

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;

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;

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;

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;

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;
      

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;
      

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;
      

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;

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

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. Néanmoins, le revers de la médaille est que l'optimiseur n'est pas en mesure de faire descendre les restrictions de la requête parent dans une requête WITH à références multiples, car cela pourrait affecter toutes les utilisations de la sortie de la requête WITH alors que cela ne devrait en affecter qu'une seule. 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, DELETE ou MERGE. 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, mais pas MERGE) 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.