CREATE FUNCTION

Sintassi

CREATE [OR REPLACE]
    [DEFINER = {utente | CURRENT_USER | role | CURRENT_ROLE}]
    FUNCTION [IF NOT EXISTS] 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.

Sicurezza

Per chiamare una funzione occorre avere il privilegio EXECUTE su di essa. MariaDB assegna automaticamente i permessi EXECUTE e ALTER ROUTINE all'account che esegue CREATE FUNCTION, anche se viene specificata una clausola DEFINER.

Ad ogni funzione è associato un account come definer. Per default, il definer è l'account che l'ha creata. Con la clausola DEFINER si può specificarne uno diverso. Occorre però avere il privilegio SUPER per usare la clausola DEFINER. Si veda I nomi degli account per i dettagli sulla sintassi da usare per specificare un account.

La clausola SQL SECURITY quali privilegi verranno usati quando la funzione verrà chiamata. Se SQL SECURITY è INVOKER, il corpo della funzione verrà eseguito ocn i permessi di chi l'ha invocata. Se SQL SECURITY è DEFINER, il corpo della funzione viene sempre eseguito con i permessi dell'account del definer. Il default è DEFINER.

Questo comportamento permette di creare funzioni che garantiscano un accesso limitato ai dati. Per esempio, si può avere una tabella che contiene alcune informazioni sui dipendenti e l'utente roger può disporre dei privilegi SELECT solo su alcune colonne.

CREATE TABLE dipendenti (nome TINYTEXT, dipartimento TINYTEXT, salario INT);
GRANT SELECT (nome, dipartimento) ON dipendenti TO roger;

Se si desidera permettere a roger di conoscere il salario più alto dei dipendenti di un certo dipartimento, si può definire una funzione e assegnare all'utente il permesso EXECUTE:

CREATE FUNCTION max_salario (dipartimento TINYTEXT) RETURNS INT RETURN
  (SELECT MAX(salario) FROM impiegati WHERE impiegati.dipartimento = dipartimento);
GRANT EXECUTE ON FUNCTION max_salario TO roger;

Poiché il default di SQL SECURITY è DEFINER, quando l'utente roger chiama questa funzione, la query viene eseguita con i suoi privilegi. A patto che si disponga dei privilegi per estrarre il salario di ogni impiegato, il chiamante della funzione potrà ottenere il salario massimo di ogni dipartimento senza per questo avere la facoltà di leggere i salari individuali.

Esempi

La seguente funzione di esempio accetta un parametro, effettua un'operazione servendosi di una funzione SQL e restituisce il risultato.

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

E' possibile utilizzare un'istruzione composta nella funzione per utilizzare i parametri con istruzioni come INSERT e UPDATE. Il prossimo esempio crea una funzione contatore che memorizza il valore corrente in una tabella temporanea. Siccome l'istruzione composta contiene comandi che terminano con un punto e virgola, occorre prima cambiare il separatore delle istruzioni con l'istruzione DELIMITER. In questo modo, sarà possibile utilizzare il punto e virgola all'interno del corpo della funzione.

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 ;