PostgreSQL - WITH-Klausel

In PostgreSQL bietet die WITH-Abfrage eine Möglichkeit, zusätzliche Anweisungen zur Verwendung in einer größeren Abfrage zu schreiben. Es hilft dabei, komplizierte und große Abfragen in einfachere Formen zu zerlegen, die leicht lesbar sind. Diese Anweisungen, die häufig als Common Table Expressions oder CTEs bezeichnet werden, können als Definition temporärer Tabellen angesehen werden, die nur für eine Abfrage vorhanden sind.

Die WITH-Abfrage als CTE-Abfrage ist besonders nützlich, wenn die Unterabfrage mehrmals ausgeführt wird. Es ist ebenso hilfreich anstelle von temporären Tabellen. Es berechnet die Aggregation einmal und ermöglicht es uns, sie in den Abfragen anhand ihres Namens (möglicherweise mehrmals) zu referenzieren.

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

Syntax

Die grundlegende Syntax der 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]

Wobei name_for_summary_data der Name der WITH-Klausel ist. Der Name_für_Zusammenfassung_Daten kann mit einem vorhandenen Tabellennamen identisch sein und hat Vorrang.

Sie können datenmodifizierende Anweisungen (INSERT, UPDATE oder DELETE) in WITH verwenden. Auf diese Weise können Sie mehrere verschiedene Vorgänge in derselben Abfrage ausführen.

Rekursiv MIT

Rekursive WITH- oder hierarchische Abfragen sind eine Form von CTE, bei der ein CTE auf sich selbst verweisen kann, dh eine WITH-Abfrage kann auf seine eigene Ausgabe verweisen, daher der Name rekursiv.

Beispiel

Betrachten Sie die Tabelle UNTERNEHMEN mit Aufzeichnungen wie folgt:

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)

Schreiben wir nun eine Abfrage mit der WITH-Klausel, um die Datensätze aus der obigen Tabelle wie folgt auszuwählen:

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

Die oben angegebene PostgreSQL-Anweisung führt zu folgendem Ergebnis:

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)

Schreiben wir nun eine Abfrage mit dem Schlüsselwort RECURSIVE zusammen mit der WITH-Klausel, um die Summe der Gehälter unter 20000 wie folgt zu ermitteln:

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

Die oben angegebene PostgreSQL-Anweisung führt zu folgendem Ergebnis:

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

Schreiben wir eine Abfrage mit datenmodifizierenden Anweisungen zusammen mit der WITH-Klausel, wie unten gezeigt.

Erstellen Sie zunächst eine Tabelle COMPANY1 ähnlich der Tabelle COMPANY. Die Abfrage im Beispiel verschiebt effektiv Zeilen von UNTERNEHMEN nach UNTERNEHMEN1. Das DELETE in WITH löscht die angegebenen Zeilen aus COMPANY und gibt ihren Inhalt mithilfe seiner RETURNING-Klausel zurück. und dann liest die primäre Abfrage diese Ausgabe und fügt sie in COMPANY1 TABLE - ein.

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

Die oben angegebene PostgreSQL-Anweisung führt zu folgendem Ergebnis:

INSERT 0 3

Nun sind die Datensätze in den Tabellen COMPANY und COMPANY1 wie folgt:

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)