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