proposal \gcsv

On 29/02/2020 06:43, Pavel Stehule wrote:

Hi

I would to enhance \g command about variant \gcsv

proposed command has same behave like \g, only the result will be every
time in csv format.

It can helps with writing psql macros wrapping \g command.

Options, notes?

But then we would need \ghtml and \glatex etc. If we want a shortcut
for setting a one-off format, I would go for \gf or something.

\gf csv
\gf html
\gf latex

-1 on \gcsv
--
Vik Fearing

so 29. 2. 2020 v 11:34 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

On 29/02/2020 06:43, Pavel Stehule wrote:

Hi

I would to enhance \g command about variant \gcsv

proposed command has same behave like \g, only the result will be every
time in csv format.

It can helps with writing psql macros wrapping \g command.

Options, notes?

But then we would need \ghtml and \glatex etc. If we want a shortcut
for setting a one-off format, I would go for \gf or something.

\gf csv
\gf html
\gf latex

usability of html or latex format in psql is significantly lower than csv
format. There is only one generic format for data - csv.

Regards

Pavel

On Sat, Feb 29, 2020 at 11:59:22AM +0100, Pavel Stehule wrote:

so 29. 2. 2020 v 11:34 odes�latel Vik Fearing <vik@postgresfriends.org>
napsal:

On 29/02/2020 06:43, Pavel Stehule wrote:

Hi

I would to enhance \g command about variant \gcsv

proposed command has same behave like \g, only the result will be every
time in csv format.

It can helps with writing psql macros wrapping \g command.

Options, notes?

But then we would need \ghtml and \glatex etc. If we want a shortcut
for setting a one-off format, I would go for \gf or something.

\gf csv
\gf html
\gf latex

usability of html or latex format in psql is significantly lower than csv
format. There is only one generic format for data - csv.

Not exactly. There's a lot of uses for things along the lines of

\gf json
\gf yaml

I'd rather add a new \gf that takes arguments, as it seems more
extensible. For example, there are uses for

\gf csv header

if no header is the default, or

\gf csv noheader

if header is the default.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

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

so 29. 2. 2020 v 11:34 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

On 29/02/2020 06:43, Pavel Stehule wrote:

Hi

I would to enhance \g command about variant \gcsv

proposed command has same behave like \g, only the result will be every
time in csv format.

It can helps with writing psql macros wrapping \g command.

Options, notes?

But then we would need \ghtml and \glatex etc. If we want a shortcut
for setting a one-off format, I would go for \gf or something.

\gf csv
\gf html
\gf latex

ok. I implemented \gf. See a attached patch

Regards

Pavel

so 29. 2. 2020 v 18:06 odesílatel David Fetter <david@fetter.org> napsal:

On Sat, Feb 29, 2020 at 11:59:22AM +0100, Pavel Stehule wrote:

so 29. 2. 2020 v 11:34 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

On 29/02/2020 06:43, Pavel Stehule wrote:

Hi

I would to enhance \g command about variant \gcsv

proposed command has same behave like \g, only the result will be

every

time in csv format.

It can helps with writing psql macros wrapping \g command.

Options, notes?

But then we would need \ghtml and \glatex etc. If we want a shortcut
for setting a one-off format, I would go for \gf or something.

\gf csv
\gf html
\gf latex

usability of html or latex format in psql is significantly lower than csv
format. There is only one generic format for data - csv.

Not exactly. There's a lot of uses for things along the lines of

\gf json
\gf yaml

I'd rather add a new \gf that takes arguments, as it seems more
extensible. For example, there are uses for

I implemented \gf by Vik's proposal

\gf csv header

if no header is the default, or

\gf csv noheader

It is little bit hard (although it looks simply).

The second option of this command can be file - and it reads all to end of
line. So in this case a implementation of variadic parameters is difficult.

Motivation for this patch is a possibility to write macros like

postgres=# \set gnuplot '\\g | gnuplot -p -e "set datafile separator
\',\'; set key autotitle columnhead; set terminal dumb enhanced; plot
\'-\'with boxes"'

postgres=# \pset format csv

postgres=# select i, sin(i) from generate_series(0, 6.3, 0.05) g(i) :gnuplot

with \gf csv I can do almost what I need.

\set gnuplot '\\gf csv | gnuplot -p -e "set datafile separator \',\'; set
key autotitle columnhead; set terminal dumb enhanced; plot \'-\'with
boxes"'

On 01/03/2020 13:29, Pavel Stehule wrote:

so 29. 2. 2020 v 11:34 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

On 29/02/2020 06:43, Pavel Stehule wrote:

Hi

I would to enhance \g command about variant \gcsv

proposed command has same behave like \g, only the result will be every
time in csv format.

It can helps with writing psql macros wrapping \g command.

Options, notes?

But then we would need \ghtml and \glatex etc. If we want a shortcut
for setting a one-off format, I would go for \gf or something.

\gf csv
\gf html
\gf latex

ok. I implemented \gf. See a attached patch

I snuck this into the commitfest that starts today while no one was
looking. https://commitfest.postgresql.org/27/2503/

And I added myself as reviewer.
--
Vik Fearing

Hi

rebase

Regards

Pavel

On 3/24/20 3:02 AM, Pavel Stehule wrote:

Hi

rebase

Thank you, Pavel.

I have now had time to review it, and it looks good to me except for two
issues.

The first is, even though I suggested gf, I think it should actually be
gfmt. There may be something else in the future that starts with f and
we shouldn't close ourselves off to it.

The second is tab completion doesn't work for the second argument.
Adding the following fixes that:

diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index ed6945a7f12..9d8cf442972 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3786,6 +3786,12 @@ psql_completion(const char *text, int start, int end)
                COMPLETE_WITH_CS("aligned", "asciidoc", "csv", "html",
"latex",
                                                 "latex-longtable",
"troff-ms", "unaligned",
                                                 "wrapped");
+       else if (TailMatchesCS("\\gf", MatchAny))
+       {
+               completion_charp = "\\";
+               completion_force_quote = false;
+               matches = rl_completion_matches(text, complete_from_files);
+       }

else if (TailMatchesCS("\\h|\\help"))
COMPLETE_WITH_LIST(sql_commands);

After some opinions on the first issue and fixing the second, I think
this is good to be committed.
--
Vik Fearing

Hi

čt 26. 3. 2020 v 17:45 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

On 3/24/20 3:02 AM, Pavel Stehule wrote:

Hi

rebase

Thank you, Pavel.

I have now had time to review it, and it looks good to me except for two
issues.

The first is, even though I suggested gf, I think it should actually be
gfmt. There may be something else in the future that starts with f and
we shouldn't close ourselves off to it.

renamed to \gfmt

The second is tab completion doesn't work for the second argument.
Adding the following fixes that:

diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index ed6945a7f12..9d8cf442972 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3786,6 +3786,12 @@ psql_completion(const char *text, int start, int
end)
COMPLETE_WITH_CS("aligned", "asciidoc", "csv", "html",
"latex",
"latex-longtable",
"troff-ms", "unaligned",
"wrapped");
+       else if (TailMatchesCS("\\gf", MatchAny))
+       {
+               completion_charp = "\\";
+               completion_force_quote = false;
+               matches = rl_completion_matches(text, complete_from_files);
+       }

else if (TailMatchesCS("\\h|\\help"))
COMPLETE_WITH_LIST(sql_commands);

merged

After some opinions on the first issue and fixing the second, I think
this is good to be committed.

Thank you for review

Pavel

--

On 3/26/20 10:49 AM, Pavel Stehule wrote:

Hi

čt 26. 3. 2020 v 17:45 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

After some opinions on the first issue and fixing the second, I think
this is good to be committed.

Thank you for review

This patch now looks good to me. Marking as Ready for Committer.
--
Vik Fearing

čt 26. 3. 2020 v 18:55 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

On 3/26/20 10:49 AM, Pavel Stehule wrote:

Hi

čt 26. 3. 2020 v 17:45 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

After some opinions on the first issue and fixing the second, I think
this is good to be committed.

Thank you for review

This patch now looks good to me. Marking as Ready for Committer.

Thank you very much

Pavel

--

On 2020-03-26 18:49, Pavel Stehule wrote:

Hi

[psql-gfmt.patch]

This seems useful and works well; I haven't found any errors. Well done.

However, I have a suggestion that is perhaps slightly outside of this
patch but functionally so close that maybe we can discuss it here.

When you try to get a tab-separated output via this new \gfmt in a
one-liner
you're still forced to use
\pset csv_fieldsep '\t'

Would it be possible to do one of the following to enable a more compact
one-liner syntax:

1. add an option:
\gfmt tsv --> use a TAB instead of a comma in the csv

or

2. let the psql command-line option '--csv' honour the value given by
psql -F/--field-separator (it does not do so now)

or

3. add an psql -commandline option:
--csv-field-separator

Any of these three (I'd prefer the first) would make producing a tsv in
shell one-liners with psql easier/more compact.

Thanks,

Erik Rijkers

čt 26. 3. 2020 v 19:41 odesílatel Erik Rijkers <er@xs4all.nl> napsal:

On 2020-03-26 18:49, Pavel Stehule wrote:

Hi

[psql-gfmt.patch]

This seems useful and works well; I haven't found any errors. Well done.

However, I have a suggestion that is perhaps slightly outside of this
patch but functionally so close that maybe we can discuss it here.

When you try to get a tab-separated output via this new \gfmt in a
one-liner
you're still forced to use
\pset csv_fieldsep '\t'

Would it be possible to do one of the following to enable a more compact
one-liner syntax:

1. add an option:
\gfmt tsv --> use a TAB instead of a comma in the csv

or

2. let the psql command-line option '--csv' honour the value given by
psql -F/--field-separator (it does not do so now)

or

3. add an psql -commandline option:
--csv-field-separator

Any of these three (I'd prefer the first) would make producing a tsv in
shell one-liners with psql easier/more compact.

I understand to your proposal, but it's hard to do inside \gfmt command

1. a syntax of psql backslash commands doesn't support named parameters,
and \gfmt (like some others \gx) statements has optional parameter already.
There was a long discussion (without success) about possible
parametrizations of psql commands.

2. if I understand to tsv format, then it is not CSV format with different
separator.

the most correct design is introduction new output format "tsv".This format
can produce 100% valid tsv.

Regards

Pavel

Erik Rijkers wrote:

2. let the psql command-line option '--csv' honour the value given by
psql -F/--field-separator (it does not do so now)

or

3. add an psql -commandline option:
--csv-field-separator

Setting the field separator on the command line is already supported
through this kind of invocation:

psql --csv -P csv_fieldsep=$'\t'

bash expands $'\t' to a tab character. Other shells might need
different tricks.

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

so 28. 3. 2020 v 15:06 odesílatel Daniel Verite <daniel@manitou-mail.org>
napsal:

Erik Rijkers wrote:

2. let the psql command-line option '--csv' honour the value given by
psql -F/--field-separator (it does not do so now)

or

3. add an psql -commandline option:
--csv-field-separator

Setting the field separator on the command line is already supported
through this kind of invocation:

psql --csv -P csv_fieldsep=$'\t'

bash expands $'\t' to a tab character. Other shells might need
different tricks.

We have named parameters in shell, but not in psql

On 2020-03-28 15:06, Daniel Verite wrote:

Erik Rijkers wrote:

2. let the psql command-line option '--csv' honour the value given by
psql -F/--field-separator (it does not do so now)

or

3. add an psql -commandline option:
--csv-field-separator

Setting the field separator on the command line is already supported
through this kind of invocation:

psql --csv -P csv_fieldsep=$'\t'

bash expands $'\t' to a tab character. Other shells might need
different tricks.

Ah yes, that works. I had not seen that psql -P option. Thanks!

I took a look at this proposal, and while I see the value of being
able to do something like this, it seems pretty short-sighted and
non-orthogonal as it stands. We've already got \gx, which is a wart,
and now this patch wants to add \gfmt which is a different wart of the
same ilk. What happens if you want to combine them? Plus we already
had David complaining upthread that he'd like to be able to select
CSV-format suboptions; and now here comes Erik wondering about the
same thing.

It seems to me that this line of development is going to end in a whole
flotilla of \g-something commands that aren't composable and never quite
satisfy the next use-case to come down the pike, so we keep on needing
even more of them.

So I think we really need a way to be able to specify multiple different
\pset subcommands that apply just for the duration of one \g command.
Pavel dismissed that upthread as being too hard, but I think we'd better
try harder.

Plan A:

Consider some syntax along the lines of

\gpset (pset-option-name [pset-option-value]) ... filename

or if you don't like parentheses, choose some other punctuation to wrap
the \pset options in. I initially thought of square brackets, but I'm
afraid they might be just too darn confusing to document --- how could
you make them distinct from metasyntax square brackets, especially in
plain-ASCII docs? Also it'd have to be punctuation that's unlikely to
start a file name --- but parens are already reserved in most shells.

Plan B:

Another idea is to break the operation into multiple backslash commands,
where the initial ones set up state that doesn't do anything until the
output command comes along:

\tpset [ pset-option-name [ pset-option-value ] ]

Sets a "temporary" pset option, which will have effect in the
next \gpset command; or with no parameters, shows the current set
of temporary options

\gpset filename

Execute SQL command and output to filename (or pipe), using the
pset option set defined by preceding \tpset commands, and reverting
that option set to all-defaults afterward.

Probably we could think of better terminology than "temporary"
and a better command name than "\tpset", but you get the gist.

Admittedly, "\tpset format csv \gpset filename" is a bit more
verbose than the current proposal of "\gfmt csv filename"
... but we'd have solved the problem once and for all, even
for pset options we've not invented yet.

Plan C:

Probably there are other ways to get there; these are just the
first ideas that came to me.

regards, tom lane

On 4/1/20 1:53 AM, Tom Lane wrote:

Consider some syntax along the lines of

\gpset (pset-option-name [pset-option-value]) ... filename

or if you don't like parentheses, choose some other punctuation to wrap
the \pset options in. I initially thought of square brackets, but I'm
afraid they might be just too darn confusing to document --- how could
you make them distinct from metasyntax square brackets, especially in
plain-ASCII docs? Also it'd have to be punctuation that's unlikely to
start a file name --- but parens are already reserved in most shells.

If parens are going to be required, why don't we just add them to \g?

TABLE blah \g (format csv) filename
--
Vik Fearing

Vik Fearing <vik@postgresfriends.org> writes:

On 4/1/20 1:53 AM, Tom Lane wrote:

Consider some syntax along the lines of
\gpset (pset-option-name [pset-option-value]) ... filename

If parens are going to be required, why don't we just add them to \g?
TABLE blah \g (format csv) filename

Yeah, if we're willing to assume that nobody uses filenames beginning
with '(', we could just extend \g's syntax rather than adding a new
command.

After sleeping on it, though, I'm liking my Plan B idea better than
Plan A. Plan B is very clearly implementable without needing surgery
on the backslash-command parser (I didn't look at the lexer to see
what paren-handling would involve, but it might be painful). And it
doesn't put any new limits on what pset parameters can look like;
Plan A would likely result in some problems if anybody wants to use
parens in future pset options.

I think that maybe the best terminology for Plan B would be to say
that there's an "alternate" formatting parameter set, which is
manipulated by \apset and then used by \ga.

Another thought, bearing in mind the dictum that the only good numbers
in computer science are 0, 1, and N, is to introduce a concept of named
formatting parameter sets, which you'd manipulate with say
\npset set-name [param-name [param-value]]
and use with
\gn set-name filename-or-command
A likely usage pattern for that would be to set up a few favorite
formats in your ~/.psqlrc, and then they'd be available to just use
immediately in \gn. (In this universe, \gn should not destroy or
reset the parameter set it uses.)

This is way beyond what anyone has asked for, so I'm not seriously
proposing that we do it right now, but it might be something to keep
in mind as a possible future direction. The main thing that it calls
into question is whether we really want \ga to reset the alternate
parameter values after use. Maybe it'd be better not to --- I can
think of about-equally-likely usage patterns where you would want
that or not. We could invent an explicit "\apset reset" command
instead of auto-reset. I could see having a command to copy the
current primary formatting parameters to the alternate area, too.

There's an argument that this is all way too complicated, of course,
and maybe it is. But considering that we've already had two requests
for things you can't do with \gfmt as it stands, I think the patch
is too simple as it is.

regards, tom lane