PSQL Language Extensions - Firebird

The PSQL language extensions include the following language elements:

• BEGIN and END statements for defining code blocks, which can be nested.
• DECLARE VARIABLE statements for declaring local variables.
• FOR SELECT <select-specification> INTO <variable-list> DO encapsulating an SQL cursor, for looping through sets. Loops can be nested.
• WHILE loops.
• SUSPEND statement for sending a row of output to the row cache.
• IF ... THEN and ELSE for branching logic.
• The EXCEPTION <declared exception name> statement for raising custom exceptions.
• Optional WHEN <exception condition> DO blocks for catching and handling exceptions.
• POST_EVENT <string> to pass notifications to the client. Firebird 1.5 and later also support
• EXECUTE STATEMENT statement for executing ad hoc DML and DDL statements from within the module (v.1.5 and later)
• Boolean context variables UPDATING, INSERTING, and DELETING
• Context variable ROW_COUNT to read the number of rows affected by a completed DML statement within the same block
• Optional syntaxes for EXCEPTION, with no argument for reraising exceptions and with an optional text argument for passing runtime information back to the client

Restrictions on PSQL

Certain language restrictions apply to the code in PSQL modules:

• Statements using the data definition language (DDL) subset of Firebird SQL are not permissible in PSQL.
• Transaction control statements are not valid in PSQL, because stored procedures and triggers always execute within an existing client transaction context and Firebird does not support embedded transactions.
• Some other statement types are reserved for use in different environments (e.g., isql , scripts, or embedded SQL—see the next section). All dynamic DML statements are allowed.
• Metadata object identifiers, such as the names of tables, columns, views, or stored procedures, cannot be passed to or returned from stored procedures in arguments.
• Trigger procedures cannot accept or return arguments.

Statement Types Not Supported in PSQL

The following statement types are not supported in triggers or stored procedures:

• Data definition language statements (i.e., any beginning with any of the keywords CREATE, RECREATE, ALTER, or DROP; SET GENERATOR; DECLARE EXTERNAL FUNCTION; and DECLARE FILTER)
• Transaction control statements: SET TRANSACTION, COMMIT, COMMIT RETAIN, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT, ROLLBACK TO SAVEPOINT
• ESQL statements: PREPARE, DESCRIBE, EXECUTE
• CONNECT/DISCONNECT, and sending SQL statements to another database
• GRANT/REVOKE
• EVENT INIT/EVENT WAIT
• BEGIN DECLARESECTION/END DECLARE SECTION
• BASED ON
• WHENEVER
• DECLARE CURSOR
• OPEN
• FETCH
• Any statement beginning with the keywords SET or SHOW

Exceptions

Exception handlers can be written to “swallow” an error by dealing with it in some way. For example, an input row in an iterative routine that causes an exception does not need to cause the entire process to stop. The exception handling inside the trigger or procedure can allow the problem input to be skipped—perhaps logging the error in a text file or error table —and let further processing continue.

The code module can handle an error itself with an optional piece of code, known as an exception block, which is a sequence of statements bounded by BEGIN and END, preceded by a directive beginning with the keyword WHEN.

An unhandled exception stops processing, undoes any work done thus far, and returns an error message to the application. You can also have your code raise a custom exception itself and stop processing. You can handle the error in your code—or stop the processing and return a custom message to the client application. If the code module is a trigger, the DML operation in which the error occurred will be undone also. You can create as many custom exceptions as you need in a database. From v.1.5 on, you can use runtime data and construct extensions to your exception messages “on the fly.”

Events

Firebird events are optional “signals” that PSQL modules can accumulate during execution, to be passed to client applications once the work has been committed. Client applications anywhere on the network can optionally listen—by way of “event alerters”—for specific events that they are interested in, without needing to poll for changes specifically.

Security

Procedures and triggers can be granted privileges for specific actions (SELECT, INSERT, DELETE, and so on) on tables, just as users or roles can be granted privileges. There is no special syntax: An ordinary GRANT statement is used, but the recipient named in the TO clause is a trigger or procedure, instead of a user or a role. Similarly, privileges can be revoked from procedures and triggers.

It is not always necessary to grant privileges to trigger and procedure modules. It is enough for either the user or the module to have the privileges for the actions the module has to perform.

For example, if a user performs an UPDATE of table A, which fires a trigger, and the trigger performs an INSERT on table B, the action is allowed if the user has INSERT privileges on the table or the trigger has INSERT privileges on the table.

If there are not sufficient privileges for a trigger or procedure to perform its actions, Firebird fires an SQL error and sets the appropriate error code number. You can intercept this error code with an exception handler, just as with other exceptions.