CREATE AGGREGATE FUNCTION

Adds a user-defined aggregate function (UDAF) stored in a shared Linux library to the catalog. You must have already loaded this library using the CREATE LIBRARY statement. When you call the SQL function, Vertica passes data values to the code in the library to process it.

CREATE AGGREGATE FUNCTION automatically determines the parameters and return value for the function from data supplied by the factory class.

Vertica supports overloading aggregate functions.

Syntax

CREATE [ OR REPLACE ] AGGREGATE FUNCTION [[database.]schema.]function 
... AS [LANGUAGE 'language'] NAME 'factory' LIBRARY library;

Parameters

OR REPLACE

If you do not supply this parameter, CREATE AGGREGATE FUNCTION fails if an existing function matches the name and parameters of the function you are trying to define. If you do supply this parameter, the new function definition overwrites the old.
[database.]schema

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

If you specify a database, it must be the current database.
function

Identifies the function to create, where function conforms to conventions described in Identifiers.

This name does not need to match the name of the factory, but it is less confusing if they are the same or similar.
LANGUAGE 'language'

The language used to develop this function, currently C++ only.

Default value: C++
NAME 'factory'

The name of the factory class in the shared library that generates the object to handle the function's processing.
LIBRARY library

The name of the shared library that contains the C++ object to perform the processing for this function. This library must have been previously loaded using the CREATE LIBRARY statement.

Privileges

Non-superuser:

  • CREATE privilege on the function's schema
  • USAGE privilege on the function's library

Examples

The following example demonstrates loading a library named AggregateFunctions then defining a function named ag_avg and ag_cat that are mapped to the ag_cat AverageFactory and ConcatenateFactory classes in the library:

=> CREATE LIBRARY AggregateFunctions AS '/opt/vertica/sdk/examples/build/AggregateFunctions.so';
CREATE LIBRARY
=> CREATE AGGREGATE FUNCTION ag_avg AS LANGUAGE 'C++' NAME 'AverageFactory'
   library AggregateFunctions;
CREATE AGGREGATE FUNCTION
=> CREATE AGGREGATE FUNCTION ag_cat AS LANGUAGE 'C++' NAME 'ConcatenateFactory'
   library AggregateFunctions;
CREATE AGGREGATE FUNCTION
=> \x
Expanded display is on.
select * from user_functions;
-[ RECORD 1 ]----------+------------------------------------------------------------------
schema_name            | public
function_name          | ag_avg
procedure_type         | User Defined Aggregate
function_return_type   | Numeric
function_argument_type | Numeric
function_definition    | Class 'AverageFactory' in Library 'public.AggregateFunctions'
volatility             | 
is_strict              | f
is_fenced              | f
comment                | 
-[ RECORD 2 ]----------+------------------------------------------------------------------
schema_name            | public
function_name          | ag_cat
procedure_type         | User Defined Aggregate
function_return_type   | Varchar
function_argument_type | Varchar
function_definition    | Class 'ConcatenateFactory' in Library 'public.AggregateFunctions'
volatility             | 
is_strict              | f
is_fenced              | f
comment                |

See Also