SQL
Процедуры
12
Авторские права
© Postgres Professional, 2017–2021
Авторы: Егор Рогов, Павел Лузанов
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Процедуры и их отличие от функций
Входные и выходные параметры
Перегрузка и полиморфизм
3
Подпрограммы
Функции
вызываются в контексте выражения
не могут управлять транзакциями
возвращают результат
Процедуры
вызываются оператором CALL
могут управлять транзакциями
могут возвращать результат
Процедуры были введены в PostgreSQL 11. Основная причина их
появления состоит в том, что функции не могут управлять
транзакциями. Функции вызываются в контексте какого-либо
выражения, которое вычисляется как часть уже начатого оператора
(например SELECT) в уже начатой транзакции. Нельзя завершить
транзакцию и начать новую «посередине» выполнения оператора.
Процедуры всегда вызываются специальным оператором CALL. Если
этот оператор сам начинает новую транзакцию (а не вызывается из уже
начатой), то в процедуре можно использовать команды управления
транзакциями.
К сожалению, процедуры, написанные на языке SQL, лишены
возможности использовать команды COMMIT и ROLLBACK. Поэтому
пример процедуры, управляющей транзакциями, придется отложить до
темы «PL/pgSQL. Выполнение запросов».
Иногда можно услышать, что процедура отличается от функции тем,
что не возвращает результат. Но это не так — процедуры тоже могут
возвращать результат, если необходимо.
Вместе функции и процедуры называются подпрограммами (routine).
5
Перегрузка
Несколько подпрограмм с одним и тем же именем
подпрограммы различаются типами входных параметров;
тип возвращаемого значения и выходные параметры игнорируются
подходящая подпрограмма выбирается во время выполнения
в зависимости от фактических параметров
Команда CREATE OR REPLACE
при несовпадении типов входных параметров создаст новую
перегруженную подпрограмму
при совпадении — изменит существующую подпрограмму,
но поменять тип возвращаемого значения нельзя
Перегрузка — это возможность использования одного и того же имени
для нескольких подпрограмм (функций или процедур), отличающихся
типами параметров IN и INOUT. Иными словами, сигнатура
подпрограммы — ее имя и типы входных параметров.
При вызове подпрограммы PostgreSQL находит ту подпрограмму,
которая соответствует переданным фактическим параметрам.
Возможны ситуации, когда подходящую подпрограмму невозможно
определить однозначно; в таком случае во время выполнения
возникнет ошибка.
Перегрузку надо учитывать при использовании команды CREATE OR
REPLACE (FUNCTION или PROCEDURE). Дело в том, что при
несовпадении типов входных параметров будет создана новая —
перегруженная — подпрограмма. Кроме того, для функций эта команда
не позволяет изменить тип возвращаемого значения и типы выходных
параметров.
Поэтому при необходимости следует удалять подпрограмму и
создавать ее заново, но это будет уже новая подпрограмма. При
удалении старой функции потребуется также удалить зависящие от нее
представления, триггеры и т. п. (DROP FUNCTION ... CASCADE).
6
Полиморфизм
Подпрограмма, принимающая параметры разных типов
формальные параметры используют полиморфные псевдотипы
(например, anyelement)
конкретный тип данных выбирается во время выполнения
по типу фактических параметров
В некоторых случаях удобно не создавать несколько перегруженных
подпрограмм для разных типов, а написать одну, принимающую
параметры любого (или почти любого) типа.
Для этого в качестве типа формального параметра указывается
специальный полиморфный псевдотип. Пока мы ограничимся одним
типом — anyelement, который соответствует любому базовому типу, —
но позже познакомимся и с другими.
Конкретный тип, с которым будет работать подпрограмма, выбирается
во время выполнения по типу фактического параметра.
Если у подпрограммы определено несколько полиморфных
параметров, то типы соответствующих фактических параметров
должны совпадать. Иными словами, anyelement при каждом вызове
функции обозначает какой-то один конкретный тип.
Если функция объявлена с возвращаемым значением полиморфного
типа, то она должна иметь по крайней мере один входной
полиморфный параметр. Конкретный тип возвращаемого значения
также определяется исходя из типа фактического входного параметра.
8
Итоги
Можно создавать и использовать собственные процедуры
В отличие от функций, процедуры вызываются оператором
CALL и могут управлять транзакциями
Для процедур и функций поддерживаются перегрузка
и полиморфизм
9
Практика
1. В таблице authors имена, фамилии и отчества авторов
по смыслу должны быть уникальны, но это условие никак не
проверяется. Напишите процедуру, удаляющую возможные
дубликаты авторов.
2. Чтобы необходимость в подобной процедуре не возникала,
создайте ограничение целостности, которое не позволит
появляться дубликатам в будущем.
1. В приложении возможность добавлять авторов появится в теме
«PL/pgSQL. Выполнение запросов». А пока для проверки можно
добавить дубликаты в таблицу вручную.
10
Практика
1. Получится ли создать в одной и той же схеме и имеющие
одно и то же имя: 1) процедуру с одним входным параметром,
2) функцию с одним входным параметром того же типа,
возвращающую некоторое значение? Проверьте.
2. В таблице хранятся вещественные числа (например,
результаты каких-либо измерений). Напишите процедуру
нормализации данных, которая умножает все числа на
определенный коэффициент так, чтобы все значения попали
в интервал от −1 до 1.
Процедура должна возвращать выбранный коэффициент.
2. В качестве коэффициента возьмите максимальное абсолютное
значение из таблицы.
