Parameters

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

Deterministic functions

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.

Note

Current versions of Firebird do not cache results of deterministic functions.

Specifying the DETERMINISTIC clause is comparable to a “promise” that the function will return the same thing for equal inputs.At the moment, a deterministic function is considered an invariant, and works like other invariants.That is, they are computed and cached at the current execution level of a given statement.

This is easily demonstrated with an example:

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;