Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements

ALTER FUNCTION statement

Modifies a function.

Syntax
  • Change the definition of a function 
    ALTER FUNCTION [ owner.]function-name function-definition
    function-definition : see the CREATE FUNCTION statement
  • Obfuscate a function definition 
    ALTER FUNCTION [ owner.]function-name 
SET HIDDEN
  • Recompile a function 
    ALTER FUNCTION [ owner.]function-name
RECOMPILE
Remarks

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

  • Change the definition of a function

    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.
  • Obfuscate a function definition

    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.

    Note This setting is irreversible. Retain the original function definition outside of the database.
  • Recompile a function

    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.
Note For required parameters that accept variable names, an error is returned if one of the following conditions is true:
  • The variable does not exist
  • The contents of the variable are NULL
  • The variable exceeds the length allowed by the parameter
  • The data type of the variable does not match that required by the parameter
Privileges

You must be the owner of the function or have one of the following privileges:

  • ALTER ANY PROCEDURE system privilege
  • ALTER ANY OBJECT system privilege

To make a function external, you must have the CREATE EXTERNAL REFERENCE system privilege.

Side effects

Automatic commit.

Standards
  • ANSI/ISO SQL Standard

    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.

Example

  1. 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;

  2. The following example creates and then alters a function using a variable in the NAMESPACE clause

    1. The following statements create a variable for a NAMESPACE clause:

      CREATE VARIABLE @ns LONG VARCHAR ;
SET @ns = 'http://wsdl.domain.com/' ;

    2. 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;

    3. 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;
Related Information
Hiding the contents of a procedure, function, trigger, event, or view
CREATE FUNCTION statement
CREATE FUNCTION statement [External call]
CREATE FUNCTION statement [Web service]
ALTER PROCEDURE statement
DROP FUNCTION statement