Creating User-Defined Functions
Contents
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(). 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; or 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.
