7.8. Requêtes WITH (expressions de table communes)

7.8.1. SELECT dans WITH

La valeur fondamentale de SELECT dans WITH est de décomposer les requêtes compliquées en parties plus simples. Un exemple est :

WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales) )SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_salesFROM ordersWHERE region IN (SELECT region FROM top_regions)GROUP BY region, product;

qui affiche les totaux des ventes par produit dans les seules régions de vente les plus importantes. La clause WITH définit deux instructions auxiliaires nommées regional_sales et top_regions, où la sortie de regional_sales est utilisée dans top_regions et la sortie de top_regions est utilisée dans la requête SELECT primaire. Cet exemple aurait pu être écrit sans WITH, mais nous aurions eu besoin de deux niveaux de sous-SELECT imbriqués. C’est un peu plus facile à suivre de cette façon.

Le modificateur optionnel RECURSIVE transforme WITH d’une simple commodité syntaxique en une fonctionnalité qui accomplit des choses qui ne sont pas autrement possibles en SQL standard. En utilisant RECURSIVE, une requête WITH peut faire référence à sa propre sortie. Un exemple très simple est cette requête visant à additionner les entiers 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 récursive est toujours un terme non récursif, puis UNION (ou UNION ALL), puis un terme récursif, où seul le terme récursif peut contenir une référence à la propre sortie de la requête. Une telle requête est exécutée comme suit :

Dans l’exemple ci-dessus, la table de travail n’a qu’une seule ligne à chaque étape, et elle prend les valeurs de 1 à 100 dans les étapes successives. À la 100e étape, il n’y a pas de sortie à cause de la clause WHERE, et donc la requête se termine.

Les requêtes récursives sont généralement utilisées pour traiter des données hiérarchiques ou arborescentes. Un exemple utile est cette requête pour trouver toutes les sous-parties directes et indirectes d’un produit, étant donné seulement un tableau qui montre les inclusions immédiates:

WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part )SELECT sub_part, SUM(quantity) as total_quantityFROM included_partsGROUP BY sub_part

Lorsque l’on travaille avec des requêtes récursives, il est important de s’assurer que la partie récursive de la requête ne retournera finalement aucun tuples, sinon la requête bouclera indéfiniment. Parfois, l’utilisation de UNION au lieu de UNION ALL permet d’atteindre cet objectif en éliminant les lignes qui dupliquent les lignes de sortie précédentes. Cependant, il arrive souvent qu’un cycle n’implique pas des lignes de sortie entièrement dupliquées : il peut être nécessaire de vérifier un ou quelques champs pour voir si le même point a déjà été atteint auparavant. La méthode standard pour gérer de telles situations consiste à calculer un tableau des valeurs déjà visitées. Par exemple, considérez la requête suivante qui recherche un graphique de table en utilisant un champ de lien:

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)SELECT * FROM search_graph;

Cette requête tournera en boucle si les relations de lien contiennent des cycles. Comme nous avons besoin d’une sortie « en profondeur », le simple fait de remplacer UNION ALL par UNION n’élimine pas le bouclage. Au lieu de cela, nous devons reconnaître si nous avons atteint la même ligne à nouveau en suivant un chemin particulier de liens. Nous ajoutons deux colonnes chemin et cycle à la requête sujette aux boucles :

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

A part empêcher les cycles, la valeur du tableau est souvent utile en soi car elle représente le « chemin » emprunté pour atteindre toute ligne particulière.

Dans le cas général où plus d’un champ doit être vérifié pour reconnaître un cycle, utilisez un tableau de lignes. Par exemple, si nous avions besoin de comparer les champs f1 et f2:

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

Une astuce utile pour tester les requêtes lorsque vous n’êtes pas certain qu’elles risquent de boucler est de placer une LIMITE dans la requête parent. Par exemple, cette requête bouclerait indéfiniment sans le LIMIT:

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

Cela fonctionne parce que l’implémentation de PostgreSQL évalue seulement autant de lignes d’une requête WITH que celles qui sont effectivement récupérées par la requête parent. L’utilisation de cette astuce en production n’est pas recommandée, car d’autres systèmes pourraient fonctionner différemment. De plus, cela ne fonctionnera généralement pas si vous faites en sorte que la requête externe trie les résultats de la requête récursive ou les joigne à une autre table, car dans de tels cas, la requête externe essaiera généralement de récupérer toute la sortie de la requête WITH de toute façon.

Une propriété utile des requêtes WITH est qu’elles ne sont évaluées qu’une seule fois par exécution de la requête parent, même si elles sont référencées plus d’une fois par la requête parent ou les requêtes WITH sibling. Ainsi, les calculs coûteux qui sont nécessaires à plusieurs endroits peuvent être placés dans une requête WITH pour éviter le travail redondant. Une autre application possible est d’empêcher les évaluations multiples non souhaitées de fonctions ayant des effets secondaires. Toutefois, le revers de la médaille est que l’optimiseur est moins à même de faire descendre les restrictions de la requête parent dans une requête WITH que dans une sous-requête ordinaire. La requête WITH sera généralement évaluée telle qu’elle est écrite, sans suppression des lignes que la requête parente pourrait rejeter par la suite. (Mais, comme mentionné ci-dessus, l’évaluation pourrait s’arrêter tôt si la ou les références à la requête ne demandent qu’un nombre limité de lignes.)

Les exemples ci-dessus ne montrent que WITH utilisé avec SELECT, mais il peut être attaché de la même manière à INSERT, UPDATE, ou DELETE. Dans chaque cas, il fournit effectivement une ou plusieurs tables temporaires auxquelles on peut se référer dans la commande principale.