DROP FUNCTION

Use the DROP FUNCTION command to remove an existing user-defined function from a database. When you drop a function, the function object files are also removed from the user code object repository.

Synopsis

Syntax for dropping a user-defined function:
DROP FUNCTION function_name(argument_types)

Inputs

The DROP FUNCTION command takes the following inputs:
Table 1. DROP FUNCTION inputs
Input Description
function_name Specifies the name of an existing user-defined function. For systems that support multiple schemas, you can specify a name in the format schema.function to drop a function in a different schema of the current database. You cannot drop a function in a different database.
argument_types Specifies a list of fully specified argument data types to uniquely identify the function. 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. You can also specify the VARARGS value to drop a variable argument function.

Outputs

The DROP FUNCTION command has the following outputs:
Table 2. DROP FUNCTION outputs
Output Description
DROP FUNCTION The message that the system returns if the command is successful.
ERROR: Name: No such function The message that the system returns if the specified function does not exist in the current database.
Error: DropFunction: 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 drop the function, make sure that you specify the exact argument type list with correct sizes.
ERROR: Can't delete function name - object depends on it The message that the system returns if a UDF is referenced in a table or a view. You cannot drop the UDF until the dependency is resolved.

Description

You cannot drop 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 DROP FUNCTION command to drop the function. The function must be defined in the current database.

You cannot drop a UDF that is referenced by an existing table or view. For more information about resolving dependencies to UDFs that you want to drop, seeDependency checks before you drop UDXs.

Privileges required

To drop a UDF, you must meet one of the following criteria:
  • You must have the Drop privilege on the FUNCTION object.
  • You must have the Drop 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 supports multiple schemas.

Common tasks

Use the DROP FUNCTION command to drop an existing function from a database.

Usage

The following is sample usage.
  • To drop a sample function myfunc(char(12)), enter: 
    MYDB.SCHEMA(MYUSER)=> DROP FUNCTION myfunc(char(12));