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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pvsbandiAuthor Commented:
Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.