Subject: [PATCH] Add PROMPT_COMMAND and dynamic prompt support to psql

From: Kirk Roybal <[email-masked]>
Date: Sun, 5 Jul 2026 00:41:13 -0500

Hello Folks,

This patch adds optional, bash-like dynamic prompt support to psql so the
interactive prompt can be regenerated before each readline() call.  The
motivation is to let users plug in external prompt renderers (status lines,
connection context, last-command exit status) without forking psql.

Picture attached to see it in action.

Comments of all sorts welcome.

Summary
-------

* PROMPT_COMMAND — if set, run a shell command before each interactive
prompt;
  capture the first line of stdout (truncated to the existing prompt buffer
  limit).

* %D — new PROMPT1/PROMPT2 escape; substitute the most recent PROMPT_COMMAND
  stdout.  Intended for embedding renderer output in the prompt string.

* SHELL_EXIT — new psql variable holding the exit status of the last user
  command: 0 on success, 1 on SQL failure, or the real exit code for \! and
  similar shell invocations.  PROMPT_COMMAND does not update SHELL_EXIT, so
  renderers can reflect the prior command's outcome.

* PROMPT_SESSION_EXPORT — new boolean psql variable, off by default.  When
on,
  export current-session PG* and PSQL_* environment variables to the
  PROMPT_COMMAND subprocess (connection target, transaction state, row
count,
  PostgreSQL superuser flag, optional txid from a user-defined :txid
variable).
  This is separate from PROMPT_COMMAND so users who only need %D without
  touching the subprocess environment can leave it off.

* Prompt refresh — call run_prompt_command() immediately before readline()
in
  input.c (replacing an rl_pre_input_hook approach that left the prompt
blank
  until the first keypress).

* \connect housekeeping — after a successful \connect, reset ROW_COUNT and
  SHELL_EXIT and optionally refresh :txid when that variable exists.

Backward compatibility
----------------------

No behavior change unless the user configures the new facilities in .psqlrc
(or equivalent).  PROMPT_COMMAND, %D, and PROMPT_SESSION_EXPORT are all
inert
until set.  Existing scripts and non-interactive use are unaffected.

I am happy to rename variables, change defaults, add further gating, or
split
the patch if reviewers prefer a narrower initial commit.

Testing
-------

Manual testing on Linux with readline:

* PROMPT_COMMAND + %D rendering before each prompt
* \c with spaces preserved in readline (e.g. \c postgres)
* SHELL_EXIT after successful SQL, failed SQL, and \!
* PROMPT_SESSION_EXPORT on/off
* \connect reset of ROW_COUNT / SHELL_EXIT and txid refresh

I have not added a TAP test yet; I can add coverage in src/bin/psql/t/ if
that is desired before commit.

Companion work (not part of this patch)
---------------------------------------

A reference implementation for the Powerline prompt utility is maintained
separately:

  https://github.com/powerline/powerline/pull/2287

Integration notes for that companion (example .psqlrc, exported variables,
readline marker requirements) are in the patch as:

  src/bin/psql/powerline-integration.md

That file is documentation only and is not installed.  The two projects can
be adopted independently; full functionality requires both plus user config.

References
----------

Fork branch (for convenience):

  https://github.com/bithead2k/postgres/tree/psql-prompt-command

Tracking PR on the mirror (not upstream merge):

  https://github.com/bithead2k/postgres/pull/1

Patch is attached (git format-patch output).  A screenshot of the companion
powerline prompt in use is also attached as powerline-prompt-example.png and
checked in as src/bin/psql/powerline-prompt-example.png (see
powerline-integration.md).

Comments welcome.

Regards,
Kirk Roybal
From 8f0eb0f8e321bd3af842e145fd0e91361980c8ab Mon Sep 17 00:00:00 2001
From: bithead2k <[email-masked]>
Date: Fri, 3 Jul 2026 18:56:59 -0500
Subject: [PATCH] Add PROMPT_COMMAND and dynamic prompt support to psql

Introduce bash-like PROMPT_COMMAND handling that runs before each
interactive readline prompt, with a %D escape to embed the first line
of stdout in PROMPT1/PROMPT2.  Add SHELL_EXIT for last-command status
and PROMPT_SESSION_EXPORT (off by default) to optionally export PG*
and PSQL_* environment for external prompt renderers.

Includes reset_prompt_status_after_connect() after successful \connect,
prompt regeneration in input.c, and powerline-integration.md documenting
a companion patch for the powerline psql extension.

Backward compatible: no behavior change until PROMPT_COMMAND and related
variables are configured in .psqlrc.
---
 scripts/PR-description-postgres.md    |  57 +++++++
 src/bin/psql/command.c                |   2 +
 src/bin/psql/common.c                 |  25 ++-
 src/bin/psql/help.c                   |   7 +
 src/bin/psql/input.c                  |  19 ++-
 src/bin/psql/input.h                  |   5 +-
 src/bin/psql/mainloop.c               |   3 +-
 src/bin/psql/powerline-integration.md |  67 ++++++++
 src/bin/psql/prompt.c                 | 213 ++++++++++++++++++++++++--
 src/bin/psql/prompt.h                 |   2 +
 src/bin/psql/startup.c                |   1 +
 11 files changed, 384 insertions(+), 17 deletions(-)
 create mode 100644 scripts/PR-description-postgres.md
 create mode 100644 src/bin/psql/powerline-integration.md

diff --git a/scripts/PR-description-postgres.md b/scripts/PR-description-postgres.md
new file mode 100644
index 0000000000..e3dcd65d21
--- /dev/null
+++ b/scripts/PR-description-postgres.md
@@ -0,0 +1,57 @@
+# PostgreSQL patch: dynamic psql prompts (PROMPT_COMMAND)
+
+## Summary
+
+Add bash-like `PROMPT_COMMAND` support to psql so interactive prompts can be
+regenerated before each `readline()` call, with optional session export for
+external prompt renderers.  A companion patch for the
+[powerline](https://github.com/powerline/powerline) project provides a
+reference implementation; see `src/bin/psql/powerline-integration.md`.
+
+## Backward compatibility
+
+- **Default behavior unchanged** for users who do not set new variables.
+- `PROMPT_COMMAND` and `%D` are inert until configured (same model as bash).
+- `PROMPT_SESSION_EXPORT` defaults to **off**; session environment is only
+  exported when explicitly enabled, so existing `PROMPT_COMMAND` uses are not
+  surprised by extra `setenv()` calls.
+- New psql variables (`SHELL_EXIT`, etc.) do not alter query execution.
+
+We are happy to adjust naming, defaults, or add further gating if reviewers
+prefer a different compatibility story.
+
+## Features
+
+1. **`PROMPT_COMMAND`** — run a shell command before each interactive prompt;
+   capture first line of stdout.
+2. **`%D` prompt escape** — insert `PROMPT_COMMAND` output into `PROMPT1`/`PROMPT2`.
+3. **`SHELL_EXIT`** — last command exit status (SQL failure → 1, `\!` → real
+   shell code); not updated by `PROMPT_COMMAND` itself.
+4. **`PROMPT_SESSION_EXPORT`** — when on, export `PG*` and `PSQL_*` env vars
+   for the `PROMPT_COMMAND` subprocess (connection info, txn state, row count,
+   superuser flag, optional `txid`).
+5. **Prompt refresh** — `run_prompt_command()` before each `readline()` (fixes
+   stale prompts without `rl_pre_input_hook` hacks).
+6. **Connect reset** — clear `ROW_COUNT`/`SHELL_EXIT` and refresh optional
+   `txid` after successful `\connect`.
+
+## Files touched
+
+- `src/bin/psql/prompt.c`, `prompt.h` — core prompt/PROMPT_COMMAND logic
+- `src/bin/psql/input.c`, `input.h` — regenerate prompt before readline
+- `src/bin/psql/common.c` — `SHELL_EXIT` tracking
+- `src/bin/psql/command.c` — connect hook
+- `src/bin/psql/mainloop.c`, `startup.c`, `help.c`
+- `src/bin/psql/powerline-integration.md` — integration notes (not installed)
+
+## Testing
+
+Manual: interactive psql with `PROMPT_COMMAND`, `%D`, `\c`, failed SQL,
+`\!`, and readline editing (e.g. `\c postgres` must retain spaces).
+
+Suggested follow-up: TAP test for `%D` and `SHELL_EXIT` in `src/bin/psql/t/`.
+
+## Companion patch
+
+powerline psql extension (separate PR): segments, theme, readline renderer.
+Cross-reference: `src/bin/psql/powerline-integration.md`.
\ No newline at end of file
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 7fdd4511a3..9a1be6e7cd 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -27,6 +27,7 @@
 #include "catalog/pg_class_d.h"
 #include "command.h"
 #include "common.h"
+#include "prompt.h"
 #include "common/logging.h"
 #include "common/string.h"
 #include "copy.h"
@@ -4324,6 +4325,7 @@ do_connect(enum trivalue reuse_previous_specification,
 	pset.db = n_conn;
 	SyncVariables();
 	connection_warnings(false); /* Must be after SyncVariables */
+	reset_prompt_status_after_connect();
 
 	/* Tell the user about the new connection */
 	if (!pset.quiet)
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 7db8025d24..ac72fff4ed 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -463,6 +463,22 @@ AcceptResult(const PGresult *result, bool show_error)
 }

+/*
+ * Set SHELL_EXIT, the exit status of the last psql command (SQL or shell).
+ *
+ * SQL failures use exit code 1.  Shell commands use the real exit code
+ * (0-127, 128+signal, or -1).  PROMPT_COMMAND does not update this variable.
+ */
+static void
+SetLastExitVariable(int exit_code)
+{
+	char		buf[32];
+
+	snprintf(buf, sizeof(buf), "%d", exit_code);
+	SetVariable(pset.vars, "SHELL_EXIT", buf);
+}
+
+
 /*
  * Set special variables from a query result
  * - ERROR: true/false, whether an error occurred on this query
@@ -470,6 +486,7 @@ AcceptResult(const PGresult *result, bool show_error)
  * - ROW_COUNT: how many rows were returned or affected, or "0"
  * - LAST_ERROR_SQLSTATE: same for last error
  * - LAST_ERROR_MESSAGE: message of last error
+ * - SHELL_EXIT: 0 on success, 1 on failure
  *
  * Note: current policy is to apply this only to the results of queries
  * entered by the user, not queries generated by slash commands.
@@ -484,6 +501,7 @@ SetResultVariables(PGresult *result, bool success)
 		SetVariable(pset.vars, "ERROR", "false");
 		SetVariable(pset.vars, "SQLSTATE", "00000");
 		SetVariable(pset.vars, "ROW_COUNT", *ntuples ? ntuples : "0");
+		SetLastExitVariable(0);
 	}
 	else
 	{
@@ -502,6 +520,7 @@ SetResultVariables(PGresult *result, bool success)
 		SetVariable(pset.vars, "ROW_COUNT", "0");
 		SetVariable(pset.vars, "LAST_ERROR_SQLSTATE", code);
 		SetVariable(pset.vars, "LAST_ERROR_MESSAGE", mesg ? mesg : "");
+		SetLastExitVariable(1);
 	}
 }
 
@@ -510,6 +529,7 @@ SetResultVariables(PGresult *result, bool success)
  * Set special variables from a shell command result
  * - SHELL_ERROR: true/false, whether command returned exit code 0
  * - SHELL_EXIT_CODE: exit code according to shell conventions
+ * - SHELL_EXIT: same exit code as SHELL_EXIT_CODE
  *
  * The argument is a wait status as returned by wait(2) or waitpid(2),
  * which also applies to pclose(3) and system(3).
@@ -518,11 +538,13 @@ void
 SetShellResultVariables(int wait_result)
 {
 	char		buf[32];
+	int			exit_code = wait_result_to_exit_code(wait_result);
 
 	SetVariable(pset.vars, "SHELL_ERROR",
 				(wait_result == 0) ? "false" : "true");
-	snprintf(buf, sizeof(buf), "%d", wait_result_to_exit_code(wait_result));
+	snprintf(buf, sizeof(buf), "%d", exit_code);
 	SetVariable(pset.vars, "SHELL_EXIT_CODE", buf);
+	SetLastExitVariable(exit_code);
 }

@@ -2037,6 +2059,7 @@ ExecQueryAndProcessResults(const char *query,
 				SetVariable(pset.vars, "SQLSTATE", "00000");
 				snprintf(buf, sizeof(buf), INT64_FORMAT, total_tuples);
 				SetVariable(pset.vars, "ROW_COUNT", buf);
+				SetLastExitVariable(0);
 				/* Prevent SetResultVariables call below */
 				is_chunked_result = true;
 
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index bfd3fea177..895ec37e1e 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -433,6 +433,10 @@ helpVariables(unsigned short int pager)
 		  "    specifies the standard psql prompt\n");
 	HELP0("  PROMPT2\n"
 		  "    specifies the prompt used when a statement continues from a previous line\n");
+	HELP0("  PROMPT_COMMAND\n"
+		  "    shell command executed before each prompt; stdout available as %D\n");
+	HELP0("  PROMPT_SESSION_EXPORT\n"
+		  "    if on, export PG* and PSQL_* environment for PROMPT_COMMAND (off by default)\n");
 	HELP0("  PROMPT3\n"
 		  "    specifies the prompt used during COPY ... FROM STDIN\n");
 	HELP0("  QUIET\n"
@@ -444,6 +448,9 @@ helpVariables(unsigned short int pager)
 		  "    server's version (in short string or numeric format)\n");
 	HELP0("  SHELL_ERROR\n"
 		  "    \"true\" if the last shell command failed, \"false\" if it succeeded\n");
+	HELP0("  SHELL_EXIT\n"
+		  "    exit status of the last SQL or shell command (0 on success, 1 on SQL error,\n"
+		  "    or the shell exit code for \\!, \\copy, etc.)\n");
 	HELP0("  SHELL_EXIT_CODE\n"
 		  "    exit status of the last shell command\n");
 	HELP0("  SHOW_ALL_RESULTS\n"
diff --git a/src/bin/psql/input.c b/src/bin/psql/input.c
index 6d37359fc9..bb34196e36 100644
--- a/src/bin/psql/input.c
+++ b/src/bin/psql/input.c
@@ -16,6 +16,7 @@
 #include "common.h"
 #include "common/logging.h"
 #include "input.h"
+#include "prompt.h"
 #include "settings.h"
 #include "tab-complete.h"
 
@@ -55,7 +56,7 @@ static void finishInput(void);
  *
  * Gets a line of interactive input, using readline if desired.
  *
- * prompt: the prompt string to be used
+ * status, cstack: prompt state passed to get_prompt()
  * query_buf: buffer containing lines already read in the current command
  * (query_buf is not modified here, but may be consulted for tab completion)
  *
@@ -64,12 +65,14 @@ static void finishInput(void);
  * Caller *must* have set up sigint_interrupt_jmp before calling.
  */
 char *
-gets_interactive(const char *prompt, PQExpBuffer query_buf)
+gets_interactive(promptStatus_t status, ConditionalStack cstack,
+				 PQExpBuffer query_buf)
 {
 #ifdef USE_READLINE
 	if (useReadline)
 	{
 		char	   *result;
+		const char *prompt;
 
 		/*
 		 * Some versions of readline don't notice SIGWINCH signals that arrive
@@ -82,6 +85,15 @@ gets_interactive(const char *prompt, PQExpBuffer query_buf)
 		rl_reset_screen_size();
 #endif
 
+		/*
+		 * Regenerate the prompt immediately before readline(), the same way
+		 * bash runs PROMPT_COMMAND and re-evaluates PS1.  Using
+		 * rl_pre_input_hook with readline("") leaves the prompt invisible
+		 * until the user presses a key.
+		 */
+		run_prompt_command();
+		prompt = get_prompt(status, cstack);
+
 		/* Make current query_buf available to tab completion callback */
 		tab_completion_query_buf = query_buf;
 
@@ -100,7 +112,8 @@ gets_interactive(const char *prompt, PQExpBuffer query_buf)
 	}
 #endif
 
-	fputs(prompt, stdout);
+	run_prompt_command();
+	fputs(get_prompt(status, cstack), stdout);
 	fflush(stdout);
 	return gets_fromFile(stdin);
 }
diff --git a/src/bin/psql/input.h b/src/bin/psql/input.h
index 2a47166347..14b73c12f5 100644
--- a/src/bin/psql/input.h
+++ b/src/bin/psql/input.h
@@ -44,10 +44,13 @@
 #endif							/* HAVE_READLINE_READLINE_H, etc */
 #endif							/* HAVE_LIBREADLINE */
 
+#include "fe_utils/conditional.h"
+#include "fe_utils/psqlscan.h"
 #include "pqexpbuffer.h"

-extern char *gets_interactive(const char *prompt, PQExpBuffer query_buf);
+extern char *gets_interactive(promptStatus_t status, ConditionalStack cstack,
+							  PQExpBuffer query_buf);
 extern char *gets_fromFile(FILE *source);
 
 extern void initializeInput(int flags);
diff --git a/src/bin/psql/mainloop.c b/src/bin/psql/mainloop.c
index 1f409a573b..90dc453628 100644
--- a/src/bin/psql/mainloop.c
+++ b/src/bin/psql/mainloop.c
@@ -163,8 +163,7 @@ MainLoop(FILE *source)
 				need_redisplay = false;
 			}
 			/* Now we can fetch a line */
-			line = gets_interactive(get_prompt(prompt_status, cond_stack),
-									query_buf);
+			line = gets_interactive(prompt_status, cond_stack, query_buf);
 		}
 		else
 		{
diff --git a/src/bin/psql/powerline-integration.md b/src/bin/psql/powerline-integration.md
new file mode 100644
index 0000000000..b318d946f0
--- /dev/null
+++ b/src/bin/psql/powerline-integration.md
@@ -0,0 +1,67 @@
+# psql prompt extensions and Powerline integration
+
+This document describes optional prompt features added for dynamic prompt
+generation (similar in spirit to bash `PROMPT_COMMAND`) and their companion
+implementation in the [powerline](https://github.com/powerline/powerline) project.
+
+## Backward compatibility
+
+All behavior is **opt-in**.  With no `.psqlrc` changes, psql behaves as before
+except for the addition of new psql variables (`SHELL_EXIT`, `PROMPT_COMMAND`,
+`PROMPT_SESSION_EXPORT`, and the `%D` prompt escape) that have no effect until
+configured.
+
+| Switch | Default | Purpose |
+|--------|---------|---------|
+| `PROMPT_COMMAND` | unset | Run a shell command before each interactive prompt; first line of stdout is inserted via `%D` |
+| `PROMPT_SESSION_EXPORT` | off | When on, export `PG*` / `PSQL_*` environment for `PROMPT_COMMAND` subprocesses |
+| `%D` in `PROMPT1`/`PROMPT2` | unused | Substitute `PROMPT_COMMAND` output (only when `PROMPT_COMMAND` is set) |
+
+`PROMPT_SESSION_EXPORT` exists so maintainers can keep session export separate
+from `PROMPT_COMMAND` itself: users who only need `%D` without polluting the
+subprocess environment leave it off.
+
+## PostgreSQL side (this tree)
+
+- `PROMPT_COMMAND` — shell command before each prompt (like bash)
+- `%D` — insert first line of `PROMPT_COMMAND` stdout into `PROMPT1`/`PROMPT2`
+- `SHELL_EXIT` — exit status of the last SQL or shell command (for prompt themes)
+- `PROMPT_SESSION_EXPORT` — gate for exporting session state to the subprocess
+- Exported when `PROMPT_SESSION_EXPORT` is on: `PGDATABASE`, `PGUSER`, `PGHOST`,
+  `PGPORT`, `PSQL_SHELL_EXIT`, `PSQL_TXN`, `PSQL_ROW_COUNT`, `PSQL_TXID` (if
+  the `txid` variable is set), `PSQL_SUPERUSER`
+
+## Powerline side (companion patch)
+
+A matching **psql extension** is prepared for submission to powerline:
+
+- Repository: https://github.com/powerline/powerline
+- Extension: `psql` (`powerline-render psql left`)
+- Segments: user (with PostgreSQL superuser highlighting), database, host,
+  port, transaction, row count, txid, last exit status
+- Renderer: readline non-printing markers (`\x01`/`\x02`)
+
+See powerline’s `docs/source/usage/psql-postgres-integration.rst` (added in the
+companion patch) for `.psqlrc` configuration.
+
+## Example `.psqlrc` (requires both patches)
+
+```sql
+\set PROMPT_SESSION_EXPORT on
+\set PROMPT_COMMAND 'powerline-render psql left --last-exit-code ${PSQL_SHELL_EXIT:-0} -w ${COLUMNS:-120} 2>/dev/null'
+\set PROMPT1 '%D %x%# '
+\set PROMPT2 '%w%R%x%# '
+```
+
+Optional: snapshot transaction id at connect for the txid segment:
+
+```sql
+SELECT txid_current() AS txid \gset
+```
+
+## Deployment note
+
+Neither patch depends on the other at build time.  Full functionality appears
+when both are deployed and the user enables the variables above.  Either
+project can merge independently; cross-references in code and docs point to
+the companion implementation.
\ No newline at end of file
diff --git a/src/bin/psql/prompt.c b/src/bin/psql/prompt.c
index 4906f320e7..2d7762050e 100644
--- a/src/bin/psql/prompt.c
+++ b/src/bin/psql/prompt.c
@@ -13,11 +13,184 @@
 #endif
 
 #include "common.h"
+#include "common/logging.h"
 #include "common/string.h"
 #include "input.h"
 #include "libpq/pqcomm.h"
 #include "prompt.h"
 #include "settings.h"
+#include "variables.h"
+
+#define MAX_PROMPT_SIZE 8192
+#define MAX_PROMPT_SUBST_SIZE 8192
+
+/* stdout captured from the most recent PROMPT_COMMAND execution */
+static char prompt_command_result[MAX_PROMPT_SUBST_SIZE + 1];
+
+/*
+ * Export psql state for PROMPT_COMMAND subprocesses (powerline, etc.).
+ *
+ * Enabled only when the PROMPT_SESSION_EXPORT psql variable is on (see
+ * src/bin/psql/powerline-integration.md).  A companion implementation
+ * lives in the powerline project (psql extension).
+ *
+ * Connection settings use the standard libpq PG* names, set from the
+ * current session (PQhost, PQdb, etc.) rather than stale startup env.
+ * There is no PG* variable for last-command exit status; use
+ * PSQL_SHELL_EXIT (from :SHELL_EXIT) for that.  The psql variable ``txid`` is
+ * exported as PSQL_TXID when set (e.g. via ``\\gset`` at connect).
+ * PSQL_SUPERUSER is set to ``1`` or ``0`` from the connection's superuser
+ * status (same test used for the ``%#`` prompt escape).
+ */
+static bool
+prompt_session_export_enabled(void)
+{
+	const char *val = GetVariable(pset.vars, "PROMPT_SESSION_EXPORT");
+	bool		on = false;
+
+	if (val != NULL && ParseVariableBool(val, "PROMPT_SESSION_EXPORT", &on))
+		return on;
+
+	return false;
+}
+
+static void
+export_prompt_environment(void)
+{
+	const char *val;
+
+	val = GetVariable(pset.vars, "SHELL_EXIT");
+	if (val)
+		setenv("PSQL_SHELL_EXIT", val, 1);
+
+	if (pset.db)
+	{
+		setenv("PGDATABASE", PQdb(pset.db), 1);
+		setenv("PGUSER", session_username(), 1);
+
+		val = PQhost(pset.db);
+		if (val && val[0] != '\0')
+			setenv("PGHOST", val, 1);
+		else
+			unsetenv("PGHOST");
+
+		val = PQport(pset.db);
+		if (val && val[0] != '\0')
+			setenv("PGPORT", val, 1);
+		else
+			unsetenv("PGPORT");
+
+		switch (PQtransactionStatus(pset.db))
+		{
+			case PQTRANS_IDLE:
+				setenv("PSQL_TXN", "idle", 1);
+				break;
+			case PQTRANS_ACTIVE:
+			case PQTRANS_INTRANS:
+				setenv("PSQL_TXN", "active", 1);
+				break;
+			case PQTRANS_INERROR:
+				setenv("PSQL_TXN", "error", 1);
+				break;
+			default:
+				setenv("PSQL_TXN", "unknown", 1);
+				break;
+		}
+
+		setenv("PSQL_SUPERUSER", is_superuser() ? "1" : "0", 1);
+	}
+	else
+	{
+		unsetenv("PGDATABASE");
+		unsetenv("PGUSER");
+		unsetenv("PGHOST");
+		unsetenv("PGPORT");
+		unsetenv("PSQL_TXN");
+		unsetenv("PSQL_SUPERUSER");
+	}
+
+	val = GetVariable(pset.vars, "ROW_COUNT");
+	if (val)
+		setenv("PSQL_ROW_COUNT", val, 1);
+	else
+		unsetenv("PSQL_ROW_COUNT");
+
+	val = GetVariable(pset.vars, "txid");
+	if (val && val[0] != '\0')
+		setenv("PSQL_TXID", val, 1);
+	else
+		unsetenv("PSQL_TXID");
+}
+
+/*
+ * Run PROMPT_COMMAND, if set, before generating a prompt.
+ *
+ * Like bash's PROMPT_COMMAND, this executes a shell command before each
+ * prompt is displayed.  The first line of stdout is captured and can be
+ * inserted into PROMPT1/PROMPT2 via the %D escape.  (Unlike bash, psql
+ * cannot run the command in-process, so "export" in PROMPT_COMMAND will
+ * not affect later %`command` substitutions.)
+ */
+void
+run_prompt_command(void)
+{
+	const char *cmd = GetVariable(pset.vars, "PROMPT_COMMAND");
+	FILE	   *fd;
+	size_t		len = 0;
+	int			c;
+
+	prompt_command_result[0] = '\0';
+
+	if (cmd == NULL || cmd[0] == '\0')
+		return;
+
+	if (prompt_session_export_enabled())
+		export_prompt_environment();
+
+	fflush(NULL);
+	fd = popen(cmd, "r");
+	if (fd == NULL)
+		return;
+
+	while (len < MAX_PROMPT_SUBST_SIZE && (c = fgetc(fd)) != EOF)
+	{
+		if (c == '\n' || c == '\r')
+			break;
+		prompt_command_result[len++] = (char) c;
+	}
+	prompt_command_result[len] = '\0';
+
+	/*
+	 * Do not update SHELL_EXIT or SHELL_EXIT_CODE here: PROMPT_COMMAND is not
+	 * a user command, and overwriting would hide the status of the last SQL
+	 * or shell command (e.g. for powerline --last-exit-code).
+	 */
+	(void) pclose(fd);
+}
+
+/*
+ * Reset prompt-related psql variables after a successful \connect.
+ *
+ * Clears stale row-count and exit-status from the previous session, and
+ * refreshes :varname:`txid` when that variable is already defined (e.g. via
+ * ``\\gset`` in .psqlrc for powerline).
+ */
+void
+reset_prompt_status_after_connect(void)
+{
+	PGresult   *res;
+
+	SetVariable(pset.vars, "ROW_COUNT", "0");
+	SetVariable(pset.vars, "SHELL_EXIT", "0");
+
+	if (!pset.db || GetVariable(pset.vars, "txid") == NULL)
+		return;
+
+	res = PQexec(pset.db, "SELECT txid_current()::text");
+	if (PQresultStatus(res) == PGRES_TUPLES_OK && PQntuples(res) > 0)
+		SetVariable(pset.vars, "txid", PQgetvalue(res, 0, 0));
+	PQclear(res);
+}
 
 /*--------------------------
  * get_prompt
@@ -59,8 +232,16 @@
  *					 newline stripped.
  * %:name:		   - The value of the psql variable 'name'
  * (those will not be rescanned for more escape sequences!)
+ * %D			   - stdout from PROMPT_COMMAND (first line, no trailing newline)
  *
  * %[ ... %]	   - tell readline that the contained text is invisible
+ *					 (use around ANSI color sequences from prompt utilities)
+ *
+ * PROMPT_COMMAND   - if set, a shell command run before each prompt (see
+ *					 run_prompt_command()).
+ *
+ * PROMPT_SESSION_EXPORT - if on, export connection/session environment for
+ *					 PROMPT_COMMAND (see powerline-integration.md).
  *
  * If the application-wide prompts become NULL somehow, the returned string
  * will be empty (not NULL!).
@@ -70,14 +251,21 @@
 char *
 get_prompt(promptStatus_t status, ConditionalStack cstack)
 {
-#define MAX_PROMPT_SIZE 256
-	static char destination[MAX_PROMPT_SIZE + 1];
-	char		buf[MAX_PROMPT_SIZE + 1];
+	static PQExpBuffer destination = NULL;
+	char		buf[MAX_PROMPT_SUBST_SIZE + 1];
 	bool		esc = false;
 	const char *p;
 	const char *prompt_string = "? ";
 	static size_t last_prompt1_width = 0;
 
+	if (destination == NULL)
+		destination = createPQExpBuffer();
+	else
+		resetPQExpBuffer(destination);
+
+	if (PQExpBufferBroken(destination))
+		pg_fatal("out of memory");
+
 	switch (status)
 	{
 		case PROMPT_READY:
@@ -98,10 +286,8 @@ get_prompt(promptStatus_t status, ConditionalStack cstack)
 			break;
 	}
 
-	destination[0] = '\0';
-
 	for (p = prompt_string;
-		 *p && strlen(destination) < sizeof(destination) - 1;
+		 *p && destination->len < MAX_PROMPT_SIZE;
 		 p++)
 	{
 		memset(buf, 0, sizeof(buf));
@@ -307,6 +493,10 @@ get_prompt(promptStatus_t status, ConditionalStack cstack)
 					/* not here yet */
 					break;
 
+				case 'D':
+					strlcpy(buf, prompt_command_result, sizeof(buf));
+					break;
+
 				case '#':
 					if (is_superuser())
 						buf[0] = '#';
@@ -384,14 +574,17 @@ get_prompt(promptStatus_t status, ConditionalStack cstack)
 		}
 
 		if (!esc)
-			strlcat(destination, buf, sizeof(destination));
+			appendPQExpBufferStr(destination, buf);
 	}
 
+	if (PQExpBufferBroken(destination))
+		pg_fatal("out of memory");
+
 	/* Compute the visible width of PROMPT1, for PROMPT2's %w */
 	if (prompt_string == pset.prompt1)
 	{
-		char	   *p = destination;
-		char	   *end = p + strlen(p);
+		char	   *p = destination->data;
+		char	   *end = p + destination->len;
 		bool		visible = true;
 
 		last_prompt1_width = 0;
@@ -433,5 +626,5 @@ get_prompt(promptStatus_t status, ConditionalStack cstack)
 		}
 	}
 
-	return destination;
+	return destination->data;
 }
diff --git a/src/bin/psql/prompt.h b/src/bin/psql/prompt.h
index 3227cb6eeb..0c25a72038 100644
--- a/src/bin/psql/prompt.h
+++ b/src/bin/psql/prompt.h
@@ -13,5 +13,7 @@
 #include "fe_utils/psqlscan.h"
 
 char	   *get_prompt(promptStatus_t status, ConditionalStack cstack);
+void		run_prompt_command(void);
+void		reset_prompt_status_after_connect(void);
 
 #endif							/* PROMPT_H */
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index eafc7c9734..7412e47ca1 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -198,6 +198,7 @@ main(int argc, char *argv[])
 	/* Initialize variables for last error */
 	SetVariable(pset.vars, "LAST_ERROR_MESSAGE", "");
 	SetVariable(pset.vars, "LAST_ERROR_SQLSTATE", "00000");
+	SetVariable(pset.vars, "SHELL_EXIT", "0");
 
 	/* Default values for variables (that don't match the result of \unset) */
 	SetVariableBool(pset.vars, "AUTOCOMMIT");
-- 
2.43.0

---

From: Kirk Roybal <[email-masked]>
Date: Thu, 09 Jul 2026 07:54:24 +0000

This works the same way that bash works.    Code largely plagiarized and customized from there.