User-defined functions allow MariaDB to be extended with a new function that
works like a native (built-in) MariaDB function such as ABS() or CONCAT().
There are alternative ways to add a new function: writing a native function
(which requires modifying and compiling the server source code), or writing a
stored function.

Statements making use of user-defined functions are not safe for replication.

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                           |
+------------------------------------------+----------------------------------+
| long long                                | INTEGER                          |
+------------------------------------------+----------------------------------+
| double                                   | REAL                             |
+------------------------------------------+----------------------------------+

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 will be 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 for more details on the
functions.

Aggregate Functions
-------------------

The following functions are required for aggregate functions, such as AVG()
and SUM(). When using CREATE FUNCTION, the 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()
----------

Starting from MariaDB 10.4, improves the support of window functions (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 will be 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 will be 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 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 https://github.com/mysqludf.

URL: https://mariadb.com/kb/en/creating-user-defined-functions/