Thread: exception handling in plpgsql

exception handling in plpgsql

From
Sibtay Abbas
Date:
hello

I am using the following sytex to handle exceptions in
plpgsql (I am using postgres 8 rc1)

....some code ........

EXCEPTION   WHEN NO_DATA THEN            RAISE NOTICE 'NO DATA';   WHEN OTHERS THEN       RAISE NOTICE 'An exception
occurred';      RETURN emp_rec;
 

and i receive the following error 
ERROR:  unrecognized exception condition "no_data"

How can i rectify this error?

    
__________________________________ 
Do you Yahoo!? 
Yahoo! Mail - Find what you need with new enhanced search.
http://info.mail.yahoo.com/mail_250


Re: exception handling in plpgsql

From
Andrew Dunstan
Date:
PEBKAC.

It has told you what the problem is. Use a handler for an exception that 
actually exists. To see what these are, read 
http://developer.postgresql.org/docs/postgres/plpgsql-errors-and-messages.html

cheers

andrew

Sibtay Abbas wrote:

>hello
>
>I am using the following sytex to handle exceptions in
>plpgsql (I am using postgres 8 rc1)
>
>....some code ........
>
>EXCEPTION
>    WHEN NO_DATA THEN      
>       RAISE NOTICE 'NO DATA';
>    WHEN OTHERS THEN
>        RAISE NOTICE 'An exception occurred';
>        RETURN emp_rec;
>
>and i receive the following error 
>ERROR:  unrecognized exception condition "no_data"
>
>How can i rectify this error?
>
>
>    
>  
>


Re: exception handling in plpgsql

From
Michael Fuhr
Date:
On Fri, Dec 31, 2004 at 03:18:39PM -0500, Andrew Dunstan wrote:

> It has told you what the problem is. Use a handler for an exception that 
> actually exists. To see what these are, read 
> http://developer.postgresql.org/docs/postgres/plpgsql-errors-and-messages.html

As the PL/pgSQL "Trapping Errors" documentation says, "The _condition_
names can be any of those shown in Appendix A," so a more useful link
would be:

http://developer.postgresql.org/docs/postgres/errcodes-appendix.html

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/


Re: exception handling in plpgsql

From
Andrew Dunstan
Date:

Michael Fuhr wrote:

>On Fri, Dec 31, 2004 at 03:18:39PM -0500, Andrew Dunstan wrote:
>
>  
>
>>It has told you what the problem is. Use a handler for an exception that 
>>actually exists. To see what these are, read 
>>http://developer.postgresql.org/docs/postgres/plpgsql-errors-and-messages.html
>>    
>>
>
>As the PL/pgSQL "Trapping Errors" documentation says, "The _condition_
>names can be any of those shown in Appendix A," so a more useful link
>would be:
>
>http://developer.postgresql.org/docs/postgres/errcodes-appendix.html
>  
>


You are right. My humble apologies.

andrew


Re: exception handling in plpgsql

From
Korry
Date:
<blockquote type="CITE"><pre>
<font color="#000000">I am using the following sytex to handle exceptions in</font>
<font color="#000000">plpgsql (I am using postgres 8 rc1)</font>

<font color="#000000">....some code ........</font>

<font color="#000000">EXCEPTION</font>
<font color="#000000">    WHEN NO_DATA THEN      </font>
<font color="#000000">       RAISE NOTICE 'NO DATA';</font>
<font color="#000000">    WHEN OTHERS THEN</font>
<font color="#000000">        RAISE NOTICE 'An exception occurred';</font>
<font color="#000000">        RETURN emp_rec;</font>

<font color="#000000">and i receive the following error </font>
<font color="#000000">ERROR:  unrecognized exception condition "no_data"</font>

<font color="#000000">How can i rectify this error?</font>
</pre></blockquote><br /> It seems you can’t trap every condition listed in errocodes-appendix.html; in particular, you
can’ttrap <tt>SUCCESSFUL_COMPLETION</tt>, any of the conditions listed in the <tt>WARNING</tt> category, or any of the
conditionslisted in the <tt>NO DATA</tt> category.  (At least through 8.0 rc1 - I haven't checked in later
versions).<br/><br />         -- Korry  

Re: exception handling in plpgsql

From
Michael Fuhr
Date:
On Sat, Jan 01, 2005 at 10:04:57AM -0500, Korry wrote:

> It seems you can???t trap every condition listed in errocodes-
> appendix.html; in particular, you can't trap SUCCESSFUL_COMPLETION, any
> of the conditions listed in the WARNING category, or any of the
> conditions listed in the NO DATA category.  (At least through 8.0 rc1 -
> I haven't checked in later versions).

src/pl/plpgsql/src/plerrcodes.h contains the following comment:

/* Success and warnings can't be caught, so omit them from table */

Maybe an IF NOT FOUND test could substitute for trapping NO DATA.
As for SUCCESSFUL COMPLETION, it seems reasonable to infer that the
operation was successful if an exception *isn't* raised (for some
definition of "successful" -- additional logic might be necessary).
Or maybe I'm misunderstanding the purpose of trapping these conditions.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/


Re: exception handling in plpgsql

From
Tom Lane
Date:
Korry <korry@starband.net> writes:
> It seems you can’t trap every condition listed in errocodes-
> appendix.html; in particular, you can’t trap SUCCESSFUL_COMPLETION, any
> of the conditions listed in the WARNING category, or any of the
> conditions listed in the NO DATA category.  (At least through 8.0 rc1 -
> I haven't checked in later versions).

Those aren't errors.
        regards, tom lane


Re: exception handling in plpgsql

From
Korry
Date:
On Sat, 2005-01-01 at 14:10 -0500, Tom Lane wrote: <blockquote type="CITE"><pre>
<font color="#000000">Korry <<a href="mailto:korry@starband.net">korry@starband.net</a>> writes:</font>
<font color="#000000">> It seems you can’t trap every condition listed in errocodes-</font>
<font color="#000000">> appendix.html; in particular, you can’t trap SUCCESSFUL_COMPLETION, any</font>
<font color="#000000">> of the conditions listed in the WARNING category, or any of the</font>
<font color="#000000">> conditions listed in the NO DATA category.  (At least through 8.0 rc1 -</font>
<font color="#000000">> I haven't checked in later versions).</font>

<font color="#000000">Those aren't errors.</font>
</pre></blockquote><br /> Right.  Just trying to clarify the issue for the person that asked the question.  <br /><br
/>The pl/pgSQL documentation (37.7.5) says:<br /><br /> > The <tt>condition</tt> names can be any of those shown in
<ahref="file:///usr/local/pg800b5/doc/postgresql/html/errcodes-appendix.html">Appendix A</a>.<br /><br /> As you say,
notall of the conditions listed in Appendix A are error conditions.  Perhaps 37.7.5 should be changed to clarify? 
Withoutlooking at plerrcodes.h, you can't find a definitive list.<br /><br /><br />         -- Korry<br /><br />