8.15. Массивы#
8.15. Массивы
Tantor SE-1C позволяет определять столбцы таблицы как многомерные массивы переменной длины. Можно создавать массивы любого встроенного или определенного пользователем базового типа, типа перечисления, составного типа, типа диапазона или домена.
8.15.1. Объявление типов массивов
Для иллюстрации использования массивов создадим следующую таблицу:
CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[],
schedule text[][]
);
Как показано, тип данных массива называется путем добавления к имени типа данных элементов массива квадратных скобок ([]). Вышеуказанная команда создаст таблицу с именем sal_emp с колонкой типа text (name), одномерным массивом типа integer (pay_by_quarter), который представляет зарплату сотрудника по кварталам, и двумерным массивом типа text (schedule), который представляет недельное расписание сотрудника.
Синтаксис для CREATE TABLE позволяет указать точный размер массивов, например:
CREATE TABLE tictactoe (
squares integer[3][3]
);
Однако, текущая реализация игнорирует любые указанные ограничения размера массива, то есть поведение такое же, как для массивов неопределенной длины.
Текущая реализация не обеспечивает соблюдение заявленного количества измерений. Массивы определенного типа элементов считаются одним и тем же типом, независимо от размера или количества измерений. Таким образом, объявление размера массива или количества измерений в CREATE TABLE является просто документацией; это не влияет на поведение во время выполнения.
Альтернативный синтаксис, соответствующий стандарту SQL и использующий ключевое слово ARRAY, может быть использован для одномерных массивов. pay_by_quarter мог быть определен так:
pay_by_quarter integer ARRAY[4],
Или, если не указан размер массива:
pay_by_quarter integer ARRAY,
Как и раньше, однако Tantor SE-1C не накладывает ограничение на размер в любом случае.
8.15.2. Ввод значений массива
Для записи значения массива в виде литеральной константы заключите значения элементов в фигурные скобки и разделите их запятыми. (Если вы знакомы с языком C, это похоже на синтаксис инициализации структур в C). Вы можете заключить в двойные кавычки любое значение элемента и должны сделать это, если оно содержит запятые или фигурные скобки. (Подробности приведены ниже). Таким образом, общий формат литеральной константы массива следующий:
'{ val1 delim val2 delim ... }'
где delim - это символ-разделитель для типа, записанного в его записи pg_type. Среди стандартных типов данных, предоставляемых в дистрибутиве Tantor SE-1C, все они используют запятую (,), за исключением типа box, который использует точку с запятой (;). Каждое val - это либо константа типа элемента массива, либо подмассив. Пример константы массива:
'{{1,2,3},{4,5,6},{7,8,9}}'
Эта константа представляет собой двумерный массив размером 3 на 3, состоящий из трех подмассивов целых чисел.
Чтобы установить элемент массива в NULL, напишите NULL в качестве значения элемента. (Любая версия NULL в верхнем или нижнем регистре подойдет). Если вы хотите использовать фактическое строковое значение “NULL”, вы должны заключить его в двойные кавычки.
(Эти виды констант массивов на самом деле являются только особым случаем общих констант типов, описанных в Раздел 4.1.2.7. Константа изначально рассматривается как строка и передается в рутину преобразования ввода массива. Может потребоваться явное указание типа).
Теперь мы можем показать некоторые операторы INSERT:
INSERT INTO sal_emp
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"training", "presentation"}}');
INSERT INTO sal_emp
VALUES ('Carol',
'{20000, 25000, 25000, 25000}',
'{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
Результат предыдущих двух вставок выглядит следующим образом:
SELECT * FROM sal_emp;
name | pay_by_quarter | schedule
-------+---------------------------+-------------------------------------------
Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)
Многомерные массивы должны иметь совпадающие размеры для каждого измерения. Несоответствие вызывает ошибку, например:
INSERT INTO sal_emp
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"meeting"}}');
ERROR: multidimensional arrays must have array expressions with matching dimensions
Синтаксис конструктора ARRAY также может быть использован:
INSERT INTO sal_emp
VALUES ('Bill',
ARRAY[10000, 10000, 10000, 10000],
ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);
INSERT INTO sal_emp
VALUES ('Carol',
ARRAY[20000, 25000, 25000, 25000],
ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);
Обратите внимание, что элементы массива являются обычными SQL-константами или выражениями; например, строковые литералы заключаются в одинарные кавычки, вместо двойных кавычек, как это было бы в литерале массива. Синтаксис конструктора ARRAY подробно рассматривается в разделе Раздел 4.2.12.
8.15.3. Доступ к массивам
Теперь мы можем выполнить некоторые запросы к таблице. Сначала покажем, как получить доступ к отдельному элементу массива. Этот запрос извлекает имена сотрудников, у которых изменилась зарплата во втором квартале:
SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2]; name ------- Carol (1 row)
Номера индексов массива записываются в квадратных скобках.
По умолчанию Tantor SE-1C использует
схему нумерации массивов, начинающуюся с единицы, то есть
массив из n элементов начинается с array[1] и
заканчивается с array[.
n]
Этот запрос извлекает зарплату всех сотрудников за третий квартал:
SELECT pay_by_quarter[3] FROM sal_emp;
pay_by_quarter
----------------
10000
25000
(2 rows)
Мы также можем получить доступ к произвольным прямоугольным срезам массива или подмассивам. Срез массива обозначается записью lower-bound:upper-bound
SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
schedule
------------------------
{{meeting},{training}}
(1 row)
Если любое измерение записано в виде среза, т.е. содержит двоеточие, то все измерения рассматриваются как срезы. Любое измерение, которое содержит только одно число (без двоеточия), рассматривается как диапазон от 1 до указанного числа. Например, [2] рассматривается как [1:2], как в этом примере:
SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
schedule
-------------------------------------------
{{meeting,lunch},{training,presentation}}
(1 row)
Чтобы избежать путаницы с непрерывным случаем, лучше использовать синтаксис среза для всех измерений, например, [1:2][1:1], а не [2][1:1].
Возможно опустить lower-bound и/или
upper-bound спецификатора среза; пропущенная
граница заменяется нижним или верхним пределом подписей массива.
Например:
SELECT schedule[:2][2:] FROM sal_emp WHERE name = 'Bill';
schedule
------------------------
{{lunch},{presentation}}
(1 row)
SELECT schedule[:][1:1] FROM sal_emp WHERE name = 'Bill';
schedule
------------------------
{{meeting},{training}}
(1 row)
Выражение с подскриптом массива вернет null, если сам массив или любое из выражений подскрипта равно null. Также null возвращается, если подскрипт находится за пределами границ массива (в этом случае ошибка не возникает). Например, если schedule в настоящее время имеет размеры [1:3][1:2], то ссылка на schedule[3][3] дает NULL. Аналогично, ссылка на массив с неправильным количеством подскриптов дает null, а не ошибку.
Выражение среза массива также возвращает null, если сам массив или любое из выражений индекса является null. Однако в других случаях, например, при выборе среза массива, который полностью находится за пределами текущих границ массива, выражение среза возвращает пустой (нульмерный) массив вместо null. (Это не соответствует поведению без среза и делается по историческим причинам). Если запрошенный срез частично перекрывается с границами массива, то он молча сокращается только до перекрывающейся области вместо возврата null.
Текущие размеры любого массивного значения можно получить с помощью функции array_dims:
SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol'; array_dims ------------ [1:2][1:2] (1 row)
array_dims возвращает результат типа text,
что удобно для чтения людьми, но может быть неудобно для программ.
Размерности также могут быть получены с помощью функций
array_upper и array_lower,
которые возвращают верхнюю и нижнюю границу
указанной размерности массива соответственно:
SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
array_upper
-------------
2
(1 row)
array_length вернет длину указанного измерения массива:
SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';
array_length
--------------
2
(1 row)
cardinality возвращает общее количество элементов в массиве по всем измерениям. Это фактически количество строк, которые вернул бы вызов unnest:
SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';
cardinality
-------------
4
(1 row)
8.15.4. Изменение массивов
Значение массива может быть полностью заменено:
UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Carol';
Или используя синтаксис выражения ARRAY:
UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Carol';
Массив также может быть обновлен в одном элементе:
UPDATE sal_emp SET pay_by_quarter[4] = 15000
WHERE name = 'Bill';
или обновлено в срезе:
UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
WHERE name = 'Carol';
Синтаксис среза с опущенными lower-bound и/или
upper-bound также может быть использован, но только при
обновлении значения массива, которое не является NULL или нулевой размерности (в противном случае
нет существующего ограничения подписки для замены).
Значение хранимого массива можно увеличить, присваивая значения элементам, которых еще нет. Любые позиции между ранее существующими элементами и новыми присвоенными элементами будут заполнены значениями null. Например, если массив myarray в настоящее время имеет 4 элемента, после обновления, которое присваивает значение myarray[6], массив будет содержать шесть элементов; myarray[5] будет содержать null.
В настоящее время увеличение массива таким образом разрешено только для одномерных массивов, а не для многомерных массивов.
Подстрочное присваивание позволяет создавать массивы, которые не используют однозначные
индексы. Например, можно присвоить значение myarray[-2:7], чтобы
создать массив с индексными значениями от -2 до 7.
Новые значения массива также могут быть созданы с использованием оператора конкатенации,
||:
SELECT ARRAY[1,2] || ARRAY[3,4];
?column?
-----------
{1,2,3,4}
(1 row)
SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
?column?
---------------------
{{5,6},{1,2},{3,4}}
(1 row)
Оператор конкатенации позволяет добавить один элемент в начало или конец одномерного массива. Он также принимает два N-мерных массива или N-мерный и N+1-мерный массивы.
Когда один элемент добавляется в начало или конец одномерного массива, результатом будет массив с тем же нижним индексом подсчета, что и у операнда массива. Например:
SELECT array_dims(1 || '[0:1]={2,3}'::int[]);
array_dims
------------
[0:2]
(1 row)
SELECT array_dims(ARRAY[1,2] || 3);
array_dims
------------
[1:3]
(1 row)
Когда два массива с одинаковым количеством измерений объединяются, результат сохраняет нижнюю границу подскрипта внешнего измерения левого операнда. Результатом является массив, содержащий каждый элемент левого операнда, за которым следует каждый элемент правого операнда. Например:
SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]); array_dims ------------ [1:5] (1 row) SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]); array_dims ------------ [1:5][1:2] (1 row)
Когда N-мерный массив добавляется в начало или конец N+1-мерного массива, результат аналогичен случаю с элементом-массивом выше. Каждый N-мерный подмассив фактически является элементом внешнего измерения N+1-мерного массива. Например:
SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]); array_dims ------------ [1:3][1:2] (1 row)
Массив также может быть создан с использованием функций
array_prepend, array_append,
или array_cat. Первые две функции поддерживают только одномерные
массивы, но array_cat поддерживает многомерные массивы.
Некоторые примеры:
SELECT array_prepend(1, ARRAY[2,3]);
array_prepend
---------------
{1,2,3}
(1 row)
SELECT array_append(ARRAY[1,2], 3);
array_append
--------------
{1,2,3}
(1 row)
SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
array_cat
-----------
{1,2,3,4}
(1 row)
SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
array_cat
---------------------
{{1,2},{3,4},{5,6}}
(1 row)
SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
array_cat
---------------------
{{5,6},{1,2},{3,4}}
В простых случаях предпочтительнее использовать оператор конкатенации, о котором было рассказано выше, вместо прямого использования этих функций. Однако, поскольку оператор конкатенации перегружен для обслуживания всех трех случаев, существуют ситуации, когда использование одной из функций помогает избежать неоднозначности. Например, рассмотрим:
SELECT ARRAY[1, 2] || '{3, 4}'; -- the untyped literal is taken as an array
?column?
-----------
{1,2,3,4}
SELECT ARRAY[1, 2] || '7'; -- so is this one
ERROR: malformed array literal: "7"
SELECT ARRAY[1, 2] || NULL; -- so is an undecorated NULL
?column?
----------
{1,2}
(1 row)
SELECT array_append(ARRAY[1, 2], NULL); -- this might have been meant
array_append
--------------
{1,2,NULL}
В приведенных выше примерах парсер видит целочисленный массив с одной стороны оператора конкатенации и константу неопределенного типа с другой стороны. Эвристиеский алгоритм, который он использует для определения типа константы, заключается в предположении, что она имеет тот же тип, что и другой вход оператора - в данном случае целочисленный массив. Таким образом, предполагается, что оператор конкатенации представляет функцию array_cat, а не array_append. Если это неправильный выбор, его можно исправить, приведя константу к типу элемента массива; однако явное использование функции array_append может быть предпочтительным решением.
8.15.5. Поиск в массивах
Для поиска значения в массиве необходимо проверить каждое значение. Это можно сделать вручную, если вы знаете размер массива. Например:
SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
pay_by_quarter[2] = 10000 OR
pay_by_quarter[3] = 10000 OR
pay_by_quarter[4] = 10000;
Однако, это быстро становится утомительным для больших массивов и не является полезным, если размер массива неизвестен. Альтернативный метод описан в Раздел 9.24. Вышеуказанный запрос может быть заменен на:
SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
Кроме того, вы можете найти строки, в которых массив содержит все значения, равные 10000, с помощью:
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
В качестве альтернативы, функция generate_subscripts может быть использована.
Например:
SELECT * FROM
(SELECT pay_by_quarter,
generate_subscripts(pay_by_quarter, 1) AS s
FROM sal_emp) AS foo
WHERE pay_by_quarter[s] = 10000;
Эта функция описана в Таблица 9.65.
Вы также можете искать массив, используя оператор &&, который проверяет, пересекаются ли левый операнд с правым операндом. Например:
SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];
Этот и другие операторы массивов подробно описаны в разделе Раздел 9.19. Он может быть ускорен с помощью соответствующего индекса, как описано в разделе Раздел 11.2.
Вы также можете искать конкретные значения в массиве с помощью функций array_position и array_positions. Первая функция возвращает индекс первого вхождения значения в массиве, а вторая функция возвращает массив с индексами всех вхождений значения в массиве. Например:
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
array_position
----------------
2
(1 row)
SELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);
array_positions
-----------------
{1,4,8}
(1 row)
Подсказка
Массивы не являются множествами; поиск конкретных элементов массива может быть признаком неправильного проектирования базы данных. Рассмотрите возможность использования отдельной таблицы с записью для каждого элемента, который должен быть элементом массива. Это будет проще для поиска и, вероятно, масштабируется лучше для большого количества элементов.
8.15.6. Синтаксис ввода и вывода массивов
Внешнее текстовое представление значения массива состоит из элементов, которые интерпретируются в соответствии с правилами преобразования ввода-вывода для типа элемента массива, а также декорации, которая указывает структуру массива. Декорация состоит из фигурных скобок ({ и }) вокруг значения массива плюс разделительных символов между смежными элементами. Символ-разделитель обычно является запятой (,), но может быть и другим: он определяется настройкой typdelim для типа элемента массива. Среди стандартных типов данных, предоставляемых в дистрибутиве Tantor SE-1C, все используют запятую, за исключением типа box, который использует точку с запятой (;). В многомерном массиве каждое измерение (строка, плоскость, куб и т. д.) имеет свой уровень фигурных скобок, и между смежными сущностями с одинаковым уровнем должны быть записаны разделители.
Рутинный вывод массива будет заключать значения элементов в двойные кавычки, если они являются пустыми строками, содержат фигурные скобки, разделительные символы, двойные кавычки, обратные слеши или пробелы, или совпадают со словом NULL. Двойные кавычки и обратные слеши, встроенные в значения элементов, будут экранированы обратным слешем. Для числовых типов данных можно считать, что двойные кавычки никогда не появятся, но для текстовых типов данных следует быть готовым к наличию или отсутствию кавычек.
По умолчанию, нижнее значение индекса размерности массива установлено равным одному. Чтобы представить массивы с другими нижними границами, диапазоны индексов массива могут быть явно указаны перед записью содержимого массива. Эта декорация состоит из квадратных скобок ([]) вокруг нижней и верхней границ каждой размерности массива, с символом-разделителем двоеточием (:) между ними. Декорация размерности массива следует за знаком равенства (=). Например:
SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;
e1 | e2
----+----
1 | 6
(1 row)
Рутина вывода массива будет включать явные размеры в своем результате только в том случае, если есть один или более нижних границ, отличных от единицы.
Если значение, записанное для элемента, равно NULL (в любом регистре), элемент считается NULL. Наличие кавычек или обратных слешей отключает это и позволяет вводить литеральное строковое значение “NULL”. Кроме того, для обратной совместимости с предыдущими версиями PostgreSQL до 8.2, параметр конфигурации array_nulls может быть отключен (off), чтобы подавить распознавание NULL как NULL.
Как показано ранее, при записи значения массива вы можете использовать двойные кавычки вокруг каждого отдельного элемента массива. Вы должны сделать это, если значение элемента в противном случае может запутать парсер значения массива. Например, элементы, содержащие фигурные скобки, запятые (или символ разделителя типа данных), двойные кавычки, обратные слеши или ведущие или завершающие пробелы, должны быть заключены в двойные кавычки. Пустые строки и строки, совпадающие с словом NULL, также должны быть заключены в кавычки. Чтобы вставить двойные кавычки или обратный слеш в значение элемента массива в кавычках, предварите его обратным слешем. В качестве альтернативы, вы можете избежать кавычек и использовать экранирование обратным слешем, чтобы защитить все символы данных, которые в противном случае будут восприняты как синтаксис массива.
Вы можете добавить пробел перед открывающей фигурной скобкой или после закрывающей фигурной скобки. Вы также можете добавить пробел перед или после каждой отдельной строковой части. Во всех этих случаях пробелы будут проигнорированы. Однако пробелы внутри элементов, заключенных в двойные кавычки, или окруженные с обеих сторон непробельными символами элемента, не игнорируются.
Подсказка
Синтаксис конструктора ARRAY (см. Раздел 4.2.12) часто более удобен для работы, чем синтаксис литерала массива, при написании значений массива в SQL-командах. В ARRAY отдельные значения элементов записываются так же, как если бы они не были членами массива.