Catching DML exceptions in PL/pgSQL

From: Radu-Adrian Popescu <radu(dot)popescu(at)aldratech(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Catching DML exceptions in PL/pgSQL
Date: 2003-06-17 09:25:06
Message-ID: 5.2.1.1.0.20030617121526.02185a50@192.168.0.1
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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>
&nbsp;&nbsp;&nbsp; _res.code:=1;<br>
&nbsp;&nbsp;&nbsp; select id into iid from log where id=_id;<br>
&nbsp;&nbsp;&nbsp; if not found then begin<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; _res.msg:=''insert'';<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <b>insert into log (log, data)
values (_log, _data);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if not found</b> then
begin<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
_res.msg:=_res.msg || '' error'';<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
_res.code:=-1;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end if;<br>
&nbsp;&nbsp;&nbsp; end;<br>
&nbsp;&nbsp;&nbsp; else begin<br>
</font>.....................<br>
The thing is if _data (parameter) is null and table has a (data &lt;&gt;
null) check, the insert would fail and abort the function before my
&quot;if not found&quot; 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>

Attachment Content-Type Size
unknown_filename text/html 1.6 KB

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tomasz Myrta 2003-06-17 09:44:36 Re: Catching DML exceptions in PL/pgSQL
Previous Message zhuj 2003-06-17 09:22:47 maxconnection