Re: Parameters in user-defined aggregate final functions

On Thu, Jan 11, 2018 at 6:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Esteban Zimanyi <ezimanyi(at)ulb(dot)ac(dot)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.

Or alternatively, don't define a final function at all, or define one
that just serializes the transition state to JSON or whatever. Then
define some completely separate function that takes the transition
state (or the serialized representation thereof) and the additional
parameters and write something like:

SELECT completely_separate_nonaggregate_function(not_quite_the_aggregate_i_really_want(stuff),
42, 'omaha') FROM my_table;

Like Tom's proposal that's syntactically different but it should be
close enough.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company