CREATE AGGREGATE FUNCTION — Defines an aggregate SQL function and associates it with a Java class.
CREATE AGGREGATE FUNCTION function-name FROM CLASS class-path
The CREATE AGGREGATE FUNCTION statement declares a user-defined aggregate function and associates it with a Java class. Aggregate functions process multiple values based on a query expression and produce a single result. For example, the built-in AVG aggregate function calculates the average of the values of a specific column or expression based on the query constraints.
The return value of a user-defined aggregate function matches the datatype of the Java method itself. Similarly, the number and datatype of the function's arguments are defined by the arguments of the method.
User-defined aggregate functions allow you to extend the functionality of the SQL language by declaring your own functions that can be used in SQL queries and data manipulation statements. The steps for creating a user-defined aggregate function are:
Write, compile, and debug the program code for a class that performs the function's action. The class must include the following methods:
start() — Initializes the function. Called once for each invocation of the
function.
assemble( arg,... ) — Processes the arguments to the function. called once for each
record matching the constraints of the query in which the function appears.
combine( class-instance ) — For partitioned queries, combines the results of one
partition into the results of another.
end() — Finalizes the function and returns the function result. Called once at the
completion of the function invocation.
Package the class in a JAR file, just as you would a stored procedure. (Classes for functions and stored procedures can be packaged in the same JAR file.)
Load the JAR file into the database using the LOAD CLASSES statement.
Declare and name the user-defined function using the CREATE AGGREGATE FUNCTION statement.
The Java methods that implement the user-defined function must follow the same rules for determinism as user-defined stored procedures, as outlined in Section 5.1.2.1, “Avoid Introducing Non-deterministic Values from External Functions”. See the chapter on "Creating Custom SQL Functions" in the VoltDB Guide to Performance and Customization for details on designing the Java class and methods necessary for a user-defined aggregate function.
To declare a scalar rather than an aggregate function, see the description of the CREATE FUNCTION statement.
Documentation