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.

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.

See Also

• Stored Functions
• Stored Aggregate Functions
• User-defined Functions Calling Sequences