0001-Add-sqlstate-output-mode-to-VERBOSITY_v1.patch

From e6d0261156838b07a5a253355552fa0121d6d5c5 Mon Sep 17 00:00:00 2001
From: didier <didier@users.sourceforge.net>
Date: Mon, 3 Dec 2018 19:20:54 +0100
Subject: [PATCH] Add sqlstate output mode to VERBOSITY

Returned messages include severity and SQLSTATE error code.
---
 doc/src/sgml/libpq.sgml             | 28 +++++++++++++++------------
 doc/src/sgml/ref/psql-ref.sgml      |  7 ++++---
 src/bin/psql/help.c                 |  2 +-
 src/bin/psql/startup.c              |  4 +++-
 src/bin/psql/tab-complete.c         |  2 +-
 src/interfaces/libpq/fe-protocol3.c |  9 +++++++++
 src/interfaces/libpq/libpq-fe.h     |  3 ++-
 src/test/regress/expected/psql.out  | 30 +++++++++++++++++++++++++++++
 src/test/regress/sql/psql.sql       | 19 ++++++++++++++++++
 9 files changed, 85 insertions(+), 19 deletions(-)

diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 2e9fe760eb..e0c3a4fb9f 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -411,7 +411,7 @@ helpVariables(unsigned short int pager)
 	fprintf(output, _("  USER\n"
 					  "    the currently connected database user\n"));
 	fprintf(output, _("  VERBOSITY\n"
-					  "    controls verbosity of error reports [default, verbose, terse]\n"));
+					  "    controls verbosity of error reports [default, verbose, terse, sqlstate]\n"));
 	fprintf(output, _("  VERSION\n"
 					  "  VERSION_NAME\n"
 					  "  VERSION_NUM\n"
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index e7536a8a06..293ffcc5ef 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -1088,9 +1088,11 @@ verbosity_hook(const char *newval)
 		pset.verbosity = PQERRORS_TERSE;
 	else if (pg_strcasecmp(newval, "verbose") == 0)
 		pset.verbosity = PQERRORS_VERBOSE;
+	else if (pg_strcasecmp(newval, "sqlstate") == 0)
+		pset.verbosity = PQERRORS_SQLSTATE;
 	else
 	{
-		PsqlVarEnumError("VERBOSITY", newval, "default, terse, verbose");
+		PsqlVarEnumError("VERBOSITY", newval, "default, terse, sqlstate, verbose");
 		return false;
 	}
 
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index fa44b2820b..3f0ad5192a 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3571,7 +3571,7 @@ psql_completion(const char *text, int start, int end)
 		else if (TailMatchesCS("SHOW_CONTEXT"))
 			COMPLETE_WITH_CS("never", "errors", "always");
 		else if (TailMatchesCS("VERBOSITY"))
-			COMPLETE_WITH_CS("default", "verbose", "terse");
+			COMPLETE_WITH_CS("default", "verbose", "terse", "sqlstate");
 	}
 	else if (TailMatchesCS("\\sf*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_routines, NULL);
diff --git a/src/interfaces/libpq/fe-protocol3.c b/src/interfaces/libpq/fe-protocol3.c
index 8345faface..c58eabcd10 100644
--- a/src/interfaces/libpq/fe-protocol3.c
+++ b/src/interfaces/libpq/fe-protocol3.c
@@ -1017,6 +1017,15 @@ pqBuildErrorMessage3(PQExpBuffer msg, const PGresult *res,
 	val = PQresultErrorField(res, PG_DIAG_SEVERITY);
 	if (val)
 		appendPQExpBuffer(msg, "%s:  ", val);
+	if (verbosity == PQERRORS_SQLSTATE)
+	{
+		val = PQresultErrorField(res, PG_DIAG_SQLSTATE);
+		if (val)
+			appendPQExpBuffer(msg, "%s", val);
+		appendPQExpBufferChar(msg, '\n');
+		return;
+	}
+
 	if (verbosity == PQERRORS_VERBOSE)
 	{
 		val = PQresultErrorField(res, PG_DIAG_SQLSTATE);
diff --git a/src/interfaces/libpq/libpq-fe.h b/src/interfaces/libpq/libpq-fe.h
index 3f13ddf092..f280a19b5a 100644
--- a/src/interfaces/libpq/libpq-fe.h
+++ b/src/interfaces/libpq/libpq-fe.h
@@ -111,7 +111,8 @@ typedef enum
 {
 	PQERRORS_TERSE,				/* single-line error messages */
 	PQERRORS_DEFAULT,			/* recommended style */
-	PQERRORS_VERBOSE			/* all the facts, ma'am */
+	PQERRORS_VERBOSE,			/* all the facts, ma'am */
+	PQERRORS_SQLSTATE			/* single-line SQLSTATE error code */
 } PGVerbosity;
 
 typedef enum
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 1bb2a6e16d..64628f29a3 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1016,3 +1016,22 @@ select 1/(15-unique2) from tenk1 order by unique2 limit 19;
 \echo 'last error code:' :LAST_ERROR_SQLSTATE
 
 \unset FETCH_COUNT
+
+-- verbosity error setting
+\set VERBOSITY terse
+SELECT 1 UNION;
+\echo 'error:' :ERROR
+\echo 'error code:' :SQLSTATE
+\echo 'last error message:' :LAST_ERROR_MESSAGE
+
+\set VERBOSITY sqlstate
+SELECT 1 UNION;
+\echo 'error:' :ERROR
+\echo 'error code:' :SQLSTATE
+\echo 'last error message:' :LAST_ERROR_MESSAGE
+
+\set VERBOSITY default
+SELECT 1 UNION;
+\echo 'error:' :ERROR
+\echo 'error code:' :SQLSTATE
+\echo 'last error message:' :LAST_ERROR_MESSAGE
-- 
2.17.1

diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 775b127121..113d910202 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -4539,3 +4539,33 @@ last error message: division by zero
 \echo 'last error code:' :LAST_ERROR_SQLSTATE
 last error code: 22012
 \unset FETCH_COUNT
+-- verbosity error setting
+\set VERBOSITY terse
+SELECT 1 UNION;
+ERROR:  syntax error at or near ";" at character 15
+\echo 'error:' :ERROR
+error: true
+\echo 'error code:' :SQLSTATE
+error code: 42601
+\echo 'last error message:' :LAST_ERROR_MESSAGE
+last error message: syntax error at or near ";"
+\set VERBOSITY sqlstate
+SELECT 1 UNION;
+ERROR:  42601
+\echo 'error:' :ERROR
+error: true
+\echo 'error code:' :SQLSTATE
+error code: 42601
+\echo 'last error message:' :LAST_ERROR_MESSAGE
+last error message: syntax error at or near ";"
+\set VERBOSITY default
+SELECT 1 UNION;
+ERROR:  syntax error at or near ";"
+LINE 1: SELECT 1 UNION;
+                      ^
+\echo 'error:' :ERROR
+error: true
+\echo 'error code:' :SQLSTATE
+error code: 42601
+\echo 'last error message:' :LAST_ERROR_MESSAGE
+last error message: syntax error at or near ";"
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index d2e5b08541..3301c45b1d 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -5957,23 +5957,27 @@ typedef enum
 {
     PQERRORS_TERSE,
     PQERRORS_DEFAULT,
-    PQERRORS_VERBOSE
+    PQERRORS_VERBOSE,
+    PQERRORS_SQLSTATE
 } PGVerbosity;
 
 PGVerbosity PQsetErrorVerbosity(PGconn *conn, PGVerbosity verbosity);
 </synopsis>
 
-      <function>PQsetErrorVerbosity</function> sets the verbosity mode, returning
-      the connection's previous setting.  In <firstterm>TERSE</firstterm> mode,
-      returned messages include severity, primary text, and position only;
-      this will normally fit on a single line.  The default mode produces
-      messages that include the above plus any detail, hint, or context
-      fields (these might span multiple lines).  The <firstterm>VERBOSE</firstterm>
-      mode includes all available fields.  Changing the verbosity does not
-      affect the messages available from already-existing
-      <structname>PGresult</structname> objects, only subsequently-created ones.
-      (But see <function>PQresultVerboseErrorMessage</function> if you
-      want to print a previous error with a different verbosity.)
+      <function>PQsetErrorVerbosity</function> sets the verbosity mode,
+      returning the connection's previous setting.  In
+      <firstterm>SQLSTATE</firstterm> mode, returned messages include
+      severity and <symbol>SQLSTATE</symbol> error code.  In
+      <firstterm>TERSE</firstterm> mode, returned messages include severity,
+      primary text, and position only; this will normally fit on a single
+      line.  The default mode produces messages that include the above plus
+      any detail, hint, or context fields (these might span multiple lines). 
+      The <firstterm>VERBOSE</firstterm> mode includes all available fields. 
+      Changing the verbosity does not affect the messages available from
+      already-existing <structname>PGresult</structname> objects, only
+      subsequently-created ones.  (But see
+      <function>PQresultVerboseErrorMessage</function> if you want to print
+      a previous error with a different verbosity.)
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 6c76cf2f00..05946e8507 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3867,7 +3867,8 @@ bar
         messages from the server. The default is <literal>errors</literal> (meaning
         that context will be shown in error messages, but not in notice or
         warning messages).  This setting has no effect
-        when <varname>VERBOSITY</varname> is set to <literal>terse</literal>.
+        when <varname>VERBOSITY</varname> is set to <literal>sqlstate</literal>
+        or <literal>terse</literal>.
         (See also <command>\errverbose</command>, for use when you want a verbose
         version of the error you just got.)
         </para>
@@ -3921,8 +3922,8 @@ bar
         <listitem>
         <para>
         This variable can be set to the values <literal>default</literal>,
-        <literal>verbose</literal>, or <literal>terse</literal> to control the verbosity
-        of error reports.
+        <literal>verbose</literal>, <literal>sqlstate</literal> or <literal>terse</literal> 
+        to control the verbosity of error reports.
         (See also <command>\errverbose</command>, for use when you want a verbose
         version of the error you just got.)
         </para>