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

Для предотвращения зацикливания, значение массива часто полезно для представления самого себя как "path", чтобы понять, что была достигнула любая отдельная строка.

В большинстве случаев, когда для определения зацикливания нужна проверка более чем одного поля, используйте массив строк. Например, если вам необходимо сравнить поля 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);

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

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

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

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

внешний SELECT должен вернуть первоначальные цены (prices) перед выполнением UPDATE, в то время как

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

внешний SELECT должен вернуть обновлённые данные.

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

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