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