CREATE DERBY AGGREGATE statement

The CREATE DERBY AGGREGATE statement creates a user-defined aggregate (UDA). A UDA is a custom aggregate operator.

Syntax

CREATE DERBY AGGREGATE aggregateName FOR valueDataType
[ RETURNS returnDataType ]
EXTERNAL NAME singleQuotedString

The aggregate name is composed of an optional schemaName and a SQL92Identifier. If a schemaName is not provided, the current schema is the default schema. If a qualified aggregate name is specified, the schema name cannot begin with SYS.

In general, UDAs live in the same namespace as one-argument user-defined functions (see CREATE FUNCTION statement). A schema-qualified UDA name may not be the schema-qualified name of a one-argument user-defined function.

An unqualified UDA name (that is, the UDA name without its schema name) may not be the name of an aggregate defined in part 2 of the SQL Standard, section 10.9:

ANY
AVG
COLLECT
COUNT
EVERY
FUSION
INTERSECTION
MAX
MIN
SOME
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP

In addition, an unqualified UDA name may not be the name of any of the Derby built-in functions which take one argument.

The valueDataType can be any valid nullable Derby data type except for XML, including user-defined types.

The returnDataType can be any valid nullable Derby data type except for XML. If the returnDataType is omitted, it defaults to be the same as valueDataType.

The singleQuotedString specified by the EXTERNAL NAME clause is the full name of a Java class which implements the org.apache.derby.agg.Aggregator interface. That contract is not checked until a statement is compiled which invokes the UDA.

The org.apache.derby.agg.Aggregator interface extends java.io.Serializable, so you must make sure that all of the state of your UDA is serializable. A UDA may be serialized to disk when it performs grouped aggregation over a large number of groups. That is, intermediate results may be serialized to disk for a query like the following:

SELECT a, myAggregate( b ) FROM myTable GROUP BY a

The serialization will fail if the UDA contains non-serializable fields.

The owner of the schema where the UDA lives automatically gains the USAGE privilege on the UDA and can grant this privilege to other users and roles. Only the database owner and the owner of the UDA can grant these USAGE privileges. The USAGE privilege cannot be revoked from the schema owner. See GRANT statement and REVOKE statement for more information.

CREATE DERBY AGGREGATE mode FOR INT
EXTERNAL NAME 'com.example.myapp.aggs.Mode';

CREATE DERBY AGGREGATE types.maxPrice FOR PRICE
EXTERNAL NAME 'com.example.myapp.types.PriceMaxer';

CREATE DERBY AGGREGATE types.avgLength FOR VECTOR
RETURNS DOUBLE
EXTERNAL NAME 'com.example.myapp.types.VectorLength';