DROP FUNCTION (Delete function)

Function

DROP FUNCTION deletes a function.

Privileges

Owner of a function

The owner of a function can delete that function.

Users with the DBA privilege

These users can delete functions that are owned by other users.

Format

DROP FUNCTION [authorization-identifier.] routine-identifier
             ([data-type [,data-type]...])
       [WITH PROGRAM]

Operands

authorization-identifier
Specifies the authorization identifier of the owner of the function that is to be deleted.
routine-identifier
Specifies the name of the function that is to be deleted.

Specifies the data type that was specified in the parameter for the function that is to be deleted.

When deleting a function, this option is specified to nullify an SQL object for which any of the following functions, procedures, and triggers are in affect:

If WITH PROGRAM is omitted, and if there is an SQL object for which any of the following functions, procedures, and triggers are in effect, that function cannot be deleted:

Common rules

  1. DROP FUNCTION cannot be executed from within a Java procedure if the deletion results in invalidation of the SQL object being executed.
  2. A function that is defined in an abstract data type cannot be deleted.
  3. If there are multiple functions with the same name, specify the data type parameters so that the functions to be deleted can be identified uniquely.
  4. If there are view tables (including public views) that use a specified function, the function cannot be deleted.

Notes

  1. DROP FUNCTION cannot be specified from an X/Open-compliant UAP running under OLTP.
  2. If an SQL object for which functions, procedures, or triggers are in effect is nullified by specifying WITH PROGRAM, any rows associated with the nullified functions, procedures, or triggers in the SQL_ROUTINE_RESOURCES dictionary table are deleted.
  3. Before executing the SQL object associated with the function, procedure, or trigger that was nullified by specifying WITH PROGRAM, you need to recreate the function, procedure, or trigger by executing ALTER ROUTINE, ALTER PROCEDURE, or ALTER TRIGGER.
  4. To execute the SQL object associated with the trigger that was nullified by specifying WITH PROGRAM, you need to perform either of the following operations; however, if the trigger was using a function having the same owner, routine identifier, and number of parameters as the function that was deleted, you can recreate the trigger SQL object by executing either ALTER TRIGGER or ALTER ROUTINE:
    • Redefine the function, and recreate the trigger SQL object by executing either ALTER TRIGGER or ALTER ROUTINE.
    • Delete the nullified trigger by using DROP TRIGGER, and then redefine the trigger by using CREATE TRIGGER so that the deleted functions are not reused. If triggers satisfying all of the following conditions exist, use DROP TRIGGER to delete them all, and redefine the triggers using CREATE TRIGGER in the order in which they were defined so that there is no change in the sequence of execution of trigger actions:
      Conditions:
      [Figure]The defined trigger is later than the nullified trigger.
      [Figure]The nullified trigger is the same as the defined table.
      [Figure]The nullified trigger is the same as the trigger event (INSERT, UPDATE, or DELETE) (for UPDATE, the nullified trigger is considered to be the same as the trigger event regardless of whether a trigger event column is specified or the contents of the specification).
      [Figure]The nullified trigger has the same trigger action timing (BEFORE or AFTER) as the trigger event.
      [Figure]The nullified trigger has the same trigger action units (units of rows or statements) as the trigger event.
  5. If the function that was being used in the trigger action conditions was deleted by specifying WITH PROGRAM, an error occurs, not only during the execution of the SQL object associated with the nullified trigger, but also during the preprocessing of the SQL statement that induces the trigger.