# CREATE FUNCTION UDF

## Syntax

```sql
CREATE [OR REPLACE] [AGGREGATE] FUNCTION [IF NOT EXISTS] function_name
    RETURNS {STRING|INTEGER|REAL|DECIMAL}
    SONAME shared_library_name
```

## Description

A user-defined function (UDF) is a way to extend MariaDB with a new function\
that works like a native (built-in) MariaDB function such as [ABS()](https://mariadb.com/docs/server/reference/sql-functions/numeric-functions/abs) or [CONCAT()](https://mariadb.com/docs/server/reference/sql-functions/string-functions/concat).

`function_name` is the name that should be used in SQL statements to invoke\
the function.

To create a function, you must have the [INSERT privilege](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant) for the\
mysql database. This is necessary because`CREATE FUNCTION` adds a row to the [mysql.func system table](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables/mysql-func-table) that records the function's name,\
type, and shared library name. If you do not have this table, you should run\
the [mariadb-upgrade](https://mariadb.com/docs/server/clients-and-utilities/deployment-tools/mariadb-upgrade) command to create it.

UDFs need to be written in C, C++ or another language that uses C calling\
conventions, MariaDB needs to have been dynamically compiled, and your\
operating system must support dynamic loading.

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

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

For creating a stored function as opposed to a user-defined function, see [CREATE FUNCTION](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-function).

For valid identifiers to use as function names, see [Identifier Names](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/identifier-names).

#### RETURNS

The `RETURNS` clause indicates the type of the function's\
return value, and can be one of [string](https://mariadb.com/docs/server/reference/data-types/string-data-types), [INTEGER](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/integer), [REAL](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/real) or [DECIMAL](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/decimal). `DECIMAL` functions currently return string values and should be written like [STRING](https://mariadb.com/docs/server/reference/data-types/string-data-types) functions.

#### shared\_library\_name

`shared_library_name` is the basename of the shared object file that contains\
the code that implements the function. The file must be located in the plugin\
directory. This directory is given by the value of the [plugin\_dir](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#plugin_dir) system variable. Note that\
before MariaDB/MySQL 5.1, the shared object could be located in any directory\
that was searched by your system's dynamic linker.

#### AGGREGATE

Aggregate functions are summary functions such as [SUM()](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/sum) and [AVG()](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/avg).\
Aggregate UDF functions can be used as [window functions](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions).

#### OR REPLACE

If the optional `OR REPLACE` clause is used, it acts as a shortcut for:

```sql
DROP FUNCTION IF EXISTS function_name;
CREATE FUNCTION name ...;
```

#### IF NOT EXISTS

When the IF NOT EXISTS clause is used, MariaDB will return a warning instead of an error if the specified function already exists. Cannot be used together with OR REPLACE.

### Upgrading a UDF

To upgrade the UDF's shared library, first run a [DROP FUNCTION](https://mariadb.com/docs/server/server-usage/stored-routines/stored-functions/drop-function) statement, then upgrade the shared library and\
finally run the CREATE FUNCTION statement. If you upgrade without following\
this process, you may crash the server.

### Examples

```sql
CREATE FUNCTION jsoncontains_path RETURNS INTEGER SONAME 'ha_connect.so';
Query OK, 0 rows affected (0.00 sec)
```

OR REPLACE and IF NOT EXISTS:

```sql
CREATE FUNCTION jsoncontains_path RETURNS INTEGER SONAME 'ha_connect.so';
ERROR 1125 (HY000): Function 'jsoncontains_path' already exists

CREATE OR REPLACE FUNCTION jsoncontains_path RETURNS INTEGER SONAME 'ha_connect.so';
Query OK, 0 rows affected (0.00 sec)

CREATE FUNCTION IF NOT EXISTS jsoncontains_path RETURNS INTEGER SONAME 'ha_connect.so';
Query OK, 0 rows affected, 1 warning (0.00 sec)

SHOW WARNINGS;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Note  | 1125 | Function 'jsoncontains_path' already exists |
+-------+------+---------------------------------------------+
```

## See Also

* [Identifier Names](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/identifier-names)
* [DROP FUNCTION](https://mariadb.com/docs/server/server-usage/stored-routines/stored-functions/drop-function)
* [CREATE FUNCTION](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-function)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/server-usage/user-defined-functions/create-function-udf.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.