Syntax
------

CREATE [OR REPLACE]
  [DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }]
  [AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([func_parameter[,...]])
  RETURNS type
  [characteristic ...]
  RETURN func_body
func_parameter:
  [ IN | OUT | INOUT | IN OUT ]  param_name type
type:
  Any valid MariaDB data type
characteristic:
  LANGUAGE SQL
 | [NOT] DETERMINISTIC
 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
 | SQL SECURITY { DEFINER | INVOKER }
 | COMMENT 'string'
func_body:
  Valid SQL procedure statement

Description
-----------

Use the CREATE FUNCTION statement to create a new stored function. You must
have the CREATE ROUTINE database privilege to use CREATE FUNCTION. A function
takes any number of arguments and returns a value from the function body. The
function body can be any valid SQL expression as you would use, for example,
in any select expression. If you have the appropriate privileges, you can call
the function exactly as you would any built-in function. See Security below
for details on privileges.

You can also use a variant of the CREATE FUNCTION statement to install a
user-defined function (UDF) defined by a plugin. See CREATE FUNCTION (UDF) for
details.

You can use a SELECT statement for the function body by enclosing it in
parentheses, exactly as you would to use a subselect for any other expression.
The SELECT statement must return a single value. If more than one column is
returned when the function is called, error 1241 results. If more than one row
is returned when the function is called, error 1242 results. Use a LIMIT
clause to ensure only one row is returned.

You can also replace the RETURN clause with a BEGIN...END compound statement.
The compound statement must contain a RETURN statement. When the function is
called, the RETURN statement immediately returns its result, and any
statements after RETURN are effectively ignored.

By default, a function is associated with the current database. To associate
the function explicitly with a given database, specify the fully-qualified
name as db_name.func_name when you create it. If the function name is the same
as the name of a built-in function, you must use the fully qualified name when
you call it.

The parameter list enclosed within parentheses must always be present. If
there are no parameters, an empty parameter list of () should be used.
Parameter names are not case sensitive.

Each parameter can be declared to use any valid data type, except that the
COLLATE attribute cannot be used.

For valid identifiers to use as function names, see Identifier Names.

IN | OUT | INOUT | IN OUT
-------------------------

MariaDB starting with 10.8.0
----------------------------
The function parameter qualifiers for IN, OUT, INOUT, and IN OUT were added in
a 10.8.0 preview release. Prior to 10.8.0 quantifiers were supported only in
procedures.

OUT, INOUT and its equivalent IN OUT, are only valid if called from SET and
not SELECT. These quantifiers are especially useful for creating functions
with more than one return value. This allows functions to be more complex and
nested.

DELIMITER $$
CREATE FUNCTION add_func3(IN a INT, IN b INT, OUT c INT) RETURNS INT
BEGIN
 SET c = 100;
 RETURN a + b;
END;
$$
DELIMITER ;

SET @a = 2;
SET @b = 3;
SET @c = 0;
SET @res= add_func3(@a, @b, @c);

SELECT add_func3(@a, @b, @c);
ERROR 4186 (HY000): OUT or INOUT argument 3 for function add_func3 is not
allowed here

DELIMITER $$
CREATE FUNCTION add_func4(IN a INT, IN b INT, d INT) RETURNS INT
BEGIN
 DECLARE c, res INT;
 SET res = add_func3(a, b, c) + d;
 if (c > 99) then
  return  3;
 else
  return res;
 end if;
END;
$$

DELIMITER ;

SELECT add_func4(1,2,3);
+------------------+
| add_func4(1,2,3) |
+------------------+
|                3 |
+------------------+

AGGREGATE
---------

MariaDB starting with 10.3.3
----------------------------
From MariaDB 10.3.3, it is possible to create stored aggregate functions as
well. See Stored Aggregate Functions for details.

RETURNS
-------

The RETURNS clause specifies the return type of the function. NULL values are
permitted with all return types.

What happens if the RETURN clause returns a value of a different type? It
depends on the SQL_MODE in effect at the moment of the function creation.

If the SQL_MODE is strict (STRICT_ALL_TABLES or STRICT_TRANS_TABLES flags are
specified), a 1366 error will be produced.

Otherwise, the value is coerced to the proper type. For example, if a function
specifies an ENUM or SET value in the RETURNS clause, but the RETURN clause
returns an integer, the value returned from the function is the string for the
corresponding ENUM member of set of SET members.

MariaDB stores the SQL_MODE system variable setting that is in effect at the
time a routine is created, and always executes the routine with this setting
in force, regardless of the server SQL mode in effect when the routine is
invoked.

LANGUAGE SQL
------------

LANGUAGE SQL is a standard SQL clause, and it can be used in MariaDB for
portability. However that clause has no meaning, because SQL is the only
supported language for stored functions.

A function is deterministic if it can produce only one result for a given list
of parameters. If the result may be affected by stored data, server variables,
random numbers or any value that is not explicitly passed, then the function