Search the MySQL manual:

12.2.2 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 mysqld to be able to use UDF functions, you should configure MySQL with --with-mysqld-ldflags=-rdynamic The reason is that to on many platforms (including Linux) you can load a dynamic library (with dlopen()) from a static linked program, which you would get if you are using --with-mysqld-ldflags=-all-static If you want to use an UDF that needs to access symbols from mysqld (like the metaphone example in `sql/udf_example.cc' that uses default_charset_info), you must link the program with -rdynamic (see man dlopen).

If you are using a precompiled version of the server, use MySQL-Max, which supports dynamic loading.

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 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 type and return type of your C/C++ function is shown here:
SQL type C/C++ type
STRING char *
INTEGER long long
REAL double
xxx_init() (optional)
The initialisation 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 the result can be NULL.
xxx_deinit() (optional)
The deinitialisation function for xxx(). It should deallocate any memory allocated by the initialisation function.

When an SQL statement invokes XXX(), MySQL calls the initialisation 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 deinitialisation functions are not called. Otherwise, the main function xxx() is called once for each row. After all rows have been processed, the deinitialisation function xxx_deinit() is called so it can perform any required cleanup.

For aggregate functions (like SUM()), you must also provide the following functions:

xxx_reset() (required)
Reset sum and insert the argument as the initial value for a new group.
xxx_add() (required)
Add the argument to the old sum.

When using aggregate UDFs, MySQL works the following way:

  1. Call xxx_init() to let the aggregate function allocate the memory it will need to store results.
  2. Sort the table according to the GROUP BY expression.
  3. For the first row in a new group, call the xxx_reset() function.
  4. For each new row that belongs in the same group, call the xxx_add() function.
  5. When the group changes or after the last row has been processed, call xxx() to get the result for the aggregate.
  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 (not just the main function, but the initialisation and deinitialisation 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().

Subsections

User Comments

Add your own comment.