Re: Syntax decisions for pl/pgsql RAISE extension - Mailing list pgsql-hackers

2008/5/12 Tom Lane <tgl@sss.pgh.pa.us>:
> I've started to look over Pavel's revised RAISE patch
> http://archives.postgresql.org/pgsql-patches/2008-05/msg00187.php
> and I've got a few quibbles with the syntax choices.
>
> Pavel proposes extending RAISE like this:
>
> RAISE level 'format' [, expression [, ...] ] [ USING ( option = value [, ... ] ) ]



>
> the part before USING being what we had already.  Each "option" keyword
> is one of SQLSTATE, CONDITION, DETAIL, or HINT, and each "value" is a
> string-valued expression.  SQLSTATE takes a value like '22012' while the
> (mutually exclusive) CONDITION takes a value like 'DIVISION_BY_ZERO'.
> DETAIL and HINT allow those parts of an error report to be filled in.
>
> I'd like to propose the following changes:
>
> 1. The parentheses around the USING list seem useless; let's drop 'em.

it hasn't any precedent in PostgreSQL. But option list in parenthesesis
>
> 2. I think the separation between SQLSTATE and CONDITION is just
> complication.  A SQLSTATE is required to be exactly 5 digits and/or
> upper case ASCII letters; I see no realistic prospect that any condition
> name would ever look like a SQLSTATE (and we could certainly adjust
> condition names to prevent it, if anyone would make such an unhappy
> choice).  So I think we could unify these options into one.  I think
> CODE might be a better choice for the option name than SQLSTATE (since
> the latter already has a meaning in pl/pgsql, ie the function that
> gives you the code for the currently thrown error) --- thoughts?
>

CODE isn't well name. It's too much general. If you would to drop one
identifier I prefer CONDITION or some similar (minim. ERRCODE). In
plpgsql SQLSTATE is keyword, and in some implementations it's implicit
variables too. Using it, it's more readable - more verbose - it's in
spirit of PL/SQL. Maybe:

CONDITION = expression returning name | SQLSTATE expression returning SQLSTATE.


> 3. I think we should allow the user to specify the error message the
> same way as the other options, that is
>        RAISE level USING MESSAGE = string_expression [ , ... ]
> The %-format business has always struck me as a bit weird, and it's
> much more so if we aren't handling the other error report components
> in the same fashion.  So we ought to provide an alternative that's
> more uniform.
>
> Now, the elephant in the room is the issue of Oracle compatibility.
> None of this looks anything even a little bit like Oracle's RAISE
> command.  Oracle allows
>        RAISE exception_name ;
>        RAISE ;
> where the second case is allowed only in an EXCEPTION handler and
> means to re-throw the current error.  I think the latter is a very
> good idea and we ought to support it.  Right now there's no way to
> re-throw an error without information loss, and that'll get a lot
> worse with these additions to what RAISE can throw.  I'm less
> excited about the condition-name-only syntax; that seems awfully
> impoverished given the lack of any way to supply a specific error
> message or data values.  Still, we could imagine people wanting
> something like
>        RAISE condition_name USING message = string_expression
> where the condition_name would substitute for the CODE option.
> I think we could support this as long as the condition name were
> given as an exception name rather than a string literal (otherwise
> it looks too much like our legacy syntax).  Comments?  Is anyone
> excited about that one way or the other?

I agree with this syntax, but I propose using code only with SQLSTATE keyword

RAISE 22345 is ugly
RAISE SQLSTATE 22345 is better and on this position can be
parametrized - now I thing, so SQLSTATE and CONDITION shouldn't be
defined in USING.

variants:
RAISE unique_violation USING message = 'aaaa', hint = 'aaaa';
RAISE SQLSTATE USING message ...
RAISE variable USING ...
RAISE SQLSTATE USING ...

>
> Lastly: to allow users to catch errors thrown with user-defined
> SQLSTATEs, Pavel proposes extending the syntax of EXCEPTION WHEN lists
> so that an error code can be specified in either of these styles:
>        DIVISION_BY_ZERO
>        SQLSTATE 22012
> I find the second style rather weird, and I think it probably doesn't
> even work for cases like 2201F (which isn't going to get lexed as
> a single token).  I would suggest a quoted literal and drop the noise
> word, so that the alternatives are
>        DIVISION_BY_ZERO
>        '22012'
> Comments?

it's true - it's have to quoted literal or other hand, solve it on
lexer level. But it's not important on plpgsql - there we can choice
the most simple solution.


Regards
Pavel Stehule

>
> If we can get some consensus I'll undertake to adjust the patch
> accordingly.
>
>                        regards, tom lane
>