Use this statement to modify a function. You must include the entire new function in the ALTER FUNCTION statement.

Syntax 1

ALTER FUNCTION [ owner.]function-name function-definition

function-definition : CREATE FUNCTION syntax

Syntax 2

ALTER FUNCTION [ owner.]function-name 
SET HIDDEN

Syntax 3

ALTER FUNCTION [ owner.]function-name
RECOMPILE

Remarks

Syntax 1   The ALTER FUNCTION statement is identical in syntax to the CREATE FUNCTION statement except for the first word. Either version of the CREATE FUNCTION statement can be altered.

Existing permissions on the function are maintained, and do not have to be reassigned. If a DROP FUNCTION and CREATE FUNCTION were carried out, execute permissions would have to be reassigned.

Syntax 2   Use SET HIDDEN to obfuscates the definition of the associated function and cause it to become unreadable. The function can be unloaded and reloaded into other databases.

If SET HIDDEN is used, debugging using the debugger will not show the function definition, nor will it be available through procedure profiling.

Syntax 3   Use the RECOMPILE syntax to recompile a user-defined SQL function. When you recompile a function, the definition stored in the catalog is re-parsed and the syntax is verified. The preserved source for a function is not changed by recompiling. When you recompile a function, the definitions obfuscated by the SET HIDDEN clause remain obfuscated and unreadable.

Permissions

Must be the owner of the function or have DBA authority.

Side effects

Automatic commit.

See also

• CREATE FUNCTION statement (web services)
• ALTER PROCEDURE statement
• DROP FUNCTION statement
• Hiding the contents of procedures, functions, triggers and views

Standards and compatibility

Example

In this example, MyFunction is created and altered. The SET HIDDEN clause obfuscates the function definition and makes it unreadable.

CREATE FUNCTION MyFunction(
   firstname CHAR(30),
   lastname CHAR(30) )
RETURNS CHAR(61)
BEGIN
   DECLARE name CHAR(61);
   SET name = firstname || ' ' || lastname;
   RETURN (name);
ALTER FUNCTION MyFunction SET HIDDEN;
END;