Is there any way to make less program repeat first line (or first 2 lines) on every displayed page?

Is there any other pager program which can do this?

This would be a killer-app for database table browsing, think mysql or psql or gqlplus...

See the screenshot in the bottom of this page. I want to repeat header line + horizontal ascii bar.

  • 1
    Oh boy, that asks for an extension to less, like a Freeze Pane point. For example --freeze-pane 10,2 would keep 1 line of column headers and 10 columns row header. Horizontal and vertical scrolling would preserve the row and column headers respectively. That would be really cool to use for a psql pager (merlinmoncure.blogspot.com/2007/10/better-psql-with-less.html) – Gunther Schadow Jan 17 '20 at 19:20

There is a solution using Vim.

First, you need a Vim macro, which will do most of the work. 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
command! -nargs=0 Less call Less()

Second, to emulate a pager, you 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:

test "$@" && what="$@"
exec vim -u NONE -R -S ~/.vim/plugin/less.vim -c Less $what

Make the script executable with chmod +x ~/bin/vimpager.

Third, you need to override pager program for psql. Do not set variable PAGER globally, as it can affect other programs, not only psql. Instead, add this to your ~/.psqlrc file:

\setenv PAGER ~/bin/vimpager

Voila! After reloading your profile, you can enjoy the result, which should behave as expected (arrow keys browse both vertically and horizontally) and look like this: vimpager in action. Plus, all the power of Vim is right there if you need it.

  • This is lovely, I tried it. Now it would be nice if one could add row headers also, instead of these row numbers. – Gunther Schadow Jan 17 '20 at 19:27

Have you tried SQL Mode in Emacs/XEmacs?

It's certainly not as simple to use as more or less, but it does what your asking for, leaving a header row while scrolling results vertically and horizontally.

  • Thanks, I don't know Emacs but it sound interesting. I would eventually need a shell script which would: start emacs, run psql there, (with given conn. params), enable sql-mode and do what I want (always freeze top 2 rows when there are query results larger than screen size). any hints on that? – filiprem Dec 28 '11 at 14:03

This borrows very heavily from the accepted answer, but adds...

  • Faster scrolling
  • Cannot accidentally scroll into the header
  • Syntax highlighting (some credit belongs here)
    • Positive/negative numbers, dates, times, NULL, True/False (and T/F, Y/N, Yes/No)
    • Row numbers, if you have them before a pipe char.
  • Help text
  • Support for the Vim that is included with Git for Windows
  • Do not threaten to update the view if the stdin buffer changes

Some portions may have to be tweaked for your specific output, since I do not use psql. I also have slightly different helper functions for my purposes, but they are similar to those in the accepted answer.

Sample input

  | ID |   First   |     Last     | Member | Balance |
 1|  4 | Tom       | Hanks        | False  |    0.00 |
 2| 12 | Susan     | Patterson    | True   |   10.00 |
 3| 23 | Harriet   | Langford-Wat | False  |    0.00 |
 4|  8 | Jerry     |     NULL     | True   | -382.94 |
[… More rows …]
10| 87 | Horace    | Weaver       | False  |   47.52 |


" :HeadPager
" Turn vim into a pager with a header row
" Adapted from https://unix.stackexchange.com/a/27840/143088
fun! HeadPager()
    " If you didn't get three lines, shortcut out
    if line('$') < 3
        set nocompatible
        nmap <silent> q :qa!<c-M>
        nmap <silent> Q :qa!<c-M>

    set noswapfile
    set nocompatible
    set nowrap
    set scrollopt=hor
    set scrollbind

    " Hide statusline in lower window
    set laststatus=0
    " Explain mapped chars in status line.
    set statusline=\ \ \ Q\ to\ quit\.\ Arrows\ or\ mousewheel\ to\ scroll\.\ \(Vim\ commands\ work\,\ too\.\)

    " Delete/copy header lines
    silent execute '1,2d'

    " Split screen with new buffer (opens at top)
    execute 'new'

    " Switch to upper split
    wincmd k

    " Paste the header over the blank line
    execute 'norm! Vp'

    " Header highlighting
    syn match Pipe "|"
    hi def Pipe ctermfg=blue
    syn match Any /[^|]\+/
    hi def Any ctermfg=yellow

    " Switch back to lower split for scrolling
    wincmd j

    " Set lower split height to maximum
    execute "norm! \<c-W>_"

    " Syntax highlighting
    syn cluster CellContents contains=None
    syn match Pipe "|" contained nextgroup=@CellContents skipwhite
    hi def Pipe ctermfg=blue

    " Start with newline or |. End right before next | or EOL
    syn region Cell start=/\v(^|\|)\s*/ end=/\v(\||$)\@=/ contains=LineNumber,Pipe

    syn match NumPos /\v\+?\d+(,?\d{3})*\.?\d*\ze *(\||$)\@=/ contained
    syn match NumNeg   /\v-\d+(,?\d{3})*\.?\d*\ze *(\||$)\@=/ contained
    syn match NumZero         /\v[+-]?0+\.?0*\ze *(\||$)\@=/  contained
    hi def NumPos ctermfg=cyan
    hi def NumNeg ctermfg=red
    hi def NumZero ctermfg=NONE
    syn cluster CellContents add=NumPos,NumNeg,NumZero

    syn match DateVal /\v\d{4}-\d{2}-\d{2}/ contained nextgroup=TimeVal skipwhite
    syn match TimeVal /\v\d{1,2}:\d{2}(:\d{2})?(\.\d+)?(Z| ?\c[AP]M)?\ze *(\||$)\@=/ contained
    hi def DateVal ctermfg=magenta
    hi def TimeVal ctermfg=magenta
    syn cluster CellContents add=DateVal,TimeVal

    syn match TrueVal /\v\c(t(rue)?|y(es)?)\ze *(\||$)\@=/ contained
    syn match FalseVal /\v\c(f(alse)?|no?)\ze *(\||$)\@=/ contained
    hi def TrueVal ctermfg=green
    hi def FalseVal ctermfg=red
    syn match NullVal /\v\cnull?\ze *(\||$)\@=/ contained
    hi def NullVal ctermbg=gray ctermfg=black
    syn cluster CellContents add=TrueVal,FalseVal,NullVal

    syn match LineNumber /^ *\d\+/ contained
    hi def LineNumber ctermfg=yellow

    " Arrows do scrolling instead of moving
    nmap <silent> <Up> 3<c-Y>
    nmap <silent> <Down> 3<c-E>
    nmap <silent> <Left> zH
    nmap <silent> <Right> zL
    nmap <Space> <PageDown>
    " Faster quit (I tend to forget about the upper panel)
    nmap <silent> q :qa!<c-M>
    nmap <silent> Q :qa!<c-M>

    " Ignore external updates to the buffer
    autocmd! FileChangedShell */fd/*
    autocmd! FileChangedRO */fd/*
command! -nargs=0 HeadPager call HeadPager()

You could use multiple 'regions' in screen:

$ cat screenrc.sql
escape ^aa  # adjust as needed
bind q quit # to quickly exit
screen 0 less ${FILE}
screen 1 less ${FILE}
split  # create two regions
focus top # starting with the top region
resize 4  # make it four lines (one for screen line, one for less prompt)
select 0  # display window 0
focus bottom  # in the bottom region
select 1  # display window 1 and focus here

Then you just need to set the $FILE environment variable:

$ FILE=$HOME/.bash_profile screen -mc screenrc.sql
  • 1
    this is almost what I wanted, but (a) the top window does not scroll right, so it is useless for wide tables – filiprem Dec 28 '11 at 19:53
  • Not sure what you mean by 'useless for wide tables'; screen can expand to the size of the terminal (if it doesn't run the fit screen command). I had thought that you didn't want the top to scroll. When I tested it on my own, both windows scroll as they should. The top scrolls two lines (1-2, 3-4, 5-6, etc.) and the bottom scrolls as needed. What is the behavior you are seeing/ – Arcege Dec 28 '11 at 20:05

You can add a number before the 'forward' and it will scroll N lines, not a full length. So if your terminal window is 40 lines, type 38f to start scrolling only 38 lines, leaving the last 2 line from the last 'page.' From the manpage:

   SPACE or ^V or f or ^F
          Scroll forward N  lines,  default  one  window  (see  option  -z
          below).   If  N  is  more  than  the screen size, only the final
          screenful is displayed.  Warning: some systems use ^V as a  spe‐
          cial literalization character.

   z      Like  SPACE,  but  if  N is specified, it becomes the new window

   b or ^B or ESC-v
          Scroll backward N lines,  default  one  window  (see  option  -z
          below).   If  N  is  more  than  the screen size, only the final
          screenful is displayed.
  • 1
    I need to keep first, not last N lines. As "freeze top N rows" in Google Spreadsheet. – filiprem Dec 28 '11 at 13:29
  • Ah, sorry. I don't know if a utility that would do that. Then what I suggest is: use screen or tmux to create two panes, resize of the first one to two lines (resize 2) and then run less, in the second pane, run less normally. You could set this up as a script with a specific .screenrc file. See alternate answer. – Arcege Dec 28 '11 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.