Exception handling and signaling in SQL/PSM samples

2007-01-05T15:01:58Z

85.160.65.65: /* Signaling exception */

=== 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).

PostgreSQL - Příspěvky [cs]

Exception handling and signaling in SQL/PSM samples