Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I’m trying to get dynamic version of the RAISE command working so that I can use a table of custom application error messages and codes for use by all developed plpgsql functions. In this way the customer error codes and message are not hard coded into code and are defined consistently in one place in the db.


However, I cannot get a dynamic/parameterised version of the RAISE command working with the USING syntax - I want to do this so that i can catch the raised error in an EXCEPTION block.


The following example shows a example of (working) hardcoded version:


DO 

$$ 

DECLARE


BEGIN 

  RAISE EXCEPTION 'Something is wrong' USING errcode = 'E0001'; 


EXCEPTION 

  WHEN SQLSTATE 'E0001' THEN

     RAISE NOTICE '%','Error E0001 raised - going to do something about it';

  WHEN OTHERS THEN

     RAISE NOTICE 'OTHER ERRORS: %', sqlstate;

   

END 

$$ 


Which raises and catches the custom error E0001 and returns (as expected)

NOTICE:  Error E0001 raised - going to do something about it.


Now what I am trying to achieve is as above but for the msg text and errcode to be retrieved from a table before issuing the RAISE EXCEPTION statement.


ie. Assume v_msg and v_sqlstate have been retrieved and contain:

v_msg = 'Something is wrong’

v_sqlstate =  ‘E0001’


The what I want to raise dynamically is:


RAISE EXCEPTION v_msg USING errcode = v_sqlstate;


and be able to use the same exception block as above in the hard coded example.


I searched and found a couple of similar examples where 

RAISE EXCEPTION ’%’, i_msg 

is used and works but this does not allow a custom SQLSTATE to be raised and trapped.


ie. The following runs ok:


DO 

$$ 

DECLARE


v1 TEXT ;


BEGIN 

  

  v1 := 'SOMETHING IS WRONG';

  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;

   

END 

$$ 


and returns:

NOTICE:  SOMETHING IS WRONG

NOTICE:  OTHER ERRORS: P0001


but obviously the WHEN OTHERS has caught the exception and the SQLSTATE is the default P0001.


So, then what i really want is similar to the above but with the USING keyword of RAISE being dynamic/parameterised.


So i tried the following:


DO 

$$ 

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!


Any help or advice on how to achieve this very much appreciated !


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux