Thread: exception handling in plpgsql
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
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? > > > > >
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/
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
<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
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/
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
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 />