On Mon, 2 Aug 2004, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > Oracle defines very few named exceptions. Instead, the intention is that
> > you define a name for a numeric exception and use it yourself.
>
> Yeah, I noticed that. It seems a spectacularly bad idea :-(. What
> redeeming social value has it got? AFAICS there are no upsides, only
> downsides: you might get the numeric code wrong, and never know it
> until your code fails in the field; and even if you always get it
> right, having every bit of code invent its own random name for the
> same exception doesn't seem like it does anything for readability or
> maintainability.
I agree with you that forcing users to declare names for SQLCODEs is not
such a great idea. What I do like, however, is the ability to declare your
own exceptions. For example:
DECLARE
invalid_sale EXCEPTION;
BEGIN
...
IF saleid < 0 THEN
RAISE EXCEPTION invalid_sale;
END IF;
...
IF price < '0.00' THEN
RAISE EXCEPTION invalid_sale;
END IF;
...
EXCEPTION
WHEN invalid_sale THEN
...
END;
This is essentially using the exception system for as a goto mechanism,
which usually I wouldn't like except for the problems created when you
have large PL/PgSQL blocks which may encounter the same conditions in
different parts of the block.
This will also be useful because people will want to emulate Oracle PL/SQL
behaviour of generating an exception if is generated when a SELECT INTO
returns no rows. So, they could do:
SELECT INTO myvar ...
IF NOT FOUND THEN
RAISE EXCEPTION NO_DATA_FOUND;
END IF
I also took a look at the Oracle PL/SQL exceptions in 10g. There are only
21 of them people have much finer granularity with PL/PgSQL. The problem
is that I'd imagine that I'd a lot of PL/SQL code captures the exception
VALUE_ERROR (which seems to cover all of SQLSTATE Class 22 it seems). This
would be a special case to the excecption label map.
There is also the STORAGE_ERROR exception which covers
ERRCODE_OUT_OF_MEMORY, ERRCODE_DISK_FULL, ERRCODE_INSUFFICIENT_RESOURCES,
ERRCODE_IO_ERROR and ERRCODE_DATA_CORRUPTED (!!).
There is also INVALID_CURSOR, which basically covers all the cursor
errors.
I have no evidence that these exceptions are in wide use so, maybe its not
a problem at all.
Anyway, I've attached a patch which adds a few more labels for existing
SQLSTATE error codes where there is a one-to-one mapping from PostgreSQL
to Oracle.
Having now added these new exception labels, and given that there are some
errors not supported as exceptions from within PL/PgSQL (success,
warnings, etc), perhaps should generate our own list of error codes within
the PL/PgSQL documentation by looking at plerrcodes.h ?
Just some thoughts...
GavinIndex: src/pl/plpgsql/src/plerrcodes.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/pl/plpgsql/src/plerrcodes.h,v
retrieving revision 1.3
diff -2 -c -r1.3 plerrcodes.h
*** src/pl/plpgsql/src/plerrcodes.h 2 Aug 2004 17:03:48 -0000 1.3
--- src/pl/plpgsql/src/plerrcodes.h 3 Aug 2004 10:28:44 -0000
***************
*** 40,43 ****
--- 40,44 ----
{ "datetime_value_out_of_range", ERRCODE_DATETIME_VALUE_OUT_OF_RANGE },
{ "division_by_zero", ERRCODE_DIVISION_BY_ZERO },
+ { "zero_divide", ERRCODE_DIVISION_BY_ZERO },
{ "error_in_assignment", ERRCODE_ERROR_IN_ASSIGNMENT },
{ "escape_character_conflict", ERRCODE_ESCAPE_CHARACTER_CONFLICT },
***************
*** 188,191 ****
--- 189,193 ----
{ "lock_file_exists", ERRCODE_LOCK_FILE_EXISTS },
{ "plpgsql_error", ERRCODE_PLPGSQL_ERROR },
+ { "program_error", ERRCODE_PLPGSQL_ERROR },
{ "raise_exception", ERRCODE_RAISE_EXCEPTION },
{ "internal_error", ERRCODE_INTERNAL_ERROR },
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
