CODEWITHSUNDEEP
postgresqlparametersuser-defined-aggregate
Esteban Zimanyi
Esteban Zimanyi

Reputation: 201

Direct arguments in PostgreSQL user-defined aggregate functions

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 computes the average of the n last values of a 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 lastNavg_transfn(state integer[], next integer, n integer)
    RETURNS integer[] AS $$
    BEGIN
        RETURN array_append(state, next);
    END;
    $$ LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;

CREATE FUNCTION lastNavg_finalfn(state integer[], n integer)
    RETURNS float AS $$
    DECLARE
        card integer;
        count float;
        sum float;
    BEGIN
        count := 0;
        sum := 0;
        card := array_length(state, 1);
        FOR i IN greatest(1,card-n+1)..card
        LOOP 
            sum := sum + state[i];
            count := count + 1;
        END LOOP;
        RETURN sum/count;
    END;
    $$ LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;

CREATE AGGREGATE lastNavg(integer, integer) (
    SFUNC = lastNavg_transfn,
    STYPE = integer[],
    INITCOND = '{}',
    FINALFUNC = lastNavg_finalfn,
    PARALLEL = SAFE
);

I receive the following error 

ERROR: function lastnavg_finalfn(integer[]) does not exist
SQL state: 42883

How to tell PostgreSQL that my final function also needs a direct 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.

Upvotes: 2

Views: 842

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

You could define an aggregate with two arguments and supply your additional parameter as constant second argument.

The SFUNC simply stores the second argument e.g. as 0-th element of the STYPE integer array.

FINALFUNC has only a single argument and gets n from the 0-th element of the array.

If you need a second argument that is not an integer, define a composite type and use that as STYPE.

Not pretty, but it should do the trick.

Upvotes: 1

Related Questions