DROP FUNCTION statement

Use the DROP FUNCTION statement to remove a user-defined function from the database. This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

^{(explicit id drro002)
drro002} DROP FUNCTION [IF EXISTS] { [ owner . ] function [ ( parameter_type ) ] | SPECIFIC FUNCTION [IF EXISTS] <Specific Name>^[] }


Element	Description	Restrictions	Syntax
`function`	Name of the user-defined function to be dropped	Must exist (that is, be registered) in the database. If the name does not uniquely identify a function, you must enter one or more appropriate values for `parameter_type`.	Identifier
`parameter_type`	Data type of the parameter	The data type (or list of data types) must be the same data types (and specified in the same order) as in the CREATE FUNCTION statement that registered the function	Data Type

Usage

Dropping a user-defined function removes the text and executable versions of the function from the database. (Make sure to keep a copy of the function text somewhere outside the database, in case you need to re-create a function after it is dropped.)

If you do not know whether a UDR is a function or a procedure, you can drop it by using the DROP ROUTINE statement.

To use the DROP FUNCTION statement, you must be the owner of the user-defined function (and hold the Resource privilege on the database) or have the DBA privilege. You must also hold the Usage privilege on the programming language in which the UDR is written. To drop an external user-defined function, see also Dropping an External Routine.

You cannot use the DROP ROUTINE, DROP FUNCTION, or DROP PROCEDURE statement to drop a protected routine. For more information about protected routines, see the description of the sysprocedures system catalog table in the Informix® Guide to SQL: Reference.

You cannot drop an SPL function from within the same SPL function.

Informix® can resolve a function by its specific name, if the function definition declared a specific name. If you use the specific name in this statement, you must also use the keyword SPECIFIC, as in the following example:

DROP SPECIFIC FUNCTION compare_point;

Otherwise, if the function name is not unique within the database, you must specify enough parameter_type information to disambiguate the name. If you use parameter data types to identify a user-defined function, they follow the function name, as in the following example:

DROP FUNCTION compare (int, int);

But the database server returns an error if it cannot resolve an ambiguous function name whose signature differs from that of another function only in an unnamed ROW-type parameter. (This error cannot be anticipated by the database server when the ambiguous function is defined.)

If you include the optional IF EXISTS keywords, the database server takes no action (rather than issue an error) if the database server cannot find in the current database any function that matches what your DROP FUNCTION statement specifies.

Determine whether a function exists

Before you attempt to drop a user-defined function, you can check for its existence in the database by querying the system catalog. In the following example, the SELECT statement retrieves from the sysprodures table any routines whose identifier is MyFunction:

SELECT * FROM sysprocedures WHERE procname = MyFunction;

If this query returns a single row, then a UDR called MyFunction is registered in the current database.

If this query returns no rows, you do not need to issue the DROP FUNCTION statement, but you might wish to verify that the WHERE clause specified the correct name, and that you are connected to the correct database.

If the query returns more than one row, then the routine name MyFunction is overloaded in the current database, and you need to examine the attributes of the MyFunction routines to determine which of them, if any, need to be unregistered by the DROP FUNCTION statement.

Examples

Most functions can be dropped using SQL statements similar to the following:

DROP FUNCTION best_month;

If you have more than one function with the same name, however, by using function overloading, the DROP FUNCTION statement must either specify the specific name of the function (if it has one), or the parameter list to uniquely identify it. For example, the superstores_demo database has two last_contact functions that were created with the following arguments:

CREATE FUNCTION last_contact(cust_name name_t) ...

and

CREATE FUNCTION last_contact(c_num INT) ...

To drop the second of these functions, use the following:

DROP FUNCTION last_contact(INT);

If the above functions had been created with the specific names last_cname_contact and last_cnum_contact, then to drop the second of these, issue the following statement:

DROP SPECIFIC FUNCTION last_cnum_contact;

Details of existing functions can be found in the sysprocedures system catalog table using SQL queries like the following:

SELECT procname, specificname, paramtypes
    	    FROM sysprocedures ;

¹ See Specific Name