Thread: Relative ordering in array aggregation

I'm using 8.3.3.  I came across the array_accum aggregate function in
the docs:

http://www.postgresql.org/docs/8.3/interactive/xaggr.html

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


This would be very useful to me, but I have a question about ordering
of values.  If I use more than one array_accum in the same select
clause, is there any reliable correspondence between the order of
elements in the returned arrays?  In other words, if a have a table
foo like so:

   a    b    c
 ----+----+----
 aaa | 12 | a1
 aaa | 13 | a2

and I run

 select a, array_accum (b), array_accum (c) from foo group by a;

then can I expect that the results will always be one of
 aaa | {12, 13} | {a1, a2}
or
 aaa | {13, 12} | {a2, a1}?


OR, could the query also return the arrays as
 aaa | {13, 12} | {a1, a2}
or
 aaa | {12, 13} | {a2, a1}?

If so, would there be any way to enforce the first behaviour?

Thanks,

nishad

prakashn@uci.edu writes:
> I'm using 8.3.3.  I came across the array_accum aggregate function in
> the docs:
> http://www.postgresql.org/docs/8.3/interactive/xaggr.html

> This would be very useful to me, but I have a question about ordering
> of values.  If I use more than one array_accum in the same select
> clause, is there any reliable correspondence between the order of
> elements in the returned arrays?

As long as you don't use DISTINCT (or, in more recent versions than 8.3,
within-aggregate ordering of the input rows), all aggregates in the same
SELECT list should receive the input rows in the same order.

            regards, tom lane