Статья основана на двадцать седьмом видео из 31 темы курса SQL 2.0 — PL/pgSQL в PostgreSQL от Аристова Евгения, который является логическим продолжением курса SQL c 0. Ссылки на видео на платформах RUTUBE и VK video.
В данной статье подробно разбираются массивы, их сложные типы, функции для работы с массивами, многомерные массивы.
В прошлой статье мы разобрали обработка ошибок для DML команд и как получить информацию об ошибке.
Презентация и исходники доступны по ссылке.
Массивы
Это набор однотипных данных. Определение и доступ к элементам заключаются в квадратные скобки. Нумерация элементов идёт с 1.
Примеры одномерных массивов:
DECLARE
-- Простые массивы
numbers INTEGER[];
names TEXT[];
flags BOOLEAN[];
-- С инициализацией
prices NUMERIC[] := ARRAY[10.5, 20.3, 15.7];
cities TEXT[] := ARRAY['Москва', 'СПб', 'Казань'];Массивы. Сложные типы
DECLARE
-- Массивы дат
dates DATE[] := ARRAY['2024-01-01', '2024-01-02'];
-- Массивы JSON
json_data JSON[] := ARRAY['{"name": "John"}', '{"name": "Jane"}'];
-- Массивы UUID
uuids UUID[] := ARRAY[
'550e8400-e29b-41d4-a716-446655440000'::uuid,
'f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid
];
Массивы. Доступ
DECLARE
fruits TEXT[] := ARRAY['яблоко', 'банан', 'апельсин'];
first_fruit TEXT;
last_fruit TEXT;
sliced_fruits TEXT[];
BEGIN
-- Доступ к элементам (индексация с 1)
first_fruit := fruits[1];
last_fruit := fruits[array_length(fruits, 1)];
-- Изменение элементов
fruits[2] := 'груша';
-- Срез массива
sliced_fruits := fruits[1:2]; -- ['яблоко', 'груша']Функции для работы с массивами
-- Добавление элементов
numbers := array_append(numbers, 4); -- [1, 2, 3, 4]
numbers := array_prepend(0, numbers); -- [0, 1, 2, 3, 4]
numbers := array_cat(numbers, ARRAY[5, 6]); -- [0, 1, 2, 3, 4, 5, 6]
-- Удаление элементов
numbers := array_remove(numbers, 3); -- Удалить все 3
numbers := array_remove(numbers, 999); -- Ничего не изменится, так как массив меньшей длины
-- Фильтрация (через unnest)
SELECT ARRAY(SELECT unnest(numbers) WHERE unnest % 2 = 0)
INTO filtered_numbers;Массивы. Доп.функции
-- Длина массива
result := array_length(arr, 1)::text; RETURN NEXT;
-- Поиск элемента
searched_index := array_position(arr, 8);
result := COALESCE(searched_index::text, 'не найден');
-- Содержит ли элемент
result := (5 = ANY(arr))::text;
-- Пересечение массивов
result := array_to_string(arr && ARRAY[3,7], ','); – не сработает, посмотрим на практике
-- Сортировка
sorted_arr := ARRAY(SELECT unnest(arr) ORDER BY 1);
result := array_to_string(sorted_arr, ',');
-- Реверс
reversed_arr := ARRAY(SELECT unnest(arr) ORDER BY ordinality DESC FROM unnest(arr) WITH ORDINALITY); – аналогично нерабочий код
result := array_to_string(reversed_arr, ',');Многомерные массивы
Примеры двухмерных массивов:
DECLARE
numbers INTEGER[][];
names TEXT[][];
-- доступ
i = numbers[1][1]
Трехмерных:
numbers INTEGER[][][];
i = numbers[1][1][1]
Четырёх и т.д. аналогичноЦикл по элементам многомерного массива FOR
Цикл FOREACH очень похож на FOR. Отличие в том, что вместо перебора строк SQL- запроса происходит перебор элементов массива. Синтаксис цикла FOREACH:
[ <<метка>> ]
FOREACH цель [ SLICE размерность_массива] IN ARRAY выражение LOOP
операторы
END LOOP [ метка ];Без указания SLICE, или если SLICE равен 0, цикл выполняется по всем элементам массива, полученного из выражения. Переменной цель последовательно присваивается каждый элемент массива и для него выполняется тело цикла.
Итоги
Массивы в PL/pgSQL — это мощный инструмент для:
- Пакетной обработки данных
- Динамических запросов с множественными условиями
- Временного хранения наборов значений
- Упрощения сложной логики
Особенности:
- аффектит на производительность, особенно удаление элемента
- прямой доступ без проверки value := my_array[100]; — может быть ошибка, если элемента нет или тип не совпадает
- по факту дорого аннестить и собирать обратно — лучше напрямую с массивами работать
Больше примеров доступно на гитхабе и в видео.
В следующей статье мы разберём DML триггеры.
Добавить комментарий