Re: Aggregate ORDER BY patch

2009/11/14 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> writes:
>> "Peter" == Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>>  Peter> This is exactly the syntax that is in the spec AFAICT.
>
>> Right. The spec defines this syntax for array_agg and xmlagg (only).
>
> Cool, I had forgotten that they added that in the latest revisions.
> I withdraw the complaint that this patch goes too far beyond the spec.
>
>> But it would be entirely unreasonable, the way postgres works, to
>> implement ORDER BY for only specific aggregates.
>
> Quite.  This is another instance of the thing I complained of before,
> that the SQL committee likes to define the behavior of specific
> aggregates instead of inducing a generic aggregate-behavior definition.
> So we're on our own to extract one, and this proposal seems pretty
> reasonable to me: it's useful and it's consistent with the query-level
> behavior of DISTINCT and ORDER BY.
It's not only in aggregates but also window function as well as plain
functions like substring(x from t). In window functions, IGNORE NULLS
is defined in spec for those first_vlaue(), last_value(), lead(),
lag(), etc. but not for generic use. I'm +1 for an approach to apply
them for generic cases.

Regards,

--
Hitoshi Harada