proposal: psql command \graw

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

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.

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

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

2017-08-22 10:36 GMT+02:00 Aleksander Alekseev <a.alekseev@postgrespro.ru>:

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 ...

you are mixing server side and client side code. Maybe some time we can
write psql custom commands.

can be nice to have more pipes in series

so you can do

select * from data
\graw | histogram | gnuplot ...

Hi

2017-08-22 11:22 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:

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

postgres=# select pocet_muzu + pocet_zen from obce
postgres-# \graw | gnuplot -p -e "set terminal dumb; plot '-' with boxes"

  1.4e+06
+-+-----+-------+-------+--------+-------+-------+-------+-----+-+
          +       +       +       +        +       +       +       +
+
          |       *                                            '-' *******
|
  1.2e+06 +-+     *
 +-+
          |       *
 |
    1e+06 +-+     *
 +-+
          |       *
 |
          |       *
 |
   800000 +-+     *
 +-+
          |       *
 |
          |       *
 |
   600000 +-+     *
 +-+
          |       *
 |
          |       *
 |
   400000 +-+     *                                     *
 +-+
          |       *                                     *            *
|
   200000 +-+     *                                     *            *
+-+
          |       *               *                     *            *
|
          +       * *  *  +**     *  ** ****   *   *  * *  + ***  ****
+
        0
+-+-----****************************************************---+-+
        -1000     0      1000    2000     3000    4000    5000    6000
 7000

postgres=#

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.

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

Hi

2017-08-24 5:50 GMT+02:00 Fabien COELHO <coelho@cri.ensmp.fr>:

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?

I can do

"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

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.

Missing help entry.

Missing non regression tests.

Missing documentation.

yes - I wrote it like proof concept - be possible (for me, for others) to
verify usability of this commands (and design). I tested it against gnuplot
and looks it is works

Regards

Pavel

"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.

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

2017-08-24 8:53 GMT+02:00 Fabien COELHO <coelho@cri.ensmp.fr>:

"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?

yes - in this case you have true.

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.

It is open - I'll wait to winter commitfest for some other ideas, tests,
comments - it is topic for PostgreSQL 11, and then there are a time for
discussion

Now, I'll be happy if some other people will test it with larger set of
applications and send me a feedback.

Regards

Pavel

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.

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

2017-08-24 17:27 GMT+02:00 Fabien COELHO <coelho@cri.ensmp.fr>:

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?

It can be moved

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.

I would to verify format, but thank you very much for your interest :)

Regards

Pavel

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?

It can be moved

Indeed it can. Feel free to move it, then.

--
Fabien.

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

Hi

2017-08-24 5:50 GMT+02:00 Fabien COELHO <coelho@cri.ensmp.fr>:

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.

I hope so I fixed all mentioned issues.

Regards

Pavel

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.

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

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

2017-11-09 21:12 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

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.

updated patch

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.

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

2017-11-10 8:12 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

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.

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) ----<<<<<

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.

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