Re: [GENERAL] Dynamic use of RAISE with USING to generate and catchnon-hardcoded custom exceptions

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!

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