Alter an existing function.
ALTER FUNCTIONstatement it is possible to change attributes or the procedural sql statement used in the routine body for the function.
The function-name should follow the normal rules for naming database objects, see Naming Objects.
If no schema name is given, it is assumed that the function is defined in a schema with the same name as the current ident.
If the function name is unique within the schema and only the routine attributes are altered, it is not necessary to provide a parameter list.
If there are multiple functions with the same name, it is possible to identify the function by uisng a specific name or by providing a parameter list. How to use a specific name when altering a routine is described in the
ALTER ROUTINEstatement (see ALTER ROUTINE.)
The parameter-name should follow the normal rules for naming SQL identifiers, see SQL Identifiers.
The routine attributes that can be altered are:
IS NULL CALLand
SPECIFIC. If a routine attribute is not present in the alter function statement the attribute will keep the value it had prior to the statement.
The meaning of the routine attributes are the same as when creating a function (see CREATE FUNCTION.)
It is possible to change the data type in the returns clause, with some restrictions (see below.)
It is only the creator of the schema in which the function is defined, that is allowed to alter the function.
It is not possible to alter the data type of a parameter.
If the routine body is altered, a complete parameter list with names and a returns clause must also be given.
It is possible to change the data type in the returns clause if there are no other objects referencing this function or if the new data type is comparable with the old data type (see Comparisons for more details.)
If the altered routine body contains references to objects on which the current ident does not have the applicable privilege with grant option and there are other objects referencing the function being altered, the alter operation is not allowed.
In addition, all restrictions for create function also applies.
Any privilege on the function granted to other idents will remain.
It is possible to alter a function that is part of a module.
Alter the deterministic attribute for a functionCREATE FUNCTION mimer_store_book.authors_name(p_name VARCHAR(48)) RETURNS VARCHAR(48) DETERMINISTIC BEGIN ... END ALTER FUNCTION mimer_store_book.authors_name NOT DETERMINISTIC
Example on how to change the procedure sql statement in a function definitionCREATE FUNCTION C_from_F (Fdegrees integer) RETURNS integer RETURN CAST((Fdegrees - 42) * 5.0 / 9 + 0.5 AS integer); ALTER FUNCTION C_from_F (Fdegrees integer) RETURNS integer RETURN CAST((Fdegrees - 32) * 5.0 / 9 + 0.5 AS integer);
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40