Re: proposal \gcsv
út 7. 4. 2020 v 23:47 odesílatel Tom Lane napsal: > Pavel Stehule writes: > > út 7. 4. 2020 v 19:27 odesílatel Tom Lane 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 >
Re: proposal \gcsv
Pavel Stehule writes: > út 7. 4. 2020 v 19:27 odesílatel Tom Lane 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
Re: proposal \gcsv
út 7. 4. 2020 v 19:27 odesílatel Tom Lane 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 > >
Re: proposal \gcsv
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
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index db31fa8..1055af5 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -160,8 +160,8 @@ static void minimal_error_message(PGresult *res);
static void printSSLInfo(void);
static void printGSSInfo(void);
-static bool printPsetInfo(const char *param, struct printQueryOpt *popt);
-static char *pset_value_string(const char *param, struct printQueryOpt *popt);
+static bool printPsetInfo(const char *param, printQueryOpt *popt);
+static char *pset_value_string(const char *param, printQueryOpt *popt);
#ifdef WIN32
static void checkWin32Codepage(void);
@@ -1302,7 +1302,11 @@ exec_command_g(PsqlScanState scan_state, bool active_branch, const char *cmd)
}
free(fname);
if (strcmp(cmd, "gx") == 0)
- pset.g_expanded = true;
+ {
+ /* save settings, then force expanded = 1 */
+ pset.gsavepopt = savePsetInfo();
+ pset.popt.topt.expanded = 1;
+ }
status = PSQL_CMD_SEND;
}
else
@@ -3785,6 +3789,17 @@ _unicode_linestyle2string(int linestyle)
/*
* do_pset
*
+ * Performs the assignment "param = value", where value could be NULL;
+ * for some params that has an effect such as inversion, for others
+ * it does nothing.
+ *
+ * Adjusts the state of the formatting options at *popt. (In practice that
+ * is always pset.popt, but maybe someday it could be different.)
+ *
+ * If successful and quiet is false, then invokes printPsetInfo() to report
+ * the change.
+ *
+ * Returns true if successful, else false (eg for invalid param or value).
*/
bool
do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
@@ -4109,9 +4124,11 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
return true;
}
-
+/*
+ * printPsetInfo: print the state of the "param" formatting parameter in popt.
+ */
static bool
-printPsetInfo(const char *param, struct printQueryOpt *popt)
+printPsetInfo(const char *param, printQueryOpt *popt)
{
Assert(param != NULL);
@@ -4292,6 +4309,77 @@ printPsetInfo(const char *param, struct printQueryOpt *popt)
return true;
}
+/*
+ * savePsetInfo: make a malloc'd copy of the data in *popt.
+ *
+ * Possibly this should be somewhere else, but it's a bit specific to psql.
+ */
+printQueryOpt *
+savePsetInfo(const printQueryOpt *popt)
+{
+ printQueryOpt *save;
+
+ save = (printQueryOpt *) pg_malloc(sizeof(printQueryOpt));
+
+ /* Flat-copy all the scalar fields, then duplicate sub-structures. */
+ memcpy(save, popt, sizeof(printQueryOpt));
+
+ /* topt.line_style points to const data that need not be duplicated */
+ if (popt->topt.fieldSep.separator)
+ save->topt.fieldSep.separator = pg_strdup(popt->topt.fieldSep.separator);
+ if (popt->topt.recordSep.separator)
+ save->topt.recordSep.separator = pg_strdup(popt->topt.recordSep.separator);
+ if (popt->topt.tableAttr)
+ save->topt.tableAttr = pg_strdup(popt->topt.tableAttr);
+ if (popt->nullPrint)
+ save->nullPrint = pg_strdup(popt->nullPrint);
+ if (popt->title)
+ save->title = pg_strdup(popt->title);
+
+ /*
+ * footers and translate_columns are never set in psql's print settings,
+ * so we needn't write code to duplicate them.
+ */
+ Assert(popt->footers == NULL);
+ Assert(popt->translate_columns == NULL);
+
+ return save;
+}
+
+/*
+ * restorePsetInfo: restore *popt from the previously-saved copy *save,
+ * then free *save.
+ */
+void
+restorePsetInfo(printQueryOpt *popt, printQueryOpt *save)
+{
+ /* Free all the old data we're about to overwrite the pointers to. */
+
+ /* topt.line_style points to const data that need not be duplicated */
+ if (popt->topt.fieldSep.separator)
+ free(popt->topt.fieldSep.separator);
+ if (popt->topt.recordSep.separator)
+ free(popt->topt.recordSep.separator);
+ if (popt->topt.tableAttr)
+ free(popt->topt.tableAttr);
+ if (popt->nullPrint)
+ free(popt->nullPrint);
+ if (popt->title)
+ free(popt->title);
+
+ /*
+ * footers and translate_columns are never set in psql's print settings,
+ * so we needn't write code to duplicate them.
+ */
+ Assert(popt->footers == NULL);
+ Assert(popt->translate_columns == NULL);
+
+ /* Now we may flat-copy all the fields, including pointers. */
+ memcpy(popt, save, sizeof(printQueryOpt));
+
+ /* Lastly, free "save" ... but its sub-structures now belong to popt. */
+ free(save);
+}
static const char *
pset_bool_string(bool val)
@@ -4339,7 +4427,7 @@ pset_quoted_string(const char *str)
* output that produces the correct setting when fed back into \pset.
*/
static char *
-pset_value_string(const char *param, struct
Re: proposal \gcsv
út 7. 4. 2020 v 16:28 odesílatel Tom Lane napsal: > Isaac Morland writes: > > On Tue, 7 Apr 2020 at 03:30, Pavel Stehule > 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 >
Re: proposal \gcsv
Isaac Morland writes: > On Tue, 7 Apr 2020 at 03:30, Pavel Stehule 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
Re: proposal \gcsv
út 7. 4. 2020 v 12:49 odesílatel Isaac Morland napsal: > On Tue, 7 Apr 2020 at 03:30, Pavel Stehule > 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.
Re: proposal \gcsv
On Tue, 7 Apr 2020 at 03:30, Pavel Stehule 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.
Re: proposal \gcsv
út 7. 4. 2020 v 2:28 odesílatel Tom Lane 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 > >
Re: proposal \gcsv
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
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index db31fa8..1055af5 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -160,8 +160,8 @@ static void minimal_error_message(PGresult *res);
static void printSSLInfo(void);
static void printGSSInfo(void);
-static bool printPsetInfo(const char *param, struct printQueryOpt *popt);
-static char *pset_value_string(const char *param, struct printQueryOpt *popt);
+static bool printPsetInfo(const char *param, printQueryOpt *popt);
+static char *pset_value_string(const char *param, printQueryOpt *popt);
#ifdef WIN32
static void checkWin32Codepage(void);
@@ -1302,7 +1302,11 @@ exec_command_g(PsqlScanState scan_state, bool active_branch, const char *cmd)
}
free(fname);
if (strcmp(cmd, "gx") == 0)
- pset.g_expanded = true;
+ {
+ /* save settings, then force expanded = 1 */
+ pset.gsavepopt = savePsetInfo();
+ pset.popt.topt.expanded = 1;
+ }
status = PSQL_CMD_SEND;
}
else
@@ -3785,6 +3789,17 @@ _unicode_linestyle2string(int linestyle)
/*
* do_pset
*
+ * Performs the assignment "param = value", where value could be NULL;
+ * for some params that has an effect such as inversion, for others
+ * it does nothing.
+ *
+ * Adjusts the state of the formatting options at *popt. (In practice that
+ * is always pset.popt, but maybe someday it could be different.)
+ *
+ * If successful and quiet is false, then invokes printPsetInfo() to report
+ * the change.
+ *
+ * Returns true if successful, else false (eg for invalid param or value).
*/
bool
do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
@@ -4109,9 +4124,11 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
return true;
}
-
+/*
+ * printPsetInfo: print the state of the "param" formatting parameter in popt.
+ */
static bool
-printPsetInfo(const char *param, struct printQueryOpt *popt)
+printPsetInfo(const char *param, printQueryOpt *popt)
{
Assert(param != NULL);
@@ -4292,6 +4309,77 @@ printPsetInfo(const char *param, struct printQueryOpt *popt)
return true;
}
+/*
+ * savePsetInfo: make a malloc'd copy of the data in *popt.
+ *
+ * Possibly this should be somewhere else, but it's a bit specific to psql.
+ */
+printQueryOpt *
+savePsetInfo(const printQueryOpt *popt)
+{
+ printQueryOpt *save;
+
+ save = (printQueryOpt *) pg_malloc(sizeof(printQueryOpt));
+
+ /* Flat-copy all the scalar fields, then duplicate sub-structures. */
+ memcpy(save, popt, sizeof(printQueryOpt));
+
+ /* topt.line_style points to const data that need not be duplicated */
+ if (popt->topt.fieldSep.separator)
+ save->topt.fieldSep.separator = pg_strdup(popt->topt.fieldSep.separator);
+ if (popt->topt.recordSep.separator)
+ save->topt.recordSep.separator = pg_strdup(popt->topt.recordSep.separator);
+ if (popt->topt.tableAttr)
+ save->topt.tableAttr = pg_strdup(popt->topt.tableAttr);
+ if (popt->nullPrint)
+ save->nullPrint = pg_strdup(popt->nullPrint);
+ if (popt->title)
+ save->title = pg_strdup(popt->title);
+
+ /*
+ * footers and translate_columns are never set in psql's print settings,
+ * so we needn't write code to duplicate them.
+ */
+ Assert(popt->footers == NULL);
+ Assert(popt->translate_columns == NULL);
+
+ return save;
+}
+
+/*
+ * restorePsetInfo: restore *popt from the previously-saved copy *save,
+ * then free *save.
+ */
+void
+restorePsetInfo(printQueryOpt *popt, printQueryOpt *save)
+{
+ /* Free all the old data we're about to overwrite the pointers to. */
+
+ /* topt.line_style points to const data that need not be duplicated */
+ if (popt->topt.fieldSep.separator)
+ free(popt->topt.fieldSep.separator);
+ if (popt->topt.recordSep.separator)
+ free(popt->topt.recordSep.separator);
+ if (popt->topt.tableAttr)
+ free(popt->topt.tableAttr);
+ if (popt->nullPrint)
+ free(popt->nullPrint);
+ if (popt->title)
+ free(popt->title);
+
+ /*
+ * footers and translate_columns are never set in psql's print settings,
+ * so we needn't write code to duplicate them.
+ */
+ Assert(popt->footers == NULL);
+ Assert(popt->translate_columns == NULL);
+
+ /* Now we may flat-copy all the fields, including pointers. */
+ memcpy(popt, save,
Re: proposal \gcsv
so 4. 4. 2020 v 0:24 odesílatel Tom Lane napsal: > Pavel Stehule 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 >
Re: proposal \gcsv
Pavel Stehule 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
Re: proposal \gcsv
st 1. 4. 2020 v 18:29 odesílatel Tom Lane napsal:
> Pavel Stehule 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
>
Re: proposal \gcsv
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
Re: proposal \gcsv
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
Re: proposal \gcsv
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 Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: proposal \gcsv
Pavel Stehule 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
Re: proposal \gcsv
st 1. 4. 2020 v 17:52 odesílatel Daniel Verite 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 >
Re: proposal \gcsv
On Wed, 1 Apr 2020 at 11:52, Daniel Verite 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.
Re: proposal \gcsv
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
Re: proposal \gcsv
Vik Fearing 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
Re: proposal \gcsv
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
Re: proposal \gcsv
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
Re: proposal \gcsv
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
Re: proposal \gcsv
so 28. 3. 2020 v 15:06 odesílatel Daniel Verite 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 >
Re: proposal \gcsv
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
Re: proposal \gcsv
čt 26. 3. 2020 v 19:41 odesílatel Erik Rijkers 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 > > > > > > > > > > > >
Re: proposal \gcsv
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
Re: proposal \gcsv
čt 26. 3. 2020 v 18:55 odesílatel Vik Fearing napsal: > On 3/26/20 10:49 AM, Pavel Stehule wrote: > > Hi > > > > čt 26. 3. 2020 v 17:45 odesílatel Vik Fearing > > 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 >
Re: proposal \gcsv
On 3/26/20 10:49 AM, Pavel Stehule wrote: > Hi > > čt 26. 3. 2020 v 17:45 odesílatel Vik Fearing > 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
Re: proposal \gcsv
Hi
čt 26. 3. 2020 v 17:45 odesílatel Vik Fearing
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
>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 402c4b1b26..ba480c7bb6 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2199,6 +2199,16 @@ CREATE INDEX
+
+\gfmt format [ filename ]
+\gfmt format [ |command ]
+
+
+\gfmt is equivalent to \g, but
+forces specified format. See \pset format.
+
+
+
\gset [ prefix ]
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index abb18a19c2..d724a5eb16 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -163,6 +163,8 @@ static void printGSSInfo(void);
static bool printPsetInfo(const char *param, struct printQueryOpt *popt);
static char *pset_value_string(const char *param, struct printQueryOpt *popt);
+static bool format_number(const char *value, int vallen, enum printFormat *numformat);
+
#ifdef WIN32
static void checkWin32Codepage(void);
#endif
@@ -333,7 +335,8 @@ exec_command(const char *cmd,
status = exec_command_errverbose(scan_state, active_branch);
else if (strcmp(cmd, "f") == 0)
status = exec_command_f(scan_state, active_branch);
- else if (strcmp(cmd, "g") == 0 || strcmp(cmd, "gx") == 0)
+ else if (strcmp(cmd, "g") == 0 || strcmp(cmd, "gx") == 0 ||
+ strcmp(cmd, "gfmt") == 0)
status = exec_command_g(scan_state, active_branch, cmd);
else if (strcmp(cmd, "gdesc") == 0)
status = exec_command_gdesc(scan_state, active_branch);
@@ -1282,6 +1285,8 @@ exec_command_f(PsqlScanState scan_state, bool active_branch)
/*
* \g [filename] -- send query, optionally with output to file/pipe
* \gx [filename] -- same as \g, with expanded mode forced
+ * \gfmt format [filename] -- send result in specified format to
+ * file/pipe.
*/
static backslashResult
exec_command_g(PsqlScanState scan_state, bool active_branch, const char *cmd)
@@ -1290,7 +1295,42 @@ exec_command_g(PsqlScanState scan_state, bool active_branch, const char *cmd)
if (active_branch)
{
- char *fname = psql_scan_slash_option(scan_state,
+ char *fname;
+
+ if (strcmp(cmd, "gfmt") == 0)
+ {
+ char *fmtname = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, false);
+
+ if (!fmtname)
+ {
+pg_log_error("no format name");
+
+return PSQL_CMD_ERROR;
+ }
+ else
+ {
+enum printFormat format;
+bool result;
+
+result = format_number(fmtname, strlen(fmtname), );
+
+free(fmtname);
+
+if (result)
+{
+ pset.gfmt_format = format;
+}
+else
+{
+ pg_log_error("\\pset: allowed formats are aligned, asciidoc, csv, html, latex, latex-longtable, troff-ms, unaligned, wrapped");
+
+ return PSQL_CMD_ERROR;
+}
+ }
+ }
+
+ fname = psql_scan_slash_option(scan_state,
OT_FILEPIPE, NULL, false);
if (!fname)
@@ -3777,6 +3817,68 @@ _unicode_linestyle2string(int linestyle)
return "unknown";
}
+/*
+ * Returns true if format name was recognized.
+ */
+static bool
+format_number(const char *value, int vallen, enum printFormat *numformat)
+{
+ static const struct fmt
+ {
+ const char *name;
+ enum printFormat number;
+ } formats[] =
+ {
+ /* remember to update error message below when adding more */
+ {"aligned", PRINT_ALIGNED},
+ {"asciidoc", PRINT_ASCIIDOC},
+ {"csv", PRINT_CSV},
+ {"html", PRINT_HTML},
+
Re: proposal \gcsv
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
Re: proposal \gcsv
Hi
rebase
Regards
Pavel
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 402c4b1b26..429e0d5708 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2199,6 +2199,16 @@ CREATE INDEX
+
+\gf format [ filename ]
+\gf format [ |command ]
+
+
+\gf is equivalent to \g, but
+forces specified format. See \pset format.
+
+
+
\gset [ prefix ]
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index abb18a19c2..5b460fb656 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -163,6 +163,8 @@ static void printGSSInfo(void);
static bool printPsetInfo(const char *param, struct printQueryOpt *popt);
static char *pset_value_string(const char *param, struct printQueryOpt *popt);
+static bool format_number(const char *value, int vallen, enum printFormat *numformat);
+
#ifdef WIN32
static void checkWin32Codepage(void);
#endif
@@ -333,7 +335,8 @@ exec_command(const char *cmd,
status = exec_command_errverbose(scan_state, active_branch);
else if (strcmp(cmd, "f") == 0)
status = exec_command_f(scan_state, active_branch);
- else if (strcmp(cmd, "g") == 0 || strcmp(cmd, "gx") == 0)
+ else if (strcmp(cmd, "g") == 0 || strcmp(cmd, "gx") == 0 ||
+ strcmp(cmd, "gf") == 0)
status = exec_command_g(scan_state, active_branch, cmd);
else if (strcmp(cmd, "gdesc") == 0)
status = exec_command_gdesc(scan_state, active_branch);
@@ -1290,7 +1293,42 @@ exec_command_g(PsqlScanState scan_state, bool active_branch, const char *cmd)
if (active_branch)
{
- char *fname = psql_scan_slash_option(scan_state,
+ char *fname;
+
+ if (strcmp(cmd, "gf") == 0)
+ {
+ char *fmtname = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, false);
+
+ if (!fmtname)
+ {
+pg_log_error("no format name");
+
+return PSQL_CMD_ERROR;
+ }
+ else
+ {
+enum printFormat format;
+bool result;
+
+result = format_number(fmtname, strlen(fmtname), );
+
+free(fmtname);
+
+if (result)
+{
+ pset.gf_format = format;
+}
+else
+{
+ pg_log_error("\\pset: allowed formats are aligned, asciidoc, csv, html, latex, latex-longtable, troff-ms, unaligned, wrapped");
+
+ return PSQL_CMD_ERROR;
+}
+ }
+ }
+
+ fname = psql_scan_slash_option(scan_state,
OT_FILEPIPE, NULL, false);
if (!fname)
@@ -3777,6 +3815,68 @@ _unicode_linestyle2string(int linestyle)
return "unknown";
}
+/*
+ * Returns true if format name was recognized.
+ */
+static bool
+format_number(const char *value, int vallen, enum printFormat *numformat)
+{
+ static const struct fmt
+ {
+ const char *name;
+ enum printFormat number;
+ } formats[] =
+ {
+ /* remember to update error message below when adding more */
+ {"aligned", PRINT_ALIGNED},
+ {"asciidoc", PRINT_ASCIIDOC},
+ {"csv", PRINT_CSV},
+ {"html", PRINT_HTML},
+ {"latex", PRINT_LATEX},
+ {"troff-ms", PRINT_TROFF_MS},
+ {"unaligned", PRINT_UNALIGNED},
+ {"wrapped", PRINT_WRAPPED}
+ };
+
+ int match_pos = -1;
+
+ for (int i = 0; i < lengthof(formats); i++)
+ {
+ if (pg_strncasecmp(formats[i].name, value, vallen) == 0)
+ {
+ if (match_pos < 0)
+match_pos = i;
+ else
+ {
+pg_log_error("\\pset: ambiguous abbreviation \"%s\" matches both \"%s\" and \"%s\"",
+ value,
+ formats[match_pos].name, formats[i].name);
+return false;
+ }
+ }
+ }
+ if (match_pos >= 0)
+ {
+ *numformat = formats[match_pos].number;
+
+ return true;
+ }
+ else if (pg_strncasecmp("latex-longtable", value, vallen) == 0)
+ {
+ /*
+ * We must treat latex-longtable specially because latex is a
+ * prefix of it; if both were in the table above, we'd think
+ * "latex" is ambiguous.
+ */
+ *numformat = PRINT_LATEX_LONGTABLE;
+
+ return true;
+ }
+
+ return false;
+}
+
+
/*
* do_pset
*
@@ -3794,55 +3894,14 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
/* set format */
if (strcmp(param, "format") == 0)
{
- static const struct fmt
- {
- const char *name;
- enum printFormat number;
- } formats[] =
- {
- /* remember to update error message below when adding more */
- {"aligned", PRINT_ALIGNED},
- {"asciidoc", PRINT_ASCIIDOC},
- {"csv", PRINT_CSV},
- {"html", PRINT_HTML},
- {"latex", PRINT_LATEX},
- {"troff-ms", PRINT_TROFF_MS},
- {"unaligned", PRINT_UNALIGNED},
- {"wrapped", PRINT_WRAPPED}
- };
+ enum printFormat number;
if (!value)
;
else
{
- int match_pos = -1;
-
- for (int i = 0; i < lengthof(formats); i++)
- {
-if (pg_strncasecmp(formats[i].name, value, vallen) == 0)
-{
- if (match_pos < 0)
- match_pos = i;
- else
- {
- pg_log_error("\\pset: ambiguous abbreviation \"%s\" matches both \"%s\" and \"%s\"",
- value,
- formats[match_pos].name,
Re: proposal \gcsv
On 01/03/2020 13:29, Pavel Stehule wrote: > so 29. 2. 2020 v 11:34 odesílatel Vik Fearing > 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
Re: proposal \gcsv
so 29. 2. 2020 v 18:06 odesílatel David Fetter 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 > > 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 http://fetter.org/ > Phone: +1 415 235 3778 > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate >
Re: proposal \gcsv
so 29. 2. 2020 v 11:34 odesílatel Vik Fearing
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
>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 20ba105160..734b3e94e0 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2108,6 +2108,16 @@ CREATE INDEX
+
+\gf format [ filename ]
+\gf format [ |command ]
+
+
+\gf is equivalent to \g, but
+forces specified format. See \pset format.
+
+
+
\gset [ prefix ]
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index e111cee556..fe93313964 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -163,6 +163,8 @@ static void printGSSInfo(void);
static bool printPsetInfo(const char *param, struct printQueryOpt *popt);
static char *pset_value_string(const char *param, struct printQueryOpt *popt);
+static bool format_number(const char *value, int vallen, enum printFormat *numformat);
+
#ifdef WIN32
static void checkWin32Codepage(void);
#endif
@@ -333,7 +335,8 @@ exec_command(const char *cmd,
status = exec_command_errverbose(scan_state, active_branch);
else if (strcmp(cmd, "f") == 0)
status = exec_command_f(scan_state, active_branch);
- else if (strcmp(cmd, "g") == 0 || strcmp(cmd, "gx") == 0)
+ else if (strcmp(cmd, "g") == 0 || strcmp(cmd, "gx") == 0 ||
+ strcmp(cmd, "gf") == 0)
status = exec_command_g(scan_state, active_branch, cmd);
else if (strcmp(cmd, "gdesc") == 0)
status = exec_command_gdesc(scan_state, active_branch);
@@ -1259,7 +1262,42 @@ exec_command_g(PsqlScanState scan_state, bool active_branch, const char *cmd)
if (active_branch)
{
- char *fname = psql_scan_slash_option(scan_state,
+ char *fname;
+
+ if (strcmp(cmd, "gf") == 0)
+ {
+ char *fmtname = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, false);
+
+ if (!fmtname)
+ {
+pg_log_error("no format name");
+
+return PSQL_CMD_ERROR;
+ }
+ else
+ {
+enum printFormat format;
+bool result;
+
+result = format_number(fmtname, strlen(fmtname), );
+
+free(fmtname);
+
+if (result)
+{
+ pset.gf_format = format;
+}
+else
+{
+ pg_log_error("\\pset: allowed formats are aligned, asciidoc, csv, html, latex, latex-longtable, troff-ms, unaligned, wrapped");
+
+ return PSQL_CMD_ERROR;
+}
+ }
+ }
+
+ fname = psql_scan_slash_option(scan_state,
OT_FILEPIPE, NULL, false);
if (!fname)
@@ -3746,6 +3784,68 @@ _unicode_linestyle2string(int linestyle)
return "unknown";
}
+/*
+ * Returns true if format name was recognized.
+ */
+static bool
+format_number(const char *value, int vallen, enum printFormat *numformat)
+{
+ static const struct fmt
+ {
+ const char *name;
+ enum printFormat number;
+ } formats[] =
+ {
+ /* remember to update error message below when adding more */
+ {"aligned", PRINT_ALIGNED},
+ {"asciidoc", PRINT_ASCIIDOC},
+ {"csv", PRINT_CSV},
+ {"html", PRINT_HTML},
+ {"latex", PRINT_LATEX},
+ {"troff-ms", PRINT_TROFF_MS},
+ {"unaligned", PRINT_UNALIGNED},
+ {"wrapped", PRINT_WRAPPED}
+ };
+
+ int match_pos = -1;
+
+ for (int i = 0; i < lengthof(formats); i++)
+ {
+ if (pg_strncasecmp(formats[i].name, value, vallen) == 0)
+ {
+ if (match_pos < 0)
+match_pos = i;
+ else
+ {
+pg_log_error("\\pset: ambiguous abbreviation \"%s\" matches both \"%s\" and \"%s\"",
+ value,
+ formats[match_pos].name, formats[i].name);
+return false;
+ }
+ }
+ }
+ if (match_pos >= 0)
+ {
+ *numformat = formats[match_pos].number;
+
+ return true;
+ }
+ else if (pg_strncasecmp("latex-longtable", value, vallen) == 0)
+ {
+ /*
+ * We must treat latex-longtable specially because latex is a
+ * prefix of it; if both were in the table above, we'd think
+ * "latex" is ambiguous.
+ */
+ *numformat = PRINT_LATEX_LONGTABLE;
+
+ return true;
+ }
+
+ return false;
+}
+
+
/*
* do_pset
*
@@ -3763,55 +3863,14 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
/* set format */
if (strcmp(param, "format") == 0)
{
- static const struct fmt
- {
- const char *name;
- enum printFormat number;
- } formats[] =
- {
- /* remember to update error message below when adding more */
- {"aligned", PRINT_ALIGNED},
- {"asciidoc", PRINT_ASCIIDOC},
-
Re: proposal \gcsv
On Sat, Feb 29, 2020 at 11:59:22AM +0100, Pavel Stehule wrote: > so 29. 2. 2020 v 11:34 odesílatel Vik Fearing > 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 http://fetter.org/ Phone: +1 415 235 3778 Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Re: proposal \gcsv
so 29. 2. 2020 v 11:34 odesílatel Vik Fearing 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 >
Re: 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
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
