March 30th, 2008 by depesz | Tags: , , , , , | 5 comments »
Did it help? If yes - maybe you can help me?

there is this quite old, but pretty functional concept: keyboard shortcuts.

you know it – you press ctrl-s, and your editor saves currently-edited file.

my problem is that there are some standard cases when i would really love to have this ability in psql. but psql doesn't support it. or does it?

luckily psql uses readline. and this gives me ability to do some nice tricks 🙂

in my home dir there is (but if there wasn't, i could just create empty) file named .inputrc.

this is configuration of readline.

and what can be added there? for example this line:

"^[e":"explain analyze "

this string: ^[e should be generated in your editor by pressing ctrl-v and then alt-e.

now. let's start psql, and press alt-e.

if everything was added correctly you should see “automagically added" explain analyze command 🙂

the problem is, that at the moment alt-e works even in shell (you can try it if you want), which doesn't really break anything, but is not elegant.

luckily – there is solution.

if i will change our mapping to:

$if psql
    "^[e":"explain analyze "
$endif

my new mapping will work only in psql.

other usecases might be to issue frequent queries with just a keypress:

"^[OP":"SELECT * FROM pg_stat_activity;^M"

^OP was generated by ctrl-v and then f1 key, and ^M at the end is ctrl-v <enter>

(please note that actual ^xxx sequences on your machine might be different as they depend on terminal).

this will make psql run my select by just pressing f1.

as for other uses – try it yourself. think about what commands do you usually call. perhaps you will want to add shortcuts for “select", “update", “insert into", “delete from"?

only your imagination limits you. anything that can be entered from keyboard can be put in .inputrc thus making your work easier and more time-effective.

  1. 5 comments

  2. # kjusupov
    Dec 19, 2008

    I’ve followed your tip and tried to have this working in psql prompt, no success so far…

    I’ve been able to get that workin in shell prompt, but as soon as I run psql db_name, the shortcuts stop working… no response…

    PS: Using Mac OS X 10.5.6/ Terminal.app

  3. # kjusupov
    Dec 19, 2008

    OK, after some reading I found out these:

    * Mac OS is using “editrc” instead of “readline”, so $HOME/.editrc has to be configured

    And below what i was able to do:

    postgres@deimos:~ $ vim $HOME/.editrc
    bind -s “^[e” “explain analyze ”
    bind -s “^[s” “select * from ”

    To have the special characters “ctrl-v” and “option-e” insterted while in vim, I had to tick/enable “Terminal.app –> Preferences –> [*] Use option as meta key”

  4. # depesz
    Dec 19, 2008

    @kjusupov:
    i dont know macosx, but i heard thay have their own “very special” readline-alike library. and psql by default is liked with it.

    try to build psql/postgres with readline instead of the macosx thing, and it should be fine.

  5. # silvestrov
    Oct 28, 2010

    To get “vi” keybindings with tab-completion, use the following in ~/.editrc

    edit on
    bind -v
    bind \\t rl_complete

  1. 1 Trackback(s)

  2. Apr 11, 2008: Log Buffer #92: a Carnival of the Vanities for DBAs

Sorry, comments for this post are disabled.