ALTER FUNCTION

The ALTER FUNCTION statement is used to change 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 change the parameters or body of a stored function. If such changes are needed, you must use the DROP FUNCTION and CREATE FUNCTION statements to drop and recreate the function.

You must have the ALTER ROUTINE privilege on the function. By default, this privilege is automatically granted to the function creator.

The following table describes the characteristics of routine usage data:

• CONTAINS SQL indicates that the routine does not contain statements that read or write data. This is the default value. For example, SET@x=1 or DO RELEASE_LOCK('abc') will be executed but do not read or write data.

• NO SQL indicates that the routine does not contain SQL statements.

• READS SQL DATA indicates that the routine contains statements that read data (such as SELECT) but do not contain statements that write data.

• MODIFIES SQL DATA indicates that the routine contains statements that may write data (such as INSERT or DELETE).

SQL SECURITY can be set to DEFINER or INVOKER to specify whether the routine is executed with the privileges of the specified user (who must have access to the database associated with the routine) or the privileges of the caller. The default value is DEFINER. The user calling the routine must have the EXECUTE privilege on the routine.

Here is an example of the ALTER FUNCTION statement:

obclient> ALTER FUNCTION my_func LANGUAGE SQL READS SQL DATA  COMMENT 'Example';
Query OK, 0 rows affected