|
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, и мы не рекомендуем изменять его, если у вас
нет на то действительно веских причин.
Функции и производительность
Do'stlaringiz bilan baham: |
