On 2nd of September, Andres Freund committed patch:

Add psql PROMPT variable showing which line of a statement is being edited.
The new %l substitution shows the line number inside a (potentially
multi-line) statement starting from one.
Author: Sawada Masahiko, heavily editorialized by me.
Reviewed-By: Jeevan Chalke, Alvaro Herrera

This should help some people which get surprised when they type the query, press enter, and it's not executed due to missing ‘, " or ;.

With this patch in place, we can add %l in PROMPT variables in psql.

How does it look?

Without it, if you added new line in query, your psql could have looked like this:

depesz=# select
depesz-# *
depesz-# from
depesz-# pg_class
depesz-# limit 3
depesz-# ;

The difference between “=" and “-" was rather minuscule. Of course you could change your PROMPT* variables to better suit your needs, but now we can:

depesz=# \echo :PROMPT1
depesz=# \echo :PROMPT2
depesz=# \set PROMPT1 %/%R% (%l)#
depesz=(1)#\set PROMPT2 %/%R% (%l)#

With these in place, previous query looks now:

depesz-(5)#limit 3

Should be much simpler to spot when psql waits for next line, and when it ran something, just didn't output anything. Cool, thanks 🙂

  1. One comment

  2. Sep 8, 2014

    I’ve got green PROMPT1 and yellow PROMPT2, no way not to notice unterminated command. This feature might be usable for copy-pasting some huge queries from logs or generated from some apps (for people not familiar with psql -f, using line editor for such things should be forbidden).

Leave a comment