Parameters in user-defined aggregate final functions

From: Esteban Zimanyi <ezimanyi(at)ulb(dot)ac(dot)be>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Parameters in user-defined aggregate final functions
Date: 2018-01-11 19:51:27
Message-ID: CAPqRbE6qTEadF0Ys2OU9ffnZkVTmxcuErfkdVwV4csZpsRqz6Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I am creating a user-defined aggregate function that needs an additional
parameter. More precisely it is a cumulative (aka window) minimum that
takes as second parameter a time interval defining the window. Since the
aggregate function operates on my user-defined data types I have conveyed a
dummy example that concatenates the n last values of a text column. I am
aware that I can solve this dummy problem in PostgreSQL but the purpose of
the example is only to highlight my problem.

CREATE FUNCTION lastNconcat_transfn(state text[], next text, n integer)
RETURNS text[] AS $$
BEGIN
RETURN array_append(state, next);
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;

CREATE FUNCTION lastNconcat_combinefn(state1 text[], state2 text[], n
integer)
RETURNS text[] AS $$
BEGIN
RETURN array_concat(state1, state2);
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;

CREATE FUNCTION lastNconcat_finalfn(state text[], n integer)
RETURNS text AS $$
DECLARE
card integer;
result text;
BEGIN
result := '';
card := array_length(state, 1);
FOR i IN greatest(1,card-n+1)..card
LOOP
result := result || state[i];
END LOOP;
RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;

CREATE AGGREGATE lastNconcat(text, integer) (
SFUNC = lastNconcat_transfn,
STYPE = text[],
INITCOND = '{}',
COMBINEFUNC = lastNconcat_combinefn,
FINALFUNC = lastNconcat_finalfn,
PARALLEL = SAFE
);

I receive the following error

ERROR: function lastnconcat_finalfn(text[]) does not exist
SQL state: 42883

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.

Notice that the solution proposed here
https://stackoverflow.com/questions/48189751/direct-arguments-in-postgresql-user-defined-aggregate-functions/48190288?noredirect=1#comment83364017_48190288
is neither ideal nor efficient.

IMHO since combine functions accept parameters I don't see why final
functions should not also accept parameters.

--
------------------------------------------------------------
Prof. Esteban Zimanyi
Department of Computer & Decision Engineering (CoDE) CP 165/15
Universite Libre de Bruxelles
Avenue F. D. Roosevelt 50
B-1050 Brussels, Belgium
fax: + 32.2.650.47.13
tel: + 32.2.650.31.85
e-mail: ezimanyi(at)ulb(dot)ac(dot)be
Internet: http://code.ulb.ac.be/
------------------------------------------------------------

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Teodor Sigaev 2018-01-11 20:00:06 Re: CUBE seems a bit confused about ORDER BY
Previous Message Robert Haas 2018-01-11 19:51:18 Re: [HACKERS] Parallel tuplesort (for parallel B-Tree index creation)