PL/pgSQL
Массивы
12
Авторские права
© Postgres Professional, 2017–2021
Авторы: Егор Рогов, Павел Лузанов
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Массивы и работа с ними в PL/pgSQL
Циклы по элементам массивов
Функции с переменным числом параметров
и полиморфные функции
Использование массивов в столбцах таблиц
3
Тип массива
Массив
набор пронумерованных элементов одного и того же типа
одномерные, многомерные
Создание
использование без явного определения (имя-типа[])
неявно при создании базового типа или таблицы (_имя-типа)
Использование
элементы как скалярные значения
срезы массива
операции с массивами: сравнение, вхождение, пересечение,
конкатенация, использование с ANY и ALL вместо подзапроса, ...
Массив, как и составной тип (запись), не является скалярным и состоит
из нескольких элементов другого типа. Но, в отличие от записей, а) все
эти элементы имеют одинаковый тип и б) обращение к ним происходит
не по имени, а по целочисленному индексу (здесь индекс понимается
в математическом смысле, а не как индекс БД).
Тип массива не надо специально объявлять, достаточно добавить
квадратные скобки к имени типа элементов. (При создании любого
базового типа или таблицы автоматически создается соответствующий
тип массива. Он получает то же имя, что и тип элемента, но
с подчеркиванием впереди. Но такое имя выглядит не столь наглядно,
как квадратные скобки.)
Массив является полноценным типом SQL, его можно использовать как
любой другой тип: создавать столбцы таблиц этого типа, использовать
его для параметров функций и т. п. Элементы массива могут
использоваться как обычные скалярные значения. Можно использовать
и срезы (slice) массивов.
Массивы можно сравнивать, проверять на неопределенность,
определять вхождение элемента и находить пересечение с другими
массивами, конкатенировать и пр. Также массивы можно использовать
по аналогии с подзапросами в конструкциях ANY/SOME и ALL.
Различные функции для работы с массивами можно найти
в справочном материале, прилагаемом к курсу.
5
Массивы и циклы
Обычный цикл по индексам элементов
array_lower
array_upper
Цикл FOREACH по элементам массива
проще, но индексы элементов недоступны
Для итерации по элементам массива вполне можно использовать
обычный целочисленный цикл FOR от минимального до максимального
индекса массива.
Однако есть и специализированный вариант цикла: FOREACH. В таком
варианте переменная цикла пробегает не индексы элементов, а сами
элементы. Поэтому переменная должна иметь тот же тип, что и
элементы массива (как обычно, если элементами являются записи, то
одну переменную составного типа можно заменить несколькими
скалярными переменными).
Тот же цикл с фразой SLICE позволяет итерировать срезы массива.
Например, для двумерного массива одномерными срезами будут его
строки.
7
Массивы и подпрограммы
Подпрограммы с переменным числом параметров
все необязательные параметры должны иметь одинаковый тип
необязательные параметры передаются в подпрограмму как массив
последний формальный параметр-массив объявляется как VARIADIC
Полиморфные подпрограммы
работают со значениями разных типов;
тип конкретизируется во время выполнения
используют полиморфные псевдотипы anyarray и anynonarray
могут иметь переменное число параметров
Массивы позволяют создавать подпрограммы (функции или
процедуры) с переменным числом параметров.
В отличие от параметров со значениями по умолчанию, которые при
объявлении подпрограммы надо явно перечислить, необязательных
параметров может быть сколько угодно и все они передаются
подпрограмме в виде массива. Но, как следствие, все они должны
иметь один и тот же тип.
При объявлении подпрограммы последний параметр помечается как
VARIADIC и должен иметь тип массива.
Мы уже говорили про полиморфные подпрограммы, которые могут
работать с параметрами разных типов. При объявлении подпрограммы
указывается специальный полиморфный псевдотип, а конкретный тип
уточняется во время выполнения по фактическому типу переданных
параметров.
Для массивов есть отдельный полиморфный тип anyarray
(и anynonarray для не-массивов).
Этот тип можно использовать совместно с передачей переменного
числа аргументов при объявлении VARIADIC-параметра.
9
Массив или таблица?
1 ... {A}
2 ... {B,C,D}
3 ... {A,C}
1 ...
2 ...
3 ...
1 1
2 2
2 3
2 4
3 1
3 3
компактное представление
не требуется соединение
удобно в простых случаях
отдельные таблицы:
многие ко многим
универсальное решение
1 A ...
2 B ...
3 C ...
4 D ...
Классический реляционный подход предполагает, что в таблице
хранятся атомарные значения (первая нормальная форма). Язык SQL
не имеет средств для «заглядывания внутрь» сложносоставных
значений.
Поэтому обычный подход состоит в создании отдельной таблицы,
связанной с основной отношением «многие ко многим».
Тем не менее, мы можем создать таблицу со столбцом типа массива.
PostgreSQL имеет богатый набор функций для работы с массивами,
а поиск элемента в массиве может быть ускорен специальными
индексами (такие индексы рассматриваются в курсе DEV2).
Такой подход бывает удобен: получается компактное представление,
не требующее соединений. В частности, массивы активно
используются в системном каталоге PostgreSQL.
Какое решение выбрать? Зависит от того, какие ставятся задачи, какие
требуются операции. Рассмотрим пример.
11
Итоги
Массив состоит из пронумерованных элементов
одного и того же типа данных
Столбец с массивом как альтернатива отдельной таблице:
удобные операции и индексная поддержка
Позволяет создавать функции с переменным числом
параметров
12
Практика
1. Создайте функцию add_book для добавления новой книги.
Функция должна принимать два параметра —
название книги и массив идентификаторов авторов —
и возвращать идентификатор новой книги.
Проверьте, что в приложении появилась возможность
добавлять книги.
1.
FUNCTION add_book(title text, authors integer[])
RETURNS integer
13
Практика
1. Реализуйте функцию map, принимающую два параметра:
массив вещественных чисел и название вспомогательной
функции, принимающей один параметр вещественного типа.
Функция возвращает массив, полученный из исходного
применением вспомогательной функции к каждому
элементу.
2. Реализуйте функцию reduce, принимающую два параметра:
массив вещественных чисел и название вспомогательной
функции, принимающей два параметра вещественного типа.
Функция возвращает вещественное число, полученное
последовательной сверткой массива слева направо.
3. Сделайте функции map и reduce полиморфными.
1. Например:
map(ARRAY[4.0,9.0],'sqrt') → ARRAY[2.0,3.0]
2. Например:
reduce(ARRAY[1.0,3.0,2.0,0.5],'greatest') → 3.0
В этом случае значение вычисляется как
greatest( greatest( greatest(1.0,3.0), 2.0 ), 0.5 )
