Re: Dynamic use of RAISE with USING to generate and catch non-hardcoded custom exceptions

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 !
>>
>
>