Руководство по созданию эффективных запросов

Листинг 12.7 

Функция, выполняющая динамический SQL
CREATE OR REPLACE FUNCTION select_booking_leg_country_dynamic (
p_country text,
p_updated timestamptz
)
RETURNS SETOF booking_leg_part
AS $body$
BEGIN
RETURN QUERY EXECUTE $$
SELECT departure_airport,
booking_id,
is_returning
FROM booking_leg bl
JOIN flight f USING (flight_id)
WHERE departure_airport IN (
SELECT airport_code
FROM airport
WHERE iso_country = $$|| quote_literal(p_country) ||$$
)
AND bl.booking_id IN (
SELECT booking_id
FROM booking
WHERE update_ts > $$|| quote_literal(p_updated) ||$$
);$$
END;
%body$
LANGUAGE plpgsql;
Также обратите внимание на использование функции 
quote_literal
для 
защиты от внедрения SQL-кода.
Это первая, но не единственная причина, почему динамический SQL 
в функциях оказывается полезен. Позже мы рассмотрим другие случаи в под-
держку данного утверждения.
к
ак
 
исПользовать
 
динамический
 sQL 
в
 
системах
 oLAP
Не поймите название этого раздела неправильно. Способ, который мы про-
демонстрируем, можно использовать в любой системе, однако наиболее 
впечатляющих результатов можно достичь при большом результирующем 
множестве. Чем больше строк в результате, тем сильнее проявляется выгода.
Представим, что для статистического анализа нам нужно отсортировать 
пассажиров по возрасту.

---

228

Динамический SQL
Функция определения возрастных категорий представлена в лис тинге 12.8.
Листинг 12.8 

Функция, назначающая возрастную категорию
CREATE OR REPLACE FUNCTION age_category (p_age int)
RETURNS TEXT
LANGUAGE plpgsql
AS $body$
BEGIN
RETURN (CASE
WHEN p_age <= 2 THEN 'Infant'
WHEN p_age <= 12 THEN 'Child'
WHEN p_age < 65 THEN 'Adult'
ELSE 'Senior'
END);
END;
$body$;
Если эта функция используется для статистических отчетов, нам может 
потребоваться вычислить возрастную категорию для всех пассажиров. В гла-
ве 11 мы упоминали, что выполнение функций из списка SELECT может за-
медлить работу, но функции были более сложными. Здесь функция 
age_cat-
egory
выполняет очень простую замену. Тем не менее вызов функции требует 
времени. Таким образом, для выполнения
SELECT passenger_id, age_category(age)
FROM passenger
LIMIT 5000000
требуется 25 секунд, в то время как
SELECT passenger_id,
CASE
WHEN age <= 2 THEN 'Infant'
WHEN age <= 12 THEN 'Child'
WHEN age < 65 THEN 'Adult'
ELSE 'Senior'
END
FROM passenger
LIMIT 5000000
занимает всего 6 секунд.
В данном конкретном случае использование функции не является обяза-
тельным, потому что она нужна нам всего один раз, и даже в одной из наших 
самых больших таблиц, 
passenger
, только 16 млн строк.
В реальных аналитических запросах количество строк, которые нужно 
обработать, может исчисляться сотнями миллионов, и может понадобиться 
использовать несколько подобных функций. В одном из реальных случаев 
время выполнения с функциями составляло четыре часа, а замена всего лишь 
одной функции на оператор 
CASE
сократила время до полутора часов.
Означает ли это, что мы хотим любой ценой избегать использования функ-
ций в списке SELECT? Возможно, есть причина, по которой наша аналитиче-

---

Как использовать динамический SQL в системах OLAP 

229
ская команда хочет упаковать назначение возрастной категории в функцию. 
Скорее всего, они собираются использовать эту функцию в разных запросах 
и с разными таблицами и не хотят пересматривать и исправлять все свои 
запросы, если алгоритм назначения категории изменится.
Более производительное решение, сохраняющее простоту сопровождения, 
состоит в создании другой функции, которая содержит 
фрагмент
кода
в
виде
текста
, 
–
см. лис тинг 12.9.