The FUNCTION statement creates a temporary SQL user-defined function (UDF) within a single script. Use it when you need reusable SQL logic in an ad hoc query or script without registering a permanent function in the MaxCompute catalog.
For a persistent UDF that all queries in the project can call, use CREATE SQL FUNCTION instead.
Limitations
-
Script mode required. Define UDFs in SQL script mode. Defining a UDF in common SQL editing mode causes an error.
-
Scope limited to the defining script. A temporary SQL UDF is not stored in the MaxCompute metadata system. It does not appear in the function list and cannot be called from other query environments.
-
Input parameter data types must be MaxCompute-supported types. See MaxCompute V2.0 data type edition for the full list. When calling the UDF, argument types must match the types declared in the definition exactly.
-
Permissions. Creating, querying, calling, or dropping a SQL UDF requires function-level permissions on your Alibaba Cloud account. See MaxCompute permissions.
Syntax
function <function_name>(@<parameter_in1> <datatype>[, @<parameter_in2> <datatype>...])
[returns @<parameter_out> <datatype>]
as [begin]
<function_expression>
[end];Parameters
| Parameter | Required | Description |
|---|---|---|
function_name |
Yes | Name of the UDF. Must be unique within the project and cannot match a built-in function name. Each name can only be registered once. Run LIST FUNCTIONS to check for name conflicts. |
parameter_in |
Yes | Input parameters. Accepts standard data types and function types, including anonymous functions. See function-type input parameter example and anonymous function example. |
datatype |
Yes | Data type of each input parameter. See MaxCompute V2.0 data type edition. |
returns |
No | Return value declaration. The return value is a variable. If omitted, the value of function_name is returned by default. |
parameter_out |
Yes | Name of the output variable. |
function_expression |
Yes | The SQL expression that implements the function logic. |
Examples
Define a function that adds 1 to an integer
function my_add(@a BIGINT) as @a + 1;Related statements
-
CREATE SQL FUNCTION: creates a permanent SQL UDF stored in the MaxCompute metadata system, callable by all queries in the project.
-
DESC FUNCTION: returns details about a permanently stored UDF — name, owner, creation time, class name, and resource list. Temporary SQL UDFs are not queryable with this statement.
-
DROP FUNCTION: drops an existing UDF from a MaxCompute project.
-
SELECT FUNCTION: calls a SQL UDF. A temporary SQL UDF is callable only in the script where it is defined.
-
LIST FUNCTIONS: lists all functions registered in a project.