Some SQL statement editors — specifically the isql utility that comes with Firebird, and possibly some third-party editors — employ an internal convention that requires all statements to be terminated with a semicolon. This creates a conflict with PSQL syntax when coding in these environments. If you are unacquainted with this problem and its solution, please study the details in the PSQL chapter in the section entitled Switching the Terminator in isql.

Each parameter has a data type.

A collation can be specified for string-type parameters, using the COLLATE clause.

Input Parameters

Input parameters are presented as a parenthesized list following the name of the function. They are passed by value into the function, so any changes inside the function has no effect on the parameters in the caller. The NOT NULL constraint can also be specified for any input parameter, to prevent NULL being passed or assigned to it. Input parameters may have default values. Parameters with default values specified must be added at the end of the list of parameters.

Output Parameter

The RETURNS clause specifies the return type of the stored function. If a function returns a string value, then it is possible to specify the collation using the COLLATE clause. As a return type, you can specify a data type, a domain, the type of a domain (using TYPE OF), or the type of a column of a table or view (using TYPE OF COLUMN).

The optional DETERMINISTIC clause indicates that the function is deterministic. Deterministic functions always return the same result for the same set of inputs. Non-deterministic functions can return different results for each invocation, even for the same set of inputs. If a function is specified as deterministic, then such a function might not be called again if it has already been called once with the given set of inputs, and instead takes the result from a metadata cache.

   |CREATE FUNCTION FN_T
   |RETURNS DOUBLE PRECISION DETERMINISTIC
   |AS
   |BEGIN
   |  RETURN rand();
   |END;
   | 
   |-- the function will be evaluated twice and will return 2 different values
   |SELECT fn_t() FROM rdb$database
   |UNION ALL
   |SELECT fn_t() FROM rdb$database;
   | 
   |-- the function will be evaluated once and will return 2 identical values
   |WITH t (n) AS (
   |  SELECT 1 FROM rdb$database
   |  UNION ALL
   |  SELECT 2 FROM rdb$database
   |)
   |SELECT n, fn_t() FROM t;

The SQL SECURITY clause specifies the security context for executing other routines or inserting into other tables. When SQL Security is not specified, the default value of the database is applied at runtime.

The SQL SECURITY clause can only be specified for PSQL functions, and is not valid for functions defined in a package.

See also SQL Security in chapter Security.

The optional declarations section, located at the start of the body of the function definition, defines variables (including cursors) and subroutines local to the function. Local variable declarations follow the same rules as parameters regarding specification of the data type. See details in the PSQL chapter for DECLARE VARIABLE, DECLARE CURSOR, DECLARE FUNCTION, and DECLARE PROCEDURE.

The header section is followed by the function body, consisting of one or more PSQL statements enclosed between the outer keywords BEGIN and END. Multiple BEGIN …​ END blocks of terminated statements may be embedded inside the procedure body.

A stored function can also be located in an external module. In this case, instead of a function body, the CREATE FUNCTION specifies the location of the function in the external module using the EXTERNAL clause. The optional NAME clause specifies the name of the external module, the name of the function inside the module, and — optionally — user-defined information. The required ENGINE clause specifies the name of the UDR engine that handles communication between Firebird and the external module. The optional AS clause accepts a string literal body, which can be used by the engine or module for various purposes.

The CREATE FUNCTION statement can be executed by:

• Administrators

• Users with the CREATE FUNCTION privilege

The user who created the stored function becomes its owner.

1. 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);
   

2. Creating a deterministic stored function

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

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

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

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

6. 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;
   

See alsoSection 5.9.3, “CREATE OR ALTER FUNCTION”, Section 5.9.2, “ALTER FUNCTION”, Section 5.9.5, “RECREATE FUNCTION”, Section 5.9.4, “DROP FUNCTION”, Section 5.10.1, “DECLARE EXTERNAL FUNCTION”

The ALTER FUNCTION statement can be executed by:

• Administrators

• Owner of the stored function

• Users with the ALTER ANY FUNCTION privilege

Altering a stored function

  |ALTER FUNCTION ADD_INT(A INT, B INT, C INT)
  |RETURNS INT
  |AS
  |BEGIN
  |  RETURN A + B + C;
  |END

See alsoSection 5.9.1, “CREATE FUNCTION”, Section 5.9.3, “CREATE OR ALTER FUNCTION”, Section 5.9.5, “RECREATE FUNCTION”, Section 5.9.4, “DROP 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 alsoSection 5.9.1, “CREATE FUNCTION”, Section 5.9.2, “ALTER FUNCTION”, Section 5.9.4, “DROP FUNCTION”

The DROP FUNCTION statement can be executed by:

• Administrators

• Owner of the stored function

• Users with the DROP ANY FUNCTION privilege

  |DROP FUNCTION ADD_INT;

See alsoSection 5.9.1, “CREATE FUNCTION”, Section 5.9.3, “CREATE OR ALTER FUNCTION”, Section 5.9.5, “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 alsoSection 5.9.1, “CREATE FUNCTION”, Section 5.9.4, “DROP FUNCTION”