that's cool!
one big stumbling block resolved.!
Next I think I would like to look at the remaining exception handling functionality in PL/SQL
One is clearly user defined exceptions.
The PL/SQL documentation says that one must delare user defined exceptions in the DECLARE block
like:
DECLARE
my_excep EXCEPTION;
BEGIN
....
and the ONLY way a user defined exception can be raised is through the raise exception command;
RAISE my_excep;
This makes me think that user-defined exceptions can be handled in the pgSQL library without going into SPI.
Whenever we get a 'RAISE xyz' command to execute, we simply compute where the handler is and bracnch off there.
There are some scoping issues as well, which I think can also be resolved in the library.
Would the postgres dev team think this is a good architechture? or should we move user defined exceptions into the core as well?
Lastly, I installed 8.0 to test the exception handling.
The function I used is given as follows.
I extracted ERRCODE_DIVISION_BY_ZERO from plpgsql/src/plerrcodes.h
but the function does not compile.
The error that I get is:
test=# select * from foo(10);
ERROR: division by zero
CONTEXT: SQL query "insert into temp values(19/0)"
PL/pgSQL function "foo" line 2 at SQL statement
Have I done something wrong? Exception codes are also absent from the 8.0 documentation (which looks like it needs an upgrade).
*********************************************
drop function foo(integer);
create function foo(integer) returns integer
as '
begin
insert into temp values(19/0);
return 1200;
exception
when ERRCODE_DIVISION_BY_ZERO then
return 11;
end;
' language 'plpgsql';
*********************************************
thanks
paraM
| Jeff <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 15/08/2004 07:00 PM
|
To: [EMAIL PROTECTED] cc: "pgSQL General" <[EMAIL PROTECTED]> Subject: Re: [GENERAL] exception handling support in pgSQL |
On Aug 15, 2004, at 1:57 AM, [EMAIL PROTECTED] wrote:
> Instead of porting each of these procedures by hand,
> we would like to add exception handling support to pgSQL if possible.
>
Today is your lucky day! 8.0 adds exceptions to plpgsql!
8.0 however is in beta. But testers are greatly wanted!
> I looked into the code for the pgSQL library, and as I understand it,
> we can put support for user defined exceptions (something we use a lot)
> within the pgSQL library by intercepting the
I'm not sure we have user defined exceptions yet..
perhaps you could work on implementing them..
--
Jeff Trout <[EMAIL PROTECTED]>
http://www.jefftrout.com/
http://www.stuarthamm.net/
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
