|
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 ! |
