Re: exception handling support in pgSQL

Paramveer.Singh@trilogy.com writes:
> Have I done something wrong?

Yes, misspelled the condition name.

> Exception codes are also absent from the 8.0
> documentation (which looks like it needs an upgrade).

One wonders if you've actually read the 8.0 documentation... see
http://developer.postgresql.org/docs/postgres/errcodes-appendix.html
in particular the statement

: The PL/pgSQL condition name for each error code is the same as the
: phrase shown in the table, with underscores substituted for spaces. For
: example, code 22012, DIVISION BY ZERO, has condition name
: DIVISION_BY_ZERO. Condition names can be written in either upper or
: lower case.

The example given under
http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
also seems reasonably clear about the spelling of this particular
condition name.

As for your original question --- I'm not that eager to try to emulate
Oracle's approach to user-defined exceptions.  The syntax they use is
amazing ugly (pragma exception_init?) and we could not be very
compatible in any case, because (a) we use SQLSTATEs not SQLCODEs for
error IDs and (b) there doesn't seem to be a very close mapping between
their error IDs and ours.  Also, AFAICS their approach does not let a
RAISE command insert any parameter values into error messages, which is
mighty restrictive.

We could probably build something based on the same idea of declaring
names that can be referenced in RAISE and EXCEPTION, but I think we
should deliberately not adopt exactly their syntax for it.

One possibility is that the declaration identifies the SQLSTATE to use,
perhaps

    declare myerr exception('XP012');

and generalize RAISE to

    RAISE name 'message string' [, value [, ...]]

where the "name" can either be one of the level values we used to allow
(for backwards compatibility) or it can be a built-in or user-defined
condition name.  This would provide enough info for RAISE to do the
correct elog call.  EXCEPTION clauses in the same block could also use
"myerr" as a condition name to trap this error.

            regards, tom lane