DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(mysql.info.gz) Adding UDF

Info Catalog (mysql.info.gz) CREATE FUNCTION (mysql.info.gz) Adding functions (mysql.info.gz) Adding native function
 
 25.2.2 Adding a New User-defined Function
 -----------------------------------------
 

Menu

 
* UDF calling                 UDF Calling Sequences for simple functions
* UDF aggr. calling           UDF Calling Sequences for aggregate functions
* UDF arguments               Argument Processing
* UDF return values           Return Values and Error Handling
* UDF compiling               Compiling and Installing User-defined Functions
 
 For the UDF mechanism to work, functions must be written in C or C++ and
 your operating system must support dynamic loading.  The MySQL source
 distribution includes a file `sql/udf_example.cc' that defines 5 new
 functions.  Consult this file to see how UDF calling conventions work.
 
 For `mysqld' to be able to use UDF functions, you should configure MySQL
 with `--with-mysqld-ldflags=-rdynamic' The reason is that to on many
 platforms (including Linux) you can load a dynamic library (with
 `dlopen()') from a static linked program, which you would get if you
 are using `--with-mysqld-ldflags=-all-static' If you want to use an UDF
 that needs to access symbols from `mysqld' (like the `metaphone'
 example in `sql/udf_example.cc' that uses `default_charset_info'), you
 must link the program with `-rdynamic' (see `man dlopen').
 
 If you are using a precompiled version of the server, use MySQL-Max,
 which supports dynamic loading.
 
 For each function that you want to use in SQL statements, you should
 define corresponding C (or C++) functions.  In the following
 discussion, the name "xxx" is used for an example function name.  To
 distinguish between SQL and C/C++ usage, `XXX()' (uppercase) indicates
 an SQL function call, and `xxx()' (lowercase) indicates a C/C++
 function call.
 
 The C/C++ functions that you write to implement the interface for
 `XXX()' are:
 
 `xxx()' (required)
      The main function.  This is where the function result is computed.
      The correspondence between the SQL type and return type of your
      C/C++ function is shown here:
 
      *SQL       *C/C++
      Type*      Type*
      `STRING'   `char *'
      `INTEGER'  `long
                 long'
      `REAL'     `double'
 
 `xxx_init()' (optional)
      The initialization function for `xxx()'.  It can be used to:
 
         * Check the number of arguments to `XXX()'.
 
         * Check that the arguments are of a required type or,
           alternatively, tell MySQL to coerce arguments to the types
           you want when the main function is called.
 
         * Allocate any memory required by the main function.
 
         * Specify the maximum length of the result.
 
         * Specify (for `REAL' functions) the maximum number of decimals.
 
         * Specify whether the result can be `NULL'.
 
 `xxx_deinit()' (optional)
      The deinitialization function for `xxx()'.  It should deallocate
      any memory allocated by the initialization function.
 
 When an SQL statement invokes `XXX()', MySQL calls the initialization
 function `xxx_init()' to let it perform any required setup, such as
 argument checking or memory allocation.  If `xxx_init()' returns an
 error, the SQL statement is aborted with an error message and the main
 and deinitialization functions are not called.  Otherwise, the main
 function `xxx()' is called once for each row.  After all rows have been
 processed, the deinitialization function `xxx_deinit()' is called so it
 can perform any required cleanup.
 
 For aggregate functions (like `SUM()'), you must also provide the
 following functions:
 
 `xxx_reset()' (required)
      Reset sum and insert the argument as the initial value for a new
      group.
 
 `xxx_add()' (required)
      Add the argument to the old sum.
 
 When using aggregate UDFs, MySQL works the following way:
 
   1. Call `xxx_init()' to let the aggregate function allocate the
      memory it will need to store results.
 
   2. Sort the table according to the `GROUP BY' expression.
 
   3. For the first row in a new group, call the `xxx_reset()' function.
 
   4. For each new row that belongs in the same group, call the
      `xxx_add()' function.
 
   5. When the group changes or after the last row has been processed,
      call `xxx()' to get the result for the aggregate.
 
   6. Repeat 3-5 until all rows has been processed
 
   7. Call `xxx_deinit()' to let the UDF free any memory it has
      allocated.
 
 All functions must be thread-safe (not just the main function, but the
 initialization and deinitialization functions as well). This means that
 you are not allowed to allocate any global or static variables that
 change!  If you need memory, you should allocate it in `xxx_init()' and
 free it in `xxx_deinit()'.
 
Info Catalog (mysql.info.gz) CREATE FUNCTION (mysql.info.gz) Adding functions (mysql.info.gz) Adding native function
automatically generated byinfo2html