Массивы
QHB позволяет определять столбцы таблицы как многомерные массивы переменной длины. Можно создать массивы любого встроенного или определенного пользователем базового типа, типа перечисления, составного типа, типа диапазона или домена.
Объявление типов массивов
Чтобы проиллюстрировать использование типов массивов, создадим эту таблицу:
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 нужно только для документирования; на логику работы с массивом
это не влияет.
Для одномерных массивов можно использовать альтернативный синтаксис с ключевым словом ARRAY, который соответствует стандарту SQL. Столбец pay_by_quarter можно было бы определить так:
pay_by_quarter integer ARRAY[4],
Или, если не указан размер массива:
pay_by_quarter integer ARRAY,
Однако, как и в предыдущем случае, QHB не налагает ограничения на фактический размер массива.
Ввод значений массива
Чтобы записать значение массива в виде литеральной константы, заключите значения элементов в фигурные скобки и разделите их запятыми. (Если вы знаете C, это очень похоже на синтаксис для инициализации структур в C.) Вы можете заключить в кавычки значение любого элемента, а если он содержит запятые или фигурные скобки, то должны сделать это обязательно. (Подробнее см. ниже.) Таким образом, общий формат константы массива выглядит следующим образом:
'{ значение1 разделитель значение2 разделитель ... }'
где разделитель — это символ, указанный в качестве разделителя для данного типа в его записи в таблице pg_type. Все стандартные типы данных, представленные в дистрибутиве QHB, используют запятую (,), за исключением типа box, в котором используется точка с запятой (;). Каждое значение является либо константой типа элемента массива, либо вложенным массивом. Пример константы массива:
'{{1,2,3},{4,5,6},{7,8,9}}'
Эта константа представляет собой двумерный массив размером 3x3, состоящий из трех подмножеств целых чисел.
Чтобы присвоить элементу константы массива значение NULL, нужно написать NULL для его значения. (При этом регистр символов не имеет значения.) Если же нужно добавить в массив строку со словом «NULL», нужно заключить его в кавычки.
(Эти виды констант массива на самом деле являются лишь частным случаем констант универсального типа, рассматриваемых в подразделе Константы других типов. Константа первоначально обрабатывается как строка и передается процедуре преобразования входных данных массива. Может потребоваться явное указание целевого типа).
Теперь мы можем показать некоторые операторы 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 более подробно рассматривается в подразделе Конструкторы массивов.
Обращение к массивам
Теперь можно выполнить несколько запросов к таблице. Сначала мы покажем, как обратиться к одному элементу массива. Этот запрос выводит имена сотрудников, зарплата которых изменилась во втором квартале:
SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
name
-------
Carol
(1 row)
Номера индексов массива пишутся в квадратных скобках. По умолчанию в QHB действует соглашение о нумерации массивов, начиная с единицы, то есть массив из n элементов начинается с array[1] и заканчивается array[n].
Этот запрос выводит зарплату всех сотрудников за третий квартал:
SELECT pay_by_quarter[3] FROM sal_emp;
pay_by_quarter
----------------
10000
25000
(2 rows)
Также возможно получить доступ к произвольным прямоугольным срезам массива, или подмассивам. Срез массива обозначается записью нижняя-граница:верхняя- граница для одной или нескольких размерностей. Например, этот запрос выводит первые пункты в расписании Билла за первые два дня недели:
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].
Значения нижняя-граница и/или верхняя-граница в указании среза можно опустить; отсутствующая граница заменяется нижним или верхним пределом индексов массива. Например:
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, а пустой массив (с размерностью 0). (Это отличается от обращений к элементам и выполняется по историческим причинам). Если запрашиваемый срез частично выходит за границы массива, то возвращается не 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_lower(schedule, 1), array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
array_lower array_upper
--------------------------
1 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)
Изменение массивов
Значение массива можно полностью заменить следующим образом:
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';
При этом в указании среза можно опустить значение нижней-границы и/или верхней-границы, но только при изменении значения массива, отличного от NULL и не имеющего нулевой размерности (в противном случае не будет граничных значений индекса, которые должны подставлять вместо опущенных).
Сохраненный массив можно расширять, присваивая значения элементам, которых в нем еще нет. Все позиции между уже существующими и новыми элементами будут заполнены значениями NULL. Например, если массив myarray содержит 4 элемента, после присвоения значение элементу myarray[6] в нем будет 6 элементов, а myarray[5] будет содержать NULL. В настоящее время подобное расширение допускается только для одномерных, но не многомерных массивов.
Определение элементов по индексам позволяет создавать массивы, в которых
нумерация элементов начинается не с 1. Например, можно записать выражение
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}'; -- нетипизированный литерал воспринимается как массив
?column?
-----------
{1,2,3,4}
SELECT ARRAY[1, 2] || '7'; -- как и этот
ERROR: malformed array literal: "7"
SELECT ARRAY[1, 2] || NULL; -- как и буквальный NULL
?column?
----------
{1,2}
(1 row)
SELECT array_append(ARRAY[1, 2], NULL); -- возможно, это и имелось в виду
array_append
--------------
{1,2,NULL}
В приведенных выше примерах анализатор видит целочисленный массив на одной стороне оператора конкатенации и константу неопределенного типа на другой. Эвристический алгоритм, который он использует для разрешения типа константы, должен полагать, что она имеет тот же тип, что и другие входные данные оператора — в данном случае целочисленный массив. Поэтому предполагается, что оператор конкатенации представляет array_cat, а не array_append. Если это неправильный выбор, его можно исправить, приведя константу к типу элемента массива; но, возможно, лучше будет явно использовать array_append.
Поиск в массивах
Для поиска значения в массиве необходимо проверить каждое значение. Это можно сделать вручную, если вы знаете размер массива. Например:
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;
Однако с большими массивами этот метод быстро становится утомительным, а если размер массива неизвестен, то и бесполезным. Альтернативный метод описан в разделе Сравнение строк и массивов. Приведенный выше запрос можно заменить на:
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;
Эта функция описана в таблице Функции, генерирующие индексы массивов.
Также поиск в массиве можно выполнить с помощью оператора &&, который
проверяет, перекрывается ли левый операнд с правым. Например:
SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];
Этот и другие операторы для работы с массивами более подробно описаны в разделе Функции и операторы для массивов. Его можно ускорить с помощью соответствующего индекса, как описано в разделе Типы индексов.
Также в массиве можно искать конкретные значения, используя функции array_position и array_positions. Первая возвращает индекс первого вхождения значения в массив, вторая возвращает массив с индексами всех его вхождений. Например:
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
array_positions
-----------------
2
SELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);
array_positions
-----------------
{1,4,8}
Совет
Массивы не являются множествами; необходимость поиска определенных элементов массива может быть признаком неправильного проектирования базы данных. Рассмотрите возможность использования отдельной таблицы со строками для каждого элемента массива. Это облегчит поиск и, скорее всего, позволит лучше оперировать большим количеством элементов.
Синтаксис вводимых и выводимых значений массива
Внешнее текстовое представление значения массива состоит из элементов, которые
интерпретируются в соответствии с правилами преобразования ввода/вывода для
типа элемента массива, а также элементов оформления, которые указывают на
структуру массива. Оформление состоит из фигурных скобок ({ и }) вокруг
значения массива и символов-разделителей между соседними элементами. Символом-
разделителем обычно служит запятая (,), но это может быть и другой знак: он
определяется настройкой параметра typdelim для типа элемента массива. Для
стандартных типов данных, представленных в дистрибутиве QHB,
в качестве разделителя используется запятая, за исключением типа box, в
котором используется точка с запятой (;). В многомерном массиве у каждой
размерности (строка, плоскость, куб и т. д.) есть свой уровень фигурных скобок,
а между соседними объектами в фигурных скобках на одном уровне должны стоять
разделители.
Функция вывода массива помещает значения элементов в кавычки, если те являются пустыми строками, содержат фигурные скобки, символы-разделители, кавычки, обратный слэш, пробел или это слово NULL. Кавычки и обратные слэши, включенные в значения элементов, экранируются обратным слэшем. Можно с уверенностью предположить, что числовые типы данных никогда не будут выводиться в кавычках, но следует быть готовыми к тому, что в текстовых типах они могут появиться.
По умолчанию значение индекса нижней границы размерностей массива равно 1. Для
представления массивов с другими нижними границами можно явно указать диапазоны
индексов перед содержимым массива. Это оформление включает в себя квадратные
скобки ([]) вокруг нижней и верхней границ каждой размерности с двоеточием
(:) в качестве разделителя между ними. За этим выражением следует знак
равенства (=). Например:
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)
Функция вывода массива будет включать в результат явное указание размерностей только в том случае, если нижняя граница одной или нескольких размерностей отличается от 1.
Если в качестве значения элемента записывается NULL (в любом варианте), этот элемент считается равным NULL. Если же запись включает кавычки или обратный слэш, это позволяет вводить буквальное строковое значение литерала «NULL». Кроме того, при желании можно выключить параметр конфигурации array_nulls (установив значение off), и тогда строки NULL не будут восприниматься как значения NULL.
Как было показано ранее, при записи значения массива любой его отдельный элемент можно заключить в кавычки. Это нужно делать в том случае, если значение элемента без кавычек может запутать синтаксический анализатор значений массива. Например, в кавычки нужно заключать элементы, содержащие фигурные скобки, запятые (или символ-разделитель, определенный для этого типа данных), кавычки, обратные слэши или пробелы в начале или конце строки. Пустые строки и строки, соответствующие слову NULL, также должны быть заключены в кавычки. Чтобы поместить в значение элемента массива, заключенного в кавычки, кавычку или обратный слэш, поставьте перед этим символом обратный слэш. Или же можно обойтись без кавычек и использовать обратный слэш для экранирования всех символов в данных, которые в противном случае могут быть восприняты как синтаксис массива.
Перед открывающей или после закрывающей скобки можно добавить пробел. Также можно добавить пробел до или после любой отдельной строки элемента. Во всех этих случаях пробелы будут игнорироваться. Однако пробелы в элементах, заключенных в кавычки, или окруженные с обеих сторон отличными от пробелов символами, учитываются.
Совет
При записи значений массива в командах SQL зачастую удобнее работать с синтаксисом конструктора ARRAY (см. раздел Конструкторы массивов), чем с синтаксисом литерала массива. В ARRAY значения отдельных элементов записываются так же, как если бы они не были членами массива.