pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCatching DML exceptions in PL/pgSQL
7+ messages / 4 participants
[nested] [flat]
* Catching DML exceptions in PL/pgSQL
@ 2003-06-17 09:25 Radu-Adrian Popescu <radu.popescu@aldratech.com>
2003-06-17 09:44 ` Re: Catching DML exceptions in PL/pgSQL Tomasz Myrta <jasiek@klaster.net>
2003-06-17 09:50 ` Re: Catching DML exceptions in PL/pgSQL Radu-Adrian Popescu <radu.popescu@aldratech.com>
2003-06-17 09:56 ` Re: Catching DML exceptions in PL/pgSQL Joe Conway <mail@joeconway.com>
2003-06-17 10:49 ` Re: Catching DML exceptions in PL/pgSQL Radu-Adrian Popescu <radu.popescu@aldratech.com>
0 siblings, 4 replies; 7+ messages in thread
From: Radu-Adrian Popescu @ 2003-06-17 09:25 UTC (permalink / raw)
To: pgsql-sql
<html>
<body>
Hello all,<br>
(and sorry if this has been aswered before)<br><br>
Take this piece of code for example:<br>
.....................<br>
<font face="Courier, Courier">begin<br>
_res.code:=1;<br>
select id into iid from log where id=_id;<br>
if not found then begin<br>
_res.msg:=''insert'';<br>
<b>insert into log (log, data)
values (_log, _data);<br>
if not found</b> then
begin<br>
_res.msg:=_res.msg || '' error'';<br>
_res.code:=-1;<br>
end;<br>
end if;<br>
end;<br>
else begin<br>
</font>.....................<br>
The thing is if _data (parameter) is null and table has a (data <>
null) check, the insert would fail and abort the function before my
"if not found" test.<br><br>
I'm porting a java app. from mssql to postgresql, and the java code
relies on the stored procedure to always return it's status (in _res.code
in this case).<br><br>
Is there anything I can do to make sure the function always returns _res
?<br>
Something along the lines of Oracle's exception handling, or the @@error
trick in mssql ?<br><br>
<br>
<x-sigsep><p></x-sigsep>
Regards,<br>
--<br>
Radu-Adrian Popescu<br>
CSA, DBA, Developer<br>
Aldratech Ltd.</body>
</html>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Catching DML exceptions in PL/pgSQL
2003-06-17 09:25 Catching DML exceptions in PL/pgSQL Radu-Adrian Popescu <radu.popescu@aldratech.com>
@ 2003-06-17 09:44 ` Tomasz Myrta <jasiek@klaster.net>
3 siblings, 0 replies; 7+ messages in thread
From: Tomasz Myrta @ 2003-06-17 09:44 UTC (permalink / raw)
To: Radu-Adrian Popescu <radu.popescu@aldratech.com>; +Cc: pgsql-sql
Dnia 2003-06-17 11:25, Użytkownik Radu-Adrian Popescu napisał:
> Hello all,
> (and sorry if this has been aswered before)
>
> Take this piece of code for example:
> .....................
> begin
> _res.code:=1;
> select id into iid from log where id=_id;
> if not found then begin
> _res.msg:=''insert'';
> *insert into log (log, data) values (_log, _data);
> if not found* then begin
> _res.msg:=_res.msg || '' error'';
> _res.code:=-1;
> end;
> end if;
> end;
> else begin
> .....................
> The thing is if _data (parameter) is null and table has a (data <> null)
> check, the insert would fail and abort the function before my "if not
> found" test.
>
> I'm porting a java app. from mssql to postgresql, and the java code
> relies on the stored procedure to always return it's status (in
> _res.code in this case).
>
> Is there anything I can do to make sure the function always returns _res ?
> Something along the lines of Oracle's exception handling, or the @@error
> trick in mssql ?
You can't do it this way. Your insert is violation of some constraint
(problably "not null" or "primary key" constraint). This kind of violation
raises exception and whole transaction is aborted. I don't use java, but C
librares raises also ordinary C exception which can be easily caught. If you
want to avoid such cases - check your data before inserting them.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Catching DML exceptions in PL/pgSQL
2003-06-17 09:25 Catching DML exceptions in PL/pgSQL Radu-Adrian Popescu <radu.popescu@aldratech.com>
@ 2003-06-17 09:50 ` Radu-Adrian Popescu <radu.popescu@aldratech.com>
3 siblings, 0 replies; 7+ messages in thread
From: Radu-Adrian Popescu @ 2003-06-17 09:50 UTC (permalink / raw)
To: pgsql-sql
At 6/17/2003 11:44, Tomasz Myrta wrote:
>Dnia 2003-06-17 11:25, U¿ytkownik Radu-Adrian Popescu napisa³:
>
>>Hello all,
>>(and sorry if this has been aswered before)
>>Take this piece of code for example:
>>.....................
>>begin
>> _res.code:=1;
>> select id into iid from log where id=_id;
>> if not found then begin
>> _res.msg:=''insert'';
>> *insert into log (log, data) values (_log, _data);
>> if not found* then begin
>> _res.msg:=_res.msg || '' error'';
>> _res.code:=-1;
>> end;
>> end if;
>> end;
>> else begin
>>.....................
>>The thing is if _data (parameter) is null and table has a (data <> null)
>>check, the insert would fail and abort the function before my "if not
>>found" test.
>>I'm porting a java app. from mssql to postgresql, and the java code
>>relies on the stored procedure to always return it's status (in _res.code
>>in this case).
>>Is there anything I can do to make sure the function always returns _res ?
>>Something along the lines of Oracle's exception handling, or the @@error
>>trick in mssql ?
>You can't do it this way. Your insert is violation of some constraint
>(problably "not null" or "primary key" constraint). This kind of violation
>raises exception and whole transaction is aborted. I don't use java, but C
>librares raises also ordinary C exception which can be easily caught. If
>you want to avoid such cases - check your data before inserting them.
Thanks, unfortunatelly I knew that...
My java/db code currently decides whether the call was successful based on
1) return from stored procedure (currently, to be replaced by select * from
function_name(...))
2) SQLException
So basically it would work no probs: i.e. instead of reading a -1 when an
error occured, there would be an SQLException caught and the
final outcome, either way, would be that the java method would still
function properly and let the calling code know whether things went okay
or not.
However, the point was to be able to deal with these exceptions inside
pl/plgsql, as it would bring imho a load more power and fexibility to the
db code.
For instance, it could choose to log failures to a database table, or
choose another execution path and so on.
Anyway, thanks for the reply :-)
--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Catching DML exceptions in PL/pgSQL
2003-06-17 09:25 Catching DML exceptions in PL/pgSQL Radu-Adrian Popescu <radu.popescu@aldratech.com>
@ 2003-06-17 09:56 ` Joe Conway <mail@joeconway.com>
3 siblings, 0 replies; 7+ messages in thread
From: Joe Conway @ 2003-06-17 09:56 UTC (permalink / raw)
To: Radu-Adrian Popescu <radu.popescu@aldratech.com>; +Cc: pgsql-sql
Radu-Adrian Popescu wrote:
> .....................
> begin
> _res.code:=1;
> select id into iid from log where id=_id;
> if not found then begin
> _res.msg:=''insert'';
> *insert into log (log, data) values (_log, _data);
> if not found* then begin
> _res.msg:=_res.msg || '' error'';
> _res.code:=-1;
> end;
> end if;
> end;
> else begin
> .....................
> The thing is if _data (parameter) is null and table has a (data <> null) check,
> the insert would fail and abort the function before my "if not found" test.
You could test for _data is null, and if so check attnotnull in
pg_attribute. E.g. something like:
declare
iattnotnull bool
[...]
begin
if _data is null then
select into iattnotnull attnotnull from pg_catalog.pg_attribute
where attrelid = 'log'::regclass and attname = 'data';
if iattnotnull then
_res.code := -1;
[...]
> Is there anything I can do to make sure the function always returns _res ?
> Something along the lines of Oracle's exception handling, or the @@error trick
> in mssql ?
There is currently no way to "catch" the exception in PL/pgSQL, but
maybe the above would work for you.
HTH,
Joe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Catching DML exceptions in PL/pgSQL
2003-06-17 09:25 Catching DML exceptions in PL/pgSQL Radu-Adrian Popescu <radu.popescu@aldratech.com>
@ 2003-06-17 10:49 ` Radu-Adrian Popescu <radu.popescu@aldratech.com>
2003-06-17 15:33 ` Re: Catching DML exceptions in PL/pgSQL Josh Berkus <josh@agliodbs.com>
2003-06-17 18:58 ` Re: Catching DML exceptions in PL/pgSQL Radu-Adrian Popescu <radu.popescu@aldratech.com>
3 siblings, 2 replies; 7+ messages in thread
From: Radu-Adrian Popescu @ 2003-06-17 10:49 UTC (permalink / raw)
To: pgsql-sql
Joe, Tomasz,
Thanks for the reply.
In any event, like I said, my java code should work with the functions as
they are now, whether db throws an exception or returns some result.
Also, the oracle-like exception handling would be really great and i know
i'm looking forward to it !
Maybe in 7.5 ?
:-)
Cheers,
--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Catching DML exceptions in PL/pgSQL
2003-06-17 09:25 Catching DML exceptions in PL/pgSQL Radu-Adrian Popescu <radu.popescu@aldratech.com>
2003-06-17 10:49 ` Re: Catching DML exceptions in PL/pgSQL Radu-Adrian Popescu <radu.popescu@aldratech.com>
@ 2003-06-17 15:33 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 7+ messages in thread
From: Josh Berkus @ 2003-06-17 15:33 UTC (permalink / raw)
To: Radu-Adrian Popescu <radu.popescu@aldratech.com>; pgsql-sql
Radu,
> In any event, like I said, my java code should work with the functions as
> they are now, whether db throws an exception or returns some result.
> Also, the oracle-like exception handling would be really great and i know
> i'm looking forward to it !
> Maybe in 7.5 ?
Unfortunately, we seem to still lack a dedicated PL/pgSQL source developer on
the project. Know anybody?
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Catching DML exceptions in PL/pgSQL
2003-06-17 09:25 Catching DML exceptions in PL/pgSQL Radu-Adrian Popescu <radu.popescu@aldratech.com>
2003-06-17 10:49 ` Re: Catching DML exceptions in PL/pgSQL Radu-Adrian Popescu <radu.popescu@aldratech.com>
@ 2003-06-17 18:58 ` Radu-Adrian Popescu <radu.popescu@aldratech.com>
1 sibling, 0 replies; 7+ messages in thread
From: Radu-Adrian Popescu @ 2003-06-17 18:58 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; pgsql-sql
At 6/17/2003 08:33, Josh Berkus wrote:
>Radu,
>
> > In any event, like I said, my java code should work with the functions as
> > they are now, whether db throws an exception or returns some result.
> > Also, the oracle-like exception handling would be really great and i know
> > i'm looking forward to it !
> > Maybe in 7.5 ?
>
>Unfortunately, we seem to still lack a dedicated PL/pgSQL source developer on
>the project. Know anybody?
Unfortunatelly yacc and myself are not good friends, at least not yet :-)
Aside from that, this is pretty bad news for me :-(
PEOPLE HELP OUT PL/pgSQL !!! :-O
Cheers,
--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-06-17 18:58 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-17 09:25 Catching DML exceptions in PL/pgSQL Radu-Adrian Popescu <radu.popescu@aldratech.com>
2003-06-17 09:44 ` Tomasz Myrta <jasiek@klaster.net>
2003-06-17 09:50 ` Radu-Adrian Popescu <radu.popescu@aldratech.com>
2003-06-17 09:56 ` Joe Conway <mail@joeconway.com>
2003-06-17 10:49 ` Radu-Adrian Popescu <radu.popescu@aldratech.com>
2003-06-17 15:33 ` Josh Berkus <josh@agliodbs.com>
2003-06-17 18:58 ` Radu-Adrian Popescu <radu.popescu@aldratech.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox