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