Перевод 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 довольно тривиально (в случае без рекурсии), но очень здорово то что у нас есть такая встроенная возможность.
Posts
