Re: Parameters in user-defined aggregate final functions
От | David Fetter |
---|---|
Тема | Re: Parameters in user-defined aggregate final functions |
Дата | |
Msg-id | 20180111225324.GP4132@fetter.org обсуждение исходный текст |
Ответ на | Parameters in user-defined aggregate final functions (Esteban Zimanyi <ezimanyi@ulb.ac.be>) |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Robert HaasДата:
Сообщение: Re: [HACKERS] Parallel tuplesort (for parallel B-Tree index creation)