Categories:

DDL for User-Defined Functions, External Functions, and Stored Procedures

ALTER FUNCTION

Modifies the properties for an existing UDF (user-defined function). Currently, the only supported operations are:

  • Renaming a UDF.

  • Converting to (or reverting from) a secure UDF.

  • Adding, overwriting, removing a comment for a UDF.

To make any other changes to a UDF, you must drop the function (using DROP FUNCTION) and then recreate it.

See also:

CREATE FUNCTION , SHOW USER FUNCTIONS

Syntax

ALTER FUNCTION [ IF EXISTS ] <name> ( [ <arg_data_type> , ... ] ) RENAME TO <new_name>

ALTER FUNCTION [ IF EXISTS ] <name> ( [ <arg_data_type> , ... ] ) SET { SECURE | COMMENT = '<string_literal>' }

ALTER FUNCTION [ IF EXISTS ] <name> ( [ <arg_data_type> , ... ] ) UNSET { SECURE | COMMENT }

Parameters

name

Specifies the identifier for the UDF to alter. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

arg_data_type [ , ... ]

Specifies the data type of the argument(s), if any, for the UDF. The argument types are necessary because UDFs support name overloading (i.e. two UDFs in the same schema can have the same name) and the argument types are used to identify the UDF you wish to alter.

RENAME TO new_name

Specifies the new identifier for the UDF; the combination of the identifier and existing argument data types must be unique for the schema.

For more details, see Identifier Requirements.

Note

When specifying the new name for the UDF, do not specify argument data types or parentheses; specify only the new name.

When an object is renamed, other objects that reference it must be updated with the new name.

SET ...

SECURE

Specifies whether a function is secure. For more details, see Secure UDFs.

COMMENT = 'string_literal'

Adds a comment or overwrites the existing comment for the UDF. The value you specify is displayed in the DESCRIPTION column in the SHOW FUNCTIONS and SHOW USER FUNCTIONS output.

UNSET ...

Specifies the properties to unset for the UDF, which resets them to the defaults.

Usage Notes

  • Moving a UDF to a managed access schema (using the ALTER FUNCTION … RENAME TO syntax) is prohibited unless the UDF owner (i.e. the role that has the OWNERSHIP privilege on the UDF) also owns the target schema.

  • If using a UDF in a masking policy, ensure the data type of the column, UDF, and masking policy match. For more information, see User-defined Functions in a Masking Policy.

Examples

Rename UDF function1 to function2:

ALTER FUNCTION IF EXISTS function1(number) RENAME TO function2;

Convert regular UDF function2 to a secure UDF:

ALTER FUNCTION IF EXISTS function2(number) SET SECURE;