14.2 Adding a new native function

The procedure for adding a new native function is described below. Note that you cannot add native functions to a binary distribution since 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 (e.g., when a new version is released), you will 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. 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 SOUNDEX 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)
    
  5. You should probably also define the following 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 if any of the function arguments can return NULL by checking the arguments maybe_null variable.

All functions must be thread-safe.

For string 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.
  • The function should return the string that holds the result.
  • All current string functions try to avoid allocating any memory unless absolutely necessary!