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

pvsbandiAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Please try this -



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      

IF (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');
ELSE
 INSERT INTO TB_CLIENT
 VALUES (V_ID,V_NAME,V_START_DT,V_END_DT);
END IF;

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;
0
 
Pawan KumarDatabase ExpertCommented:
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;
0
 
pvsbandiAuthor Commented:
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
0
 
pvsbandiAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.