IBM PureData System for Analytics, Version 7.1
IBM PureData System for Analytics, Version 7.1
Use the CREATE FUNCTION command to create a user-defined function. The CREATE OR REPLACE FUNCTION creates a function or replaces an existing function of the same name with new object files, return value, function behaviors, or logging level.
CREATE [OR REPLACE] FUNCTION function_name(argument_types)
RETURNS return_type LANGUAGE CPP PARAMETER STYLE NPSGENERIC
[FENCED | NOT FENCED] [DETERMINISTIC | NOT DETERMINISTIC]
[RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT]
[MAXIMUM MEMORY mem] [LOGMASK <MASK>]
[NO DEPENDENCIES| DEPENDENCIES deplibs]
[API VERSION [ 1 | 2 ]]
[ENVIRONMENT 'name' = 'value', 'name2' = 'value2']
[TABLE, TABLE FINAL ALLOWED | TABLE ALLOWED | TABLE FINAL ALLOWED]
[PARALLEL ALLOWED | PARALLEL NOT ALLOWED]
[EXTERNAL CLASS NAME 'class_name']
[EXTERNAL HOST OBJECT 'host_object_filename']
[EXTERNAL SPU OBJECT 'SPU_object_filename']| Input | Description |
|---|---|
| function_name | Specifies the name of the function that you want to create.
This is the SQL identifier that is used to invoke the function in
a SQL expression. The name must meet the naming criteria for keywords
and identifiers, which are described in the IBM Netezza Database User’s Guide. If the function exists, you cannot change the name by using the CREATE OR REPLACE command. For systems that support multiple schemas, you can specify a name in the format schema.function to create a function in a different schema of the current database. You cannot create a function in a different database. |
| argument_types | Specifies a list of fully specified function argument data
types. All IBM® Netezza® data
types are supported. Strings must include either a size or ANY for
generic sizes. NUMERIC types must include precision and scale or ANY
for generic sizes. You can also specify the VARARGS value to create a variable argument aggregate where users can input up to 64 values of any supported data type. VARARGS is a mutually exclusive value; you cannot specify any other arguments in the list. You cannot change the argument list or sizes. You can remove VARARGS from the argument list, or add it to an otherwise empty argument list. You cannot change the argument list by using this command. If the function exists, you cannot change the argument type list by using the CREATE OR REPLACE command. You can also use CREATE OR REPLACE to alter some aspects of a UDFs argument types; for example, you can change the size of a string or the precision and scale of a numeric value. |
| RETURNS return_type | The return_type value is one fully specified argument and type. It follows the same rules as argument_types. If the API VERSION is 2, the return_type can also be TABLE(name type, name2 type2, ...) or TABLE(ANY) to specify a table function. |
| LANGUAGE | Specifies the programming language that is used for the function. The default and only supported value currently is CPP (C++). |
| PARAMETER STYLE | The default and only supported value currently is NPSGENERIC. |
| FENCED NOT FENCED |
Specifies whether the function is executed in a separate process in protected address space (fenced mode). To create an unfenced function, you must have the Unfence admin privilege. |
| [DETERMINISTIC | NOT DETERMINISTIC] | DETERMINISTIC indicates that the UDF is a pure function, one
which always returns the same value given the same argument values
and which has no side effects. The system might consider multiple
instances of a deterministic UDF having identical argument lists to
be candidates for common subexpression elimination (CSE). The default
is DETERMINISTIC. If a function is DETERMINISTIC, it is called
once at statement preparation time instead of once per row if either
of the following is true:
An argument is constant if it is a SQL literal, or the result of a UDF or built-in that is evaluated once at statement preparation time instead of once per row. |
| [RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT] | RETURNS NULL ON NULL INPUT indicates that the function always
returns NULL whenever any of its arguments are NULL. If you specify
this parameter, the function is not executed when there are NULL arguments;
instead a NULL result is assumed automatically. CALLED ON NULL INPUT (the default) indicates that the function is called normally when some of its arguments are NULL. It is then the responsibility of the function creator to check for NULL values if necessary and respond appropriately. For more information about query optimization effects for this setting, see Netezza query optimization and UDX calls. |
| MAXIMUM MEMORY | Specifies an indication of the potential memory use of the function. The size value can be an empty value or a value in the form of a number and the letters b (bytes), k (kilobytes), m (megabytes), or g (gigabytes). For example, valid values can be '0', '1k', '100k', '1g', or '10m'. The default is 0. |
| LOGMASK mask | Specifies the logging control level for the function. Valid values are NONE, DEBUG, and TRACE, or a comma-separated combination of DEBUG and TRACE. |
| DEPENDENCIES deplibs | Specifies an optional list of user-defined shared library dependencies for the UDX. You can specify one or a comma-separated list of library names. |
| API VERSION [1 | 2] | Specifies the version of the UDX interface that is used by the aggregate. The API VERSION must match the compiled version of the object files for the host and SPU. The default is 1. If you include version 2 compiled objects, you must specify API VERSION 2. |
| ENVIRONMENT | Specifies a name-value pair that is available to the function
when executing. You can specify several comma-separated name-value
pairs. To alter an existing set of one or more environment pairs, you must specify all the environment settings; the alter command replaces the current list with the list specified in the ALTER command. |
| TABLE, TABLE FINAL ALLOWED TABLE ALLOWED TABLE FINAL ALLOWED |
Specifies the options that control how the user-defined table
function can be invoked.
FINAL means that the table function is invoked after all of the input rows are processed, thus allowing for it to output more rows. |
| PARALLEL ALLOWED | Specifies that a user-defined table function can be invoked on either the host or the SPU, at the discretion of the optimizer. |
| PARALLEL NOT ALLOWED | Specifies that the table function is always invoked on the host or one selected SPU at the discretion of the optimizer. |
| EXTERNAL CLASS NAME 'class_name' | Specifies the name of the C++ class that implements the function. The class must derive from the Udf base class and must implement a static method that instantiates an instance of the class. |
| EXTERNAL HOST OBJECT 'host_object_filename' | Specifies the path name to the compiled object for host execution. |
| EXTERNAL SPU OBJECT 'SPU_object_filename' | Specifies the path name for the compiled object file of the Linux SPU. Specify the spu10 compiled object for Rev10 SPUs on IBM Netezza 1000 and Netezza 1000 models. |
| Output | Description |
|---|---|
| CREATE FUNCTION | The message that the system returns if the command is successful. |
| ERROR: User 'username' is not allowed to create/drop functions. | The system returns this message if your user account does not have Create Function privilege. |
| ERROR: Synonym 'name' already exists | The system returns this message if a synonym exists with the name that you specified for the function. |
| ERROR: function name already exists with the same signature | This error is returned when you issue a CREATE FUNCTION command and a function with the same name and argument type list exists in the database. Use CREATE OR REPLACE FUNCTION instead. |
| ERROR: function name already exists with the same signature | The system returns this message if a function exists with the name that you specified for the function. |
| NOTICE: FunctionCreate: existing UDX name(argument_types) differs in size of string/numeric arguments | This message indicates that a UDX exists with the name but has different sizes that are specified for string or numeric arguments. If you did not intend to change the function signature, check the signature and ensure that it is correct. |
| ERROR: lookupLibrary: library libname does not exist | The message that the system returns if it cannot find the user-defined shared library that is specified as a dependency. |
| ERROR: ProcedureCreate: Can't use version 2 features without specifying API VERSION 2 for udx_name | The message indicates that you specified version 2 options for the SQL command, but you did not also specify API VERSION 2 in the SQL command. |
| ERROR: Version mismatch for function udx_name. Specified version 2, but provided version 1 object file | The compiled object files use API version 1 support, but the SQL command uses version 2 functionality. You must either create version 2 compiled objects, or remove options in the command that specify version 2 features. |
| ERROR: Version mismatch for function udx_name. Specified version 1, but provided version 2 object file | The compiled object files use API version 2 support, but the SQL command uses version 1 functionality. You must either specify version 1 compiled objects, or change the ALTER command to specify version 2 syntax. |
| ERROR: Environment names can't be empty | The name value of an environment setting cannot be an empty string. |
| ERROR: type 'type' is not yet defined | The specified return type is not a known Netezza data type. |
When you create a function, the function signature (that is, its name and argument type list) must be unique within its database. No other user-defined function or aggregate can have the same name and argument type list in the same database.
You cannot change the function name or the argument type list by using the CREATE OR REPLACE command. You can change some aspects of the argument types; for example, you can change the size of a string or the precision and scale of a numeric value. To change a function name, argument type list, or both, you must drop the function and then create a function with the new name, argument type list, or both.
You cannot replace a user-defined function that is in use in an active query. After the active query transaction completes, the Netezza system processes the CREATE OR REPLACE FUNCTION command to update the function.
You must be the database admin user, own the current database, or own the current schema on systems that support multiple schemas. Other users must have Create Function privilege to use the CREATE FUNCTION command. Also, if you use CREATE OR REPLACE FUNCTION to change a UDF, you must have Create Function and Alter privilege for the UDF to change it. To create an unfenced function, you must have the Unfence admin privilege.
Use the CREATE FUNCTION command to create and become the owner of a new user-defined function. You must create the function C++ files and compile them by using nzudxcompile before you can use this command to register the function with the Netezza system. The function is defined as an object in the current database.
MYDB.SCHEMA(MYUSER)=> CREATE FUNCTION CustomerName(varchar(64000))
RETURNS int4 LANGUAGE CPP PARAMETER STYLE npsgeneric
EXTERNAL CLASS NAME 'CCustomerName'
EXTERNAL HOST OBJECT '/home/nz/udx_files/customername.o_x86'
EXTERNAL SPU OBJECT '/home/nz/udx_files/customername.o_spu10'