# CREATE FUNCTION (UDF)

The `CREATE FUNCTION` (UDF) command creates a user-defined scalar-valued function (UDF). A UDF is a callable routine that accepts input parameters, executes programmatic logic, and returns a value (or values).

To create Wasm-based UDFs, refer to [Create Wasm UDFs](https://docs.singlestore.com/cloud/reference/code-engine-powered-by-wasm/create-wasm-udfs.md).

## Syntax

```sql

CREATE [OR REPLACE] FUNCTION function_name ( [parameter_list] )
  RETURNS { data_type [data_type_modifier] } [DETERMINISTIC] AS
  [DECLARE variable_list] [ ...n ]
  BEGIN
      function_body
  END ;

parameter_list:
  variable_name data_type [data_type_modifier [ ...n ] ]  [, ...]
  DEFINER = CURRENT_USER

data_type_modifier:
  DEFAULT default_value | NOT NULL | NULL | COLLATE collation_name

variable_list:
  variable_name data_type [= initial_value] ; [... ;]

```

## Arguments

**OR REPLACE**

If specified, replaces a UDF if one already exists with the same name.

**function\_name**

The name of the function. The scope of function names is the database.

Function names must be unique within a database, and cannot be duplicates of names for other UDFs, tables, views, user-defined table-valued functions (TVFs), user-defined aggregate functions (UDAFs), stored procedures (SPs), or built-in functions such as CONCAT(), ABS(), COUNT(), and so on.

You can also specify `database_name` and `function_name` together by replacing `function_name` with `database_name.function_name` instead of defaulting to the current context database. For example, you can write the following:

```sql

CREATE FUNCTION db.some_func(a int)
...

```

Function names are not case-sensitive. For details on case-sensitivity, refer to the [Database Object Case-Sensitivity](https://docs.singlestore.com/cloud/reference/sql-reference/database-object-case-sensitivity.md) topic.

**parameter\_list**

Input parameters are optional. Any number of input parameters can be specified, and each must be delimited by a comma (`,`). Each input parameter must be given a unique identifier name within the scope of the function. A parameter’s name cannot exceed 64 characters in length.

The following example shows how to declare a single input parameter:

```sql

CREATE FUNCTION single_param_example(a INT)
...

```

UDFs allow both scalar data types and non-scalar data types (`ARRAY` and `RECORD`) as input parameters. Each valid type is described in the [Data Types](https://docs.singlestore.com/cloud/reference/sql-reference/data-types.md) topic. The following example demonstrates how to declare more than one input parameter, using both scalar and non-scalar data types:

```sql

CREATE FUNCTION multi_param_example(a INT, b ARRAY(BIGINT NOT NULL), c DECIMAL(19,4))
...

```

Default values can be specified for input parameters by using the `DEFAULT` constraint. Consider the following example:

```sql

CREATE FUNCTION default_example(a INT DEFAULT 5)
...

```

Input parameters can also specify data type modifiers, namely `NOT NULL`, `NULL`, and `COLLATE`. Consider the following example:

```sql

CREATE FUNCTION modifier_example(a TEXT NOT NULL COLLATE utf8_bin)
...

```

You can use parameters with any supported character sets using the following syntax.

```sql
CREATE FUNCTION function_name(id INT, product VARCHAR(20) COLLATE utf8_general_ci);
…

```

For more information, see [Character Encoding](https://docs.singlestore.com/cloud/reference/sql-reference/character-encoding.md).

**data\_type**

Any scalar-valued or non-scalar valued data type as described in the [Data Types](https://docs.singlestore.com/cloud/reference/sql-reference/data-types.md) topic.

**DETERMINISTIC**

If specified, the query optimizer assumes that this function is deterministic. A deterministic function always returns the same result when called with a specific set of arguments given the same state of a database.

> **📝 Note**: SingleStore does not check whether the function definition contains statements that may return non-deterministic results. Declaring a non-deterministic function as `DETERMINISTIC` may return incorrect results and be prone to incorrect query rewrites.

**DECLARE variable\_list**

A list of zero or more variable declaration statements. Variable names must be unique within the scope of the function, and cannot conflict with existing names for other identifiers. Variables may be set with an initial value. If a default value is not specified, variables are set with a value of NULL. The `DECLARE` clause is only required once before declaring variables, but each variable declaration can be preceded by its own `DECLARE` statement. So, the following two examples are equivalent:

```sql

CREATE FUNCTION single_parameter_example(a INT) AS
  DECLARE
    num INT = 0;
    str VARCHAR(255);
  ...

```

```sql
CREATE FUNCTION multi_declare_example(a INT) AS
  DECLARE num INT = 0;
  DECLARE str VARCHAR(255);
  ...

```

You can use variables with any supported character sets using the following syntax.

```
CREATE FUNCTION single_parameter_example(a INT) AS
  DECLARE
    num INT = 0;
    str VARCHAR(255) COLLATE utf8_general_ci;
  ...
```

For more information, see [Character Encoding](https://docs.singlestore.com/cloud/reference/sql-reference/character-encoding.md).

**function\_body**

The function body of a UDF may contain any of the following statements:

* [Control Flow Statements](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/control-flow-statements.md)
* Variable assignments
* Calls to other UDFs when assigning them to a variable or in a `RETURN` statement.

**Security and Permissions**

The invoker of a UDF must have `EXECUTE` permissions on the UDF. Also, the UDF’s definer must have `EXECUTE` permissions on any UDFs used in the function’s body.

**Optional parameter DEFINER**

UDFs can be created or run based on the `DEFINER`. The `DEFINER` is equivalent to the current user. When the `DEFINER` optional parameter is used a special flag is stored in the metadata table which indicates if a different user is required to run the procedure.

## Remarks

SingleStore Helios user-defined scalar-valued functions (UDFs) provide a mechanism to create custom programmatic logic that can be called in SQL queries, stored procedures, or in other UDFs.

You may reference a system-defined global or session variable inside a user-defined function by using its identifier in a place where a constant value may appear. User-defined variables cannot be referenced inside a user-defined function; use a local variable instead.

This command causes implicit commits. Refer to [COMMIT](https://docs.singlestore.com/cloud/reference/sql-reference/data-manipulation-language-dml/commit.md) for more information.

**MySQL Client Delimiters**

When creating a UDF using a MySQL-compatible client connected to SingleStore Helios, you must change the client delimiter to ensure that the function definition is correctly passed to the server and then set it back to a semicolon after the alternate delimiter is no longer needed. See the [MySQL Client Delimiters](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/mysql-client-delimiters.md) topic for details on MySQL client delimiters.

**Limitations**

UDFs are limited in the following ways:

**Calling Limitations**

A UDF cannot call a stored procedure (SP) or a user-defined aggregate function (UDAF).

**Function Overloading**

A UDF definition cannot be overloaded by changing the function signature, such as adding or removing input parameters or changing the return type.

**SQL and Database Object Manipulation**

UDFs cannot execute SQL statements, and therefore cannot read or modify any persistent database objects.

Refer to the [Permissions Matrix](https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/permissions-matrix.md) for the required permissions.

## Call a UDF

A UDF can be called from a SQL query, a [stored procedure](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/create-procedure.md), or another UDF.

## Pass Arguments to a UDF

Arguments can be passed to a UDF using positional, named, or mixed notation.

**Positional Notation:** Arguments are specified in order. Arguments can be omitted from right to left if the omitted arguments have defaults specified.

```sql
SELECT [database_name.]function_name([arg[, ...]]) ...
```

**Named Notation:** Argument name is specified using `=>` to separate it from the argument expression. Arguments can be specified in any order.

```sql
SELECT [database_name.]function_name([arg=>val [,...]]) ...
```

**Mixed Notation:** A combination of positional and named notation. Named arguments cannot come before positional arguments.

```sql
SELECT [database_name.]function_name([arg [, ...] [, arg=>val [,...] ]]) ...
```

## Examples

## Normalize String

The following example normalizes a string by making all letters lowercase, removing any leading and trailing whitespace characters, and ensuring one whitespace character between words.

```sql
DELIMITER //
CREATE FUNCTION normalize_string(input VARCHAR(255)) RETURNS VARCHAR(255) AS
  DECLARE
    result VARCHAR(255) = "";
    i INT;
    previousChar CHAR;
    nextChar CHAR;
    str VARCHAR(255) = input;
  BEGIN
    str = LCASE(TRIM(str));
    IF LENGTH(str) = 0 THEN
      RETURN str;
    END IF;
    previousChar = SUBSTR(str, 1, 1);
    result = CONCAT(result, previousChar);
    i = 2;
    WHILE i <= LENGTH(str) LOOP
      nextChar = SUBSTR(str, i, 1);
      IF NOT(previousChar = ' ' AND nextChar = ' ') THEN
        result = CONCAT(result, SUBSTR(str, i, 1));
      END IF;
      previousChar = nextChar;
      i += 1;
    END LOOP;
    RETURN result;
  END //
DELIMITER ;

```

```sql
SELECT normalize_string(" THIS is   a test ");

```

```output

+----------------------------------------+
| normalize_string(" THIS is   a test ") |
+----------------------------------------+
| this is a test                         |
+----------------------------------------+

```

## Is Prime Number

The following example function accepts an integer input parameter and returns `1` if it is a prime number or `0` if it is not a prime number.

```sql
DELIMITER //
CREATE FUNCTION is_prime(n BIGINT NOT NULL) returns BIGINT AS
  BEGIN
    IF n <= 1 THEN
      RETURN FALSE;
    END IF;
    FOR i IN 2 .. (n-1) LOOP
      EXIT WHEN i * i > n;
      IF n % i != 0 THEN
        CONTINUE;
      END IF;
      RETURN FALSE;
    END LOOP;
    RETURN TRUE;
  END //
DELIMITER ;

```

```sql
SELECT is_prime(101);

```

```output

+---------------+
| is_prime(101) |
+---------------+
|             1 |
+---------------+

```

## Referencing Variables

The following example shows how to use engine variables inside a user-defined function.

```sql
DELIMITER //
CREATE OR REPLACE FUNCTION getVer() RETURNS VARCHAR(255) AS
DECLARE
  var1 VARCHAR(255);
  var2 VARCHAR(255);
  res VARCHAR(255);
BEGIN
  var1 = @@memsql_version;
  var2 = @@version_compile_os;
  res = CONCAT("Version: ",var1, "; OS: ", var2);
RETURN res;
END //
DELIMITER ;

```

```sql
SELECT getVer() As Info;

```

```output

+---------------------------+
| Info                      |
+---------------------------+
| Version: 7.3.1; OS: Linux |
+---------------------------+
```

## Pass Arguments using Positional, Named, and Mixed Notation

The following examples demonstrate how to use positional, named, and mixed notation to pass arguments to a UDF.

The examples use the following UDF that calculates the total price based on price, quantity, and discount.

```sql
DELIMITER //
CREATE FUNCTION calculate_total(price DECIMAL(10,2), quantity INT DEFAULT 1, discount DECIMAL(5,2) DEFAULT 0.0)
RETURNS DECIMAL(10,2) AS
BEGIN
    RETURN (price * quantity) * (1 - discount);
END;
//
DELIMITER ;
```

## Positional Notation

Call `calculate_total` using positional notation.

```sql
SELECT calculate_total(10.5, 5, 0.1) AS total_price_10pct_discount;

```

```output

+----------------------------+
| total_price_10pct_discount |
+----------------------------+
|                      47.25 |
+----------------------------+

```

In the following call, which also uses positional notation, only two arguments (`price` and `quantity`) are provided, the third argument (`discount`) is not provided. The default value for discount, `0.0`, will be used in the calculation.

```sql
SELECT calculate_total(10.5, 5) AS total_price_default_discount;

```

```output

+------------------------------+
| total_price_default_discount |
+------------------------------+
|                        52.50 |
+------------------------------+

```

## Named Notation

Call `calculate_total` using named notation. Note that the order of arguments in the call to `calculate_total` in the following query is different from the order of arguments in the definition `calculate_total`.

```sql
SELECT calculate_total(quantity=>5,price=>10.5,discount=>0.1) AS total_price_10pct_discount;

```

```output

+----------------------------+
| total_price_10pct_discount |
+----------------------------+
|                      47.25 |
+----------------------------+
```

## Mixed Notation

Call `calculate_total` using mixed notation. The first two arguments use positional notation, and the third argument for the discount uses named notation. Positional arguments must be specified first, followed by named arguments.

```sql
SELECT calculate_total(10.5, 5, discount=>0.1) AS total_price_10pct_discount;

```

```output

+----------------------------+
| total_price_10pct_discount |
+----------------------------+
|                      47.25 |
+----------------------------+

```

## Create a UDF Using `DEFINER`

```sql
DELIMITER // 
CREATE FUNCTION func1(usr TEXT) RETURNS TEXT DEFINER = current_user AS 
    BEGIN 
    RETURN usr; 
    END //
DELIMITER ;

```

```sql
SHOW FUNCTION STATUS LIKE'func1';

```

```output

+-----------+----------+----------+-----------+------------+------------+------------+----------+--------------+------------+-----------+------------+
| DB        | Name     | Type     | Definer   | Modified   | Created    | Security_  | Comment  | character_   | collation_ | Database  | Routine    |
|           |          |          |           |            |            | Type       |          | set_client   | connection | Collation | Lifetime   |
+-----------+----------+----------+-----------+------------+------------+------------+----------+--------------+------------+-----------+------------+
| func_proc | func1    | FUNCTION | <user>@   | 0000-00-00 | 2022-09-22 | DEFINER    |          |              |            |           | PERSISTENT |
|           |          |          | localhost | 00:00:00   | 22:21:47   |            |          |              |            |           |            |
+-----------+----------+----------+-----------+------------+------------+------------+----------+--------------+------------+-----------+------------+
```

## **Related Topics**

* [DROP FUNCTION](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/drop-function.md)
* [SHOW FUNCTIONS](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/show-functions.md)
* [SHOW CREATE FUNCTION](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/show-create-function.md)

***

Modified at: June 11, 2026

Source: [/cloud/reference/sql-reference/procedural-sql-reference/create-function-udf/](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/create-function-udf/)

(An index of the documentation is available at /llms.txt)