(mysql.info.gz) Adding UDF
Info Catalog
(mysql.info.gz) CREATE FUNCTION
(mysql.info.gz) Adding functions
(mysql.info.gz) Adding native function
25.2.2 Adding a New User-defined Function
-----------------------------------------
Menu
* UDF calling UDF Calling Sequences for simple functions
* UDF aggr. calling UDF Calling Sequences for aggregate functions
* UDF arguments Argument Processing
* UDF return values Return Values and Error Handling
* UDF compiling Compiling and Installing User-defined Functions
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 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 type and return type of your
C/C++ function is shown here:
*SQL *C/C++
Type* 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 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, 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.
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
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()'.
Info Catalog
(mysql.info.gz) CREATE FUNCTION
(mysql.info.gz) Adding functions
(mysql.info.gz) Adding native function
automatically generated byinfo2html