PostgreSQL WITH-Klausel

In PostgreSQL bietet die WITH-Klausel eine Möglichkeit, Hilfsanweisungen für die Verwendung in einer größeren Abfrage zu schreiben.

Die WITH-Klausel hilft dabei, komplexe große Abfragen zur besseren Lesbarkeit in einfachere Formen zu zerlegen. Diese Anweisungen werden oft als Common Table Expressions (CTEs) bezeichnet und können auch als temporäre Tabellen betrachtet werden, die für die Abfrage existieren.

Die WITH-Klausel ist besonders nützlich, wenn eine Unterabfrage mehrmals ausgeführt wird, sodass wir innerhalb der Abfrage über ihren Namen darauf verweisen können (möglicherweise mehrmals).

Die WITH-Klausel muss definiert werden, bevor sie verwendet wird.

Syntax

Die grundlegende Syntax einer WITH-Abfrage lautet wie folgt:

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_dataist der Name der WITH-Klausel,name_for_summary_dataEr kann mit einem vorhandenen Tabellennamen identisch sein und hat Vorrang.

INSERT-, UPDATE- oder DELETE-Anweisungen für Daten können in WITH verwendet werden, sodass Sie mehrere verschiedene Operationen in derselben Abfrage ausführen können.

WITH Recursive

In der WITH-Klausel können Sie die von ihr selbst ausgegebenen Daten verwenden.

Ein Common Table Expression (CTE) hat einen wichtigen Vorteil: Es kann auf sich selbst verweisen und so einen rekursiven CTE erstellen. Ein rekursiver CTE ist ein Common Table Expression, das den anfänglichen CTE wiederholt ausführt, um Teilmengen von Daten zurückzugeben, bis die vollständige Ergebnismenge abgerufen wurde.

Beispiele

Erstellen Sie die COMPANY-Tabelle (Laden Sie die COMPANY-SQL-Datei herunter), der Dateninhalt lautet wie folgt:

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)

Als Nächstes verwenden wir die WITH-Klausel, um Daten aus der obigen Tabelle abzufragen:

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

Das Ergebnis ist wie folgt:

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)

Als Nächstes verwenden wirRECURSIVESchlüsselwort und die WITH-Klausel, um eine Abfrage zu schreiben, dieSALARYDatensätze findet, deren SALARY-Feld kleiner als 20000 ist, und deren Summe berechnet:

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

Das Ergebnis ist wie folgt:

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

Als Nächstes erstellen wir eine COMPANY1-Tabelle ähnlich der COMPANY-Tabelle und verwenden die DELETE-Anweisung und die WITH-Klausel, um aus der COMPANY-TabelleSALARYDatensätze zu löschen, deren SALARY-Feld größer oder gleich 30000 ist, und die gelöschten Datensätze in die COMPANY1-Tabelle einzufügen, wodurch Daten von der COMPANY-Tabelle in die COMPANY1-Tabelle übertragen werden:

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

Das Ergebnis ist wie folgt:

INSERT 0 3

Zu diesem Zeitpunkt sind die Daten in der CAMPANY-Tabelle und der CAMPANY1-Tabelle wie folgt:

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)
Weitere Erweiterungen