The “my_sum” example is similar to the built-in SUM, except it only operates on integers.
Since my_sum, like SUM, can be used in any context, it has a relatively brief declaration:
CREATE AGGREGATE FUNCTION my_sum(IN arg1 INT) RETURNS BIGINT ON EMPTY INPUT RETURNS NULL EXTERNAL NAME 'describe_my_integer_sum@my_shared_lib'
The various usage restrictions all default to ALLOWED to specify that this function can be used anywhere in a SQL statement that any aggregate function is allowed.
Without any usage restrictions, my_sum is usable as a simple aggregate across an entire set of rows, as shown here:
SELECT MIN(t.x), COUNT (*), my_sum(t.y) FROM t
Without usage restrictions, my_sum is also usable as a simple aggregate computed for each group as specified by a GROUP BY clause.
SELECT t.x, COUNT(*), my_sum(t.y) FROM t GROUP BY t.x
Because of the lack of usage restrictions, my_sum is usable as an OLAP-style aggregate with an OVER clause, as shown in this cumulative summation example:
SELECT t.x, my_sum(t.x) OVER (ORDER BY t.x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_x, COUNT(*) FROM t GROUP BY t.x ORDER BY t.x