Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

27.3.4. Adding a New User-Defined 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.

A UDF contains code that becomes part of the running server, so when you write a UDF, you are bound by any and all constraints that otherwise apply to writing server code. For example, you may have problems if you attempt to use functions from the libstdc++ library. Note that these constraints may change in future versions of the server, so it is possible that server upgrades will require revisions to UDFs that were originally written for older servers. For information about these constraints, see Section 2.8.2, “Typical configure Options”, and Section 2.8.4, “Dealing with Problems Compiling MySQL”.

To be able to use UDFs, you need to link mysqld dynamically. Don't configure MySQL using --with-mysqld-ldflags=-all-static. If you want to use a UDF that needs to access symbols from mysqld (for example, the metaphone function in sql/udf_example.cc that uses default_charset_info), you must link the program with -rdynamic (see man dlopen). If you plan to use UDFs, the rule of thumb is to configure MySQL with --with-mysqld-ldflags=-rdynamic unless you have a very good reason not to.

If you must use a precompiled distribution of MySQL, use MySQL-Max, which contains a dynamically linked server that 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 function data type and the return type of your C/C++ function is shown here:

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

    It is also possible to declare a DECIMAL function, but currently the value is returned as a string, so you should write the UDF as though it were a STRING function.

  • xxx_init() (optional)

    The initialization function for xxx(). It can be used for the following purposes:

    • To check the number of arguments to XXX().

    • To check that the arguments are of a required type or, alternatively, to tell MySQL to coerce arguments to the types you want when the main function is called.

    • To allocate any memory required by the main function.

    • To specify the maximum length of the result.

    • To specify (for REAL functions) the maximum number of decimal places in the result.

    • To 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, MySQL aborts the SQL statement with an error message and does not call the main or deinitialization functions. Otherwise, MySQL calls the main function xxx() once for each row. After all rows have been processed, MySQL calls the deinitialization function xxx_deinit() so that it can perform any required cleanup.

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

  • xxx_clear() (required in 5.1)

    Reset the current aggregate value but do not insert the argument as the initial aggregate value for a new group.

  • xxx_add() (required)

    Add the argument to the current aggregate value.

MySQL handles aggregate UDFs as follows:

  1. Call xxx_init() to let the aggregate function allocate any memory it needs for storing results.

  2. Sort the table according to the GROUP BY expression.

  3. Call xxx_clear() for the first row in each new group.

  4. Call xxx_add() for each new row that belongs in the same group.

  5. Call xxx() to get the result for the aggregate when the group changes or after the last row has been processed.

  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. This includes not just the main function, but the initialization and deinitialization functions as well, and also the additional functions required by aggregate functions. A consequence of this requirement is 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().


 
 
  Published under the terms of the GNU General Public License Design by Interspire