Here is a patch set adding an optional ON EMPTY clause to aggregate (and aggregate-as-window-function) calls. It supplies a value to return when the aggregate processes no input rows at all:
agg_function(args, default_value ON EMPTY)
For example:
SELECT sum(i, -1 ON EMPTY) FROM generate_series(1,10) AS s(i) WHERE i > 100; sum ----- -1 (1 row)
ON EMPTY is triggered only by an empty input set, not by NULL inputs that are ignored during aggregation. A FILTER that removes all rows makes the input set empty, so the default applies in that case too. Because a grouped query never produces empty groups, ON EMPTY takes effect for an ungrouped aggregate over zero rows, or for a group whose rows are all removed by FILTER. For an aggregate used as a window function, the default is returned for any row whose frame contains no rows. It also works with an ordered-set aggregate, with the default written before WITHIN GROUP:
Is there any chance of storing a default default_value with the aggregate? I ask because for most aggregate functions there is a specific value for each function which is almost always what will be wanted, e.g., 0 for sum, -Infinity for max, +Infinity for min, 1 for multiplication (if the other proposal for a multiplication aggregate is accepted), and so on, generally characterizable as the identity element for the function in question. Only in rare cases would one actually want to override the identity and use some other specified value.
I was going to suggest there would need to be an additional clause for CREATE AGGREGATE, but I see there is already an INITCOND parameter which in principle should already be doing the job (except that as I understand it the aggregate isn't invoked at all for empty input?).
Also I'm not entirely happy with the name "default_value". It's not really a default, just the value of the aggregate at empty input. Unfortunately, I don't have a better suggestion.
