
is not deterministic. Also, a function is non-deterministic if it uses
non-deterministic functions like NOW() or CURRENT_TIMESTAMP(). The optimizer
may choose a faster execution plan if it known that the function is
deterministic. In such cases, you should declare the routine using the
DETERMINISTIC keyword. If you want to explicitly state that the function is
not deterministic (which is the default) you can use the NOT DETERMINISTIC
keywords.

If you declare a non-deterministic function as DETERMINISTIC, you may get
incorrect results. If you declare a deterministic function as NOT
DETERMINISTIC, in some cases the queries will be slower.

OR REPLACE
----------

MariaDB starting with 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 function_name ...;

with the exception that any existing privileges for the function are not
dropped.

IF NOT EXISTS
-------------

MariaDB starting with 10.1.3
----------------------------
If the IF NOT EXISTS clause is used, MariaDB will return a warning instead of
an error if the function already exists. Cannot be used together with OR
REPLACE.

[NOT] DETERMINISTIC
-------------------

The [NOT] DETERMINISTIC clause also affects binary logging, because the
STATEMENT format can not be used to store or replicate non-deterministic
statements.

CONTAINS SQL, NO SQL, READS SQL DATA, and MODIFIES SQL DATA are informative
clauses that tell the server what the function does. MariaDB does not check in
any way whether the specified clause is correct. If none of these clauses are
specified, CONTAINS SQL is used by default.

MODIFIES SQL DATA
-----------------

MODIFIES SQL DATA means that the function contains statements that may modify
data stored in databases. This happens if the function contains statements
like DELETE, UPDATE, INSERT, REPLACE or DDL.

READS SQL DATA
--------------

READS SQL DATA means that the function reads data stored in databases, but
does not modify any data. This happens if SELECT statements are used, but
there no write operations are executed.

CONTAINS SQL
------------

CONTAINS SQL means that the function contains at least one SQL statement, but
it does not read or write any data stored in a database. Examples include SET
or DO.

NO SQL
------

NO SQL means nothing, because MariaDB does not currently support any language
other than SQL.

Oracle Mode
-----------

MariaDB starting with 10.3
--------------------------
From MariaDB 10.3, a subset of Oracle's PL/SQL language has been supported in
addition to the traditional SQL/PSM-based MariaDB syntax. See Oracle mode from
MariaDB 10.3 for details on changes when running Oracle mode.

Security
--------

You must have the EXECUTE privilege on a function to call it. MariaDB
automatically grants the EXECUTE and ALTER ROUTINE privileges to the account
that called CREATE FUNCTION, even if the DEFINER clause was used.

Each function has an account associated as the definer. By default, the
definer is the account that created the function. Use the DEFINER clause to
specify a different account as the definer. You must have the SUPER privilege,
or, from MariaDB 10.5.2, the SET USER privilege, to use the DEFINER clause.
See Account Names for details on specifying accounts.

The SQL SECURITY clause specifies what privileges are used when a function is
called. If SQL SECURITY is INVOKER, the function body will be evaluated using
the privileges of the user calling the function. If SQL SECURITY is DEFINER,
the function body is always evaluated using the privileges of the definer
account. DEFINER is the default.

This allows you to create functions that grant limited access to certain data.
For example, say you have a table that stores some employee information, and
that you've granted SELECT privileges only on certain columns to the user
account roger.

CREATE TABLE employees (name TINYTEXT, dept TINYTEXT, salary INT);
GRANT SELECT (name, dept) ON employees TO roger;

To allow the user the get the maximum salary for a department, define a
function and grant the EXECUTE privilege:

CREATE FUNCTION max_salary (dept TINYTEXT) RETURNS INT RETURN
 (SELECT MAX(salary) FROM employees WHERE employees.dept = dept);
GRANT EXECUTE ON FUNCTION max_salary TO roger;

Since SQL SECURITY defaults to DEFINER, whenever the user roger calls this
function, the subselect will execute with your privileges. As long as you have
privileges to select the salary of each employee, the caller of the function
will be able to get the maximum salary for each department without being able
to see individual salaries.

Character sets and collations
-----------------------------

Function return types can be declared to use any valid character set and
collation. If used, the COLLATE attribute needs to be preceded by a CHARACTER
SET attribute.

If the character set and collation are not specifically set in the statement,
the database defaults at the time of creation will be used. If the database
defaults change at a later stage, the stored function character set/collation
will not be changed at the same time; the stored function needs to be dropped
and recreated to ensure the same character set/collation as the database is
used.

Examples
--------

The following example function takes a parameter, performs an operation using
an SQL function, and returns the result.
