From: Heikki Linnakangas on
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?

> No artificial restrictions are imposed on what
> syntactical combinations are allowed. However, ORDER BY is not allowed
> with aggregates used as window functions (as per the existing
> restriction on DISTINCT).

How is this different from window functions?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Greg Stark on
On Fri, Nov 13, 2009 at 7:54 AM, Heikki Linnakangas
<heikki.linnakangas(a)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()

I suspect it would be good to have a flag marking the commutative ones


--
greg

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Peter Eisentraut on
On fre, 2009-11-13 at 03:16 +0000, Andrew Gierth wrote:
> Caveat: as discussed earlier, this patch changes the behaviour of
> array_agg(DISTINCT x) when applied to NULL inputs. Formerly, the NULLs
> were unconditionally skipped; now, they are treated just like DISTINCT
> or GROUP BY normally do.

The right answer to that should be in the SQL standard.



--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Tom Lane on
Peter Eisentraut <peter_e(a)gmx.net> writes:
> On fre, 2009-11-13 at 03:16 +0000, Andrew Gierth wrote:
>> Caveat: as discussed earlier, this patch changes the behaviour of
>> array_agg(DISTINCT x) when applied to NULL inputs. Formerly, the NULLs
>> were unconditionally skipped; now, they are treated just like DISTINCT
>> or GROUP BY normally do.

> The right answer to that should be in the SQL standard.

It's not. The standard defines the behavior of certain specific
aggregates; it doesn't provide general rules that would apply to
user-defined aggregates. In particular, all the standard aggregates
are strict and so they just ignore nulls anyhow. The proposed change
would only affect the behavior of aggregates with non-strict transition
functions.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Tom Lane on
Greg Stark <gsstark(a)mit.edu> writes:
> On Fri, Nov 13, 2009 at 7:54 AM, Heikki Linnakangas
> <heikki.linnakangas(a)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.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers