>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
