Re: exception handling support in pgSQL - Mailing list pgsql-general
| From | Paramveer.Singh@trilogy.com |
|---|---|
| Subject | Re: exception handling support in pgSQL |
| Date | |
| Msg-id | OFE7AC603E.854E96E7-ONE5256EF1.0050CFF2@trilogy.com Whole thread Raw |
| In response to | exception handling support in pgSQL (Paramveer.Singh@trilogy.com) |
| Responses |
Re: exception handling support in pgSQL
|
| List | pgsql-general |
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@torgo.978.org> Sent by: pgsql-general-owner@postgresql.org 15/08/2004 07:00 PM | To: Paramveer.Singh@trilogy.com cc: "pgSQL General" <pgsql-general@postgresql.org> Subject: Re: [GENERAL] exception handling support in pgSQL |
On Aug 15, 2004, at 1:57 AM, Paramveer.Singh@trilogy.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@jefftrout.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@postgresql.org)
pgsql-general by date: