PL/pgSQL
Выполнение запросов
16
Авторские права
© Postgres Professional, 2017–2024
Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
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).
Напомним также, что в подпрограммах нельзя использовать служебные
команды, такие как VACUUM, REINDEX и т. п.
Команды 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).
Отдельные элементы возвращаемого значения формируются
в специальном буфере командами RETURN QUERY и(или) RETURN
NEXT. Эти операторы не завершают выполнение, их вызовы можно
смешивать и вызывать несколько раз в теле функции, при этом их
результаты будут объединены. По окончании работы функции (вызов
RETURN без параметров или достижение конца ее тела) будет
возвращен результирующий набор строк из буфера.
Если после выполнения одиночной команды RETURN QUERY запрос
выполнение будет завершено, то результат функции будет таким же,
как в случае SQL-функции, содержащей этот запрос в качестве
последнего. Однако запрос из SQL-функции имеет шансы быть
подставленным в объемлющий запрос, а в случае PL/pgSQL это
исключено.
Команда RETURN NEXT накапливает результат функции построчно,
она похожа на обычный RETURN, но, как мы уже отмечали,
не прекращает выполнение функции, а добавляет возвращаемое
значение в качестве очередной строки будущего результата.
Таким образом, команды RETURN NEXT и RETURN QUERY
не работают как 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
