exception handling in plpgsql

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:

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

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?

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

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

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.

Right. Just trying to clarify the issue for the person that asked the
question.

The pl/pgSQL documentation (37.7.5) says:

The condition names can be any of those shown in Appendix A.

As you say, not all of the conditions listed in Appendix A are error
conditions. Perhaps 37.7.5 should be changed to clarify? Without
looking at plerrcodes.h, you can't find a definitive list.

-- Korry