Основное назначение SELECT в WITH заключается в том, чтобы разбить сложные запросы на более простые части. Пример:

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

которая отображает итоги продаж по каждому продукту только в регионах с наибольшими объемами продаж. Инструкция WITH предложение определяет две вспомогательные инструкции с именами regional_sales и top_regions, где результат regional_sales используется в top_regions а результат top_regions используется в основном SELECT запросе. Этот пример можно было бы написать без использования WITH, но тогда потребовалось бы два уровня вложенных под-SELECTзапросов. В таком виде логику отследить несколько проще.

Необязательные RECURSIVE модификатор превращает WITH из простого синтаксического удобства в функционал, позволяющий решать задачи, которые иначе невозможно реализовать в стандартном SQL. Используя RECURSIVE, WITH запрос может обращаться к своему собственному результату. Простой пример такого запроса для вычисления суммы целых чисел от 1 до 100:

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

Общая форма рекурсивного WITH запроса всегда представляет собой нерекурсивный терм, затем UNION (или UNION ALL), а затем рекурсивный терм, где только рекурсивный терм может содержать ссылку на собственный результат запроса. Такой запрос выполняется следующим образом:

В приведенном выше примере рабочая таблица на каждом шаге содержит всего одну строку и принимает значения от 1 до 100 на последующих шагах. На 100-м шаге вывод отсутствует из-за WHERE предложения, и поэтому выполнение запроса завершается.

Рекурсивные запросы обычно используются для работы с иерархическими данными или данными со структурой дерево. Полезным примером является данный запрос для поиска всех прямых и косвенных подкомпонентов изделия при наличии только таблицы, содержащей сведения о непосредственных вложениях:

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity * pr.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part

WITH RECURSIVE search_tree(id, link, data) AS (
    SELECT t.id, t.link, t.data
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree;

WITH RECURSIVE search_tree(id, link, data, путь) AS (
    SELECT t.id, t.link, t.data, ARRAY[t.id]
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data, path || t.id
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree ORDER BY путь;

WITH RECURSIVE search_tree(id, link, data, путь) AS (
    SELECT t.id, t.link, t.data, ARRAY[ROW(t.f1, t.f2)]
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data, путь || ROW(t.f1, t.f2)
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree ORDER BY путь;

WITH RECURSIVE search_tree(id, link, data, depth) AS (
    SELECT t.id, t.link, t.data, 0
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data, depth + 1
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree ORDER BY depth;

WITH RECURSIVE search_tree(id, link, data) AS (
    SELECT t.id, t.link, t.data
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data
    FROM tree t, search_tree st
    WHERE t.id = st.link
) SEARCH DEPTH FIRST BY id SET ordercol
SELECT * FROM search_tree ORDER BY ordercol;

WITH RECURSIVE search_tree(id, link, data) AS (
    SELECT t.id, t.link, t.data
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data
    FROM tree t, search_tree st
    WHERE t.id = st.link
) SEARCH BREADTH FIRST BY id SET ordercol
SELECT * FROM search_tree ORDER BY ordercol;

WITH RECURSIVE search_graph(id, link, data, depth) AS (
    SELECT g.id, g.link, g.data, 0
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1
    FROM graph g, search_graph sg
    WHERE g.id = sg.link
)
SELECT * FROM search_graph;

WITH RECURSIVE search_graph(id, link, data, depth, is_cycle, path) AS (
    SELECT g.id, g.link, g.data, 0,
      false,
      ARRAY[g.id]
    из предложения FROM graph g
  UNION ALL
    команда SELECT g.id, g.link, g.data, sg.depth + 1,
      g.id = ANY(path),
      path || g.id
    FROM graph g, search_graph sg
    WHERE g.id = sg.link AND NOT is_cycle
)
команда SELECT * FROM search_graph;

WITH RECURSIVE search_graph(id, link, data, depth, is_cycle, path) AS (
    SELECT g.id, g.link, g.data, 0,
      false,
      ARRAY[ROW(g.f1, g.f2)]
    из предложения FROM graph g
  UNION ALL
    команда SELECT g.id, g.link, g.data, sg.depth + 1,
      ROW(g.f1, g.f2) = ANY(path),
      path || ROW(g.f1, g.f2)
    FROM graph g, search_graph sg
    WHERE g.id = sg.link AND NOT is_cycle
)
команда SELECT * FROM search_graph;

WITH RECURSIVE search_graph(id, link, data, depth) AS (
    SELECT g.id, g.link, g.data, 1
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1
    FROM graph g, search_graph sg
    WHERE g.id = sg.link
) CYCLE id SET is_cycle USING path
SELECT * FROM search_graph;

WITH RECURSIVE t(n) AS (
    SELECT 1
  UNION ALL
    SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;

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

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

Простым примером этих правил является

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w WHERE key = 123;

Данный WITH запрос будет преобразован, формируя тот же план выполнения, что и

SELECT * FROM big_table WHERE key = 123;

В частности, если имеется индекс по столбцу key, он, скорее всего, будет использован для получения только тех строк, где key = 123. С другой стороны, в команде

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

WITH запрос будет материализован с созданием временной копии big_table которая затем соединяется сама с собой — без использования каких-либо индексов. Этот запрос будет выполнен гораздо эффективнее, если написать его так:

WITH w AS NOT MATERIALIZED (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

чтобы ограничения основного запроса могли быть применены непосредственно к сканированию big_table.

Пример, в котором использование NOT MATERIALIZED может быть нежелательным:

WITH w AS (
    SELECT key, very_expensive_function(val) as f FROM some_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

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

В примерах выше показано только использование WITH совместно с SELECT, но его можно аналогичным образом присоединить и к INSERT, UPDATE, DELETE, или MERGE. В каждом из этих случаев фактически создаются временные таблицы, к которым можно обращаться в основной команде.

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

WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;

Данный запрос фактически перемещает строки из таблицы products в products_log. Инструкция DELETE в WITH удаляет указанные строки из products, возвращая их содержимое посредством своего RETURNING предложении; и затем основной запрос считывает этот результат и вставляет его в products_log.

Тонкий момент приведенного выше примера заключается в том, что WITH предложение присоединяется к INSERT, а не к под-SELECT внутри команды INSERT. Это необходимо, так как команды изменения данных разрешены только в WITH предложениях, которые присоединены к инструкции верхнего уровня. Однако применяются обычные WITH правила видимости, поэтому можно ссылаться на WITH результат команды из под-SELECT.

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

WITH t AS (
    DELETE FROM foo
)
DELETE FROM bar;

В данном примере будут удалены все строки из таблиц foo и bar. Число затронутых строк, сообщаемое клиенту, будет включать только строки, удалённые из bar.

Рекурсивные ссылки на самих себя в инструкциях изменения данных не допускаются. В некоторых случаях это ограничение можно обойти, обратившись к результату рекурсивной WITH, например:

WITH RECURSIVE included_parts(sub_part, part) AS (
    SELECT sub_part, part FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
DELETE FROM parts
  WHERE part IN (SELECT part FROM included_parts);

Данный запрос удалит все прямые и косвенные составные части продукта.

Команды изменения данных в WITH выполняются ровно один раз и всегда до завершения, независимо от того, считывает ли основной запрос все их выходные данные (или не считывает их вовсе). Обратите внимание, что это отличается от правила для SELECT в WITH: как было сказано в предыдущем разделе, выполнение SELECT осуществляется лишь в той степени, в которой основной запрос требует их вывода.

Вложенные инструкции в WITH выполняются параллельно друг с другом и с основным запросом. Таким образом, при использовании изменяющих данные инструкций в WITH, порядок фактического выполнения указанных обновлений является непредсказуемым. Все инструкции выполняются в рамках одного и того же snapshot (см. Глава 2.10), поэтому они не могут «видеть» результаты изменений, внесенных друг другом в целевые таблицы. Это сглаживает последствия непредсказуемости фактического порядка обновления строк и означает, что RETURNING передача данных является единственным способом сообщения об изменениях между различными WITH вложенными инструкциями и основным запросом. В качестве примера можно привести ситуацию, когда в

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM products;

внешняя команда SELECT вернет исходные цены, существовавшие до выполнения действия UPDATE, тогда как в

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;

внешняя команда SELECT будут возвращены обновленные данные.

Попытка дважды обновить одну и ту же строку в рамках одной инструкции не поддерживается. Применяется только одно из изменений, при этом надежно предсказать, какое именно, затруднительно (а иногда и невозможно). Это также справедливо для удаления строки, которая уже была обновлена в той же инструкции: будет выполнено только обновление. Поэтому в целом следует избегать попыток дважды изменить одну и ту же строку в рамках одной инструкции. В частности, следует избегать написания WITH вложенных инструкций, которые могут затронуть те же строки, измененные основной инструкцией или параллельной вложенной инструкцией. Результаты такой инструкции будут непредсказуемыми.

В настоящее время любая таблица, используемая в качестве целевой для инструкции изменения данных в WITH не должна иметь ни условного правила, ни ALSO правила, ни INSTEAD правила, которое разворачивается в несколько инструкций.