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

и
сПользование
 
динамического
 sQL 
для
 
гиБкости
Техника, описанная в этом разделе, чаще всего используется в системах OLTP, 
хотя, опять же, строгого ограничения на тип окружения здесь нет.
Часто системы позволяют пользователю выбирать произвольный список 
критериев поиска, возможно, с помощью выпадающих списков или других 
графических способов построения запросов.
Пользователь ничего не знает (и не должен знать) о том, как данные хра-
нятся в базе. Однако поля поиска могут принадлежать разным таблицам, 

---

Использование динамического SQL для гибкости 

231
критерии поиска могут иметь разную селективность, и, в общем, запросы 
могут сильно отличаться в зависимости от выбранных критериев.
Посмотрим на один пример. Предположим, нам необходима функция для 
поиска бронирования по любой комбинации следующих значений:
 

адрес электронной почты (или начальная часть адреса);
 

аэропорт вылета;
 

аэропорт прибытия;
 

дата отправления;
 

идентификатор рейса.
Можно ли обойтись без Elasticsearch, чтобы эффективно реализовать эту 
функцию?
Когда нужно реализовать такую функциональность, разработчик обычно 
придумывает что-нибудь, похожее на функцию из лис тинга 12.11.
Листинг 12.11 

Функция для поиска по различным комбинациям параметров
CREATE TYPE booking_record_basic AS (
booking_id bigint,
booking_ref text,
booking_name text ,
account_id integer,
email text
);
CREATE OR REPLACE FUNCTION select_booking (
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$
BEGIN
RETURN QUERY
SELECT DISTINCT
b.booking_id,
b.booking_ref,
booking_name,
account_id,
email
FROM booking b
JOIN booking_leg bl USING (booking_id)
JOIN flight f USING (flight_id)
WHERE (p_email IS NULL OR lower(email) LIKE p_email||'%')
AND (p_dep_airport IS NULL OR departure_airport = p_dep_airport)
AND (p_arr_airport IS NULL OR arrival_airport = p_arr_airport)
AND (p_dep_date IS NULL OR scheduled_departure BETWEEN p_dep_date
AND p_dep_date + 1)
AND (p_flight_id IS NULL OR bl.flight_id = p_flight_id);
END;
$func$
LANGUAGE plpgsql;

---

232

Динамический SQL
Эта функция всегда будет возвращать правильный результат, но с точки 
зрения производительности ее поведение как минимум трудно предсказать. 
Обратите внимание, что при поиске по адресу электронной почты соедине-
ния с таблицами 
booking_leg
и 
flight
не нужны, но они все равно будут вы-
полняться.
Давайте сравним время выполнения на нескольких примерах.