Creating User-defined Functions
Contents
This article is currently incomplete.
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.
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().
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.
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 http://www.mysqludf.org/.