Re: Parameters in user-defined aggregate final functions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Esteban Zimanyi <ezimanyi(at)ulb(dot)ac(dot)be>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Parameters in user-defined aggregate final functions
Date: 2018-01-11 23:11:37
Message-ID: 24133.1515712297@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Vaishnavi Prabakaran 2018-01-11 23:12:35 Re: [HACKERS] PATCH: Batch/pipelining support for libpq
Previous Message Thomas Munro 2018-01-11 23:01:55 Re: [HACKERS] [BUGS] BUG #14825: enum type: unsafe use?