Bug 3044

Summary: Exception handling in procedures and functions
Product: SQL Reporter: Gatis Ozolins <g.ozolins>
Component: allAssignee: SQL devs <bugs-sql>
Status: REOPENED ---    
Severity: enhancement CC: duc, k.vitols, niels
Priority: Normal Keywords: NONEEDTOTEST
Version: -- development   
Hardware: Other   
OS: All   

Description Gatis Ozolins 2012-02-27 09:35:36 CET
User-Agent:       Mozilla/5.0 (Windows; Windows NT 6.1; rv:10.0) Gecko/20100101 Firefox/9.0
Build Identifier: Dec2011

In procedures and functions it should be possible to catch errors, similar like in PL/pgSQL (http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING)

Reproducible: Always
Comment 1 Niels Nes cwiconfidential 2012-02-27 12:42:28 CET
EXCEPTION isn't part of the SQL spec's
Comment 2 Gatis Ozolins 2012-02-27 13:20:25 CET
So if you are following SQL/PSM, then it would be possible to get value of SQLSTATE. Also it would be possible to catch and process errors with DECLARE CONTINUE HANDLER and DECLARE EXIT HANDLER.

It seems that this is not implemented.

create function sqlstate_test(x int)
returns int
begin
  declare r int;
  declare continue handler for sqlstate '01002'
    set r = r + 1;
  declare continue handler for sqlstate '01003'
    set r = r + 2;
  set r = 0;
  
  if (x > 0) then
    signal sqlstate '01002';
  elsif (x < 0) then
    signal sqlstate '01003';
  end if;
  
  return r;
end;
Comment 3 karlis 2012-04-23 21:37:15 CEST
Any idea when this feature might be implemented?
Comment 4 Fabian Groffen 2012-07-21 18:39:06 CEST
(In reply to comment #3)
> Any idea when this feature might be implemented?

this is absolutely not likely to be implemented anywhere near soon