FirebirdSQL logo

The Module Body

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

Syntax of a Module Body
<module-body> ::=
  <psql-module-body> | <external-module-body>

<psql-module-body> ::=
  AS
    [<forward-declarations>]
    [<declarations>]
  BEGIN
    [<PSQL_statements>]
  END

<external-module-body> ::=
  EXTERNAL [NAME <extname>] ENGINE engine
  [AS '<extbody>']

<forward-declarations> ::=
  <forward-declare-item> [<forward-declare-item> ...]

<declarations> ::=
  <declare-item> [<declare-item> ...]

<forward-declare-item> ::=
    <subfunc-forward>
  | <subproc-forward>

<declare-item> ::=
    <declare-var>
  | <declare-cursor>
  | <subfunc-def>
  | <subproc-def>

<extname> ::=
  '<module-name>!<routine-name>[!<misc-info>]'

<declare-var> ::=
  !! See DECLARE VARIABLE !!

<declare-cursor> ::=
  !! See DECLARE .. CURSOR !!

<subfunc-forward>, <subfunc-def> ::=
  !! See DECLARE FUNCTION !!

<subproc-forward>, <subproc-def> ::=
  !! See DECLARE PROCEDURE !!
Table 1. Module Body Parameters
Parameter Description

declarations

Section for declaring local variables, named cursors, and subroutines

PSQL_statements

Procedural SQL statements.Some PSQL statements may not be valid in all types of PSQL.For example, RETURN <value>; is only valid in functions.

subfunc-forward

Sub-function forward declaration

subproc-forward

Sub-procedure forward declaration

declare_var

Local variable declaration

declare_cursor

Named cursor declaration

subfunc-def

Sub-function declaration

subproc-def

Sub-procedure declaration

extname

String identifying the external procedure

engine

String identifying the UDR engine

extbody

External procedure body.A string literal that can be used by UDRs for various purposes.

module-name

The name of the module that contains the procedure

routine-name

The internal name of the procedure inside the external module

misc-info

Optional string that is passed to the procedure in the external module

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

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

Sets 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
Table 1. SET TERM Parameters
Argument Description

new_terminator

New terminator

old_terminator

Old terminator

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.

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 */
The External Module Body

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.