Bug 3044 - Exception handling in procedures and functions
Summary: Exception handling in procedures and functions
Status: REOPENED
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: -- development
Hardware: Other All
: Normal enhancement
Assignee: SQL devs
URL:
Keywords: NONEEDTOTEST
Depends on:
Blocks:
 
Reported: 2012-02-27 09:35 CET by Gatis Ozolins
Modified: 2016-04-11 11:46 CEST (History)
3 users (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
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