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.
