Re: plpgsql raise - parameters can be expressions

On Mon, 13 Jun 2005, Tom Lane wrote:

> Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz> writes:
> >> Ideas?
>
> > only RAISE? Without parameters can be used only in block. It's same scope
> > like SQLERRM and SQLSTATE.
>
> OK, but what about the other problem --- throwing a non-default SQLSTATE
> along with your custom error message?
>

I understand. Its not problem when I have SQLERRM non paramized ~ constant
string - I can get custom error message without changes. Problem is when
error message is parametrized. What is better, rebuild this string or use
old. Maybe its reason why isn't possible easy change errstr in Oracle.

Next problem, visibility custom exceptions. When I define exception
variable I can't rethrown exceptions outside block when is defined. What
is outside - some custom exception?

> I think it would be better to do something like
>
> RAISE ERROR my_exception, 'format', params;

disadvantage - I have to define format string everywhere where I wont to
raise exception. Idea is similar MsSQL. Then is necessary define only
interval for user's exception. But it's not too much readable.

> I like your DECLARE syntax better than Oracle's incredibly ugly "pragma"
> notation, and we can't expect to be exactly compatible with the pragma
> anyway since the pragma is associating the name with an integer code,
> which is not what we are going to use. So that part seems good.

From OOP view exception is object. But I need define more properties than
one. SQLSTATE is only first, second message, level, meybe next

DECLARE myexception EXCEPTION VALUE 'xxxx' MESSAGE 'xxx % % %';
or
DECLARE myexception EXCEPTION MESSAGE 'xxxx % %' = '3333'; -- optional
DECLARE myexception EXCEPTION = '2222'; -- short no message

I think so user's exception must not have errmsg. When I define own
exception I expect own exception handler. This is only more readable
style. I can have one global variable and one exception. It's can be short
way to exception handler

I think so we need more then one exception level. I can use
user's exception for easy way of write to log. Every user's exception can
by handled - this is differnt then RAISE NOTICE, any user's exception
have to be handled - equal RAISE EXCEPTION (default have to be handled),
any user's excp. can be logged. This is big theory :-), pardon

DECLARE myex EXCEPTION TOLOG NOERROR CATCHED MESSAGE 'aaaa' VALUE '10';

short myex EXCEPTION = '10';
~ ERROR CATCHED MESSAGE ''

>
> I assume you intend to allow "RAISE ERROR division_by_zero ..." and
> "EXCEPTION WHEN my_exception THEN ...", right? That is, declared
> exception names are interchangeable with the predefined ones for
> both throwing and catching errors.

Yes, I wont it. When I use SQLSTATE from system's SQLSTATEs, I speek
my exception can by handled by any general handler. But I have possibility
logging, level, ...

this is way for throwing system's exception. I think will be better
prohibit throwing system's exceptions directly. But I can associate my
exception with system's exception, and throw my exception.

>
> > I can use user's exception with default unique value from predefined
> > interval too.
>
> I see zero value in that. The only reason for declaring a specific
> user-defined exception code is so you can identify it from elsewhere,
> like in your application --- so not knowing which code it's getting
> makes it useless.

There are two questions?

Have Every user's exception unique SQLSTATE value?
Will be exist any interval for user's exception SQLSTATES?

My answer is true for all. If it's true, then I can check one SQLSTATE
exception handler in block. In my example is clean nonsens

DECLARE e EXCEPTION = '22....'; -- div_by_zero
BEGIN
RAISE ERROR e;
EXCEPTION WHEN e THEN ..
EXCEPTION WHEN div_by_zero THEN ..

NONSENS e and div_by_zero are synonyms

Regards
Pavel Stehule