Example of Extending a Built-In Aggregate

The following example uses SPL functions to overload the plus() and divide() operators for a row type, complex, that represents a complex number. After you overload the operators, you can use the SUM, AVG, and COUNT operators with complex.

CREATE ROW TYPE complex(real FLOAT, imag FLOAT);

CREATE FUNCTION plus (c1 complex, c2 complex)
   RETURNING complex;
   RETURN row(c1.real +c2.real, c1.imag +c2.imag)::complex;
END FUNCTION;

CREATE FUNCTION divide (c1 complex, count INT)
   RETURNING complex;
   RETURN row(c1.real/count, c1.imag/count)::complex;
END FUNCTION;

You can now use the extended aggregates as follows:

CREATE TABLE c_test (a complex, b integer);
INSERT INTO c_test VALUES (ROW(4,8)::complex,14);
INSERT INTO c_test VALUES (ROW(7,9)::complex,3);
...
SELECT SUM(a) FROM c_test;
SELECT AVG(a) FROM c_test;
SELECT COUNT(a) FROM c_test;