Creating User-defined Functions

You are viewing an old version of this article. View the current version here.

This article is currently incomplete.

User-defined functions allow MariaDB to be extended with a new function that works like a native (built-in) MariaDB function such as ABS() or CONCAT(). There are alternative ways to add a new function: writing a native function (which requires modifying and compiling the server source code), or writing a stored function.

Statements making use of user-defined functions are not safe for replication.

Functions are written in C or C++, and to make use of them, the operating system must support dynamic loading.

Each new SQL function requires corresponding functions written in C/C++. In the list below, at least the main function, and one other, are required. x should be replaced by the name of the function you are creating.

x()

Required for all UDF's, this is where the results are calculated.

C/C++ typeSQL type
char *STRING
long longINTEGER
doubleREAL

DECIMAL functions return string values, and so should be written accordingly. It is not possible to create ROW functions.

x_init()

Initialization function for x(). Can be used for the following:

  • Check the number of arguments to X() (the SQL equivalent).
  • Verify the argument types, or to force arguments to be of a particular type after the function is called.
  • Specify whether the result can be NULL.
  • Specify the maximum result length.
  • For REAL functions, specify the maximum number of decimals for the result.
  • Allocate any required memory. To verify that the arguments are of a required type or, alternatively, to tell MySQL to coerce arguments to the required types when the main function is called.

For an example, see sql/udf_example.cc in the source tree. For a collection of existing UDFs see http://www.mysqludf.org/.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.