Parameters in user-defined aggregate final functions

Поиск
Список
Период
Сортировка
От Esteban Zimanyi
Тема Parameters in user-defined aggregate final functions
Дата
Msg-id CAPqRbE6qTEadF0Ys2OU9ffnZkVTmxcuErfkdVwV4csZpsRqz6Q@mail.gmail.com
обсуждение исходный текст
Ответы Re: Parameters in user-defined aggregate final functions
Re: Parameters in user-defined aggregate final functions
Список 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 
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.be
Internet: http://code.ulb.ac.be/
------------------------------------------------------------

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Alexander Korotkov
Дата:
Сообщение: Re: CUBE seems a bit confused about ORDER BY
Следующее
От: Teodor Sigaev
Дата:
Сообщение: Re: CUBE seems a bit confused about ORDER BY