FirebirdSQL logo

DECLARE FUNCTION

Declares a sub-function

Syntax
<subfunc-forward> ::= <subfunc-header>;

<subfunc-def> ::= <subfunc-header> <psql-module-body>

<subfunc-header>  ::=
  DECLARE FUNCTION subfuncname [ ( [ <in_params> ] ) ]
  RETURNS <domain_or_non_array_type> [COLLATE collation]
  [DETERMINISTIC]

<in_params> ::=
  !! See CREATE FUNCTION Syntax !!

<domain_or_non_array_type> ::=
  !! See Scalar Data Types Syntax !!

<psql-module-body> ::=
  !! See Syntax of Module Body !!
Table 1. DECLARE FUNCTION Statement Parameters
Argument Description

subfuncname

Sub-function name

collation

Collation name

The DECLARE FUNCTION statement declares a sub-function.A sub-function is only visible to the PSQL module that defined the sub-function.

A sub-function can use variables, but not cursors, from its parent module.It can access other routines from its parent modules, including recursive calls to itself.

Sub-functions have a number of restrictions:

  • A sub-function cannot be nested in another subroutine.Subroutines are only supported in top-level PSQL modules (stored procedures, stored functions, triggers and PSQL blocks).This restriction is not enforced by the syntax, but attempts to create nested sub-functions will raise an error “feature is not supported” with detail message “nested sub function”.

  • Currently, a sub-function has no direct access to use cursors from its parent module.

A sub-function can be forward declared to resolve mutual dependencies between subroutines, and must be followed by its actual definition.When a sub-function is forward declared and has parameters with default values, the default values should only be specified in the forward declaration, and should not be repeated in subfunc_def.

Note

Declaring a sub-function with the same name as a stored function will hide that stored function from your module.It will not be possible to call that stored function.

Note

Contrary to DECLARE [VARIABLE], a DECLARE FUNCTION is not terminated by a semicolon.The END of its main BEGIN …​ END block is considered its terminator.

Examples of Sub-Functions

  1. Sub-function within a stored function

    CREATE OR ALTER FUNCTION FUNC1 (n1 INTEGER, n2 INTEGER)
      RETURNS INTEGER
    AS
    - Subfunction
      DECLARE FUNCTION SUBFUNC (n1 INTEGER, n2 INTEGER)
        RETURNS INTEGER
      AS
      BEGIN
        RETURN n1 + n2;
      END
    BEGIN
      RETURN SUBFUNC (n1, n2);
    END
  2. Recursive function call

    execute block returns (i integer, o integer)
    as
        -- Recursive function without forward declaration.
        declare function fibonacci(n integer) returns integer
        as
        begin
          if (n = 0 or n = 1) then
           return n;
         else
           return fibonacci(n - 1) + fibonacci(n - 2);
        end
    begin
      i = 0;
    
      while (i < 10)
      do
      begin
        o = fibonacci(i);
        suspend;
        i = i + 1;
      end
    end