PostgreSQL - Clause WITH

Dans PostgreSQL, la requête WITH fournit un moyen d'écrire des instructions auxiliaires à utiliser dans une requête plus volumineuse. Il aide à décomposer les requêtes complexes et volumineuses en formes plus simples, facilement lisibles. Ces instructions, souvent appelées expressions de table communes ou CTE, peuvent être considérées comme définissant des tables temporaires qui n'existent que pour une requête.

La requête WITH étant une requête CTE, est particulièrement utile lorsque la sous-requête est exécutée plusieurs fois. Il est également utile à la place des tables temporaires. Il calcule l'agrégation une fois et nous permet de la référencer par son nom (peut être plusieurs fois) dans les requêtes.

La clause WITH doit être définie avant d'être utilisée dans la requête.

Syntaxe

La syntaxe de base de la 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]

Où name_for_summary_data est le nom donné à la clause WITH. Le nom_for_summary_data peut être le même qu'un nom de table existant et aura la priorité.

Vous pouvez utiliser des instructions de modification des données (INSERT, UPDATE ou DELETE) dans WITH. Cela vous permet d'effectuer plusieurs opérations différentes dans la même requête.

Récursif AVEC

Requêtes WITH récursives ou Hierarchical, est une forme de CTE où un CTE peut se référer à lui-même, c'est-à-dire qu'une requête WITH peut faire référence à sa propre sortie, d'où le nom récursif.

Exemple

Considérez la table COMPANY ayant des enregistrements comme suit -

testdb# 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)

Maintenant, écrivons une requête en utilisant la clause WITH pour sélectionner les enregistrements dans le tableau ci-dessus, comme suit -

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

L'instruction PostgreSQL donnée ci-dessus produira le résultat 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)

Maintenant, écrivons une requête en utilisant le mot-clé RECURSIVE avec la clause WITH, pour trouver la somme des salaires inférieurs à 20000, comme suit -

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

L'instruction PostgreSQL donnée ci-dessus produira le résultat suivant -

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

Écrivons une requête en utilisant des instructions de modification de données avec la clause WITH, comme indiqué ci-dessous.

Commencez par créer une table COMPANY1 similaire à la table COMPANY. La requête de l'exemple déplace effectivement les lignes de COMPANY vers COMPANY1. Le DELETE dans WITH supprime les lignes spécifiées de COMPANY, retournant leur contenu au moyen de sa clause RETURNING; puis la requête principale lit cette sortie et l'insère dans COMPANY1 TABLE -

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);

L'instruction PostgreSQL donnée ci-dessus produira le résultat suivant -

INSERT 0 3

Maintenant, les enregistrements dans les tables COMPANY et COMPANY1 sont les suivants -

testdb=# 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)


testdb=# 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)