CREATE FILTER

Adds a user-defined load filter function to the catalog. CREATE FILTER automatically determines the function parameters and return value from data supplied by the factory class. Vertica supports overloading load filter functions. When you call the SQL function, Vertica passes the input table to the function in the library to process.

Installing an untrusted UDL function can compromise the security of the server. UDx's can contain arbitrary code. In particular, UD source functions can read data from any arbitrary location. It is up to the developer of the function to enforce proper security limitations. Superusers must not grant access to UDx's to untrusted users.

Syntax

CREATE [ OR REPLACE ] FILTER [[database.]schema.]function‑name 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‑name

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'

The language used to develop this function, one of the following:

  • C++ (default)
  • Java
  • Python
NAME 'factory'

Name of the shared library factory class that generates the object to handle function processing. This is the same name used by the RegisterFactory class.
LIBRARY library

Name of the shared library that contains the object to process this function. This library must already be loaded by CREATE LIBRARY.
FENCED | NOT FENCED

Enables or disables fenced mode for this function.

Default: FENCED

Privileges

Superuser

Example

The following example demonstrates loading a library named iConverterLib, then defining a function named Iconverter that is mapped to the iConverterFactory factory class in the library:

=> CREATE LIBRARY iConverterLib as '/opt/vertica/sdk/examples/build/IconverterLib.so';
CREATE LIBRARY
=> CREATE FILTER Iconverter AS LANGUAGE 'C++' NAME 'IconverterFactory' LIBRARY IconverterLib;
CREATE FILTER FUNCTION
=> \x
Expanded display is on.
=> SELECT * FROM user_functions;
-[ RECORD 1 ]----------+--------------------
schema_name            | public
function_name          | Iconverter
procedure_type         | User Defined Filter
function_return_type   | 
function_argument_type | 
function_definition    | 
volatility             | 
is_strict              | f
is_fenced              | f
comment                |

See Also