Общие табличные выражения postgresql
Обновлено: 05.05.2024
Основное предназначение SELECT в предложении WITH заключается в разбиении сложных запросов на простые части. Например, запрос:
выводит итоги по продажам только для передовых регионов. Предложение WITH определяет два дополнительных оператора regional_sales и top_regions так, что результат regional_sales используется в top_regions , а результат top_regions используется в основном запросе SELECT . Этот пример можно было бы переписать без WITH , но тогда нам понадобятся два уровня вложенных подзапросов SELECT . Показанным выше способом это можно сделать немного проще.
Необязательное указание RECURSIVE превращает WITH из просто удобной синтаксической конструкции в средство реализации того, что невозможно в стандартном SQL. Используя RECURSIVE , запрос WITH может обращаться к собственному результату. Очень простой пример, суммирующий числа от 1 до 100:
В общем виде рекурсивный запрос WITH всегда записывается как не рекурсивная часть, потом UNION (или UNION ALL ), а затем рекурсивная часть, где только в рекурсивной части можно обратиться к результату запроса. Такой запрос выполняется следующим образом:
Вычисляется не рекурсивная часть. Для UNION (но не UNION ALL ) отбрасываются дублирующиеся строки. Все оставшиеся строки включаются в результат рекурсивного запроса и также помещаются во временную рабочую таблицу.
Вычисляется рекурсивная часть так, что рекурсивная ссылка на сам запрос обращается к текущему содержимому рабочей таблицы. Для UNION (но не UNION ALL ) отбрасываются дублирующиеся строки и строки, дублирующие ранее полученные. Все оставшиеся строки включаются в результат рекурсивного запроса и также помещаются во временную промежуточную таблицу.
Примечание
Строго говоря, этот процесс является итерационным, а не рекурсивным, но комитетом по стандартам SQL был выбран термин RECURSIVE .
В показанном выше примере в рабочей таблице на каждом этапе содержится всего одна строка и в ней последовательно накапливаются значения от 1 до 100. На сотом шаге, благодаря условию WHERE , не возвращается ничего, так что вычисление запроса завершается.
Рекурсивные запросы обычно применяются для работы с иерархическими или древовидными структурами данных. В качестве полезного примера можно привести запрос, находящий все непосредственные и косвенные составные части продукта, используя только таблицу с прямыми связями:
Работая с рекурсивными запросами, важно обеспечить, чтобы рекурсивная часть запроса в конце концов не выдала никаких кортежей (строк), в противном случае цикл будет бесконечным. Иногда для этого достаточно применять UNION вместо UNION ALL , так как при этом будут отбрасываться строки, которые уже есть в результате. Однако часто в цикле выдаются строки, не совпадающие полностью с предыдущими: в таких случаях может иметь смысл проверить одно или несколько полей, чтобы определить, не была ли текущая точка достигнута раньше. Стандартный способ решения подобных задач — вычислить массив с уже обработанными значениями. Например, рассмотрите следующий запрос, просматривающий таблицу graph по полю link :
Этот запрос зациклится, если связи link содержат циклы. Так как нам нужно получать в результате « depth » , одно лишь изменение UNION ALL на UNION не позволит избежать зацикливания. Вместо этого мы должны как-то определить, что уже достигали текущей строки, пройдя некоторый путь. Для этого мы добавляем два столбца path и cycle и получаем запрос, защищённый от зацикливания:
Помимо предотвращения циклов, значения массива часто бывают полезны сами по себе для представления « пути » , приведшего к определённой строке.
В общем случае, когда для выявления цикла нужно проверять несколько полей, следует использовать массив строк. Например, если нужно сравнить поля f1 и f2 :
Подсказка
Часто для распознавания цикла достаточного одного поля и тогда ROW() можно опустить. При этом будет использоваться не массив данных составного типа, а простой массив, что более эффективно.
Подсказка
Этот алгоритм рекурсивного вычисления запроса выдаёт в результате узлы, упорядоченные по пути погружения. Чтобы получить результаты, отсортированные по глубине, можно добавить во внешний запрос ORDER BY по столбцу « path » , полученному, как показано выше.
Для тестирования запросов, которые могут зацикливаться, есть хороший приём — добавить LIMIT в родительский запрос. Например, следующий запрос зациклится, если не добавить предложение LIMIT :
Но в данном случае этого не происходит, так как в PostgreSQL запрос WITH выдаёт столько строк, сколько фактически принимает родительский запрос. В производственной среде использовать этот приём не рекомендуется, так как другие системы могут вести себя по-другому. Кроме того, это не будет работать, если внешний запрос сортирует результаты рекурсивного запроса или соединяет их с другой таблицей, так как в подобных случаях внешний запрос обычно всё равно выбирает результат запроса WITH полностью.
Запросы WITH имеют полезное свойство — они вычисляются только раз для всего родительского запроса, даже если этот запрос или соседние запросы WITH обращаются к ним неоднократно. Таким образом, сложные вычисления, результаты которых нужны в нескольких местах, можно выносить в запросы WITH в целях оптимизации. Кроме того, такие запросы позволяют избежать нежелательных вычислений функций с побочными эффектами. Однако есть и обратная сторона — оптимизатор не может распространить ограничения родительского запроса на запрос WITH так, как он делает это для обычного подзапроса. Запрос WITH обычно выполняется буквально и возвращает все строки, включая те, что потом может отбросить родительский запрос. (Но как было сказано выше, вычисление может остановиться раньше, если в ссылке на этот запрос затребуется только ограниченное число строк.)
Примеры выше показывают только предложение WITH с SELECT , но таким же образом его можно использовать с командами INSERT , UPDATE и DELETE . В каждом случае он по сути создаёт временную таблицу, к которой можно обратиться в основной команде.
7.8.2. Изменение данных в WITH
В предложении WITH можно также использовать операторы, изменяющие данные ( INSERT , UPDATE или DELETE ). Это позволяет выполнять в одном запросе сразу несколько разных операций. Например:
Этот запрос фактически перемещает строки из products в products_log . Оператор DELETE в WITH удаляет указанные строки из products и возвращает их содержимое в предложении RETURNING ; а затем главный запрос читает это содержимое и вставляет в таблицу products_log .
Следует заметить, что предложение WITH в данном случае присоединяется к оператору INSERT , а не к SELECT , вложенному в INSERT . Это необходимо, так как WITH может содержать операторы, изменяющие данные, только на верхнем уровне запроса. Однако при этом применяются обычные правила видимости WITH , так что к результату WITH можно обратиться и из вложенного оператора SELECT .
Операторы, изменяющие данные, в WITH обычно дополняются предложением RETURNING (см. Раздел 6.4), как показано в этом примере. Важно понимать, что временная таблица, которую можно будет использовать в остальном запросе, создаётся из результата RETURNING , а не целевой таблицы оператора. Если оператор, изменяющий данные, в WITH не дополнен предложением RETURNING , временная таблица не создаётся и обращаться к ней в остальном запросе нельзя. Однако такой запрос всё равно будет выполнен. Например, допустим следующий не очень практичный запрос:
Он удалит все строки из таблиц foo и bar . При этом число задействованных строк, которое получит клиент, будет подсчитываться только по строкам, удалённым из bar .
Рекурсивные ссылки в операторах, изменяющих данные, не допускаются. В некоторых случаях это ограничение можно обойти, обратившись к конечному результату рекурсивного WITH , например так:
Этот запрос удаляет все непосредственные и косвенные составные части продукта.
Операторы, изменяющие данные в WITH , выполняются только один раз и всегда полностью, вне зависимости от того, принимает ли их результат основной запрос. Заметьте, что это отличается от поведения SELECT в WITH : как говорилось в предыдущем разделе, SELECT выполняется только до тех пор, пока его результаты востребованы основным запросом.
Вложенные операторы в WITH выполняются одновременно друг с другом и с основным запросом. Таким образом, порядок, в котором операторы в WITH будут фактически изменять данные, непредсказуем. Все эти операторы выполняются с одним снимком данных (см. Главу 13), так что они не могут « видеть » , как каждый из них меняет целевые таблицы. Это уменьшает эффект непредсказуемости фактического порядка изменения строк и означает, что RETURNING — единственный вариант передачи изменений от вложенных операторов WITH основному запросу. Например, в данном случае:
внешний оператор SELECT выдаст цены, которые были до действия UPDATE , тогда как в запросе
внешний SELECT выдаст изменённые данные.
Неоднократное изменение одной и той же строки в рамках одного оператора не поддерживается. Иметь место будет только одно из нескольких изменений и надёжно определить, какое именно, часто довольно сложно (а иногда и вовсе невозможно). Это так же касается случая, когда строка удаляется и изменяется в том же операторе: в результате может быть выполнено только обновление. Поэтому в общем случае следует избегать подобного наложения операций. В частности, избегайте подзапросов WITH , которые могут повлиять на строки, изменяемые основным оператором или операторами, вложенные в него. Результат действия таких запросов будет непредсказуемым.
В настоящее время, для оператора, изменяющего данные в WITH , в качестве целевой нельзя использовать таблицу, для которой определено условное правило или правило ALSO или INSTEAD , если оно состоит из нескольких операторов.
Читайте также: