Re: Aggregate ORDER BY patch

On Fri, Nov 13, 2009 at 10:35 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Greg Stark <gsstark@mit.edu> writes:
>> On Fri, Nov 13, 2009 at 7:54 AM, Heikki Linnakangas
>> <heikki.linnakangas@enterprisedb.com> wrote:
>>> Andrew Gierth wrote:
>>>> Herewith a patch to implement agg(foo ORDER BY bar) with or without
>>>> DISTINCT, etc.
>>>
>>> What does that mean? Aggregate functions are supposed to be commutative,
>>> right?
>
>> We certainly have non-commutative agggregates currently, notably array_agg()
>
> Right.  The fact that none of the standard aggregates are
> order-sensitive doesn't mean that it's not useful to have user-defined
> ones that are.  Currently we suggest fetching from an ordered sub-select
> if you want to use an aggregate that is input order sensitive.  This
> patch just provides an alternative (and equally nonstandard) notation
> for that.
>
> I'm not entirely convinced that adding ORDER BY here is a good idea,
> partly because it goes so far beyond the spec and partly because it's
> not going to be easily optimizable.  But I can see that there is a
> use-case.

Yeah, for sure.  I currently handle this, when necessary, by using
subselects, but it would sure be nice to have a more compact notation,
if there's a good way to do that.

...Robert