ALTER FUNCTION

Advantage SQL Engine

Modifies the name or definition of a function. The existing permissions on the function are maintained.

Syntax

ALTER FUNCTION [package_name.]function_name [new_function_name]

( [parameter, ] )

[RETURNS data-type | NULL]

[DESCRIPTION string]

BEGIN sql-script END

 

package_name ::= identifier

function_name ::= identifier

new_function_name ::= function_name | package_name.function_name

parameter ::= identifier data-type

sql-script ::= declare_statements;statement_block | statement_block

 

Remark

The ALTER FUNCTION statement has the identical syntax as the CREATE FUNCTION statement except for the first word and the optional new_function_name.

It allows the function to be modified without having to re-grant the permissions to users. If the function is dropped and recreated, then the permissions must be re-granted to the user who had permission on the function.

The optional new_function_name may be used to change the name of the function. A package_name may be specified as part of the new_function_name but it must match the existing package name. ALTER FUNCTION cannot be used to change the package ownership of the function.

To alter a function that is not owned by any package, the user must have the ALTER permission over the function. To alter a function that is owned by a package, the user must have the ALTER permission over the package.

Example

  1. ALTER FUNCTION without changing the function name

ALTER FUNCTION FullName( lastname char(15), firstname char(15))

RETURNS char(31)

DESCRIPTION 'This function constructs a conventional name with blanks trimmed'

BEGIN

RETURN Trim(firstname) + ' ' + Trim(lastname);

END;

 

  1. Changing the name of a function

// Correct the spelling of the function name

ALTER FUNCTION local.squar square( i Integer )
RETURNS integer
BEGIN
return i * i;
end;

See Also

CREATE FUNCTION

CREATE PACKAGE

DROP FUNCTION

DROP PACKAGE

Effective Permissions