vim as a pager for psql
I asked this question on stackexchange, about a pager with "freeze first line" support.
There was no easy answer, but someone hinted me at Emacs. My Emacs skill is close to zero, so I wanted to check if it's possible in Vim.
And yes it is! Here you are:
First, we need a Vim macro, which will do most of the work, I save it in ~/.vim/plugin/less.vim:
" :Less " turn vim into a pager for psql aligned results fun! Less() set nocompatible set nowrap set scrollopt=hor set scrollbind set number execute 'above split' " resize upper window to one line; two lines are not needed because vim adds separating line execute 'resize 1' " switch to lower window and scroll 2 lines down wincmd j execute 'norm! 2^E' " hide statusline in lower window set laststatus=0 " hide contents of upper statusline. editor note: do not remove trailing spaces in next line! set statusline=\ " arrows do scrolling instead of moving nmap ^[OC zL nmap ^[OB ^E nmap ^[OD zH nmap ^[OA ^Y nmap <Space> <PageDown> " faster quit (I tend to forget about the upper panel) nmap q :qa^M nmap Q :qa^M endfun command! -nargs=0 Less call Less()
Second, to emulate a pager, I need to invoke vim so that it will:
- read standard input
- but if argument is given on command line, read whatever comes there
- work in read-only mode
- skip all init scripts, but instead execute Less macro defined above
I put this together as helper script in ~/bin/vimpager:
#!/bin/bash what=- test "$@" && what="$@" exec vim -u NONE -R -S ~/.vim/plugin/less.vim -c Less $what
Third, I need to override environment variable $PAGER, but only for psql (add this to my ~/.bash_aliases):
if which vimpager &>/dev/null; then alias psql='PAGER=vimpager psql'; fi
Fourth, I disabled "\pset pager always" in my ~/.psqlrc file, because I don't need to invoke vim on small listings.
That's all - and it works for me.
PS. There is "vimpager" here, but mine is much simpler and specific to psql query results.
psql variables – quoting and more
psql, postgreSQL powerful command line interface, has it's variables system. I do use it sometimes but... how many single quotes should I put there?
I have puzzled over this so many times, that I decided to put it here as a memo.
Proper quoting of text variables
Variable declaration:
\set d1 '''2008-01-01 00:00:00.000'''
\set d2 '''2008-12-31 23:59:59.999'''
Variable usage:
SELECT something FROM somewhere
WHERE now() BETWEEN :d1 AND :d2;
To make this post a bit more useful, here are some other
examples of psql variables usage
A shortcut for frequently used SQL phrase:
\set cnt 'SELECT COUNT(*) FROM'
:cnt users;
Another one:
\set csn 'SELECT * FROM DBA.CLIENTS WHERE name LIKE'
\set csni 'SELECT * FROM DBA.CLIENTS WHERE name ILIKE'
:csni 'kowalski'\g all_kowalskis.txt
This one if for pretty-ascii-report-printout lovers:
\set hr **************************************
\set inform '\\echo :hr \\echo :msg \\echo :hr'
Usage of the above:
\set msg 'starting mass update\nat ' `date`
:inform
**************************************
starting mass update
at Fri Dec 19 21:15:49 CET 2008
**************************************
Nice, eh?
You can put such "macros" into your .psqlrc file to save some keystrokes.
You can even create your very own "library" of psql macros and include it in your scripts with \i.
Have fun!
Note: these are not variables in SQL stored procedures sense. This is just plain text substitution.
A bit similar to Host Variables (note :colon usage), but not so powerful.