2017-09-22 1:40 GMT+02:00 mike davis <mike.davi...@hotmail.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 ! >
