Feature: psql - display current search_path in prompt

On Sat, 7 Jun 2025 at 20:52, Lauri Siltanen <lauri.siltanen@gmail.com> wrote:

I need to switch search_paths often. It would be tremendously helpful to see the current search_path in the prompt.

That feature should be pretty easy to implement, now that search_path
is marked as GUC_REPORT in PG18. Basically you need to use
PQparameterStatus like we do for session_authorization[1]https://github.com/postgres/postgres/blob/73e26cbeb5927053eea4e209e5eda34a30c353f1/src/bin/psql/prompt.c#L166-L169[2]https://github.com/postgres/postgres/blob/73e26cbeb5927053eea4e209e5eda34a30c353f1/src/bin/psql/common.c#L2508-L2520.

[1]: https://github.com/postgres/postgres/blob/73e26cbeb5927053eea4e209e5eda34a30c353f1/src/bin/psql/prompt.c#L166-L169
[2]: https://github.com/postgres/postgres/blob/73e26cbeb5927053eea4e209e5eda34a30c353f1/src/bin/psql/common.c#L2508-L2520

On 8 Jun 2025, at 2:36 AM, Jelte Fennema-Nio <postgres@jeltef.nl> wrote:

On Sat, 7 Jun 2025 at 20:52, Lauri Siltanen <lauri.siltanen@gmail.com> wrote:

I need to switch search_paths often. It would be tremendously helpful to see the current search_path in the prompt.

That feature should be pretty easy to implement, now that search_path
is marked as GUC_REPORT in PG18. Basically you need to use
PQparameterStatus like we do for session_authorization[1][2].

[1]: https://github.com/postgres/postgres/blob/73e26cbeb5927053eea4e209e5eda34a30c353f1/src/bin/psql/prompt.c#L166-L169
[2]: https://github.com/postgres/postgres/blob/73e26cbeb5927053eea4e209e5eda34a30c353f1/src/bin/psql/common.c#L2508-L2520

Here’s a quick attempt that makes %S substitue for a search_path
Like
\set PROMPT1 'user:%n search_path: %S'

On Mon, 9 Jun 2025 at 17:54, Florents Tselai <florents.tselai@gmail.com> wrote:

Here’s a quick attempt that makes %S substitue for a search_path
Like
\set PROMPT1 'user:%n search_path: %S'

+ else
+ return PQuser(pset.db);

That seems like a copy paste error. If we don't have data for it, we
should either use the empty string, or some string like "<unknown>".

Other than that, the patch looks good (but I haven't tested it yet).

On Tue, Jun 10, 2025 at 2:08 AM Jelte Fennema-Nio <postgres@jeltef.nl>
wrote:

On Mon, 9 Jun 2025 at 17:54, Florents Tselai <florents.tselai@gmail.com>
wrote:

Here’s a quick attempt that makes %S substitue for a search_path
Like
\set PROMPT1 'user:%n search_path: %S'

+ else
+ return PQuser(pset.db);

That seems like a copy paste error. If we don't have data for it, we
should either use the empty string, or some string like "<unknown>".

Opted for an empty string

Other than that, the patch looks good (but I haven't tested it yet).

Btw - I haven't worked on bin/psql code;
aren't these auto tested?

CF: https://commitfest.postgresql.org/patch/5808/

Hi Florents

On 10.06.25 13:36, Florents Tselai wrote:

On Tue, Jun 10, 2025 at 2:08 AM Jelte Fennema-Nio <postgres@jeltef.nl
<mailto:postgres@jeltef.nl>> wrote:

On Mon, 9 Jun 2025 at 17:54, Florents Tselai
<florents.tselai@gmail.com <mailto:florents.tselai@gmail.com>> wrote:

Here’s a quick attempt that makes %S substitue for a search_path
Like
  \set PROMPT1 'user:%n search_path: %S'

+   else
+       return PQuser(pset.db);

That seems like a copy paste error. If we don't have data for it, we
should either use the empty string, or some string like "<unknown>".

Opted for an empty string
 

Other than that, the patch looks good (but I haven't tested it yet).

I've taken a quick look at the patch, and it seems to work as expected.

== PROMPT1 ==

postgres=# \set PROMPT1 '(search_path: %S) ;; '
(search_path: "$user", public) ;; SET search_path TO s1, public;
SET
(search_path: s1, public) ;; SET search_path TO s2, public;
SET
(search_path: s2, public) ;; RESET search_path;
RESET
(search_path: "$user", public) ;;

== PROMPT2 ==

postgres=# \set PROMPT2 '(search_path: %S) ** '
postgres=# SELECT
(search_path: "$user", public) ** ^C
postgres=# SET search_path TO s1, public;
SET
postgres=# SELECT
(search_path: s1, public) ** ^C
postgres=# SET search_path TO s2, public;
SET
postgres=# SELECT
(search_path: s2, public) ** ^C
postgres=# RESET search_path;
RESET
postgres=# SELECT
(search_path: "$user", public) **

== PROMPT3 ==

postgres=# \set PROMPT3 '(search_path: %S) ## '
postgres=# COPY t1 (a) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
(search_path: "$user", public) ## 1
(search_path: "$user", public) ## 2
(search_path: "$user", public) ## \.
COPY 2
postgres=# SET search_path TO s1, public;
SET
postgres=# COPY t1 (a) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
(search_path: s1, public) ## 42
(search_path: s1, public) ## 73
(search_path: s1, public) ## \.
COPY 2
postgres=# RESET search_path;
RESET
postgres=# COPY t1 (a) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
(search_path: "$user", public) ## 0
(search_path: "$user", public) ## 1
(search_path: "$user", public) ## \.
COPY 2

Documentation looks ok as well -- it aligns with the other entries in
the file.

Btw - I haven't worked on bin/psql code; 
aren't these auto tested?  

I also couldn't find any test related to psql's PROMPT*. Perhaps Jelte
knows more about it?

Best regards,
Jim

On 10 Jun 2025, at 3:51 PM, Jim Jones <jim.jones@uni-muenster.de> wrote:

Hi Florents

On 10.06.25 13:36, Florents Tselai wrote:

On Tue, Jun 10, 2025 at 2:08 AM Jelte Fennema-Nio <postgres@jeltef.nl
<mailto:postgres@jeltef.nl>> wrote:

On Mon, 9 Jun 2025 at 17:54, Florents Tselai
<florents.tselai@gmail.com <mailto:florents.tselai@gmail.com>> wrote:

Here’s a quick attempt that makes %S substitue for a search_path
Like
\set PROMPT1 'user:%n search_path: %S'

+ else
+ return PQuser(pset.db);

That seems like a copy paste error. If we don't have data for it, we
should either use the empty string, or some string like "<unknown>".

Opted for an empty string

Other than that, the patch looks good (but I haven't tested it yet).

I've taken a quick look at the patch, and it seems to work as expected.

== PROMPT1 ==

postgres=# \set PROMPT1 '(search_path: %S) ;; '
(search_path: "$user", public) ;; SET search_path TO s1, public;
SET
(search_path: s1, public) ;; SET search_path TO s2, public;
SET
(search_path: s2, public) ;; RESET search_path;
RESET
(search_path: "$user", public) ;;

== PROMPT2 ==

postgres=# \set PROMPT2 '(search_path: %S) ** '
postgres=# SELECT
(search_path: "$user", public) ** ^C
postgres=# SET search_path TO s1, public;
SET
postgres=# SELECT
(search_path: s1, public) ** ^C
postgres=# SET search_path TO s2, public;
SET
postgres=# SELECT
(search_path: s2, public) ** ^C
postgres=# RESET search_path;
RESET
postgres=# SELECT
(search_path: "$user", public) **

== PROMPT3 ==

postgres=# \set PROMPT3 '(search_path: %S) ## '
postgres=# COPY t1 (a) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
(search_path: "$user", public) ## 1
(search_path: "$user", public) ## 2
(search_path: "$user", public) ## \.
COPY 2
postgres=# SET search_path TO s1, public;
SET
postgres=# COPY t1 (a) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
(search_path: s1, public) ## 42
(search_path: s1, public) ## 73
(search_path: s1, public) ## \.
COPY 2
postgres=# RESET search_path;
RESET
postgres=# COPY t1 (a) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
(search_path: "$user", public) ## 0
(search_path: "$user", public) ## 1
(search_path: "$user", public) ## \.
COPY 2

Documentation looks ok as well -- it aligns with the other entries in
the file.

Thanks for the review.

Btw - I haven't worked on bin/psql code;
aren't these auto tested?

I also couldn't find any test related to psql's PROMPT*. Perhaps Jelte
knows more about it?

Doesn’t look like it though;
e.g. this https://github.com/Florents-Tselai/postgres/commit/79fad725aa410d6c631d4ffe0f4120837f9b478c
didn’t break anything - on Cirrus at least

On 10 Jun 2025, at 4:09 PM, Florents Tselai <florents.tselai@gmail.com> wrote:

On 10 Jun 2025, at 3:51 PM, Jim Jones <jim.jones@uni-muenster.de> wrote:

Hi Florents

On 10.06.25 13:36, Florents Tselai wrote:

On Tue, Jun 10, 2025 at 2:08 AM Jelte Fennema-Nio <postgres@jeltef.nl
<mailto:postgres@jeltef.nl>> wrote:

On Mon, 9 Jun 2025 at 17:54, Florents Tselai
<florents.tselai@gmail.com <mailto:florents.tselai@gmail.com>> wrote:

Here’s a quick attempt that makes %S substitue for a search_path
Like
\set PROMPT1 'user:%n search_path: %S'

+ else
+ return PQuser(pset.db);

That seems like a copy paste error. If we don't have data for it, we
should either use the empty string, or some string like "<unknown>".

Opted for an empty string

Other than that, the patch looks good (but I haven't tested it yet).

I've taken a quick look at the patch, and it seems to work as expected.

== PROMPT1 ==

postgres=# \set PROMPT1 '(search_path: %S) ;; '
(search_path: "$user", public) ;; SET search_path TO s1, public;
SET
(search_path: s1, public) ;; SET search_path TO s2, public;
SET
(search_path: s2, public) ;; RESET search_path;
RESET
(search_path: "$user", public) ;;

== PROMPT2 ==

postgres=# \set PROMPT2 '(search_path: %S) ** '
postgres=# SELECT
(search_path: "$user", public) ** ^C
postgres=# SET search_path TO s1, public;
SET
postgres=# SELECT
(search_path: s1, public) ** ^C
postgres=# SET search_path TO s2, public;
SET
postgres=# SELECT
(search_path: s2, public) ** ^C
postgres=# RESET search_path;
RESET
postgres=# SELECT
(search_path: "$user", public) **

== PROMPT3 ==

postgres=# \set PROMPT3 '(search_path: %S) ## '
postgres=# COPY t1 (a) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
(search_path: "$user", public) ## 1
(search_path: "$user", public) ## 2
(search_path: "$user", public) ## \.
COPY 2
postgres=# SET search_path TO s1, public;
SET
postgres=# COPY t1 (a) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
(search_path: s1, public) ## 42
(search_path: s1, public) ## 73
(search_path: s1, public) ## \.
COPY 2
postgres=# RESET search_path;
RESET
postgres=# COPY t1 (a) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
(search_path: "$user", public) ## 0
(search_path: "$user", public) ## 1
(search_path: "$user", public) ## \.
COPY 2

Documentation looks ok as well -- it aligns with the other entries in
the file.

Thanks for the review.

Btw - I haven't worked on bin/psql code;
aren't these auto tested?

I also couldn't find any test related to psql's PROMPT*. Perhaps Jelte
knows more about it?

Doesn’t look like it though;
e.g. this https://github.com/Florents-Tselai/postgres/commit/79fad725aa410d6c631d4ffe0f4120837f9b478c
didn’t break anything - on Cirrus at least

EDIT: There are test under `src/psql/t` , not sure though how much coverage they have,
but most importantly how it’d look like for this case.

On 10.06.25 15:37, Florents Tselai wrote:

EDIT: There are test under `src/psql/t` , not sure though how much
coverage they have,
but most importantly how it’d look like for this case. 

I took a look at these files, but I'm still unsure how to use them for
automated prompt checking - I'm not super familiar with the perl tests,
to be honest.

--
Jim

On Wed, Jun 11, 2025 at 12:51 PM Jim Jones <jim.jones@uni-muenster.de>
wrote:

On 10.06.25 15:37, Florents Tselai wrote:

EDIT: There are test under `src/psql/t` , not sure though how much
coverage they have,
but most importantly how it’d look like for this case.

I took a look at these files, but I'm still unsure how to use them for
automated prompt checking - I'm not super familiar with the perl tests,
to be honest.

From Tom at the discord channel

* Yeah, you can see from the code coverage report [1] that
session_username() isn't reached in our tests. It's only used if the psql
prompt string is set to use it, and testing that in an interesting way is
kind of hard --- our standard regression-script framework doesn't expose
prompt output. On balance I'm not sure that covering session_username()
would be worth the test cycles. [1]
https://coverage.postgresql.org/src/bin/psql/common.c.gcov.html
<https://coverage.postgresql.org/src/bin/psql/common.c.gcov.html&gt;*

So, yes I don't think we can auto-test it really, thus we'll have to rely
on these simple functional tests.

On Wed, Jun 11, 2025 at 1:01 PM Florents Tselai <florents.tselai@gmail.com>
wrote:

On Wed, Jun 11, 2025 at 12:51 PM Jim Jones <jim.jones@uni-muenster.de>
wrote:

On 10.06.25 15:37, Florents Tselai wrote:

EDIT: There are test under `src/psql/t` , not sure though how much
coverage they have,
but most importantly how it’d look like for this case.

I took a look at these files, but I'm still unsure how to use them for
automated prompt checking - I'm not super familiar with the perl tests,
to be honest.

From Tom at the discord channel

* Yeah, you can see from the code coverage report [1] that
session_username() isn't reached in our tests. It's only used if the psql
prompt string is set to use it, and testing that in an interesting way is
kind of hard --- our standard regression-script framework doesn't expose
prompt output. On balance I'm not sure that covering session_username()
would be worth the test cycles. [1]
https://coverage.postgresql.org/src/bin/psql/common.c.gcov.html
<https://coverage.postgresql.org/src/bin/psql/common.c.gcov.html&gt;*

So, yes I don't think we can auto-test it really, thus we'll have to rely
on these simple functional tests.

Absent any other feedback I'm marking this as Ready for Committer;
Said committer can push back on my arbitrary %S selection
https://commitfest.postgresql.org/patch/5808/

On Thu, Jun 12, 2025 at 06:57:37PM +0300, Florents Tselai wrote:

Absent any other feedback I'm marking this as Ready for Committer;
Said committer can push back on my arbitrary %S selection
https://commitfest.postgresql.org/patch/5808/

PQparameterStatus() will return NULL if the parameter is not known. So,
with this patch, using %S in a prompt when connected to a <v18 version of
PostgreSQL will produce an empty string. Given an empty string is a valid
value for search_path, I think we probably want to use a special value in
this case. Perhaps we could use "?", which is much less likely to be a
value for search_path.

--
nathan

On Thu, Oct 23, 2025 at 11:52 PM Nathan Bossart <nathandbossart@gmail.com>
wrote:

On Thu, Jun 12, 2025 at 06:57:37PM +0300, Florents Tselai wrote:

Absent any other feedback I'm marking this as Ready for Committer;
Said committer can push back on my arbitrary %S selection
https://commitfest.postgresql.org/patch/5808/

PQparameterStatus() will return NULL if the parameter is not known. So,
with this patch, using %S in a prompt when connected to a <v18 version of
PostgreSQL will produce an empty string. Given an empty string is a valid
value for search_path, I think we probably want to use a special value in
this case. Perhaps we could use "?", which is much less likely to be a
value for search_path.

Good catch. v2 attached

On Fri, Oct 24, 2025 at 05:10:13PM +0300, Florents Tselai wrote:

Good catch. v2 attached

Thanks.

+     <varlistentry id="app-psql-prompting-S">
+      <term><literal>%S</literal></term>
+      <listitem><para>The current search path.</para></listitem>
+     </varlistentry>

I'd suggest linking to a page about the search_path [0]https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH.

+extern const char *session_search_path(void);

This function appears to be unused.

+					/*
+					 * Distinguish unknown (NULL) from an empty but valid search_path ("").
+					 * If not connected or older server doesn't report it via ParameterStatus,
+					 * show "?".
+					 */
+					if (!pset.db || PQparameterStatus(pset.db, "search_path") == NULL)
+						strlcpy(buf, "?", sizeof(buf));
+					else
+						strlcpy(buf, PQparameterStatus(pset.db, "search_path"), sizeof(buf));
+					break;

Shouldn't we move most of this logic to session_search_path() and use it
here?

[0]: https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH

--
nathan

On Fri, Oct 24, 2025 at 5:20 PM Nathan Bossart <nathandbossart@gmail.com>
wrote:

On Fri, Oct 24, 2025 at 05:10:13PM +0300, Florents Tselai wrote:

Good catch. v2 attached

Thanks.

+     <varlistentry id="app-psql-prompting-S">
+      <term><literal>%S</literal></term>
+      <listitem><para>The current search path.</para></listitem>
+     </varlistentry>

I'd suggest linking to a page about the search_path [0].

Done

+extern const char *session_search_path(void);

This function appears to be unused.

+                                       /*
+                                        * Distinguish unknown (NULL) from
an empty but valid search_path ("").
+                                        * If not connected or older
server doesn't report it via ParameterStatus,
+                                        * show "?".
+                                        */
+                                       if (!pset.db ||
PQparameterStatus(pset.db, "search_path") == NULL)
+                                               strlcpy(buf, "?",
sizeof(buf));
+                                       else
+                                               strlcpy(buf,
PQparameterStatus(pset.db, "search_path"), sizeof(buf));
+                                       break;

Shouldn't we move most of this logic to session_search_path() and use it
here?

Yes

On Oct 25, 2025, at 12:38, Florents Tselai <florents.tselai@gmail.com> wrote:

<v3-0001-psql-Add-S-prompt-escape-to-display-current-searc.patch>

Overall LGTM. Just one comment:

```
+					/* current search_path, or "?" if not reported by the server */
+				case 'S':
+					strlcpy(buf, session_search_path(), sizeof(buf));
+					break;
```

I guess we also need to update the function comment to add a description for “%S”.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

Here is what I have staged for commit. I ended up simplifying the patch a
bit. In particular, I thought better of the question mark business. It
looks like we ordinarily just skip values that can't be found, and an empty
search_path will appear as "" (two double-quotes), so you can still
distinguish empty versus not-available.

--
nathan

On Oct 28, 2025, at 04:29, Nathan Bossart <nathandbossart@gmail.com> wrote:

Here is what I have staged for commit. I ended up simplifying the patch a
bit. In particular, I thought better of the question mark business. It
looks like we ordinarily just skip values that can't be found, and an empty
search_path will appear as "" (two double-quotes), so you can still
distinguish empty versus not-available.

+1, I like this idea.

V4 looks good to me.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

On Tue, Oct 28, 2025 at 09:08:36AM +0800, Chao Li wrote:

On Oct 28, 2025, at 04:29, Nathan Bossart <nathandbossart@gmail.com> wrote:
Here is what I have staged for commit. I ended up simplifying the patch a
bit. In particular, I thought better of the question mark business. It
looks like we ordinarily just skip values that can't be found, and an empty
search_path will appear as "" (two double-quotes), so you can still
distinguish empty versus not-available.

+1, I like this idea.

Actually, I take it back. The following command to empty the search_path
will cause %S to be replaced with nothing, in which case you can't
distinguish empty versus not-reported.

SELECT pg_catalog.set_config('search_path', '', false);

So, I've added the question mark back.

--
nathan

Committed.

--
nathan