Getting SQLSTATE in psql error messages

Question

I have the following problem.

SELECT * FROM tgvbn();
ERROR:  function tgvbn() does not exist
LINE 1: SELECT * FROM tgvbn();
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Since Appendix A of the official documentation only states error_codes and conditional names but not the actual messages, one can only guess that "No function matches..." refers to 42883 / undefined_function. I can set log_error_verbosity = verbose in postgresql.conf - but that will affect the logs, not the messages returned to the client:

Controls the amount of detail written in the server log for each message that is logged.

Well, I can consult the logs for this piece of information, but is there any means of including it in the error messages?

score 11 · Accepted Answer · answered Nov 16 '12 at 15:03

Based on a_horse_with_no_name's comment, I started searching around psql and found the solution:

\set VERBOSITY verbose
SELECT * FROM tgvbn();

ERROR:  42883: function vfjkb() does not exist
...

Now that goes into .psqlrc. Details and further options can be found in the psql documentation.

score 1 · Answer 2 · answered May 21 '20 at 04:28

It looks like since version 9.2 there is functionality for this:

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

Getting SQLSTATE in psql error messages

2 Answers2