On Wed, Apr 27, 2016 at 1:29 AM, Francisco Olarte <folarte@xxxxxxxxxxxxxx> wrote:
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%# '
Hi Cal:
On Tue, Apr 26, 2016 at 5:20 PM, Cal Heldenbrand <cal@xxxxxxxxxxx> 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.
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=>
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
