Re: Parameters in user-defined aggregate final functions

On Thu, Jan 11, 2018 at 08:51:27PM +0100, Esteban Zimanyi wrote:
> 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.

This is an interesting problem. In CREATE AGGREGATE, I count 10
parameters that could easily have a function attached. One could
imagine an aggregate which took different parameters at each stage,
but is there really any sane way to do this other than making a call
to the aggregate with those parameters all included, passing each
along as one goes?

SELECT my_custom_agg(expression) WITH ([finalfunc_args = ...][, finalfunc_extra_args = ...]...)

is what I could come up with. It seems ugly as grammar and
ill-advised in that it makes promises about the implementation details
of aggregates into a distant future.

What am I missing?

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate