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

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 !