Expressions de table commune et requêtes récursives avec WITH

Ce que c'est

WITH name AS (SELECT ...) définit un résultat temporaire nommé pour la requête principale ; une instruction peut en définir plusieurs, chacune pouvant se référer aux précédentes. La documentation de PostgreSQL décrit la forme récursive : WITH RECURSIVE t AS (non-recursive term UNION [ALL] recursive term) évalue le terme non récursif, place ses lignes dans une table de travail, puis évalue à répétition le terme récursif en substituant la table de travail à l'auto-référence, jusqu'à ce qu'aucune ligne ne soit plus renvoyée. UNION élimine les lignes en double, UNION ALL les conserve. La même page documente SEARCH DEPTH FIRST | BREADTH FIRST BY ... SET col pour ordonner la sortie, et CYCLE col SET is_cycle USING path pour s'arrêter sur les boucles, et précise qu'une requête WITH non récursive et sans effet de bord, référencée une seule fois, est repliée par défaut dans la requête parente, alors que MATERIALIZED force une évaluation séparée et NOT MATERIALIZED force le repliement.

Pourquoi c'est important

Les hiérarchies (catégories, organigrammes, fils de commentaires, nomenclatures) ne s'accommodent pas d'un nombre fixe de jointures. Une CTE récursive gère une profondeur quelconque en une seule instruction ; les CTE non récursives découpent une longue requête en étapes nommées qui peuvent être lues et testées une à une.

Comment l'appliquer

• Chaîne d'ancêtres : partir du nœud (SELECT id, parent_id, 1 AS depth FROM nodes WHERE id = $1) et joindre le terme récursif sur parent_id ; pour les descendants, procéder dans l'autre sens. Transporter depth et le plafonner (WHERE depth < 50) comme garde-fou contre des boucles inattendues.
• Pour les graphes susceptibles de contenir des cycles, utiliser UNION ou la clause CYCLE ; UNION ALL sur un cycle ne se termine jamais sans plafond.
• Utiliser SEARCH DEPTH FIRST BY name SET ord avec ORDER BY ord pour afficher un arbre dans l'ordre imbriqué.
• Les CTE modifiant des données déplacent des lignes en une seule instruction : WITH moved AS (DELETE FROM live WHERE ... RETURNING *) INSERT INTO archive SELECT * FROM moved.
• Une CTE référencée plus d'une fois est matérialisée par défaut ; n'écrire MATERIALIZED sur une CTE à usage unique que pour forcer une évaluation séparée (une fonction coûteuse calculée une seule fois), et NOT MATERIALIZED sur une CTE référencée plusieurs fois lorsque chaque usage n'a besoin que d'une petite tranche compatible avec un index, comme le montre l'exemple big_table de la documentation.

Pièges

Une CTE matérialisée est évaluée telle qu'écrite, si bien que le WHERE externe ne peut pas y être repoussé, et les index des tables de base ne servent pas le prédicat externe. Chaque ligne produite par un terme récursif reste dans le résultat, donc des lignes larges dans de grands arbres coûtent cher. La documentation qualifie l'ordre de sortie en largeur d'abord de détail d'implémentation à ne pas exploiter, et l'ordre au sein d'un même niveau de non défini ; utiliser SEARCH ou un ORDER BY explicite.