Informix Guide to SQL: Syntax SQL Statements

Use the DROP ROUTINE statement to remove a user-defined routine (UDR) from the database.

Dropping a UDR removes the text and executable versions of the UDR from the database.

This statement is useful when you do not know whether a UDR is a user-defined function or a user-defined procedure. When you use this statement, the database server drops the appropriate user-defined function or user-defined procedure.

To use the DROP ROUTINE statement, you must be the owner of the UDR or have the DBA privilege.

When you use this statement, the type of UDR cannot be ambiguous. The UDR that you specify must refer to either a user-defined function or a user-defined procedure. If either of the following conditions exist, the database server returns an error:

• The name (and parameters) that you specify apply to both a user-defined procedure and a user-defined function
• The specific name that you specify applies to both a user-defined function and a user-defined procedure

If you use parameter data types to identify a UDR, they follow the UDR name, as in the following example:

DROP ROUTINE compare(int, int) 

If you use the specific name for the UDR, you must use the keyword SPECIFIC, as in the following example:

DROP SPECIFIC ROUTINE compare_point 

Because you cannot change the text of an SPL routine, you must drop it and then re-create it. Make sure that you have a copy of the SPL function text somewhere outside the database, in case you want to re-create it after it is dropped.

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

Related statements: CREATE FUNCTION, CREATE PROCEDURE, DROP FUNCTION, DROP PROCEDURE, EXECUTE FUNCTION, and EXECUTE PROCEDURE