PostgreSQL - лучший способ вернуть массив пар ключ-значение
Я пытаюсь выбрать количество полей, одно из которых должно быть массивом с каждым элементом массива, содержащим два значения. Каждый элемент массива должен содержать имя (изменяющийся символ) и идентификатор (числовой). Я знаю, как вернуть массив отдельных значений (используя ARRAY ключевое слово), но я не уверен, как вернуть массив объекта, который сам по себе содержит два значения.
Запрос что-то вроде
SELECT
t.field1,
t.field2,
ARRAY(--with each element containing two values i.e. {'TheName', 1 })
FROM MyTable t
Я прочитал, что один из способов сделать это, выбрав значения в типе, а затем создать массив этого типа. Проблема в том, что остальная часть функции уже возвращает тип (что означает, что я бы тогда имел вложенные типы - это нормально? Если да, то как бы вы прочитали эти данные обратно в код приложения - то есть с поставщиком данных.Net, таким как NPGSQL??)
Буду признателен за любую оказанную помощь.
3 ответа
Я подозреваю, что, не зная вашего приложения, я не смогу получить вас полностью до нужного вам результата. Но мы можем получить довольно далеко. Для начала, есть 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 информацию, поэтому проблема может быть достаточно глубокой.
Таким образом, то, что вы делаете здесь, выполнимо при условии, что вы можете жить без множества тонкостей. Я бы порекомендовал не преследовать его, потому что это будет тяжелая битва весь путь, если я действительно не дезинформирован.
Массивы могут содержать только элементы одного типа
Ваш пример отображает 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[]
Однако есть способы обойти это.
Объединить пары ключ-значение в 2-мерный массив
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)
Простой способ без hstore
SELECT
jsonb_agg(to_jsonb (t))
FROM (
SELECT
unnest(ARRAY ['foo', 'bar', 'baz']) AS table_name
) t
>>> [{"table_name": "foo"}, {"table_name": "bar"}, {"table_name": "baz"}]