Re: exception handling support in pgSQL - Mailing list pgsql-general

From Paramveer.Singh@trilogy.com
Subject Re: exception handling support in pgSQL
Date
Msg-id OFDA45E87B.D781F22B-ONE5256EF4.001EB806@trilogy.com
Whole thread Raw
In response to exception handling support in pgSQL  (Paramveer.Singh@trilogy.com)
List pgsql-general

Hi !
As Tom suggested, I augmented the declare statements to allow for
    my_excep exception;
    // I haven't mapped them to sqlstates yet.
in a way that the user defined exception also goes into the namestack to allow for resolution/scoping,
then I augmented the raise statement to allow
    raise my_excep;
now, the problem that I am facing is how to generate a 'my_excep' exception when I get to
    exec_stmt_raise in plpgsql/src/pl_exec.c

I looked into the PG_TRY and other macros and ran the database through gdb, but I could not figure
out how the database creates and throws exception when they occur.(in an attempt to copy it)

Can anyone point this out to me?

thanks
paraM


Tom Lane <tgl@sss.pgh.pa.us>
Sent by: pgsql-general-owner@postgresql.org

16/08/2004 12:33 AM

       
        To:        Paramveer.Singh@trilogy.com
        cc:        Jeff <threshar@torgo.978.org>, "pgSQL General" <pgsql-general@postgresql.org>
        Subject:        Re: [GENERAL] 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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings


pgsql-general by date:

Previous
From: David Fetter
Date:
Subject: Re: psql - user defined SQL variables
Next
From: Joel
Date:
Subject: Re: apple uses Postgres for RemoteDesktop 2