Re: Aggregate ORDER BY patch

On Friday 13 November 2009 16:35:08 Tom Lane 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.
The spec supports the ORDER BY syntax for the xmlagg aggregate...

Andres