CREATE FUNCTION

Sintassi

CREATE
    [DEFINER = {utente | CURRENT_USER}]
    FUNCTION func_name ([parametro[, ...]])
    RETURNS type
    [caratteristica ...]
    RETURN corpo_funzione
parametro:
    nome_param tipo_param
tipo_param:
    Qualsiasi tipo di dato valido in MariaDB
caratteristica:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'stringa'
corpo_funzione:
    Istruzioni SQL valide

Spiegazione

L'istruzione CREATE FUNCTION crea una funzione. Per usarla occorre disporre del privilegio CREATE ROUTINE sul database interessato. Le funzioni accettano un qualsiasi numero di argomenti e restituiscono un valore dal corpo della funzione. Il corpo della funzione può essere una qualsiasi istruzione SQL valida che si potrebbe usare altrove, per esempio nelle istruzioni SELECT. Se si dispone dei privilegi necessari, è possibile chiamare la funzione esattamente come se fosse built-in. Si veda Sicurezza, sotto, per ulteriori dettagli sui permessi.

E' anche possibile utilizzare una variante di CREATE FUNCTION per installare una "funzione definita dall'utente" (User Defined Function, UDF) presente in un plugin. Si veda CREATE FUNCTION (UDF) per i dettagli.

E' possibile usare un'istruzione SELECT come corpo della funzione racchiudendola fra parentesi, esattamente come si fa con le subquery. La SELECT deve restituire un valore singolo. Se restituisce più di un campo, quando la funzione viene chiamata si ottiene un errore 1241. Se invece viene restituita più di una riga, si ottiene un errore 1242. E' possibile utilizzare la clausola LIMIT per essere certi che venga restituita una sola riga.

Si può sostituire la clausola RETURN con un'istruzione composta BEGIN...END#. Tale istruzione composta deve contenere un'istruzione RETURN. Quando la funzione viene chianata, l'istruzione RETURN restituisce il risultato e tutto ciò che si trova dopo di essa viene ignorato.

Per default, le funzioni vengono associate al database corrente. Per associare esplicitamente a un database differente, nel crearla occorre specificare il nome qualificato nella forma nome_db.nome_funzione. Se il nome della funzione è uguale a quello di una funzione built-int, per chiamarla occorrerà usare il nome qualificato.

Deve sempre essere presente la lista dei parametri racchiusa tra parentesi. Se non vi sono parametri, bisogna specificare una lista vuota: (). I nomi dei parametri non sono case sensitive (le lettere maiuscole e le minuscole vengono considerate uguali).

Ad ogni parametro deve essere associato un tipo valido, e non si può utilizzare l'attributo COLLATE.

La clausola RETURNS specifica il tipo del valore restituito dalla funzione. Se la clausola RETURN restituisce un valore di un tipo differente, verrà convertito nel tipo specificato. Per esempio, se una funzione specifica ENUM o SET nella clausola RETURNS, ma poi l'istruzione RETURN restituisce un valore intero, la funzione restituirà il membro corrispondente dell'insieme ENUM o SET come stringa.

MariaDB memorizza le impostazioni della variabile sql_mode in uso al momento della creazione della funzione, e le utilizzerà sempre per eseguire la routine, ignorando l'SQL usato dal server al momento dell'invocazione.

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 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.

Examples

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

MariaDB [test]> CREATE FUNCTION hello (s CHAR(20))
    -> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

MariaDB [test]> 

You can use a compound statement in a function to manipulate data with statements like INSERT and UPDATE. The following example creates a counter function that uses a temporary table to store the current value. Because the compound statement contains statements terminated with semicolons, you have to first change the statement delimiter with the DELIMITER statement to allow the semicolon to be used in the function body.

CREATE TEMPORARY TABLE counter (c INT);
INSERT INTO counter VALUES (0);
DELIMITER //
CREATE FUNCTION counter () RETURNS INT
  BEGIN
    UPDATE counter SET c = c + 1;
    RETURN (SELECT c FROM counter LIMIT 1);
  END //
DELIMITER ;