14.1 Ajouter une nouvelle fonction utilisateur

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().

Sommaire: