7.1. Elements of PSQL

A procedural extension may contain declarations of local variables and cursors, assignments, conditional statements, loops, statements for raising custom exceptions, error handling and sending messages (events) to client applications. Triggers have access to special context variables, two arrays that store, respectively, the NEW values for all columns during insert and update activity, and the OLD values during update and delete work.

Statements that modify metadata (DDL) are not available in PSQL.

7.1.1. DML Statements with Parameters

If DML statements (SELECT, INSERT, UPDATE, DELETE, etc.) in the body of the module (procedure, trigger or block) use parameters, only named parameters can be used and they must “exist” before the statements can use them. They can be made available by being declared either as input or output parameters in the module’s header or as local variables, in DECLARE [VARIABLE] statements at the bottom of the 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. The colon prefix on parameters is not required when calling stored procedures from within another PSQL module or in DSQL.

7.1.2. Transactions

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

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.

7.1.3. Module Structure

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) and end with the last END statement of the body.

The Module Header

The header provides the module name and defines any parameters and variables that are used in the body. Stored procedures and PSQL blocks may have input and output parameters. Triggers do not have either input or output parameters.

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

The Module Body

The body of a PSQL module is a block of statements that run in a logical sequence, like a program. A block of statements is contained within a BEGIN and an END statement. The main BEGIN…​END block may contain any number of other BEGIN…​END blocks, both embedded and sequential. All statements except BEGIN and END are terminated by semicolons (‘;’). No other character is valid for use as a terminator for PSQL statements.

Switching the Terminator in isql

Here we digress a little, to explain how to switch the terminator character in the isql utility to make it possible to define PSQL modules in that environment without conflicting with isql itself, which uses the same character, semicolon (‘;’), as its own statement terminator.

isql Command SET TERM

Used for

Changing the terminator character[s] to avoid conflict with the terminator character in PSQL statements

Available in

ISQL only

Syntax

SET TERM new_terminator old_terminator

When you write your triggers and stored procedures 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 some other character or short string, to avoid conflict 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.

Example

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

SET TERM ^;
CREATE OR ALTER PROCEDURE SHIP_ORDER (
  PO_NUM CHAR(8))
AS
BEGIN
  /* Stored procedure body */
END^
/* Other stored procedures and triggers */
SET TERM ;^
/* Other DDL statements */