psql color hostname prompt

Hi everyone,

The default psql prompt can be a little frustrating when managing many
hosts. Typing the wrong command on the wrong host can ruin your day. ;-)

I whipped up a psqlrc and companion shell script to provide a colored
prompt with the hostname of the machine you're connected to. It works for
both local sockets and remote connections too.

The only outside tool it requires is lsof to determine the hostname of the
remote socket. Otherwise it uses plain stuff like awk / sec and bash tools.

If everyone gives this a thumbs up, I'd like to submit this for inclusion
in the official postgres source. (Maybe as an additional
psqlrc.sample.color_hostname file or similar)

Inline paste of the two files below. Replace the paths with your
environment:

/usr/local/pgsql/etc/psqlrc
==============================================
-- PROMPT1 is the primary prompt
\set PROMPT1
'%[%033[1;31m%]%`/usr/local/pgsql/etc/psql_hostname.sh`%[%033[0m%] %n(at)%/%R%#%x
'

-- PROMPT2 is the secondary (query continue) prompt
\set PROMPT2
'%[%033[1;31m%]%`/usr/local/pgsql/etc/psql_hostname.sh`[%033[0m%] %n(at)%/%R
%# '
==============================================

/usr/local/pgsql/etc/psql_hostname.sh
==============================================
#!/bin/bash
# Intelligently return local hostname, or remote server connection

# - list file descriptors of my parent PID (psql command)
# - include only FD #3, which is the postgres socket
# - print the NAME column
name=$(/usr/sbin/lsof -p $PPID -a -d 3 | tail -1 | awk '{print $9}')

if [[ "$name" == "socket" ]]; then
# We're on the local socket
hostname -f
else
# Cut out the destination machine from the socket pair
echo $( sed 's/.*->\(.*\):postgres/\1/' <<< $name )
fi
==============================================

Thank you!

---------------------------------------------------------------
Cal Heldenbrand
Web Operations at FBS
Creators of flexmls <http://flexmls.com>® and Spark Platform
<http://sparkplatform.com>
cal(at)fbsdata(dot)com