asked on

Postgres Custom Error messages

Hi,
I have the below Stored Procedure in Postgres.
I have an error log table. When someone passes the V_START_DT to be greater than V_END_DT, i want to insert a custom SQLState and SQLERRM into the error log table.
What i've coded, isn't working. Can someone kindly point me to the solution?

CREATE OR REPLACE FUNCTION F_ERR_VALIDATION(V_ID INT
                                           	  ,V_NAME VARCHAR(50)
                                           	  ,V_START_DT DATE
                                           	  ,V_END_DT DATE
                                              )
RETURNS VOID
AS
$$

DECLARE LV_ERROR_CODE VARCHAR(20);
		 LV_ERROR_MSG  VARCHAR(200);
 BEGIN       
 INSERT INTO TB_CLIENT
 VALUES (V_ID,V_NAME,V_START_DT,V_END_DT);

EXCEPTION WHEN (V_START_DT > V_END_DT) THEN
SQLSTATE := '-1';
SQLERRM := 'Start Date Cannot be greater than End date';

 INSERT INTO TB_ERROR_LOG
 VALUES ('TB_CLIENT',V_ID,SQLSTATE,SQLERRM);
END IF;
 
 EXCEPTION WHEN OTHERS THEN
 LV_ERROR_CODE = SQLSTATE;
 LV_ERROR_MSG = SQLERRM;
 INSERT INTO TB_ERROR_LOG
 VALUES ('TB_CLIENT',V_ID,LV_ERROR_CODE,LV_ERROR_MSG);
END $$ LANGUAGE PLPGSQL;

Open in new window

Pawan Kumar

Please try this -
Do not try set SQLSTATE AND SQLErrm and directly insert the values into our log table.

CREATE OR REPLACE FUNCTION F_ERR_VALIDATION(V_ID INT
       ,V_NAME VARCHAR(50)
       ,V_START_DT DATE
       ,V_END_DT DATE
)
RETURNS VOID
AS
$$

DECLARE LV_ERROR_CODE VARCHAR(20);
             LV_ERROR_MSG VARCHAR(200);
BEGIN
INSERT INTO TB_CLIENT
VALUES (V_ID,V_NAME,V_START_DT,V_END_DT);

EXCEPTION WHEN (V_START_DT > V_END_DT) THEN
INSERT INTO TB_ERROR_LOG
VALUES ('TB_CLIENT',V_ID,'-1','Start Date Cannot be greater than End date');
END IF;

EXCEPTION WHEN OTHERS THEN
LV_ERROR_CODE = SQLSTATE;
LV_ERROR_MSG = SQLERRM;
INSERT INTO TB_ERROR_LOG
VALUES ('TB_CLIENT',V_ID,LV_ERROR_CODE,LV_ERROR_MSG);
END $$ LANGUAGE PLPGSQL;

pvsbandi

ASKER

Thanks!
When I compile, it throws an error
SQL State 42704
Unrecognized exception condition "v_start_dt"
Looks like EXCEPTION WHEN v_start_dt is not correct

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pvsbandi

ASKER

Thank you.