Re: Customize psql prompt to show current_role

On 2024-09-23 14:22 +0200, Asad Ali wrote:
> 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’s 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(at)%/ (%`current_role`)=%# '
>
> 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 is wrong. First of all, \set does not run any SQL commands.
Secondly, %`current_role` will be sent to the shell and execute command
current_role.

You could instead use this:

SELECT current_role \gset
\set PROMPT1 '%n(at)%/ (%:current_role:)=%# '

But that won't work with subsequent SET ROLE commands.

> This approach requires you to execute it manually or include it in your
> .psqlrc file for automatic loading with each session.
>
> On Mon, Sep 23, 2024 at 2:31 PM Dominique Devienne <ddevienne(at)gmail(dot)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 = `select current_role` does not work).
> >
> > Surely there's a way, no? Thanks, --DD

--
Erik