Writing my own aggregate function in postgresql

Question

I've never wriiten my own aggreagtes, only store procedures and I need some advice. I want to write a custom aggregate which is going to return maximum value of the integer rows and incerement it by 10. How can I do that? I tried this:

CREATE AGGREGATE incremented_max ( v ) (
    SFUNC = max,
    STYPE = integer,
    INITCOND = max + 10
)

but it didn't work. Can someone help me out?

I got the error:

ERROR:  syntax error at or near "+"
LINE 4:     INITCOND = max + 10

Honestly, I didn't understand how it should work.

Answer 1

The following example shows how to create an aggregate using custom functions:

create function greaterint (int, int)
returns int language sql
as $$
    select case when $1 < $2 then $2 else $1 end
$$;

create function intplus10 (int)
returns int language sql
as $$
    select $1+ 10;
$$;

create aggregate incremented_max (int) (
    sfunc = greaterint,
    finalfunc = intplus10,
    stype = integer,
    initcond = 0
);

select incremented_max(v), max(V)
from (
    select 3 as v
    union select 10
    union select 12
    union select 5) alias

Sfunc is a state transition function. It is executed in an aggregation as many times as there are rows to aggregate. Its first argument is an internal-state, i.e. the value accumulated so far. In the first call the value is equal to initcond (or null if initcond was not defined). The second argument is a next-data-value, i.e. value from the next row. In the above example the function calculates the maximum of not null positive integers and is executed four times (for four rows):

call #      internal-state     next-data-value       result
1           0 (initcond)       3                     3 (because 3 > 0)
2           3                  10                    10 (10 > 3)
3           10                 12                    12 (12 > 10)
4           12                 5                     12 (12 > 5)

Finalfunc is executed once at the end of an aggregation. It has one argument (the value calculated so far) and returns the final (modified) result of the aggregation. In our example it just adds 10 to the result of the aggregation.

Read more in the documentation.

The example above is just a kind of exercise. In fact, there is no need to define such an aggregate since select max (v + 10) gives the desired result.

Answer 2

BEGIN;

CREATE AGGREGATE inc_sum(int) (
    sfunc = int4pl,
    stype = int,
    initcond = 10
);

CREATE TEMP TABLE tt (i int);
INSERT INTO tt VALUES (1),(2),(3),(4);

SELECT sum(i), inc_sum(i) FROM tt;

ROLLBACK;

 sum | inc_sum 
-----+---------
  10 |      20
(1 row)

In the above int4pl is the underlying function for "+" (found by listing functions starting with int and then guessing).

The initcond is the initial condition, so where you start from. For a normal sum that would be 0.