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

201
Листинг 11.11 

Создание функции, которое завершается без ошибок
CREATE OR REPLACE FUNCTION num_passengers (p_airport_code text, p_departure date)
RETURNS integer
AS $$
BEGIN
RETURN (
SELECT count(*)
FROM booking_leg bl
JOIN booking b USING (booking_id)
JOIN passenger p USING (booking_id)
JOIN flight f USING (flight_id)
WHERE airport_code = p_airport_code
AND scheduled_departure BETWEEN p_date AND p_date + 1
);
END;
$$ LANGUAGE plpgsql;
Что пошло не так? Функция использует 
airport_code
вместо 
departure_air-
port
. Такую ошибку легко совершить, но может оказаться неожиданным, что 
PostgreSQL не сообщил о ней при создании функции.
Теперь, если вы исправите эту ошибку и снова создадите функцию (см. 
лис тинг 11.12), при ее выполнении вы получите еще одну ошибку:
ERROR: column "p_date" does not exist
Листинг 11.12 

Создаем функцию: одна ошибка исправлена, осталась еще одна
CREATE OR REPLACE FUNCTION num_passengers (p_airport_code text, p_departure date)
RETURNS integer
AS $$
BEGIN
RETURN (
SELECT count(*)
FROM booking_leg bl
JOIN booking b USING (booking_id)
JOIN passenger p USING (booking_id)
JOIN flight f USING (flight_id)
WHERE departure_airport = p_airport_code
AND scheduled_departure BETWEEN p_date AND p_date + 1
);
END;
$$ LANGUAGE plpgsql;
И PostgreSQL прав, поскольку параметр называется 
p_departure_date
, 
а не 
p_date
. Тем не менее почему сообщение об этой ошибке не появилось 
раньше?
Во время создания функции PostgreSQL выполняет только начальный раз-
бор текста, в процессе которого обнаруживаются лишь тривиальные синтак-
сические ошибки. Все более сложное не проявится до момента выполнения. 
Это плохие новости, если вы только что пришли с Oracle и предполагаете, что 

---

202

Функции
при создании функции движок базы данных компилирует ее и сохраняется 
в скомпилированном виде. Функции не только хранятся в виде исходного 
кода, но и, в отличие от других СУБД, они интерпретируются, а не компили-
руются.
Интерпретатор PL/pgSQL разбирает исходный текст функции и создает 
(внутреннее) дерево инструкций при первом вызове функции в каждом сеан-
се. Даже в этом случае отдельные выражения SQL и команды, используемые 
в функции, не транслируются тотчас же. Только когда выполнение доходит 
до определенной команды, она анализируется и для нее создается 
подго-
товленный
оператор
. Он будет использован повторно, если та же функция 
будет выполнена снова в том же сеансе. Одно из следствий этого состоит 
в том, что если ваша функция содержит условия (например, инструкции 
IF
THEN
ELSE
или 
CASE
), вы можете не обнаружить даже синтаксическую ошибку 
в своем коде, если не наткнулись на нее во время выполнения. Мы видели, 
как такие неприятные открытия совершались уже после того, как функция 
перешла в промышленное окружение. Подводя итог, можно сказать, что при 
создании функции PL/pgSQL:
1) план выполнения не сохраняется;
2) никакие проверки на наличие таблиц, столбцов или других функций 
не выполняются;
3) вы не знаете, работает ваша функция или нет, пока не выполните ее 
(и, как правило, не один раз, если есть несколько путей выполнения).
Еще одно важное свойство функций PostgreSQL, которое следует из пре-
дыдущего объяснения, состоит в том, что функции являются «атомарными» 
в нескольких разных смыслах. Во-первых (к разочарованию пользователей 
Oracle), вы не можете начинать транзакции внутри функций PostgreSQL, 
поэтому инструкции DML – это всегда «все или ничего». Во-вторых, когда 
оптимизируется план выполнения запроса, планировщик PostgreSQL ничего 
не знает о пользовательских функциях. Например, выполните
EXPLAIN SELECT num_passengers(13)
План выполнения будет выглядеть примерно так:
Result (cost=0.00..0.26 rows=1 width=4)
Если вам нужно выяснить, какие планы используются для выполнения 
запросов внутри функции, вам нужно будет подставить какие-нибудь фак-
тические значения вместо параметров и для каждой инструкции выполнить 
команду 
EXPLAIN
.
Одно из ключевых слов в команде 
CREATE
FUNCTION
(помните, мы перечисли-
ли не все из них!) – 
COST
. Оно позволяет разработчику явно установить стои-
мость выполнения функции, которая будет использоваться оптимизатором. 
Значение по умолчанию – 100, и мы не рекомендуем изменять его, если у вас 
нет на то действительно веских причин.

---

Функции и производительность 
