2016-12-11 18:23 GMT+01:00 Pavel Stehule <[email protected]>:
> Hi
>
> 2016-12-09 18:39 GMT+01:00 Pavel Stehule <[email protected]>:
>
>> Hi
>>
>> Long time I am pushing a COPY RAW - without success.
>>
>> Now I propose functionally similar solution - reduced to only to psql
>> console
>>
>> Now we have a statement \g for execution query, \gset for exec and store
>> result in memory and I propose \gstore for storing result in file and
>> \gstore_binary for storing result in file with binary passing. The query
>> result should be one row, one column.
>>
>> Usage:
>>
>> SELECT image FROM accounts WHERE id = xxx
>> \gstore_binary ~/image.png
>>
>> What do you think about this proposal?
>>
>
> here is a poc patch
>
> Regards
>
> Pavel
>
> Usage:
>
> postgres=# set client_encoding to 'latin2';
> SET
> Time: 1,561 ms
> postgres=# select a from foo
> postgres-# \gbstore ~/doc.xml
> Time: 1,749 ms
>
> content of doc.xml
> <?xml version="1.0" encoding="LATIN2"?><a>příliš žluťoučký kůň se napil
> žluté vody</a>
>
>
second update - + doc
the export import regress tests are little bit heavy - I'll write it for
loading content file together.
Regards
Pavel
>
>> Regards
>>
>> Pavel
>>
>
>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 9915731..7e2fa96 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1944,6 +1944,31 @@ hello 10
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>\gstore [ <replaceable class="parameter">filename</replaceable> ]</literal></term>
+ <term><literal>\gstore [ |<replaceable class="parameter">command</replaceable> ]</literal></term>
+ <term><literal>\gbstore [ <replaceable class="parameter">filename</replaceable> ]</literal></term>
+ <term><literal>\gbstore [ |<replaceable class="parameter">command</replaceable> ]</literal></term>
+ <listitem>
+ <para>
+ Sends the current query input buffer to the server and stores the
+ raw query's output into stores the query's output in <replaceable
+ class="parameter">filename</replaceable> or pipes the output
+ to the shell command <replaceable
+ class="parameter">command</replaceable>. The file or command is
+ written to only if the query successfully returns exactly one row
+ one column non null result, not if the query fails or is a
+ non-data-returning SQL command. For example:
+<programlisting>
+=> <userinput>SELECT avatar FROM users WHERE id = 123</userinput>
+-> <userinput>\gbstore ~/avatar.png</userinput>
+</programlisting>
+ </para>
+ </listitem>
+ </varlistentry>
+
+
<varlistentry>
<term><literal>\h</literal> or <literal>\help</literal> <literal>[ <replaceable class="parameter">command</replaceable> ]</literal></term>
<listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index a9a2fdb..e8fabb9 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -929,6 +929,27 @@ exec_command(const char *cmd,
status = PSQL_CMD_SEND;
}
+ /* \gstore [filename], \gbstore [filename] -- send query and store result in (binary) file */
+ else if (strcmp(cmd, "gstore") == 0 ||
+ (strcmp(cmd, "gbstore") == 0))
+ {
+ char *fname = psql_scan_slash_option(scan_state,
+ OT_FILEPIPE, NULL, false);
+
+ if (!fname)
+ pset.gfname = pg_strdup("");
+ else
+ {
+ expand_tilde(&fname);
+ pset.gfname = pg_strdup(fname);
+ }
+
+ pset.raw_flag = true;
+ pset.binres_flag = (strcmp(cmd, "gbstore") == 0);
+ free(fname);
+ status = PSQL_CMD_SEND;
+ }
+
/* help */
else if (strcmp(cmd, "h") == 0 || strcmp(cmd, "help") == 0)
{
@@ -1064,7 +1085,6 @@ exec_command(const char *cmd,
free(opt2);
}
-
/* \o -- set query output */
else if (strcmp(cmd, "o") == 0 || strcmp(cmd, "out") == 0)
{
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index a7789df..d4b4f15 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -854,6 +854,85 @@ StoreQueryTuple(const PGresult *result)
return success;
}
+/*
+ * StoreRawResult: the returned value (possibly binary) is displayed
+ * or stored in file. The result should be exactly one row, one column.
+ */
+static bool
+StoreRawResult(const PGresult *result)
+{
+ bool success = true;
+
+ if (PQntuples(result) < 1)
+ {
+ psql_error("no rows returned for \\gstore or \\gbstore\n");
+ success = false;
+ }
+ else if (PQntuples(result) > 1)
+ {
+ psql_error("more than one row returned for \\gstore or \\gbstore\n");
+ success = false;
+ }
+ else if (PQnfields(result) < 1)
+ {
+ psql_error("no columns returned for \\gstore or \\gbstore\n");
+ success = false;
+ }
+ else if (PQnfields(result) > 1)
+ {
+ psql_error("more than one column returned for \\gstore or \\gbstore\n");
+ success = false;
+ }
+ else if (PQgetisnull(result, 0, 0))
+ {
+ psql_error("returned value is null for \\gstore or \\gbstore\n");
+ success = false;
+ }
+ else
+ {
+ char *value;
+ int length;
+ FILE *fout = NULL;
+ bool is_pipe = false;
+
+ value = PQgetvalue(result, 0, 0);
+ length = PQgetlength(result, 0, 0);
+
+ if (pset.gfname && *(pset.gfname) != '\0')
+ {
+ if (!openQueryOutputFile(pset.gfname, &fout, &is_pipe))
+ success = false;
+ if (success && is_pipe)
+ disable_sigpipe_trap();
+ }
+
+ if (success)
+ {
+ success = fwrite(value, 1, length, fout != NULL ? fout : pset.queryFout) == length;
+ if (!success)
+ psql_error("%s: %s\n", pset.gfname, strerror(errno));
+
+ if (success)
+ success = fflush(fout != NULL ? fout : pset.queryFout) == 0;
+
+ if (!success)
+ psql_error("%s: %s\n", pset.gfname, strerror(errno));
+
+ if (fout != NULL)
+ {
+ if (is_pipe)
+ {
+ pclose(fout);
+ restore_sigpipe_trap();
+ }
+ else
+ fclose(fout);
+ }
+ }
+ }
+
+ return success;
+}
/*
* ExecQueryTuples: assuming query result is OK, execute each query
@@ -1124,6 +1203,8 @@ PrintQueryResults(PGresult *results)
success = ExecQueryTuples(results);
else if (pset.crosstab_flag)
success = PrintResultsInCrosstab(results);
+ else if (pset.raw_flag)
+ success = StoreRawResult(results);
else
success = PrintQueryTuples(results);
/* if it's INSERT/UPDATE/DELETE RETURNING, also print status */
@@ -1278,7 +1359,8 @@ SendQuery(const char *query)
}
if (pset.fetch_count <= 0 || pset.gexec_flag ||
- pset.crosstab_flag || !is_select_command(query))
+ pset.crosstab_flag || !is_select_command(query) ||
+ pset.raw_flag)
{
/* Default fetch-it-all-and-print mode */
instr_time before,
@@ -1287,7 +1369,16 @@ SendQuery(const char *query)
if (pset.timing)
INSTR_TIME_SET_CURRENT(before);
- results = PQexec(pset.db, query);
+ if (pset.binres_flag)
+ results = PQexecParams(pset.db, query,
+ 0,
+ NULL,
+ NULL,
+ NULL,
+ NULL,
+ pset.binres_flag);
+ else
+ results = PQexec(pset.db, query);
/* these operations are included in the timing result: */
ResetCancelConn();
@@ -1404,7 +1495,7 @@ SendQuery(const char *query)
sendquery_cleanup:
- /* reset \g's output-to-filename trigger */
+ /* reset \g, \g[b]store output-to-filename trigger */
if (pset.gfname)
{
free(pset.gfname);
@@ -1421,6 +1512,10 @@ sendquery_cleanup:
/* reset \gexec trigger */
pset.gexec_flag = false;
+ /* reset \gstore, gbstore trigger */
+ pset.raw_flag = false;
+ pset.binres_flag = false;
+
/* reset \crosstabview trigger */
pset.crosstab_flag = false;
for (i = 0; i < lengthof(pset.ctv_args); i++)
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index a69c4dd..7f337f9 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -168,7 +168,7 @@ slashUsage(unsigned short int pager)
* Use "psql --help=commands | wc" to count correctly. It's okay to count
* the USE_READLINE line even in builds without that.
*/
- output = PageOutput(113, pager ? &(pset.popt.topt) : NULL);
+ output = PageOutput(115, pager ? &(pset.popt.topt) : NULL);
fprintf(output, _("General\n"));
fprintf(output, _(" \\copyright show PostgreSQL usage and distribution terms\n"));
@@ -176,6 +176,8 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\g [FILE] or ; execute query (and send results to file or |pipe)\n"));
fprintf(output, _(" \\gexec execute query, then execute each value in its result\n"));
fprintf(output, _(" \\gset [PREFIX] execute query and store results in psql variables\n"));
+ fprintf(output, _(" \\gstore [FILE] execute query and store result to file or |pipe\n"));
+ fprintf(output, _(" \\gbstore [FILE] execute query and store bin result to file or |pipe\n"));
fprintf(output, _(" \\q quit psql\n"));
fprintf(output, _(" \\crosstabview [COLUMNS] execute query and display results in crosstab\n"));
fprintf(output, _(" \\watch [SEC] execute query every SEC seconds\n"));
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 8cfe9d2..74a99e6 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -95,6 +95,8 @@ typedef struct _psqlSettings
bool gexec_flag; /* one-shot flag to execute query's results */
bool crosstab_flag; /* one-shot request to crosstab results */
char *ctv_args[4]; /* \crosstabview arguments */
+ bool raw_flag; /* one-shot flag to work with exact one value */
+ bool binres_flag; /* one-shot flag - enforce binary result format */
bool notty; /* stdin or stdout is not a tty (as determined
* on startup) */
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index cd64c39..166e3a7 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1336,7 +1336,8 @@ psql_completion(const char *text, int start, int end)
"\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\drds", "\\ds", "\\dS",
"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy",
"\\e", "\\echo", "\\ef", "\\encoding", "\\errverbose", "\\ev",
- "\\f", "\\g", "\\gexec", "\\gset", "\\h", "\\help", "\\H", "\\i", "\\ir", "\\l",
+ "\\f", "\\g", "\\gbstore", "\\gexec", "\\gset", "gstore",
+ "\\h", "\\help", "\\H", "\\i", "\\ir", "\\l",
"\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
"\\o", "\\p", "\\password", "\\prompt", "\\pset", "\\q", "\\qecho", "\\r",
"\\s", "\\set", "\\setenv", "\\sf", "\\sv", "\\t", "\\T",
@@ -3240,8 +3241,8 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
else if (TailMatchesCS1("\\sv*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL);
- else if (TailMatchesCS1("\\cd|\\e|\\edit|\\g|\\i|\\include|"
- "\\ir|\\include_relative|\\o|\\out|"
+ else if (TailMatchesCS1("\\cd|\\e|\\edit|\\g|\\gbstore|\\gstore|"
+ "\\i|\\include|\\ir|\\include_relative|\\o|\\out|"
"\\s|\\w|\\write|\\lo_import"))
{
completion_charp = "\\";
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
