7.7.3. EXCEPTION

Used for

Throwing a user-defined exception or re-throwing an exception

Available in

PSQL

Syntax

EXCEPTION [exception_name [custom_message]]

An EXCEPTION statement throws the user-defined exception with the specified name. An alternative message text of up to 1,021 bytes can optionally override the exception’s default message text.

The exception can be handled in the statement, by just leaving it with no specific WHEN …​ DO handler and allowing the trigger or stored procedure to terminate and roll back all operations. The calling application gets the alternative message text, if any was specified; otherwise, it receives the message originally defined for that exception.

Within the exception-handling block — and only within it — the caught exception can be re-thrown by executing the EXCEPTION statement without parameters. If located outside the block, the re-thrown EXCEPTION call has no effect.

Examples

1. Throwing an exception with dynamically generated text:

   …
   EXCEPTION EX_BAD_TYPE
     'Incorrect record type with id ' || new.id;
   …

2. Throwing an exception upon a condition in the SHIP_ORDER stored procedure:

   CREATE OR ALTER PROCEDURE SHIP_ORDER (
     PO_NUM CHAR(8))
   AS
     DECLARE VARIABLE ord_stat  CHAR(7);
     DECLARE VARIABLE hold_stat CHAR(1);
     DECLARE VARIABLE cust_no   INTEGER;
     DECLARE VARIABLE any_po    CHAR(8);
   BEGIN
     SELECT
       s.order_status,
       c.on_hold,
       c.cust_no
     FROM
       sales s, customer c
     WHERE
       po_number = :po_num AND
       s.cust_no = c.cust_no
     INTO :ord_stat,
          :hold_stat,
          :cust_no;
     IF (ord_stat = 'shipped') THEN
       EXCEPTION order_already_shipped;
     /* Other statements */
   END

3. Throwing an exception upon a condition and replacing the original message with an alternative message:

   CREATE OR ALTER PROCEDURE SHIP_ORDER (
     PO_NUM CHAR(8))
   AS
     DECLARE VARIABLE ord_stat  CHAR(7);
     DECLARE VARIABLE hold_stat CHAR(1);
     DECLARE VARIABLE cust_no   INTEGER;
     DECLARE VARIABLE any_po    CHAR(8);
   BEGIN
     SELECT
       s.order_status,
       c.on_hold,
       c.cust_no
     FROM
       sales s, customer c
     WHERE
       po_number = :po_num AND
       s.cust_no = c.cust_no
     INTO :ord_stat,
          :hold_stat,
          :cust_no;
     IF (ord_stat = 'shipped') THEN
       EXCEPTION order_already_shipped
         'Order status is "' || ord_stat || '"';
     /* Other statements */
   END

4. Logging an error and re-throwing it in the WHEN block:

   CREATE PROCEDURE ADD_COUNTRY (
     ACountryName COUNTRYNAME,
     ACurrency VARCHAR(10) )
   AS
   BEGIN
     INSERT INTO country (country,
                          currency)
     VALUES (:ACountryName,
             :ACurrency);
     WHEN ANY DO
     BEGIN
       -- write an error in log
       IN AUTONOMOUS TRANSACTION DO
         INSERT INTO ERROR_LOG (PSQL_MODULE,
                                GDS_CODE,
                                SQL_CODE,
                                SQL_STATE)
         VALUES ('ADD_COUNTRY',
                 GDSCODE,
                 SQLCODE,
                 SQLSTATE);
       -- Re-throw exception
       EXCEPTION;
     END
   END

See also

CREATE EXCEPTION, WHEN …​ DO