Re: exception handling support in pgSQL

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 <threshar(at)torgo(dot)978(dot)org>
Sent by: pgsql-general-owner(at)postgresql(dot)org
15/08/2004 07:00 PM

To: Paramveer(dot)Singh(at)trilogy(dot)com
cc: "pgSQL General" <pgsql-general(at)postgresql(dot)org>
Subject: Re: [GENERAL] exception handling support in pgSQL

On Aug 15, 2004, at 1:57 AM, Paramveer(dot)Singh(at)trilogy(dot)com 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 <jeff(at)jefftrout(dot)com>
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 majordomo(at)postgresql(dot)org)