Re: Dynamic use of RAISE with USING to generate and catch non-hardcoded custom exceptions

2017-09-22 1:40 GMT+02:00 mike davis <mike(dot)davis65(at)hotmail(dot)com>:

> I’m trying to get dynamic version of the RAISE command working so that I
> can use a table of custom application error messages and codes for use by
> all developed plpgsql functions. In this way the customer error codes and
> message are not hard coded into code and are defined consistently in one
> place in the db.
>
>
> However, I cannot get a dynamic/parameterised version of the RAISE command
> working with the USING syntax - I want to do this so that i can catch the
> raised error in an EXCEPTION block.
>
>
> The following example shows a example of (working) hardcoded version:
>
>
> DO
>
> $$
>
> DECLARE
>
>
> BEGIN
>
> RAISE EXCEPTION 'Something is wrong' USING errcode = 'E0001';
>
>
> EXCEPTION
>
> WHEN SQLSTATE 'E0001' THEN
>
> RAISE NOTICE '%','Error E0001 raised - going to do something about
> it';
>
> WHEN OTHERS THEN
>
> RAISE NOTICE 'OTHER ERRORS: %', sqlstate;
>
>
>
> END
>
> $$
>
>
> Which raises and catches the custom error E0001 and returns (as expected)
>
> NOTICE: Error E0001 raised - going to do something about it.
>
>
> Now what I am trying to achieve is as above but for the msg text and
> errcode to be retrieved from a table before issuing the RAISE EXCEPTION
> statement.
>
>
> ie. Assume v_msg and v_sqlstate have been retrieved and contain:
>
> v_msg = 'Something is wrong’
>
> v_sqlstate = ‘E0001’
>
>
> The what I want to raise dynamically is:
>
>
> RAISE EXCEPTION v_msg USING errcode = v_sqlstate;
>
>
> and be able to use the same exception block as above in the hard coded
> example.
>
>
> I searched and found a couple of similar examples where
>
> RAISE EXCEPTION ’%’, i_msg
>
> is used and works but this does not allow a custom SQLSTATE to be raised
> and trapped.
>
>
> ie. The following runs ok:
>
>
> DO
>
> $$
>
> DECLARE
>
>
> v1 TEXT ;
>
>
> BEGIN
>
>
>
> v1 := 'SOMETHING IS WRONG';
>
> RAISE NOTICE '%', v1;
>
> RAISE EXCEPTION '%', v1;
>
>
> EXCEPTION
>
> WHEN SQLSTATE 'E0001' THEN
>
> RAISE NOTICE '%','Error E0001 raised - going to do something about
> it';
>
> WHEN OTHERS THEN
>
> RAISE NOTICE 'OTHER ERRORS: %', sqlstate;
>
>
>
> END
>
> $$
>
>
> and returns:
>
> NOTICE: SOMETHING IS WRONG
>
> NOTICE: OTHER ERRORS: P0001
>
>
> but obviously the WHEN OTHERS has caught the exception and the SQLSTATE is
> the default P0001.
>
>
> So, then what i really want is similar to the above but with the USING
> keyword of RAISE being dynamic/parameterised.
>
>
> So i tried the following:
>
>
> DO
>
> $$
>
> DECLARE
>
>
> v_msg TEXT := '''SOMETHING IS WRONG''';
>
> v_sqlstate TEXT := '''E0001''';
>
> v1 TEXT ;
>
>
> BEGIN
>
> v1 := v_msg || ' USING errcode = ' || v_sqlstate;
>
> RAISE NOTICE '%', v1;
>
> RAISE EXCEPTION '%', v1;
>
>
> EXCEPTION
>
> WHEN SQLSTATE 'E0001' THEN
>
> RAISE NOTICE '%','Error E0001 raised - going to do something about
> it';
>
> WHEN OTHERS THEN
>
> RAISE NOTICE 'OTHER ERRORS: %,%', sqlstate,sqlerrm;
>
>
>
> END
>
> $$
>
>
> which returns:
>
> NOTICE: 'SOMETHING IS WRONG' USING errcode = 'E0001'
>
> NOTICE: OTHER ERRORS: P0001,'SOMETHING IS WRONG' USING errcode = 'E0001'
>
>
> So clearly the whole of v1 (whilst syntatically correct) is treated as the
> message and the default sqlstate of P0001 is still raised and caught by
> WHEN OTHERS.
>
>
> Have tried a few other things but cannot find way to get a custom
> errcode/sqlstate to be raised except by hardcoding it ! I’m sure there must
> be a way to do this!
>

It is not allowed in PLpgSQL - it is based on origin PL/SQL and the master
origin ADA language - these languages are static to be possible do deep
static analyse.

If you need this, then you can use PLPythonu or some own C extension.

Regards

Pavel

> Any help or advice on how to achieve this very much appreciated !
>