Direct arguments in PostgreSQL user-defined aggregate functions

Question

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.

Laurenz Albe · Accepted Answer · 2018-01-10 16:23:20Z

1

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.

edited Jan 10, 2018 at 16:23

answered Jan 10, 2018 at 15:04

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Esteban Zimanyi Over a year ago

Indeed, I already thought about this possibility but the problem is that arrays in PostgreSQL are not polymorphic and thus, in my case, the type of the 0-th element of the array would of type interval, and the type of the other elements in the state array would be my user-defined data type (e.g., temporal floats) :-(

Laurenz Albe Over a year ago

No problem. Use a composite type. I have extended the answer accordingly.

Collectives™ on Stack Overflow

Direct arguments in PostgreSQL user-defined aggregate functions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related