Re: Add PRODUCT() aggregate function

Vik Fearing <vik(at)postgresfriends(dot)org> writes:
> It should be called PRODUCT and it should accept the syntax
>     PRODUCT(col, 1 ON EMPTY)
> for when there are no non-nulls in the input.

Sigh. The committee really loves to invent randomly creative syntax,
don't they. At least this one won't force us to create any new
fully-reserved words.

> This will be required by the next edition of the SQL standard. The exact
> value "1" is required by the standard, but I think we should allow any
> a_expr there.

I think that this may be trickier than it looks. You'd really want
that to act like a "direct" argument, ie evaluate once not once per
row. Also, if PRODUCT can use this, SUM could use it even more,
and probably other aggregates too (but likely values would be
different from "1"). Did they generalize at all, or is this a
PRODUCT-specific wart?

Anyway, I'd counsel thinking of this ON EMPTY business as an
orthogonal feature to PRODUCT. My real concern about PRODUCT
as such is that it seems enormously prone to overflow. It'd
make little sense to invent variants emitting anything except
numeric or float8.

regards, tom lane