Re: Add PRODUCT() aggregate function

On Fri, Sep 11, 2026 at 5:16 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Vik Fearing <vik(at)postgresfriends(dot)org> writes:
> > On 10/09/2026 18:16, Tom Lane wrote:
> >> 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.
>
> Sure, but that's not the same thing. You can do something credible
> with a separator-per-value in string_agg, but it's nonsense to suppose
> that ON EMPTY is a per-row value. If it were per-row, which value
> would you use? I assume it applies even if there are zero input rows,
> not only if there are some inputs but they happen to all be null.
>

In my currently proposed patch (
https://www.postgresql.org/message-id/CAM2+6=VS=fSKxfimW6Th9iu_xjbxOEAKg4eYwaa=SMg3X8pHaQ@mail.gmail.com)
the ON EMPTY value is strictly returned only when there are zero input
rows. Rows containing NULL are treated as valid rows and do not trigger the ON
EMPTY clause.

>
> The committee is evidently choosing to sidestep the
> how-many-evaluations question by insisting on a constant value,
> which may well be sufficient for all real-world cases. If we want
> it to be "any a_expr" though, we have to think about that.
>
> > It's for both PRODUCT and SUM (1 and 0 respectively) and not generalized
> > beyond that.
>
> OK, at least the SUM case occurred to them ;-). But I think for
> our purposes we definitely want to allow it for any aggregate.
>

Yes, the proposed patch supports this for all aggregate functions and is
not restricted to specific ones at the moment.

Additionally, while the grammar accepts an a_expr, the backend code
includes checks to ensure the provided expression is a constant value that
is type-coercible to the aggregate's result type.

I would be very happy to receive any feedback or comments on that thread as
well.

Thanks,

>
> regards, tom lane
>

--
*Jeevan Chalke*
*Senior Principal Engineer, Engineering Manager*
*Product Development*

enterprisedb.com <https://www.enterprisedb.com>