SQL
Функции
12
Авторские права
© Postgres Professional, 2017–2021
Авторы: Егор Рогов, Павел Лузанов
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Функции и их особенности в базах данных
Параметры и возвращаемое значение
Способы передачи параметров при вызове
Категории изменчивости и оптимизация
3
Функции в базе данных
Основной мотив: упрощение задачи
интерфейс (параметры) и реализация (тело функции)
о функции можно думать вне контекста всей задачи
Традиционные языки PostgreSQL
побочные глобальные переменные вся база данных
эффекты (категории изменчивости)
модули со своим интерфейсом пространства имен,
и реализацией клиент и сервер
сложности накладные расходы скрытие запроса
на вызов от планировщика
(подстановка) (подстановка,
подзапросы, представления)
Основная цель появления функций в программировании вообще —
упростить решаемую задачу за счет ее декомпозиции на более мелкие
подзадачи. Упрощение достигается за счет того, что о функции можно
думать, абстрагировавшись от «большой» задачи. Для этого функция
определяет четкий интерфейс с внешним миром (параметры и
возвращаемое значение). Ее реализация (тело функции) может
меняться; вызывающая сторона «не видит» этих изменений и не
зависит от них. Этой идеальной ситуации может мешать глобальное
состояние (глобальные переменные), и надо учитывать, что в случае
БД таким состоянием является вся база данных.
В традиционных языках функции часто объединяются в модули
(пакеты, классы для ООП и т. п.), имеющие собственный интерфейс
и реализацию. Границы модулей могут проводиться более или менее
произвольно. Для PostgreSQL есть жесткая граница между клиентской
частью и серверной: серверный код работает с базой, клиентский —
управляет транзакциями. Модули (пакеты) отсутствуют, есть только
пространства имен.
Для традиционных языков единственный минус широкого
использования функций состоит в накладных расходах на ее вызов.
Иногда его преодолевают с помощью подстановки (inlining) кода
функции в вызывающую программу. Для БД последствия могут быть
более серьезные: если в функцию выносится часть запроса,
планировщик перестает видеть «общую картину» и не может построить
хороший план. В некоторых случаях PostgreSQL умеет выполнять
подстановку; альтернативные варианты — использование подзапросов
или представлений.
4
Общие сведения
Объект базы данных
определение хранится в системном каталоге
Основные составные части определения
имя
параметры
тип возвращаемого значения
тело
Доступны несколько языков, в том числе SQL
код в виде строковой константы
интерпретируется при вызове
Вызывается в контексте выражения
Функции являются такими же объектами базы данных, как, например,
таблицы и индексы. Определение функции сохраняется в системном
каталоге; поэтому функции в базе данных называют хранимыми.
В PostgreSQL доступно большое количество стандартных функций.
С некоторыми их которых можно познакомиться в справочном
материале «Основные типы данных и функции».
И, конечно, можно писать собственные функции на разных языках
программирования. Материал этой темы относится к функциям на
любом языке, но примеры будут использовать язык SQL.
Определение функции — довольно ожидаемо — состоит из имени,
необязательных параметров, типа возвращаемого значения, и тела.
Что может показаться неожиданным — тело записывается в виде
строковой константы, которая содержит код на выбранном языке
программирования. За счет этого определение функции выглядит
одинаково независимо от выбранного языка. Тело-строка сохраняется
в системном каталоге и интерпретируется каждый раз, когда функция
вызывается. В настоящий момент единственный способ избежать
интерпретации — написать функцию на языке Си, но это требуется
нечасто, и в данном курсе эта тема не рассматривается.
Функция всегда вызывается в контексте какого-либо выражения.
Например, в списке выражений команды SELECT, в условии WHERE,
в ограничении целостности CHECK и т. п.
6
Вход и выход
Входные значения
определяются параметрами с режимом IN и INOUT
Выходное значение
определяется либо предложением RETURNS,
либо параметрами с режимом INOUT и OUT
если одновременно указаны обе формы, они должны быть согласованы
Формальные параметры с режимом IN и INOUT считаются входными.
Значения соответствующих фактических параметров должны быть
указаны при вызове функции (либо должны быть определены значения
по умолчанию).
Возвращаемое значение можно определить двумя способами:
- использовать предложение RETURNS для указания типа;
- определить выходные параметры с режимом INOUT или OUT.
Две эти формы записи эквивалентны. Например, функция с указанием
RETURNS integer и функция с параметром OUT integer возвращают
целое число.
Можно использовать и оба способа одновременно. В этом случае
функция также будет возвращать одно целое число. Но при этом типы
RETURNS и выходных параметров должны быть согласованы друг
с другом.
Таким образом, нельзя написать функцию, которая будет возвращать
одно значение, и при этом передавать другое значение в OUT-
параметр — что позволяет большинство традиционных языков
программирования. В PostgreSQL такая функция будет возвращать
оба значения.
8
Категории изменчивости
Volatile
возвращаемое значение может произвольно меняться
при одинаковых значениях входных параметров
используется по умолчанию
Stable
значение не меняется в пределах одного оператора SQL
функция не может менять состояние базы данных
Immutable
значение не меняется, функция детерминирована
функция не может менять состояние базы данных
Каждой функции сопоставлена категория изменчивости, которая
определяет свойства возвращаемого значения при одинаковых
значениях входных параметров.
Категория volatile говорит о том, что возвращаемое значение может
произвольно меняться. Такие функции будут вычисляться при каждом
вызове. Если при создании функции категория не указана, назначается
именно эта категория.
Категория stable используется для функций, возвращаемое значение
которых не меняется в пределах одного SQL-оператора. В частности,
такие функции не могут менять состояние БД. Такая функция может
быть выполнена один раз во время выполнения запроса, а затем будет
использоваться вычисленное значение.
Категория immutable еще более строгая: возвращаемое значение не
меняется никогда. Такую функцию можно вычислить на этапе
планирования запроса, а не во время выполнения.
Можно — не означает, что всегда происходит именно так, но
планировщик вправе выполнить такие оптимизации. В некоторых
(простых) случаях планировщик делает собственные выводы об
изменчивости функции, невзирая на указанную явно категорию.
10
Итоги
Можно создавать собственные функции
и использовать их так же, как и встроенные
Функции можно писать на разных языках, в том числе SQL
Изменчивость влияет на возможности оптимизации
Иногда функция на SQL может быть подставлена в запрос
11
Практика
1. Создайте функцию author_name для формирования имени
автора. Функция принимает три параметра (фамилия, имя,
отчество) и возвращает строку с фамилией и инициалами.
Используйте эту функцию в представлении authors_v.
2. Создайте функцию book_name для формирования названия
книги.
Функция принимает два параметра (идентификатор книги
и заголовок) и возвращает строку, составленную из
заголовка и списка авторов в порядке seq_num. Имя каждого
автора формируется функций author_name.
Используйте эту функцию в представлении catalog_v.
Проверьте изменения в приложении.
Напомним, что необходимые функции можно посмотреть в раздаточном
материале «Основные типы данных и функции».
1. FUNCTION author_name(
last_name text, first_name text, surname text
)
RETURNS text
Например: author_name('Толстой','Лев','Николаевич') →
→ 'Толстой Л. Н.'
2. FUNCTION book_name(book_id integer, title text)
RETURNS text
Например: book_name(3,'Трудно быть богом') →
→ 'Трудно быть богом. Стругацкий А. Н., Стругацкий Б. Н.'
Все инструменты позволяют «непосредственно» редактировать
хранимые функции. Например, в psql есть команда \ef, открывающая
текст функции в редакторе и сохраняющая изменения в базу.
Такой возможностью лучше не пользоваться (или как минимум не
злоупотреблять). Нормально построенный процесс разработки
предполагает хранение всего кода в файлах под версионным
контролем. При необходимости изменить функцию файл редактируется
и выполняется (с помощью psql или средствами IDE). Если же менять
определение функций сразу в БД, изменения легко потерять. (Вообще
же вопрос организации процесса разработки намного сложнее и в курсе
мы его не затрагиваем.)
12
Практика
1. Напишите функцию, выдающую случайное время,
равномерно распределенное в указанном отрезке.
Начало отрезка задается временной отметкой (timestamptz),
конец — либо временной отметкой, либо интервалом
(interval).
2. В таблице хранятся номера автомобилей, введенные кое-как:
встречаются как латинские, так и русские буквы в любом
регистре; между буквами и цифрами могут быть пробелы.
Считая, что формат номера «буква три-цифры две-буквы»,
напишите функцию, выдающую число уникальных номеров.
Например, «К 123 ХМ» и «k123xm» считаются равными.
3. Напишите функцию, находящую корни квадратного
уравнения.
Во всех заданиях обратите особое внимание на категорию
изменчивости функций.
2. Сначала напишите функцию «нормализации» номера, то есть
приводящую номер к какому-нибудь стандартному виду. Например, без
пробелов и только заглавными латинскими буквами.
В номерах используются только 12 русских букв, имеющих латинские
аналоги схожего начертания, а именно: АВЕКМНОРСТУХ.
3. Для уравнения y = ax
2
+ bx + c:
дискриминант D = b
2
– 4ac.
●
при D > 0 два корня x
1,2
= (–b ± √D) / 2a
●
при D = 0 один корень x = –b / 2a (в качестве x
2
можно вернуть null)
●
при D < 0 корней нет (оба корня null).
