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 UDF's, 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.
 To verify that the arguments are of a required type or,
alternatively, to tell MySQL to coerce arguments to the
required types when the main function is called.
 
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(). 
 
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()
 
Staring from MariaDB 10.4 it 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/