Предложение WITH в PostgreSQL

В PostgreSQL конструкция WITH предоставляет способ написания вспомогательных инструкций для использования в более крупном запросе.

Конструкция WITH помогает разбивать сложные большие запросы на более простые формы для лучшей читаемости. Такие инструкции часто называют общими табличными выражениями (CTE), и их также можно рассматривать как временные таблицы, существующие для запроса.

Конструкция WITH особенно полезна, когда подзапрос выполняется несколько раз, позволяя ссылаться на него по имени (возможно, многократно) внутри запроса.

Предложение WITH должно быть определено до его использования.

Синтаксис

Базовый синтаксис запроса WITH выглядит следующим образом:

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_data— это имя предложения WITH,name_for_summary_dataОно может совпадать с именем существующей таблицы и имеет приоритет.

Инструкции INSERT, UPDATE или DELETE можно использовать в WITH, что позволяет выполнять несколько различных операций в одном запросе.

WITH Recursive

В предложении WITH можно использовать данные, которые оно выводит само.

Общее табличное выражение (CTE) имеет важное преимущество: оно может ссылаться на себя, создавая таким образом рекурсивное CTE. Рекурсивное CTE — это общее табличное выражение, которое многократно выполняет исходное CTE для возврата подмножеств данных до получения полного набора результатов.

Примеры

Создайте таблицу COMPANY (Скачайте SQL-файл COMPANY), содержимое данных следующее:

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)

Далее мы будем использовать предложение WITH для запроса данных из приведённой выше таблицы:

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

Результат следующий:

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)

Далее давайте используемRECURSIVEключевое слово и предложение WITH, чтобы написать запрос для поиска записейSALARY, у которых поле SALARY меньше 20000, и вычислить их сумму:

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

Результат следующий:

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

Далее мы создаём таблицу COMPANY1, подобную таблице COMPANY, и используем оператор DELETE и предложение WITH, чтобы удалить из таблицы COMPANYSALARYзаписи, у которых поле SALARY больше или равно 30000, и вставить удалённые записи в таблицу COMPANY1, тем самым перенеся данные из таблицы COMPANY в таблицу 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);

Результат следующий:

INSERT 0 3

На данный момент данные в таблице CAMPANY и таблице CAMPANY1 выглядят следующим образом:

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)
Другие расширения