CREATE FUNCTION syntax

CREATE FUNCTION <function_name> RETURNS [string|real|integer]
       SONAME <name_of_shared_library>

DROP FUNCTION <function_name>

User definable functions (UDF) is way to extend MySQL with new functions that works as native MySQL functions like ABS() and concat(). UDF's are written in C or C++ and require that dynamic loading works on the operating system. The source distribution includes the file `udf_example.cc' that defines 5 new functions.

The functions name, type and shared library is saved in the new system table 'func' in the 'mysql' database. To be able to create new functions one must have write privilege for the database 'mysql'. If one starts MySQL with --skip-grant-tables, then UDF initialization will also be skipped.

Each defined function may have a xxxx_init function and a xxxx_deinit function. The init function should alloc memory for the function and tell the main function about the max length of the result (for string functions), number of decimals (for double functions) and if the result may be a null value.

If a function sets the 'error' argument to 1 the function will not be called anymore and mysqld will return NULL for all calls to this instanse of the function.

All strings arguments to functions are given as string pointer + length to allow handling of binary data. Remember that all functions must be thread safe. This means that one is not allowed to alloc any global or static variables that changes! If one needs memory one should alloc this in the init function and free this on the __deinit function.

A dynamicly loadable file should be compiled sharable (something like: gcc -shared -o udf_example.so myfunc.cc). You can easily get all switches right by doing: cd sql ; make udf_example.o Take the compile line that make writes, remove the '-c' near the end of the line and add -o udf_example.so to the end of the compile line. The resulting library (udf_example.so) should be copied to some dir searched by ld, for example /usr/lib.

Some notes about the example functions:

• Function metaphon returns a metaphon string of the string argument. This is something like a soundex string, but it's more tuned for English.
• Function myfunc_double returns summary of codes of all letters of arguments divided by summary length of all its arguments.
• Function myfunc_int returns summary length of all its arguments.
• Function lookup returns the IP number for an hostname.
• Function reverse_lookup returns the hostname for a IP number. The function may be called with a string "xxx.xxx.xxx.xxx" or four numbers.

After the library is installed one must notify mysqld about the new functions with the commands:

CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so";
CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so";
CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so";
CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so";
CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "udf_example.so";

Functions should be created only once. The functions can be deleted by:

DROP FUNCTION metaphon;
DROP FUNCTION myfunc_double;
DROP FUNCTION myfunc_int;
DROP FUNCTION lookup;
DROP FUNCTION reverse_lookup;

The CREATE FUNCTION and DROP FUNCTION update the func table. All active function will be reloaded on every restart of server (if --skip-grant-tables is not given).

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