Functions in PostgreSQL do not support explicit transactions. However, they do support implicit transactions.
This means that in case of an occurring error a complete ROLLBACK is executed. This works even if you call another function within the function.
Now it often happens that database changes have already been made, but you still want to execute a manual ROLLBACK with personalized errors within the function.
The following SQL code could be an approach:
CREATE OR REPLACE FUNCTION public.myFunctionName()
RETURNS json
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
declare
_lngkey character varying = 'eng';
__internal_error_number character varying = '';
__internal_error_message character varying = '';
__internal_error_add_info character varying = '';
__error_description character varying; __error_log_id bigint;
__sql_err_returned_sqlstate text; __sql_err_column_name text; __sql_err_constraint_name text; __sql_err_pg_datatype_name text; __sql_err_message_text text;
__sql_err_table_name text; __sql_err_schema_name text; __sql_err_pg_exception_detail text; __sql_err_pg_exception_hint text; __sql_err_pg_exception_context text;
BEGIN
BEGIN
INSERT INTO xxx(val) SELECT 'abc';
PERFORM public.yyy();
__internal_error_number = '0404';
__internal_error_message = 'Message not found';
__internal_error_add_info = 'I am a test-exception call';
RAISE EXCEPTION 'INTERNALERROR';
RETURN json_build_object( 'success', true );
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS __sql_err_returned_sqlstate = RETURNED_SQLSTATE, __sql_err_column_name = COLUMN_NAME, __sql_err_constraint_name = CONSTRAINT_NAME,
__sql_err_pg_datatype_name = PG_DATATYPE_NAME, __sql_err_message_text = MESSAGE_TEXT, __sql_err_table_name = TABLE_NAME,
__sql_err_schema_name = SCHEMA_NAME, __sql_err_pg_exception_detail = PG_EXCEPTION_DETAIL, __sql_err_pg_exception_hint = PG_EXCEPTION_HINT, __sql_err_pg_exception_context = PG_EXCEPTION_CONTEXT;
IF __sql_err_message_text = 'INTERNALERROR' THEN
__error_description = (SELECT _propdesc FROM public.sys_error_get( _lngkey, __internal_error_number));
RETURN json_build_object(
'success', false,
'errors', json_agg(
json_build_object(
'code', __internal_error_number,
'message', __error_description,
'moreInfo', __internal_error_add_info
)
)
);
END IF;
__error_description = (SELECT _propdesc FROM public.sys_error_get( _lngkey, '0001'));
__error_log_id = (SELECT public.sys_errorlog_set('500', __error_description, NULL, 0, 'myFunctionName', 0,
__sql_err_returned_sqlstate, __sql_err_column_name, __sql_err_constraint_name, __sql_err_pg_datatype_name, __sql_err_message_text, __sql_err_table_name, __sql_err_schema_name,
__sql_err_pg_exception_detail, __sql_err_pg_exception_hint, __sql_err_pg_exception_context));
RETURN json_build_object(
'success', false,
'errors', json_agg(
json_build_object(
'code', '500',
'message', __error_description,
'moreInfo', 'error_log.id ' || __error_log_id::text
)
)
);
END;
END;
$BODY$;