PL/pgSQL
Выполнение запросов
12
Авторские права
© Postgres Professional, 2017–2021
Авторы: Егор Рогов, Павел Лузанов
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Использование команд SQL в коде PL/pgSQL
Устранение неоднозначностей именования
Проверка статуса команды
Табличные функции
3
Команды без результата
Команды SQL встраиваются в код PL/pgSQL
как и в выражениях:
запрос подготавливается,
переменные PL/pgSQL подставляются как параметры
SELECT → PERFORM
удобно для вызова функций с побочными эффектами
запросы, начинающиеся на WITH, надо «оборачивать» в SELECT
INSERT, UPDATE, DELETE и другие команды SQL
кроме служебных команд
управление транзакциями — только в процедурах и анонимных блоках
Как мы уже видели, PL/pgSQL очень тесно интегрирован с SQL.
В частности, все выражения вычисляются с помощью подготовленных
SQL-операторов. При этом в выражениях можно использовать
переменные PL/pgSQL и параметры подпрограмм — они
автоматически подставляются в запрос в виде параметров.
Внутри кода на PL/pgSQL можно выполнять и SQL-запросы. Чтобы
выполнить запрос, не возвращающий результат (INSERT, UPDATE,
DELETE, CREATE, DROP и т. п.), достаточно просто написать команду
SQL внутри кода на PL/pgSQL как отдельный оператор.
Команды подготавливаются точно так же, как и выражения. Это
позволяет закешировать разобранный (или спланированный) запрос и
не выполнять эту часть работы повторно. Внутри команд так же можно
использовать переменные PL/pgSQL, которые будут автоматически
заменены на параметры.
Таким же образом можно вызвать и обычный запрос SELECT, если его
результат не важен — для этого ключевое слово SELECT надо
заменить на PERFORM. Это имеет смысл, например, для вызова
функций с побочным эффектом. Если запрос начинается с WITH, его
необходимо «обернуть» в SELECT (чтобы в конечном итоге запрос
начинался на PERFORM).
Команды COMMIT и ROLLBACK допускаются только в процедурах и
в анонимных блоках кода (выполняемых SQL-командой DO).
5
Одна строка результата
SELECT … INTO
получение первой по порядку строки результата
одна переменная составного типа
или подходящее количество скалярных переменных
INSERT, UPDATE, DELETE RETURNING … INTO
получение вставленной (измененной, удаленной) строки
одна переменная составного типа
или подходящее количество скалярных переменных
Если результат запроса важен, его можно сохранить с помощью фразы
INTO в одну переменную составного типа или в несколько скалярных
переменных. Если запрос возвращает несколько строк, в переменную
попадет только первая из них (порядок можно гарантировать фразой
ORDER BY). Если запрос не возвращает ни одной строки, переменная
получит неопределенное значение.
Аналогично можно использовать команды INSERT, UPDATE, DELETE
с фразой RETURNING. Отличие состоит в том, что эти команды не
должны возвращать более одной строки — это приведет к ошибке, так
как нет способа указать, какая из строк считается «первой».
Если запрос возвращает несколько строк, из которых используется
только одна, это скорее всего говорит о том, что запрос написан
неправильно. PL/pgSQL может сообщать о такой подозрительной
ситуации (и о нескольких других).
Более широкими возможностями обладает стороннее расширение
plpgsql_check (автор — Павел Стехуле).
7
Проверка результата
INTO STRICT
гарантия получения ровно одной строки — ни больше, ни меньше
Диагностика ROW_COUNT
число строк, возвращенных (обработанных) последней командой SQL
Переменная FOUND
после команды SQL: истина, если команда вернула (обработала) строку
после цикла: признак того, что выполнилась хотя бы одна итерация
Добавив к предложению INTO ключевое слово STRICT, мы получим
гарантию того, что команда вернула или обработала ровно одну
строку — иначе будет зафиксирована ошибка.
Кроме того, можно узнать статус только что выполненной команды SQL
(если она не завершилась ошибкой). Для этого есть два способа.
Во-первых, можно получить число строк, затронутых командой,
с помощью конструкции GET DIAGNOSTICS.
Во-вторых, специальная логическая переменная FOUND показывает,
были ли обработаны командой хотя бы какие-то данные.
Переменную FOUND можно использовать и как индикатор того, что
тело цикла выполнилось минимум один раз.
9
Табличные функции
Строки запроса
RETURN QUERY запрос;
Одна строка
RETURN NEXT выражение; если нет выходных параметров
RETURN NEXT; если есть выходные параметры
Особенности
строки добавляются к результату,
но выполнение функции не прекращается
команды можно выполнять несколько раз
результат не возвращается, пока функция не завершится
Чтобы создать на PL/pgSQL табличную функцию, нужно объявить ее
как RETURNS SETOF или RETURNS TABLE (точно так же, как и для
SQL).
Чтобы вернуть из функции множество строк, надо воспользоваться
специальной конструкцией RETURNS QUERY запрос. Результат будет
практически таким же, как в случае SQL-функции, содержащей этот
запрос. Однако запрос из SQL-функции имеет шансы быть
подставленным в объемлющий запрос, а в случае PL/pgSQL-функции
это исключено.
Можно возвращать результат и построчно, используя конструкцию
RETURN NEXT. Она похожа на обычный RETURN, но не прекращает
выполнение функции, а добавляет возвращаемое значение в качестве
очередной строки будущего результата. Команду RETURN NEXT
(и RETURN QUERY тоже) можно вызывать несколько раз.
Окончательный результат будет возвращен только, когда выполнение
функции полностью завершится (для этого можно использовать
обычную команду RETURN). Иными словами, RETURN NEXT не
работает как yield в функциях-генераторах современных языков.
11
Итоги
PL/pgSQL тесно интегрирован с SQL
в процедурном коде можно выполнять запросы
(оформленные как выражения или отдельные команды)
в запросах можно использовать переменные
можно получать результаты запросов и их статус
Нужно следить за неоднозначностями разрешения имен
12
Практика
1. Напишите функцию add_author для добавления новых
авторов. Функция должна принимать три параметра
(фамилия, имя, отчество) и возвращать идентификатор
нового автора.
Проверьте, что приложение позволяет добавлять авторов.
2. Напишите функцию buy_book для покупки книги. Функция
принимает идентификатор книги и уменьшает количество
таких книг на складе на единицу. Возвращаемое значение
отсутствует.
Проверьте, что в «Магазине» появилась возможность
покупки книг.
1.
FUNCTION add_author(last_name text, first_name text, middle_name
text)
RETURNS integer
3.
FUNCTION buy_book(book_id integer)
RETURNS void
Вы можете обратить внимание, что при покупке книг приложение
позволяет «уйти в минус». Если бы количество книг хранилось в
столбце, простым и хорошим решением было бы сделать ограничение
CHECK. Но в нашем случае количество рассчитывается, и мы отложим
написание проверки до темы «Триггеры».
13
Практика
Напишите игру, в которой сервер пытается угадать загаданное
пользователем животное, задавая последовательные
уточняющие вопросы, на которые можно отвечать «да» или
«нет».
Если сервер предложил неправильный вариант, он запрашивает
у пользователя имя животного и отличающий вопрос. Эта новая
информация запоминается и используется в следующих играх.
1. Создайте таблицу для представления информации.
2. Придумайте интерфейс и реализуйте необходимые функции.
3. Проверьте реализацию.
Пример диалога (между людьми):
— Это млекопитающее? — Да.
— Это слон? — Нет.
— Сдаюсь. Кто это? — Кит.
— Как отличить кита от слона? — Он живет в воде.
1. Информацию удобно представить в виде двоичного дерева.
Внутренние узлы хранят вопросы, листовые узлы — названия
животных. Один из дочерних узлов соответствует ответу «да», другой —
ответу «нет».
2. Между вызовами функций надо передавать информацию о том, на
каком узле дерева мы остановились («контекст» диалога). Функции
могут быть, например, такими:
- начать игру (нет входного контекста)
FUNCTION start_game(OUT context integer, OUT question text)
- продолжение игры (получаем ответ, выдаем следующий вопрос)
FUNCTION continue_game(
INOUT context integer, IN answer boolean,
OUT you_win boolean, OUT question text)
- завершение игры (внесение информации о новом животном)
FUNCTION end_game(
IN context integer, IN name text, IN question text)
RETURNS void
