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.

PgSQL
1
2
WITH [RECURSIVE] query_name [ (column_name [,...]) ] AS (SELECT ...) [, ...]
SELECT...

PgSQL
1
2
3
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

# Requête CTE sans récursivité

Nous allons commencer en douceur avec un modèle assez basique :

PgSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
CREATE TABLE categorie
(
  idcategorie INTEGER NOT NULL,
  libelle CHARACTER VARYING,
  idcategorieparente INTEGER,
  PRIMARY KEY (idcategorie),
  UNIQUE (libelle),
  FOREIGN KEY (idcategorieparente)
    REFERENCES categorie (idcategorie)
);
 
INSERT INTO categorie (idcategorie,libelle,idcategorieparente)
VALUES
(1,'Bases de données',null),
(2,'PostgreSQL',1),
(3,'Installation',2),
(4,'Administration',2),
(5,'Outils',2),
(6,'Requêtes',2),
(7,'Langage SQL',6),
(8,'Conception',5);
 
CREATE TABLE article
(
  idarticle INTEGER NOT NULL,
  idcategorie INTEGER NOT NULL,
  libelle CHARACTER VARYING,
  contenu TEXT,
  PRIMARY KEY (idarticle),
  FOREIGN KEY (idcategorie)
    REFERENCES categorie (idcategorie)
);
 
INSERT INTO article (idarticle,idcategorie,libelle)
VALUES
(1,5,'PgAdmin'),
(2,8,'PowerAMC'),
(3,8,'DbDesigner'),
(4,5,'PgBouncer'),
(5,2,'Requêtes CTE'),
(6,2,'Utilisation du format json'),
(7,2,'Osez les OVER PARTITION BY');

Sans passer par une requête CTE voilà grosso modo ce qu’il faudrait faire si l’on veut obtenir la catégorie contenant le plus d’articles :

PgSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT categorie.*, COUNT(idarticle)
FROM categorie
INNER JOIN article USING (idcategorie)
GROUP BY categorie.idcategorie
HAVING COUNT(idarticle) = (
  SELECT MAX(nb_article)
  FROM (
    SELECT categorie.idcategorie, COUNT(idarticle) as nb_article
    FROM categorie
    INNER JOIN article USING (idcategorie)
    GROUP BY categorie.idcategorie
  ) t
)

En somme cela n’est pas très simple ni très lisible. Avec une requête CTE voilà ce que cela donne :

PgSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH
  sub_count AS (
    SELECT categorie.*, COUNT(idarticle) as nb_article
    FROM categorie
    inner join article using (idcategorie)
    GROUP BY idcategorie
  ),
  sub_max AS (
    SELECT MAX(nb_article) as max_article
    FROM sub_count
  )
SELECT *
FROM sub_count t1
INNER JOIN sub_max t2 ON t1.nb_article = t2.max_article

Les requêtes CTE trouvent aussi leur utilité dans postgres 9.2 avec le format JSON cela évite de devoir créer un type, vue, table ou sous-requête pour avoir les noms des champs « mappés » dans le flux JSON. Admettons que l’on veuille la liste des catégories avec leur nombre d’articles :

PgSQL
1
2
3
4
5
6
7
8
WITH
  sub_count AS (
    SELECT categorie.*, COUNT(idarticle) as nb_article
    FROM categorie
    LEFT JOIN article USING (idcategorie)
    GROUP BY idcategorie
  )
SELECT ARRAY_TO_JSON(ARRAY_AGG(sub_count),TRUE) FROM sub_count;

on obtient alors un JSON de nos catégories :

JavaScript
1
2
3
4
5
6
7
8
[{"idcategorie":8,"libelle":"Conception","idcategorieparente":5,"nb_article":2},
{"idcategorie":6,"libelle":"Requêtes","idcategorieparente":2,"nb_article":0},
{"idcategorie":7,"libelle":"Langage SQL","idcategorieparente":6,"nb_article":0},
{"idcategorie":2,"libelle":"PostgreSQL","idcategorieparente":1,"nb_article":3},
{"idcategorie":5,"libelle":"Outils","idcategorieparente":2,"nb_article":2},
{"idcategorie":4,"libelle":"Administration","idcategorieparente":2,"nb_article":0},
{"idcategorie":1,"libelle":"Bases de données","idcategorieparente":null,"nb_article":0},
{"idcategorie":3,"libelle":"Installation","idcategorieparente":2,"nb_article":0}]

# Requête CTE avec récursivité

Fini de rigoler, passons à la récursivité 😉

Le but du jeu : récupérer l’arbre des catégories avec leur niveau de hiérarchie

PgSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
WITH RECURSIVE
  sub_tree (idcategorie, libelle, filariane, idcategorieparente, rang) AS (
    SELECT idcategorie, libelle, libelle, idcategorieparente, 1
    FROM categorie
    WHERE idcategorieparente IS NULL
    UNION
    SELECT t1.idcategorie, t1.libelle, t2.filariane || ' > ' || t1.libelle, t1.idcategorieparente, t2.rang+1
    FROM categorie t1
    INNER JOIN sub_tree t2 ON t2.idcategorie = t1.idcategorieparente
  )
SELECT *
FROM sub_tree
ORDER BY rang

résultats :

idcategorie libelle filariane idcategorieparente rang
1 Bases de données Bases de données 1
2 PostgreSQL Bases de données > PostgreSQL 1 2
3 Installation Bases de données > PostgreSQL > Installation 2 3
4 Administration Bases de données > PostgreSQL > Administration 2 3
5 Outils Bases de données > PostgreSQL > Outils 2 3
6 Requêtes Bases de données > PostgreSQL > Requêtes 2 3
7 Langage SQL Bases de données > PostgreSQL > Requêtes > Langage SQL 6 4
8 Conception Bases de données > PostgreSQL > Outils > Conception 5 4


Après ces quelques exemples, on peut donc se rendre compte de la puissance et de la facilité qu’apporte les requêtes CTE dans PostgreSQL autant en terme de lisibilité/simplification sur des requêtes complexes que sur l’ajout de la récursivité.

Tweetez
Partagez
Partagez
0 Partages