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.
Syntax
CREATE [ OR REPLACE ] AGGREGATE FUNCTION [schema.]function-name ... AS LANGUAGE 'language' NAME 'factory' LIBRARY library-name;
Parameters
OR REPLACE
|
If you do not supply this parameter, |
schema |
Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example: myschema.thisDbObject |
function‑name
|
The name of the function to create. If the function name is schema-qualified (as above), the function is created in the specified schema. 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 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‑name
|
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. |
Notes
- The parameters and return value for the function are automatically determined by the CREATE AGGREGATE FUNCTION statement, based on data supplied by the factory class.
- When a User Defined Aggregate function that is defined multiple times with arguments of different data types is called, Vertica selects the function whose input parameters match the parameters in the function call to perform the processing.
- You can return a list of all SQL functions and User Defined Functions (including aggregates) by querying the system table
V_CATALOG.USER_FUNCTIONS
or executing the vsql meta-command\df
. Users see only the functions on which they haveEXECUTE
privileges.
Privileges
- Only a superuser can create or drop a User Defined Aggregate library.
- To create a User Defined Aggregate function, the user must have CREATE and USAGE privileges on the schema and USAGE privileges on the library.
- To use a User Defined Aggregate, the user must have USAGE privileges on the schema and EXECUTE privileges on the defined function. See GRANT (User Defined Extension) and REVOKE (User Defined Extension).
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 |