22.3.3 Adding a New Native Function

To add a new native MySQL function, use the procedure described
here, which requires that you use a source distribution. You
cannot add native functions to a binary distribution because it
is necessary to modify MySQL source code and compile MySQL from
the modified source. If you migrate to another version of MySQL
(for example, when a new version is released), you must repeat
the procedure with the new version.

If the new native function will be referred to in statements
that will be replicated to slave servers, you must ensure that
every slave server also has the function available. Otherwise,
replication will fail on the slaves when they attempt to invoke
the function.

To add a new native function, follow these steps to modify
source files in the sql directory. For
MySQL 5.1, the first two steps apply only as of 5.1.13. For
older versions, see the instructions in the corresponding
section of the MySQL 5.0 manual.

Create a subclass for the function in
item_create.cc:

If the function takes a fixed number of arguments,
create a subclass of
Create_func_arg0,
Create_func_arg1,
Create_func_arg2, or
Create_func_arg3, respectively,
depending on whether the function takes zero, one, two,
or three arguments. For examples, see the
Create_func_uuid,
Create_func_abs,
Create_func_pow, and
Create_func_lpad classes.

If the function takes a variable number of arguments,
create a subclass of
Create_native_func. For an example,
see Create_func_concat.

To provide a name by which the function can be referred to
in SQL statements, register the name in
item_create.cc by adding a line to this
array:

static Native_func_registry func_array[]

You can register several names for the same function. For
example, see the lines for "LCASE" and
"LOWER", which are aliases for
Create_func_lcase.

In item_func.h, declare a class
inheriting from Item_num_func or
Item_str_func, depending on whether your
function returns a number or a string.

In item_func.cc, add one of the
following declarations, depending on whether you are
defining a numeric or string function:

If you inherit your object from any of the standard items
(like Item_num_func), you probably only
have to define one of these functions and let the parent
object take care of the other functions. For example, the
Item_str_func class defines a
val() function that executes
atof() on the value returned by
::str().

If the function is nondeterministic, include the following
statement in the item constructor to indicate that function
results should not be cached:

current_thd->lex->safe_to_cache_query=0;

A function is nondeterministic if, given fixed values for
its arguments, it can return different results for different
invocations.

You should probably also define the following object
function:

void Item_func_newname::fix_length_and_dec()

This function should at least calculate
max_length based on the given arguments.
max_length is the maximum number of
characters the function may return. This function should
also set maybe_null = 0 if the main
function can't return a NULL value. The
function can check whether any of the function arguments can
return NULL by checking the arguments'
maybe_null variable. Look at
Item_func_mod::fix_length_and_dec for a
typical example of how to do this.

All functions must be thread-safe. In other words, do not use
any global or static variables in the functions without
protecting them with mutexes.

If you want to return NULL from
::val(), ::val_int(), or
::str(), you should set
null_value to 1 and return 0.

For ::str() object functions, there are
additional considerations to be aware of:

The String *str argument provides a
string buffer that may be used to hold the result. (For more
information about the String type, take a
look at the sql_string.h file.)

The ::str() function should return the
string that holds the result, or (char*)
0 if the result is NULL.

All current string functions try to avoid allocating any
memory unless absolutely necessary!