ALTER FUNCTION statement

Modifies a function. You must include the entire new function in the ALTER FUNCTION statement.

Syntax 1 - Change the definition of a function

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

function-definition : CREATE FUNCTION syntax

Syntax 2 - Obfuscate a function definition

ALTER FUNCTION [ owner.]function-name 
SET HIDDEN

Syntax 3 - Recompile a function

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.
With ALTER FUNCTION, existing permissions on the function remain unmodified. Conversely, if you execute DROP FUNCTION followed by CREATE FUNCTION, execute permissions are reassigned.
Syntax 2 Use SET HIDDEN to obfuscate 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 does not show the function definition, nor is it available through procedure profiling.

Note

This setting is irreversible. It is strongly advised that you retain the original function definition outside of the database.
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

Side effects

Standards and compatibility

SQL/2008 Vendor extension. ALTER FUNCTION is optional SQL language feature F381 of the SQL/2008 standard. However, in the SQL standard, ALTER FUNCTION cannot be used to re-define a PSM function definition. SQL/2008 does not include support for SET HIDDEN or RECOMPILE.

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;

Discuss this page in DocCommentXchange.

ALTER FUNCTION statement

Note