psql -- now with a splash of color!

I was tired of looking at psql's dull grey output and decided to see if I could work in some color.   I was able to do this -- it's pretty hacky but seems to work well enough for me to publish here.  first, if you haven't already, please read the instructions here for a good primer for how to drastically improve psql data browsing through the less utility (this will work on windows, but only through cygwin based terminals like the excellent rxvt).

My .bashrc is now including this:

export YELLOW=`echo -e '\033[1;33m'`
export LIGHT_CYAN=`echo -e '\033[1;36m'`
export NOCOLOR=`echo -e '\033[0m'`

export LESS="-iMSx4 -FXR"

PAGER="sed \"s/\([[:space:]]\+[0-9.\-]\+\)$/${LIGHT_CYAN}\1$NOCOLOR/;" 
PAGER+="s/\([[:space:]]\+[0-9.\-]\+[[:space:]]\)/${LIGHT_CYAN}\1$NOCOLOR/g;" 
PAGER+="s/|/$YELLOW|$NOCOLOR/g;s/^\([-+]\+\)/$YELLOW\1$NOCOLOR/\" 2>/dev/null  | less"
export PAGER


My .psqlrc typically looks like this (you might want to get in the habit for disabling it with psql -X for output sensitive scripts):

\pset pager always
\timing

you should see output like this:













pretty cool, huh?  it works for table properties too:

relying on sed of course is pretty sketchy -- if just the right combination of characters come up you'll see garbled output -- but it seems to work pretty well.  of course, disabling the pager is always an option.  Ideally, we'd have tighter control over output formatting for rich color displays (like, having stderr ERROR messages in red for example).

edit: blogspot was garbling the string...breaking it up fixed it
edit2: see comments for method of non-global adjustment of LESS (in case other uses of LESS are impacted by the settings)