CREATE FUNCTION (UDF)
Adds a user-defined scalar function to the catalog. You must have already loaded this library using the CREATE LIBRARY statement. When you call the SQL function, Vertica passes the parameters to the function in the library to process it.
CREATE FUNCTION (UDF) automatically determines the parameters and return value for the function from data supplied by the factory class.
Vertica supports overloading functions.
Syntax
CREATE [ OR REPLACE ] FUNCTION [[database.]schema.]function ... AS [ LANGUAGE 'language' ] NAME 'factory' LIBRARY library [ FENCED | NOT FENCED ];
Parameters
OR REPLACE
|
If you do not supply this parameter, the CREATE FUNCTION statement 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 myschema.thisDbObject If you specify a database, it must be the current database. |
function
|
The name of the scalar function to create, where function conforms to conventions described in Identifiers. 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, one of the following:
|
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 file that contains the C++ library, Python file, Java Jar file, or R functions file to perform the processing for this function. This library must have been previously loaded using the CREATE LIBRARY statement. |
FENCED | NOT FENCED
|
Enables or disables Fenced Mode for this function. Fenced mode is enabled by default. Functions written in Java and R always run in fenced mode. |
Privileges
- CREATE privilege on the function's schema
- USAGE privilege on the function's library
Examples
The following example demonstrates loading a library named scalarfunctions, then defining a function named Add2ints that is mapped to the Add2intsInfo factory class in the library:
=> CREATE LIBRARY ScalarFunctions AS '/opt/vertica/sdk/examples/build/ScalarFunctions.so'; CREATE LIBRARY => CREATE FUNCTION Add2Ints AS LANGUAGE 'C++' NAME 'Add2IntsFactory' LIBRARY ScalarFunctions; CREATE FUNCTION => \x Expanded display is on. => SELECT * FROM USER_FUNCTIONS; -[ RECORD 1 ]----------+---------------------------------------------------- schema_name | public function_name | Add2Ints procedure_type | User Defined Function function_return_type | Integer function_argument_type | Integer, Integer function_definition | Class 'Add2IntsFactory' in Library 'public.ScalarFunctions' volatility | volatile is_strict | f is_fenced | t comment | => \x Expanded display is off. => -- Try a simple call to the function => SELECT Add2Ints(23,19); Add2Ints ---------- 42 (1 row)