CREATE FUNCTION (UDF)

Adds a user-defined function (UDF) 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.

Note: This topic describes how to use CREATE FUNCTION to create a User Defined Function. If you want to create a SQL function, see CREATE FUNCTION (SQL Function).

In addition, if you want to create a user-defined transform function (UDTF), see CREATE TRANSFORM FUNCTION.

Syntax

CREATE [ OR REPLACE ] FUNCTION [schema.]function-name 
... AS [ LANGUAGE 'language' ] NAME 'factory' LIBRARY library-name 
[ 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.
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, where function-name 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: `C++` `Python` `Java` `R`
`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 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

To create a function, the user must have CREATE privilege on the schema to contain the function and USAGE privilege on the library containing the function.
To use a function, the user must have USAGE privilege on the schema that contains the function and EXECUTE privileges on the function.
To drop a function, the user must either be a superuser, the owner of the function, or the owner of the schema which contains the function.

Notes

The parameters and return value for the function are automatically determined by the CREATE FUNCTION statement, based on data supplied by the factory class.
Multiple functions can share the same name if they have different parameters. When you call a multiply-defined function, Vertica selects the UDF function whose input parameters match the parameters in the function call to perform the processing. This behavior is similar to having multiple signatures for a method or function in other programming languages.
You can return a list of all SQL functions and UDFs by querying the system table V_CATALOG.USER_FUNCTIONS or executing the vsql meta-command \df. Users see only the functions on which they have EXECUTE privileges.

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)