В ожидании 8.4 - накапливаем и разворачиваем массивы

Перевод Waiting for 8.4 - array aggregate and array unpacker с select * from depesz;

В нашем распоряжении появилось важное дополнение к PostgreSQL, которое облегчит нам жизнь при работе с массивами.

Теперь решится множество проблем, что обычно решались с помощью трюков, описанных в faq, блог-постах и часто объяснялись в irc.

Первым делом представляю агрегатную функцию для построения массивов. Патч принял Peter Eisentraut, вот с таким сообщением:

агрегатная функция array_agg, как в SQL:2008, но без ORDER BY 

Немного изменена документация с учётом того, что array_agg и 
xmlagg имеют схожую семантику и нюансы.

Robert Haas, Jeff Davis, Peter Eisentraut

И так, что это позволяет?

Создадим простую таблицу:

# create table simple_table (client_id int4, order_id int4);
CREATE TABLE

Заполним её случайными данными:

# insert into simple_table (client_id, order_id)
    select * from (
        select i, j from generate_series(1,4) i, generate_series(1,500) j
    ) x
where random() < 0.01;
INSERT 0 23

И проверим что получилось:

# select * from simple_table ;
 client_id | order_id
-----------+----------
         1 |      139
         1 |      195
         1 |      223
         1 |      226
         1 |      261
         1 |      325
         1 |      378
         1 |      452
         1 |      453
         2 |       89
         2 |       91
         2 |       92
         2 |      109
         2 |      183
         2 |      281
         2 |      324
         2 |      345
         2 |      386
         3 |       61
         3 |      112
         3 |      169
         3 |      178
         3 |      444
(23 rows)

Круто. Теперь допустим я хочу сделать выборку, перечисляющую все заказы для каждого клиента в одном поле. Раньше мне пришлось бы использовать медленные подзапросы, но сейчас я могу сделать следующее:

# select client_id, array_agg(order_id) from simple_table group by client_id;
 client_id |               array_agg
-----------+---------------------------------------
         2 | {89,91,92,109,183,281,324,345,386}
         3 | {61,112,169,178,444}
         1 | {139,195,223,226,261,325,378,452,453}
(3 rows)

Конечно результат можно отсортировать, преобразовать в строку или что-то еще:

# select client_id, array_to_string(array_agg(order_id), ', ') || '.'
from (
    select client_id, order_id from simple_table order by client_id, order_id
) x group by client_id;
 client_id |                   ?column?
-----------+----------------------------------------------
         1 | 139, 195, 223, 226, 261, 325, 378, 452, 453.
         2 | 89, 91, 92, 109, 183, 281, 324, 345, 386.
         3 | 61, 112, 169, 178, 444.
(3 rows)

Другой патч применил Tom Lane:

Реализует основную форму UNNEST, т.е. unnest(anyarray), 
возвращающую setof anyelement. Тут не хватает опции WITH 
ORDINALITY, а так же возможности подавать на вход более 
одного массива, что описано в свежей SQL спецификации. Но 
это уже вполне полезно, и достаточно для того, чтобы списать 
contrib/intagg

Что это делает? Всё просто:

# select * from unnest(array[1,2,3]) i;
 i
—
 1
 2
 3
(3 rows)

Как видно массив просто конвертируется в несколько записей.

Что не мало важно - конверсия рекурсивная:

# select array[array[1,2,3], array[4,5,6], array[7,8,9]];
           array
—————————
 {{1,2,3},{4,5,6},{7,8,9}}
(1 row)

# select * from unnest(array[array[1,2,3], array[4,5,6], array[7,8,9]]) i;
 i
—
 1
 2
 3
 4
 5
 6
 7
 8
 9
(9 rows)

Создание своей версии unnest довольно тривиально (в случае без рекурсии), но очень здорово то что у нас есть такая встроенная возможность.