Extending Informix Dynamic Server 2000 Creating User-Defined Aggregates

The database server provides built-in aggregate functions, such as SUM and COUNT, that operate on the built-in data types. You can extend a built-in aggregate so that it can operate on user-defined data types. To extend a built-in aggregate, you must create user-defined routines that overload several binary operators.

The following table shows the operators that you must overload for each of the built-in aggregates. For example, if you need only the SUM aggregate for a user-defined data type, you need to overload only the plus() operator.

The database server uses the compare() operator for indexing as well as for DISTINCT and UNIQUE aggregations. You must create the compare() function as an external function. You cannot use the compare() operator with user-defined types that are not hashable. For a description of hashable user-defined types, refer to Hashable Data Types.

When you extend a built-in aggregate to include a user-defined data type, you do not use the CREATE AGGREGATE statement because the aggregate itself already exists.

1. Develop functions to overload the required operators.
2. Register each function with a CREATE FUNCTION statement.

For more information, refer to Registering a User-Defined Routine.

After you have registered the functions that overload the binary operators, you can use the built-in aggregates in an SQL statement.

For the syntax of the CREATE FUNCTION statement, see the Informix Guide to SQL: Syntax. For more information about writing overloaded functions, refer to Overloading Routines. For information about writing functions in external languages, refer to the DataBlade API Programmer's Manual or Creating UDRs in Java.

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;