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

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

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

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

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

Рекурсивный WITH

Рекурсивные запросы WITH или иерархические запросы - это форма CTE, где CTE может ссылаться на себя, т. Е. Запрос WITH может ссылаться на свой собственный вывод, отсюда и название рекурсивное.

пример

Рассмотрим таблицу КОМПАНИЯ, имеющую следующие записи:

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)

Теперь давайте напишем запрос, используя предложение WITH для выбора записей из приведенной выше таблицы, следующим образом:

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

Приведенный выше оператор PostgreSQL даст следующий результат:

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, чтобы найти сумму зарплат меньше 20000, следующим образом:

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

Приведенный выше оператор PostgreSQL даст следующий результат:

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

Давайте напишем запрос, используя операторы изменения данных вместе с предложением WITH, как показано ниже.

Сначала создайте таблицу COMPANY1, аналогичную таблице COMPANY. Запрос в примере эффективно перемещает строки из COMPANY в COMPANY1. Команда DELETE в WITH удаляет указанные строки из COMPANY, возвращая их содержимое с помощью предложения RETURNING; а затем основной запрос считывает этот вывод и вставляет его в ТАБЛИЦУ 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);

Приведенный выше оператор PostgreSQL даст следующий результат:

INSERT 0 3

Теперь записи в таблицах COMPANY и COMPANY1 следующие:

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)