Выбор данных в массив Postgres

Question

У меня есть следующие данные:

name          id             url

John          1              someurl.com
Matt          2              cool.com
Sam           3              stackru.com

Как я могу написать оператор SQL в Postgres, чтобы выделить эти данные в многомерный массив, то есть:

{{John, 1, someurl.com}, {Matt, 2, cool.com}, {Sam, 3, stackru.com}}

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

Предполагая, что здесь все столбцы имеют тип text,

Answer 1

Вы не можете использовать array_agg() для создания многомерных массивов, по крайней мере, до PostgreSQL 9.4.
(Но грядущий Postgres 9.5 выпускает новый вариантarray_agg() это может!)

Из запроса @Matt Ball вы получаете массив записей (the_table[]).

Массив может содержать только элементы одного базового типа. У вас очевидно есть числовые и строковые типы. Конвертировать все столбцы (которых еще нет) в text чтобы это работало.

Вы можете создать агрегатную функцию для этого, как я продемонстрировал вам здесь ранее.

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

Вызов:

SELECT array_agg_mult(ARRAY[ARRAY[name, id::text, url]]) AS tbl_mult_arr
FROM   tbl;

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

Мгновенная демонстрация:

WITH tbl(id, txt) AS (
    VALUES
      (1::int, 'foo'::text)
     ,(2,      'bar')
     ,(3,      '}b",') -- txt has meta-characters
    )
    , x AS (
    SELECT array_agg_mult(ARRAY[ARRAY[id::text,txt]]) AS t
    FROM   tbl
    )
SELECT *, t[1][3] AS arr_element_1_1, t[3][4] AS arr_element_3_2
FROM   x;

Answer 2

Вам нужно использовать агрегатную функцию; array_agg должен делать то, что вам нужно.

SELECT array_agg(s) FROM (SELECT name, id, url FROM the_table ORDER BY id) AS s;