Relative ordering in array aggregation

Started by Nishad Prakashabout 15 years ago2 messagesgeneral
Jump to latest
#1Nishad Prakash
prakashn@uci.edu

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Nishad Prakash (#1)
Re: Relative ordering in array aggregation

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