CODEWITHSUNDEEP
postgresql
ZORRO_BLANCO
ZORRO_BLANCO

Reputation: 909

Get error message from error code - PostgreSQL

In Postgresql is there a way to get the error message/description from error code:

https://github.com/postgres/postgres/blob/master/src/backend/utils/errcodes.txt

I need it to be automated, a function will be fine, something like this:

CREATE OR REPLACE FUNCTION ErrorMessage(IN Code char(5))
returns varchar(100)
AS $BODY$
begin
  return ????;
end;
$BODY$ LANGUAGE PLPGSQL;

Upvotes: 0

Views: 8032

Answers (1)

Jaisus
Jaisus

Reputation: 1109

I think what you are looking for lies here : https://www.postgresql.org/docs/11/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

So if you want to have details about an error you may treat your transactions as follows:

DECLARE
  text_var1 text;
  text_var2 text;
  text_var3 text;
BEGIN
  -- some processing which might cause an exception
  ...
EXCEPTION WHEN OTHERS THEN
  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                          text_var2 = PG_EXCEPTION_DETAIL,
                          text_var3 = PG_EXCEPTION_HINT;
END;

But if you still want to have the corresponding error designation based on the error code (https://www.postgresql.org/docs/current/errcodes-appendix.html), then you can build a table with all the infos in it (PG_ERROR_CODES(id varchar(64), description varchar(255))) and interrogate it with your function.

Upvotes: 4

Related Questions