Skip to content

CREATE FUNCTION...LANGUAGE SQL AS

Grammar Description

CREATE FUNCTION...LANGUAGE SQL AS is used to create SQL UDFs.

SQL custom functions are SQL functions written by users themselves and can perform custom operations according to specific needs. These functions can be used for tasks such as querying, data conversion, etc., making the sQL code more modular and maintainable.

MatrixOne SQL UDF currently does not support overloading, and function names are required to be unique in a matrixone cluster.

Grammar Structure

> CREATE [ OR REPLACE ] FUNCTION <name> (
[ <arg_name> <arg_data_type> ] [ , ... ] )
RETURNS <result_data_type> LANGUAGE SQL AS 'function_body'

Structural Description

  • <name>: Specify the name of the custom function.

  • <arg_name> <arg_data_type>: Used to specify parameters for custom functions, the parameters here are only names and types.

  • RETURNS <result_data_type>: The data type used to declare the return value of a custom function. For the complete data type, please see [Data Type Overview] (../../../Reference/Data-Types/data-types.md)

  • function_body: The body part of the custom function. The user must use $1, $2,... to reference the parameter, not the actual parameter name. Function bodies support select statements and return values ​​are unique. If the sql function body is not an expression and is a select statement on the table, the query should use limit 1 or an aggregate function without the group by clause to limit its result to 1.

Example

Example 1

--Create a custom function without parameters in SQL

mysql> create table t1(n1 int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)

mysql> CREATE FUNCTION t1_fun () RETURNS VARCHAR LANGUAGE SQL AS 'select n1 from t1 limit 1' ;
Query OK, 0 rows affected (0.01 sec)

mysql> select t1_fun();
+-------------+
| t1_fun() |
+-------------+
| 1 |
+-------------+
1 row in set (0.01 sec)

Example 2

--Create a sql custom function to return the sum of two parameters
mysql> CREATE FUNCTION twoadd (x int, y int) RETURNS int LANGUAGE SQL AS 'select $1 + $2' ;
Query OK, 0 rows affected (0.02 sec)

mysql> select twoadd(1,2);
+-------------------+
| twoadd(1, 2) |
+-------------------+
| 3 |
+-------------------+
1 row in set (0.00 sec)