Creating User-defined Functions
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, and one other, are required. x should be replaced by the name of the function you are creating.
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.
For an example, see sql/udf_example.cc
in the source tree. For a collection of existing UDFs see http://www.mysqludf.org/.