February 21, 2019

1524 words 8 mins read

okbob/pspg

okbob/pspg

Unix pager designed for work with tables. Designed for PostgreSQL, but MySQL is supported too. Now it can be used as CSV or TSV viewer.

repo name okbob/pspg
repo link https://github.com/okbob/pspg
homepage
language C
size (curr.) 2091 kB
stars (curr.) 1219
created 2017-07-11
license BSD 2-Clause “Simplified” License

Build Status

pspg - Postgres Pager

Everybody who uses psql uses less pager. It is working well, but there is not any special support for tabular data. I found few projects, but no one was completed for this purpose. I decided to write some small specialized pager for usage as psql pager.

This pager can be used from mysql and pgcli clients too.

Main target

Screenshots

Screenshot Screenshot Screenshot Screenshot

Screenshot

Options

  • -a menu will use ascii borders
  • -b black/white theme
  • -X doesn’t clean screen on the end
  • -s N use theme (default theme is mc theme)
  • -c N freeze first N columns
  • -f file open file (default stdin)
  • --force-uniborder replace ascii border by unicode borders
  • -g --hilite-search don’t highlight lines for searches
  • -G --HILITE-SEARCH don’t highlight lines for searches ever
  • --help show this help
  • -i --ignore-case ignore case in searches that do not contain uppercase
  • -I --IGNORE-CASE ignore case in all searches
  • --less-status-bar status bar like less pager
  • --line-numbers show line number column
  • --no-mouse without own mouse handling (cannot be changed in app)
  • --no-sound without sound effect
  • -F, --quit-if-one-screen quit if content is one screen
  • -V, --version show version
  • --about show info about authors
  • --csv input format is csv
  • --tsv input format is tsv
  • --double-header header line is doubled
  • --border border used for formatted csv
  • --csv-separator special char used as separator inside csv documents
  • --null striing null string (default “")
  • --ni not interactive mode (format csv to table and quit)
  • --no-cursor the line cursor will be hidden
  • --no-commandbar the bottom bar will be hidden
  • --no-topbar the top bar will be hidden
  • --no-bars both bars will be hidden
  • --no-sigint-search-reset sigint is not used to reset searching
  • --tabular-cursor cursor is displayed only for table
  • --only-for-tables use std pager when content is not a table
  • --bold-labels bold font for row, column labels
  • --bold-cursor bold font for cursor
  • --vertical-cursor show column cursor
  • --on-sigint-exit double escape or ctrl c ending pager
  • -q, --query execute query
  • -w, --watch n repeat query execution every time sec
  • -d, --dbname database name
  • -h, --host database host name
  • -p, --port databae port
  • -U, --username database user name
  • -W, --password force password prompt

Themes

  1. black & white
  2. Midnight Commander like
  3. FoxPro like
  4. Pdmenu like
  5. White theme
  6. Mutt like
  7. PCFand like
  8. Green theme
  9. Blue theme
  10. Word Perfect like
  11. Low contrast blue theme
  12. Dark cyan/black mode
  13. Paradox like
  14. dBase IV retro style
  15. dBase IV retro style (Magenta labels)
  16. Red white theme
  17. Simple theme
  18. Solarized dark theme
  19. Solarized light theme
  20. Gruvbox light theme
  21. Tao Light theme

see http://okbob.blogspot.cz/2017/07/i-hope-so-every-who-uses-psql-uses-less.html

Keyboard commands

  • 0, 1, 2, 3, .., 9 - freeze first N columns
  • KEY_UP, k - navigate backward by one line
  • KEY_DOWN, j - navigate forward by one line
  • KEY_LEFT, h - scroll to left
  • KEY_RIGHT, l - scroll to right
  • Ctrl+Home, g - go to the start of file
  • Ctrl+End, G - go to the end of file
  • Alt+l - go to line number
  • H - go to first line of current window
  • M - go to half of current window
  • L - go to end of current window
  • PPAGE, Ctrl+b - backward one window
  • NPAGE, Ctrl+f, space - forward one window
  • HOME, ^ - go to begin of line, first column
  • END, $ - go to end of line, last column
  • Ctrl+e - scroll a window down
  • Ctrl+y - scroll a window up
  • Ctrl+d - forward a half window
  • Ctrl+u - backward a half window
  • s - save content to file
  • / - search for a pattern which will take you to the next occurrence
  • ? - search for a pattern which will take you to the previous occurrence
  • n - for next match
  • N - for next match in reverse direction
  • c - column search
  • Alt+c - switch (on, off) drawing line cursor
  • Alt+m - switch (on, off) own mouse handler
  • Alt+n - switch (on, off) drawing line numbers
  • Alt+v, double click on column header - switch (on, off) drawing column cursor
  • Mouse button wheel - scroll vertical
  • Alt+Mouse button wheel - scroll horizontal
  • F9 - show menu
  • q, F10, Esc 0 - quit
  • Alt+q - quit wit raw (unformatted) output
  • Alt+k, Alt+double click - switch bookmark
  • Alt+j - go to next bookmark
  • Alt+i - go to previous bookmark
  • Alt+o - flush bookmarks
  • a - sort ascendent
  • d - sort descendent
  • u - unsorted (sorted in origin order)
  • space - stop/continue in watch mode

Ending

The pager can be ended by pressing keys q or F10 or Esc 0. With option --on-sigint-exit then the pager is closed by pressing keys Ctrl+c or Esc Esc.

Column search is case insensitive every time. Searched column is marked by vertical cursor. Last non empty string searching pattern is used when current searching pattern is empty string. Searching is starting after visible vertical column or on first visible not freezed columns (after some horizontal scrolling) or on first column. After last column searching starts from first again.

Status line description

  • V: [d/d d..d] - vertical cursor: (column number)/(columns) (char possitions from) .. (char possitions to)
  • FC: d - freezed columns length in chars
  • C: d..d/d - unfreezed visible data in chars (from .. to)/(total)
  • L:[d + d d/d] - lines (number of first visible line) + (number of line of display), (current line)/(lines)
  • d% - percent of already displayed data

Usage as csv viewer

It works well with miller http://johnkerl.org/miller/doc/index.html

New version has integrated csv support - just use --csv option.

It can be integrated into mc

  • copy file from /etc/mc/mc.ext to your ~/.config/mc directory
  • insert there
  • restart mc

Usage in watch mode

The result of query can be refreshed every n seconds. pspg remembers cursor row, possible vertical cursor, possible ordering. The refreshing should be paused by pressing space key. Repeated pressing of this key enables refreshing again.

Recommended psql configuration

some possible configuration:

Attention

When you use a option --only-for-tables, then

  • set PAGER to pspg and PSPG_PAGER to less or
  • set PAGER to less and PSQL_PAGER to pspg

MySQL usage

LC_CTYPE should be correct.

SQLite

SQLite native client doesn’t produce well formatted output, but can be forced to generate CSV format - and this format is well readable for pspg

sqlite3 -csv -header testdb.db 'select * from foo2' | pspg --csv --csv-header=on --double-header

Note - compilation issue

Some linker issues can be fixed by:

On some old systems a compilation fails with error

In this case comment line with function set_escdelay

Note - Installation

When you compile code from source, run ./configure first. Sometimes ./autogen.sh first

If you would to display UTF-8 characters, then pspg should be linked with ncursesw library. UTF-8 characters are displayed badly when library ncursesw is used. You can see broken characters with incorrect locale setting too.

You can check wide chars support by pspg --version. Row ncurses with wide char support is expected. Re-run configure with --with-ncursesw option. When this command fails check if development package for ncuresesw library is installed.

Homebrew (for Linux & MacOS)

# brew install pspg

Debian

# apt-cache search pspg
# apt-get install pspg

Fedora (28 and later)

# dnf install pspg

RPM (CentOS/openSUSE/…)

The pspg is available from community repository https://yum.postgresql.org/packages.php

Alpine Linux

# apk add pspg

Gentoo

# emerge -av dev-db/pspg

Arch Linux

The Arch User Repository contains two versions:

  • pspg is a fixed release.
  • pspg-git tracks the master branch.

Use the AUR helper of your choice or git and makepkg to install pspg.

FreeBSD

# pkg install pspg

Using MacPorts (MacOS only)

# port install pspg

Solaris

There are few issues requires manual code changes for successful compilation - we successfully tested pspg, but although pspg was linked with ncursesw libraries, the utf8 encoding support didn’t work fully correctly - probably due some issues in libc library. There are problems with chars encoded to 3bytes - unicode borders, .. Two bytes unicode chars should be displayed well.

You can use pspg with usual accented chars, but unicode bordes should not be used. Replacement ascii borders by special borders chars (by ncurses technology) works well - looks on Options|Force unicode borders option.

  • Solaris make doesn’t support conditional statements - should be removed So, remove unsupported functionality from Makefile (ifdef,endif), replace -include by include first.

  • After running configure remove link on termcap library from config.make. It is garabage produced by readline automake script. Combination with ncurses libraries makes some linking issues.

builtin libraries

export CURSES_CFLAGS="-I/usr/include/ncurses/"
export PANEL_LIBS="-lpanelw"
./configure

OpenCSW development

export CFLAGS="-m64 -I/opt/csw/include"
export LDFLAGS="-L/opt/csw/lib/64 -R/opt/csw/lib/64"
export PKG_CONFIG_PATH="/opt/csw/lib/64/pkgconfig"
./configure

Possible ToDo

  • Store data in some column format (now data are stored like array of rows). With this change can be possible to operate over columns - hide columns, change width, cyclic iteration over columns, change order of columns, mark columns and export only selected columns (selected rows).

st_menu

This project uses st_menu library - implementation of CUA menubar and pulldown menu for ncurses https://github.com/okbob/ncurses-st-menu

Note

If you like it, send a postcard from your home country to my address, please:

Pavel Stehule
Skalice 12
256 01 Benesov u Prahy
Czech Republic

I invite any questions, comments, bug reports, patches on mail address pavel.stehule@gmail.com

comments powered by Disqus