The ALTER FUNCTION statement is used to modify one or more characteristics of a stored function.
The syntax for the ALTER FUNCTION statement is as follows:
ALTER FUNCTION func_name [characteristic ...]
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
The ALTER FUNCTION statement cannot be used to modify the parameters or body of a stored function. To make such changes, you must use DROP FUNCTION and CREATE FUNCTION to drop the function and then recreate it.
You must have the ALTER ROUTINE privilege on this function. By default, this privilege is automatically granted to the function creator.
The following table describes the characteristics of data used by routines:
CONTAINS SQL: The sample program does not contain statements for reading or writing data. This is the default value. For example, statements such asSET@x=1orDO RELEASE_LOCK('abc')will be executed but do not read from or write to any data.The
NO SQLtable example script does not contain SQL statements.The
READS SQL DATAtable example statement contains data reading statements (such asSELECT) but no data writing statements.The
MODIFIES SQL DATAclause example statements contain data-writing statements (such asINSERTorDELETE).
SQL SECURITY can be set to DEFINER or INVOKER. It specifies whether a stored routine executes with the privileges of the user who defined it (this user must have the necessary privileges to access the database associated with the routine) or with the privileges of the caller. The default value is DEFINER. The user that calls the stored routine must have the EXECUTE privilege on that routine.
An example of ALTER FUNCTION is as follows:
obclient> ALTER FUNCTION my_func LANGUAGE SQL READS SQL DATA COMMENT 'Example';
Query OK, 0 rows affected