Extend MariaDB Server's capabilities with user-defined functions (UDFs). Learn how to create and implement custom functions to perform specialized operations directly within your SQL queries.
An introduction to User-Defined Functions (UDFs) in MariaDB, explaining how they extend the server's functionality by adding new native-like functions.
A user-defined function (UDF) is a historical way to extend MariaDB with a new function that works similar to 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.
Statements making use of user-defined functions are not safe for replication.
For an example, see sql/udf_example.cc in the source tree. For a collection of existing UDFs go to the UDF Repository on GitHub.
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.
A guide for developers on writing UDFs in C/C++, covering the required interface functions, memory allocation, and thread safety.
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().
Required for all UDFs; 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.
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.
De-initialization function for x(). Used to de-allocate memory that was allocated in x_init().
Each time the SQL function X() is called:
MariaDB will first call the C/C++ initialization function, x_init(), assuming it exists. All setup are 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()
The following functions are required for aggregate functions, such as and . When using , the keyword is required.
Used to reset the current aggregate, but without inserting the argument as the initial aggregate value for the new group.
Used to add the argument to the current aggregate.
Improves the support of (so it is not obligatory to add it) and should remove the argument from the current aggregate.
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 are 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
For an example, see sql/udf_example.cc in the source tree. For a collection of existing UDFs see .
This page is licensed: CC BY-SA / Gnu FDL
Overview of security measures for UDFs, including file location restrictions, required privileges, and system variable configurations.
The MariaDB server imposes a number of limitations on for security purposes.
The INSERT privilege for the mysql database is required to run , as a record are added to the .
The DELETE privilege for the mysql database is required to run as the corresponding record are removed from the .
For REAL functions, specify the maximum number of decimals for the result.
Allocate any required memory.
See User-defined Functions Calling Sequences for more details on the functions.
x_init()MariaDB will first call the C/C++ initialization function, x_init(), assuming it exists. All setup are 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().
char *
long long
double
UDF object files can only be placed in the plugin directory, as specified by the value of the plugin_dir system variable.
At least one symbol, beyond the required x() , corresponding to an SQL function X(), is required. These can be x_init(), x_deinit(), xxx_reset(), x_clear(), and x_add() functions (see Creating User-defined Functions). The allow-suspicious-udfs mariadbd option (by default unset) provides a workaround, permitting only one symbol to be used. This is not recommended, as it opens the possibility of loading shared objects that are not legitimate user-defined functions.
This page is licensed: CC BY-SA / Gnu FDL
Technical details on the execution flow of UDFs, explaining the sequence in which initialization, processing, and de-initialization functions are called.
The functions described in Creating User-defined Functions are expanded on this page. They are declared as follows:
If x() returns an integer, it is declared as follows:
If x() returns a string (DECIMAL functions also return string values), it is declared as follows:
If x() returns a real, it is declared as follows:
initid is a parameter passed to all three functions that points to a UDF_INIT structure, used for communicating information between the functions. Its structure members are:
my_bool maybe_null
maybe_null should be set to 1 if x_init can return a NULL value, Defaults to 1 if any arguments are declared maybe_null.
unsigned int decimals
x_clear() is a required function for aggregate functions, and is declared as follows:
It is called when the summary results need to be reset, that is at the beginning of each new group. but also to reset the values when there were no matching rows.
is_null is set to point to CHAR(0) before calling x_clear().
In the case of an error, you can store the value to which the error argument points (a single-byte variable, not a string buffer) in the variable.
x_reset() is declared as follows:
It is called on finding the first row in a new group. Should reset the summary variables, and then use UDF_ARGS as the first value in the group's internal summary value. The function is not required if the UDF interface uses x_clear().
x_add() is declared as follows:
It is called for all rows belonging to the same group, and should be used to add the value in UDF_ARGS to the internal summary variable.
x_remove() was added in and is declared as follows (same as x_add()):
It adds more efficient support of aggregate UDFs as . x_remove() should "subtract" the row (reverse x_add()). In aggregate UDFs will work as WINDOW functions without x_remove() but it will not be so efficient.
If x_remove() supported (defined) detected automatically.
This page is licensed: CC BY-SA / Gnu FDL
Number of decimals after the decimal point. The default, if an explicit number of decimals is passed in the arguments to the main function, is the maximum number of decimals, so if 9.5, 9.55 and 9.555 are passed to the function, the default would be three (based on 9.555, the maximum). If there are no explicit number of decimals, the default is set to 31, or one more than the maximum for the DOUBLE, FLOAT and DECIMAL types. This default can be changed in the function to suit the actual calculation.
unsigned int max_length
Maximum length of the result. For integers, the default is 21. For strings, the length of the longest argument. For reals, the default is 13 plus the number of decimals indicated by initid->decimals. The length includes any signs or decimal points. Can also be set to 65KB or 16MB in order to return a BLOB. The memory remains unallocated, but this is used to decide on the data type to use if the data needs to be temporarily stored.
char *ptr
A pointer for use as required by the function. Commonly, initid->ptr is used to communicate allocated memory, with x_init() allocating the memory and assigning it to this pointer, x() using it, and x_deinit() de-allocating it.
my_bool const_item
Should be set to 1 in x_init() if x() always returns the same value, otherwise 0.
long long x(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);char *x(UDF_INIT *initid, UDF_ARGS *args,
char *result, unsigned long *length,
char *is_null, char *error);double x(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);my_bool x_init(UDF_INIT *initid, UDF_ARGS *args, char *message);void x_deinit(UDF_INIT *initid);void x_clear(UDF_INIT *initid, char *is_null, char *error);void x_reset(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);void x_add(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);void x_remove(UDF_INIT* initid, UDF_ARGS* args,
char* is_null, char *error );Install a user-defined function from a shared library. This command loads an external compiled function into the server for extended capabilities.
A user-defined function (UDF) is a way to extend MariaDB with a new function that works like a native (built-in) MariaDB function such as ABS() or .
function_name is the name that should be used in SQL statements to invoke
the function.
To create a function, you must have the for the
mysql database. This is necessary becauseCREATE FUNCTION adds a row to the that records the function's name,
type, and shared library name. If you do not have this table, you should run
the command to create it.
UDFs need to be written in C, C++ or another language that uses C calling conventions, MariaDB needs to have been dynamically compiled, and your operating system must support dynamic loading.
For an example, see sql/udf_example.cc in the source tree. For a collection of existing UDFs see .
Statements making use of user-defined functions are not .
For creating a stored function as opposed to a user-defined function, see .
For valid identifiers to use as function names, see .
The RETURNS clause indicates the type of the function's
return value, and can be one of , , or . DECIMAL functions currently return string values and should be written like functions.
shared_library_name is the basename of the shared object file that contains
the code that implements the function. The file must be located in the plugin
directory. This directory is given by the value of the system variable. Note that
before MariaDB/MySQL 5.1, the shared object could be located in any directory
that was searched by your system's dynamic linker.
Aggregate functions are summary functions such as and . Aggregate UDF functions can be used as .
If the optional OR REPLACE clause is used, it acts as a shortcut for:
When the IF NOT EXISTS clause is used, MariaDB will return a warning instead of an error if the specified function already exists. Cannot be used together with OR REPLACE.
To upgrade the UDF's shared library, first run a statement, then upgrade the shared library and finally run the CREATE FUNCTION statement. If you upgrade without following this process, you may crash the server.
OR REPLACE and IF NOT EXISTS:
This page is licensed: GPLv2, originally from
CREATE [OR REPLACE] [AGGREGATE] FUNCTION [IF NOT EXISTS] function_name
RETURNS {STRING|INTEGER|REAL|DECIMAL}
SONAME shared_library_nameDROP FUNCTION IF EXISTS function_name;
CREATE FUNCTION name ...;CREATE FUNCTION jsoncontains_path RETURNS INTEGER SONAME 'ha_connect.so';
Query OK, 0 rows affected (0.00 sec)CREATE FUNCTION jsoncontains_path RETURNS INTEGER SONAME 'ha_connect.so';
ERROR 1125 (HY000): Function 'jsoncontains_path' already exists
CREATE OR REPLACE FUNCTION jsoncontains_path RETURNS INTEGER SONAME 'ha_connect.so';
Query OK, 0 rows affected (0.00 sec)
CREATE FUNCTION IF NOT EXISTS jsoncontains_path RETURNS INTEGER SONAME 'ha_connect.so';
Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS;
+-------+------+---------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------+
| Note | 1125 | Function 'jsoncontains_path' already exists |
+-------+------+---------------------------------------------+Documentation for the DROP FUNCTION statement, which uninstalls a UDF and removes its entry from the system table.
DROP FUNCTION [IF EXISTS] function_nameThis statement drops the user-defined function (UDF) named function_name.
To drop a function, you must have the for the mysql database. This is because DROP FUNCTION removes the row from the system table that records the function's name, type and shared library name.
For dropping a stored function, see .
To upgrade the UDF's shared library, first run a statement, then upgrade the shared library and finally run the CREATE FUNCTION statement. If you upgrade without following this process, you may crash the server.
IF EXISTS:
This page is licensed: GPLv2, originally from
DROP FUNCTION jsoncontains_path;DROP FUNCTION jsoncontains_path;
ERROR 1305 (42000): FUNCTION test.jsoncontains_path does not exist
DROP FUNCTION IF EXISTS jsoncontains_path;
Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS;
+-------+------+------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------+
| Note | 1305 | FUNCTION test.jsoncontains_path does not exist |
+-------+------+------------------------------------------------+