VoltDB Home » Documentation » Using VoltDB


CREATE AGGREGATE FUNCTION — Defines an aggregate SQL function and associates it with a Java class.




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:

  1. 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.

  2. 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.)

  3. Load the JAR file into the database using the LOAD CLASSES statement.

  4. 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, “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.


The following example defines an aggregate function called longest_word from the start(), assemble(), combine(), and end() methods in the class LongestWord:

CREATE AGGREGATE FUNCTION longest_word FROM CLASS myapp.functions.LongestWord;