29.4.2 Adding a New 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.

extern "C" { ... }

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 Section 29.4.2.6, “UDF Security Precautions”.

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:

MySQL handles aggregate UDFs as follows:

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 should allocate it in xxx_init() and free it in xxx_deinit().