Durant le Confoo la semaine dernière, j'ai assisté à l'excellente présentation Programming the SQL Way with Common Table Expressions de Bruce Momjian (core team de PostgreSQL, aussi employé chez EnterpriseDB). Pour moi, les CTE étaient un nouveau concept que je n'avais encore jamais exploré et qui a été introduit en 2009 lors du lancement de la version 8.4.
Qu'est-ce qu'un Common Table Expression ? C'est essentiellement une requête nommée qui peut être référencée à l'intérieur une requête SQL et qui peut être utilisée récursivement. Wow. Out les tables temporaires utilisées dans des boucles pl/pgsql.
Un bon exemple de base qui démontre la puissance d'un CTE est de calculer la factorielle de 10 (ce qu'on connait par la notation 10!) à partir d'un seul énoncé SQL. Dans tout langage de programmation, on devrait normalement faire une boucle ou une fonction récursive pour obtenir le résultat. Ici, un énoncé SQL de ce type suffit :
WITH RECURSIVE source (counter, product) AS (Résultat :
SELECT 1, 1
UNION ALL
SELECT counter + 1, product * (counter + 1)
FROM source
WHERE counter < 10
)
SELECT counter, product FROM source;
On peut voir à chaque itération la valeur du produit calculé. La dernière ligne donne le résultat de la factorielle : 3628800. Comme j'en suis encore à assimiler ces notions, je vous laisserai le soin d'aller consulter sa présentation de 80 slides disponible en format PDF. Elle n'est pas si longue et a le mérite de décortiquer clairement les CTE à partir de la base tout en augmentant progressivement le niveau de difficulté.
OK, la démonstration de la factorielle n'était peut-être pas le meilleur exemple puisque Postgres offre un opérateur pour la calculer :
SELECT !!10Plus simple non ? Sauf que les CTE peuvent être réellement puissantes dans de nombreux autres contextes.
Enfin, comme les CTE font parties du standard SQL:2003, on peut faire exécuter le même énoncé CTE sous SQL Server 2008, à la différence qu'on doit retirer le mot RECURSIVE pour que ça fonctionne.
