PostgreSQL - klauzula WITH

W PostgreSQL kwerenda WITH umożliwia pisanie instrukcji pomocniczych do użycia w większym zapytaniu. Pomaga w rozkładaniu skomplikowanych i dużych zapytań na prostsze, czytelne formy. Instrukcje te, często określane jako Common Table Expressions lub CTE, można traktować jako definiujące tabele tymczasowe, które istnieją tylko dla jednego zapytania.

Zapytanie WITH będące zapytaniem CTE jest szczególnie przydatne, gdy podzapytanie jest wykonywane wiele razy. Jest równie pomocny w miejsce tymczasowych stolików. Oblicza agregację raz i pozwala nam odwoływać się do niej poprzez nazwę (może być wiele razy) w zapytaniach.

Klauzulę WITH należy zdefiniować, zanim zostanie użyta w zapytaniu.

Składnia

Podstawowa składnia zapytania WITH jest następująca -

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]

Gdzie name_for_summary_data to nazwa nadana klauzuli WITH. Nazwa_for_summary_data może być taka sama, jak nazwa istniejącej tabeli i będzie miała pierwszeństwo.

Możesz użyć instrukcji modyfikujących dane (INSERT, UPDATE lub DELETE) w WITH. Pozwala to na wykonanie kilku różnych operacji w tym samym zapytaniu.

Rekurencyjne z

Kwerendy rekurencyjne WITH lub Hierarchical to forma CTE, w której CTE może odwoływać się do siebie, tj. Zapytanie WITH może odnosić się do własnego wyjścia, stąd nazwa rekurencyjna.

Przykład

Rozważ poniższą tabelę FIRMA zawierająca rekordy -

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)

Teraz napiszmy zapytanie, używając klauzuli WITH, aby wybrać rekordy z powyższej tabeli, w następujący sposób -

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

Powyższa instrukcja PostgreSQL da następujący wynik -

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)

Teraz napiszmy zapytanie, używając słowa kluczowego RECURSIVE wraz z klauzulą ​​WITH, aby znaleźć sumę wynagrodzeń mniejszą niż 20000, w następujący sposób -

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

Powyższa instrukcja PostgreSQL da następujący wynik -

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

Napiszmy zapytanie, używając instrukcji modyfikujących dane wraz z klauzulą ​​WITH, jak pokazano poniżej.

Najpierw utwórz tabelę COMPANY1 podobną do tabeli COMPANY. Zapytanie w przykładzie skutecznie przenosi wiersze z COMPANY do COMPANY1. DELETE w WITH usuwa określone wiersze z COMPANY, zwracając ich zawartość za pomocą klauzuli RETURNING; a następnie podstawowe zapytanie odczytuje te dane wyjściowe i wstawia je do TABELI FIRMY1 -

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

Powyższa instrukcja PostgreSQL da następujący wynik -

INSERT 0 3

Teraz rekordy w tabelach FIRMA i FIRMA1 są następujące -

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)