Use CREATE SQL FUNCTION to create a permanent SQL UDF and store it in the metadata system - MaxCompute

SQL user-defined functions (UDFs) let you encapsulate reusable business logic directly in SQL, without writing Java or Python. Unlike Java/Python UDFs, SQL UDFs support parameters of the function type, including anonymous functions. The CREATE SQL FUNCTION statement creates a permanent SQL UDF stored in the MaxCompute metadata system.

To create a temporary SQL UDF that is not persisted to the metadata system, use the FUNCTION statement instead.

Prerequisites

Before you begin, ensure that you have:

Function-level permissions on your Alibaba Cloud account (create, query, call, and drop). For details, see MaxCompute permissions
SQL script mode enabled in your SQL editor. Running CREATE SQL FUNCTION in common SQL editing mode causes an error. For details, see SQL in script mode

Syntax

create sql function <function_name>(@<parameter_in1> <datatype>[, @<parameter_in2> <datatype>...])
[returns @<parameter_out> <datatype>]
as [begin]
<function_expression>
[end];

For a single-expression UDF, omit begin and end and write the expression directly after as. For multi-statement logic, wrap the statements in begin...end.

Parameters

Parameter	Required	Description
`function_name`	Yes	The name of the SQL UDF. Must be unique within the project and cannot match a built-in function name. Each unique function name can only be registered once. To check existing function names, run LIST FUNCTIONS.
`parameter_in`	Yes	The input parameters. Parameters can be of the function type, including anonymous functions. For examples, see function-type input parameters. For anonymous function examples, see anonymous function input parameters.
`datatype`	Yes	The data type of each input parameter. Must be a data type supported by MaxCompute. See MaxCompute V2.0 data type edition. When calling the UDF, input data types must exactly match those defined here.
`returns`	No	The return value variable. If omitted, the value of `function_name` is returned by default.
`parameter_out`	Yes (if `returns` is specified)	The output parameter name for the return value.
`function_expression`	Yes	The implementation logic of the UDF. Can be a built-in operator, built-in function, or another UDF.

Examples

Simple UDF

For single-expression logic, write the expression directly after as:

-- Increment a BIGINT value by 1
create sql function my_add(@a BIGINT) as @a + 1;

The expression after as is the function body. It can reference built-in operators, built-in functions, or other UDFs.

Multi-statement UDF

For logic that requires intermediate variables, use begin...end to wrap multiple statements:

-- Sum three BIGINT values using an intermediate variable
create sql function my_sum(@a BIGINT, @b BIGINT, @c BIGINT) returns @my_sum BIGINT
as begin
    @temp := @a + @b;
    @my_sum := @temp + @c;
end;

returns @my_sum BIGINT explicitly names the return variable. If you omit returns, the function returns the value of function_name by default.

Function-type input parameters

SQL UDFs accept parameters of the function type, allowing you to pass built-in functions or other UDFs as arguments. For detailed examples, see:

What's next

After creating a SQL UDF, use these related statements to manage and call it:

Statement	Description
SELECT FUNCTION	Call a SQL UDF.
DESC FUNCTION	View UDF details: name, owner, creation time, class name, and resource list.
LIST FUNCTIONS	List all UDFs in a MaxCompute project.
DROP FUNCTION	Drop an existing UDF from a MaxCompute project.
FUNCTION	Create a temporary SQL UDF (not persisted to the metadata system).