Compound statement

A compound statement is in principal the essential block of the SQL/PSM language. It enables us to enter the sequence of statements, declare local variables, conditions and subroutines, errors (exceptions) and warnings handling. The declaration of variables can not be intersected by the declaration of cursors, errors handling, etc. The variables can be set to the default value. The range of a compound statement is determined by the pair BEGIN END. In therm of the syntax, also the empty statement, which doesn't execute any action, is correct. In SQL/PSM is possible to capture and handle both errors and warnings. That is relatively intensively used and thanks to it the exception handling plays there far more significant role than in other programming languages. The warning NOT FOUND is captured most often and basically the most probable way how to handle it, is the setting of a signal variable. Very often we can also encounter the following example of iteration across the result of the query utilizing the cursor:

CREATE OR REPLACE FUNCTION report()
RETURNS void AS 
$$
  bl:BEGIN
      DECLARE done boolean DEFAULT false;
      DECLARE a, b integer;
      DECLARE cx CURSOR FOR SELECT f.a, f.b FROM Foo f;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

      OPEN cx;
      FETCH cx INTO bl.a, bl.b;

      WHILE NOT done
      DO
        PRINT bl.a, bl.b;
        FETCH cx INTO bl.a, bl.b;
      END WHILE;

      CLOSE cx;
    END bl;
$$ LANGUAGE plpgpsm;

This construction is very often in SQL/PSM and in PL/pgPSM we can also use it. However, more efficient and cleaner solution would be usage of the statement FOR. Every compound statement includes implicit empty handling for the '01' and '02' classes of errors (NOT FOUND). Therefore the unhandled signalized warning won't be displayed. You can also avoid the necessity of using the capture of the warning by using of the variable SQLSTATE. This variable is not default, it's necessary to declare it as char(5). If this variable exists, system will automatically set it in accordance with the status of the last executed statement. After completion of any explicit error or warning handling, this variable is reset to the default values '00000'. Implicit warning handling doesn't have any influence on that variable. As well behave: the empty compound statement, control statements (FOR, LOOP, WHILE, ... respectively they retain SQLSTATE so, as they were set by both the evaluation of the conditions of control statements and by the SQL/PSM statements called from the control statements) and statement PRINT (if PRINT influenced SQLSTATE, it couldn't be then used for debugging, without having an influence on the debugging application). Both the error handling and statement FOR will be describe further in more details.

-- for statement
CREATE OR REPLACE FUNCTION report()
RETURNS void AS 
$$
  FOR 
      SELECT a, b 
         FROM Foo
  DO
    PRINT a, b;
  END FOR;
$$ LANGUAGE plpgpsm;

-- using sqlstate variable
CREATE OR REPLACE FUNCTION report()
RETURNS void AS 
$$
  bl:BEGIN
      DECLARE SQLSTATE char(5);
      DECLARE a, b integer;
      DECLARE cx CURSOR FOR SELECT f.a, f.b FROM Foo f;

      OPEN cx;
      FETCH cx INTO bl.a, bl.b;

      WHILE SQLSTATE = '00000'
      DO
        PRINT bl.a, bl.b;
        FETCH cx INTO bl.a, bl.b;
      END WHILE;

      CLOSE cx;
    END bl;
$$ LANGUAGE plpgpsm;

Warning capture and its handling isn't difficult and you don't have to be afraid of utilizing of this technique. Different thing is exception handling, which requires creating of save points, which entails specific direction. Therefore we should handle the exception only in the cases, where we surely know how to handle the exception. Exception should not be used for covering of program insufficiencies and faults. In this respect the less effective is CONTINUE handler for any exception, when the save point is inserted before every single statement in the block.

Syntax

[statement number ':'] BEGIN [NOT ATOMIC|ATOMIC]
  [variable and condition declaration]
  [cursor declaration]
  [exception handler declaration]
  [SQL/PSM statements list]
END [statement number] ';'

variable and condition declaration = 
((DECLARE varname [, varname [, ...]] type [DEFAULT value]
| DECLARE condname CONDITION [FOR SQLSTATE [VALUE] string]) ';') [ ... ]

cursor declaration = 
(DECLARE cursorname [SCROLL|NO SCROLL] CURSOR FOR ( sql statement | prepared statement name ';') [ ... ]

exception handler declaration =
(DECLARE (CONTINUE|EXIT|UNDO) HANDLER HANDLER FOR 
  ((condname|SQLSTATE [VALUE] string|NOT FOUND|SQLWARNING|SQLEXCEPTION) [, ...])
  SQL/PSM statement ';') [...]

The usage of cursors is described in the section dedicated to the usage of SQL statements in SQL/PSM.

Exception handling

The exception handlers we divide on so called specific (SQLSTATE value or SQLSTATE class is known) or general. General handlers are declared for NOT FOUND signal, any SQLWARNING warning (class 01) or any exception (all the classes with the exception of 00, 01 and 02) - that is SQLEXCEPTION. Specific handler can be declared for any number of disjoint SQLSTATE values (classes). General handler can be defined only for one particular class determined by the identifiers NOT FOUND, SQLWARNING and SQLEXCEPTION. Every signal appointed by the SQLSTATE value will be handled by one most specific handler for the given SQLSTATE value. It means that handler searches at first a value for SQLSTATE. In the case of unsuccessful searching, it starts to search the handler with the adequate SQLSTATE class and if also this searching doesn't end with success, a general handler (if exists) will be used.

Within the range of compound statement it's not possible:

• to define more handlers for one SQLSTATE
• to define more handlers for one SQLSTATE class
• to define more identical general handlers

While declaring a condition, we don't have to introduce there SQLSTATE. In this case SQLSTATE P1001, which is predetermined for this purpose, will be used. This SQLSTATE value is not possible to specify explicitly and therefore is guaranteed that the signal will be captured only by entering the name of the condition into the list of exception handler.

CREATE OR REPLACE FUNCTION demo() 
RETURNS void AS
$$ 
  BEGIN
    DECLARE my_cond CONDITION;
    DECLARE CONTINUE HANDLER FOR my_cond PRINT 'my_cond handled';
    SIGNAL my_cond;
  END;
$$ LANGUAGE plpgpsm;