7.8. Запросы WITH (Общие табличные выражения)

7.8.1. SELECT в WITH

Основное предназначение 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. На сотом шаге, благодаря условию 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
    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

Работая с рекурсивными запросами, важно обеспечить, чтобы рекурсивная часть запроса в конце концов не выдала никаких кортежей (строк), в противном случае цикл будет бесконечным. Иногда для этого достаточно применять UNION вместо UNION ALL, так как при этом будут отбрасываться строки, которые уже есть в результате. Однако часто в цикле выдаются строки, не совпадающие полностью с предыдущими: в таких случаях может иметь смысл проверить одно или несколько полей, чтобы определить, не была ли текущая точка достигнута раньше. Стандартный способ решения подобных задач — вычислить массив с уже обработанными значениями. Например, рассмотрите следующий запрос, просматривающий таблицу graph по полю link:

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
)
SELECT * FROM search_graph;

Этот запрос зациклится, если связи link содержат циклы. Так как нам нужно получать в результате "depth", одно лишь изменение UNION ALL на UNION не позволит избежать зацикливания. Вместо этого мы должны как-то определить, что уже достигали текущей строки, пройдя некоторый путь. Для этого мы добавляем две колонки path и cycle и получаем запрос, защищённый от зацикливания:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
          ARRAY[g.id],
          false
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1,
          path || g.id,
          g.id = ANY(path)
        FROM graph g, search_graph sg
        WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

Помимо предотвращения циклов, значения массива часто бывают полезны сами по себе для представления "пути", приведшего к определённой строке.

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

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
          ARRAY[ROW(g.f1, g.f2)],
          false
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1,
          path || ROW(g.f1, g.f2),
          ROW(g.f1, g.f2) = ANY(path)
        FROM graph g, search_graph sg
        WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

Для тестирования запросов, которые могут зацикливаться, есть хороший приём — добавить LIMIT в родительский запрос. Например, следующий запрос зациклится, если не добавить предложение LIMIT:

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

Но в данном случае этого не происходит, так как в PostgreSQL запрос WITH выдаёт столько строк, сколько фактически принимает родительский запрос. В производственной среде использовать этот приём не рекомендуется, так как другие системы могут вести себя по-другому. Кроме того, это не будет работать, если внешний запрос сортирует результаты рекурсивного запроса или соединяет их с другой таблицей, так как в подобных случаях внешний запрос обычно всё равно выбирает результат запроса WITH полностью.

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

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

7.8.2. Изменение данных в WITH

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

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, как показано в этом примере. Важно понимать, что временная таблица, которую можно будет использовать в остальном запросе, создаётся из результата 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, если оно состоит из нескольких операторов.