CREATE TRANSFORM FUNCTION

Adds a user-defined transform function (UDTF) 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 the input table to the transform function in the library to process.

Note: This topic describes how to create a UDTF. To create a user-defined function (UDF), see CREATE FUNCTION (UDF). To create a SQL function, see CREATE FUNCTION (SQL).

Syntax

CREATE [OR REPLACE] TRANSFORM FUNCTION 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 TRANSFORM 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.
`function‑name`	The name to assign to the UDTF. This is the name you use in your SQL statements to call the function.
`LANGUAGE 'language'`	The language used to develop this function, one of the following: `C++` `Java` `R` `PYTHON` Default value: C++
`NAME 'factory'`	The name of the factory class or R factory function 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 object 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. Functions written in R always run in fenced mode. Default Value: `FENCED`

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.

UDTF Query Restrictions

A query that includes a UDTF cannot contain:

Any statements other than the SELECT statement containing the call to the UDTF and a PARTITION BY expression
Any other analytic function
A call to another UDTF
A TIMESERIES clause
A pattern matching clause
A gap filling and interpolation clause

Notes

The parameters and return values for the function are automatically determined by the CREATE TRANSFORM FUNCTION statement, based on data supplied by the factory class.
You can assign multiple functions the same name if they have different parameters. When you call a multiply-defined function, Vertica selects the UDTF 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 UDTFs by querying the system table V_CATALOG.USER_FUNCTIONS. You can only see functions for which you have EXECUTE privileges.

Examples

This example shows how to add a UDTF to the catalog.

=> CREATE TRANSFORM FUNCTION transFunct AS LANGUAGE 'C++' NAME 'myFactory' LIBRARY myFunction;