MySQL 5.1 Plugin Development: Extend MySQL to suit your needs with this unique guide into the world of MySQL plugins

UDFs can be of two types, normal and aggregate. Normal UDFs take inputs and deliver an output just like an ordinary function in most programming languages. When run on a set of rows, they will return a result for every row. Aggregate UDFs take a group of rows, process each row, and produce a result at the end. In other words, they will return one result per group. Therefore, aggregate functions are useful for tasks such as adding up a group of values or calculating an average.

Whether MySQL considers a given UDF as an aggregate or a normal type depends on how it was installed. However, the API is somewhat different too, and we need to take care to install UDFs that use the aggregate API as aggregate and normal UDFs as normal. Otherwise they will not only fail to work correctly, but may as well crash the whole server.

To show the basic construction of a UDF we will start with a simple constant function that returns an integer. The output of this UDF will be the same no matter what arguments are passed to the UDF. To make the example a little more complex and to demonstrate the initid->ptr usage we will allocate a small amount of memory upon initialization to store our integer. This example will be called udf_staticexample:

#include <stdlib.h>
#include <string.h>
#include <mysql.h>

These are the standard includes needed for this example. The header mysql.h contains the structures and constants that we will use.

my_bool udf_staticexample_init(UDF_INIT *initid,
UDF_ARGS *args, char *message)
{

We are calling this UDF udf_staticexample so all functions need to be prefixed with this. We start with the udf_staticexample_init() function, which as we have seen before, prepares the UDF for execution in the context of an SQL statement.

long long *staticint =...

The next example is designed to show how to deal with inputs. It is similar to the previous one, but will demonstrate how to perform argument checking. As you remember, this check should happen in the initialization function, so that we can give a proper error message when there is a problem. We will call this UDF, udf_intexample, and will make it accept only one argument, which has to be an integer for the function to succeed:

#include <string.h>
#include <mysql.h>
my_bool udf_intexample_init(UDF_INIT *initid,
UDF_ARGS *args, char *message)
{

As before, we include everything needed for this example and create an initialization function with the same prefix as the function name.

if (args->arg_count != 1)
{
strcpy(message,
"udf_intexample() can only accept one argument");
return 1;
}

We want to ensure that only one argument is accepted for this UDF so we check that arg_count is 1. If it is not, we stick an error message into the message buffer and return...

Returning text in a MySQL UDF can be slightly more complex than dealing with numbers. MySQL gives us a memory buffer to use when returning a string but it is only 766 bytes long. This is fine for some tasks, but not big enough to hold a long string or a blob, so what we do is allocate our own buffer for this purpose. Either way, we need to tell MySQL how long the resulting string is.

The next piece of example code has two UDFs inside it, the first showing how to use the memory buffer that MySQL has given us and the second allocating its own memory. Both are simple UDFs which will just return the MySQL version number that UDF was compiled against:

#include <stdlib.h>
#include <string.h>
#include <mysql.h>
my_bool udf_textexample_init(UDF_INIT *initid,
UDF_ARGS *args, char *message)
{
initid->const_item = 1;
return 0;
}

As with previous examples we create the initialization function. We set initid->const_item to 1 here because the return...

As discussed earlier in this chapter, aggregate UDFs are great for performing operations on groups of rows. Things work slightly differently here, we have a new keyword to use when installing UDFs and two extra functions to deal with individual rows and to clean up after every group of rows.

Let's write an aggregating UDF that will add up floating point numbers. We will call it udf_floatsum:

#include <stdlib.h>
#include <string.h>
#include <mysql.h>
my_bool udf_floatsum_init(UDF_INIT *initid, UDF_ARGS *args,
char *message)
{
double* float_total = malloc(sizeof(double));
*float_total = 0;
initid->ptr = (char*) float_total;

To aggregate we need an accumulator, a variable of the type double to keep the running totals as we are seeing rows one by one. Different queries invoking our UDF must have different running totals, in other words, initid->ptr is exactly what's needed. We allocate memory for one double and save a pointer to it in initid...

There is a UDF repository run by Roland Bouman that contains various useful functions in both source and binary forms. This can be found at http://www.mysqludf.org/.

To make the UDF API even easier to use, Hartmut Holzgraefe, a MySQL Support Manager at Sun Microsystems, has created a UDF generator. This generates the source code for you based on an XML description of what the UDF should do. More information on this can be found at its PECL site at: http://pear.php.net/package/CodeGen_MySQL_UDF/

We can now see that writing a UDF is not too complex. MySQL has a lot of built-in functionality to help with the task. UDFs make a good compromise between easy and limited Stored Procedures and the ultimate power and complexity of modifying the source code of the MySQL server.