MIME-Version: 1.0
References: <CAFCRh-_VwScttZi4R8grSd-b=7qguAnSzXp_+jbdpUkp=TfxPQ@mail.gmail.com>
In-Reply-To: <CAFCRh-_VwScttZi4R8grSd-b=7qguAnSzXp_+jbdpUkp=TfxPQ@mail.gmail.com>
From: Asad Ali <asadalinagri@gmail.com>
Date: Mon, 23 Sep 2024 17:22:29 +0500
Message-ID: <CAJ9xe=ttSfESR=z2YZycow8DubyfQeOgOzMDUCE95f2vN-TzFg@mail.gmail.com>
Subject: Re: Customize psql prompt to show current_role
To: Dominique Devienne <ddevienne@gmail.com>
Cc: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000000265a30622c87219"
Archived-At: <https://www.postgresql.org/message-id/CAJ9xe%3DttSfESR%3Dz2YZycow8DubyfQeOgOzMDUCE95f2vN-TzFg%40mail.gmail.com>
Precedence: bulk

--0000000000000265a30622c87219
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Dominique,

There is no direct prompt escape sequence like %n for displaying the
current_role in the psql prompt. However, you can work around this by using
a \set command to define a custom prompt that includes the result of
current_role.
You can use the following command to set your psql PROMPT1 to include both
the session_user and current_role:

Here=E2=80=99s how you can achieve this:

You can define a function in your psqlrc file that captures the current
role.

Use \set to set a custom prompt that includes both the session user (%n)
and the current_role.
Here's an example of how you can do it:

Edit your .psqlrc file to include a custom query and set a prompt:

-- Query to set the current role into a psql variable
\set current_role 'SELECT current_role;'

-- Define a custom prompt with both the session user and the current role
\set PROMPT1 '%n@%/ (%`current_role`)=3D%# '

Load the .psqlrc or start a new psql session, and your prompt will now show
the session user and current role.

The key here is that \set allows you to run SQL commands within the prompt,
which can be used to extract the current_role.

This approach requires you to execute it manually or include it in your
.psqlrc file for automatic loading with each session.

I hope this helps!

Best regards,
Asad Ali


On Mon, Sep 23, 2024 at 2:31=E2=80=AFPM Dominique Devienne <ddevienne@gmail=
.com>
wrote:

> Hi. I've successfully customized my psql PROMPT1,
> using %n for session_user, but I'd like to see
> current_role as well. And I can't seem to find a way.
>
> I didn't find a direct \x for it.
> I didn't find a %'X' variable for it.
> I didn't find a command to %`X` either.
> (and X =3D `select current_role` does not work).
>
> Surely there's a way, no? Thanks, --DD
>
>
>

--0000000000000265a30622c87219
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi Dominique,</div><div><br></div><div>There is no di=
rect prompt escape sequence like %n for displaying the current_role in the =
psql prompt. However, you can work around this by using a \set command to d=
efine a custom prompt that includes the result of current_role.<br></div><d=
iv>You can use the following command to set your psql PROMPT1 to include bo=
th the session_user and current_role:</div><div><br></div><div>Here=E2=80=
=99s how you can achieve this:<br><br>You can define a function in your psq=
lrc file that captures the current role.</div><div><br>Use \set to set a cu=
stom prompt that includes both the session user (%n) and the current_role.<=
br>Here&#39;s an example of how you can do it:<br><br>Edit your .psqlrc fil=
e to include a custom query and set a prompt:<br><br>-- Query to set the cu=
rrent role into a psql variable<br>\set current_role &#39;SELECT current_ro=
le;&#39;<br><br>-- Define a custom prompt with both the session user and th=
e current role<br>\set PROMPT1 &#39;%n@%/ (%`current_role`)=3D%# &#39;<br><=
br></div><div>Load the .psqlrc or start a new psql session, and your prompt=
 will now show the session user and current role.<br><br>The key here is th=
at \set allows you to run SQL commands within the prompt, which can be used=
 to extract the current_role.<br><br>This approach requires you to execute =
it manually or include it in your .psqlrc file for automatic loading with e=
ach session.<br></div><div><br></div><div>I hope this helps!</div><div><br>=
</div><div>Best regards,</div><div>Asad Ali</div><br></div><br><div class=
=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, Sep 23, 2024=
 at 2:31=E2=80=AFPM Dominique Devienne &lt;<a href=3D"mailto:ddevienne@gmai=
l.com">ddevienne@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gma=
il_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,2=
04,204);padding-left:1ex">Hi. I&#39;ve successfully customized my psql PROM=
PT1,<br>
using %n for session_user, but I&#39;d like to see<br>
current_role as well. And I can&#39;t seem to find a way.<br>
<br>
I didn&#39;t find a direct \x for it.<br>
I didn&#39;t find a %&#39;X&#39; variable for it.<br>
I didn&#39;t find a command to %`X` either.<br>
(and X =3D `select current_role` does not work).<br>
<br>
Surely there&#39;s a way, no? Thanks, --DD<br>
<br>
<br>
</blockquote></div>

--0000000000000265a30622c87219--