ALTER FUNCTION

Use the ALTER FUNCTION command to change the function object files, return value, memory usage options, or logging level. You can also use this command to change the owner of the UDF.

You cannot change the function name or argument type list by using this command. 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.

Synopsis

The ALTER FUNCTION command has the following syntax:
ALTER FUNCTION function_name(argument_types)
[RETURNS return_type] [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 ] ]
[NO ENVIRONMENT | 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']
ALTER FUNCTION function_name(argument_types) OWNER TO name

Inputs

The ALTER FUNCTION command takes the following inputs:
Table 1. ALTER FUNCTION inputs
Input Description
function_name Specifies the name of the function that you want to change. You cannot change the name of the function. The function must be in the current database.

For systems that support multiple schemas, you can specify a name in the format schema.function to change a UDF in a different schema of the current database. You cannot change a function in a different database.
argument_types A list of fully specified arguments and types to uniquely identify the function. 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.

All Netezza Performance Server 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.
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.
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 that have identical argument lists to be candidates for common subexpression elimination (CSE).
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:
  • It RETURNS NULL ON NULL INPUT and one or more of its arguments are NULL (the literal NULL).
  • It has all constant arguments.

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. For more information about query optimization effects for this setting, see Netezza Performance Server query optimization and UDX calls.
[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 is to check for NULL values if necessary and respond appropriately. For more information about query optimization effects for this setting, see Netezza Performance Server 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.
NO DEPENDENCIES Specifies that there are no dependencies for the UDX, which is the default if DEPENDENCIES deplibs is omitted. You can use this option to clear any previous dependencies declared for the UDX.
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.
NO ENVIRONMENT 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. To clear the environment list, specify NO ENVIRONMENT.
TABLE, TABLE FINAL ALLOWED

TABLE ALLOWED

TABLE FINAL ALLOWED

 Specifies the options that control how the user-defined table function can be invoked.
  • The TABLE, TABLE FINAL ALLOWED option specifies that you can invoke the table function by using TABLE(func()), TABLE WITH FINAL(func()), or either case.
  • You can also specify either TABLE ALLOWED or TABLE FINAL ALLOWED to allow the user-defined table function to be invoked by using one of these forms.

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® 100 and Netezza 1000 models.

Outputs

The ALTER FUNCTION command has the following outputs:
Table 2. ALTER FUNCTION outputs
Output Description
ALTER FUNCTION The message that is returned if the command is successful.
Error: AlterFunction: existing UDX name(argument_types) differs in size of string/numeric arguments This error indicates that a UDX exists with the name but has different sizes that are specified for string or numeric arguments. To alter the function, make sure that you specify the exact argument type list with correct sizes.
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: 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 ALTER 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 Performance Server data type.

Description

You cannot alter a user-defined function that is in use in an active query. After the active query transaction completes, the Netezza Performance Server system processes the ALTER FUNCTION command to update the function.

Privileges required

To alter a UDF, you must meet one of the following criteria:
  • You must have the Alter privilege on the FUNCTION object.
  • You must have the Alter privilege on the specific UDF object.
  • You must own the UDF.
  • You must be the database admin user, own the current database, or own the current schema on systems that support multiple schemas.
  • To alter a function to be unfenced, you must have the Unfence admin privilege.
Note: When you issue an ALTER FUNCTION command and specify new object files, the database processes the HOST OBJECT and the SPU OBJECT files as the user nz. The user nz must have read access to the object files and read and execute access to every directory in the path from the root to the object file.

Common tasks

You can use the ALTER FUNCTION command to change the owner of a function. Make sure that you specify the full signature of the function (name and argument type list) as follows:
ALTER FUNCTION function_name(argument_types) OWNER TO name

Usage

The following provides sample usage.
  • To alter a sample function named myfunc(char(12)) to set the MAXIMUM MEMORY option to 100k, enter: 
    MYDB.SCHEMA(MYUSER)=> ALTER FUNCTION myfunc(char(12)) MAXIMUM MEMORY
'100k';