X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.193])
	by svr1.postgresql.org (Postfix) with ESMTP id 7FE6430F08B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jun 2003 09:25:19 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 89079-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jun 2003 06:25:09 -0300 (ADT)
Received: from aldratech.com (unknown [213.154.158.58])
	by svr1.postgresql.org (Postfix) with ESMTP id 9386430F0A7
	for <pgsql-sql@postgresql.org>; Tue, 17 Jun 2003 06:25:07 -0300 (ADT)
Received: from rpopescu.aldratech.com ([192.168.0.6]) by aldratech.com for
	<radu.popescu@aldratech.com> with eXtremail;
	Tue, 17 Jun 2003 12:25:06 +3GMT
Message-Id: <5.2.1.1.0.20030617121526.02185a50@192.168.0.1>
X-Sender: radu.popescu@aldratech.com@192.168.0.1
X-Mailer: QUALCOMM Windows Eudora Version 5.2.1
Date: Tue, 17 Jun 2003 12:25:06 +0300
To: pgsql-sql@postgresql.org
From: Radu-Adrian Popescu <radu.popescu@aldratech.com>
Subject: Catching DML exceptions in PL/pgSQL
Mime-Version: 1.0
Content-Type: text/html; charset="us-ascii"
X-Spam-Status: No, hits=1.4 tagged_above=0.0 required=5.0 tests=HTML_30_40,
	HTML_FONT_FACE_ODD, MIME_HTML_ONLY
X-Spam-Level: *
X-Archive-Number: 200306/172
X-Sequence-Number: 13930

<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>