Zobrazení zdroje stránky Exception handling and signaling in SQL/PSM samples

=== Signaling exception ===
<pre>
-- signaling user exception completly parametrized
CREATE OR REPLACE FUNCTION fx()
RETURNS int AS 
$$
  BEGIN
    SIGNAL SQLSTATE VALUE '11111' 
      SET MESSAGE_TEXT = 'Pavel Stehule',
          DETAIL       = 'a bydlim ve Skalici',
          HINT         = 'obcas take nekde jinde';
    RETURN 1;
 END;
$$ LANGUAGE plpgpsm;

-- signaling knowned exception without additional parameters
CREATE OR REPLACE FUNCTION fx()
RETURNS int AS 
$$
  BEGIN
    SIGNAL SQLSTATE VALUE '42000';
    RETURN 1;
  END;
$$ LANGUAGE plpgpsm;

-- signaling exception via conditional's name
CREATE OR REPLACE FUNCTION fx()
RETURNS int AS 
$$
  BEGIN
    DECLARE my_condition CONDITION FOR SQLSTATE VALUE '00001';
    SIGNAL my_condition;
    RETURN 1;
  END;
$$ LANGUAGE plpgpsm;

-- handling warnings, undo handler
CREATE OR REPLACE FUNCTION fx()
RETURNS void AS
$$
BEGIN
  BEGIN                         
    DECLARE w CONDITION FOR SQLSTATE '01000';
    DECLARE CONTINUE HANDLER FOR w PRINT 'handler';
    DROP TABLE IF EXISTS foo;
    CREATE TABLE foo(a integer);
    PRINT 'A';
    INSERT INTO foo VALUES(10);
    PRINT 'B';
    SIGNAL w;
    BEGIN ATOMIC
      DECLARE UNDO HANDLER FOR w PRINT 'inner handler';
      INSERT INTO foo VALUES(20);
      SIGNAL SQLSTATE '01000';
      INSERT INTO foo VALUES(30);
    END;
    PRINT 'C';
  END;
END;
$$ LANGUAGE plpgpsm;

-- using SQLSTATE variable
CREATE OR REPLACE FUNCTION fog() 
RETURNS void AS 
$$
  BEGIN ATOMIC
    DECLARE SQLSTATE char(5);
    DECLARE CONTINUE HANDLER FOR NOT FOUND PRINT 'Not found', SQLSTATE;
    DECLARE UNDO HANDLER FOR SQLSTATE '33333' PRINT '33333 handled', SQLSTATE;
    SIGNAL SQLSTATE '02002';
    PRINT SQLSTATE;
    SIGNAL SQLSTATE '33333';
    PRINT SQLSTATE; -- no sence
  END;
$$ LANGUAGE plpgpsm;

-- next sample of SQLSTATE
CREATE OR REPLACE FUNCTION foo(OUT SQLSTATE char(5)) AS 
$$
  CASE 1 WHEN 0 THEN PRINT 'something wrong'; END CASE;
$$ LANGUAGE plpgpsm;

-- order specific handler (SQLSTATE, SQLCLASS) before and general handler
CREATE OR REPLACE FUNCTION foo()
RETURNS void AS
$$
  BEGIN
    DECLARE sqlstate char(5);
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION PRINT 'any sql exception', sqlstate;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '33000' PRINT 'class 33', sqlstate;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '33001' PRINT 'exact sqlstate 33001';
    SIGNAL SQLSTATE '33002';
  END;
$$ LANGUAGE plpgpsm;
</pre>

For level NOTICE use PRINT stmt. For SQLSTATE 01xxx and 02xxx use WARNING and for others without 00xxxx (INFO) use ERROR (fully compliant).