psql \G command -- send query and output using extended format

Hello!

Attached is a simple patch which adds a "\G" backslash command, that is
similar "\g" command. The difference is that "\G" prints results using
extended output format. After the query the format is reset to the original
value.

In other words command like:
SELECT * FROM pg_stat_activity\G
is similar to doing:
\x on
SELECT * FROM pg_stat_activity\g
\x on or off -- depending on the original state.

Rationale: switching format using "\x" for duration of single query I find
a bit uncomfortable. This is similar to "\g file" which is one-shot version
of "\o file" command.

Regards,
Dawid

Hi!

I have sent a patch to pgsql-patches:
http://archives.postgresql.org/pgsql-patches/2008-04/msg00050.php
...which adds \G command to psql client.

The idea of \G command is to perform the query, but with printing
query results using extended table output format.

For example:

postgres=# SELECT * FROM pg_stat_activity;
datid | datname | procpid | usesysid | usename |
current_query | waiting | xact_start |
query_start | backend_start |
client_addr | client_port
-------+----------+---------+----------+----------+---------------------------------+---------+-------------------------------+-------------------------------+-------------------------------+-------------+-------------
11511 | postgres | 11729 | 10 | postgres | SELECT * FROM
pg_stat_activity; | f | 2008-04-03 14:40:15.277272+02 |
2008-04-03 14:40:15.277272+02 | 2008-04-03 14:39:50.050512+02 |
| -1
(1 row)

postgres=# SELECT * FROM pg_stat_activity\G
-[ RECORD 1 ]-+-------------------------------
datid | 11511
datname | postgres
procpid | 11729
usesysid | 10
usename | postgres
current_query | SELECT * FROM pg_stat_activity
waiting | f
xact_start | 2008-04-03 14:41:47.533763+02
query_start | 2008-04-03 14:41:47.533763+02
backend_start | 2008-04-03 14:39:50.050512+02
client_addr |
client_port | -1

postgres=# SELECT * FROM pg_stat_activity\g
datid | datname | procpid | usesysid | usename |
current_query | waiting | xact_start |
query_start | backend_start |
client_addr | client_port
-------+----------+---------+----------+----------+--------------------------------+---------+-------------------------------+-------------------------------+-------------------------------+-------------+-------------
11511 | postgres | 11729 | 10 | postgres | SELECT * FROM
pg_stat_activity | f | 2008-04-03 14:42:09.940897+02 |
2008-04-03 14:42:09.940897+02 | 2008-04-03 14:39:50.050512+02 |
| -1
(1 row)

Comments anyone?

Regards,
Dawid

"Dawid Kuroczko" <qnex42(at)gmail(dot)com> writes:
> The idea of \G command is to perform the query, but with printing
> query results using extended table output format.

Seems a bit useless --- if you prefer \x format, wouldn't you prefer it
all the time? Or at least often enough that the toggling command is
fine? I'm dubious that this is worth eating up a command letter for.

regards, tom lane

On Thu, Apr 3, 2008 at 4:35 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Dawid Kuroczko" <qnex42(at)gmail(dot)com> writes:
> > The idea of \G command is to perform the query, but with printing
> > query results using extended table output format.
>
> Seems a bit useless --- if you prefer \x format, wouldn't you prefer it
> all the time? Or at least often enough that the toggling command is
> fine? I'm dubious that this is worth eating up a command letter for.

No, the point is that I usually have mixed queries -- ones which are
most comfortably viewed in normal format (many not-so-long rows),
and ones which are best viewed expanded (little rows, many columns).

Alternating between formats using "\x" is, at least for me, a bit
cumbersome: usually _after_ I wrote a query I realize "it would
look more readable in expanded format", which is a bit too late.
So I run the query, ctrl+c, \x, rerun the query... and forget to
turn expanded mode off afterwards.

I think that ability to decide about the format after the query,
not before, can be quite useful especially when writing ad-hoc
queries. Incidentally "\g" and "\G" is also used more or less
similarily by our dolphin-loving friends -- which doesn't help
using "\G" for other things.

Regards,
Dawid

Dawid Kuroczko wrote:
> On Thu, Apr 3, 2008 at 4:35 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > "Dawid Kuroczko" <qnex42(at)gmail(dot)com> writes:
> > > The idea of \G command is to perform the query, but with printing
> > > query results using extended table output format.
> >
> > Seems a bit useless --- if you prefer \x format, wouldn't you prefer it
> > all the time? Or at least often enough that the toggling command is
> > fine? I'm dubious that this is worth eating up a command letter for.
>
> No, the point is that I usually have mixed queries -- ones which are
> most comfortably viewed in normal format (many not-so-long rows),
> and ones which are best viewed expanded (little rows, many columns).
>
> Alternating between formats using "\x" is, at least for me, a bit
> cumbersome: usually _after_ I wrote a query I realize "it would
> look more readable in expanded format", which is a bit too late.
> So I run the query, ctrl+c, \x, rerun the query... and forget to
> turn expanded mode off afterwards.
>
> I think that ability to decide about the format after the query,
> not before, can be quite useful especially when writing ad-hoc
> queries. Incidentally "\g" and "\G" is also used more or less
> similarily by our dolphin-loving friends -- which doesn't help
> using "\G" for other things.

It seems more helpful if there were \x option to use extended format
only when the output is too wide. TODO already has:

o Add auto-expanded mode so expanded output is used if the row
length is wider than the screen width.

Consider using auto-expanded mode for backslash commands like \df+.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian escribió:

> It seems more helpful if there were \x option to use extended format
> only when the output is too wide. TODO already has:
>
> o Add auto-expanded mode so expanded output is used if the row
> length is wider than the screen width.
>
> Consider using auto-expanded mode for backslash commands like \df+.

Some sort of "\x auto"? Sounds interesting ...

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Thu, Apr 03, 2008 at 12:07:54PM -0400, Bruce Momjian wrote:
> > Alternating between formats using "\x" is, at least for me, a bit
> > cumbersome: usually _after_ I wrote a query I realize "it would
> > look more readable in expanded format", which is a bit too late.
> > So I run the query, ctrl+c, \x, rerun the query... and forget to
> > turn expanded mode off afterwards.
>
> It seems more helpful if there were \x option to use extended format
> only when the output is too wide. TODO already has:

I was thinking that maybe \x should have a one-shot mode, i.e.

\x <query>

does it only for this one statement. It would solve the OPs problem.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Alvaro Herrera wrote:
> Bruce Momjian escribi?:
>
> > It seems more helpful if there were \x option to use extended format
> > only when the output is too wide. TODO already has:
> >
> > o Add auto-expanded mode so expanded output is used if the row
> > length is wider than the screen width.
> >
> > Consider using auto-expanded mode for backslash commands like \df+.
>
> Some sort of "\x auto"? Sounds interesting ...

Yep.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> On Thu, Apr 03, 2008 at 12:07:54PM -0400, Bruce Momjian wrote:
> > > Alternating between formats using "\x" is, at least for me, a bit
> > > cumbersome: usually _after_ I wrote a query I realize "it would
> > > look more readable in expanded format", which is a bit too late.
> > > So I run the query, ctrl+c, \x, rerun the query... and forget to
> > > turn expanded mode off afterwards.
> >
> > It seems more helpful if there were \x option to use extended format
> > only when the output is too wide. TODO already has:
>
> I was thinking that maybe \x should have a one-shot mode, i.e.
>
> \x <query>
>
> does it only for this one statement. It would solve the OPs problem.

But break for others who want all output \x.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Thu, Apr 03, 2008 at 01:06:26PM -0400, Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > Bruce Momjian escribi?:
> >
> > > It seems more helpful if there were \x option to use extended format
> > > only when the output is too wide. TODO already has:
> > >
> > > o Add auto-expanded mode so expanded output is used if the row
> > > length is wider than the screen width.
> > >
> > > Consider using auto-expanded mode for backslash commands like \df+.
> >
> > Some sort of "\x auto"? Sounds interesting ...
>
> Yep.

Having \df+ go to \x automatically sounds like a really great idea :)

Cheers,
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

On Thu, Apr 3, 2008 at 2:43 PM, David Fetter <david(at)fetter(dot)org> wrote:
> On Thu, Apr 03, 2008 at 01:06:26PM -0400, Bruce Momjian wrote:
> > > Some sort of "\x auto"? Sounds interesting ...
> >
> > Yep.
>
> Having \df+ go to \x automatically sounds like a really great idea :)

you can get pretty good resultsr currently for \df+ if you set up your
'less' pager a particular way.

merlin

On Thu, Apr 03, 2008 at 03:43:50PM -0400, Merlin Moncure wrote:
> On Thu, Apr 3, 2008 at 2:43 PM, David Fetter <david(at)fetter(dot)org> wrote:
> > On Thu, Apr 03, 2008 at 01:06:26PM -0400, Bruce Momjian wrote:
> > > > Some sort of "\x auto"? Sounds interesting ...
> > >
> > > Yep.
> >
> > Having \df+ go to \x automatically sounds like a really great
> > idea :)
>
> you can get pretty good resultsr currently for \df+ if you set up
> your 'less' pager a particular way.

Does 'less' have a way to re-arrange columns?!?

Cheers,
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

On Thu, Apr 3, 2008 at 4:08 PM, David Fetter <david(at)fetter(dot)org> wrote:
>
> On Thu, Apr 03, 2008 at 03:43:50PM -0400, Merlin Moncure wrote:
> > On Thu, Apr 3, 2008 at 2:43 PM, David Fetter <david(at)fetter(dot)org> wrote:
> > > On Thu, Apr 03, 2008 at 01:06:26PM -0400, Bruce Momjian wrote:
> > > > > Some sort of "\x auto"? Sounds interesting ...
> > > >
> > > > Yep.
> > >
> > > Having \df+ go to \x automatically sounds like a really great
> > > idea :)
> >
> > you can get pretty good resultsr currently for \df+ if you set up
> > your 'less' pager a particular way.
>
> Does 'less' have a way to re-arrange columns?!?

no, but being able to scroll left/right with the arrow keys is
(usually) just as good.

that said, \G is pretty neat...I use it once in a while on the 'other'
open source database.

merlin

Bruce Momjian escribió:
> Martijn van Oosterhout wrote:

> > I was thinking that maybe \x should have a one-shot mode, i.e.
> >
> > \x <query>
> >
> > does it only for this one statement. It would solve the OPs problem.
>
> But break for others who want all output \x.

I think Martijn is proposing using it as some sort of prefix which would
take effect only on the current query.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Bruce Momjian escribi:
>> Martijn van Oosterhout wrote:
>>> I was thinking that maybe \x should have a one-shot mode, i.e.
>>> \x <query>
>>> does it only for this one statement. It would solve the OPs problem.
>>
>> But break for others who want all output \x.

> I think Martijn is proposing using it as some sort of prefix which would
> take effect only on the current query.

A bigger problem is that it doesn't play nicely at all with multi-line
queries.

regards, tom lane

Tom Lane escribió:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:

> > I think Martijn is proposing using it as some sort of prefix which would
> > take effect only on the current query.
>
> A bigger problem is that it doesn't play nicely at all with multi-line
> queries.

Hmm, why wouldn't it? I assume it would only be recognized if the query
buffer is empty.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Tom Lane escribi:
>> A bigger problem is that it doesn't play nicely at all with multi-line
>> queries.

> Hmm, why wouldn't it? I assume it would only be recognized if the query
> buffer is empty.

Huh? The proposed syntax was

\x query...

What do you do when you'd like the query to extend over multiple lines?
Backslash commands can't cross lines.

regards, tom lane

Tom Lane escribió:

> Huh? The proposed syntax was
>
> \x query...
>
> What do you do when you'd like the query to extend over multiple lines?
> Backslash commands can't cross lines.

Save the fact that the current query is extended, until query end? I
haven't actually looked at what the implementation would look like.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Tom Lane escribi:
>> Huh? The proposed syntax was
>>
>> \x query...
>>
>> What do you do when you'd like the query to extend over multiple lines?
>> Backslash commands can't cross lines.

> Save the fact that the current query is extended, until query end?

Yech. To name just a couple of problems, what if you decide after
typing another line or two that you didn't want \x after all?
Action-at-a-distance commands suck. You'd also find that this didn't
play very nicely with history recall, since the history stuff assumes
that a line starting with a backslash command is separate from those
around it.

If we want this at all, we should do it the way first proposed (\G).

regards, tom lane

On Thu, Apr 3, 2008 at 6:44 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Bruce Momjian escribió:
>
>
> > It seems more helpful if there were \x option to use extended format
> > only when the output is too wide. TODO already has:
> >
> > o Add auto-expanded mode so expanded output is used if the row
> > length is wider than the screen width.
> >
> > Consider using auto-expanded mode for backslash commands like \df+.
>
> Some sort of "\x auto"? Sounds interesting ...

Hmm, seems doable.

While writing the \G patch I wanted to keep the changes to minimum, so it would
be clear what gets done. What I don't like about that patch is that
I've added in
struct _printTableOpt another bool "extend_once" after the "extend" bool.

I think there should be a format Enum, which would take values like NORMAL,
EXTENDED, and EXTENDED_ONCE -- but this would be a much more invasive patch.
Oh, and coincidentally its where AUTO format should go. :)

Now, assuming we want "\x auto" there are couple of things to discuss
before actually coding.

1. Adding "\x auto" changes how "\x" (and \pset). Currently we accept:
\x - toggle between on and off
\x off - turn extended format off
\x anything - turn extended format on.
if doing, auto we need to change it to something like,
\x - toggle between on and off, if in "auto", toggle to "off"
\x on - extended format on
\x off - extended format off
\x auto - auto extended format
\x anything else - extended format on with a "depreciated" warning.

2. Do we want \G? I would say "yes". ;) But it should get discussed.
pgsql-general perhaps?

3. We should decide how each of the "commands" work in auto mode.
I think it should be something like: "if output is less or equal than
screen width -- use normal mode, else extended". I we have \g and \G,
they should be handled a bit differently in auto mode:
";" -- perform automatic format adjustment
"\g" -- force normal mode (probably for file-output)
"\G" -- force extended mode (probably for file-output).
I especially would like an opinion on this from you.

4. And as for using "\x" for the one-shot expanded output, I think it would
be possible to allow queries like:
SELECT * FROM foo\x
...but I think fundamental problem with this approach is that it promotes
"\x" to be able to submit query. I don't think its a good idea.

Regards,
Dawid

Dawid Kuroczko escribió:

> Hmm, seems doable.

I think that the followup discussion leads to implementing just \G (and
\x auto).

> I think there should be a format Enum, which would take values like NORMAL,
> EXTENDED, and EXTENDED_ONCE -- but this would be a much more invasive patch.
> Oh, and coincidentally its where AUTO format should go. :)

If the code ends up better with a more invasive patch, by all means do
that.

> 3. We should decide how each of the "commands" work in auto mode.
> I think it should be something like: "if output is less or equal than
> screen width -- use normal mode, else extended". I we have \g and \G,
> they should be handled a bit differently in auto mode:
> ";" -- perform automatic format adjustment
> "\g" -- force normal mode (probably for file-output)
> "\G" -- force extended mode (probably for file-output).
> I especially would like an opinion on this from you.

Sounds good -- in auto mode, you can override it if you want (unless it
is one backslash command, but I don't think that's a problem)

> 4. And as for using "\x" for the one-shot expanded output, I think it would
> be possible to allow queries like:
> SELECT * FROM foo\x
> ...but I think fundamental problem with this approach is that it promotes
> "\x" to be able to submit query. I don't think its a good idea.

No, I don't think this is a good idea either. Let's just not implement
\x.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Dawid Kuroczko wrote:
> 2. Do we want \G? I would say "yes". ;) But it should get discussed.
> pgsql-general perhaps?

No. We have had little demand for the auto, let alone a \G. Once we
have auto I don't see a use for \G.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Apr 3, 2008, at 9:35 AM, Tom Lane wrote:
> "Dawid Kuroczko" <qnex42(at)gmail(dot)com> writes:
>> The idea of \G command is to perform the query, but with printing
>> query results using extended table output format.
>
> Seems a bit useless --- if you prefer \x format, wouldn't you
> prefer it
> all the time? Or at least often enough that the toggling command is
> fine? I'm dubious that this is worth eating up a command letter for.

I agree about not eating a letter, but...

I often find myself wanting to do stuff like tuple-only output for a
single query in scripts. It would be nice if there was a command
(\G ?) that would accept additional options for controlling output.
It should be possible to map the options to existing psql output
stuff, so that you could force any single command to output in any
format that you wanted it to.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828