Waiting for 8.4 – array aggregate and array unpacker

Finally, we got very important addons to PostgreSQL, which help with dealing with arrays.

It solves a lot of problems, which were usually solved with standard cookbook code, which was in faqs, blog posts and number of examples on irc.

First, there is a function to build array as aggregate.

It was committed by Peter Eisentraut yesterday, with this commit message:
 
array_agg aggregate function, as per SQL:2008, but without ORDER BY clause
 
Rearrange the documentation a bit now that array_agg and xmlagg have similar
semantics and issues.
 
best of Robert Haas, Jeff Davis, Peter Eisentraut

So, what can it do?

Let's make a simple table:

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

Fill it with some random data:

# 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

And let's check what we have there:

# 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)

Cool. Now, let's say that I'd like to write select which lists all orders for given client in one field. Previously I would have to use subselect, which would be very slow, but now I can:

# 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)

Of course you can modify it to have the order_ids sorted, converted to string – or anything else you'd want:

# 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)

Second patch was committed by Tom Lane, and does:

Implement the basic form of UNNEST, ie unnest(anyarray) returns setof
anyelement.  This lacks the WITH ORDINALITY option, as well as the multiple
input arrays option added in the most recent SQL specs.  But it's still a
pretty useful subset of the spec's functionality, and it is enough to
allow obsoleting contrib/intagg.

What it does? It's pretty simple:

# SELECT * FROM unnest(array[1,2,3]) i;
 i
---
 1
 2
 3
(3 ROWS)

As you can see it simply converts array to recors.

What's more important – the conversion is recursive:

# 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)

Writing your own version of unnest is easy or trivial (in case of non-recursive version), but it's really great to have it built in, and ready to be used.

4 thoughts on “Waiting for 8.4 – array aggregate and array unpacker”

  1. Cool! I still recall writing my first array aggregation functions, back in 8.0 or so, and being a bit disappointed in how PostgreSQL had no built in functions for that. Great to see it in place!

  3. just for the record, previous releases will just require you to add:

    CREATE AGGREGATE array_accum (anyelement)
    (
    sfunc = array_append,
    stype = anyarray,
    initcond = ‘{}’
    );

    and use it as such:
    select array_accum(column) from foo;

    ;]

Comments are closed.