Esteban Zimanyi <ezimanyi@ulb.ac.be> writes:
> How to tell PostgreSQL that my final function also needs a parameter? I am
> working on PostgreSQL 10.1. I know that according to the documentation
> direct parameters are only allowed for ordered-set aggregates, but I would
> also need a direct parameter for "normal" aggregates.
So define it as an ordered-set aggregate, and just ignore the question
of whether you need to sort the input (which is something that we leave
to the aggregate function to do anyway). The syntax would be a little
weird/non-orthogonal, but you can blame the SQL committee for that.
regression=# create function trans(int, int) returns int language sql
regression-# as 'select $1+$2' strict;
CREATE FUNCTION
regression=# create function final(int, float8) returns float8 language sql
regression-# as 'select $1*$2' strict;
CREATE FUNCTION
regression=# create aggregate myosa(float8 order by int) (
regression(# sfunc = trans, stype = int, finalfunc = final);
CREATE AGGREGATE
regression=# select sum(ten), myosa(0.5) within group (order by ten) from tenk1;
sum | myosa
-------+-------
45000 | 22500
(1 row)
regards, tom lane