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: ezima...@ulb.ac.be
Internet: http://code.ulb.ac.be/
------------------------------------------------------------
