Re: psql color hostname prompt

On Wed, Apr 27, 2016 at 1:29 AM, Francisco Olarte <folarte@peoplecall.com> wrote:

Hi Cal:

On Tue, Apr 26, 2016 at 5:20 PM, Cal Heldenbrand <cal@fbsdata.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.

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