FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 PROCEDUREEXTERNAL FUNCTION 

A stored function is a user-defined function stored in the metadata of a database, and running on the server.Stored functions can be called by stored procedures, stored functions (including the function itself), triggers and DSQL.When a stored function calls itself, such a stored function is called a recursive function.

Unlike stored procedures, stored functions always return a single scalar value.To return a value from a stored functions, use the RETURN statement, which immediately ends the function.

See also

EXTERNAL FUNCTION

CREATE FUNCTION

Creates a stored function

Available in

DSQL

Syntax
CREATE FUNCTION funcname [ ( [ <in_params> ] ) ]
  RETURNS <domain_or_non_array_type> [COLLATE collation]
  [DETERMINISTIC]
  {<psql_function> | <external-module-body>}

<in_params> ::= <inparam> [, <inparam> ... ]

<inparam> ::= <param-decl> [ { = | DEFAULT } <value> ]

<value> ::= { <literal> | NULL | <context-var> }

<param-decl> ::= paramname <domain_or_non_array_type> [NOT NULL]
  [COLLATE collation]

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

<psql_function> ::=
  [SQL SECURITY {INVOKER | DEFINER}]
  <psql-module-body>

<psql-module-body> ::=
  !! See Syntax of Module Body !!

<external-module-body> ::=
  !! See Syntax of Module Body !!
Table 1. CREATE FUNCTION Statement Parameters
Parameter Description

funcname

Stored function name.The maximum length is 63 characters.Must be unique among all function names in the database.

inparam

Input parameter description

collation

Collation

literal

A literal value that is assignment-compatible with the data type of the parameter

context-var

Any context variable whose type is compatible with the data type of the parameter

paramname

The name of an input parameter of the function.The maximum length is 63 characters.The name of the parameter must be unique among input parameters of the function and its local variables.

The CREATE FUNCTION statement creates a new stored function.The stored function name must be unique among the names of all stored and external (legacy) functions, excluding sub-functions or functions in packages.For sub-functions or functions in packages, the name must be unique within its module (package, stored procedure, stored function, trigger).

Note

It is advisable to not reuse function names between global stored functions and stored functions in packages, although this is legal.At the moment, it is not possible to call a function or procedure from the global namespace from inside a package, if that package defines a function or procedure with the same name.In that situation, the function or procedure of the package will be called.

CREATE FUNCTION is a compound statement with a header and a body.The header defines the name of the stored function, and declares input parameters and return type.

The function body consists of optional declarations of local variables, named cursors, and subroutines (sub-functions and sub-procedures), and one or more statements or statement blocks, enclosed in an outer block that starts with the keyword BEGIN and ends with the keyword END.Declarations and statements inside the function body must be terminated with a semicolon (‘;’).

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