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. The name_for_summary_data может совпадать с именем существующей таблицы и будет иметь приоритет.
Вы можете использовать операторы модификации данных (INSERT, UPDATE или DELETE) совместно с WITH. Это позволит Вам выполнить сразу несколько операций внутри одного запроса.
Рекурсивный WITH
Рекурсивные запросы WITH или иерархические запросы — это форма CTE, в которой CTE может ссылаться сам на себя, то есть запрос WITH может ссылаться на свой собственный результат, отсюда и название «рекурсивный».
Пример
Рассмотрим таблицу COMPANY:
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;
Результат будет следующим:
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 для того, чтобы вычислить сумму зарплат меньше, чем 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)
Давайте напишем запрос, используя операторы изменения данных вместе с 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);
Результат будет следующим:
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)
