psql-sqlstate-macro-name.patch

From 4098c6d1a27f60027b9e4afa5d3ace98cd969712 Mon Sep 17 00:00:00 2001
From: Aaryan Parik <aaryanparik124@gmail.com>
Date: Sat, 23 May 2026 19:10:57 +0000
Subject: [PATCH] psql: Display SQLSTATE macro name in verbose error reports

---
 src/bin/psql/Makefile                  |  7 ++-
 src/bin/psql/command.c                 |  9 ++++
 src/bin/psql/common.c                  | 28 ++++++++++
 src/bin/psql/generate-errcode-names.pl | 71 ++++++++++++++++++++++++++
 src/bin/psql/meson.build               | 11 ++++
 5 files changed, 125 insertions(+), 1 deletion(-)
 create mode 100644 src/bin/psql/generate-errcode-names.pl

diff --git a/src/bin/psql/Makefile b/src/bin/psql/Makefile
index be00326..3b66ec1 100644
--- a/src/bin/psql/Makefile
+++ b/src/bin/psql/Makefile
@@ -64,6 +64,11 @@ psqlscanslash.c: FLEX_NO_BACKUP=yes
 tab-complete.c: gen_tabcomplete.pl tab-complete.in.c
 	$(PERL) $^ --outfile $@
 
+common.o command.o: sqlstate_names.h
+
+sqlstate_names.h: generate-errcode-names.pl $(top_srcdir)/src/backend/utils/errcodes.txt
+	$(PERL) $^ $@
+
 install: all installdirs
 	$(INSTALL_PROGRAM) psql$(X) '$(DESTDIR)$(bindir)/psql$(X)'
 	$(INSTALL_DATA) $(srcdir)/psqlrc.sample '$(DESTDIR)$(datadir)/psqlrc.sample'
@@ -77,7 +82,7 @@ uninstall:
 clean distclean:
 	rm -f psql$(X) $(OBJS) lex.backup
 	rm -rf tmp_check
-	rm -f sql_help.h sql_help.c psqlscanslash.c tab-complete.c
+	rm -f sql_help.h sql_help.c psqlscanslash.c tab-complete.c sqlstate_names.h
 
 check:
 	$(prove_check)
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 01b8f11..7d2731b 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -28,6 +28,7 @@
 #include "command.h"
 #include "common.h"
 #include "common/logging.h"
+#include "sqlstate_names.h"
 #include "common/string.h"
 #include "copy.h"
 #include "describe.h"
@@ -1656,6 +1657,14 @@ exec_command_errverbose(PsqlScanState scan_state, bool active_branch)
 			{
 				pg_log_error("%s", msg);
 				PQfreemem(msg);
+
+				const char *sqlstate = PQresultErrorField(pset.last_error_result, PG_DIAG_SQLSTATE);
+				if (sqlstate)
+				{
+					const char *sym = get_sqlstate_symbolic_name(sqlstate);
+					if (sym)
+						pg_log_error("SQLSTATE name: %s\n", sym);
+				}
 			}
 			else
 				puts(_("out of memory"));
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 1a4e2ea..4a54a08 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -22,6 +22,7 @@
 #include "command.h"
 #include "common.h"
 #include "common/logging.h"
+#include "sqlstate_names.h"
 #include "copy.h"
 #include "crosstabview.h"
 #include "fe_utils/cancel.h"
@@ -454,8 +455,21 @@ AcceptResult(const PGresult *result, bool show_error)
 		const char *error = PQerrorMessage(pset.db);
 
 		if (strlen(error))
+		{
 			pg_log_info("%s", error);
 
+			if (pset.verbosity == PQERRORS_VERBOSE)
+			{
+				const char *sqlstate = PQresultErrorField(result, PG_DIAG_SQLSTATE);
+				if (sqlstate)
+				{
+					const char *sym = get_sqlstate_symbolic_name(sqlstate);
+					if (sym)
+						pg_log_info("SQLSTATE name: %s\n", sym);
+				}
+			}
+		}
+
 		CheckConnection();
 	}
 
@@ -1784,8 +1798,22 @@ ExecQueryAndProcessResults(const char *query,
 			const char *error = PQresultErrorMessage(result);
 
 			if (strlen(error))
+			{
 				pg_log_info("%s", error);
 
+				if (pset.verbosity == PQERRORS_VERBOSE)
+				{
+					const char *sqlstate = PQresultErrorField(result, PG_DIAG_SQLSTATE);
+					if (sqlstate)
+					{
+						const char *sym = get_sqlstate_symbolic_name(sqlstate);
+						if (sym)
+							pg_log_info("SQLSTATE name: %s\n", sym);
+					}
+				}
+			}
+
+
 			CheckConnection();
 			if (!is_watch)
 				SetResultVariables(result, false);
diff --git a/src/bin/psql/generate-errcode-names.pl b/src/bin/psql/generate-errcode-names.pl
new file mode 100644
index 0000000..80d5087
--- /dev/null
+++ b/src/bin/psql/generate-errcode-names.pl
@@ -0,0 +1,71 @@
+#!/usr/bin/perl
+#
+# Generate the sqlstate_names.h header from errcodes.txt
+# This maps the 5-character SQLSTATE code to its symbolic macro name for psql's verbose error reports.
+
+use strict;
+use warnings;
+
+my $infile = shift;
+my $outfile = shift;
+
+open my $in, '<', $infile or die "Could not open $infile: $!";
+open my $out, '>', $outfile or die "Could not open $outfile: $!";
+
+print $out "/* autogenerated from src/backend/utils/errcodes.txt, do not edit */\n";
+print $out "/* This file maps SQLSTATE codes to their symbolic names. */\n\n";
+
+print $out "static const char *\n";
+print $out "get_sqlstate_symbolic_name(const char *sqlstate)\n";
+print $out "{\n";
+print $out "    if (sqlstate == NULL)\n";
+print $out "        return NULL;\n\n";
+
+my %sqlstates;
+
+while (<$in>) {
+    chomp;
+    s/#.*//; # Remove comments
+    next if /^\s*$/; # Skip empty lines
+    next if /^Section:/; # Skip section headers
+
+    if (/^([^\s]{5})\s+[EWS]\s+([^\s]+)/) {
+        my $sqlstate = $1;
+        my $macro_name = $2;
+        $sqlstates{$sqlstate} = $macro_name;
+    }
+}
+
+# We use a simple if-else chain. Since there are around 200-300 codes, a switch statement
+# based on strings isn't natively supported in C, but a series of if-strcmp is fast enough
+# for error reporting, or we can use a small static array and binary search it.
+# A static array and binary search is cleaner and scales better.
+
+print $out "    static const struct {\n";
+print $out "        const char sqlstate[6];\n";
+print $out "        const char *macro_name;\n";
+print $out "    } errcodes[] = {\n";
+
+foreach my $sqlstate (sort keys %sqlstates) {
+    print $out "        {\"$sqlstate\", \"$sqlstates{$sqlstate}\"},\n";
+}
+
+print $out "    };\n\n";
+print $out "    int low = 0;\n";
+print $out "    int high = (sizeof(errcodes) / sizeof(errcodes[0])) - 1;\n\n";
+print $out "    while (low <= high) {\n";
+print $out "        int mid = low + (high - low) / 2;\n";
+print $out "        int cmp = strcmp(errcodes[mid].sqlstate, sqlstate);\n";
+print $out "        if (cmp == 0)\n";
+print $out "            return errcodes[mid].macro_name;\n";
+print $out "        else if (cmp < 0)\n";
+print $out "            low = mid + 1;\n";
+print $out "        else\n";
+print $out "            high = mid - 1;\n";
+print $out "    }\n\n";
+
+print $out "    return NULL;\n";
+print $out "}\n";
+
+close $in;
+close $out;
diff --git a/src/bin/psql/meson.build b/src/bin/psql/meson.build
index 922b284..8a11de7 100644
--- a/src/bin/psql/meson.build
+++ b/src/bin/psql/meson.build
@@ -34,6 +34,17 @@ tabcomplete = custom_target('tabcomplete',
 generated_sources += tabcomplete
 psql_sources += tabcomplete
 
+sqlstate_names = custom_target('sqlstate_names',
+  input: '@SOURCE_ROOT@/src/backend/utils/errcodes.txt',
+  output: 'sqlstate_names.h',
+  command: [
+    perl, files('generate-errcode-names.pl'), '@INPUT@', '@OUTPUT@',
+  ],
+)
+generated_sources += sqlstate_names
+psql_sources += sqlstate_names
+
+
 sql_help = custom_target('psql_help',
   output: ['sql_help.c', 'sql_help.h'],
   depfile: 'sql_help.dep',
-- 
2.53.0