Re: psql color hostname prompt

On Wed, Apr 27, 2016 at 1:29 AM, Francisco Olarte <folarte(at)peoplecall(dot)com>
wrote:

> Hi Cal:
>
> On Tue, Apr 26, 2016 at 5:20 PM, Cal Heldenbrand <cal(at)fbsdata(dot)com> wrote:
> ...
> > 2) %M vs shell call
> > %M on when connected to the local machine displays the string "[local]"
> > which I didn't like. I wanted a real hostname to show no matter which
> > client/server pair I was using. Zero chance for mistaken commands on the
> > wrong host. Many times we ssh to a remote server, then run psql locally.
>
>
> That can be done with a named pipe ;-> ( or with an alias / function
> using getopt to parse the options before forwarding them to psql ).
> But, which just \sets $hostname in a var and uses it. ) Anyway, the
> problem with this is that if you do \connect to another. You could do
> something similar to this using only psql/psqlrc tricks:
>
> cdrs=> \set fecha `date`
> cdrs=> \echo :fecha
> Wed Apr 27 10:23:22 CEST 2016
>
> Here you would use your script instead of fecha, and interpolate it
> using %:fecha: in the prompt.
>
> And now the second step of the trick:
> cdrs=> \set recalc '\\set fecha `date`'
> cdrs=> \echo :recalc
> \set fecha `date`
> cdrs=> :recalc
> cdrs=> \echo :fecha
> Wed Apr 27 10:24:07 CEST 2016
> cdrs=> :recalc
> cdrs=> \echo :fecha
> Wed Apr 27 10:24:16 CEST 2016
>
> Now you can use :recalc if you do connect to have the prompt updated.
>
> Anyway, TIMTOWTDI.
>
> > But again, I think the more elegant approach is to alter the %M logic.
> > Any thoughts?
>
> At risk of being redundant, not altering %M, another %x better.
>
>
The various hacks appear to not deal with the fact that there may be
multiple instances of postgresql running on different TCP ports or Unix
connections nor with the fact that the local connection may, in fact, be a
pooler and not a direct connection to the database.

As long as we're into hack-land, I'll offer one.

First, you abuse the custom variables feature in postgresql.conf and add
something like:
serverinfo.name = 'steve_test'

Now you can read that info from any client:
select current_setting('serverinfo.name');
current_setting
-----------------
steve_test

Next you update .psqlrc with something along the lines of:

select current_setting('serverid.name') as server_name;
\gset
\set PROMPT1 'You are connected to ' :server_name '/%/%R%# '

Then when you start psql you will see:

server_name
-------------
steve_test
(1 row)

psql (9.4.7)
Type "help" for help.

You are connected to steve_test/steve=>

On the plus side, the custom GUC setting is available to any client, not
just psql. It also handles multiple PostgreSQL instances and connections
that are actually routed through a pooler.

On the down side, it is a hack. The method is not in any way guaranteed to
be future-proof. It leaves an ugly bit of server output at psql startup. It
requires ensuring that all servers have the variable set appropriately. You
need to avoid colliding with a custom GUC used by an extension. But perhaps
it is useful.

-Steve