Vertica Analytics Platform Version 9.2.x Documentation

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 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

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:

  • 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

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)