> ## Documentation Index
> Fetch the complete documentation index at: https://docs.risingwave.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL UDFs

> You can define SQL UDFs in RisingWave by using the [CREATE FUNCTION](/sql/commands/sql-create-function) command.

## Syntax

```sql theme={null}
CREATE FUNCTION function_name ( argument_type [, ...] )
    RETURNS return_type
    LANGUAGE sql
    { AS as_definition | RETURN return_definition };
```

For more details about the supported syntax, see the [examples of SQL UDFs](#examples-1) below.

## Parameters

| Parameter or clause             | Description                                                                                                                                                                                                  |
| :------------------------------ | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| *function\_name*                | The name of the SQL UDF that you want to declare in RisingWave.                                                                                                                                              |
| *argument\_type*                | The data type of the input parameter(s) that the SQL UDF expects to receive.                                                                                                                                 |
| **RETURNS** *return\_type*      | Specifies the data type of the return value from the UDF.                                                                                                                                                    |
| **LANGUAGE** *sql*              | Its value must be sql.                                                                                                                                                                                       |
| **AS** *as\_definition*         | Defines the implementation of the function using SQL statements. as\_definition can be single quote definition (e.g., 'select $1 + $2') or double dollar definition (e.g., $$select $1 + $1$$).              |
| **RETURN** *return\_definition* | Alternative to the AS clause. return\_definition can be an expression (e.g., $1 + $2). Note that **you must specify an AS definition or a RETURN definition, and they can not be specified simultaneously.** |

<Note>
  * Recursive definition is NOT supported at present. For example, the statement `create function recursive(INT, INT) returns int language sql as 'select recursive($1, $2) + recursive($1, $2)';` will fail.
</Note>

## Examples

At present, we support SQL UDFs with unnamed and named parameters. This section offers examples of the current supported syntax. We will offer some basic examples first to help you understand and grasp them. Then, we will offer more examples that are closer to real-world scenarios, such as a mock table, for your further practice and understanding.

### Basic examples for SQL UDFs with unnamed and named parameters

* Create a SQL UDF with unnamed parameters and double dollar definition.

```sql Create function theme={null}
create function add(INT, INT) returns int language sql as 'select $1 + $2';
```

```sql Call function theme={null}
select add(1, -1);
----RESULT
0
```

* Create a SQL UDF with unnamed parameters and single quote definition.

```sql Create function theme={null}
create function sub(INT, INT) returns int language sql as 'select $1 - $2';
```

```sql Call function theme={null}
select sub(1, 1);
----RESULT
0
```

* Create a SQL UDF with unnamed parameters that calls other pre-defined SQL UDFs.

```sql Create function theme={null}
-- Create two SQL UDFs for subsequent call
create function add1(INT, INT) returns int language sql as 'select $1 + $2';
create function sub1(INT, INT) returns int language sql as 'select $1 - $2';

create function add_sub_binding() returns int language sql as 'select add1(1, 1) + sub1(2, 2)';
```

```sql Call function theme={null}
select add_sub_binding();
----RESULT
2

select add1(1, -1), sub1(1, 1), add_sub_binding();
----RESULT
0 0 2
```

***

* Create a SQL UDF with named parameters and single quote definition.

```sql Create function theme={null}
create function add_named(a INT, b INT) returns int language sql as 'select a + b';

```

```sql Call function theme={null}
select add_named(1, -1);
----RESULT
0
```

* Create a SQL UDF with named parameters and double dollar definition.

```sql Create function theme={null}

create function sub_named(a INT, b INT) returns int language sql as $$select a - b$$;
```

```sql Call function theme={null}
select sub_named(1, 1);
----RESULT
0
```

***

* Create a SQL UDF with mixed named and unnamed parameters.

```sql Create function theme={null}
create function add_sub_mix(INT, a INT, INT) returns int language sql as 'select $1 - a + $3';
```

```sql Call function theme={null}
select add_sub_mix(1, 2, 3);
----RESULT
2
```

* Call a SQL UDF with unnamed parameters inside a SQL UDF with named parameters.

```sql Create function theme={null}
create function add2(INT, INT) returns int language sql as $$select $1 + $2$$;
create function add_named_wrapper(a INT, b INT) returns int language sql as 'select add2(a, b)';
```

```sql Call function theme={null}
select add_named_wrapper(1, -1);
----RESULT
0
```

***

* Create a SQL UDF with unnamed parameters and a return expression.

```sql Create function theme={null}
create function add_return(INT, INT) returns int language sql return $1 + $2;
```

```sql Create function theme={null}
select add_return(1, 1);
----RESULT
2
```

* Create a SQL UDF with a return expression using previously defined UDFs.

```sql Create function theme={null}
-- Create a SQL UDF used in subsequent call
create function add_return1(INT, INT) returns int language sql return $1 + $2;

create function add_return_binding() returns int language sql return add_return1(1, 1) + add_return1(1, 1);
```

```sql Call function theme={null}
select add_return_binding();
----RESULT
4
```

***

* Create a SQL UDF with multiple types of parameters.

```sql Create function theme={null}
create function add_sub(INT, FLOAT, INT) returns float language sql as $$select -$1 + $2 - $3$$;
```

```sql Call function theme={null}
select add_sub(1, 5.1415926, 1);
----RESULT
3.1415926
```

* Create a SQL UDF with complex types of unnamed parameters.

```sql Create function theme={null}
create function add_sub_types(INT, BIGINT, FLOAT, DECIMAL, REAL) returns double language sql as 'select $1 + $2 - $3 + $4 + $5';
```

```sql Call function theme={null}
select add_sub_types(1, 1919810114514, 3.1415926, 1.123123, 101010.191919);
----RESULT
1919810215523.1734494
```

***

* Create a wrapper function.

```sql Create function theme={null}

-- Create two SQL UDFs.
create function add_wp(INT, INT) returns int language sql as $$select $1 + $2$$;
create function sub_wp(INT, INT) returns int language sql as 'select $1 - $2';

-- Create a wrapper function.
create function add_sub_wrapper(INT, INT) returns int language sql as 'select add_wp($1, $2) + sub_wp($1, $2) + 114512';
```

```sql Call function theme={null}
select add_sub_wrapper(1, 1);
----RESULT
114514
```

### Basic SQL UDFs integrated with the use of mock tables

The examples in this section are a basic simulation of real-world use cases.

```sql Create table theme={null}

-- Create 3 tables. t1 and t2 are for unnamed SQL UDF. t3 is for named SQL UDF.

create table t1 (c1 INT, c2 INT);
create table t2 (c1 INT, c2 FLOAT, c3 INT);

create table t3 (a INT, b INT);
```

```sql Insert data theme={null}
-- Insert data into these tables.
insert into t1 values (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
insert into t2 values (1, 3.14, 2), (2, 4.44, 5), (20, 10.30, 02);
insert into t3 values (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
```

```sql Create function theme={null}
create function add_return_mc(INT, INT) returns int language sql return $1 + $2;

```

```sql Call function theme={null}
select c1, c2, add_return_mc(c1, c2) from t1 order by c1 asc;
----RESULT
1 1 2
2 2 4
3 3 6
4 4 8
5 5 10
```

```sql Create function theme={null}
create function add_mc(INT, INT) returns int language sql as $$select $1 + $2$$;
create function sub_mc(INT, INT) returns int language sql as 'select $1 - $2';
create function add_sub_mc(INT, FLOAT, INT) returns float language sql as $$select -$1 + $2 - $3$$;
create function add_sub_return_mc(INT, FLOAT, INT) returns float language sql return -$1 + $2 - $3;
```

```sql Call function theme={null}
select sub_mc(c1, c2), c1, c2, add_mc(c1, c2) from t1 order by c1 asc;
----RESULT
0 1 1 2
0 2 2 4
0 3 3 6
0 4 4 8
0 5 5 10

select c1, c2, c3, add_mc(c1, c3), sub_mc(c1, c3), add_sub_mc(c1, c2, c3) from t2 order by c1 asc;
----RESULT
1 3.14 2 3 -1 0.14000000000000012
2 4.44 5 7 -3 -2.5599999999999996
20 10.3 2 22 18 -11.7

select c1, c2, c3, add_mc(c1, c3), sub_mc(c1, c3), add_sub_return_mc(c1, c2, c3) from t2 order by c1 asc;
----RESULT
1 3.14 2 3 -1 0.14000000000000012
2 4.44 5 7 -3 -2.5599999999999996
20 10.3 2 22 18 -11.7

```

```sql Create function theme={null}
create function add_named_mc(a INT, b INT) returns int language sql as 'select a + b';
```

```sql Call function theme={null}
select add_named_mc(a, b) from t3 order by a asc;
----RESULT
2
4
6
8
10
```

### Examples of corner and special cases tests

```sql Create function theme={null}
-- Mixed parameters with calling inner SQL UDFs

create function add_cs(INT, INT) returns int language sql as $$select $1 + $2$$;
create function sub_cs(INT, INT) returns int language sql as 'select $1 - $2';

create function add_sub_mix_wrapper(INT, a INT, INT) returns int language sql as 'select add_cs($1, a) + a + sub_cs(a, $3)';
```

```sql Call function theme={null}
select add_sub_mix_wrapper(1, 2, 3);
----RESULT
4
```

***

```sql Create function theme={null}
-- Named SQL UDF with corner case
create function corner_case(INT, a INT, INT) returns varchar language sql as $$select '$1 + a + $3'$$;
```

```sql Call function theme={null}
select corner_case(1, 2, 3);
----RESULT
$1 + a + $3
```

***

```sql Create function theme={null}
-- Create a SQL UDF with unnamed parameters that calls built-in functions
create function call_regexp_replace() returns varchar language sql as $$select regexp_replace('Cat is the cutest animal.', 'Cat', 'Dog', 'g')$$;
```

```sql Call function theme={null}
select call_regexp_replace();
----RESULT
Dog is the cutest animal.
```

```sql Create function theme={null}
create function regexp_replace_wrapper(varchar) returns varchar language sql as $$select regexp_replace($1, 'Cat', 'Dog', 'g')$$;

```

```sql Call function theme={null}
select regexp_replace_wrapper('Cat is the cutest animal.');
----RESULT
Dog is the cutest animal.
```

<Note>
  Note that double dollar signs should be used otherwise the parsing will fail.
</Note>

***

```sql Create function theme={null}
-- Recursive corner case (i.e., valid definition should not be rejected)
create function foo(INT) returns varchar language sql as $$select 'foo(INT)'$$;

```

```sql Call function theme={null}

select foo(114514);
----RESULT
foo(INT)
```

***

```sql Create function theme={null}
-- Adjust the input value of the calling function (i.e., `print` here) with the actual input parameter
create function print_add_one(INT) returns int language sql as 'select print($1 + 1)';

create function print_add_two(INT) returns int language sql as 'select print($1 + $1)';
```

```sql Call function theme={null}
select print_add_one(1), print_add_one(114513), print_add_two(2);
----RESULT
2 114514 4
```