CREATE FUNCTION UDF
Syntax
CREATE [OR REPLACE] [AGGREGATE] FUNCTION [IF NOT EXISTS] function_name RETURNS {STRING|INTEGER|REAL|DECIMAL} SONAME shared_library_name
Contents
Description
A user-defined function (UDF) is a way to extend MariaDB with a new function that works like a native (built-in) MariaDB function such as ABS() or CONCAT().
function_name
is the name that should be used in SQL statements to invoke
the function.
To create a function, you must have the INSERT privilege for the
mysql database. This is necessary because CREATE FUNCTION
adds a row to the
mysql.func system table that records the function's name,
type, and shared library name. If you do not have this table, you should run
the mysql_upgrade command to create it.
UDFs need to be written in C, C++ or another language that uses C calling conventions, MariaDB needs to have been dynamically compiled, and your operating system must support dynamic loading.
Statements making use of user-defined functions are not safe for replication.
For creating a stored function as opposed to a user-defined function, see CREATE FUNCTION.
RETURNS
The RETURNS
clause indicates the type of the function's
return value, and can be one of STRING, INTEGER, REAL or DECIMAL. DECIMAL
functions currently return string values and should be written like STRING functions.
shared_library_name
shared_library_name
is the basename of the shared object file that contains
the code that implements the function. The file must be located in the plugin
directory. This directory is given by the value of the
plugin_dir system variable. Note that
before MariaDB/MySQL 5.1, the shared object could be located in any directory
that was searched by your system's dynamic linker.
AGGREGATE
Aggregate functions are summary functions such as SUM() and AVG().
OR REPLACE
MariaDB starting with 10.1.3
The OR REPLACE
clause was added in MariaDB 10.1.3
If the optional OR REPLACE
clause is used, it acts as a shortcut for:
DROP FUNCTION IF EXISTS function_name; CREATE FUNCTION name ...;
IF NOT EXISTS
MariaDB starting with 10.1.3
The IF NOT EXISTS
clause was added in MariaDB 10.1.3
When the IF NOT EXISTS clause is used, MariaDB will return a warning instead of an error if the specified function already exists.
Upgrading a UDF
To upgrade the UDF's shared library, first run a DROP FUNCTION statement, then upgrade the shared library and finally run the CREATE FUNCTION statement. If you upgrade without following this process, you may crash the server.