TRY

PL/pgSQL built-in procedural language for PostgreSQL DBMS lacks familiar statements TRY/CATCH for catching exceptions raised in the code at run time. The analogue is the operator EXCEPTIONwhich is used in the construction:

BEGIN
  -- код, в котором может возникнуть исключение
EXCEPTION WHEN OTHERS -- аналог catch  
THEN
    -- код, обрабатывающий исключение
END

If it is necessary to process only a specific error, then in the condition WHEN must be specified identifier or code of a specific error:

BEGIN
  -- код, в котором может возникнуть исключение
EXCEPTION WHEN '<идентификатор_или_код_ошибки>'
THEN
    -- код, обрабатывающий исключение
END

Inside the section EXCEPTION error code can be obtained from a variable SQLSTATEand the error text from the variable SQLERRM:

BEGIN
  -- код, в котором может возникнуть исключение
EXCEPTION WHEN OTHERS  
THEN
    RAISE NOTICE 'ERROR CODE: %. MESSAGE TEXT: %', SQLSTATE, SQLERRM;
END

More detailed information on the exception can be obtained with the command GET STACKED DIAGNOSTICS:

BEGIN
  -- код, в котором может возникнуть исключение
EXCEPTION WHEN OTHERS  
THEN
		GET STACKED DIAGNOSTICS
    	err_code = RETURNED_SQLSTATE, -- код ошибки
		msg_text = MESSAGE_TEXT, -- текст ошибки
    	exc_context = PG_CONTEXT, -- контекст исключения
 		msg_detail = PG_EXCEPTION_DETAIL, -- подробный текст ошибки
 		exc_hint = PG_EXCEPTION_HINT; -- текст подсказки к исключению

    RAISE NOTICE 'ERROR CODE: % MESSAGE TEXT: % CONTEXT: % DETAIL: % HINT: %', 
   	err_code, msg_text, exc_context, msg_detail, exc_hint;
END

The complete list of variables that can be obtained with the command GET STACKED DIAGNOSTICS:

Name	Type	Description
`RETURNED_SQLSTATE`	`text`	exception code
`COLUMN_NAME`	`text`	the name of the column related to the exception
`CONSTRAINT_NAME`	`text`	the name of the integrity constraint associated with the exception
`PG_DATATYPE_NAME`	`text`	the name of the data type associated with the exception
`MESSAGE_TEXT`	`text`	the text of the main message of the exception
`TABLE_NAME`	`text`	the name of the table related to the exception
`SCHEMA_NAME`	`text`	the name of the schema associated with the exception
`PG_EXCEPTION_DETAIL`	`text`	the text of the detailed exception message (if any)
`PG_EXCEPTION_HINT`	`text`	exception hint text (if any)
`PG_EXCEPTION_CONTEXT`	`text`	lines of text describing the call stack at the time of the exception

Exception Handling Example

As an example, we will consider the handling of a divide-by-zero error in the function catch_exception:

CREATE OR REPLACE FUNCTION catch_exception
(
	arg_1 int,
	arg_2 int,
	OUT res int
)
LANGUAGE plpgsql
AS $$
DECLARE 
	err_code text;
	msg_text text;
	exc_context text;
BEGIN
	BEGIN
		res := arg_1 / arg_2;
	EXCEPTION 
	WHEN OTHERS 
  THEN
		res := 0;
    
		GET STACKED DIAGNOSTICS
    	err_code = RETURNED_SQLSTATE,
		msg_text = MESSAGE_TEXT,
    	exc_context = PG_CONTEXT;

   		RAISE NOTICE 'ERROR CODE: % MESSAGE TEXT: % CONTEXT: %', 
   		err_code, msg_text, exc_context;
  END;
END;
$$;

Function call catch_exception with meaning 0 as the second parameter will cause a divide-by-zero error:

DO $$
DECLARE 
	res int;
BEGIN
	SELECT e.res INTO res
	FROM catch_exception(4, 0) AS e;
	
	RAISE NOTICE 'Result: %', res;
END;
$$;

The results of the error handling will be printed to the console:

ERROR CODE: 22012 
MESSAGE TEXT: деление на ноль 
CONTEXT: функция PL/pgSQL catch_exception(integer,integer), строка 14, оператор 
GET STACKED DIAGNOSTICS
SQL-оператор: "SELECT e.res FROM catch_exception(4, 0) AS e"
функция PL/pgSQL inline_code_block, строка 5, оператор SQL-оператор
Result: 0