14 Adding new functions to MySQL

There are two ways to add new functions to MySQL:

• You can add the function through the user-definable function (UDF) interface. User-definable functions are added and removed dynamically using the CREATE FUNCTION and DROP FUNCTION statements. See section 7.30 CREATE FUNCTION/DROP FUNCTION syntax.
• You can add the function as a native (built in) MySQL function. Native functions are compiled into the mysqld server and become available on a permanent basis.

Each method has advantages and disadvantages:

• If you write a user-definable function, you must install the object file in addition to the server itself. If you compile your function into the server, you don't need to do that.
• You can add UDFs to a binary MySQL distribution. Native functions require you to modify a source distribution.
• If you upgrade your MySQL distribution, you can continue to use your previously-installed UDFs. For native functions, you must repeat your modifications each time you upgrade.

Whichever method you use to add new functions, they may be used just like native functions such as ABS() or SOUNDEX().

14.1 Adding a new user-definable 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.

For each function that you want to use in SQL statements, you should define corresponding C (or C++) functions. In the discussion below, the name ``xxx'' is used for an example function name. To distinquish between SQL and C/C++ usage, XXX() (uppercase) indicates a SQL function call, and xxx() (lowercase) indicates a C/C++ function call.

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

xxx() (required)

The main function. This is where the function result is computed. The correspondence between the SQL type and return type of your C/C++ function is shown below:

SQL type	C/C++ type
STRING	char *
INTEGER	long long
REAL	double

xxx_init() (optional)

The initialization function for xxx(). It can be used to:

• Check the number of arguments to XXX()
• Check that the arguments are of a required type, or, alternatively, tell MySQL to coerce arguments to the types you want when the main function is called
• Allocate any memory required by the main function
• Specify the maximum length of the result
• Specify (for REAL functions) the maximum number of decimals
• Specify whether or not the result can be NULL

xxx_deinit() (optional)

The deinitialization function for xxx(). It should deallocate any memory allocated by the initialization function.

When a 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, the SQL statement is aborted with an error message and the main and deinitialization functions are not called. Otherwise, the main function xxx() is called once for each row. After all rows have been processed, the deinitialization function xxx_deinit() is called so it can perform any required cleanup.

All functions must be thread-safe (not just the main function, but the initialization and deinitialization functions as well). This means 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().

14.1.1 UDF calling sequences

The main function should be declared as shown below. Note that the return type and parameters differ, depending on whether you will 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 are listed below. 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

Number of decimals. The default value is the maximum number of decimals 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, since 1.345 has 3 decimals.

unsigned int max_length

The maximum length of the string result. The default 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 decimals indicated by initid->decimals. (For numeric functions, the length includes any sign or decimal point characters.)

char *ptr

A pointer that the function can use for its own purposes. For example, functions can use initid->ptr to communicate allocated memory between functions. In xxx_init(), 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.

14.1.2 Argument processing

The args parameter points to a UDF_ARGS structure which has the members listed below:

unsigned int arg_count

The number of arguments. Check this value in the initialization function if you want 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

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 coercion of the first two arguments to string and integer, 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 your function was called with. 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. 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).

14.1.3 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 will be 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. For string functions, the string is returned in the result and length arguments. result is a buffer at least 255 bytes long. Set these to the contents and length of the return value. For example:

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

The string function return value normally also points to the result.

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 the error parameter 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() will not even be called for subsequent rows.) Note: In MySQL versions prior to 3.22.10, you should set both *error and *is_null:

*error = 1;
*is_null = 1;

14.1.4 Compiling and installing user-definable 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 `udf_example.cc' that is included in the MySQL source distribution. This 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.
• lookup() returns the IP number for a hostname.
• reverse_lookup() returns the hostname for an IP number. The function may be called with a string "xxx.xxx.xxx.xxx" or 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 myfunc.cc

You can easily find out 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.)

Once 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 searched by ld, such as `/usr/lib'. On many systems, you can set the LD_LIBRARY or LD_LIBRARY_PATH environment variable to point at the directory where you have your UDF function files. The dopen manual page tells you which variable you should use on your system. You should set this in mysql.server or safe_mysqld and restart mysqld.

After the library is installed, notify mysqld about the new functions with these commands:

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";

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;

The CREATE FUNCTION and DROP FUNCTION statements update the system table func 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 already 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 will continue to use the old version.

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. (An active function is one that has been loaded with CREATE FUNCTION and not removed with DROP FUNCTION.)

14.2 Adding a new native function

The procedure for adding a new native function is described below. Note that you cannot add native functions to a binary distribution since 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 (e.g., when a new version is released), you will 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. 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 SOUNDEX 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)
   

5. You should probably also define the following 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 if any of the function arguments can return NULL by checking the arguments maybe_null variable.

All functions must be thread-safe.

For string 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.
• The function should return the string that holds the result.
• All current string functions try to avoid allocating any memory unless absolutely necessary!

Go to the first, previous, next, last section, table of contents.