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>
> Regards
>
> Pavel
>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index a9a2fdb..2cf54bf 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.binary_result = (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..b2e437a 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.binary_result)
+ results = PQexecParams(pset.db, query,
+ 0,
+ NULL,
+ NULL,
+ NULL,
+ NULL,
+ pset.binary_result);
+ 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.binary_result = false;
+
/* reset \crosstabview trigger */
pset.crosstab_flag = false;
for (i = 0; i < lengthof(pset.ctv_args); i++)
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 8cfe9d2..216833e 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 binary_result; /* one-shot flag - enforce binary result format */
bool notty; /* stdin or stdout is not a tty (as determined
* on startup) */
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
