Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
Modifies a function.
ALTER FUNCTION [ owner.]function-name function-definition
function-definition : see the CREATE FUNCTION statement
ALTER FUNCTION [ owner.]function-name SET HIDDEN
ALTER FUNCTION [ owner.]function-name RECOMPILE
You must include the entire new function in the ALTER FUNCTION statement.
The ALTER FUNCTION statement is identical in syntax to the CREATE FUNCTION statement except for the first word.
With ALTER FUNCTION, existing privileges on the function remain unmodified. Conversely, if you execute DROP FUNCTION followed by CREATE FUNCTION, execute privileges are reassigned.
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, then debugging using the debugger does not show the function definition, nor is it available through procedure profiling.
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.
You must be the owner of the function or have one of the following privileges:
To make a function external, you must have the CREATE EXTERNAL REFERENCE system privilege.
ALTER FUNCTION is optional ANSI/ISO SQL Language Feature F381. However, in the SQL standard, ALTER FUNCTION cannot be used to re-define a SQL Persistent Stored Module (PSM) function definition. The ANSI/ISO SQL Standard does not include support for SET HIDDEN or RECOMPILE.
In this example, MyFunction is created and altered. The SET HIDDEN clause obfuscates the function definition and makes it unreadable. To run this example, you must also have the CREATE PROCEDURE system privilege, since a function is being created before being altered.
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;
The following example creates and then alters a function using a variable in the NAMESPACE clause
The following statements create a variable for a NAMESPACE clause:
CREATE VARIABLE @ns LONG VARCHAR ; SET @ns = 'http://wsdl.domain.com/' ;
The following statement creates a function named FtoC that uses a variable in the NAMESPACE clause:
CREATE FUNCTION FtoC ( IN temperature LONG VARCHAR ) RETURNS LONG VARCHAR URL 'http://localhost:8082/FtoCService' TYPE 'SOAP:DOC' NAMESPACE @ns;
The following statement alters the function FtoC so that it accepts and returns a FLOAT data type:
ALTER FUNCTION FtoC ( IN temperature FLOAT ) RETURNS FLOAT URL 'http://localhost:8082/FtoCService' NAMESPACE @ns;