FirebirdSQL logo
 Data Manipulation (DML) StatementsBuilt-in Scalar Functions 

The Module Header

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

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