Pass extra parameter to PostgreSQL aggregate final function

Question

Is the only way to pass an extra parameter to the final function of a PostgreSQL aggregate to create a special TYPE for the state value?

e.g.:

CREATE TYPE geomvaltext AS (
    geom public.geometry,
    val double precision,
    txt text
);

And then to use this type as the state variable so that the third parameter (text) finally reaches the final function?

Why aggregates can't pass extra parameters to the final function themselves? Any implementation reason?

So we could easily construct, for example, aggregates taking a method:

SELECT ST_MyAgg(accum_number, 'COMPUTE_METHOD') FROM blablabla

Thanks

Vao Tsun · Accepted Answer · 2017-09-26 12:31:37Z

5

You can define an aggregate with more than one parameter.

I don't know if that solves your problem, but you could use it like this:

CREATE OR REPLACE FUNCTION myaggsfunc(integer, integer, text) RETURNS integer
   IMMUTABLE STRICT LANGUAGE sql AS
$f$
   SELECT CASE $3
           WHEN '+' THEN $1 + $2
           WHEN '*' THEN $1 * $2
           ELSE NULL
        END
$f$;

CREATE AGGREGATE myagg(integer, text) (
   SFUNC = myaggsfunc(integer, integer, text),
   STYPE = integer
);

It could be used like this:

CREATE TABLE mytab
   AS SELECT * FROM generate_series(1, 10) i;

SELECT myagg(i, '+') FROM mytab;

 myagg 
-------
    55
(1 row)

SELECT myagg(i, '*') FROM mytab;

  myagg  
---------
 3628800
(1 row)

edited Sep 26, 2017 at 12:31

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

answered Sep 25, 2017 at 19:14

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

4 Comments

Pierre Over a year ago

The point is "How to pass additional parameters to the final function?". Passing them to the state function is easy.

Laurenz Albe Over a year ago

There is no way to do this, you'll have to create your own type. My answer was inspired by your example. What is the problem you are trying to solve? Maybe there is a simpler way than an extra argument to the final function.

Peter Krauss Over a year ago

Why PostgreSQL not allow additional parameters to the final function? There are many useful situations for it... Some foundation for this decision?

Laurenz Albe Over a year ago

It is unnecessary. As my answer explains, you can achieve it with the existing functionality. You can handle arguments for the final function more directly by using a composite type for the state and simply having that desired argument be one of the elements of this state.

CalmDownVal · Accepted Answer · 2018-07-25 02:39:21Z

I solved a similar issue by making a custom aggregate function that did all the operations at once and stored their states in an array.

CREATE AGGREGATE myagg(integer)
(
    INITCOND = '{ 0, 1 }',
    STYPE = integer[],
    SFUNC = myaggsfunc
);

and:

CREATE OR REPLACE FUNCTION myaggsfunc(agg_state integer[], agg_next integer)
RETURNS integer[] IMMUTABLE STRICT LANGUAGE 'plpgsql' AS $$
BEGIN
    agg_state[1] := agg_state[1] + agg_next;
    agg_state[2] := agg_state[2] * agg_next;
    RETURN agg_state;
END;
$$;

Then made another function that selected one of the results based on the second argument:

CREATE OR REPLACE FUNCTION myagg_pick(agg_state integer[], agg_fn character varying)
RETURNS integer IMMUTABLE STRICT LANGUAGE 'plpgsql' AS $$
BEGIN
    CASE agg_fn
        WHEN '+' THEN RETURN agg_state[1];
        WHEN '*' THEN RETURN agg_state[2];
        ELSE RETURN 0;
    END CASE;
END;
$$;

Usage:

SELECT myagg_pick(myagg("accum_number"), 'COMPUTE_METHOD') FROM "mytable" GROUP BY ...

Obvious downside of this is the overhead of performing all the functions instead of just one. However when dealing with simple operations such as adding, multiplying etc. it should be acceptable in most cases.

rd_nielsen · Accepted Answer · 2017-09-25 18:47:45Z

0

You would have to rewrite the final function itself, and in that case you might as well write a set of new aggregate functions, one for each possible COMPUTE_METHOD. If the COMPUTE_METHOD is a data value or implied by a data value, then a CASE statement can be used to select the appropriate aggregate method. Alternatively, you may want to create a custom composite type with fields for accum_number and COMPUTE_METHOD, and write a single new aggregate function that uses this new data type.

answered Sep 25, 2017 at 18:47

rd_nielsen

2,4592 gold badges14 silver badges19 bronze badges

1 Comment

Pierre Over a year ago

"COMPUTE_METHOD" is a text parameter specifying how the final result must be computed in the final function. The problem is to pass this parameter to the final function. Apparently this is not possible other than by building a new type comprising this text parameter, which is overcomplicated.

Collectives™ on Stack Overflow

Pass extra parameter to PostgreSQL aggregate final function

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related