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.
RegardsPavel
Any help or advice on how to achieve this very much appreciated !
