# Занятие 15: PL/pgSQL / Массивы

> Исходная страница в нативном Markdown: PL/pgSQL / Массивы

---

Индекс LLMS: [llms.txt](/ru/llms.txt)

---

<!-- Generated by scripts/generate_skeleton.py. -->

> [!NOTE]
> Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета.
> **HTML source:** [dev1_15_plpgsql_arrays.html](https://edu.postgrespro.ru/16/dev1-16/dev1_15_plpgsql_arrays.html)
> **Archive:** [DEV1-handouts-16.zip](https://edu.postgrespro.ru/16/DEV1-handouts-16.zip)
> **Страниц PDF:** 24

## Page 1: PL/pgSQL / Массивы {#source-page-001}

### Авторские права

© Postgres Professional, 2017–2024 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
### Использование материалов курса

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.
### Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru
### Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения.

## Page 2: Темы {#source-page-002}

- Массивы и работа с ними в PL/pgSQL
- Циклы по элементам массивов
- Функции с переменным числом параметров и полиморфные функции
- Использование массивов в столбцах таблиц

## Page 3: Тип массива {#source-page-003}

**Массив**
  - набор пронумерованных элементов одного и того же типа
  - одномерные, многомерные
- Создание
```text
использование без явного определения (имя-типа[])
неявно при создании типа или таблицы (_имя-типа)
```
- Использование
  - элементы как скалярные значения
  - срезы массива
  - операции с массивами: сравнение, вхождение, пересечение,
  - конкатенация, использование с ANY и ALL вместо подзапроса, ...

### Комментарии преподавателя

Массив, как и составной тип (запись), не является скалярным и состоит из нескольких элементов другого типа. Но, в отличие от записей, а) все эти элементы имеют одинаковый тип и б) обращение к ним происходит не по имени, а по целочисленному индексу (здесь индекс понимается в математическом смысле, а не как индекс БД).

Тип массива не надо специально объявлять, поскольку при создании любого типа (в том числе таблицы) создается также тип массива элементов этого типа с именем \_тип. При объявлении переменной-массива достаточно добавить квадратные скобки к имени типа элементов.

Массив является полноценным типом SQL, его можно использовать как любой другой тип: создавать столбцы таблиц этого типа, использовать его для параметров функций и т. п. Элементы массива могут использоваться как обычные скалярные значения. Можно использовать и срезы (slice) массивов.

Массивы можно сравнивать, проверять на неопределенность, искать вхождение элемента и находить пересечение с другими массивами, конкатенировать и пр. Также массивы можно использовать по аналогии с подзапросами в конструкциях ANY/SOME и ALL.

<https://postgrespro.ru/docs/postgresql/16/arrays>

Различные функции для работы с массивами можно найти в справочном материале, прилагаемом к курсу.

## Page 4: Инициализация и обращение к элементам {#source-page-004}

Объявление переменной и инициализация массива целиком:
```text
=> DO $$
DECLARE
    a integer[2]; -- размер игнорируется
BEGIN
    a := ARRAY[10,20,30];
    RAISE NOTICE '%', a;
    -- по умолчанию элементы нумеруются с единицы
    RAISE NOTICE 'a[1] = %, a[2] = %, a[3] = %', a[1], a[2], a[3];
    -- срез массива
    RAISE NOTICE 'Срез [2:3] = %', a[2:3];
    -- присваиваем значения срезу массива
    a[2:3] := ARRAY[222,333];
    -- выводим весь массив
    RAISE NOTICE '%', a;
END;
$$ LANGUAGE plpgsql;
```
```text
NOTICE:  {10,20,30}
NOTICE:  a[1] = 10, a[2] = 20, a[3] = 30
NOTICE:  Срез [2:3] = {20,30}
NOTICE:  {10,222,333}
DO
```
Одномерный массив можно заполнять и поэлементно — при необходимости он автоматически расширяется. Если пропустить какие-то элементы, они получают неопределенные значения.
Что будет выведено?
```text
=> DO $$
DECLARE
    a integer[];
BEGIN
    a[2] := 10;
    a[3] := 20;
    a[6] := 30;
    RAISE NOTICE '%', a;
END;
$$ LANGUAGE plpgsql;
```
```text
NOTICE:  [2:6]={10,20,NULL,NULL,30}
DO
```
Поскольку нумерация началась не с единицы, перед самим массивом дополнительно выводится диапазон номеров элементов.
Мы можем определить составной тип и создать массив из элементов этого типа:
```sql
=> CREATE TYPE currency AS (amount numeric, code text);
```
```sql
CREATE TYPE
```
```text
=> DO $$
DECLARE
    c currency[];  -- массив из элементов составного типа
BEGIN
  -- присваиваем значения отдельным элементам
    c[1].amount := 10;  c[1].code := 'RUB';
    c[2].amount := 50;  c[2].code := 'KZT';
    RAISE NOTICE '%', c;
END
$$ LANGUAGE plpgsql;
```
```text
NOTICE:  {"(10,RUB)","(50,KZT)"}
DO
```
Еще один способ получить массив — создать его из подзапроса:

## Page 5: Page 5 {#source-page-005}

```sql
=> DO $$
DECLARE
    a integer[];
BEGIN
    a := ARRAY( SELECT n FROM generate_series(1,3) n );
    RAISE NOTICE '%', a;
END
```
```text
$$ LANGUAGE plpgsql;
```
```text
NOTICE:  {1,2,3}
DO
```
Можно и наоборот, массив преобразовать в таблицу:
```sql
=> SELECT unnest( ARRAY[1,2,3] );
```
```text
 unnest
--------
      1
      2
      3
(3 rows)
```
Интересно, что выражение IN со списком значений преобразуется в поиск по массиву:
```sql
=> EXPLAIN (costs off)
SELECT * FROM generate_series(1,10) g(id) WHERE id IN (1,2,3);
```
```text
                 QUERY PLAN
---------------------------------------------
 Function Scan on generate_series g
   Filter: (id = ANY ('{1,2,3}'::integer[]))
(2 rows)
```
Двумерный массив — прямоугольная матрица, память под которую выделяется при инициализации. Литерал выглядит как массив массивов, имеющих одинаковое число элементов. Здесь мы использовали другой способ инициализации — с помощью символьной строки.
После инициализации многомерный массив уже нельзя расширить.
```text
=> DO $$
DECLARE
    a integer[][] := '{
        { 10, 20, 30},
        {100,200,300}
    }';
BEGIN
    RAISE NOTICE 'Двумерный массив : %', a;
    RAISE NOTICE 'Неограниченный срез массива [2:] = %', a[2:];
    -- присваиваем значения этому срезу
    a[2:] := ARRAY[ARRAY[111, 222, 333]];
    -- снова выводим весь массив
    RAISE NOTICE '%', a;
    -- расширять нельзя ни по какому измерению
    a[4][4] := 1;
END
$$ LANGUAGE plpgsql;
```
```text
NOTICE:  Двумерный массив : {{10,20,30},{100,200,300}}
NOTICE:  Неограниченный срез массива [2:] = {{100,200,300}}
NOTICE:  {{10,20,30},{111,222,333}}
ERROR:  array subscript out of range
CONTEXT:  PL/pgSQL function inline_code_block line 15 at assignment
```

## Page 6: Массивы и циклы {#source-page-006}

**Обычный цикл по индексам элементов**
  - array\_lower
  - array\_upper
- Цикл FOREACH по элементам массива
  - проще, но индексы элементов недоступны

### Комментарии преподавателя

Для итерации по элементам массива вполне можно использовать обычный целочисленный цикл FOR, используя функции, возвращающие минимальный и максимальный индексы массива.

Однако есть и специализированный вариант цикла: FOREACH. В таком варианте переменная цикла пробегает не индексы элементов, а сами элементы. Поэтому переменная должна иметь тот же тип, что и элементы массива (как обычно, если элементами являются записи, то одну переменную составного типа можно заменить несколькими скалярными переменными).

Тот же цикл с фразой SLICE позволяет итерировать срезы массива. Например, для двумерного массива одномерными срезами будут его строки.

<https://postgrespro.ru/docs/postgresql/16/plpgsql-control-structures#PLPGS> QL-FOREACH-ARRAY

## Page 7: Массивы и циклы {#source-page-007}

Цикл можно организовать, итерируя индексы элементов массива. Второй параметр функций array\_lower и array\_upper — номер размерности (единица для одномерных массивов).
```text
=> DO $$
DECLARE
    a integer[] := ARRAY[10,20,30];
BEGIN
    FOR i IN array_lower(a,1)..array_upper(a,1) LOOP
        RAISE NOTICE 'a[%] = %', i, a[i];
    END LOOP;
END
$$ LANGUAGE plpgsql;
```
```text
NOTICE:  a[1] = 10
NOTICE:  a[2] = 20
NOTICE:  a[3] = 30
DO
```
Если индексы не нужны, то проще итерировать сами элементы:
```text
=> DO $$
DECLARE
    a integer[] := ARRAY[10,20,30];
    x integer;
BEGIN
    FOREACH x IN ARRAY a LOOP
        RAISE NOTICE '%', x;
    END LOOP;
END
$$ LANGUAGE plpgsql;
```
```text
NOTICE:  10
NOTICE:  20
NOTICE:  30
DO
```
Итерация индексов в двумерном массиве:
```text
=> DO $$
DECLARE
    -- можно и без двойных квадратных скобок
    a integer[] := ARRAY[
        ARRAY[ 10, 20, 30],
        ARRAY[100,200,300]
    ];
BEGIN
    FOR i IN array_lower(a,1)..array_upper(a,1) LOOP -- по строкам
        FOR j IN array_lower(a,2)..array_upper(a,2) LOOP -- по столбцам
            RAISE NOTICE 'a[%][%] = %', i, j, a[i][j];
        END LOOP;
    END LOOP;
END
$$ LANGUAGE plpgsql;
```
```text
NOTICE:  a[1][1] = 10
NOTICE:  a[1][2] = 20
NOTICE:  a[1][3] = 30
NOTICE:  a[2][1] = 100
NOTICE:  a[2][2] = 200
NOTICE:  a[2][3] = 300
DO
```
Итерация элементов двумерного массива не требует вложенного цикла:

## Page 8: Page 8 {#source-page-008}

```text
=> DO $$
DECLARE
    a integer[] := ARRAY[
        ARRAY[ 10, 20, 30],
        ARRAY[100,200,300]
    ];
    x integer;
BEGIN
    FOREACH x IN ARRAY a LOOP
        RAISE NOTICE '%', x;
    END LOOP;
```
```text
END
$$ LANGUAGE plpgsql;
```
```text
NOTICE:  10
NOTICE:  20
NOTICE:  30
NOTICE:  100
NOTICE:  200
NOTICE:  300
DO
```
Существует также возможность выполнять в подобном цикле итерацию по срезам, а не по отдельным элементам. Значение SLICE в такой конструкции должно быть целым числом, не превышающим размерность массива, а переменная, куда читаются срезы, сама должна быть массивом. В примере — цикл по одномерным срезам:
```text
=> DO $$
DECLARE
    a integer[] := ARRAY[
        ARRAY[ 10, 20, 30],
        ARRAY[100,200,300]
    ];
    x integer[];
BEGIN
    FOREACH x SLICE 1 IN ARRAY a LOOP
        RAISE NOTICE '%', x;
    END LOOP;
END
$$ LANGUAGE plpgsql;
```
```text
NOTICE:  {10,20,30}
NOTICE:  {100,200,300}
DO
```

## Page 9: Массивы и подпрограммы {#source-page-009}

**Подпрограммы с переменным числом параметров**
  - все необязательные параметры должны иметь одинаковый тип
  - передаются в подпрограмму в виде массива
  - последний параметр-массив определяется с режимом VARIADIC
- Полиморфные подпрограммы
  - работают со значениями разных типов;
  - тип конкретизируется во время выполнения
  - дополнительные полиморфные псевдотипы anyarray, anynonarray,
  - anycompatiblearray и anycompatiblenonarrray
  - могут иметь переменное число параметров

### Комментарии преподавателя

Массивы позволяют создавать подпрограммы (функции или процедуры) с переменным числом параметров.

В отличие от параметров со значениями по умолчанию, которые при объявлении подпрограммы надо явно перечислить, необязательных параметров может быть сколько угодно и все они передаются подпрограмме в виде массива. Но, как следствие, все они должны иметь один и тот же (или совместимый, в случае использования anycompatible/anycompatiblearray) тип.

При объявлении подпрограммы последним указывается один параметр с режимом VARIADIC, имеющий тип массива.

<https://postgrespro.ru/docs/postgresql/16/xfunc-sql#XFUNC-SQL-VARIADIC> -FUNCTIONS

Мы уже говорили про полиморфные подпрограммы, которые могут работать с параметрами разных типов. При объявлении подпрограммы указывается специальный полиморфный псевдотип, а конкретный тип уточняется во время выполнения по фактическому типу переданных параметров.

Для массивов есть отдельные полиморфные типы anyarray, anycompatiblearray (и anynonarray, anycompatiblenonarrray для не-массивов).

Эти типы можно использовать совместно с передачей переменного числа аргументов при объявлении VARIADIC-параметра.

<https://postgrespro.ru/docs/postgresql/16/xfunc-sql#XFUNC-SQL-POLYMO> RPHIC-FUNCTIONS

## Page 10: Массивы и подпрограммы {#source-page-010}

В теме «SQL. Процедуры» мы рассматривали перегрузку и полиморфизм и создали функцию maximum, которая находила максимальное из трех чисел. Обобщим ее на произвольное число аргументов. Для этого объявим один VARIADIC-параметр:
```sql
=> CREATE FUNCTION maximum(VARIADIC a integer[]) RETURNS integer
AS $$
DECLARE
    x integer;
    maxsofar integer;
BEGIN
    FOREACH x IN ARRAY a LOOP
        IF x IS NOT NULL AND (maxsofar IS NULL OR x > maxsofar) THEN
            maxsofar := x;
        END IF;
    END LOOP;
    RETURN maxsofar;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
Пробуем:
```sql
=> SELECT maximum(12, 65, 47);
```
```text
 maximum
---------
      65
(1 row)
```
```sql
=> SELECT maximum(12, 65, 47, null, 87, 24);
```
```text
 maximum
---------
      87
(1 row)
```
```sql
=> SELECT maximum(null, null);
```
```text
 maximum
---------
(1 row)
```
Для полноты картины и эта функция может быть сделана полиморфной, чтобы принимать любой тип данных (для которого, конечно, должны быть определены операции сравнения).
```sql
=> DROP FUNCTION maximum(integer[]);
```
```sql
DROP FUNCTION
```
Полиморфные типы anycompatiblearray и anycompatible (а также anyarray и anyelement) всегда согласованы между собой: anycompatiblearray = anycompatible&#91;&#93;, anyarray = anyelement&#91;&#93;; Нам нужна переменная, имеющая тип элемента массива. Но объявить ее как anycompatible нельзя — она должна иметь реальный тип. Здесь помогает конструкция %TYPE.
```sql
=> CREATE FUNCTION maximum(VARIADIC a anycompatiblearray, maxsofar OUT anycompatible)
AS $$
DECLARE
    x maxsofar%TYPE;
BEGIN
    FOREACH x IN ARRAY a LOOP
        IF x IS NOT NULL AND (maxsofar IS NULL OR x > maxsofar) THEN
            maxsofar := x;
        END IF;
    END LOOP;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
Проверим:

## Page 11: Page 11 {#source-page-011}

```sql
=> SELECT maximum(12, 65, 47);
```
```text
 maximum
---------
      65
(1 row)
```
```sql
=> SELECT maximum(12.1, 65.3, 47.6);
```
```text
 maximum
---------
    65.3
(1 row)
```
```sql
=> SELECT maximum(12, 65.3, 15e2, 3.14);
```
```text
 maximum
---------
    1500
(1 row)
```
Вот теперь у нас получился практически полный аналог выражения greatest!

## Page 12: Массив или таблица? {#source-page-012}

![Source slide page 12: Массив или таблица?](images/page-012.png){#source-figure-012 caption="Source slide page 12: Массив или таблица?"}

```text
1
...
{A}
```
```text
2
...
{B,C,D}
```
```text
3
...
{A,C}
```
```text
1
1
```
```text
1
A
...
```
```text
2
2
```
```text
1
...
```
```text
2
B
...
```
```text
2
3
```
```text
2
...
```
```text
2
4
```
```text
3
C
...
```
```text
3
...
```
```text
3
1
```
```text
4
D
...
```
```text
3
3
```

### Комментарии преподавателя

Классический реляционный подход предполагает, что в таблице хранятся атомарные значения (первая нормальная форма). Язык SQL не имеет средств для «заглядывания внутрь» сложносоставных значений.

Поэтому обычный подход состоит в создании отдельной таблицы, связанной с основной отношением «многие ко многим».

Тем не менее, мы можем создать таблицу со столбцом типа массива. PostgreSQL имеет богатый набор функций для работы с массивами, а поиск элемента в массиве может быть ускорен специальными индексами (такие индексы рассматриваются в курсе DEV2).

Такой подход бывает удобен: получается компактное представление, не требующее соединений. В частности, массивы активно используются в системном каталоге PostgreSQL.

Какое решение выбрать? Зависит от того, какие ставятся задачи, какие требуются операции. Рассмотрим пример.

## Page 13: Массив или таблица? {#source-page-013}

Представим себе, что мы проектируем базу данных для ведения блога. В блоге есть сообщения, и нам хотелось бы сопоставлять им теги.
Традиционный подход состоит в том, что для тегов надо создать отдельную таблицу, например, так:
```sql
=> CREATE TABLE posts(
    post_id integer PRIMARY KEY,
    message text
);
```
```sql
CREATE TABLE
```
```sql
=> CREATE TABLE tags(
    tag_id integer PRIMARY KEY,
    name text
);
```
```sql
CREATE TABLE
```
Связываем сообщения и теги отношением многие ко многим через еще одну таблицу:
```sql
=> CREATE TABLE posts_tags(
    post_id integer REFERENCES posts(post_id),
    tag_id integer REFERENCES tags(tag_id)
);
```
```sql
CREATE TABLE
```
Наполним таблицы тестовыми данными:
```sql
=> INSERT INTO posts(post_id,message) VALUES
    (1, 'Перечитывал пейджер, много думал.'),
    (2, 'Это было уже весной и я отнес елку обратно.');
```
```sql
INSERT 0 2
```
```sql
=> INSERT INTO tags(tag_id,name) VALUES
    (1, 'былое и думы'), (2, 'технологии'), (3, 'семья');
```
```sql
INSERT 0 3
```
```sql
=> INSERT INTO posts_tags(post_id,tag_id) VALUES
    (1,1), (1,2), (2,1), (2,3);
```
```sql
INSERT 0 4
```
Теперь мы можем вывести сообщения и теги:
```sql
=> SELECT p.message, t.name
FROM posts p
     JOIN posts_tags pt ON pt.post_id = p.post_id
     JOIN tags t ON t.tag_id = pt.tag_id
ORDER BY p.post_id, t.name;
```
```text
                   message                   |     name
---------------------------------------------+--------------
 Перечитывал пейджер, много думал.           | былое и
думы
 Перечитывал пейджер, много думал.           |
технологии
 Это было уже весной и я отнес елку обратно. |
былое и думы
 Это было уже весной и я отнес елку обратно. |
семья
(4 rows)
```
Или чуть иначе — возможно удобнее получить массив тегов. Для этого используем агрегирующую функцию:
```sql
=> SELECT p.message, array_agg(t.name ORDER BY t.name) tags
FROM posts p
     JOIN posts_tags pt ON pt.post_id = p.post_id
     JOIN tags t ON t.tag_id = pt.tag_id
GROUP BY p.post_id
ORDER BY p.post_id;
```

## Page 14: Page 14 {#source-page-014}

```text
                   message                   |            tags
---------------------------------------------+-----------------------------
 Перечитывал пейджер, много думал.           | {"былое
и думы",технологии}
 Это было уже весной и я отнес елку обратно. |
{"былое и думы",семья}
(2 rows)
```
Можем найти все сообщения с определенным тегом:
```sql
=> SELECT p.message
FROM posts p
     JOIN posts_tags pt ON pt.post_id = p.post_id
     JOIN tags t ON t.tag_id = pt.tag_id
WHERE t.name = 'былое и думы'
ORDER BY p.post_id;
```
```text
                   message
---------------------------------------------
 Перечитывал пейджер, много думал.
 Это было уже весной и я отнес елку обратно.
(2 rows)
```
Может потребоваться найти все уникальные теги — это совсем просто:
```sql
=> SELECT t.name
FROM tags t
ORDER BY t.name;
```
```text
     name
--------------
 былое и думы
 семья
 технологии
(3 rows)
```
Теперь попробуем подойти к задаче по-другому. Пусть теги будут представлены текстовым массивом прямо внутри таблицы сообщений.
```sql
=> DROP TABLE posts_tags;
```
```sql
DROP TABLE
```
```sql
=> DROP TABLE tags;
```
```sql
DROP TABLE
```
```sql
=> ALTER TABLE posts ADD COLUMN tags text[];
```
```sql
ALTER TABLE
```
Теперь у нас нет идентификаторов тегов, но они нам не очень и нужны.
```sql
=> UPDATE posts SET tags = '{"былое и думы","технологии"}'
WHERE post_id = 1;
```
```sql
UPDATE 1
```
```sql
=> UPDATE posts SET tags = '{"былое и думы","семья"}'
WHERE post_id = 2;
```
```sql
UPDATE 1
```
Вывод всех сообщений упростился:
```sql
=> SELECT p.message, p.tags
FROM posts p
ORDER BY p.post_id;
```
```text
                   message                   |            tags
---------------------------------------------+-----------------------------
 Перечитывал пейджер, много думал.           | {"былое
и думы",технологии}
 Это было уже весной и я отнес елку обратно. |
{"былое и думы",семья}
(2 rows)
```
Сообщения с определенным тегом тоже легко найти (используем оператор пересечения &&).
Эта операция может быть ускорена с помощью индекса GIN, и для такого запроса не придется перебирать всю таблицу

## Page 15: Page 15 {#source-page-015}

сообщений.
```sql
=> SELECT p.message
FROM posts p
WHERE p.tags && '{"былое и думы"}'
ORDER BY p.post_id;
```
```text
                   message
---------------------------------------------
 Перечитывал пейджер, много думал.
 Это было уже весной и я отнес елку обратно.
(2 rows)
```
А вот получить список тегов довольно сложно. Это требует разворачивания всех массивов тегов в большую таблицу и поиск уникальных значений — тяжелая операция.
```sql
=> SELECT DISTINCT unnest(p.tags) AS name
FROM posts p;
```
```text
     name
--------------
 технологии
 былое и думы
 семья
(3 rows)
```
Тут хорошо видно, что имеет место дублирование данных.
Итак, оба подхода вполне могут применяться.
В простых случаях массивы выглядят проще и работают хорошо.
В более сложных сценариях (представьте, что вместе с именем тега мы хотим хранить дату его создания; или требуется проверка ограничений целостности) классический вариант становится более привлекательным.

## Page 16: Итоги {#source-page-016}

**Массив состоит из пронумерованных элементов одного и того же типа данных**
- Столбец с массивом как альтернатива отдельной таблице: удобные операции и индексная поддержка
- Позволяет создавать функции с переменным числом параметров

## Page 17: Практика {#source-page-017}

1. Создайте функцию add\_book для добавления новой книги.
  - Функция должна принимать два параметра —
  - название книги и массив идентификаторов авторов —
  - и возвращать идентификатор новой книги.
  - Проверьте, что в приложении появилась возможность
  - добавлять книги.
1.
```text
FUNCTION add_book(title text, authors integer[])
RETURNS integer
```

## Page 18: 1. Функция add_book {#source-page-018}

```sql
=> CREATE FUNCTION add_book(title text, authors integer[])
RETURNS integer
AS $$
DECLARE
    book_id integer;
    id integer;
    seq_num integer := 1;
BEGIN
    INSERT INTO books(title)
        VALUES(title)
        RETURNING books.book_id INTO book_id;
    FOREACH id IN ARRAY authors LOOP
        INSERT INTO authorship(book_id, author_id, seq_num)
            VALUES (book_id, id, seq_num);
        seq_num := seq_num + 1;
    END LOOP;
    RETURN book_id;
END
$$ VOLATILE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```

## Page 19: Практика+ {#source-page-019}

1. Реализуйте функцию map, принимающую два параметра: массив вещественных чисел и название вспомогательной функции, принимающей один параметр вещественного типа.
  - Функция должна возвращать исходный массив, в котором
  - к каждому элементу применена вспомогательная функция.
- 2. Реализуйте функцию reduce, принимающую два параметра: массив вещественных чисел и название вспомогательной функции, принимающей два параметра вещественного типа.
  - Функция должна возвращать вещественное число,
  - полученное последовательной сверткой массива слева
  - направо.
- 3. Сделайте функции map и reduce полиморфными.
1. Например:
```text
map(ARRAY[4.0,9.0],'sqrt') → ARRAY[2.0,3.0]
```
2. Например:
```text
reduce(ARRAY[1.0,3.0,2.0,0.5],'greatest') → 3.0
```
В этом случае значение вычисляется как
```text
greatest( greatest( greatest(1.0,3.0), 2.0 ), 0.5 )
```

## Page 20: 1. Функция map {#source-page-020}

```sql
=> CREATE DATABASE plpgsql_arrays;
```
```sql
CREATE DATABASE
```
```sql
=> \c plpgsql_arrays
```
```text
You are now connected to database "plpgsql_arrays" as user "student".
```
```sql
=> CREATE FUNCTION map(a INOUT float[], func text)
AS $$
DECLARE
    i integer;
    x float;
BEGIN
    IF cardinality(a) > 0 THEN
        FOR i IN array_lower(a,1)..array_upper(a,1) LOOP
            EXECUTE format('SELECT %I($1)',func) USING a[i] INTO x;
            a[i] := x;
        END LOOP;
    END IF;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
INTO a&#91;i&#93; не работает, поэтому нужна отдельная переменная.
```sql
=> SELECT map(ARRAY[4.0,9.0,16.0],'sqrt');
```
```text
   map
---------
 {2,3,4}
(1 row)
```
```sql
=> SELECT map(ARRAY[]::float[],'sqrt');
```
```text
 map
-----
 {}
(1 row)
```
Другой вариант реализации с циклом FOREACH:
```sql
=> CREATE OR REPLACE FUNCTION map(a float[], func text) RETURNS float[]
AS $$
DECLARE
    x float;
    b float[]; -- пустой массив
BEGIN
    FOREACH x IN ARRAY a LOOP
        EXECUTE format('SELECT %I($1)',func) USING x INTO x;
        b := b || x;
    END LOOP;
    RETURN b;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
```sql
=> SELECT map(ARRAY[4.0,9.0,16.0],'sqrt');
```
```text
   map
---------
 {2,3,4}
(1 row)
```
```sql
=> SELECT map(ARRAY[]::float[],'sqrt');
```
```text
 map
-----
(1 row)
```

## Page 21: 2. Функция reduce {#source-page-021}

```sql
=> CREATE FUNCTION reduce(a float[], func text) RETURNS float
AS $$
DECLARE
    i integer;
    r float := NULL;
BEGIN
    IF cardinality(a) > 0 THEN
        r := a[array_lower(a,1)];
        FOR i IN array_lower(a,1)+1 .. array_upper(a,1) LOOP
            EXECUTE format('SELECT %I($1,$2)',func) USING r, a[i]
                INTO r;
        END LOOP;
    END IF;
    RETURN r;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
Greatest (как и least) — не функция, а встроенное условное выражение, поэтому из-за экранирования не получится использовать ее напрямую:
```sql
=> SELECT reduce( ARRAY[1.0,3.0,2.0], 'greatest');
```
```sql
ERROR:  function greatest(double precision, double precision) does not exist
LINE 1: SELECT "greatest"($1,$2)
               ^
HINT:  No function matches the given name and argument types. You might need to add
explicit type casts.
QUERY:  SELECT "greatest"($1,$2)
CONTEXT:  PL/pgSQL function reduce(double precision[],text) line 9 at EXECUTE
```
Вместо нее используем реализованную в демонстрации функцию maximum.
```sql
=> CREATE FUNCTION maximum(VARIADIC a anycompatiblearray, maxsofar OUT anycompatible)
AS $$
DECLARE
    x maxsofar%TYPE;
BEGIN
    FOREACH x IN ARRAY a LOOP
        IF x IS NOT NULL AND (maxsofar IS NULL OR x > maxsofar) THEN
            maxsofar := x;
        END IF;
    END LOOP;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
```sql
=> SELECT reduce(ARRAY[1.0,3.0,2.0], 'maximum');
```
```text
 reduce
--------
      3
(1 row)
```
```sql
=> SELECT reduce(ARRAY[1.0], 'maximum');
```
```text
 reduce
--------
      1
(1 row)
```
```sql
=> SELECT reduce(ARRAY[]::float[], 'maximum');
```
```text
 reduce
--------
(1 row)
```
Вариант с циклом FOREACH:

## Page 22: Page 22 {#source-page-022}

```sql
=> CREATE OR REPLACE FUNCTION reduce(a float[], func text) RETURNS float
AS $$
DECLARE
    x float;
    r float;
```
```sql
    first boolean := true;
BEGIN
    FOREACH x IN ARRAY a LOOP
        IF first THEN
            r := x;
            first := false;
        ELSE
            EXECUTE format('SELECT %I($1,$2)',func) USING r, x INTO r;
        END IF;
    END LOOP;
    RETURN r;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
```sql
=> SELECT reduce(ARRAY[1.0,3.0,2.0], 'maximum');
```
```text
 reduce
--------
      3
(1 row)
```
```sql
=> SELECT reduce(ARRAY[1.0], 'maximum');
```
```text
 reduce
--------
      1
(1 row)
```
```sql
=> SELECT reduce(ARRAY[]::float[], 'maximum');
```
```text
 reduce
--------
(1 row)
```
### 3. Полиморфные варианты функций
Функция map.
```sql
=> DROP FUNCTION map(float[],text);
```
```sql
DROP FUNCTION
```
```sql
=> CREATE FUNCTION map(
    a anyarray,
    func text,
    elem anyelement DEFAULT NULL
)
RETURNS anyarray
AS $$
DECLARE
    x elem%TYPE;
    b a%TYPE;
BEGIN
    FOREACH x IN ARRAY a LOOP
        EXECUTE format('SELECT %I($1)',func) USING x INTO x;
        b := b || x;
    END LOOP;
    RETURN b;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
Требуется фиктивный параметр типа anyelement, чтобы внутри функции объявить переменную такого же типа.
```sql
=> SELECT map(ARRAY[4.0,9.0,16.0],'sqrt');
```

## Page 23: Page 23 {#source-page-023}

```text
                           map
---------------------------------------------------------
 {2.000000000000000,3.000000000000000,4.000000000000000}
(1 row)
```
```sql
=> SELECT map(ARRAY[]::float[],'sqrt');
```
```text
 map
-----
(1 row)
```
Пример вызова с другим типом данных:
```sql
=> SELECT map(ARRAY[' a ','  b','c  '],'btrim');
```
```text
   map
---------
 {a,b,c}
(1 row)
```
Функция reduce.
```sql
=> DROP FUNCTION reduce(float[],text);
```
```sql
DROP FUNCTION
```
```sql
=> CREATE FUNCTION reduce(
    a anyarray,
    func text,
    elem anyelement DEFAULT NULL
)
RETURNS anyelement
AS $$
DECLARE
    x elem%TYPE;
    r elem%TYPE;
    first boolean := true;
BEGIN
    FOREACH x IN ARRAY a LOOP
        IF first THEN
            r := x;
            first := false;
        ELSE
            EXECUTE format('SELECT %I($1,$2)',func) USING r, x INTO r;
        END IF;
    END LOOP;
    RETURN r;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
```sql
=> CREATE FUNCTION add(x anyelement, y anyelement) RETURNS anyelement
AS $$
BEGIN
    RETURN x + y;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
```sql
=> SELECT reduce(ARRAY[1,-2,4], 'add');
```
```text
 reduce
--------
      3
(1 row)
```
```sql
=> SELECT reduce(ARRAY['a','b','c'], 'concat');
```
```text
 reduce
--------
 abc
(1 row)
```
```sql
=> \c postgres
```

## Page 24: Page 24 {#source-page-024}

```text
You are now connected to database "postgres" as user "student".
```
```sql
=> DROP DATABASE plpgsql_arrays;
```
```sql
DROP DATABASE
```