PL/pgSQL
Обзор и конструкции языка
16
Авторские права
© Postgres Professional, 2017–2024
Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
История PL/pgSQL
Структура блока, объявление переменных
Анонимные блоки
Подпрограммы на языке PL/pgSQL
Условные операторы и циклы
Вычисление выражений
3
История PL/pgSQL
Появился в версии 6.4 в 1998 году
устанавливается по умолчанию с версии 9.0
Цели создания
простой язык для написания пользовательских функций и триггеров
добавить управляющие структуры к языку SQL
сохранить возможность использования любых пользовательских типов,
функций и операторов
Родословная: Oracle PL/SQL, Ада
PL/pgSQL — один из первых процедурных языков в PostgreSQL.
Он впервые появился в 1998 году в версии 6.4, а с версии 9.0 стал
устанавливаться по умолчанию при создании базы данных.
PL/pgSQL дополняет SQL, предоставляя такие возможности
процедурных языков, как использование переменных и курсоров,
условные операторы, циклы, обработка ошибок и т. д.
PL/pgSQL проектировался на основе языка Oracle PL/SQL, а тот,
в свою очередь, был создан на основе подмножества языка Ада.
Эта ветвь происходит от таких языков, как Алгол и Паскаль.
Большинство современных языков программирования принадлежат
другой ветви — ветви Си-подобных языков, поэтому PL/pgSQL может
показаться непривычным, избыточно многословным (характерное
отличие — использование ключевых слов BEGIN и END вместо
фигурных скобок). Впрочем, этот синтаксис отлично сочетается
с синтаксисом SQL.
4
Структура блока
Метка блока
Объявления переменных
область действия — блок
область видимости может перекрываться вложенными блоками,
но можно использовать метку блока
любые типы SQL, ссылки на типы объектов (%TYPE)
Операторы
управляющие конструкции
операторы SQL, кроме служебных
Обработка исключительных ситуаций
Операторы языка PL/pgSQL объединяются в блоки. В структуре блока
можно выделить:
●
Необязательную метку, которую можно использовать для устранения
неоднозначности в именовании.
●
Необязательную секцию, предназначенную для объявления
локальных переменных и курсоров. В качестве типа можно указать
любой тип, определенный в SQL. Можно также сослаться на тип
столбца таблицы с помощью конструкции %TYPE.
●
Основную секцию исполнения, в которой располагаются операторы.
●
Необязательную секцию обработки исключительных ситуаций.
В качестве операторов можно использовать как команды PL/pgSQL,
так и большинство команд SQL, то есть этих два языка интегрированы
практически бесшовно. Нельзя использовать служебные команды SQL,
такие, как VACUUM. А команды управления транзакциями (например,
COMMIT, ROLLBACK) допускаются только в процедурах.
В качестве оператора может выступать и другой (вложенный)
PL/pgSQL-блок.
5
Анонимные блоки
Разовое выполнение процедурного кода
без создания хранимой подпрограммы
без возможности передать параметры
без возможности вернуть значение
Оператор DO языка SQL
Для использования PL/pgSQL не обязательно создавать
подпрограммы. Код на PL/pgSQL можно оформить и выполнить как
анонимный блок, при помощи SQL-команды DO.
Эту команду можно использовать с разными серверными языками,
но если язык не указать явно, то будет использоваться PL/pgSQL.
Код анонимного блока не сохраняется на сервере. Также нет
возможности передать в анонимный блок параметры или вернуть
из него значение. Хотя косвенно это можно сделать, например, через
таблицы.
7
Подпрограммы PL/pgSQL
Заголовок подпрограммы не зависит от языка
имя, входные и выходные параметры
для функций: возвращаемое значение, категория изменчивости
Указание LANGUAGE plpgsql
Возврат значений
оператор RETURN
присвоение значений выходным (INOUT, OUT) параметрам
Мы уже познакомились с хранимыми функциями и процедурами
на примере языка SQL. Большинство рассмотренных вопросов,
связанных с созданием и управлением подпрограммами, относится
и к подпрограммам на PL/pgSQL:
●
создание, изменение, удаление подпрограмм;
●
расположение в системном каталоге (pg_proc);
●
параметры;
●
возвращаемое значение и категории изменчивости (для функций);
●
перегрузка и полиморфизм;
●
и т. д.
Если в SQL подпрограмма возвращала значение, выданное последним
SQL-оператором, то в подпрограммах на PL/pgSQL требуется либо
присваивать возвращаемые значения формальным INOUT- и OUT-
параметрам, либо (для функций) использовать специальный оператор
RETURN.
9
Условные операторы
IF
стандартный условный оператор
CASE
похож на CASE языка SQL, но не возвращает значение
Внимание: трехзначная логика
условие должно быть истинно; false и NULL не подходят
PL/pgSQL предлагает два условных оператора: IF и CASE.
Первый — совершенно стандартный оператор, имеющийся во всех
языках.
Второй похож на конструкцию CASE в языке SQL, но это именно
оператор, он не возвращает значение. Это некий аналог оператора
switch в C или Java.
Важно всегда помнить о том, что логические выражения в SQL
(и, следовательно, в PL/pgSQL) могут принимать три значения:
true, false и NULL. Условие срабатывает, только когда оно истинно,
и не срабатывает, когда ложно или не определено. Это одинаково
верно и для условий WHERE в SQL, и для условных операторов
PL/pgSQL.
11
Циклы
Цикл FOR по диапазону чисел
Цикл WHILE с предусловием
Бесконечный цикл
Цикл может иметь метку, как блок
Управление
выход из цикла (EXIT)
переход на новую итерацию (CONTINUE)
Для повторного выполнения набора операторов PL/pgSQL предлагает
несколько видов циклов:
●
цикл FOR по диапазону целых чисел;
●
цикл WHILE с предусловием;
●
бесконечный цикл.
Цикл представляет собой разновидность блока и может иметь
собственную метку.
Выполнением цикла можно дополнительно управлять, инициируя
переход на новую итерацию или выход из цикла.
Цикл FOR может работать не только по диапазону чисел, но и по
результатам выполнения запроса и по массивам. Эти варианты цикла
FOR будут рассмотрены в следующих темах.
13
Вычисление выражений
Любое выражение вычисляется в контексте SQL
выражение автоматически преобразуется в запрос
запрос подготавливается
переменные PL/pgSQL подставляются как параметры
Особенности
можно использовать все возможности SQL, включая подзапросы
невысокая скорость выполнения,
хотя разобранный запрос (и, возможно, план запроса) кешируются
неоднозначности при разрешении имен требуют внимания
Выражения, встречающиеся в PL/pgSQL-коде, вычисляются с помощью
SQL-запросов к базе данных. Интерпретатор сам строит нужный
запрос, формируя оператор вида SELECT <expr> и заменяя
переменные PL/pgSQL на параметры, затем подготавливает его
(при этом разобранный запрос кешируется, как это обычно и
происходит с подготовленными операторами), после чего планирует
и выполняет.
С одной стороны, такой подход не способствует скорости работы
PL/pgSQL, зато обеспечивает теснейшую интеграцию с SQL.
Фактически, в выражениях можно использовать любые возможности
SQL без ограничений, включая вызов встроенных и пользовательских
функций, выполнение подзапросов и т. п.
Начиная с версии PostgreSQL 14 оптимизировано выполнение простых
выражений (как минимум не содержащих обращений к таблицам) —
такие выражения обрабатываются синтаксическим анализатором
сервера напрямую, без обращений к планировщику.
15
Итоги
PL/pgSQL — доступный по умолчанию, интегрированный
с SQL, удобный и простой в использовании язык
Управление подпрограммами на PL/pgSQL не отличается
от работы с подпрограммами на других языках
DO — команда SQL для выполнения анонимного блока
Переменные PL/pgSQL могут использовать любые
типы SQL
Язык поддерживает обычные управляющие конструкции,
такие как условные операторы и циклы
16
Практика
1. Измените функцию book_name так, чтобы длина
возвращаемого значения не превышала 45 символов.
Если название книги при этом обрезается, оно должно
завершаться на троеточие.
Проверьте реализацию в SQL и в приложении;
при необходимости добавьте книг с длинными названиями.
2. Снова измените функцию book_name так, чтобы избыточно
длинное название обрезалось по границе целого слова
(укорачивание по словам).
Проверьте реализацию.
1. Например:
Путешествия в некоторые удалённые страны мира в четырёх частях:
сочинение Лемюэля Гулливера, сначала хирурга, а затем капитана
нескольких кораблей →
→ Путешествия в некоторые удалённые страны м...
Вот некоторые случаи, которые имеет смысл проверить:
●
длина названия меньше 45 символов (не должно измениться);
●
длина названия ровно 45 символов (не должно измениться);
●
длина названия 46 символов (от названия должны быть отрезаны
4 символа, т. к. добавятся еще три точки).
Лучше всего написать и отладить отдельную функцию укорачивания,
которую затем использовать в book_name. Это полезно и по другим
соображениям:
●
такая функция может пригодиться где-то еще;
●
каждая функция будет выполнять ровно одну задачу.
2. Например:
Путешествия в некоторые удалённые страны мира в четырёх частях:
сочинение Лемюэля Гулливера, сначала хирурга, а затем капитана
нескольких кораблей →
→ Путешествия в некоторые удалённые страны...
Как поведет себя ваша реализация, если название состоит из одного
длинного слова без пробелов?
17
Практика+
1. Напишите PL/pgSQL-функцию, которая возвращает строку
заданной длины из случайных символов.
2. Задача про игру в «наперстки».
В одном из трех наперстков спрятан выигрыш.
Игрок выбирает один из этих трех. Ведущий убирает один
из двух оставшихся наперстков (обязательно пустой) и дает
игроку возможность поменять решение, то есть выбрать
второй из двух оставшихся.
Есть ли смысл игроку менять выбор или нет смысла менять
первоначальный вариант?
Задание: используя PL/pgSQL, посчитайте вероятность
выигрыша и для начального выбора, и для измененного.
Предварительно можно создать функцию rnd_integer, которая
возвращает случайное целое число в заданном диапазоне. Функция
будет полезна для решения обоих заданий.
Например: rnd_integer(30, 1000) → 616
1. Помимо длины строки на вход функции можно подавать список
допустимых символов. По умолчанию, это могут быть все символы
алфавита, числа и некоторые знаки. Для определения случайных
символов из списка можно использовать функцию rnd_integer.
Объявление функции может быть таким:
CREATE FUNCTION rnd_text(
len int,
list_of_chars text DEFAULT
'АБВГДЕЁЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯабвгдеёжзийклмнопрстуфхцчшщъыьэю
яABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_0123456789'
) RETURNS text AS ...
Пример вызова: rnd_text(10) → 'ЛжdфbЁ_OОJ'
2. Для решения можно использовать анонимный блок.
Сначала нужно реализовать одну игру и посмотреть, какой вариант
выиграл: начальный или измененный. Для загадывания и угадывания
одного из трех наперстков можно использовать rnd_integer(1,3).
Затем игру поместить в цикл и «сыграть», например, 1000 раз,
подсчитывая, какой вариант сколько раз победил. В конце через RAISE
NOTICE вывести значения счетчиков и выявить победивший вариант
(или отсутствие такового).
