FirebirdSQL logo

Statement Terminators

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.

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;

SQL Security

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.