If DML statements (SELECT, INSERT, UPDATE, DELETE, etc.) in the body of a module (procedure, function, trigger or block) use parameters, only named parameters can be used. If DML statements contain named parameters, then they must be previously declared as local variables using DECLARE [VARIABLE] in the declaration section of the module, or as input or output variables in the module header.

When a DML statement with parameters is included in PSQL code, the parameter name must be prefixed by a colon (‘:’) in most situations. The colon is optional in statement syntax that is specific to PSQL, such as assignments and conditionals and the INTO clause. The colon prefix on parameters is not required when calling stored procedures from within another PSQL module.

Stored procedures and functions (including those defined in packages) are executed in the context of the transaction in which they are called. Triggers are executed as an intrinsic part of the operation of the DML statement: thus, their execution is within the same transaction context as the statement itself. Individual transactions are launched for database event triggers fired on connect or disconnect.

Statements that start and end transactions are not available in PSQL, but it is possible to run a statement or a block of statements in an autonomous transaction.

PSQL code modules consist of a header and a body. The DDL statements for defining them are complex statements; that is, they consist of a single statement that encloses blocks of multiple statements. These statements begin with a verb (CREATE, ALTER, DROP, RECREATE, CREATE OR ALTER, or EXECUTE BLOCK) and end with the last END statement of the body.

The header provides the module name and defines any input and output parameters or — for functions — the return type. Stored procedures and PSQL blocks may have input and output parameters. Functions may have input parameters and must have a scalar return type. Triggers do not have either input or output parameters, but DML triggers do have the NEW and OLD “records”, and INSERTING, UPDATING and DELETING variables.

The header of a trigger indicates the DML event (insert, update or delete, or a combination) or DDL or database event and the phase of operation (BEFORE or AFTER that event) that will cause it to “fire”.

The module body is either a PSQL module body, or an external module body. PSQL blocks can only have a PSQL module body.

The PSQL module body starts with an optional section that declares variables and subroutines, followed by a block of statements that run in a logical sequence, like a program. A block of statements — or compound statement — is enclosed by the BEGIN and END keywords, and is executed as a single unit of code. The main BEGIN…​END block may contain any number of other BEGIN…​END blocks, both embedded and sequential. Blocks can be nested to a maximum depth of 512 blocks. All statements except BEGIN and END are terminated by semicolons (‘;’). No other character is valid for use as a terminator for PSQL statements.

Sets the terminator character(s) to avoid conflict with the terminator character in PSQL statements

ISQL only

When you write your triggers, stored procedures, stored functions or PSQL blocks in isql — either in the interactive interface or in scripts — running a SET TERM statement is needed to switch the normal isql statement terminator from the semicolon to another character or short string, to avoid conflicts with the non-changeable semicolon terminator in PSQL. The switch to an alternative terminator needs to be done before you begin defining PSQL objects or running your scripts.

The alternative terminator can be any string of characters except for a space, an apostrophe or the current terminator character(s). Any letter character(s) used will be case-sensitive.

Changing the default semicolon to ‘^’ (caret) and using it to submit a stored procedure definition: character as an alternative terminator character:

The external module body specifies the UDR engine used to execute the external module, and optionally specifies the name of the UDR routine to call (<extname>) and/or a string (<extbody>) with UDR-specific semantics.

Configuration of external modules and UDR engines is not covered further in this Language Reference. Consult the documentation of a specific UDR engine for details.

Stored procedures have the following advantages:

Firebird supports two types of stored procedures: executable and selectable.

Executable procedures usually modify data in a database. They can receive input parameters and return a single set of output (RETURNS) parameters. They are called using the EXECUTE PROCEDURE statement. See an example of an executable stored procedure at the end of the CREATE PROCEDURE section of [fblangref50-ddl].

Selectable stored procedures usually retrieve data from a database, returning an arbitrary number of rows to the caller. The caller receives the output one row at a time from a row buffer that the database engine prepares for it.

Selectable procedures can be useful for obtaining complex sets of data that are often impossible or too difficult or too slow to retrieve using regular DSQL SELECT queries. Typically, this style of procedure iterates through a looping process of extracting data, perhaps transforming it before filling the output variables (parameters) with fresh data at each iteration of the loop. A [fblangref50-psql-suspend] statement at the end of the iteration fills the buffer and waits for the caller to fetch the row. Execution of the next iteration of the loop begins when the buffer has been cleared.

Selectable procedures may have input parameters, and the output set is specified by the RETURNS clause in the header.

A selectable stored procedure is called with a SELECT statement. See an example of a selectable stored procedure at the end of the CREATE PROCEDURE section of [fblangref50-ddl].

The syntax for creating executable stored procedures and selectable stored procedures is the same. The difference comes in the logic of the program code, specifically the absence or presence of a [fblangref50-psql-suspend] statement.

For information about creating stored procedures, see CREATE PROCEDURE in [fblangref50-ddl].

For information about modifying existing stored procedures, see ALTER PROCEDURE, CREATE OR ALTER PROCEDURE, RECREATE PROCEDURE.

For information about dropping (deleting) stored procedures, see DROP PROCEDURE.

For information about creating stored functions, see CREATE FUNCTION in [fblangref50-ddl].

For information about modifying stored functions, see ALTER FUNCTION, CREATE OR ALTER FUNCTION, RECREATE FUNCTION.

For information about dropping (deleting) stored functions, see DROP FUNCTION.

The notion of “packaging” the code components of a database operation addresses has several advantages:

For information on creating packages, see CREATE PACKAGE, and CREATE PACKAGE BODY in [fblangref50-ddl].

For information on modifying existing package header or bodies, see ALTER PACKAGE, CREATE OR ALTER PACKAGE, RECREATE PACKAGE, ALTER PACKAGE BODY, and RECREATE PACKAGE BODY.

For information on dropping (deleting) a package, see DROP PACKAGE, and DROP PACKAGE BODY.

More than one trigger can be defined for each phase-event combination. The order in which they are executed — also known as “firing order” — can be specified explicitly with the optional POSITION argument in the trigger definition. You have 32,767 numbers to choose from. Triggers with the lowest position numbers fire first.

If a POSITION clause is omitted, or if several matching event-phase triggers have the same position number, then the triggers will fire in alphabetical order.

DML triggers are those that fire when a DML operation changes the state of data: updating rows in tables, inserting new rows or deleting rows. They can be defined for both tables and views.

Six base options are available for the event-phase combination for tables and views:

These base forms are for creating single phase/single-event triggers. Firebird also supports forms for creating triggers for one phase and multiple-events, BEFORE INSERT OR UPDATE OR DELETE, for example, or AFTER UPDATE OR DELETE: the combinations are your choice.

The Boolean context variables INSERTING, UPDATING and DELETING can be used in the body of a trigger to determine the type of event that fired the trigger.

For DML triggers, the Firebird engine provides access to sets of OLD and NEW context variables (or, “records”). Each is a record of the values of the entire row: one for the values as they are before the data-changing event (the BEFORE phase) and one for the values as they will be after the event (the AFTER phase). They are referenced in statements using the form NEW.column_name and OLD.column_name, respectively. The column_name can be any column in the table’s definition, not just those that are being updated.

The NEW and OLD variables are subject to some rules:

A trigger associated with a database or transaction event can be defined for the following events:

DDL triggers fire on specified metadata change events in a specified phase. BEFORE triggers run before changes to system tables. AFTER triggers run after changes to system tables.

DDL triggers are a specific type of database trigger, so most rules for and semantics of database triggers also apply for DDL triggers.

When a DDL trigger is running, the DDL_TRIGGER namespace is available for use with RDB$GET_CONTEXT. This namespace contains information on the currently firing trigger.

See also The DDL_TRIGGER Namespace in RDB$GET_CONTEXT in [fblangref50-functions].

For information on creating triggers, see CREATE TRIGGER, CREATE OR ALTER TRIGGER, and RECREATE TRIGGER in [fblangref50-ddl].

For information on modifying triggers, see ALTER TRIGGER, CREATE OR ALTER TRIGGER, and RECREATE TRIGGER.

For information on dropping (deleting) triggers, see DROP TRIGGER.

Assigns a value to a variable

PSQL uses the equal symbol (‘=’) as its assignment operator. The assignment statement assigns a SQL expression value on the right to the variable on the left of the operator. The expression can be any valid SQL expression: it may contain literals, internal variable names, arithmetic, logical and string operations, calls to internal functions, stored functions or external functions (UDFs).

[fblangref50-psql-declare-variable]

Exits a loop

The BREAK statement immediately terminates the inner loop of a WHILE or FOR looping statement. Code continues to be executed from the first statement after the terminated loop block.

BREAK is similar to LEAVE, except it doesn’t support a label.

[fblangref50-psql-leave]

Exits a loop

The LEAVE statement immediately terminates the inner loop of a WHILE or FOR looping statement. Using the optional label parameter, LEAVE can also exit an outer loop, that is, the loop labelled with label. Code continues to be executed from the first statement after the terminated loop block.

[fblangref50-psql-break], [fblangref50-psql-continue], [fblangref50-psql-exit]

Continues with the next iteration of a loop

The CONTINUE statement skips the remainder of the current block of a loop and starts the next iteration of the current WHILE or FOR loop. Using the optional label parameter, CONTINUE can also start the next iteration of an outer loop, that is, the loop labelled with label.

[fblangref50-psql-break], [fblangref50-psql-leave], [fblangref50-psql-exit]

Terminates execution of a module

The EXIT statement causes execution of the current PSQL module to jump to the final END statement from any point in the code, thus terminating the program.

Calling EXIT in a function will result in the function returning NULL.

Passes output to the buffer and suspends execution while waiting for caller to fetch it

The SUSPEND statement is used in selectable stored procedures to pass the values of output parameters to a buffer and suspend execution. Execution remains suspended until the calling application fetches the contents of the buffer. Execution resumes from the statement directly after the SUSPEND statement. In practice, this is likely to be a new iteration of a looping process.

[fblangref50-psql-exit]

Executes dynamically created SQL statements

The statement EXECUTE STATEMENT takes a string parameter and executes it as if it were a DSQL statement. If the statement returns data, it can be passed to local variables by way of an INTO clause.

EXECUTE STATEMENT can only produce a single row of data. Statements producing multiple rows of data must be executed with [fblangref50-psql-forexec].

You can use parameters — either named or positional — in the DSQL statement string. Each parameter must be assigned a value.

By default, the executed SQL statement runs within the current transaction. Using WITH AUTONOMOUS TRANSACTION causes a separate transaction to be started, with the same parameters as the current transaction. This separate transaction will be committed when the statement was executed without errors and rolled back otherwise.

The clause WITH COMMON TRANSACTION uses the current transaction whenever possible; this is the default behaviour. If the statement must run in a separate connection, an already started transaction within that connection is used, if available. Otherwise, a new transaction is started with the same parameters as the current transaction. Any new transactions started under the “COMMON” regime are committed or rolled back with the current transaction.

By default, the SQL statement is executed with the privileges of the current user. Specifying WITH CALLER PRIVILEGES combines the privileges of the calling procedure or trigger with those of the user, as if the statement were executed directly by the routine. WITH CALLER PRIVILEGES has no effect if the ON EXTERNAL clause is also present.

With ON EXTERNAL [DATA SOURCE], the SQL statement is executed in a separate connection to the same or another database, possibly even on another server. If connection_string is NULL or “''” (empty string), the entire ON EXTERNAL [DATA SOURCE] clause is considered absent, and the statement is executed against the current database.

When ON EXTERNAL is used, the extra connection is always made via a so-called external provider, even if the connection is to the current database. One of the consequences is that exceptions cannot be caught in the usual way. Every exception caused by the statement is wrapped in either an eds_connection or an eds_statement error. To catch them in your PSQL code, you have to use WHEN GDSCODE eds_connection, WHEN GDSCODE eds_statement or WHEN ANY.

The optional AS USER, PASSWORD and ROLE clauses allow specification of which user will execute the SQL statement and with which role. The method of user login, and whether a separate connection is opened, depends on the presence and values of the ON EXTERNAL [DATA SOURCE], AS USER, PASSWORD and ROLE clauses:

All in all, this feature is meant to be used cautiously, and you should always take the caveats into account. If you can achieve the same result with PSQL and/or DSQL, it will almost always be preferable.

[fblangref50-psql-forexec]

Loops row-by-row through a query result set

The FOR SELECT statement

The optional AS CURSOR clause surfaces the result set of the FOR SELECT structure as an undeclared, named cursor that can be operated on using the WHERE CURRENT OF clause inside the statement or block following the DO command, to delete or update the current row before execution moves to the next row. In addition, it is possible to use the cursor name as a record variable (similar to OLD and NEW in triggers), allowing access to the columns of the result set (i.e. cursor_name.columnname).

Other points to take into account regarding undeclared cursors:

[fblangref50-psql-declare-cursor], [fblangref50-psql-break], [fblangref50-psql-leave], [fblangref50-psql-continue], [fblangref50-psql-exit], SELECT, UPDATE, DELETE

Executes dynamically created SQL statements and loops over its result set

The statement FOR EXECUTE STATEMENT is used, in a manner analogous to FOR SELECT, to loop through the result set of a dynamically executed query that returns multiple rows.

[fblangref50-psql-execstmt], [fblangref50-psql-break], [fblangref50-psql-leave], [fblangref50-psql-continue]

Opens a declared cursor

An OPEN statement opens a previously declared cursor, executes its declared SELECT statement, and makes the first record of the result data set ready to fetch. OPEN can be applied only to cursors previously declared in a [fblangref50-psql-declare-cursor] statement.

[fblangref50-psql-declare-cursor], [fblangref50-psql-fetch], [fblangref50-psql-close]

Fetches a record from a cursor

The FETCH statement fetches the next row from the result set of the cursor and assigns the column values to PSQL variables. The FETCH statement can be used only with a cursor declared with the [fblangref50-psql-declare-cursor] statement.

Using the optional fetch_scroll part of the FETCH statement, you can specify in which direction and how many rows to advance the cursor position. The NEXT fetch option can be used for scrollable and forward-only cursors. Other fetch options are only supported for scrollable cursors.

The optional INTO clause gets data from the current row of the cursor and loads them into PSQL variables. If a fetch moves beyond the bounds of the result set, the variables will be set to NULL.

It is also possible to use the cursor name as a variable of a record type (similar to OLD and NEW in triggers), allowing access to the columns of the result set (i.e. cursor_name.columnname).

For checking whether all the rows of the result set have been fetched, the context variable ROW_COUNT returns the number of rows fetched by the statement. If a record was fetched, then ROW_COUNT is one (1), otherwise zero (0).

[fblangref50-psql-declare-cursor], [fblangref50-psql-open], [fblangref50-psql-close]

Management statement are allowed in PSQL modules (triggers, procedures, functions and PSQL blocks), which is especially helpful for applications that need management statements to be executed at the start of a session, specifically in ON CONNECT triggers.

The management statements permitted in PSQL are:

Management Statements

Closes a declared cursor

A CLOSE statement closes an open cursor. Only a cursor that was declared with [fblangref50-psql-declare-cursor] can be closed with a CLOSE statement. Any cursors that are still open will be automatically closed after the module code completes execution.

See [fblangref50-psql-fetch-exmpl]

[fblangref50-psql-declare-cursor], [fblangref50-psql-open], [fblangref50-psql-fetch]

Executes a statement or a block of statements in an autonomous transaction

The IN AUTONOMOUS TRANSACTION statement enables execution of a statement or a block of statements in an autonomous transaction. Code running in an autonomous transaction will be committed right after its successful execution, regardless of the status of its parent transaction. This can be used when certain operations must not be rolled back, even if an error occurs in the parent transaction.

An autonomous transaction has the same isolation level as its parent transaction. Any exception that is thrown in the block of the autonomous transaction code will result in the autonomous transaction being rolled back and all changes made will be undone. If the code executes successfully, the autonomous transaction will be committed.

Using an autonomous transaction in a trigger for the database ON CONNECT event, to log all connection attempts, including those that failed:

Transaction Control

Posts an event for notification to registered clients on commit

The POST_EVENT statement notifies the event manager about the event, which saves it to an event table. When the transaction is committed, the event manager notifies applications that have registered their interest in the event.

The event name can be a code, or a short message: the choice is open as it is a string of up to 127 bytes. Keep in mind that the application listening for an event must use the exact event name when registering.

The content of the string can be a string literal, a variable or any valid SQL expression that resolves to a string.

Notifying the listening applications about inserting a record into the SALES table:

Returns a value from a stored function

The RETURN statement ends the execution of a function and returns the value of the expression value.

RETURN can only be used in PSQL functions (stored functions and local sub-functions).

See CREATE FUNCTION Examples

Declares a local variable

The statement DECLARE [VARIABLE] is used for declaring a local variable. One DECLARE [VARIABLE] statement is required for each local variable. Any number of DECLARE [VARIABLE] statements can be included and in any order. The name of a local variable must be unique among the names of local variables and input and output parameters declared for the module.

A special case of DECLARE [VARIABLE] — declaring cursors — is covered separately in [fblangref50-psql-declare-cursor]

A local variable can be of any SQL type other than an array.

For local variables, you can specify the NOT NULL constraint, disallowing NULL values for the variable. If a domain has been specified as the data type and the domain already has the NOT NULL constraint, the declaration is unnecessary. For other forms, including use of a domain that is nullable, the NOT NULL constraint can be included if needed.

Unless specified, the character set and collation of a string variable will be the database defaults. A CHARACTER SET clause can be specified to handle string data that needs a different character set. A valid collation (COLLATE clause) can also be included, with or without the character set clause.

Local variables are NULL when execution of the module begins. They can be explicitly initialized so that a starting or default value is available when they are first referenced. The initial value can be specified in two ways, DEFAULT <initvalue> and = <initvalue>. The value can be any type-compatible literal or context variable, including NULL.

Data Types and Subtypes, Custom Data Types — Domains, CREATE DOMAIN

Declares a named cursor

The DECLARE …​ CURSOR …​ FOR statement binds a named cursor to the result set obtained by the SELECT statement specified in the FOR clause. In the body code, the cursor can be opened, used to iterate row-by-row through the result set, and closed. While the cursor is open, the code can perform positioned updates and deletes using the WHERE CURRENT OF in the UPDATE or DELETE statement.

[fblangref50-psql-open], [fblangref50-psql-fetch], [fblangref50-psql-close]

Declares a sub-function

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

[fblangref50-psql-declproc], CREATE FUNCTION

Declares a sub-procedure

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

A sub-procedure can use variables, but not cursors, from its parent module. It can access other routines from its parent modules.

Sub-procedures have a number of restrictions:

A sub-procedure can be forward declared to resolve mutual dependencies between subroutines, and must be followed by its actual definition. When a sub-procedure 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 subproc_def.

[fblangref50-psql-declfunc], CREATE PROCEDURE

Delimits a block of statements

The BEGIN …​ END construct is a two-part statement that wraps a block of statements that are executed as one unit of code. Each block starts with the keyword BEGIN and ends with the keyword END. Blocks can be nested a maximum depth of 512 nested blocks. A block can be empty, allowing them to act as stubs, without the need to write dummy statements.

The BEGIN …​ END itself should not be followed by a statement terminator (semicolon). However, when defining or altering a PSQL module in the isql utility, that application requires that the last END statement be followed by its own terminator character, that was previously switched — using SET TERM — to a string other than a semicolon. That terminator is not part of the PSQL syntax.

The final, or outermost, END statement in a trigger terminates the trigger. What the final END statement does in a stored procedure depends on the type of procedure:

[fblangref50-psql-exit], SET TERM

Conditional branching

The conditional branch statement IF …​ THEN is used to branch the execution process in a PSQL module. The condition is always enclosed in parentheses. If the condition returns the value TRUE, execution branches to the statement or the block of statements after the keyword THEN. If an ELSE is present, and the condition returns FALSE or UNKNOWN, execution branches to the statement or the block of statements after it.

[fblangref50-psql-while], CASE

Looping construct

A WHILE statement implements the looping construct in PSQL. The statement or the block of statements will be executed as long as the condition returns TRUE. Loops can be nested to any depth.

A procedure calculating the sum of numbers from 1 to I shows how the looping construct is used.

Executing the procedure in isql:

the result is:

[fblangref50-psql-ifthen], [fblangref50-psql-break], [fblangref50-psql-leave], [fblangref50-psql-continue], [fblangref50-psql-exit], [fblangref50-psql-forselect], [fblangref50-psql-forexec]

An exception is a message that is generated when an error occurs.

All exceptions handled by Firebird have predefined numeric values for context variables (symbols) and text messages associated with them. Error messages are output in English by default. Localized Firebird builds are available, where error messages are translated into other languages.

Complete listings of the system exceptions can be found in [fblangref50-appx02-errorcodes]:

Custom exceptions can be declared in the database as persistent objects and called in PSQL code to signal specific errors; for example, to enforce certain business rules. A custom exception consists of an identifier, and a default message of 1021 bytes. For details, see CREATE EXCEPTION.

Throws a user-defined exception or rethrows an exception

The EXCEPTION statement with exception_name throws the user-defined exception with the specified name. An alternative message text of up to 1,021 bytes can optionally override the exception’s default message text.

The default exception message can contain slots for parameters that can be filled when throwing an exception. To pass parameter values to an exception, use the USING clause. Considering, in left-to-right order, each parameter passed in the exception-raising statement as “the Nth”, with N starting at 1:

Exceptions can be handled in a [fblangref50-psql-when] statement. If an exception is not handled in a module, then the effects of the actions executed inside this module are cancelled, and the caller program receives the exception (either the default text, or the custom text).

Within the exception-handling block — and only within it — the caught exception can be re-thrown by executing the EXCEPTION statement without parameters. If located outside the block, the re-thrown EXCEPTION call has no effect.

Custom exceptions are stored in the system table RDB$EXCEPTIONS.

Catches an exception for error handling

The WHEN …​ DO statement handles Firebird errors and user-defined exceptions. The statement catches all errors and user-defined exceptions listed after the keyword WHEN keyword. If WHEN is followed by the keyword ANY, the statement catches any error or user-defined exception, even if they have already been handled in a WHEN block located higher up.

The WHEN …​ DO statements must be located at the end of a block of statements, before the block’s END statement, and after any other statement.

The keyword DO is followed by a single statement, or statements wrapped in a BEGIN …​ END block, that handles the exception. The SQLCODE, GDSCODE, and SQLSTATE context variables are available in the context of this statement or block. Use the RDB$ERROR function to obtain the SQLCODE, GDSCODE, SQLSTATE, custom exception name and exception message. The EXCEPTION statement, without parameters, can also be used in this context to re-throw the error or exception.

The WHEN …​ DO statement or block is only executed when one of the events targeted by its conditions occurs at run-time. If the WHEN …​ DO statement is executed, even if it does nothing, execution will continue as if no error occurred: the error or user-defined exception neither terminates nor rolls back the operations of the trigger or stored procedure.

However, if the WHEN …​ DO statement or block does nothing to handle or resolve the error, the DML statement (SELECT, INSERT, UPDATE, DELETE, MERGE) that caused the error will be rolled back and none of the statements below it in the same block of statements are executed.

A WHEN …​ DO statement catches errors and exceptions in the current block of statements. It also catches exceptions from nested blocks, if those exceptions have not been handled in those blocks.

All changes made before the statement that caused the error are visible to a WHEN …​ DO statement. However, if you try to log them in an autonomous transaction, those changes are unavailable, because the transaction where the changes took place is not committed at the point when the autonomous transaction is started. Example 4, below, demonstrates this behaviour.

[fblangref50-psql-exception], CREATE EXCEPTION, SQLCODE and GDSCODE Error Codes and Message Texts and SQLSTATE Codes and Message Texts, GDSCODE, SQLCODE, SQLSTATE, RDB$ERROR()