-1
   EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
          dup_prod_limt_info := 'An error ocurred trying to inset a record into FICS_CLM.T_CLM_CPARTY_PROD_LMT with CPARTY_PROD_LIMIT_TXN_ID <' || max_limit_txn_id || 
          '> CPARTY_ID <' || pro_lim_rec.CPARTY_ID || '> BU_LVL4_CODE <' || pro_lim_rec.BU_LVL4_CODE || '> PROD_CODE <' || pro_lim_rec.PROD_CODE || '>';
          RAISE dup_prod_limit;
Radagast81
  • 2,921
  • 1
  • 7
  • 21
Azhar Shaik
  • 1
  • 1

1 Answers1

0

you can use the clause RAISE EXCEPTION in EXCEPTION BLOCK

example: 

DECLARE
mensaje text;
mensaje_detalle text;
sqlerror text;
begin
--do something;

EXCEPTION
WHEN syntax_error THEN
RAISE EXCEPTION 'sintaxis error';
--for unknown exception
WHEN OTHERS THEN
GET STACKED DIAGNOSTICS mensaje = message_text, mensaje_detalle =
pg_exception_detail, sqlerror = returned_sqlstate;
RAISE EXCEPTION 'other error: %, %, %', sqlerror, mensaje,mensaje_detalle;
END;
Anthony Sotolongo
  • 1,395
  • 2
  • 9
  • 17
  • if duplicate comes he is raising exception , after that it will come out from loop RAISE dup_prod_limit; ( loop etc..) EXCEPTION WHEN dup_prod_limit THEN Perr_msg := dup_prod_limt_info; WHEN OTHERS THEN Perr_msg := 'An error was encountered while executing procedure P_CLM_CLOSE_PRODUCT_LIMITS <' || SQLERRM || '>'; How to write this in postgree?? – Azhar Shaik Nov 08 '19 at 11:23
  • in PostgreSQL The condition treated by EXCEPTION WHEN can be any of those shown in ERROR CODE (https://www.postgresql.org/docs/10/errcodes-appendix.html). if your error is not lited here. you can use special condition name OTHERS, as the example of the answer, or check this answer https://stackoverflow.com/questions/7767413/postgresql-custom-exception-conditions – Anthony Sotolongo Nov 08 '19 at 12:29