24.2. Adding New Functions to MySQL

There are two ways to add new functions to MySQL:

Each method has advantages and disadvantages:

Whichever method you use to add new functions, they can be invoked in SQL statements just like native functions such as ABS() or SOUNDEX().

Another way to add functions is by creating stored functions. These are written using SQL statements rather than by compiling object code. The syntax for writing stored functions is described in Chapter 17, Stored Procedures and Functions.

The following sections describe features of the UDF interface, provide instructions for writing UDFs, discuss security precautions that MySQL takes to prevent UDF misuse, and describe how to add native mySQL functions.

For example source code that illustrates how to write UDFs, take a look at the sql/udf_example.cc file that is provided in MySQL source distributions.

24.2.1. Features of the User-Defined Function Interface

The MySQL interface for user-defined functions provides the following features and capabilities:

  • Functions can return string, integer, or real values.

  • You can define simple functions that operate on a single row at a time, or aggregate functions that operate on groups of rows.

  • Information is provided to functions that enables them to check the number and types of the arguments passed to them.

  • You can tell MySQL to coerce arguments to a given type before passing them to a function.

  • You can indicate that a function returns NULL or that an error occurred.

24.2.2. CREATE FUNCTION Syntax

    SONAME shared_library_name

A user-defined function (UDF) is a way to extend MySQL with a new function that works like a native (built-in) MySQL function such as ABS() or CONCAT().

function_name is the name that should be used in SQL statements to invoke the function. The RETURNS clause indicates the type of the function's return value. As of MySQL 5.0.3, DECIMAL is a legal value after RETURNS, but currently DECIMAL functions return string values and should be written like STRING 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 a directory that is searched by your system's dynamic linker.

To create a function, you must have the INSERT and privilege for the mysql database. This is necessary because CREATE FUNCTION adds a row to the mysql.func system table that records the function's name, type, and shared library name. If you do not have this table, you should run the mysql_upgrade command to create it. See Section 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.

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, UDF initialization is skipped and UDFs are unavailable.

For instructions on writing user-defined functions, see Section 24.2.4, “Adding a New User-Defined Function”. For the UDF mechanism to work, functions must be written in C or C++, your operating system must support dynamic loading and you must have compiled mysqld dynamically (not statically).

An AGGREGATE function works exactly like a native MySQL aggregate (summary) function such as SUM or COUNT(). For AGGREGATE to work, your mysql.func table must contain a type column. If your mysql.func table does not have this column, you should run the mysql_upgrade program to create it (see Section 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”).

24.2.3. DROP FUNCTION Syntax

DROP FUNCTION function_name

This statement drops the user-defined function (UDF) named function_name.

To drop a function, you must have the DELETE privilege for the mysql database. This is because DROP FUNCTION removes a row from the mysql.func system table that records the function's name, type, and shared library name.

24.2.4. 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. The MySQL source distribution includes a file sql/udf_example.cc that defines 5 new functions. Consult this file to see how UDF calling conventions work.

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 otherwise apply to writing server code. For example, you may have problems if you attempt to use functions from the libstdc++ library. Note that 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.2, “Typical configure Options”, and Section 2.9.4, “Dealing with Problems Compiling MySQL”.

To be able to use UDFs, you need to link mysqld dynamically. Don't configure MySQL using --with-mysqld-ldflags=-all-static. If you want to use a UDF that needs to access symbols from mysqld (for example, the metaphone function in sql/udf_example.cc that uses default_charset_info), you must link the program with -rdynamic (see man dlopen). If you plan to use UDFs, the rule of thumb is to configure MySQL with --with-mysqld-ldflags=-rdynamic unless you have a very good reason not to.

If you must use a precompiled distribution of MySQL, use MySQL-Max, which contains a dynamically linked server that supports dynamic loading.

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.

The C/C++ functions that you write to implement the interface for XXX() are:

  • xxx() (required)

    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:

    SQL TypeC/C++ Type
    STRINGchar *
    INTEGERlong long

    It is also possible to declare a DECIMAL function, but currently the value is returned as a string, so you should write the UDF as though it were a STRING function.

  • xxx_init() (optional)

    The initialization function for xxx(). It can be used for the following purposes:

    • To check the number of arguments to XXX().

    • To check that the arguments are of a required type or, alternatively, to tell MySQL to coerce arguments to the types you want 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() (optional)

    The deinitialization function for xxx(). 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() (required in 5.0)

    Reset the current aggregate value but do not insert the argument as the initial aggregate value for a new group.

  • xxx_add() (required)

    Add the argument to the current aggregate value.

MySQL handles aggregate UDFs as follows:

  1. Call xxx_init() to let the aggregate function allocate any memory it needs for storing results.

  2. Sort the table according to the GROUP BY expression.

  3. Call xxx_clear() for the first row in each new group.

  4. Call xxx_add() for each new row that belongs in the same group.

  5. Call xxx() to get the result for the aggregate when the group changes or after the last row has been processed.

  6. Repeat 3-5 until all rows has been processed

  7. 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 allowed 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(). UDF Calling Sequences for Simple Functions

This section describes the different functions that you need to define when you create a simple UDF. Section 24.2.4, “Adding a New User-Defined Function”, describes the order in which MySQL calls these 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);

The initialization and deinitialization functions are declared 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 set maybe_null to 1 if xxx() can return NULL. The default value is 1 if any of the arguments are declared maybe_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, and 1.3, the default would be 3, because 1.345 has 3 decimal digits.

  • 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 by initid->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() and xxx_deinit(), refer to initid->ptr to use or deallocate the memory.

  • my_bool const_item

    xxx_init() should set const_item to 1 if xxx() always returns the same value and to 0 otherwise. UDF Calling Sequences for Aggregate Functions

This section describes the different functions that you need to define when you create an aggregate UDF. Section 24.2.4, “Adding a New User-Defined Function”, describes the order in which MySQL calls these 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. Declare xxx_reset() as follows:

    char *xxx_reset(UDF_INIT *initid, UDF_ARGS *args,
                    char *is_null, char *error);

    xxx_reset() is not needed or used in MySQL 5.0, in which the UDF interface uses xxx_clear() instead. However, you can define both xxx_reset() and xxx_clear() if you want to have your UDF work with older versions of the server. (If you do include both functions, the xxx_reset() function in many cases can be implemented internally by calling xxx_clear() to reset all variables, and then calling xxx_add() to add the UDF_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:

    char *xxx_clear(UDF_INIT *initid, char *is_null, char *error);

    is_null is set to point to CHAR(0) before calling xxx_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.0.

  • xxx_add()

    This function is called for all rows that belong to the same group, except for the first row. You should use it to add the value in the UDF_ARGS argument to your internal summary variable.

    char *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 non-aggregate UDF. See Section, “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 non-aggregate UDF. See Section, “UDF Return Values and Error Handling”.

The xxx_reset() and xxx_add() functions handle their UDF_ARGS argument the same way as functions for non-aggregate UDFs. See Section, “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. UDF Argument Processing

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;
  • 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, and REAL_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;

    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 to xxx(). For example, to specify that the first two arguments should be coerced to string and integer, respectively, do this in xxx_init():

    args->arg_type[0] = STRING_RESULT;
    args->arg_type[1] = INT_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 argument i, args->args[i] points to the argument value. (See below for instructions on how to access the value properly.) For a non-constant argument, args->args[i] is 0. A constant argument is an expression that uses only constants, such as 3 or 4*7-2 or SIN(3.14). A non-constant 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 non-constant arguments.

    For each invocation of the main function, args->args contains the actual arguments that are passed for the row currently being processed.

    Functions can refer to an argument i as follows:

    • An argument of type STRING_RESULT is given as a string pointer plus a length, to allow handling of binary data or data of arbitrary length. The string contents are available as args->args[i] and the string length is args->lengths[i]. You should not assume that strings are null-terminated.

    • For an argument of type INT_RESULT, you must cast args->args[i] to a long long value:

      long long int_val;
      int_val = *((long long*) args->args[i]);
    • For an argument of type REAL_RESULT, you must cast args->args[i] to a double value:

      double    real_val;
      real_val = *((double*) args->args[i]);
  • 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 types INT_RESULT or REAL_RESULT, lengths still contains the maximum length of the argument (as for the initialization function). UDF Return Values and Error Handling

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, but you should 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 *result and *length to the contents and length of the return value. For example:

memcpy(result, "result string", 13);
*length = 13;

The result buffer that is passed to the xxx() function is 255 bytes long. If your result fits in this, you don't have to worry about memory allocation for results.

If your string function needs to return a string longer than 255 bytes, you must allocate the space for it with malloc() in your xxx_init() function or your 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 Section, “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.) Compiling and Installing User-Defined Functions

Files implementing UDFs must be compiled and installed on the host where the server runs. This process is described below for the example UDF file sql/udf_example.cc that is included in the MySQL source distribution.

The immediately following instructions are for Unix. Instructions for Windows are given later in this section.

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's 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 number for a hostname.

  • reverse_lookup() returns the hostname for an IP number. The function may be called either with a single string argument of the form 'xxx.xxx.xxx.xxx' or with four numbers.

A dynamically loadable file should be compiled as a sharable object file, using a command something like this:

shell> gcc -shared -o udf_example.so udf_example.cc

If you are using gcc, you should be able to create udf_example.so with a simpler command:

shell> make udf_example.so

You can easily determine the correct compiler options for your system by running this command in the sql directory of your MySQL source tree:

shell> make udf_example.o

You should run a compile command similar to the one that make displays, except that you should remove the -c option near the end of the line and add -o udf_example.so to the end of the line. (On some systems, you may need to leave the -c on the command.)

After you compile a shared object containing UDFs, you must install it and tell MySQL about it. Compiling a shared object from udf_example.cc produces a file named something like udf_example.so (the exact name may vary from platform to platform). Copy this file to some directory such as /usr/lib that searched by your system's dynamic (runtime) linker, or add the directory in which you placed the shared object to the linker configuration file (for example, /etc/ld.so.conf).

The dynamic linker name is system-specific (for example, ld-elf.so.1 on FreeBSD, ld.so on Linux, or dyld on Mac OS X). Consult your system documentation for information about the linker name and how to configure it.

On many systems, you can also set the LD_LIBRARY or LD_LIBRARY_PATH environment variable to point at the directory where you have the files for your UDF. The dlopen manual page tells you which variable you should use on your system. You should set this in mysql.server or mysqld_safe startup scripts and restart mysqld.

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, you can compile user-defined functions by using the following procedure:

  1. You need to obtain the BitKeeper source repository for MySQL 5.0. See Section 2.9.3, “Installing from the Development Source Tree”.

  2. In the source repository, look in the VC++Files/examples/udf_example directory. There are files named udf_example.def, udf_example.dsp, and udf_example.dsw there.

  3. In the source repository, look in the sql directory. Copy the udf_example.cc from this directory to the VC++Files/examples/udf_example directory and rename the file to udf_example.cpp.

  4. Open the udf_example.dsw file with Visual Studio VC++ and use it to compile the UDFs as a normal project.

After the shared object file has been installed, notify mysqld about the new functions with these statements:

mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';
mysql> CREATE FUNCTION myfunc_double RETURNS REAL SONAME 'udf_example.so';
mysql> CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME 'udf_example.so';
mysql> CREATE FUNCTION lookup RETURNS STRING SONAME 'udf_example.so';
mysql> CREATE FUNCTION reverse_lookup
    ->        RETURNS STRING SONAME 'udf_example.so';
    ->        RETURNS REAL SONAME 'udf_example.so';

Functions can be deleted using DROP FUNCTION:

mysql> DROP FUNCTION metaphon;
mysql> DROP FUNCTION myfunc_double;
mysql> DROP FUNCTION myfunc_int;
mysql> DROP FUNCTION lookup;
mysql> DROP FUNCTION reverse_lookup;
mysql> DROP FUNCTION avgcost;

The CREATE FUNCTION and DROP FUNCTION statements update the func system table in the mysql database. The function's name, type and shared library name are saved in the table. You must have the INSERT and DELETE privileges for the mysql database to create and drop functions.

You should not use CREATE FUNCTION to add a function that has previously been created. If you need to reinstall a function, you should remove it with DROP FUNCTION and then reinstall it with CREATE FUNCTION. You would need to do this, for example, if you recompile a new version of your function, so that mysqld gets the new version. 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, UDF initialization is skipped and UDFs are unavailable. User-Defined Function Security Precautions

MySQL takes the following measures to prevent misuse of user-defined functions.

You must have the INSERT privilege to be able to use CREATE FUNCTION and the DELETE privilege to be able to use DROP FUNCTION. This is necessary because these 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. As of MySQL 5.0.3, mysqld supports an --allow-suspicious-udfs option that controls whether UDFs that have only an xxx symbol can be loaded. By default, the option is off, to prevent attempts at loading functions from shared object 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 this capability.

UDF object files cannot be placed in arbitrary directories. They must be located in some system directory that the dynamic linker is configured to search. To enforce this restriction and prevent attempts at specifying pathnames outside of directories searched by the dynamic linker, MySQL checks the shared object file name specified in CREATE FUNCTION statements for pathname delimiter characters. As of MySQL 5.0.3, MySQL also checks for pathname delimiters in filenames stored in the mysql.func table when it loads functions. This prevents attempts at specifying illegitimate pathnames through direct manipulation of the mysql.func table. For information about UDFs and the runtime linker, see Section, “Compiling and Installing User-Defined Functions”.

24.2.5. Adding a New Native Function

The procedure for adding a new native function is described here. Note that you cannot add native functions to a binary distribution because the procedure involves modifying MySQL source code. You must compile MySQL yourself from a source distribution. Also note that if you migrate to another version of MySQL (for example, when a new version is released), you need to repeat the procedure with the new version.

To add a new native MySQL function, follow these steps:

  1. Add one line to lex.h that defines the function name in the sql_functions[] array.

  2. If the function prototype is simple (just takes zero, one, two or three arguments), you should in lex.h specify SYM(FUNC_ARGN) (where N is the number of arguments) as the second argument in the sql_functions[] array and add a function that creates a function object in item_create.cc. Take a look at "ABS" and create_funcs_abs() for an example of this.

    If the function prototype is complicated (for example, if it takes a variable number of arguments), you should add two lines to sql_yacc.yy. One indicates the preprocessor symbol that yacc should define (this should be added at the beginning of the file). Then define the function parameters and add an “item” with these parameters to the simple_expr parsing rule. For an example, check all occurrences of ATAN in sql_yacc.yy to see how this is done.

  3. In item_func.h, declare a class inheriting from Item_num_func or Item_str_func, depending on whether your function returns a number or a string.

  4. In item_func.cc, add one of the following declarations, depending on whether you are defining a numeric or string function:

    double   Item_func_newname::val()
    longlong Item_func_newname::val_int()
    String  *Item_func_newname::Str(String *str)

    If you inherit your object from any of the standard items (like Item_num_func), you probably only have to define one of these functions and let the parent object take care of the other functions. For example, the Item_str_func class defines a val() function that executes atof() on the value returned by ::str().

  5. You should probably also define the following object function:

    void Item_func_newname::fix_length_and_dec()

    This function should at least calculate max_length based on the given arguments. max_length is the maximum number of characters the function may return. This function should also set maybe_null = 0 if the main function can't return a NULL value. The function can check whether any of the function arguments can return NULL by checking the arguments' maybe_null variable. You can take a look at Item_func_mod::fix_length_and_dec for a typical example of how to do this.

All functions must be thread-safe. In other words, don't use any global or static variables in the functions without protecting them with mutexes)

If you want to return NULL, from ::val(), ::val_int() or ::str() you should set null_value to 1 and return 0.

For ::str() object functions, there are some additional considerations to be aware of:

  • The String *str argument provides a string buffer that may be used to hold the result. (For more information about the String type, take a look at the sql_string.h file.)

  • The ::str() function should return the string that holds the result or (char*) 0 if the result is NULL.

  • All current string functions try to avoid allocating any memory unless absolutely necessary!