CREATE FUNCTION

`CREATE FUNCTION` Examples

Creating a stored function

CREATE FUNCTION ADD_INT (A INT, B INT DEFAULT 0)
RETURNS INT
AS
BEGIN
  RETURN A + B;
END

Calling in a select:

SELECT ADD_INT(2, 3) AS R FROM RDB$DATABASE

Call inside PSQL code, the second optional parameter is not specified:

MY_VAR = ADD_INT(A);

Creating a deterministic stored function

CREATE FUNCTION FN_E()
RETURNS DOUBLE PRECISION DETERMINISTIC
AS
BEGIN
  RETURN EXP(1);
END

Creating a stored function with table column type parameters

Returns the name of a type by field name and value

CREATE FUNCTION GET_MNEMONIC (
  AFIELD_NAME TYPE OF COLUMN RDB$TYPES.RDB$FIELD_NAME,
  ATYPE TYPE OF COLUMN RDB$TYPES.RDB$TYPE)
RETURNS TYPE OF COLUMN RDB$TYPES.RDB$TYPE_NAME
AS
BEGIN
  RETURN (SELECT RDB$TYPE_NAME
          FROM RDB$TYPES
          WHERE RDB$FIELD_NAME = :AFIELD_NAME
          AND RDB$TYPE = :ATYPE);
END

Creating an external stored function

Create a function located in an external module (UDR).Function implementation is located in the external module udrcpp_example.The name of the function inside the module is wait_event.
```
CREATE FUNCTION wait_event (
  event_name varchar (31) CHARACTER SET ascii
) RETURNS INTEGER
EXTERNAL NAME 'udrcpp_example!Wait_event'
ENGINE udr
```

Creating a stored function containing a sub-function

Creating a function to convert a number to hexadecimal format.

CREATE FUNCTION INT_TO_HEX (
  ANumber BIGINT ,
  AByte_Per_Number SMALLINT = 8)
RETURNS CHAR (66)
AS
DECLARE VARIABLE xMod SMALLINT ;
DECLARE VARIABLE xResult VARCHAR (64);
DECLARE FUNCTION TO_HEX (ANum SMALLINT ) RETURNS CHAR
  AS
  BEGIN
    RETURN CASE ANum
      WHEN 0 THEN '0'
      WHEN 1 THEN '1'
      WHEN 2 THEN '2'
      WHEN 3 THEN '3'
      WHEN 4 THEN '4'
      WHEN 5 THEN '5'
      WHEN 6 THEN '6'
      WHEN 7 THEN '7'
      WHEN 8 THEN '8'
      WHEN 9 THEN '9'
      WHEN 10 THEN 'A'
      WHEN 11 THEN 'B'
      WHEN 12 THEN 'C'
      WHEN 13 THEN 'D'
      WHEN 14 THEN 'E'
      WHEN 15 THEN 'F'
      ELSE NULL
    END;
  END
BEGIN
  xMod = MOD (ANumber, 16);
  ANumber = ANumber / 16;
  xResult = TO_HEX (xMod);
  WHILE (ANUMBER> 0) DO
  BEGIN
    xMod = MOD (ANumber, 16);
    ANumber = ANumber / 16;
    xResult = TO_HEX (xMod) || xResult;
  END
  RETURN '0x' || LPAD (xResult, AByte_Per_Number * 2, '0' );
END

With DEFINER set for function f, user US needs only the EXECUTE privilege on f.If it were set for INVOKER, the user would also need the INSERT privilege on table t.

set term ^;
create function f (i integer) returns int SQL SECURITY DEFINER
as
begin
  insert into t values (:i);
  return i + 1;
end^
set term ;^
grant execute on function f to user us;

commit;

connect 'localhost:/tmp/59.fdb' user us password 'pas';
select f(3) from rdb$database;

`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.

CREATE FUNCTION

CREATE FUNCTION Examples

ALTER FUNCTION

`CREATE FUNCTION` Examples

`ALTER FUNCTION`