Обсуждение: [HACKERS] proposal: psql command \graw

Hello Pavel,

One my idea is introduction new simple output format and execution command
with result in this format.

It should work something like

\setenv GNUPLOT_OPTION '......'
SELECT * FROM data
\graw | gnuplot ...

I understand that it is kind of a shortcut for:

  \pset fieldsep ' '
  \pset format unaligned
  \pset tuples_only on
  -- possibly other settings...
  SELECT * FROM data \g | gnuplot '...'

And then you have to persuade gnuplot to take its data from stdin?

--
Fabien.

Hi Pavel,

> I am thinking about printing graphs in psql (mainly some histograms). I
> found so gnuplot is able do very good graphs in console. The one issue is
> user friendly (with less steps) generating data in good format for this
> application.
>
> One my idea is introduction new simple output format and execution command
> with result in this format.
>
> It should work something like
>
> \setenv GNUPLOT_OPTION '......'
>
> SELECT * FROM data
>
> \graw | gnuplot ...
>
> It can be used for any other applications R, ggplot, ..
>
> Ideas, comments?

Sounds cool. On the other hand, I think it's kind of too domain specific
task. So I wonder whether it could be generalized somehow so anyone
could write an extension that would export data in any format in a
friendly way.

For instance:

create extension export_to_gnuplot;
select * from data
\export_to_gnuplot | gnuplot ...

--
Best regards,
Aleksander Alekseev

2017-08-22 10:46 GMT+02:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello Pavel,

One my idea is introduction new simple output format and execution command
with result in this format.

It should work something like

\setenv GNUPLOT_OPTION '......'
SELECT * FROM data
\graw | gnuplot ...

I understand that it is kind of a shortcut for:

  \pset fieldsep ' '
  \pset format unaligned
  \pset tuples_only on
  -- possibly other settings...
  SELECT * FROM data \g | gnuplot '...'

And then you have to persuade gnuplot to take its data from stdin?

There are some methods

https://stackoverflow.com/questions/17543386/pipe-plot-data-to-gnuplot-script/17576571#17576571

--
Fabien.

Hello Pavel,

I have added the patch to the next commitfest.

Patch applies, compiles, works.

I'm okay with the names graw/graw+, and for having such short-hands.

Missing break in switch, even if last item and useless, because other
items do it... Also should be added at its place in alphabetical order?

"column_header" is somehow redundant with "tuples_only". Use the
existing one instead of adding a new one?

More generally, ISTM that the same effect could be achieved without
adding a new print function, but by setting more options (separator,
...) and calling an existing print function. If so, I think it would
reduce the code size.

Missing help entry.

Missing non regression tests.

Missing documentation.

--
Fabien.

"column_header" is somehow redundant with "tuples_only". Use the
existing one instead of adding a new one?

It is different - a result of tuples_only is just tuples - not column
names, not title, footer. I needed new special flag for enhancing
tuples_only to print column names

I do not understand. If you keep the special print_raw function, it can use tuples_only as true for without column names, and false for with column names?

More generally, ISTM that the same effect could be achieved without
adding a new print function, but by setting more options (separator,
...) and calling an existing print function. If so, I think it would
reduce the code size.

Maybe, maybe not. removing PRINT_RAW you need to enhance PRINT_UNALIGNED to
use one shot parameters and you have to teach it to print column names in
tuples_only mode. The code's length will be same. The form of this patch is
not final.

Hmmm. Ok. It depends on the change implication on the print unaligned function.

--
Fabien.

Hello,

I'll wait to winter commitfest

Argh, sorry, I put it in the September commitfest, and it seems that it cannot be changed afterwards.

Maybe you can close it and redeclare it in the commitfest you want?

for some other ideas, tests, comments - it is topic for PostgreSQL 11, and then there are a time for discussion.

I was rather seeing that as a small patch which could have been processed quickly, but if you expect feedback maybe it is better to give it more time.

--
Fabien.

Hello Pavel,

I hope so I fixed all mentioned issues.

Patch applies with a warning:

 > git apply ~/psql-graw-2.patch
 /home/fabien/psql-graw-2.patch:192: new blank line at EOF.
 +
 warning: 1 line adds whitespace errors.

Otherwise it compiles. "make check" ok. doc gen ok.

Two spurious empty lines are added before StoreQueryTuple.

Doc: "If <literal>+</literal> is appended to the command name, a column names are displayed."

I suggest instead: "When <literal>+</literal> is appended, column names are also displayed."

ISTM that you can remove "force_column_header" and just set "tuple_only" to what you need, that is you do not need to change anything in function "print_unaligned_text".

--
Fabien.

2017-11-09 21:03 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello Pavel,

I hope so I fixed all mentioned issues.

Patch applies with a warning:

 > git apply ~/psql-graw-2.patch
 /home/fabien/psql-graw-2.patch:192: new blank line at EOF.
 +
 warning: 1 line adds whitespace errors.

Otherwise it compiles. "make check" ok. doc gen ok.

Two spurious empty lines are added before StoreQueryTuple.

Doc: "If <literal>+</literal> is appended to the command name, a column names are displayed."

I suggest instead: "When <literal>+</literal> is appended, column names are also displayed."

ISTM that you can remove "force_column_header" and just set "tuple_only" to what you need, that is you do not need to change anything in function "print_unaligned_text".

Last point is not possible - I would not to break original tuple only mode.

Pavel

--
Fabien.

ISTM that you can remove "force_column_header" and just set "tuple_only"
to what you need, that is you do not need to change anything in function
"print_unaligned_text".

Last point is not possible - I would not to break original tuple only mode.

Hmmm... I do not understand. I can see only one use of force_column_header in the function:

 -               if (!opt_tuples_only)
 +               if (!opt_tuples_only || opt_force_column_header)

So I would basically suggest to do:

 my_popt.topt.tuples_only = !pset.g_raw_header;

in the driver. Looking at the detailed code in that function, probably you need to set start_table to on when headers are needed and stop_table to off for the raw mode anyway?

Maybe I'm missing something, but it looks that it could be made to work without adding another boolean.

--
Fabien.

Hello,

Maybe I'm missing something, but it looks that it could be made to work without adding another boolean.

The tuples only cannot be disabled, because then other parts print number
of rows

postgres=# \pset format unaligned
Output format is unaligned.

postgres=# select 10 as a, 20 as b;
a|b
10|20
(1 row) ----<<<<<

Argh. Too bad.

I'm not at ease with having two bools which nearly mean the opposite one of the other but not exactly... however I'm not sure that there is a simpler way out of this, some exception handling is needed one way or the other, either within the header or within the footer... Maybe the whole topt logic should be reviewed, but that is not the point of this patch.

So I switched the patch to "ready for committer".

--
Fabien.

2017-12-04 9:29 GMT+01:00 Alexander Korotkov <a.korotkov@postgrespro.ru>:

On Mon, Dec 4, 2017 at 11:21 AM, Alexander Korotkov <a.korotkov@postgrespro.ru> wrote:

The problem is that it's hard to read arbitrary formatted psql output from external program (not just gnuplot, but even especially written script).  I made my scripts read few variations, but it doesn't look feasible to read all the combinations.  For sure, it's possible to set format options inside macro, but then it would affect psql format options after execution.

This is why I think only one \graw option is just fine, because it produces stable machine-readable output.

Oh, I just get that in current state of \graw doesn't produce good machine-readable output.

# select '|', '|' \graw

|||

Column separator is character which can occur in values, and values aren't escaped.  Thus, reader can't correctly divide values between columns in all the cases.  So, I would rather like to see \graw to output in csv format with proper escaping.

current \graw implementation is pretty minimalistic

It is interesting topic - the client side csv support.

It can simplify lot of things 

Hi!

On Mon, Dec 4, 2017 at 6:42 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2017-12-04 9:29 GMT+01:00 Alexander Korotkov <a.korotkov@postgrespro.ru>:

On Mon, Dec 4, 2017 at 11:21 AM, Alexander Korotkov <a.korotkov@postgrespro.ru> wrote:

The problem is that it's hard to read arbitrary formatted psql output from external program (not just gnuplot, but even especially written script).  I made my scripts read few variations, but it doesn't look feasible to read all the combinations.  For sure, it's possible to set format options inside macro, but then it would affect psql format options after execution.

This is why I think only one \graw option is just fine, because it produces stable machine-readable output.

Oh, I just get that in current state of \graw doesn't produce good machine-readable output.

# select '|', '|' \graw

|||

Column separator is character which can occur in values, and values aren't escaped.  Thus, reader can't correctly divide values between columns in all the cases.  So, I would rather like to see \graw to output in csv format with proper escaping.

current \graw implementation is pretty minimalistic

It is interesting topic - the client side csv support.

It can simplify lot of things 

So, I see there is no arguing yet that exporting dataset from psql into a pipe in machine-readable format (presumably csv) would be an useful feature.

Are you going to revise your patch that way during this commitfest?

I'm marking this patch as "waiting for author" for now.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com

The Russian Postgres Company 

        Pavel Stehule wrote:

> We are able to generate html/tex/markdown formats on client side. CSV is
> more primitive, but much more important data format, so it should not be a
> problem. But I remember some objections related to code duplication.

While experimenting with adding csv as an output format
(similar to unaligned except that it quotes with CSV rules), I find out
that we can already do this:

\o | client-side-program with arguments
or
\o /path/to/file.csv
COPY (select query) TO STDOUT CSV [HEADER] ;
\o

This can be used to route multiple resultsets into multiple programs
or files without the minor drawbacks of \copy (single line
formatting, no variables interpolation), or
psql -c "COPY (query) TO STDOUT.." >file.csv which is a bit rigid
(single redirect).


OTOH I notice that this simpler form with a right-side argument of \g:
 =# COPY (select 1) TO STDOUT CSV HEADER \g /tmp/file.csv
outputs to the console rather into a file. Not sure why it acts
differently than \o and whether it's intentional.

Anyway I think the above sequence with \o already does everything
that is being proposed as an alternative to \graw, or am I missing
something?

What \o + COPY CSV lacks compared to a csv output format
is the ability to export results from meta-commands in csv,
such as \g in \x mode or \gx, \crosstabview, \d, \l...

The other problem with \o |program + COPY CSV is that it's
not easy to discover that combination. It's more obvious if
we have csv explicitly listed as an output format.

I'll post the patch implementing that in a new thread and we'll see
how it goes.


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