CREATE FUNCTION (Scalar)

Adds a user-defined scalar function (UDSF) to the catalog. UDSFs take in a single row of data and return a single value. These functions can be used anywhere a native Vertica function or statement can be used, except CREATE TABLE with its PARTITION BY or any segmentation clause.

CREATE FUNCTION automatically determines the function parameters and return value from data supplied by the factory class. Vertica supports overloading analytic functions. When you call the SQL function, Vertica passes the parameters to the function in the library to process.

Syntax

CREATE [ OR REPLACE ] FUNCTION [[database.]schema.]function AS
   [ LANGUAGE 'language' ] 
   NAME 'factory' 
   LIBRARY library 
   [ FENCED | NOT FENCED ]

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

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'

Language used to develop this function, one of the following:

  • C++ (default)
  • Python
  • Java
  • R
NAME 'factory' Name of the shared library factory class that generates the object to handle function processing.
LIBRARY library

Name of the file that contains the C++ library, Python file, Java Jar file, or R functions file to process this function. This library must already be loaded by CREATE LIBRARY.

FENCED | NOT FENCED

Enables or disables fenced mode for this function. Functions written in Java and R always run in fenced mode.

Default: FENCED

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)

See Also

Developing User-Defined Extensions (UDxs)