Managing & Using MySQL, 2nd Edition

Standard Functions

Create a standard UDF in three steps, two of which are optional:

init

When an SQL query calls a UDF, the init routine for that function is called first, if it exists. This routine is responsible for allocating data and setting certain parameters for the main routine.

main

After the init routine is called, the main routine for the desired UDF is called. This is the only routine required to exist to define a UDF. This routine is responsible for processing the data and returning the result of the function.

deinit

Once the main routine is finished and the result is returned to the user, MySQL will call a deinit routine for the function, if it exists. The purpose of this routine is to deallocate any memory allocated by the init routine and clean up as necessary.

When an SQL query calls a standard function, these routines are called in the following manner:

init
   main
   main
   main
   ...
deinit

MySQL calls the main routine once for each row returned by the SQL query. The return values of the main routine comprise the results of the UDF as a whole.

When creating a UDF, all three routines must reside in the same library. You can, however, bundle multiple functions in the same library.

Tip

We use the terms “routine” and “function” in a specific manner in this section to reduce the possibility of confusion. The problem is that multiple native functions make up a single UDF. When we refer to a routine, we mean one of the native functions that make up the UDF. When we refer ...