Re: Add PRODUCT() aggregate function

On 10/09/2026 18:16, Tom Lane wrote:
> 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.

Yes.  I tried to fight back on it but I was overruled.

>> 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.

That's fair, but it isn't what happens for string_agg, for example,
which can have a different separator per value.

SELECT string_agg(col, sep)
FROM (VALUES ('a', ','), ('b', ':'), ('c', 'd')) AS v (col, sep);

Result: a:bdc

> 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?

It's for both PRODUCT and SUM (1 and 0 respectively) and not generalized
beyond that.

I tried to at least make it IDENTITY ON EMPTY but that wasn't even
understood!

--

Vik Fearing