An Overview of Installing UDFs

Because the MySQL source distribution contains a C file with a number of sample UDFs, you can easily start experimenting with UDFs right away. For this reason, you will learn about installing UDFs first. If you also have a knowledge of C and want to learn how to write your own functions, you will learn how to do this a little later today.

Preparing mysqld

To use UDFs, your operating system must support dynamic loading (which will normally be the case with Unix and Linux).

Your mysqld should also have been compiled using --with-mysqld-ldflags=-rdynamic as an option to configure. So when running configure, the options would include (among other options):

# ./configure --with-mysqld-ldflags=-rdynamic
						

You may therefore have to recompile mysqld to use UDFs (see Day 2, “Installing MySQL,” for more information).

An Overview of Using UDFs

We'll get on to the nitty-gritty of installing UDFs in a moment, but first let's get an overview of what you will achieve with user-definable functions.

You're trying to create a function that you can call with all the convenience of any other MySQL function. You will be able to call your function using, for example, MYFUNC() in your SQL.

To implement a UDF called MYFUNC() requires a C or C++ program containing a set of function calls with names like myfunc() and myfunc_init().

(It's important that the names correspond. The SQL function will usually be referred to in uppercase, although like other functions in SQL it can be called in lowercase too. The C or C++ functions will always be referred to in lowercase.)

Just like other MySQL functions, your user-definable function may return a result that is of type STRING, INTEGER, or REAL.

Simple Functions and Their Definition in C or C++

A ”simple” UDF is a function that does not aggregate across rows of a SELECT resultset. It takes only constants or column names as arguments and returns a result based only on those values. It's a function like DATE_FORMAT().

For a simple UDF, the C or C++ program that defines it will have between one and three functions:

• An optional function called myfunc_init(), which checks the arguments that have been passed, allocates memory if need be, and specifies what sort of output the function returns (whether it can be NULL, the maximum length, and so on)

• A mandatory function called myfunc(), which does the actual computation

• An optional function called myfunc_deinit(), which releases any memory allocated by myfunc_init()

Aggregating Functions and Their Definition in C or C++

An aggregating function is a little more complex. In MySQL, an aggregating function always works in the context of a SELECT...GROUP BY query. For example, the SUM() aggregating function sums the values across several rows, returning one result for every “grouped” set of values.

Because of this, when we write an aggregating UDF, a C/C++ function needs to be called to process each row being analyzed.

(For example, in the case of SUM(), it would be adding up the number in the current row to some interim running total.)

Each time the GROUP BY aggregator decides that the end of a group has been reached, it expects that a result row be returned. Therefore, a different routine in the SUM() function (likewise, in an aggregating UDF) needs to be called so that it returns its overall result.

Aggregating functions therefore have some additional C/C++ function calls. In addition to those stated previously for simple functions, an aggregating UDF must have the following:

• A function called myfunc_reset(), which resets the UDF's variables and stores the first argument as the initial value of a new group

• A function called myfunc_add(), which takes subsequent arguments in the group and sums or otherwise processes them, maintaining variables that pertain to the arguments from the group so far

We'll look in more detail at these C/C++ routines in a moment. But before going into this, you should learn about how to make these functions available to MySQL.

Making a Function Available to MySQL

After a function has been written in C or C++, you'll need to tell the MySQL server to load it. MySQL has the CREATE FUNCTION and DROP FUNCTION commands for creating and dropping UDFs.

The syntax for creating a function is as follows:

CREATE [AGGREGATE] FUNCTION function_name
RETURNS {STRING|REAL|INTEGER}
SONAME 'shared_object_name'

When you run CREATE FUNCTION, it enters details of that function (contained in the binary object given by shared_object_name) into the func table in the mysql database. The optional keyword AGGREGATE tells MySQL that the function is an aggregating one (as opposed to simple), and the RETURNS part specifies the datatype that will be returned when the function is called.

When created like this, a user-defined function persists even if the server is shut down and restarted. It should be considered active and available for use as soon as the command has been run and remains active until the function is dropped.

DROP FUNCTION has the effect of dropping a function:

DROP FUNCTION function_name
						

This makes the function immediately inactive, though will not destroy the shared object code.

# cd /usr/local/mysql-4.0.2-alpha/scripts
							

# ./mysql_fix_privilege_tables
							

mysql> desc func;

+-------+------------------------------+------+------+----------+-------+
| Field | Type                         | Null | Key  | Default  | Extra |
+-------+------------------------------+------+------+----------+-------+
| name  | char(64) binary              |      | PRI  |          |       |
| ret   | tinyint(1)                   |      |      | 0        |       |
| dl    | char(128)                    |      |      |          |       |
| type  | enum('function','aggregate') |      |      | function |       |
+-------+------------------------------+-------+-----+----------+-------+

4 rows in set (0.00 sec)