Clause WITH PostgreSQL

Dans PostgreSQL, la clause WITH offre un moyen d'écrire des instructions auxiliaires à utiliser dans une requête plus vaste.

La clause WITH aide à décomposer les requêtes complexes et volumineuses en formes plus simples pour améliorer la lisibilité. Ces instructions sont souvent appelées expressions de table communes (CTE) et peuvent aussi être considérées comme des tables temporaires qui existent pour la requête.

La clause WITH est particulièrement utile lorsqu'une sous-requête est exécutée plusieurs fois, ce qui permet de la référencer par son nom (éventuellement plusieurs fois) dans la requête.

La clause WITH doit être définie avant d'être utilisée.

Syntaxe

La syntaxe de base d'une requête WITH est la suivante :

WITH
   name_for_summary_data AS (
      SELECT Statement)
   SELECT columns
   FROM name_for_summary_data
   WHERE conditions <=> (
      SELECT column
      FROM name_for_summary_data)
   [ORDER BY columns]

name_for_summary_dataest le nom de la clause WITH,name_for_summary_dataIl peut être identique au nom d'une table existante et est prioritaire.

Les instructions INSERT, UPDATE ou DELETE de données peuvent être utilisées dans WITH, ce qui vous permet d'effectuer plusieurs opérations différentes dans la même requête.

WITH récursif

Dans la clause WITH, vous pouvez utiliser les données qu'elle produit elle-même.

Une expression de table commune (CTE) présente un avantage important : elle peut se référencer elle-même, créant ainsi une CTE récursive. Une CTE récursive est une expression de table commune qui exécute de manière répétée la CTE initiale pour renvoyer des sous-ensembles de données jusqu'à ce que l'ensemble de résultats complet soit obtenu.

Exemples

Créer la table COMPANY (Télécharger le fichier SQL COMPANY), le contenu des données est le suivant :

exampledb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Ensuite, nous utiliserons la clause WITH pour interroger les données de la table ci-dessus :

With CTE AS
(Select
 ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select * From CTE;

Le résultat est le suivant :

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Ensuite, utilisonsRECURSIVEmot-clé et la clause WITH pour écrire une requête afin de trouver les enregistrementsSALARYdont le champ SALARY est inférieur à 20000 et calculer leur somme :

WITH RECURSIVE t(n) AS (
   VALUES (0)
   UNION ALL
   SELECT SALARY FROM COMPANY WHERE SALARY < 20000
)
SELECT sum(n) FROM t;

Le résultat est le suivant :

 sum
-------
 25000
(1 row)

Ensuite, nous créons une table COMPANY1 similaire à la table COMPANY, et utilisons l'instruction DELETE et la clause WITH pour supprimer de la table COMPANY les enregistrementsSALARYdont le champ SALARY est supérieur ou égal à 30000, et insérer les enregistrements supprimés dans la table COMPANY1, transférant ainsi les données de la table COMPANY vers la table COMPANY1 :

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);


WITH moved_rows AS (
   DELETE FROM COMPANY
   WHERE
      SALARY >= 30000
   RETURNING *
)
INSERT INTO COMPANY1 (SELECT * FROM moved_rows);

Le résultat est le suivant :

INSERT 0 3

À ce stade, les données de la table CAMPANY et de la table CAMPANY1 sont les suivantes :

exampledb=# SELECT * FROM COMPANY;
 id | name  | age |  address   | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  7 | James |  24 | Houston    |  10000
(4 rows)


exampledb=# SELECT * FROM COMPANY1;
 id | name  | age | address | salary
----+-------+-----+-------------+--------
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
(3 rows)
Autres extensions