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

с
ложный
 
Поиск
Функция из лис тинга 13.2 допускает только один критерий фильтрации – по 
идентификатору сегмента бронирования.
NORM, однако, позволяет использовать всю поисковую мощь движка ре-
ляционной базы данных и выполнять запросы любой сложности, при этом 
предоставляя результаты в формате, который удобен для приложения.
Следующий шаг уже должен быть очевиден. Мы объединяем динамиче-
ский запрос, который мы написали в главе 12 (лис тинг 12.12), с тем, как мы 

---

252

Как избежать подводных камней объектно-реляционного отображения
форматируем вывод функции (лис тинге 13.5). Результат представлен в лис-
тинге 13.7. Здесь мы показываем упрощенную версию функции.
Листинг 13.7 

Поиск по сложным критериям
CREATE OR REPLACE FUNCTION search_booking_leg(p_json json)
RETURNS booking_leg_record[]
AS $func$
DECLARE
v_search_condition text := null;
v_rec record;
v_result booking_leg_record[];
v_sql text :=
$$SELECT array_agg(single_item)
FROM ( SELECT row(
bl.booking_leg_id,
leg_num,
bl.booking_id,
( SELECT row(
flight_id,
flight_no,
departure_airport,
da.airport_name,
arrival_airport,
aa.airport_name,
scheduled_departure,
scheduled_arrival
)::flight_record
FROM flight f
JOIN airport da
ON da.airport_code = departure_airport
JOIN airport aa
ON aa.airport_code = arrival_airport
WHERE flight_id = bl.flight_id
),
( SELECT array_agg (row(
pass_id,
bp.booking_leg_id,
flight_no,
departure_airport,
arrival_airport,
last_name,
first_name,
seat,
boarding_time
)::boarding_pass_record)
FROM flight f1
JOIN boarding_pass bp
ON f1.flight_id = bl.flight_id
AND bp.booking_leg_id = bl.booking_leg_id
JOIN passenger p
ON p.passenger_id = bp.passenger_id)

---

Сложный поиск 

253
)::booking_leg_record AS single_item
FROM booking_leg bl$$;
v_where_booking_leg text;
v_where_flight text;
BEGIN
FOR v_rec IN (SELECT * FROM json_each_text(p_json))
LOOP
CASE
WHEN v_rec.key IN ('departure_airport','arrival_airport') THEN
IF v_where_flight IS NULL THEN
v_where_flight :=
v_rec.key || '=' || quote_literal(v_rec.value);
ELSE
v_where_flight := v_where_flight || ' AND ' ||
v_rec.key || '=' || quote_literal(v_rec.value);
END IF;
WHEN v_rec.key = 'scheduled_departure' THEN
IF v_where_flight IS NULL THEN
v_where_flight :=
v_rec.key || $$ BETWEEN $$ ||
quote_literal(v_rec.value) || $$::date AND $$ ||
quote_literal(v_rec.value) || $$::date + 1$$;
ELSE
v_where_flight :=
v_where_flight || ' AND ' ||
v_rec.key || $$ BETWEEN $$ ||
quote_literal(v_rec.value) || $$::date AND $$ ||
quote_literal(v_rec.value) || $$::date + 1$$;
END IF;
WHEN v_rec.key = 'flight_id' THEN
v_where_booking_leg := 'bl.flight_id = ' || v_rec.value;
ELSE
NULL;
END CASE;
END LOOP;
IF v_where_flight IS NULL THEN
v_search_condition :=
$$ WHERE $$ || v_where_booking_leg;
ELSE
v_search_condition :=
$$ JOIN flight f1 ON f1.flight_id = bl.flight_id
WHERE $$ || concat_ws(' AND ', v_where_flight, v_where_booking_leg);
END IF;
v_sql := v_sql || v_search_condition || ') s';
EXECUTE v_sql INTO v_result;
RETURN (v_result);
END;
$func$
LANGUAGE plpgsql;
И снова здесь много кода, но разные его части уже появлялись в главе 12 
и в начале этой главы. Динамические критерии поиска построены аналогич-

---

254

Как избежать подводных камней объектно-реляционного отображения
но примеру, представленному в лис тинге 12.12, а список SELECT такой же, 
как и в лис тинге 13.5.
Мы считаем необходимым показать вам рабочий пример. Вы можете вы-
полнить этот код в своей локальной копии 
postgres_air
и попробовать вы-
зывать функцию с другими параметрами.
Как мы уже неоднократно упоминали, построение функции с динамиче-
ским SQL – непростая задача, особенно вначале, и отладка займет дополни-
тельное время. Когда вкладывать это время в процесс разработки – решать 
вам. Мы призываем вас поэкспериментировать с этими функциями, чтобы 
убедиться в их эффективности и стабильном времени выполнения.
Кроме того, когда вы разработаете один набор функций для классов одного 
из объектов приложения, то увидите, что вам будет намного проще создать 
аналогичные наборы функций для всех классов объектов, используя один 
и тот же шаблон.
Наконец, мы хотели бы упомянуть, что есть и другие способы создания 
динамического SQL для произвольных критериев поиска. В репозитории 
NORM на GitHub вы можете найти другие примеры.