One-shot expanded output in psql using \G

Christoph,

* Christoph Berg (christoph.berg@credativ.de) wrote:

The same idea was discussed back in 2008. Back then the outcome was
that "\x auto" was implemented, but I still think that \G is a useful
feature to have on its own, and several people in the thread seem to
have agreed back then.

+1 for my part. I often run a query and then realize that I really
wanted \x output.

Thanks!

Stephen

Re: To PostgreSQL Hackers 2017-01-27 <20170127132737.6skslelaf4txs6iw@msg.credativ.de>

The same idea was discussed back in 2008. Back then the outcome was
that "\x auto" was implemented, but I still think that \G is a useful
feature to have on its own, and several people in the thread seem to
have agreed back then.

I forgot to add the archive URL here:
/messages/by-id/758d5e7f0804030023j659d72e6nd66a9d6b93b30886@mail.gmail.com

Mit freundlichen Gr��en,
Christoph Berg
--
Senior Berater, Tel.: +49 2166 9901 187
credativ GmbH, HRB M�nchengladbach 12080, USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 M�nchengladbach
Gesch�ftsf�hrung: Dr. Michael Meskes, J�rg Folz, Sascha Heuer
pgp fingerprint: 5C48 FE61 57F4 9179 5970 87C6 4C5A 6BAB 12D2 A7AE

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Jan 27, 2017 at 02:27:37PM +0100, Christoph Berg wrote:

I frequently find myself in the situation that I want the "\x"
expanded output mode activated just for one query. There's little
wrong with typing "\x" and re-executing the query in that case, but
then I'm always annoyed that the expanded output is still active for
the next query after that.

+1

Your situation is familiar to me, and likely common among heavy users
of psql.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2017-01-27 10:05 AM, David Fetter wrote:

On Fri, Jan 27, 2017 at 02:27:37PM +0100, Christoph Berg wrote:

I frequently find myself in the situation that I want the "\x"
expanded output mode activated just for one query. There's little
wrong with typing "\x" and re-executing the query in that case, but
then I'm always annoyed that the expanded output is still active for
the next query after that.

+1

Your situation is familiar to me, and likely common among heavy users
of psql.

I am a pretty heavy user of psql but I don't think that that would be so
helpful. I assume you mean a new option, let's call it "\X" the causes
the next query to be expanded. I type "\X" then a query. I realize
that I made a mistake and have to redo the query so I have to type "\X"
again. If I forget then I have to run the query yet again.

What would be useful for me is a control that causes every query to be
expanded if it returns exactly one row. Now I can do this:

SELECT * FROM table WHERE table_status = 1; % get a short list
SELECT * FROM table WHERE table_id = 123; % drill down to one ID

The first would give me a normal list and the second would give me an
expanded look at the one I want to see in detail.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 788 2246     (DoD#0082)    (eNTP)   |  what's for dinner.
IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

D'Arcy Cain wrote:

I am a pretty heavy user of psql but I don't think that that would be so
helpful. I assume you mean a new option, let's call it "\X" the causes the
next query to be expanded. I type "\X" then a query. I realize that I made
a mistake and have to redo the query so I have to type "\X" again. If I
forget then I have to run the query yet again.

I think the suggestion is that \G replaces \g (which is the same thing
as the semicolon). So you would do this:

SELECT * FROM table WHERE table_status = 1; % get a short list; normal output
SELECT * FROM table WHERE table_id = 123 \G % drill down to one ID

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Alvaro Herrera (alvherre@2ndquadrant.com) wrote:

D'Arcy Cain wrote:

I am a pretty heavy user of psql but I don't think that that would be so
helpful. I assume you mean a new option, let's call it "\X" the causes the
next query to be expanded. I type "\X" then a query. I realize that I made
a mistake and have to redo the query so I have to type "\X" again. If I
forget then I have to run the query yet again.

I think the suggestion is that \G replaces \g (which is the same thing
as the semicolon). So you would do this:

SELECT * FROM table WHERE table_status = 1; % get a short list; normal output
SELECT * FROM table WHERE table_id = 123 \G % drill down to one ID

Uh, I figured it was more like \g, which just re-runs the last query..
As in, you'd do:

table pg_proc; % blargh, I can't read it like this
\G % ahh, much nicer

Thanks!

Stephen

Stephen Frost wrote:

* Alvaro Herrera (alvherre@2ndquadrant.com) wrote:

I think the suggestion is that \G replaces \g (which is the same thing
as the semicolon). So you would do this:

SELECT * FROM table WHERE table_status = 1; % get a short list; normal output
SELECT * FROM table WHERE table_id = 123 \G % drill down to one ID

Uh, I figured it was more like \g, which just re-runs the last query..
As in, you'd do:

table pg_proc; % blargh, I can't read it like this
\G % ahh, much nicer

Sure, that's exactly the same thing. (You can omit the query in either
case which causes the previous query to be re-ran. \crosstabview,
\gexec etc also work like that).

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Jan 27, 2017 at 8:31 AM, Stephen Frost <sfrost@snowman.net> wrote:

* Alvaro Herrera (alvherre@2ndquadrant.com) wrote:

D'Arcy Cain wrote:

I am a pretty heavy user of psql but I don't think that that would be

so

helpful. I assume you mean a new option, let's call it "\X" the

causes the

next query to be expanded. I type "\X" then a query. I realize that

I made

a mistake and have to redo the query so I have to type "\X" again. If

I

forget then I have to run the query yet again.

I think the suggestion is that \G replaces \g (which is the same thing
as the semicolon). So you would do this:

SELECT * FROM table WHERE table_status = 1; % get a short list; normal

output

SELECT * FROM table WHERE table_id = 123 \G % drill down to one ID

Uh, I figured it was more like \g, which just re-runs the last query..
As in, you'd do:

table pg_proc; % blargh, I can't read it like this
\G % ahh, much nicer

​This information surprised me. It was unexpected that the last
successfully executed query remains in the query buffer until the next SQL
(and not meta) command is started. I was expecting that as soon as result
was returned to the screen the current query buffer would be cleared in
preparation for the next query.​

A sentence or two describing this behavior (or, more generally the query
buffer itself), probably placed at the end of the "Entering SQL Commands"
section, would help to make this common knowledge.

David J.

* David G. Johnston (david.g.johnston@gmail.com) wrote:

On Fri, Jan 27, 2017 at 8:31 AM, Stephen Frost <sfrost@snowman.net> wrote:

* Alvaro Herrera (alvherre@2ndquadrant.com) wrote:

D'Arcy Cain wrote:

I am a pretty heavy user of psql but I don't think that that would be

so

helpful. I assume you mean a new option, let's call it "\X" the

causes the

next query to be expanded. I type "\X" then a query. I realize that

I made

a mistake and have to redo the query so I have to type "\X" again. If

I

forget then I have to run the query yet again.

I think the suggestion is that \G replaces \g (which is the same thing
as the semicolon). So you would do this:

SELECT * FROM table WHERE table_status = 1; % get a short list; normal

output

SELECT * FROM table WHERE table_id = 123 \G % drill down to one ID

Uh, I figured it was more like \g, which just re-runs the last query..
As in, you'd do:

table pg_proc; % blargh, I can't read it like this
\G % ahh, much nicer

This information surprised me. It was unexpected that the last
successfully executed query remains in the query buffer until the next SQL
(and not meta) command is started. I was expecting that as soon as result
was returned to the screen the current query buffer would be cleared in
preparation for the next query.

Well, I did get the impression that you weren't thinking about that,
which is actually kind of surpirsing to me. Lots of things work on "the
current query buffer", which is the last query (successful or not, to be
clear..):

\crosstabview
\e
\g
\gexec
\gset
\p
\w
\watch

It's not entirely clear to me why the docs sometimes say "current query
buffer" and somtimes say "current query input buffer".

A sentence or two describing this behavior (or, more generally the query
buffer itself), probably placed at the end of the "Entering SQL Commands"
section, would help to make this common knowledge.

Generally speaking, I agree that we should be more consistent in the
docs, use one term where we mean one thing, and define that term
somewhere.

Thanks!

Stephen

* Alvaro Herrera (alvherre@2ndquadrant.com) wrote:

Stephen Frost wrote:

* Alvaro Herrera (alvherre@2ndquadrant.com) wrote:

I think the suggestion is that \G replaces \g (which is the same thing
as the semicolon). So you would do this:

SELECT * FROM table WHERE table_status = 1; % get a short list; normal output
SELECT * FROM table WHERE table_id = 123 \G % drill down to one ID

Uh, I figured it was more like \g, which just re-runs the last query..
As in, you'd do:

table pg_proc; % blargh, I can't read it like this
\G % ahh, much nicer

Sure, that's exactly the same thing. (You can omit the query in either
case which causes the previous query to be re-ran. \crosstabview,
\gexec etc also work like that).

Right, I agree it's the same thing, but (clearly), not everyone
discussing this realized that and, well, the \G-by-itself is a lot
easier for me, at least. I have a really hard time not ending things
with a semi-colon. ;)

Thanks!;

Stephen;

Re: Stephen Frost 2017-01-27 <20170127160544.GI9812@tamriel.snowman.net>

Uh, I figured it was more like \g, which just re-runs the last query..
As in, you'd do:

table pg_proc; % blargh, I can't read it like this
\G % ahh, much nicer

Sure, that's exactly the same thing. (You can omit the query in either
case which causes the previous query to be re-ran. \crosstabview,
\gexec etc also work like that).

Right, I agree it's the same thing, but (clearly), not everyone
discussing this realized that and, well, the \G-by-itself is a lot
easier for me, at least. I have a really hard time not ending things
with a semi-colon. ;)

Heh, tbh even I as the patch other didn't realize that \G-by-itself
just works, my intention was that it replaces the semicolon. :)

So, to clarify, both ways work:

select * from pg_class where relname = 'pg_class';
-- dang, much too wide, press <arrow-up>
select * from pg_class where relname = 'pg_class' \G
-- ah nice!

select * from pg_class where relname = 'pg_class';
-- dang, much too wide
\G
-- ah nice!

Christoph

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Christoph Berg wrote:

A workaround is to submit queries using "\x\g\x", but that's ugly,
clutters the output with toggle messages, and will forget that "\x
auto" was set.

Mysql's CLI client is using \G for this purpose, and adding the very
same functionality to psql fits nicely into the set of existing
backslash commands: \g sends the query buffer, \G will do exactly the
same as \g (including parameters), but forces expanded output just for
this query.

+1 for the functionality but should we choose to ignore the comparison
to mysql, I'd suggest \gx for the name.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Jan 27, 2017 at 11:03:05AM -0500, Stephen Frost wrote:

Well, I did get the impression that you weren't thinking about that,
which is actually kind of surpirsing to me. Lots of things work on "the
current query buffer", which is the last query (successful or not, to be
clear..):

\crosstabview
\e
\g
\gexec
\gset
\p
\w
\watch

It's not entirely clear to me why the docs sometimes say "current query
buffer" and somtimes say "current query input buffer".

I would expect the "current query input buffer" to be what is used to
enter the next query. When you hit enter that goes into the "current query
buffer" (as the entered query is now officially complete and thus becomes
the current query) and then "current query input buffer" is cleared for
the next action to be dealt with, be it a query or psql command.

--
"A search of his car uncovered pornography, a homemade sex aid, women's
stockings and a Jack Russell terrier."
- http://www.dailytelegraph.com.au/news/wacky/indeed/story-e6frev20-1111118083480

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: Daniel Verite 2017-01-28 <74e7fd23-f5a9-488d-a8c4-1e0da674b27c@manitou-mail.org>

Mysql's CLI client is using \G for this purpose, and adding the very
same functionality to psql fits nicely into the set of existing
backslash commands: \g sends the query buffer, \G will do exactly the
same as \g (including parameters), but forces expanded output just for
this query.

+1 for the functionality but should we choose to ignore the comparison
to mysql, I'd suggest \gx for the name.

IMHO \G is a tad easier to type than \gx, though the difference isn't
huge, so I would be fine with either. But do we really want to choose
something different just because MySQL is using it? \G will be much
easier to explain to existing users (both people coming from MySQL to
PostgreSQL, and PostgreSQL users doing a detour into foreign
territory), and it would be one difference less to have to care about
when typing on the CLIs.

+1 on \G.

Christoph

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Christoph Berg (christoph.berg@credativ.de) wrote:

Re: Daniel Verite 2017-01-28 <74e7fd23-f5a9-488d-a8c4-1e0da674b27c@manitou-mail.org>

Mysql's CLI client is using \G for this purpose, and adding the very
same functionality to psql fits nicely into the set of existing
backslash commands: \g sends the query buffer, \G will do exactly the
same as \g (including parameters), but forces expanded output just for
this query.

+1 for the functionality but should we choose to ignore the comparison
to mysql, I'd suggest \gx for the name.

IMHO \G is a tad easier to type than \gx, though the difference isn't
huge, so I would be fine with either. But do we really want to choose
something different just because MySQL is using it? \G will be much
easier to explain to existing users (both people coming from MySQL to
PostgreSQL, and PostgreSQL users doing a detour into foreign
territory), and it would be one difference less to have to care about
when typing on the CLIs.

+1 on \G.

Agreed, +1 on \G and with the above argument- why in the world would we
want to avoid using \G just because MySQL uses it?

Thanks!

Stephen

2017-01-30 14:46 GMT+01:00 Stephen Frost <sfrost@snowman.net>:

* Christoph Berg (christoph.berg@credativ.de) wrote:

Re: Daniel Verite 2017-01-28 <74e7fd23-f5a9-488d-a8c4-

1e0da674b27c@manitou-mail.org>

Mysql's CLI client is using \G for this purpose, and adding the very
same functionality to psql fits nicely into the set of existing
backslash commands: \g sends the query buffer, \G will do exactly the
same as \g (including parameters), but forces expanded output just

for

this query.

+1 for the functionality but should we choose to ignore the comparison
to mysql, I'd suggest \gx for the name.

IMHO \G is a tad easier to type than \gx, though the difference isn't
huge, so I would be fine with either. But do we really want to choose
something different just because MySQL is using it? \G will be much
easier to explain to existing users (both people coming from MySQL to
PostgreSQL, and PostgreSQL users doing a detour into foreign
territory), and it would be one difference less to have to care about
when typing on the CLIs.

+1 on \G.

Agreed, +1 on \G and with the above argument- why in the world would we
want to avoid using \G just because MySQL uses it?

The argument so MySQL uses it is not good. But \gx respect convention and
it is little bit more descriptive.

The difference is minor - I prefer \gx due consistency with Postgres. I
don't know much MySQL people who use a terminal.

Regards

Pavel

Christoph Berg wrote:

But do we really want to choose
something different just because MySQL is using it?

That's not what I meant. If mysql wasn't using \G
I'd still suggest the name \gx because:

- it means the functionality of \g combined with \x so
semantically it makes sense.

- there is no precedent in psql that the upper-case version
of a meta-command as a variant of the lower-case version:
\C has nothing to do with \c, and \H nothing with \h, and
\T and \t are equally disconnected

- there hasn't been much use up to now of uppercase
meta-commands, C,T and H are the only ones I see in \?
\d[something] is crowded with lots of "something", whereas \D is not
used at all. The pattern seems to be that uppercase is the exception.

FWIW I don't share the feeling that \G is easier to remember or type
than \gx.

\G will be much easier to explain to existing users (both people
coming from MySQL to PostgreSQL, and PostgreSQL users doing a detour
into foreign territory), and it would be one difference less to have
to care about when typing on the CLIs.

That's a good argument, but if it's pitted against psql's
consistency with itself, I'd expect the latter to win.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

"Daniel Verite" <daniel@manitou-mail.org> writes:

\G will be much easier to explain to existing users (both people
coming from MySQL to PostgreSQL, and PostgreSQL users doing a detour
into foreign territory), and it would be one difference less to have
to care about when typing on the CLIs.

That's a good argument, but if it's pitted against psql's
consistency with itself, I'd expect the latter to win.

FWIW, \gx makes sense to me as well, particularly if we make it a
complete extension of \g and allow an optional target file name.
Does that functionality exist in mysql's \G ?

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Pavel Stehule (pavel.stehule@gmail.com) wrote:

2017-01-30 14:46 GMT+01:00 Stephen Frost <sfrost@snowman.net>:

* Christoph Berg (christoph.berg@credativ.de) wrote:

Re: Daniel Verite 2017-01-28 <74e7fd23-f5a9-488d-a8c4-

1e0da674b27c@manitou-mail.org>

Mysql's CLI client is using \G for this purpose, and adding the very
same functionality to psql fits nicely into the set of existing
backslash commands: \g sends the query buffer, \G will do exactly the
same as \g (including parameters), but forces expanded output just

for

this query.

+1 for the functionality but should we choose to ignore the comparison
to mysql, I'd suggest \gx for the name.

IMHO \G is a tad easier to type than \gx, though the difference isn't
huge, so I would be fine with either. But do we really want to choose
something different just because MySQL is using it? \G will be much
easier to explain to existing users (both people coming from MySQL to
PostgreSQL, and PostgreSQL users doing a detour into foreign
territory), and it would be one difference less to have to care about
when typing on the CLIs.

+1 on \G.

Agreed, +1 on \G and with the above argument- why in the world would we
want to avoid using \G just because MySQL uses it?

The argument so MySQL uses it is not good. But \gx respect convention and
it is little bit more descriptive.

The difference is minor - I prefer \gx due consistency with Postgres. I
don't know much MySQL people who use a terminal.

This particular bike-shedding really doesn't seem to be terribly useful
or sensible, to me. \gx isn't "consistent" or "descriptive", frankly.

If anything, 'x' as the second character, today, implies "extension" as
it's used that way by \dx.

Let's go with \G and be done with it.

Thanks!

Stephen