(mysql.info) adding-udf

(mysql.info) adding-native-function

 
 24.2.4 Adding a New User-Defined Function
 -----------------------------------------

 
* udf-calling                  UDF Calling Sequences for Simple Functions
* udf-aggr-calling             UDF Calling Sequences for Aggregate Functions
* udf-arguments                UDF Argument Processing
* udf-return-values            UDF Return Values and Error Handling
* udf-compiling                Compiling and Installing User-Defined Functions
* udf-security                 User-Defined Function Security Precautions
 
 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.
 
 A UDF contains code that becomes part of the running server, so when
 you write a UDF, you are bound by any and all constraints that
 otherwise apply to writing server code. For example, you may have
 problems if you attempt to use functions from the `libstdc++' library.
 Note that these constraints may change in future versions of the
 server, so it is possible that server upgrades will require revisions
 to UDFs that were originally written for older servers. For information
 about these constraints, see  configure-options, and 
 compilation-problems.
 
 To be able to use UDFs, you need to link `mysqld' dynamically. Don't
 configure MySQL using -with-mysqld-ldflags=-all-static. If you want to
 use a UDF that needs to access symbols from `mysqld' (for example, the
 `metaphone' function in `sql/udf_example.cc' that uses
 `default_charset_info'), you must link the program with -rdynamic (see
 `man dlopen'). If you plan to use UDFs, the rule of thumb is to
 configure MySQL with -with-mysqld-ldflags=-rdynamic unless you have a
 very good reason not to.
 
 If you must use a precompiled distribution of MySQL, use MySQL-Max,
 which contains a dynamically linked server that 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 function data type and the
      return type of your C/C++ function is shown here:
 
      *SQL Type*        *C/C++ Type*
      `STRING'          `char *'
      `INTEGER'         `long long'
      `REAL'            `double'
 
      It is also possible to declare a `DECIMAL' function, but currently
      the value is returned as a string, so you should write the UDF as
      though it were a `STRING' function.
 
    * `xxx_init()' (optional)
 
      The initialization function for `xxx()'. It can be used for the
      following purposes:
 
         * To check the number of arguments to `XXX()'.
 
         * To check that the arguments are of a required type or,
           alternatively, to tell MySQL to coerce arguments to the types
           you want when the main function is called.
 
         * To allocate any memory required by the main function.
 
         * To specify the maximum length of the result.
 
         * To specify (for `REAL' functions) the maximum number of
           decimal places in the result.
 
         * To 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, MySQL aborts the SQL statement with an error message and does
 not call the main or deinitialization functions.  Otherwise, MySQL
 calls the main function `xxx()' once for each row. After all rows have
 been processed, MySQL calls the deinitialization function
 `xxx_deinit()' so that it can perform any required cleanup.
 
 For aggregate functions that work like `SUM()', you must also provide
 the following functions:
 
    * `xxx_clear()' (required in 5.0)
 
      Reset the current aggregate value but do not insert the argument
      as the initial aggregate value for a new group.
 
    * `xxx_add()' (required)
 
      Add the argument to the current aggregate value.
 
 MySQL handles aggregate UDFs as follows:
 
   1. Call `xxx_init()' to let the aggregate function allocate any
      memory it needs for storing results.
 
   2. Sort the table according to the `GROUP BY' expression.
 
   3. Call `xxx_clear()' for the first row in each new group.
 
   4. Call `xxx_add()' for each new row that belongs in the same group.
 
   5. Call `xxx()' to get the result for the aggregate when the group
      changes or after the last row has been processed.
 
   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. This includes not just the main
 function, but the initialization and deinitialization functions as
 well, and also the additional functions required by aggregate
 functions. A consequence of this requirement is 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) drop-function

(mysql.info) adding-functions

(mysql.info) adding-native-function

automatically generated byinfo2html

(mysql.info) adding-udf

Menu