les requêtes CTE avec PostgreSQL
Aurélien Morlé juin 7th, 2013
Vous allez peut-être me dire «CTE ? Quésako ?». Les requêtes CTE ou Common Table Expressions peuvent être considérées comme des tables ou vues temporaires qui vont exister seulement dans le cadre de l’exécution d’une requête. Pour notre plus grand bonheur, cela ne se limite pas à une simple vue ou table temporaire, nous pouvons y ajouter de la récursivité.
Les requêtes CTE vont avoir plusieurs utilités notamment pour :
- rendre plus claires des requêtes complexes construites à partir de résultats d’autres requêtes.
- ne plus passer par des fonctions en pl/pgsql pour faire de la récursivité
L’instruction permettant cela est WITH [RECURSIVE] qui est disponible depuis postgresql 8.4 pour les requêtes CTE de type SELECT et depuis la 9.1 pour les types INSERT, UPDATE et DELETE.
NB : les requêtes qui seront comprises dans le WITH ne seront évaluées qu’une fois, ce qui permet d’avoir de bonnes performances et le même ensemble de données.
Syntaxe d’une requête CTE
La clause WITH permet de spécifier une ou plusieurs sous requêtes qui pourront être référencées par leur nom dans la requête principale. En option, une liste de noms de colonnes peut être spécifié, si ce n’est pas spécifié, les noms de colonnes sont déduites de la sous-requête.
WITH [RECURSIVE] query_name [ (column_name [,...]) ] AS (SELECT ...) [, ...] SELECT...
Si RECURSIVE est spécifié, la sous-requête peut se référencer elle même. Une sous-requête de ce type doit avoir la forme :
non_recursive_term UNION [ ALL ] recursive_term
