Pass extra parameter to PostgreSQL aggregate final function

Question

Is the only way to pass an extra parameter to the final function of a PostgreSQL aggregate to create a special TYPE for the state value?

e.g.:

CREATE TYPE geomvaltext AS (
    geom public.geometry,
    val double precision,
    txt text
);

And then to use this type as the state variable so that the third parameter (text) finally reaches the final function?

Why aggregates can't pass extra parameters to the final function themselves? Any implementation reason?

So we could easily construct, for example, aggregates taking a method:

SELECT ST_MyAgg(accum_number, 'COMPUTE_METHOD') FROM blablabla

Thanks

Answer 1

You can define an aggregate with more than one parameter.

I don't know if that solves your problem, but you could use it like this:

CREATE OR REPLACE FUNCTION myaggsfunc(integer, integer, text) RETURNS integer
   IMMUTABLE STRICT LANGUAGE sql AS
$f$
   SELECT CASE $3
           WHEN '+' THEN $1 + $2
           WHEN '*' THEN $1 * $2
           ELSE NULL
        END
$f$;

CREATE AGGREGATE myagg(integer, text) (
   SFUNC = myaggsfunc(integer, integer, text),
   STYPE = integer
);

It could be used like this:

CREATE TABLE mytab
   AS SELECT * FROM generate_series(1, 10) i;

SELECT myagg(i, '+') FROM mytab;

 myagg 
-------
    55
(1 row)

SELECT myagg(i, '*') FROM mytab;

  myagg  
---------
 3628800
(1 row)