DECLARE FUNCTION

Data Manipulation (DML) StatementsBuilt-in Scalar Functions

`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

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

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