> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/server-usage/user-defined-functions/creating-user-defined-functions.md).

# Creating User-Defined Functions

[User-defined functions](/docs/server/server-usage/user-defined-functions.md) allow MariaDB to be extended with a new function that works like a native (built-in) MariaDB function such as [ABS()](/docs/server/reference/sql-functions/numeric-functions/abs.md) or [CONCAT()](/docs/server/reference/sql-functions/string-functions/concat.md). It was introduced in 1998 and is generally limited to supporting features that existed at that time. There are alternative ways to add a new function: a native function, which requires modifying and compiling the server source code; a [function plugin](/docs/server/reference/plugins.md); or a [stored function](/docs/server/server-usage/stored-routines/stored-functions.md).

Statements making use of user-defined functions are not [safe for replication](/docs/server/ha-and-performance/standard-replication/unsafe-statements-for-statement-based-replication.md).

Functions are written in C or C++, and to make use of them, the operating system must support dynamic loading.

Each new SQL function requires corresponding functions written in C/C++. In the list below, at least the main function - x() - and one other, are required. *x* should be replaced by the name of the function you are creating.

All functions need to be thread-safe, so not global or static variables that change can be allocated. Memory is allocated in `x_init()` and freed in `x_deinit()`.

## Simple Functions

### x()

Required for all UDFs; this is where the results are calculated.

| C/C++ type | SQL type                                                                   |
| ---------- | -------------------------------------------------------------------------- |
| char \*    | [STRING](/docs/server/reference/data-types/string-data-types.md)           |
| long long  | [INTEGER](/docs/server/reference/data-types/numeric-data-types/integer.md) |
| double     | [REAL](/docs/server/reference/data-types/numeric-data-types.md)            |

`DECIMAL` functions return string values, and so should be written accordingly. It is not possible to create `ROW` functions.

### x\_init()

Initialization function for `x()`. Can be used for the following:

* Check the number of arguments to `X()` (the SQL equivalent).
* Verify the argument types, or to force arguments to be of a particular type after the function is called.
* Specify whether the result can be `NULL`.
* Specify the maximum result length.
* For `REAL` functions, specify the maximum number of decimals for the result.
* Allocate any required memory.

### x\_deinit()

De-initialization function for `x()`. Used to de-allocate memory that was allocated in `x_init()`.

### Description

Each time the SQL function `X()` is called:

* MariaDB will first call the C/C++ initialization function, `x_init()`, assuming it exists. All setup are performed, and if it returns an error, the SQL statement is aborted and no further functions are called.
* If there is no `x_init()` function, or it has been called and did not return an error, x() is then called once per row.
* After all rows have finished processing, `x_deinit()` is called, if present, to clean up by de-allocating any memory that was allocated in `x_init()`.
* See [User-defined Functions Calling Sequences](/docs/server/server-usage/user-defined-functions/user-defined-functions-calling-sequences.md) for more details on the functions.

## Aggregate Functions

The following functions are required for aggregate functions, such as [AVG()](/docs/server/reference/sql-functions/aggregate-functions/avg.md) and [SUM()](/docs/server/reference/sql-functions/aggregate-functions/sum.md). When using [CREATE FUNCTION](/docs/server/server-usage/user-defined-functions/create-function-udf.md), the [AGGREGATE](/docs/server/server-usage/user-defined-functions/create-function-udf.md#aggregate) keyword is required.

### x\_clear()

Used to reset the current aggregate, but without inserting the argument as the initial aggregate value for the new group.

### x\_add()

Used to add the argument to the current aggregate.

### x\_remove()

Improves the support of [window functions](/docs/server/reference/sql-functions/special-functions/window-functions.md) (so it is not obligatory to add it) and should remove the argument from the current aggregate.

### Description

Each time the aggregate SQL function `X()` is called:

* MariaDB will first call the C/C++ initialization function, `x_init()`, assuming it exists. All setup are performed, and if it returns an error, the SQL statement is aborted and no further functions are called.
* If there is no `x_init()` function, or it has been called and did not return an error, `x()` is then called once per row.
* After all rows have finished processing, `x_deinit()` is called, if present, to clean up by de-allocating any memory that was allocated in `x_init()`.
* MariaDB will first call the C/C++ initialization function, `x_init()`, assuming it exists. All setup are performed, and if it returns an error, the SQL statement is aborted and no further functions are called.
* The table is sorted according to the [GROUP BY](/docs/server/reference/sql-statements/data-manipulation/selecting-data/group-by.md) expression.
* `x_clear()` is called for the first row of each new group.
* `x_add()` is called once per row for each row in the same group.
* `x()` is called when the group changes, or after the last row, to get the aggregate result.
* The latter three steps are repeated until all rows have been processed.
* After all rows have finished processing, `x_deinit()` is called, if present, to clean up by de-allocating any memory that was allocated in `x_init()`.

## Examples

For an example, see `sql/udf_example.cc` in the source tree. For a collection of existing UDFs see [mysqludf](https://github.com/mysqludf).

## See Also

* [Stored Functions](/docs/server/server-usage/stored-routines/stored-functions.md)
* [Stored Aggregate Functions](/docs/server/server-usage/stored-routines/stored-functions/stored-aggregate-functions.md)
* [User-defined Functions Calling Sequences](/docs/server/server-usage/user-defined-functions/user-defined-functions-calling-sequences.md)
* [allow-suspicious-udfs](/docs/server/server-management/starting-and-stopping-mariadb/mariadbd-options.md#-allow-suspicious-udfs)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}