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 !
