psql slash command '\G' - Mailing list pgsql-patches

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/