28.4.2 Adding a User-Defined Function
For the UDF mechanism to work, functions must be written in C or C++ and your operating system must support dynamic loading. MySQL source distributions include a file sql/udf_example.cc
that defines five UDF functions. Consult this file to see how UDF calling conventions work. The include/mysql_com.h
header file defines UDF-related symbols and data structures, although you need not include this header file directly; it is included by mysql.h
.
A UDF contains code that becomes part of the running server, so when you write a UDF, you are bound by any and all constraints that apply to writing server code. For example, you may have problems if you attempt to use functions from the libstdc++
library. These constraints may change in future versions of the server, so it is possible that server upgrades will require revisions to UDFs that were originally written for older servers. For information about these constraints, see Section 2.9.7, “MySQL Source-Configuration Options”, and Section 2.9.8, “Dealing with Problems Compiling MySQL”.
To be able to use UDFs, you must link mysqld dynamically. If you want to use a UDF that needs to access symbols from mysqld (for example, the metaphone
function in sql/udf_example.cc
uses default_charset_info
), you must link the program with -rdynamic
(see man dlopen
).
For each function that you want to use in SQL statements, you should define corresponding C (or C++) functions. In the following discussion, the name “xxx” is used for an example function name. To distinguish between SQL and C/C++ usage, XXX()
(uppercase) indicates an SQL function call, and xxx()
(lowercase) indicates a C/C++ function call.
When using C++, encapsulate your C functions within this construct:
extern "C" { ... }
This ensures that your C++ function names remain readable in the completed UDF.
The following list describes the C/C++ functions that you write to implement the interface for a function named XXX()
. The main function, xxx()
, is required. In addition, a UDF requires at least one of the other functions described here, for reasons discussed in UDF Security Precautions.
xxx()
The main function. This is where the function result is computed. The correspondence between the SQL function data type and the return type of your C/C++ function is shown here.
It is also possible to declare a
DECIMAL
function, but the value is returned as a string, so you should write the UDF as though it were aSTRING
function.ROW
functions are not implemented.xxx_init()
The initialization function for
xxx()
. If present, it can be used for the following purposes:To check the number of arguments to
XXX()
.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.
To allocate any memory required by the main function.
To specify the maximum length of the result.
To specify (for
REAL
functions) the maximum number of decimal places in the result.To specify whether the result can be
NULL
.
xxx_deinit()
The deinitialization function for
xxx()
. If present, it should deallocate any memory allocated by the initialization function.
When an SQL statement invokes XXX()
, MySQL calls the initialization function xxx_init()
to let it perform any required setup, such as argument checking or memory allocation. If xxx_init()
returns an error, MySQL aborts the SQL statement with an error message and does not call the main or deinitialization functions. Otherwise, MySQL calls the main function xxx()
once for each row. After all rows have been processed, MySQL calls the deinitialization function xxx_deinit()
so that it can perform any required cleanup.
For aggregate functions that work like SUM()
, you must also provide the following functions:
xxx_clear()
Reset the current aggregate value but do not insert the argument as the initial aggregate value for a new group.
xxx_add()
Add the argument to the current aggregate value.
MySQL handles aggregate UDFs as follows:
Call
xxx_init()
to let the aggregate function allocate any memory it needs for storing results.Sort the table according to the
GROUP BY
expression.Call
xxx_clear()
for the first row in each new group.Call
xxx_add()
for each row that belongs in the same group.Call
xxx()
to get the result for the aggregate when the group changes or after the last row has been processed.Repeat steps 3 to 5 until all rows has been processed
Call
xxx_deinit()
to let the UDF free any memory it has allocated.
All functions must be thread-safe. This includes not just the main function, but the initialization and deinitialization functions as well, and also the additional functions required by aggregate functions. A consequence of this requirement is that you are not permitted to allocate any global or static variables that change! If you need memory, you must it in xxx_init()
and free it in xxx_deinit()
.
This section describes the different functions that you must define to create a simple UDF. For information about the order in which MySQL calls these functions, see User-Defined Function Interface Functions.
The main xxx()
function should be declared as shown in this section. Note that the return type and parameters differ, depending on whether you declare the SQL function XXX()
to return STRING
, INTEGER
, or REAL
in the CREATE FUNCTION
statement:
For STRING
functions:
char *xxx(UDF_INIT *initid, UDF_ARGS *args,
char *result, unsigned long *length,
char *is_null, char *error);
For INTEGER
functions:
long long xxx(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
For REAL
functions:
double xxx(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
DECIMAL
functions return string values and are declared the same way as STRING
functions. ROW
functions are not implemented.
Declare the initialization and deinitialization functions like this:
my_bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
void xxx_deinit(UDF_INIT *initid);
The initid
parameter is passed to all three functions. It points to a UDF_INIT
structure that is used to communicate information between functions. The UDF_INIT
structure members follow. The initialization function should fill in any members that it wishes to change. (To use the default for a member, leave it unchanged.)
my_bool maybe_null
xxx_init()
should setmaybe_null
to1
ifxxx()
can returnNULL
. The default value is1
if any of the arguments are declaredmaybe_null
.unsigned int decimals
The number of decimal digits to the right of the decimal point. The default value is the maximum number of decimal digits in the arguments passed to the main function. For example, if the function is passed
1.34
,1.345
, and1.3
, the default would be 3, because1.345
has 3 decimal digits.For arguments that have no fixed number of decimals, the
decimals
value is set to 31, which is 1 more than the maximum number of decimals permitted for theDECIMAL
,FLOAT
, andDOUBLE
data types. This value is available as the constantNOT_FIXED_DEC
in themysql_com.h
header file.A
decimals
value of 31 is used for arguments in cases such as aFLOAT
orDOUBLE
column declared without an explicit number of decimals (for example,FLOAT
rather thanFLOAT(10,3)
) and for floating-point constants such as1345E-3
. It is also used for string and other nonnumber arguments that might be converted within the function to numeric form.The value to which the
decimals
member is initialized is only a default. It can be changed within the function to reflect the actual calculation performed. The default is determined such that the largest number of decimals of the arguments is used. If the number of decimals isNOT_FIXED_DEC
for even one of the arguments, that is the value used fordecimals
.unsigned int max_length
The maximum length of the result. The default
max_length
value differs depending on the result type of the function. For string functions, the default is the length of the longest argument. For integer functions, the default is 21 digits. For real functions, the default is 13 plus the number of decimal digits indicated byinitid->decimals
. (For numeric functions, the length includes any sign or decimal point characters.)If you want to return a blob value, you can set
max_length
to 65KB or 16MB. This memory is not allocated, but the value is used to decide which data type to use if there is a need to temporarily store the data.char *ptr
A pointer that the function can use for its own purposes. For example, functions can use
initid->ptr
to communicate allocated memory among themselves.xxx_init()
should allocate the memory and assign it to this pointer:initid->ptr = allocated_memory;
In
xxx()
andxxx_deinit()
, refer toinitid->ptr
to use or deallocate the memory.my_bool const_item
xxx_init()
should setconst_item
to1
ifxxx()
always returns the same value and to0
otherwise.
This section describes the different functions that you need to define when you create an aggregate UDF. For information about the order in which MySQL calls these functions, see User-Defined Function Interface Functions.
xxx_reset()
This function is called when MySQL finds the first row in a new group. It should reset any internal summary variables and then use the given
UDF_ARGS
argument as the first value in your internal summary value for the group. Declarexxx_reset()
as follows:void xxx_reset(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);
xxx_reset()
is not needed or used in MySQL 5.7, in which the UDF interface usesxxx_clear()
instead. However, you can define bothxxx_reset()
andxxx_clear()
if you want to have your UDF work with older versions of the server. (If you do include both functions, thexxx_reset()
function in many cases can be implemented internally by callingxxx_clear()
to reset all variables, and then callingxxx_add()
to add theUDF_ARGS
argument as the first value in the group.)xxx_clear()
This function is called when MySQL needs to reset the summary results. It is called at the beginning for each new group but can also be called to reset the values for a query where there were no matching rows. Declare
xxx_clear()
as follows:void xxx_clear(UDF_INIT *initid, char *is_null, char *error);
is_null
is set to point toCHAR(0)
before callingxxx_clear()
.If something went wrong, you can store a value in the variable to which the
error
argument points.error
points to a single-byte variable, not to a string buffer.xxx_clear()
is required by MySQL 5.7.xxx_add()
This function is called for all rows that belong to the same group. You should use it to add the value in the
UDF_ARGS
argument to your internal summary variable.void xxx_add(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);
The xxx()
function for an aggregate UDF should be declared the same way as for a nonaggregate UDF. See UDF Calling Sequences for Simple Functions.
For an aggregate UDF, MySQL calls the xxx()
function after all rows in the group have been processed. You should normally never access its UDF_ARGS
argument here but instead return a value based on your internal summary variables.
Return value handling in xxx()
should be done the same way as for a nonaggregate UDF. See UDF Return Values and Error Handling.
The xxx_reset()
and xxx_add()
functions handle their UDF_ARGS
argument the same way as functions for nonaggregate UDFs. See UDF Argument Processing.
The pointer arguments to is_null
and error
are the same for all calls to xxx_reset()
, xxx_clear()
, xxx_add()
and xxx()
. You can use this to remember that you got an error or whether the xxx()
function should return NULL
. You should not store a string into *error
! error
points to a single-byte variable, not to a string buffer.
*is_null
is reset for each group (before calling xxx_clear()
). *error
is never reset.
If *is_null
or *error
are set when xxx()
returns, MySQL returns NULL
as the result for the group function.
The args
parameter points to a UDF_ARGS
structure that has the members listed here:
unsigned int arg_count
The number of arguments. Check this value in the initialization function if you require your function to be called with a particular number of arguments. For example:
if (args->arg_count != 2) { strcpy(message,"XXX() requires two arguments"); return 1; }
For other
UDF_ARGS
member values that are arrays, array references are zero-based. That is, refer to array members using index values from 0 toargs->arg_count
- 1.enum Item_result *arg_type
A pointer to an array containing the types for each argument. The possible type values are
STRING_RESULT
,INT_RESULT
,REAL_RESULT
, andDECIMAL_RESULT
.To make sure that arguments are of a given type and return an error if they are not, check the
arg_type
array in the initialization function. For example:if (args->arg_type[0] != STRING_RESULT || args->arg_type[1] != INT_RESULT) { strcpy(message,"XXX() requires a string and an integer"); return 1; }
Arguments of type
DECIMAL_RESULT
are passed as strings, so you handle them the same way asSTRING_RESULT
values.As an alternative to requiring your function's arguments to be of particular types, you can use the initialization function to set the
arg_type
elements to the types you want. This causes MySQL to coerce arguments to those types for each call toxxx()
. For example, to specify that the first two arguments should be coerced to string and integer, respectively, do this inxxx_init()
:args->arg_type[0] = STRING_RESULT; args->arg_type[1] = INT_RESULT;
Exact-value decimal arguments such as
1.3
orDECIMAL
column values are passed with a type ofDECIMAL_RESULT
. However, the values are passed as strings. To receive a number, use the initialization function to specify that the argument should be coerced to aREAL_RESULT
value:args->arg_type[2] = REAL_RESULT;
char **args
args->args
communicates information to the initialization function about the general nature of the arguments passed to your function. For a constant argumenti
,args->args[i]
points to the argument value. (See later for instructions on how to access the value properly.) For a nonconstant argument,args->args[i]
is0
. A constant argument is an expression that uses only constants, such as3
or4*7-2
orSIN(3.14)
. A nonconstant argument is an expression that refers to values that may change from row to row, such as column names or functions that are called with nonconstant arguments.For each invocation of the main function,
args->args
contains the actual arguments that are passed for the row currently being processed.If argument
i
representsNULL
,args->args[i]
is a null pointer (0). If the argument is notNULL
, functions can refer to it as follows:An argument of type
STRING_RESULT
is given as a string pointer plus a length, to enable handling of binary data or data of arbitrary length. The string contents are available asargs->args[i]
and the string length isargs->lengths[i]
. Do not assume that the string is null-terminated.For an argument of type
INT_RESULT
, you must castargs->args[i]
to along long
value:long long int_val; int_val = *((long long*) args->args[i]);
For an argument of type
REAL_RESULT
, you must castargs->args[i]
to adouble
value:double real_val; real_val = *((double*) args->args[i]);
For an argument of type
DECIMAL_RESULT
, the value is passed as a string and should be handled like aSTRING_RESULT
value.ROW_RESULT
arguments are not implemented.
unsigned long *lengths
For the initialization function, the
lengths
array indicates the maximum string length for each argument. You should not change these. For each invocation of the main function,lengths
contains the actual lengths of any string arguments that are passed for the row currently being processed. For arguments of typesINT_RESULT
orREAL_RESULT
,lengths
still contains the maximum length of the argument (as for the initialization function).char *maybe_null
For the initialization function, the
maybe_null
array indicates for each argument whether the argument value might be null (0 if no, 1 if yes).char **attributes
args->attributes
communicates information about the names of the UDF arguments. For argumenti
, the attribute name is available as a string inargs->attributes[i]
and the attribute length isargs->attribute_lengths[i]
. Do not assume that the string is null-terminated.By default, the name of a UDF argument is the text of the expression used to specify the argument. For UDFs, an argument may also have an optional
[AS]
clause, in which case the argument name isalias_name
alias_name
. Theattributes
value for each argument thus depends on whether an alias was given.Suppose that a UDF
my_udf()
is invoked as follows:SELECT my_udf(expr1, expr2 AS alias1, expr3 alias2);
In this case, the
attributes
andattribute_lengths
arrays will have these values:args->attributes[0] = "expr1" args->attribute_lengths[0] = 5 args->attributes[1] = "alias1" args->attribute_lengths[1] = 6 args->attributes[2] = "alias2" args->attribute_lengths[2] = 6
unsigned long *attribute_lengths
The
attribute_lengths
array indicates the length of each argument name.
The initialization function should return 0
if no error occurred and 1
otherwise. If an error occurs, xxx_init()
should store a null-terminated error message in the message
parameter. The message is returned to the client. The message buffer is MYSQL_ERRMSG_SIZE
characters long. Try to keep the message to less than 80 characters so that it fits the width of a standard terminal screen.
The return value of the main function xxx()
is the function value, for long long
and double
functions. A string function should return a pointer to the result and set *length
to the length (in bytes) of the return value. For example:
memcpy(result, "result string", 13);
*length = 13;
MySQL passes a buffer to the xxx()
function using the result
parameter. This buffer is sufficiently long to hold 255 characters, which can be multibyte characters. The xxx()
function can store the result in this buffer if it fits, in which case the return value should be a pointer to the buffer. If the function stores the result in a different buffer, it should return a pointer to that buffer.
If your string function does not use the supplied buffer (for example, if it needs to return a string longer than 255 characters), you must allocate the space for your own buffer with malloc()
in the xxx_init()
function or the xxx()
function and free it in your xxx_deinit()
function. You can store the allocated memory in the ptr
slot in the UDF_INIT
structure for reuse by future xxx()
calls. See UDF Calling Sequences for Simple Functions.
To indicate a return value of NULL
in the main function, set *is_null
to 1
:
*is_null = 1;
To indicate an error return in the main function, set *error
to 1
:
*error = 1;
If xxx()
sets *error
to 1
for any row, the function value is NULL
for the current row and for any subsequent rows processed by the statement in which XXX()
was invoked. (xxx()
is not even called for subsequent rows.)
Files implementing UDFs must be compiled and installed on the host where the server runs. The process is described here for the example UDF file sql/udf_example.cc
that is included in MySQL source distributions. For additional information about UDF installation, see Section 5.6.2, “Installing and Uninstalling User-Defined Functions”.
If a UDF will be referred to in statements that will be replicated to slave servers, you must ensure that every slave also has the function available. Otherwise, replication fails on the slaves when they attempt to invoke the function.
The udf_example.cc
file contains the following functions:
metaphon()
returns a metaphon string of the string argument. This is something like a soundex string, but it is more tuned for English.myfunc_double()
returns the sum of the ASCII values of the characters in its arguments, divided by the sum of the length of its arguments.myfunc_int()
returns the sum of the length of its arguments.sequence([const int])
returns a sequence starting from the given number or 1 if no number has been given.lookup()
returns the IP address for a host name.reverse_lookup()
returns the host name for an IP address. The function may be called either with a single string argument of the form'xxx.xxx.xxx.xxx'
or with four numbers.avgcost()
returns an average cost. This is an aggregate function.
On Unix and Unix-like systems, compile user-defined functions using the following procedure:
A dynamically loadable file should be compiled as a sharable library file, using a command something like this:
gcc -shared -o udf_example.so udf_example.cc
If you are using gcc with CMake (which is how MySQL itself is configured), you should be able to create udf_example.so
with a simpler command:
make udf_example
After compiling a shared object containing UDFs, you must install it and tell MySQL about it. Compiling a shared object from udf_example.cc
using gcc directly produces a file named udf_example.so
. Copy the shared object to the server's plugin directory and name it udf_example.so
. This directory is given by the value of the plugin_dir
system variable.
On some systems, the ldconfig program that configures the dynamic linker does not recognize a shared object unless its name begins with lib
. In this case you should rename a file such as udf_example.so
to libudf_example.so
.
On Windows, compile user-defined functions using the following procedure:
Obtain a MySQL source distribution. See Section 2.1.2, “How to Get MySQL”.
Obtain the CMake build utility, if necessary, from http://www.cmake.org . (Version 2.6 or later is required).
In the source tree, look in the
sql
directory for files namedudf_example.def
andudf_example.cc
. Copy both files from this directory to your working directory.Create a CMake
makefile
(CMakeLists.txt
) with these contents:PROJECT(udf_example) # Path for MySQL include directory INCLUDE_DIRECTORIES("c:/mysql/include") ADD_DEFINITIONS("-DHAVE_DLOPEN") ADD_LIBRARY(udf_example MODULE udf_example.cc udf_example.def) TARGET_LINK_LIBRARIES(udf_example wsock32)
Create the VC project and solution files, substituting an appropriate
generator
value:cmake -G "generator"
Invoking cmake --help shows you a list of valid generators.
Create
udf_example.dll
:devenv udf_example.sln /build Release
On all platforms, after the shared library file has been copied to the plugin_dir
directory, notify mysqld about the new functions with the following statements. The file name suffix differs per platform (for example, .so
for Unix and Unix-like systems, .dll
for Windows), so adjust the .so
suffix for your platform as necessary.
CREATE FUNCTION metaphon RETURNS STRING
SONAME 'udf_example.so';
CREATE FUNCTION myfunc_double RETURNS REAL
SONAME 'udf_example.so';
CREATE FUNCTION myfunc_int RETURNS INTEGER
SONAME 'udf_example.so';
CREATE FUNCTION sequence RETURNS INTEGER
SONAME 'udf_example.so';
CREATE FUNCTION lookup RETURNS STRING
SONAME 'udf_example.so';
CREATE FUNCTION reverse_lookup RETURNS STRING
SONAME 'udf_example.so';
CREATE AGGREGATE FUNCTION avgcost RETURNS REAL
SONAME 'udf_example.so';
Once installed, a function remains installed until it is uninstalled.
To remove functions, use DROP FUNCTION
:
DROP FUNCTION metaphon;
DROP FUNCTION myfunc_double;
DROP FUNCTION myfunc_int;
DROP FUNCTION sequence;
DROP FUNCTION lookup;
DROP FUNCTION reverse_lookup;
DROP FUNCTION avgcost;
The CREATE FUNCTION
and DROP FUNCTION
statements update the func
table in the mysql
system database that serves as a UDF registry. The function's name, type and shared library name are saved in the mysql.func
table. To create functions, you must have the INSERT
privilege for the mysql
database. To drop functions, you must have the DELETE
privilege for the mysql
database.
You cannnot use CREATE FUNCTION
to reinstall a function that has previously been installed. To reinstall a function, first remove it with DROP FUNCTION
, then install it with CREATE FUNCTION
. You would need to do this, for example, if you upgrade to a new version of MySQL that provides an updated implementation of the function, or you recompile a new version of a function that you have written. Otherwise, the server continues to use the old version.
An active function is one that has been loaded with CREATE FUNCTION
and not removed with DROP FUNCTION
. All active functions are reloaded each time the server starts, unless you start mysqld with the --skip-grant-tables
option. In this case, the server does not load UDFs during startup and UDFs are unavailable.
MySQL takes several measures to prevent misuse of user-defined functions.
UDF library files cannot be placed in arbitrary directories. They must be located in the server's plugin directory. This directory is given by the value of the plugin_dir
system variable.
To use CREATE FUNCTION
or DROP FUNCTION
, you must have the INSERT
or DELETE
privilege, respectively, for the mysql
database. This is necessary because those statements add and delete rows from the mysql.func
table.
UDFs should have at least one symbol defined in addition to the xxx
symbol that corresponds to the main xxx()
function. These auxiliary symbols correspond to the xxx_init()
, xxx_deinit()
, xxx_reset()
, xxx_clear()
, and xxx_add()
functions. mysqld also supports an --allow-suspicious-udfs
option that controls whether UDFs that have only an xxx
symbol can be loaded. By default, the option is disabled, to prevent attempts at loading functions from shared library files other than those containing legitimate UDFs. If you have older UDFs that contain only the xxx
symbol and that cannot be recompiled to include an auxiliary symbol, it may be necessary to specify the --allow-suspicious-udfs
option. Otherwise, you should avoid enabling it.