Основное значение 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, path) 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 path;

WITH RECURSIVE search_tree(id, link, data, path) 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, path || ROW(t.f1, t.f2)
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree ORDER BY path;

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 (см. Раздел 6.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, порядок выполнения указанных обновлений фактически непредсказуем. Все операторы выполняются с одним и тем же снимком (см. Глава 13), поэтому они не могут "видеть" изменения, внесенные другими операторами в целевые таблицы. Это устраняет эффекты непредсказуемости фактического порядка обновления строк и означает, что данные, возвращаемые с помощью 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, которые раскрываются в несколько операторов.