Error handling in PostgreSQL

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;

Select allOpen in new window

Here you have to create function as they do not have proc. Exception can be done using RAISE EXCEPTION.

Thank you.
I need to capture any exception and insert into a error log table. So, I need something more generic.
Please advice

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;

Select allOpen in new window

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?

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.

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');
   

Select allOpen in new window

Thanks a lot!