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

234

Динамический SQL
Листинг 12.12 

Функция, создающая динамический SQL для поиска
по разным критериям
CREATE OR REPLACE FUNCTION select_booking_dyn (
p_email text,
p_dep_airport text,
p_arr_airport text,
p_dep_date date,
p_flight_id int
)
RETURNS SETOF booking_record_basic
AS $func$
DECLARE
v_sql text := 'SELECT DISTINCT
b.booking_id,
b.booking_ref,
booking_name,
account_id,
email
FROM booking b ';
v_where_booking text;
v_where_booking_leg text;
v_where_flight text;
BEGIN
IF p_email IS NOT NULL THEN
v_where_booking := $$ lower(email) LIKE $$ || quote_literal(p_email||'%');
END IF;
IF p_flight_id IS NOT NULL THEN
v_where_booking_leg := $$ flight_id = $$ || p_flight_id::text;
END IF;
IF p_dep_airport IS NOT NULL THEN
v_where_flight := concat_ws(
$$ AND $$,
v_where_flight,
$$departure_airport = $$ || quote_literal(p_dep_airport));
END IF;
IF p_arr_airport IS NOT NULL THEN
v_where_flight := concat_ws(
$$ AND $$,
v_where_flight,
$$arrival_airport = $$ || quote_literal(p_arr_airport));
END IF;
IF p_dep_date IS NOT NULL THEN
v_where_flight := concat_ws(
$$ AND $$,
v_where_flight,
$$scheduled_departure BETWEEN $$ || quote_literal(p_dep_date) ||
$$::date AND $$ || quote_literal(p_dep_date) || $$::date + 1$$);
END IF;
IF v_where_flight IS NOT NULL OR v_where_booking_leg IS NOT NULL THEN
v_sql := v_sql || $$ JOIN booking_leg bl USING (booking_id) $$;
END IF;

---

Использование динамического SQL для гибкости 

235
IF v_where_flight IS NOT NULL THEN
v_sql := v_sql || $$ JOIN flight f USING (flight_id) $$;
END IF;
v_sql := v_sql || $$ WHERE $$ || concat_ws(
$$ AND $$,
v_where_booking, v_where_booking_leg, v_where_flight);
--RAISE NOTICE 'sql:%', v_sql;
RETURN QUERY EXECUTE (v_sql);
END;
$func$
LANGUAGE plpgsql;
Здесь очень много кода! Давайте пройдемся по нему и разберемся, что 
именно тут происходит.
Параметры новой функции точно такие же, как и у старой, и тип результата 
тоже совпадает, но тело функции полностью другое. На высоком уровне эта 
функция создает в текстовой переменной 
v_sql
инструкцию, которая затем 
выполняется.
Динамическое построение запроса позволяет нам включать в него только 
те соединения, которые необходимы. Таблица 
booking
нужна всегда, поэтому 
начальное значение переменной 
v_sql
равно
'SELECT DISTINCT
b.booking_id,
b.booking_ref,
booking_name,
account_id,
email
FROM booking b '
Затем, в зависимости от того, какие параметры передаются непустыми (
NOT
NULL
), функция определяет, какие еще таблицы необходимы. Это может быть 
только таблица 
booking_leg
, если не заданы параметры, связанные с рейсом, 
или это могут быть обе таблицы 
booking_leg
и 
flight
.
После добавления всех необходимых таблиц строятся критерии поиска 
путем объединения всех условий с разделителем 
AND
. На этом построение 
оператора 
v_sql
завершается, и он выполняется. Чтобы увидеть окончатель-
ный запрос для различных вызовов функции, раскомментируйте инструк-
цию 
RAISE
NOTICE
.
Не слишком ли много работы для повышения производительности? Попро-
буйте создать эту функцию и выполнить ее с параметрами из предыдущих 
примеров. Быстро станет ясно, что время выполнения функции 
select_book-
ing_dyn
не превышает время выполнения соответствующих инструкций SQL 
для каждого набора параметров. Причем время выполнения предсказуемо 
и не зависит от истории вызовов в текущем сеансе.
Да, динамические функции нелегко отлаживать, и может потребоваться 
включить отладочную печать, но если производительность промышленной 
системы действительно важна, то результаты того стоят.

---