Error handling in PostgreSQL

Hi All,

   I'm new to PostgreSQL.
Have a requirement to write a stored procedure, where the error code and description should be inserted into an error log table and the handler shall continue.
Can someone kindly give me a code example which satisfies my scenario?
Thanks in advance!
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:
Sample example for you -

CREATE OR REPLACE FUNCTION YourfunctionOrProcName (ParameterName1 Datatype , ParameterName2 Datatype ..etc) 
RETURNS void AS $$
BEGIN
	IF NEW.ParameterName1 IS NULL THEN		
		RAISE EXCEPTION 'ParameterName1 should not be NULL';
	ELSE
		INSERT INTO yourTable VALUES (1, 'SomeValue');
	END IF;
END;
$$ LANGUAGE plpgsql;

Open in new window


Here you have to create function as they do not have proc. Exception can be done using RAISE EXCEPTION.
0
pvsbandiAuthor Commented:
Thank you.
I need to capture any exception and insert into a error log table. So, I need something more generic.
Please advice
0
Pawan KumarDatabase ExpertCommented:
You need to use BEGIN END, EXCEPTION..

CREATE OR REPLACE FUNCTION YourfunctionOrProcName (ParameterName1 Datatype , ParameterName2 Datatype ..etc) 
RETURNS INT AS $$
BEGIN
raise notice 'Hello';
  ---Your Statements
	RETURN 1
EXCEPTION  
  WHEN OTHERS THEN 
  BEGIN 
    raise notice 'exception';
    RETURN 0;
  END;
END;
$$ LANGUAGE plpgsql;

Open in new window

0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

pvsbandiAuthor Commented:
Hi Pawan,

   What i'm expecting is to capture the error code, error description, and insert them into a table.
The stored procedure shall continue processing after the error is handled.

Do you have an example of this?
0
earth man2Commented:
This is well documented https://www.postgresql.org/docs/9.6/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

special variable SQLSTATE contains the error code of the raised exception  Table A-1 gives a list of error codes.
special variable SQLERRM contains the error message associated with the exception.
These variables are undefined outside exception handlers.

Beyond these two simple variables there is the pl/pgsql construct

GET STACKED DIAGNOSTICS to get more information like what table was being abused.
0
pvsbandiAuthor Commented:
Thank you!
Can you please tell me if this is fine?
 
EXCEPTION
   WHEN others THEN
       LV_ERRCODE = sqlstate,
       LV_MESSAGE = COALESCE(sqlerrm,'')||'::'
       				||TO_CHAR(current_timestamp, 'yyyy-mm-dd hh12:mi:ss AM')
       				||COALESCE(LV_PROCEDURE_NM,'')
                    ||'Placement id/ Payment detail id'||' :: '
                    ||COALESCE(CAST(LV_PLACEMENT_ID AS VARCHAR(10)),'')||' / '
                    ||COALESCE(CAST(LV_SEQ_PMNT_DETAIL_ID AS VARCHAR(10)),'');
                 
   INSERT INTO TBL_PGPS_LOG(PROGRAM_NAME,
                             ERROR_SQLCODE,
                             LOG_DESC_TX,
                             CREATED_BY,
                             CREATE_TS
                             )
   SELECT LV_PROCEDURE_NM,
   		   LV_ERRCODE,
           LV_MESSAGE,
           'PGPSUSER',
           TO_CHAR(current_timestamp, 'yyyy-mm-dd hh12:mi:ss AM');
   

Open in new window

0
earth man2Commented:
Some of the syntax needs tweaking other bits are style.  I personally would store the raw timestamp in the error log table  rather than an ascii representation of it.  You can change its appearance when you read it or create a view.  Also it's good form to add a column to identify the error trap with a unique number.

EXCEPTION
   WHEN others THEN
       LV_ERRCODE := sqlstate;
       LV_MESSAGE := COALESCE(sqlerrm,'')||'::'
                    || TO_CHAR(current_timestamp, 'yyyy-mm-dd hh12:mi:ss AM')
                     || COALESCE(LV_PROCEDURE_NM,'')
                  || 'Placement id/ Payment detail id'||' :: '
                  || COALESCE(CAST(LV_PLACEMENT_ID AS VARCHAR(10)),'')||' / '
                  || COALESCE(CAST(LV_SEQ_PMNT_DETAIL_ID AS VARCHAR(10)),'');
                 
   INSERT INTO TBL_PGPS_LOG(
     PROGRAM_NAME,
     ERROR_SQLCODE,
     LOG_DESC_TX,
     CREATED_BY,
     CREATE_TS )
     VALUES (
       LV_PROCEDURE_NM,
       LV_ERRCODE,
       LV_MESSAGE,
       CURRENT_USER,
       current_timestamp );
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:
Thanks a lot!
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.