ALTER FUNCTION

You can use the ALTER FUNCTION statement to change one or more characteristics of a stored function.

The syntax 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 change the parameters or body of a stored function. To make such changes, you must use the DROP FUNCTION statement to drop the function and then use the CREATE FUNCTION statement to recreate the function.

You must have the ALTER ROUTINE privilege on the function. By default, OceanBase Database automatically grants the privilege to the creator of the function.

The following characteristics provide information about how the routine uses data:

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

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

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

• 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 by using the privileges of the specified account that has the privileges to access the database associated with the routine, or by using the privileges of the invoker. The default value is DEFINER. To call a routine, you must have the EXECUTE privilege on the routine.

Here is an example:

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