Re: proposal \gcsv

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?

Regards

Pavel

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(at)postgresfriends(dot)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

> -1 on \gcsv
> --
> Vik Fearing
>

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(at)postgresfriends(dot)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(at)postgresfriends(dot)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

> -1 on \gcsv
> --
> Vik Fearing
>

so 29. 2. 2020 v 18:06 odesílatel David Fetter <david(at)fetter(dot)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(at)postgresfriends(dot)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"'

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

On 01/03/2020 13:29, Pavel Stehule wrote:
> so 29. 2. 2020 v 11:34 odesílatel Vik Fearing <vik(at)postgresfriends(dot)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(at)postgresfriends(dot)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

--
> Vik Fearing
>

On 3/26/20 10:49 AM, Pavel Stehule wrote:
> Hi
>
> čt 26. 3. 2020 v 17:45 odesílatel Vik Fearing <vik(at)postgresfriends(dot)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(at)postgresfriends(dot)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(at)postgresfriends(dot)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

--
> Vik Fearing
>

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(at)xs4all(dot)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

>
> Thanks,
>
>
> Erik Rijkers
>
>
>
>
>
>
>
>
>
>
>
>

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(at)manitou-mail(dot)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

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

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!

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

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(at)postgresfriends(dot)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

Tom Lane wrote:

> I could see having a command to copy the current primary formatting
> parameters to the alternate area, too.

We could have a stack to store parameters before temporary
changes, for instance if you want to do one csv export and
come back to normal without assuming what "normal"
values were.

\pset push format csv_fieldsep
\pset format csv
\pset csv_fielsep '\t'
some command \g somefile
\pset pop

So \pset pop would reset the pushed parameters
to their values when pushed, which also could be all
parameters:

\pset push all
\pset param1 something
\pset param2 something-else
...other commands...
\pset pop

or

\pset push all
\i somescript.sql
\pset pop

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

On Wed, 1 Apr 2020 at 11:52, Daniel Verite <daniel(at)manitou-mail(dot)org> wrote:

> Tom Lane wrote:
>
> > I could see having a command to copy the current primary formatting
> > parameters to the alternate area, too.
>
> We could have a stack to store parameters before temporary
> changes, for instance if you want to do one csv export and
> come back to normal without assuming what "normal"
> values were.
>

I think it might be a good idea to decide whether psql is to be a
programming environment, or just a command shell.

If it is to be a programming environment, we should either adopt an
existing language or strike a committee of programming language experts to
design a new one.

If it is to be a command shell, new features should be evaluated in part on
whether they move psql significantly closer to being a programming language
and rejected if they do.

st 1. 4. 2020 v 17:52 odesílatel Daniel Verite <daniel(at)manitou-mail(dot)org>
napsal:

> Tom Lane wrote:
>
> > I could see having a command to copy the current primary formatting
> > parameters to the alternate area, too.
>
> We could have a stack to store parameters before temporary
> changes, for instance if you want to do one csv export and
> come back to normal without assuming what "normal"
> values were.
>
> \pset push format csv_fieldsep
> \pset format csv
> \pset csv_fielsep '\t'
> some command \g somefile
> \pset pop
>
> So \pset pop would reset the pushed parameters
> to their values when pushed, which also could be all
> parameters:
>
> \pset push all
> \pset param1 something
> \pset param2 something-else
> ...other commands...
> \pset pop
>
> or
>
> \pset push all
> \i somescript.sql
> \pset pop
>
>
It can work, but it is not user friendly - my proposal was motivated by
using some quick csv exports to gplot's pipe.

Regards

Pavel

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

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> It can work, but it is not user friendly - my proposal was motivated by
> using some quick csv exports to gplot's pipe.

I kind of liked the stack idea, myself. It's simpler than what I was
suggesting and it covers probably 90% of the use-case.

However, if we prefer something closer to Plan A ... I took a look at
the psql lexer, and the only difference between OT_FILEPIPE and OT_NORMAL
parsing is if the argument starts with '|'. So we could make it work
I think. I'd modify my first proposal so far as to make it

\g ( pset-option pset-value ... ) filename-or-pipe

That is, require spaces around the parens, and require a value for each
pset-option (no fair using the shortcuts like "\pset expanded"). Then
it's easy to separate the option names and values from the paren markers.
The \g parser would consume its first argument in OT_FILEPIPE mode, and
then if it sees '(' it would consume arguments in OT_NORMAL mode until
it's found the ')'.

This way also narrows the backwards-compatibility problem from "fails if
your filename starts with '('" to "fails if your filename is exactly '('",
which seems acceptably improbable to me.

regards, tom lane

On 2020-Apr-01, Pavel Stehule wrote:

> It can work, but it is not user friendly - my proposal was motivated by
> using some quick csv exports to gplot's pipe.

Can we fix that by adding some syntax to allow command aliases?
So you could add to your .psqlrc something like

\alias \gcsv \pset push all \; \cbuf; \; \pset pop

where the \cbuf is a hypothetical "function" that expands to the current
query buffer. This needs some refining I guess, but it'd allow you to
create your own shortcuts for the most common features you want without
excessive typing effort.

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

On 4/1/20 6:29 PM, Tom Lane wrote:
> I'd modify my first proposal so far as to make it
>
> \g ( pset-option pset-value ... ) filename-or-pipe
>
> That is, require spaces around the parens

I think requiring spaces inside the parentheses is a severe POLA
violation and I vote strongly against it.
--
Vik Fearing

Alvaro Herrera wrote:

> Can we fix that by adding some syntax to allow command aliases?
> So you could add to your .psqlrc something like
>
> \alias \gcsv \pset push all \; \cbuf; \; \pset pop
>
> where the \cbuf is a hypothetical "function" that expands to the current
> query buffer. This needs some refining I guess, but it'd allow you to
> create your own shortcuts for the most common features you want without
> excessive typing effort.

Since variables can contain metacommands, they can be abused
as macros. For instance I think a declaration like this would work:

\set gcsv '\\pset push all \\pset format csv \\g \\pset pop'

or with another pset with embedded single quotes:

\set gcsv '\\pset push all \\pset format csv \\pset csv_fieldsep ''\\t'' \\g
\\pset pop'

This kind of usage is not mentioned explicitly in the doc, so it might be
hard to discover, but without the push/pop feature that doesn't exist,
we can already do that:

test=> \set gcsv '\\pset format csv \\pset csv_fieldsep ''\\t'' \\g'

test=> select 1,2 :gcsv | (echo "START OF OUTPUT"; cat)
Output format is csv.
Field separator for CSV is " ".
START OF OUTPUT
?column? ?column?
1 2

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

st 1. 4. 2020 v 18:29 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > It can work, but it is not user friendly - my proposal was motivated by
> > using some quick csv exports to gplot's pipe.
>
> I kind of liked the stack idea, myself. It's simpler than what I was
> suggesting and it covers probably 90% of the use-case.
>

The stack idea probably needs much stronger psql handling error redesign to
be safe

postgres=# \set ON_ERROR_STOP 1
postgres=# select 10/0 \echo 'ahoj' \g \echo 'nazdar
ahoj
ERROR: division by zero

There is not guaranteed so the command for returning to stored state will
be executed.

> However, if we prefer something closer to Plan A ... I took a look at
> the psql lexer, and the only difference between OT_FILEPIPE and OT_NORMAL
> parsing is if the argument starts with '|'. So we could make it work
> I think. I'd modify my first proposal so far as to make it
>
> \g ( pset-option pset-value ... ) filename-or-pipe
>
> That is, require spaces around the parens, and require a value for each
> pset-option (no fair using the shortcuts like "\pset expanded"). Then
> it's easy to separate the option names and values from the paren markers.
> The \g parser would consume its first argument in OT_FILEPIPE mode, and
> then if it sees '(' it would consume arguments in OT_NORMAL mode until
> it's found the ')'.
>

To have this syntax can be nice, but the requirement spaces around
parenthesis is not too user friendly and natural.

Following ideas are based on Tom's ideas

We can have a new commands for cloning print environments and switch to one
shot environment. It can be based just on enhancing of \pset command

\pset save anyidentifier .. serialize settings
\pset load anyidentifier .. load setting
\pset oneshot [anyidentifer] .. prepare and set copy of current print
setting for next execution command
\pset main
\pset reset .. reset to defaults

so this can support some scenarios

-- one shot csv
\pset oneshot -- copy current settings to one shot environment and use one
shot environment
\pset format csv
\pset csv_delimiter ;
select 1; -- any output

-- prepare named configuration
\pset oneshot
\pset format csv
\pset csv_delimiter ;
\pset save czech_csv -- serialize changes against "main" environment
\pset main

\pset load czech_csv
select 1;

or

\pset oneshot czech_csv
select 1;

So we just need to enhance syntax only of \pset command, and we have to
support work with two print settings environments - "main" and "oneshot"

What do you think about this proposal?

Regards

Pavel

> This way also narrows the backwards-compatibility problem from "fails if
> your filename starts with '('" to "fails if your filename is exactly '('",
> which seems acceptably improbable to me.
>
> regards, tom lane
>

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> We can have a new commands for cloning print environments and switch to one
> shot environment. It can be based just on enhancing of \pset command

> \pset save anyidentifier .. serialize settings
> \pset load anyidentifier .. load setting
> \pset oneshot [anyidentifer] .. prepare and set copy of current print
> setting for next execution command
> \pset main
> \pset reset .. reset to defaults

I feel like that's gotten pretty far away from the idea of a simple,
easy-to-use way of adjusting the parameters for one \g operation.
There'd be a whole lot of typing involved above and beyond the
obviously-necessary part of specifying the new pset parameter values.

(Also, it's not clear to me how that's any more robust than the
stack idea. If you could lose "\pset pop" to an error, you could
lose "\pset reset" too.)

If people are upset about the extra whitespace in the paren-style
proposal, we could do without it. The only real problem would be
if there's ever a pset parameter for which a trailing right paren
could be a sensible part of the value. Maybe that's not ever
going to be an issue; or maybe we could provide a quoting mechanism
for weird pset values.

regards, tom lane

so 4. 4. 2020 v 0:24 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > We can have a new commands for cloning print environments and switch to
> one
> > shot environment. It can be based just on enhancing of \pset command
>
> > \pset save anyidentifier .. serialize settings
> > \pset load anyidentifier .. load setting
> > \pset oneshot [anyidentifer] .. prepare and set copy of current print
> > setting for next execution command
> > \pset main
> > \pset reset .. reset to defaults
>
> I feel like that's gotten pretty far away from the idea of a simple,
> easy-to-use way of adjusting the parameters for one \g operation.
> There'd be a whole lot of typing involved above and beyond the
> obviously-necessary part of specifying the new pset parameter values.
>

for my original proposal is important only one command \pset oneshot

so one shot setting can be done by

\pset oneshot
\pset format csv
\pset csv_separator ;
any command that print tuples

this is your plan B, but we we need just enhance only pset command, and all
others can be used without any modifications.

> (Also, it's not clear to me how that's any more robust than the
> stack idea. If you could lose "\pset pop" to an error, you could
> lose "\pset reset" too.)
>

The \pset reset should not to do switch from one shot to usual settings
(this is not necessary,because one shot settings is destroyed after
execution), but my idea is reset to initial psql settings

>
> If people are upset about the extra whitespace in the paren-style
> proposal, we could do without it. The only real problem would be
> if there's ever a pset parameter for which a trailing right paren
> could be a sensible part of the value. Maybe that's not ever
> going to be an issue; or maybe we could provide a quoting mechanism
> for weird pset values.
>

Parametrization in parenthesis is usual pattern (EXPLAIN, COPY, ..) in
Postgres, and for me it most natural syntax.

>
> regards, tom lane
>

Here's a WIP patch for the parenthesized-options route.

I realized that if we make the options be single words in the form
name=value, we can easily handle the shortcut forms with no value.
So that's what this does.

What this does *not* do is offer any solution to the question of
how to put a right paren as the last character of a pset option
value. I don't really see any easy way to handle that, but maybe
we can punt for now.

Also no docs or test cases, but I see no point in putting effort into
that in advance of consensus that this is what we want.

0001 is some save/restore infrastructure that we'd need for pretty
much all of the proposals on the table, and then 0002 improves the
command itself.

regards, tom lane

út 7. 4. 2020 v 2:28 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> Here's a WIP patch for the parenthesized-options route.
>
> I realized that if we make the options be single words in the form
> name=value, we can easily handle the shortcut forms with no value.
> So that's what this does.
>
> What this does *not* do is offer any solution to the question of
> how to put a right paren as the last character of a pset option
> value. I don't really see any easy way to handle that, but maybe
> we can punt for now.
>
> Also no docs or test cases, but I see no point in putting effort into
> that in advance of consensus that this is what we want.
>
> 0001 is some save/restore infrastructure that we'd need for pretty
> much all of the proposals on the table, and then 0002 improves the
> command itself.
>

looks well

just note to syntax

your patch supports syntax

(option1=value option2=value)

It looks little bit inconsistent and unusual

should be better comma separated list?

(option1=value, option2=value)

Regards

Pavel

>
> regards, tom lane
>
>

On Tue, 7 Apr 2020 at 03:30, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> your patch supports syntax
>
> (option1=value option2=value)
>
> It looks little bit inconsistent and unusual
>
>>
It's the same as a connection string. Actually, maybe that's the key to
allowing parentheses, etc. in option values if needed - allow the same
single-quote quoting as in connection strings. Maybe even just call the
same code to do the parsing.

út 7. 4. 2020 v 12:49 odesílatel Isaac Morland <isaac(dot)morland(at)gmail(dot)com>
napsal:

> On Tue, 7 Apr 2020 at 03:30, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>
>> your patch supports syntax
>>
>> (option1=value option2=value)
>>
>> It looks little bit inconsistent and unusual
>>
>>>
> It's the same as a connection string. Actually, maybe that's the key to
> allowing parentheses, etc. in option values if needed - allow the same
> single-quote quoting as in connection strings. Maybe even just call the
> same code to do the parsing.
>

I don't think so connection string syntax should be used there.

Isaac Morland <isaac(dot)morland(at)gmail(dot)com> writes:
> On Tue, 7 Apr 2020 at 03:30, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> your patch supports syntax
>> (option1=value option2=value)
>> It looks little bit inconsistent and unusual

> It's the same as a connection string.

Yeah, I didn't exactly invent that out of nowhere. There are a couple
of reasons not to add commas to the syntax:

* It would make comma be another character that's hard to put into
pset values in this context. And unlike right paren, there's plenty
of reason to think people would wish to do that, eg \g (fieldsep=,) ...

* If we use commas then people would figure the spaces are optional
and would try to write things like \g (expanded,null=NULL) ...
That moves the goalposts quite a bit in terms of the code having
to pick apart strings, and it makes things a lot more ambiguous
than they were before --- notably, now '=' is *also* a character
that you can't readily write in a pset value.

> Actually, maybe that's the key to
> allowing parentheses, etc. in option values if needed - allow the same
> single-quote quoting as in connection strings. Maybe even just call the
> same code to do the parsing.

I don't think there is a lot of wiggle room to let \g have its own quoting
rules. The psqlscanslash lexer has its own ideas about that, which we
cannot bypass without losing features. An example is that people would
expect this to work:
\set myfmt '(expanded tuples_only)'
\g :myfmt somefile
So we can't just ask to snarf the input in OT_WHOLE_LINE mode and then
pick it apart locally in \g. And having two levels of quoting rules
would be disastrous for usability.

The lexer does have the ability to report whether an argument was quoted,
but it doesn't seem to work quite the way we would want here; it actually
reports whether any part of the argument was quoted. So if we tried to
make right paren recognition depend on that, this'd misbehave:
\g (fieldsep='|')

regards, tom lane

út 7. 4. 2020 v 16:28 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> Isaac Morland <isaac(dot)morland(at)gmail(dot)com> writes:
> > On Tue, 7 Apr 2020 at 03:30, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> >> your patch supports syntax
> >> (option1=value option2=value)
> >> It looks little bit inconsistent and unusual
>
> > It's the same as a connection string.
>
> Yeah, I didn't exactly invent that out of nowhere. There are a couple
> of reasons not to add commas to the syntax:
>
> * It would make comma be another character that's hard to put into
> pset values in this context. And unlike right paren, there's plenty
> of reason to think people would wish to do that, eg \g (fieldsep=,) ...
>

ok, this is valid argument

> * If we use commas then people would figure the spaces are optional
> and would try to write things like \g (expanded,null=NULL) ...
> That moves the goalposts quite a bit in terms of the code having
> to pick apart strings, and it makes things a lot more ambiguous
> than they were before --- notably, now '=' is *also* a character
> that you can't readily write in a pset value.
>
> > Actually, maybe that's the key to
> > allowing parentheses, etc. in option values if needed - allow the same
> > single-quote quoting as in connection strings. Maybe even just call the
> > same code to do the parsing.
>
> I don't think there is a lot of wiggle room to let \g have its own quoting
> rules. The psqlscanslash lexer has its own ideas about that, which we
> cannot bypass without losing features. An example is that people would
> expect this to work:
> \set myfmt '(expanded tuples_only)'
> \g :myfmt somefile
> So we can't just ask to snarf the input in OT_WHOLE_LINE mode and then
> pick it apart locally in \g. And having two levels of quoting rules
> would be disastrous for usability.
>
> The lexer does have the ability to report whether an argument was quoted,
> but it doesn't seem to work quite the way we would want here; it actually
> reports whether any part of the argument was quoted. So if we tried to
> make right paren recognition depend on that, this'd misbehave:
> \g (fieldsep='|')
>

ok, I have not any objections.

Regards

Pavel

> regards, tom lane
>

Here's a more fully fleshed-out patch, with documentation and some
test cases. (0001 patch is identical to last time.)

Considering this is the last day before v13 feature freeze, should
I push this, or sit on it till v14? I feel reasonably good that we
have a nice feature definition here, but it's awfully late in the
cycle to be designing features.

regards, tom lane

út 7. 4. 2020 v 19:27 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> Here's a more fully fleshed-out patch, with documentation and some
> test cases. (0001 patch is identical to last time.)
>
> Considering this is the last day before v13 feature freeze, should
> I push this, or sit on it till v14? I feel reasonably good that we
> have a nice feature definition here, but it's awfully late in the
> cycle to be designing features.
>

I am for pushing to v13. This feature should not to break any, and there is
lot of time to finish details.

Regards

Pavel

> regards, tom lane
>
>

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> út 7. 4. 2020 v 19:27 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:
>> Considering this is the last day before v13 feature freeze, should
>> I push this, or sit on it till v14? I feel reasonably good that we
>> have a nice feature definition here, but it's awfully late in the
>> cycle to be designing features.

> I am for pushing to v13. This feature should not to break any, and there is
> lot of time to finish details.

Hearing no objections, pushed.

regards, tom lane

út 7. 4. 2020 v 23:47 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > út 7. 4. 2020 v 19:27 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:
> >> Considering this is the last day before v13 feature freeze, should
> >> I push this, or sit on it till v14? I feel reasonably good that we
> >> have a nice feature definition here, but it's awfully late in the
> >> cycle to be designing features.
>
> > I am for pushing to v13. This feature should not to break any, and there
> is
> > lot of time to finish details.
>
> Hearing no objections, pushed.
>

Thank you

Pavel

> regards, tom lane
>