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?
 
earth man2Connect With a Mentor Commented:
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
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
pvsbandiAuthor Commented:
Thanks a lot!
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.