MaxCompute:FUNCTION

Precautions

• When you use SQL statements to define a UDF, make sure that you perform the operation in SQL script mode. If you perform the operation in common SQL editing mode, an error may occur.

  Note

  For more information about the SQL script mode of MaxCompute, see SQL in script mode.

• MaxCompute allows you to execute the FUNCTION statement to create an SQL UDF. The UDFs that are created by using this statement are temporary SQL UDFs. After a UDF is created by using the FUNCTION statement, the UDF is not stored in the metadata system of MaxCompute. You cannot query the UDF in the function list of MaxCompute. The SQL UDF can be called only in the script where the UDF is defined and cannot be called in other query environments.

  Note

  For more information about how to create a permanent SQL UDF, see CREATE SQL FUNCTION.

• When you use SQL statements to define a UDF, make sure that the data types of input parameters of the UDF are the data types supported by MaxCompute. For more information about the data types supported by MaxCompute, see MaxCompute V2.0 data type edition. After the UDF is created, make sure that the data types of the input parameters of the SQL UDF you want to call are the same as the data types of the input parameters of the defined UDF.

• When you create, query, call, or drop an SQL UDF, make sure that the Alibaba Cloud account that you use has the required function-level permissions. For more information about function-level permissions and authorization operations, see MaxCompute permissions.

Syntax

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

Parameters

• function_name: required. This parameter specifies the name of the SQL UDF that you create. The function name must be unique in a project and cannot be the same as the name of a built-in function. Functions that have the same name can be registered only once. You can execute the LIST FUNCTIONS statement to view all functions in a project and check whether an existing function has the same name as the function that you want to create.

• parameter_in: required. This parameter specifies the input parameters of the SQL UDF that you want to create. The input parameters can be of the function type, including anonymous functions. For more information about how to create an SQL UDF whose input parameter is of the function type, see Example on how to create an SQL UDF whose input parameter is of the function type. For more information about how to create an SQL UDF whose input parameter is an anonymous function, see Example on how to create an SQL UDF whose input parameter is an anonymous function.

• datatype: required. This parameter specifies the data types of the input parameters of the UDF. For more information about the data types supported by MaxCompute, see MaxCompute V2.0 data type edition.

• returns: optional. This parameter specifies the return value of the UDF. The return value is a variable. If you do not specify this parameter, the value of the function_name parameter is returned by default.

• parameter_out: required. This parameter specifies the response parameters of the UDF.

• function_expression: required. This parameter specifies the expression (implementation logic) of the UDF.

Examples

function my_add(@a BIGINT) as @a + 1;

Related statements

• CREATE SQL FUNCTION: creates a permanent SQL UDF. After a permanent SQL UDF is created and stored in the metadata system of MaxCompute, all query operations can call the UDF.

• DESC FUNCTION: queries information about permanently stored UDFs, including the function name, owner, creation time, class name, and resource list. Temporary SQL UDFs cannot be queried using this statement.

• DROP FUNCTION: drops an existing UDF from a MaxCompute project.

• SELECT FUNCTION: calls an SQL UDF. A temporary SQL UDF can be called only in the script where the UDF is defined and cannot be called in other query environments.