Thread: 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/
Attachment
Ian Barwick writes: > 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. Sorry, this strikes me as a very random solution to a very particular problem. Tomorrow someone comes with a patch to do an implicit \a during \g -- what letter does he take? You say \x\g\x is too much typing? Propose a macro processor. You don't like the messages that are printed? Propose a switch to turn them off. Or you could make a "conditional \x" that automatically switches to \x format if there are more than a certain number of columns in the result set. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut writes:
> Ian Barwick writes:
> > 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.
>
> Sorry, this strikes me as a very random solution to a very particular
> problem. Tomorrow someone comes with a patch to do an implicit \a during
> \g -- what letter does he take?
Point taken - the solution is for a one-off case which does not provide
a useful framework for similar extensions. Proposal and patch withdrawn
for further consideration. See following.
> You say \x\g\x is too much typing?
Yup.
> Propose a macro processor.
Why? What do you envisage? What advantages would this, and presumably the fair
amount of additional and / or re-coding associated, have for me (or anyone
else) compared to a more specific solution as suggested below?
> You don't like the messages that are printed?
I am perfectly happy with the messages printed...
> Propose a switch to turn them off.
... except in the situation described, where they are superfluous. Manually
toggling messages as well as expanded output does not help me.
(psql already has a command line switch -q or --quiet; I presume it would not
be difficult to add a counterpart slash command. It is not a priority for
me but I would be happy to add it if it is generally seen to be useful.)
> Or you could make a "conditional \x" that automatically switches to \x
> format if there are more than a certain number of columns in the result
> set.
No. This is letting the application try and second guess my intentions, which
is dancing paperclip territory.
How about optional extensions to \g along the lines of
\g{(a|H)|t|x|...}
where the specified output modes are implictly and silently (or possibly
non-silently if the command line flag -q / --quiet is set...) toggled before
and after query execution? For example, \gx would perform in the same
way as the initially suggested \G; \ga would do similar for aligned / none
aligned output.
This doesn't collide with any existing functionality (there is nothing
stopping anyone typing say \x\H\g\H\x to get temporary expanded mode HTML
output and four extra lines of superfluous messages), can be made available
for all valid output setting combinations, and doesn't "pollute" the slash
command name space (although it doesn't help the output of \? any ;-).
Thoughts?
Ian Barwick
Ian Barwick <barwick@gmx.net> writes: > Peter Eisentraut writes: > > Propose a macro processor. > > Why? What do you envisage? What advantages would this, and presumably the fair > amount of additional and / or re-coding associated, have for me (or anyone > else) compared to a more specific solution as suggested below? If you've compiled psql with readline you could just use readline's macro system. With e.g. $if Psql "\M-g": "\\x\\g\\x" $endif in your .inputrc, Meta-g would expand to \x\g\x Bernhard -- Intevation GmbH http://intevation.de/ Sketch http://sketch.sourceforge.net/ MapIt! http://www.mapit.de/
On Tuesday 26 March 2002 11:27, Bernhard Herzog wrote: > If you've compiled psql with readline you could just use readline's > macro system. With e.g. > > $if Psql > "\M-g": "\\x\\g\\x" > $endif > > in your .inputrc, Meta-g would expand to \x\g\x aha, interesting idea and useful to know. Thanks, Ian Barwick
On Sun, Mar 24, 2002 at 09:27:24PM -0500, Peter Eisentraut wrote: > Ian Barwick writes: > > > 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. > > Sorry, this strikes me as a very random solution to a very particular > problem. Tomorrow someone comes with a patch to do an implicit \a during > \g -- what letter does he take? I rather like this, actually. mysql has exactly this command, and I use it quite a bit. -- Christopher Masto CB461C61 8AFC E3A8 7CE5 9023 B35D C26A D849 1F6E CB46 1C61