psql slash command '\G'

Please find attached:
- proposal for adding a slash command '\G' to psql
- patches for same

Description:
The proposed slash command '\G' performs the same function as
the existing command '\g' (send query buffer to server and optionally
results to a file or pipe) but silently toggles expanded output mode
immediately before and immediately after sending the query buffer to
the backend.

Background:
I do a lot of work with psql, mainly with expanded output set to 'off'.
As most queries are of the form:

test=> SELECT t1.col2, t2.col2 FROM tbl1 t1, tbl2 t2 WHERE t1.col1=t2.col1;
col2 | col2
---------+--------
This is | a test
Hello | world
(2 rows)

this is usually fine. Occasionally I have queries which produce more
columns than fit into a reasonably sized terminal window, e.g. when
examining a single record from a table with many columns, or columns
including a large amount of data (TEXT columns for example).

Toggling expanded output mode with '\x' works just fine for this kind
of query. However I find I often omit to retoggle back to none-expanded
output mode, which is annoying. (On bad days, after toggling \x to
issue a query in expanded output mode, I omit to retoggle \x, causing
the subsequent query to be in the wrong format; I then enter \x
recall the query just issued with ^P or cursor-up, forgetting the
previous command was the \x just issued, end up issuing \x a third
time, then step back four times through the buffer to recall the
query I wanted to issue in the first place...).

\x\g\x would be a work-around, but is a pain to type (particularly
on German keyboards) and also surrounds the query results with
superfluous messages telling me whether expanded output is on or off:

test=> SELECT t1.col2, t2.col2 FROM tbl1 t1, tbl2 t2 WHERE
t1.col1=t2.col1\x\g\x
Expanded display is on.
-[ RECORD 1 ]-
col2 | This is
col2 | a test
-[ RECORD 2 ]-
col2 | Hello
col2 | world

Expanded display is off.

Solution:
The \G command as proposed provides the option of issuing individual
queries explicitly in expanded mode without having to worry about with \x,
e.g.:

test=> select count(*) from tbl1;
count
-------
2
(1 row)

test=> select t1.col2, t2.col2 from tbl1 t1, tbl2 t2 where t1.col1=t2.col1\G
-[ RECORD 1 ]-
col2 | This is
col2 | a test
-[ RECORD 2 ]-
col2 | Hello
col2 | world

test=> select count(*) from tbl2;
count
-------
2
(1 row)

The \G command as implemented also works the other way round, i.e.
when expanded output is set to 'on', the current query is issued with
expanded output set to 'off'.

Internally \G works slightly differently to the existing slash commands
because it requires an action (returning expanded output to the state it
was before the query was issued) to be carried out after the query has been
passed to the backend.

To achieve this I have added an additional enumeration "CMD_SEND_X"
to 'backslashResult' in command.h. When returned by HandleSlashCmds after
processin the \G to mainloop.c, an additional check after SendQuery calls
HandleSlashCmds with \G and an empty query buffer, which causes expanded
output to be toggled silently a second time. This call should be safe, as
\g or \G mark the definitive end of a query and there is no further query
be sent to the back end. (Calling HandleSlashCmds with \x to achieve the
second toggle would produce a notice, which is not desirable.)

(If this is the wrong way of doing things, or just a nasty kludge, please
shout and I will do my best to mend my wicked ways.)

I've been using the \G patch for a while now and believe it would be a
useful addition to psql. It makes my working life a little bit easier; and
it also provides a feature available in at least one other database
system's terminal front-end.

Yours

Ian Barwick

Attached:
patches for command.c, command.h, mainloop.c and help.c in src/bin/psql/