>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