CREATE AGGREGATE FUNCTION

Adds to the catalog a user-defined aggregate function (UDAF) that is stored in a shared Linux library. CREATE AGGREGATE FUNCTION automatically determines the function parameters and return value from data supplied by the factory class. Vertica supports overloading aggregate functions. When you call the SQL function, Vertica passes the input table to the function in the library to process.

Syntax

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

Parameters

OR REPLACE

Specifies to overwrite an existing function of the same name and matching arguments; otherwise the CREATE statement returns with a rollback error.
[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‑name

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 (the default).
NAME 'factory'

Name of the shared library factory class that generates the object to handle function processing.
LIBRARY library

Name of the shared library that contains the C++ object to process this function. This library must already be loaded by CREATE LIBRARY.

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