Syntax
------ 
CREATE [OR REPLACE] [AGGREGATE] FUNCTION [IF NOT EXISTS]
function_name
 RETURNS {STRING|INTEGER|REAL|DECIMAL}
 SONAME shared_library_name
 
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.
 
For an example, see sql/udf_example.cc in the source tree.
For a collection of existing UDFs see
http://www.mysqludf.org/.
 
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.
 
For valid identifiers to use as function names, see
Identifier Names.
 
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().
 
Aggregate UDF functions can be used as window functions.
 
OR REPLACE
 
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
 
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. Cannot be used together with OR REPLACE.
 
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.
 
Examples
-------- 
CREATE FUNCTION jsoncontains_path RETURNS integer SONAME
'ha_connect.so';
 
Query OK, 0 rows affected (0.00 sec)
 
OR REPLACE and IF NOT EXISTS:
 
CREATE FUNCTION jsoncontains_path RETURNS integer SONAME
'ha_connect.so';
 
ERROR 1125 (HY000): Function 'jsoncontains_path' already
exists
 
CREATE OR REPLACE FUNCTION jsoncontains_path RETURNS integer
SONAME 'ha_connect.so';
 
Query OK, 0 rows affected (0.00 sec)
 
CREATE FUNCTION IF NOT EXISTS jsoncontains_path RETURNS
integer SONAME 'ha_connect.so';
 
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
SHOW WARNINGS;
 
+-------+------+---------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------+
| Note | 1125 | Function 'jsoncontains_path' already
exists |
+-------+------+---------------------------------------------+
 


URL: https://mariadb.com/kb/en/create-function-udf/