Example: my_sum Declaration

my_sum declaration

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