Monday, October 8, 2018

Pager for data

Any Unix like systems has great feature - a pager. The history of pagers is pretty old. Originally was used manually. The result of some programs can be redirected to any program with pipe operator.

ls -la | more

One, most simple pager is more. It can scroll only in one direction. Usually this is default pager in Unix systems.

Much more powerful pager is less. Has lot of advantages and functionality - mainly possibility to scroll in two direction, strong navigation inside result. It can display long lines, and allow horizontal scrolling. This pager is necessary for comfortable usage of psql console (note: it is works with mysql, mariadb or vertica console).

export PAGER="less -S"
psql postgres
postgres=>select * from pg_class;

It is good to know some less commands:
  • g - move to document begin,
  • G - move to document end,
  • / - search string,
  • n - next occurrence of string,
  • N - previous occurrence of string,
  • q - quit.
less is very rich pager has special support for displaying man pages. But this feature we cannot to use for database data. For tabular data, there are special pager pspg (mysql, pgcli are supported too). This pager can freeze n first columns, and m first rows. It knows almost all keyboard commands of less pager, and append some new (based on mcedit keywords).
  • Alt k - new bookmark
  • Alt i - previous bookmark
  • Alt j - next bookmark
  • Alt n - show row numbers
  • Alt l - go to line
  • Ctrl Home - move to document begin
  • Ctrl End - move to document end
  • F9 - menu
  • F10 - quit
The usage of pspg is same:
export PAGER=pspg
psql postgres
This pager is available from PostgreSQL community repository or from git


At October 8, 2018 at 4:39 AM , Blogger John Frazer said...

hi there,

thanks a lot for this great little pager that I've been using now for quite a while. Amazing work!

Will pspg become part of the official distro at some point? It certainly deserves to.

Minor feature request: right now zero to four columns can be frozen; wouldn't it be easy to allow up to nine frozen columns? That's just an arbitrary restriction, right?

Major feature request: wouldn't it be great if we were able to 'pin' rows? Often I page through some lengthy output and would love to just hit a key and have that row displayed permanently, then go on scrolling and maybe pin some other rows so I can have a small collection of interesting samples. I could imagine pinned rows could be be copied to the top and appear as the first n rows below the headers; that would be simple to grasp and also make the required logic a bit simpler.

At October 8, 2018 at 5:48 AM , Blogger Pavel Stěhule said...

@John Frazer

Thank you :).

I removed limit on 4 fixed columns. It is done in master.

Your second request is not possible to implement now. pspg is just pager - and it cannot to change content (order of rows). Maybe future versions will be based on own storage of preparsed data, and then this functionality can be possible. Unfortunately, not now.

At October 8, 2018 at 6:13 AM , Blogger John Frazer said...

> I removed limit on 4 fixed columns. It is done in master.


> it cannot to change content (order of rows).

I understand that much. I was thinking of a feature more like the current search highlight or the column freeze functionalities. Couldn't one display, near the top of the terminal right below the headers, a limited number of rows for reference? I don't want to touch data in the DB or make this persist in any way. I imagine going down with the cursor, I see an interesting row and hit a key, and then a temporary copy of that row would appear at the top.

Admittedly, it becomes less trivial when you think about how to remove such lines, but append-to-end (push) and remove-from-end (pop) would totally suffice for the purpose.

Now I'll download and compile the latest version...

At October 8, 2018 at 6:51 AM , Blogger Pavel Stěhule said...

I don't speak about data in database. pspg is working on psql output and knows just this output. Probably it can be enhanced about another dynamic part where some rows can be displayed out of order - but, .. it is not easy - now, the displaying, cursor moving, scrolling is "simple", because I can calculate offset against document start. If I push some content there, then I have to inject everywhere some additional fixes. It is implementable, but it doesn't trivial work.

I afraid so this feature needs much smarter internal format, and without it, the code can be very low maintainable and readable. Now, it is not too easy.

At October 10, 2018 at 5:05 AM , Anonymous Anonymous said...

It's possible to set the PAGER via \setenv also. You can put such a command in your .psqlrc. That way you don't have to use the psql setting in your general environment.

At October 10, 2018 at 5:29 AM , Blogger Pavel Stěhule said...

For psql PostgreSQL 10 you can use PSQL_PAGER environment variable.


Post a Comment

Subscribe to Post Comments [Atom]

<< Home