PostgreSQL - лучший способ вернуть массив пар ключ-значение

Ответ 1

Я подозреваю, что, не имея больше знаний о вашем приложении, я не смогу довести вас до нужного результата. Но мы можем получить довольно далеко. Для начала существует функция ROW:

# SELECT 'foo', ROW(3, 'Bob');
 ?column? |   row   
----------+---------
 foo      | (3,Bob)
(1 row)

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

# CREATE TYPE person(id INTEGER, name VARCHAR);
CREATE TYPE
# SELECT now(), row(3, 'Bob')::person;
              now              |   row   
-------------------------------+---------
 2012-02-03 10:46:13.279512-07 | (3,Bob)
(1 row)

Кстати, всякий раз, когда вы создаете таблицу, PostgreSQL делает тип с тем же именем, поэтому, если у вас уже есть такая таблица, у вас также есть тип. Например:

# DROP TYPE person;
DROP TYPE

# CREATE TABLE people (id SERIAL, name VARCHAR);
NOTICE:  CREATE TABLE will create implicit sequence "people_id_seq" for serial column "people.id"
CREATE TABLE

# SELECT 'foo', row(3, 'Bob')::people;
 ?column? |   row   
----------+---------
 foo      | (3,Bob)
(1 row)

В третьем запросе я использовал people как тип.

Теперь это вряд ли будет такой же полезной, как вы думаете по двум причинам:

• Я не могу найти удобный синтаксис для вытаскивания данных из вложенной строки.

  Мне может быть что-то не хватает, но я просто не вижу, чтобы многие люди использовали этот синтаксис. Единственный пример, который я вижу в документации, - это функция, принимающая значение строки в качестве аргумента и выполнение чего-то с ней. Я не вижу примера вытягивания строки из ячейки и опроса ее частей. Похоже, вы можете упаковать данные таким образом, но после этого трудно деконструировать. Вам придется сделать много хранимых процедур.

• Ваш языковой драйвер PostgreSQL может не обрабатывать данные с строкой, вложенные в строку.

  Я не могу говорить для NPGSQL, но поскольку это очень специфичная для PostgreSQL функция, вы не найдете ее в библиотеках, которые поддерживают другие базы данных. Например, Hibernate не сможет обрабатывать выборку объекта, сохраненного как значение ячейки в строке. Я даже не уверен, что JDBC сможет с пользой дать Hibernate информацию, поэтому проблема может быть довольно глубокой.

Итак, то, что вы здесь делаете, возможно, если вы можете жить без большого количества тонкостей. Я бы рекомендовал не преследовать его, потому что это будет тяжелая битва, если я не ошибаюсь.

Ответ 2

ARRAY могут содержать только элементы одного типа

В вашем примере отображается значение text и integer (без кавычек вокруг 1). Как правило, невозможно смешивать типы в массиве. Чтобы получить эти значения в массив, вы должны создать composite type, а затем сформировать массив этого составного типа, как вы уже упоминали.

Альтернативно вы можете использовать типы данных json в Postgres 9.2+, jsonb в Postgres 9.4 + или hstore для пар ключ-значение.

Конечно, вы можете отбрасывать integer до text и работать с двумерным текстовым массивом. Рассмотрим два варианта синтаксиса для ввода массива в приведенной ниже демонстрационной версии и обратитесь к руководству по вводу массива.

Существует ограничение для преодоления. Если вы попытаетесь агрегировать ARRAY (построить из ключа и значения) в двумерный массив, ошибка сборки агрегата array_agg() или конструктора ARRAY:

ERROR:  could not find array type for data type text[]

Есть способы обойти это.

Объединение пар ключ-значение в двумерный массив

PostgreSQL 9.1 с standard_conforming_strings= on:

CREATE TEMP TABLE tbl(
 id     int
,txt    text
,txtarr text[]
);

Столбец txtarr предназначен только для демонстрации вариантов синтаксиса в команде INSERT. Третий ряд содержит метасимволы:

INSERT INTO tbl VALUES
 (1, 'foo', '{{1,foo1},{2,bar1},{3,baz1}}')
,(2, 'bar', ARRAY[['1','foo2'],['2','bar2'],['3','baz2']])
,(3, '}b",a{r''', '{{1,foo3},{2,bar3},{3,baz3}}'); -- txt has meta-characters

SELECT * FROM tbl;

Простой случай: агрегировать два целых числа (я использую их дважды) в двумерный массив int:

Обновление: лучше с пользовательской функцией агрегации

С помощью полиморфного типа anyarray он работает для всех базовых типов:

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

Вызов:

SELECT array_agg_mult(ARRAY[ARRAY[id,id]]) AS x        -- for int
      ,array_agg_mult(ARRAY[ARRAY[id::text,txt]]) AS y -- or text
FROM   tbl;

Обратите внимание на дополнительный слой ARRAY[], чтобы сделать его многомерным.

Обновление для Postgres 9.5 +

Postgres теперь отправляет вариант ввода принимающего массива array_agg(), и вы можете заменить мою пользовательскую функцию сверху:

Руководство:

array_agg(expression)
...
входные массивы, объединенные в массив одного более высокий размер (все входы должны иметь одинаковую размерность и не могут пусто или NULL)