Skip navigation links
25.4 Adding New Procedures to MySQL »
Section Navigation      [Toggle]

25.3.5. Adding a New Native Function

The procedure for adding a new native function is described here. Note that you cannot add native functions to a binary distribution because the procedure involves modifying MySQL source code. You must compile MySQL yourself from a source distribution. Also note that if you migrate to another version of MySQL (for example, when a new version is released), you need to repeat the procedure with the new version.

To add a new native MySQL function, follow these steps:

  1. Add one line to lex.h that defines the function name in the sql_functions[] array.

  2. If the function prototype is simple (just takes zero, one, two or three arguments), you should in lex.h specify SYM(FUNC_ARGN) (where N is the number of arguments) as the second argument in the sql_functions[] array and add a function that creates a function object in item_create.cc. Take a look at "ABS" and create_funcs_abs() for an example of this.

    If the function prototype is complicated (for example, if it takes a variable number of arguments), you should add two lines to sql_yacc.yy. One indicates the preprocessor symbol that yacc should define (this should be added at the beginning of the file). Then define the function parameters and add an 「item」 with these parameters to the simple_expr parsing rule. For an example, check all occurrences of ATAN in sql_yacc.yy to see how this is done.

  3. 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.

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

    double   Item_func_newname::val()
longlong Item_func_newname::val_int()
String  *Item_func_newname::Str(String *str)

    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().

  5. 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. You can take a 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, don't 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 some 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!