Dynamic use of RAISE with USING to generate and catch non-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 !
2017-09-22 1:40 GMT+02:00 mike davis <mike.davis65@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
Show quoted text
Any help or advice on how to achieve this very much appreciated !
mike davis <mike.davis65@hotmail.com> writes:
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.
This works for me:
DO $$
DECLARE
v_msg TEXT := 'SOMETHING IS WRONG';
v_sqlstate TEXT := 'E0001';
BEGIN
RAISE EXCEPTION USING message = v_msg, errcode = v_sqlstate;
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$$;
NOTICE: Error E0001 raised - going to do something about it
Or you could do
RAISE EXCEPTION SQLSTATE v_sqlstate USING message = v_msg;
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
Tom has true - you can do it with errcode.
I am sorry
Regards
Pavel
Regards
Show quoted text
Pavel
Any help or advice on how to achieve this very much appreciated !
This works for me:
DO $$
DECLARE
v_msg TEXT := 'SOMETHING IS WRONG';
v_sqlstate TEXT := 'E0001';
BEGIN
RAISE EXCEPTION USING message = v_msg, errcode = v_sqlstate;
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$$;
NOTICE: Error E0001 raised - going to do something about it
Or you could do
RAISE EXCEPTION SQLSTATE v_sqlstate USING message = v_msg;
That does indeed work !
The second possible way of :
RAISE EXCEPTION SQLSTATE v_sqlstate USING message = v_msg;
however doesn't ! I think that was the format i had also tried and why i went down the dymanic route.
So it seems variables can be used in the USING subclause but not outside it. The manual does seem to hint at this as
"after level if any, you can write a format (which must be a simple string literal, not an expression)"
Anyway, RAISE EXCEPTION USING message = v_msg, errcode = v_sqlstate; works a treat!
Many thanks Tom & Pavel.
Mike