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