Re: Bug Fix: COLLATE with multiple ORDER BYs in aggregates

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2013-04-25 13:42:32 -0400, Tom Lane wrote:
>> The argument for it seems to be that
>> array_agg(a COLLATE "C" ORDER BY b COLLATE "POSIX")
>> should not throw an error, but why not?

> Uh. Why should it? SELECT foo COLLATE "C" FROM ... ORDER BY bar COLLATE
> "POSIX" doesn't throw one either?

After thinking about it a bit more, this case *should* throw an error:

string_agg(a COLLATE "C", b COLLATE "POSIX")

but these should not:

array_agg(a COLLATE "C" ORDER BY b COLLATE "POSIX")

array_agg(a ORDER BY b COLLATE "C", c COLLATE "POSIX")

that is, the ORDER BY expression(s) ought to be considered independently
rather than as part of the agg's argument list.

It looks like the proposed patch gets this right, but the proposed
test cases really fail to illuminate the problem IMO.

regards, tom lane