FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 PROCEDUREEXTERNAL FUNCTION 

ALTER FUNCTION

Alters a stored function

Available in

DSQL

Syntax
ALTER FUNCTION funcname
  [ ( [ <in_params> ] ) ]
  RETURNS <domain_or_non_array_type> [COLLATE collation]
  [DETERMINISTIC]
  {<psql_function> | <external-module-body>}

!! See syntax of CREATE FUNCTION for further rules !!

The ALTER FUNCTION statement allows the following changes to a stored function definition:

  • the set and characteristics of input and output type

  • local variables, named cursors, and subroutines

  • code in the body of the stored procedure

For external functions (UDR), you can change the entry point and engine name.For legacy external functions declared using DECLARE EXTERNAL FUNCTION — also known as UDFs — it is not possible to convert to PSQL and vice versa.

After ALTER FUNCTION executes, existing privileges remain intact and dependencies are not affected.

Altering a function without specifying the SQL SECURITY clause will remove the SQL Security property if currently set for this function.This means the behaviour will revert to the database default.

Caution

Take care about changing the number and type of input parameters and the output type of a stored function.Existing application code and procedures, functions and triggers that call it could become invalid because the new description of the parameters is incompatible with the old calling format.For information on how to troubleshoot such a situation, see the article The RDB$VALID_BLR Field in the Appendix.

Who Can Alter a Function

The ALTER FUNCTION statement can be executed by:

  • Administrators

  • Owner of the stored function

  • Users with the ALTER ANY FUNCTION privilege

docnext count = 8

Examples of ALTER FUNCTION

Altering a stored function
ALTER FUNCTION ADD_INT(A INT, B INT, C INT)
RETURNS INT
AS
BEGIN
  RETURN A + B + C;
END
See also

[fblangref50-ddl-func-create], [fblangref50-ddl-func-creatalter], [fblangref50-ddl-func-recreate], [fblangref50-ddl-func-drop]

CREATE OR ALTER FUNCTION

Creates a stored function if it does not exist, or alters a stored function

Available in

DSQL

Syntax
CREATE OR ALTER FUNCTION funcname
  [ ( [ <in_params> ] ) ]
  RETURNS <domain_or_non_array_type> [COLLATE collation]
  [DETERMINISTIC]
  {<psql_function> | <external-module-body>}

!! See syntax of CREATE FUNCTION for further rules !!

The CREATE OR ALTER FUNCTION statement creates a new stored function or alters an existing one.If the stored function does not exist, it will be created by invoking a CREATE FUNCTION statement transparently.If the function already exists, it will be altered and compiled (through ALTER FUNCTION) without affecting its existing privileges and dependencies.

Examples of CREATE OR ALTER FUNCTION

Create a new or alter an existing stored function
CREATE OR ALTER FUNCTION ADD_INT(A INT, B INT DEFAULT 0)
RETURNS INT
AS
BEGIN
  RETURN A + B;
END
See also

[fblangref50-ddl-func-create], [fblangref50-ddl-func-alter], [fblangref50-ddl-func-drop]

DROP FUNCTION

Drops a stored function

Available in

DSQL

Syntax
DROP FUNCTION funcname
Table 1. DROP FUNCTION Statement Parameters
Parameter Description

funcname

Stored function name.The maximum length is 63 characters.Must be unique among all function names in the database.

The DROP FUNCTION statement deletes an existing stored function.If the stored function has any dependencies, the attempt to delete it will fail, and raise an error.

Who Can Drop a Function

The DROP FUNCTION statement can be executed by:

  • Administrators

  • Owner of the stored function

  • Users with the DROP ANY FUNCTION privilege

Examples of DROP FUNCTION

DROP FUNCTION ADD_INT;
See also

[fblangref50-ddl-func-create], [fblangref50-ddl-func-creatalter], [fblangref50-ddl-func-recreate]

RECREATE FUNCTION

Drops a stored function if it exists, and creates a stored function

Available in

DSQL

Syntax
RECREATE FUNCTION funcname
  [ ( [ <in_params> ] ) ]
  RETURNS <domain_or_non_array_type> [COLLATE collation]
  [DETERMINISTIC]
  {<psql_function> | <external-module-body>}

!! See syntax of CREATE FUNCTION for further rules !!

The RECREATE FUNCTION statement creates a new stored function or recreates an existing one.If there is a function with this name already, the engine will try to drop it and then create a new one.Recreating an existing function will fail at COMMIT if the function has dependencies.

Note

Be aware that dependency errors are not detected until the COMMIT phase of this operation.

After a procedure is successfully recreated, existing privileges to execute the stored function and theprivileges of the stored function itself are dropped.

Examples of RECREATE FUNCTION

Creating or recreating a stored function
RECREATE FUNCTION ADD_INT(A INT, B INT DEFAULT 0)
RETURNS INT
AS
BEGIN
  RETURN A + B;
EN
See also

[fblangref50-ddl-func-create], [fblangref50-ddl-func-drop]