Re: ON EMPTY clause for aggregate and window functions

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> This seems pretty useless -- the new syntax is more work for us to
> maintain, and doesn't really add any value over just using COALESCE.

It could add value, in scenarios where substitute-for-NULL doesn't
give quite the behavior you want. But that's not so for the two
aggregates the spec has bothered to define this for, and I'm having
a hard time coming up with an aggregate for which it would be so.

If somebody actually did have an aggregate that could return null for
more than zero input rows, they could always do

CASE WHEN count(x) > 0 THEN frobnitz(x) ELSE value_for_zero_rows END

which has the extra benefit that you can choose "count(x)" or
"count(*)" depending on your desires for what to do with null
inputs.

> To be clear, I expect to lose this argument on the grounds that
> apparently this syntax is in the spec and therefore we ought to
> support it. But I don't understand why the spec -- or we -- should
> spend time inventing new ways to spell existing behaviors. That just
> seems confusing (and this particular choice of syntax seems
> extra-confusing).

Yes, this choice of syntax sucks pretty badly. In the COALESCE
spelling, it's crystal clear that the substitute value is not an
aggregate argument and so is evaluated at most once (per group);
there's no need for gamesmanship around restricting it to be a
constant.

I'd be totally fine with rejecting this as a frammish we do
not care to support.

regards, tom lane