Aller au contenu
Tous les termes du glossaire

Expression de table commune (CTE)

Qu’est-ce qu’une CTE (requête WITH) dans PostgreSQL ?

Une expression de table commune, écrite avec WITH, donne un nom à une sous-requête pour que le reste de l’instruction puisse l’utiliser comme une table. WITH RECURSIVE permet de traiter les arbres et les hiérarchies.

Une expression de table commune (CTE) est une instruction auxiliaire dans une clause WITH qui définit un résultat temporaire et nommé pour une seule requête. Elle décompose une requête complexe en étapes lisibles, et l’instruction auxiliaire peut aussi être une instruction de modification de données INSERT, UPDATE ou DELETE (insertion, mise à jour ou suppression).

WITH RECURSIVE permet à une CTE de faire référence à son propre résultat, ce qui est la méthode standard pour parcourir des hiérarchies comme les liens de subordination ou les catégories imbriquées. Une CTE non récursive, sans effet de bord et référencée une seule fois par la requête est intégrée à la requête externe afin que les deux puissent être optimisées ensemble. Ajoutez MATERIALIZED pour qu’elle soit évaluée séparément, ou NOT MATERIALIZED pour demander son intégration.

Exemple

L’exemple part d’un employé et remonte la chaîne hiérarchique avec WITH RECURSIVE, en renvoyant chaque personne avec sa distance par rapport au point de départ.

CREATE TABLE employees (
  id int PRIMARY KEY,
  name text NOT NULL,
  manager_id int REFERENCES employees (id)
);
INSERT INTO employees VALUES (1, 'Ana', NULL), (2, 'Ben', 1), (3, 'Chen', 2), (4, 'Dee', 1);

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS depth FROM employees WHERE id = 3
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM employees e JOIN chain c ON e.id = c.manager_id
)
SELECT name, depth FROM chain ORDER BY depth;

Du SQL simple pour PostgreSQL 16 ou 17 standard, sans aucune extension. Exécutez-le dans une base de données vide que vous pouvez jeter ensuite.

Références

Documentation officielle de PostgreSQL (en anglais).

Termes associés