I whipped this up some months ago and forgot that I hadn't sent in the
patch.
This implements two psql commands: \gb and \gbn
Both fetch and output results in binary mode - \gb uses separators,
while \gbn does not. Examples:
[andrew@emma inst.psql-binout.5705]$ echo "select bytea '\\x00010203', bytea
'\\x040506' \\gbn" | bin/psql | od -c
0000000 \0 001 002 003 004 005 006
0000007
[andrew@emma inst.psql-binout.5705]$ echo "select bytea '\\x00010203', bytea
'\\x040506' \\gb" | bin/psql | od -c
0000000 \0 001 002 003 | 004 005 006 \n
0000011
This is an attempt to deal with the question I originally posed here:
<http://people.planetpostgresql.org/andrew/index.php?/archives/196-Clever-trick-challenge.html>
and is based on a suggestion Tom later made (although anything wrong
here is of course my fault, not his.
If people are interested I'll try to finish this up and document it.
cheers
andrew
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 59f8b03..434b1d6 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -748,6 +748,40 @@ exec_command(const char *cmd,
status = PSQL_CMD_SEND;
}
+ /* \gb means send query, get/produce results in binary */
+ else if (strcmp(cmd, "gb") == 0)
+ {
+ char *fname = psql_scan_slash_option(scan_state,
+ OT_FILEPIPE, NULL, false);
+
+ if (!fname)
+ pset.gfname = NULL;
+ else
+ {
+ expand_tilde(&fname);
+ pset.gfname = pg_strdup(fname);
+ }
+ free(fname);
+ status = PSQL_CMD_SEND_BIN;
+ }
+
+ /* \gbn means send query, get/produce results in binary, and no separators */
+ else if (strcmp(cmd, "gbn") == 0)
+ {
+ char *fname = psql_scan_slash_option(scan_state,
+ OT_FILEPIPE, NULL, false);
+
+ if (!fname)
+ pset.gfname = NULL;
+ else
+ {
+ expand_tilde(&fname);
+ pset.gfname = pg_strdup(fname);
+ }
+ free(fname);
+ status = PSQL_CMD_SEND_BIN_NS;
+ }
+
/* help */
else if (strcmp(cmd, "h") == 0 || strcmp(cmd, "help") == 0)
{
diff --git a/src/bin/psql/command.h b/src/bin/psql/command.h
index 1b94e44..09a461b 100644
--- a/src/bin/psql/command.h
+++ b/src/bin/psql/command.h
@@ -16,6 +16,8 @@ typedef enum _backslashResult
{
PSQL_CMD_UNKNOWN = 0, /* not done parsing yet (internal only) */
PSQL_CMD_SEND, /* query complete; send off */
+ PSQL_CMD_SEND_BIN, /* same, but get/produce result in binary */
+ PSQL_CMD_SEND_BIN_NS, /* same, but with no separators*/
PSQL_CMD_SKIP_LINE, /* keep building query */
PSQL_CMD_TERMINATE, /* quit program */
PSQL_CMD_NEWEDIT, /* query buffer was changed (e.g., via \e) */
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 5fb0316..590d268 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -579,7 +579,9 @@ PrintNotifications(void)
* Returns true if successful, false otherwise.
*/
static bool
-PrintQueryTuples(const PGresult *results)
+PrintQueryTuples(const PGresult *results,
+ bool binary,
+ bool noseps)
{
printQueryOpt my_popt = pset.popt;
@@ -600,7 +602,10 @@ PrintQueryTuples(const PGresult *results)
return false;
}
- printQuery(results, &my_popt, pset.queryFout, pset.logfile);
+ if (!binary)
+ printQuery(results, &my_popt, pset.queryFout, pset.logfile);
+ else
+ printQueryBin(results, &my_popt, pset.queryFout, pset.logfile, noseps);
/* close file/pipe, restore old setting */
setQFout(NULL);
@@ -612,7 +617,12 @@ PrintQueryTuples(const PGresult *results)
pset.gfname = NULL;
}
else
- printQuery(results, &my_popt, pset.queryFout, pset.logfile);
+ {
+ if (!binary)
+ printQuery(results, &my_popt, pset.queryFout, pset.logfile);
+ else
+ printQueryBin(results, &my_popt, pset.queryFout, pset.logfile, noseps);
+ }
return true;
}
@@ -762,7 +772,9 @@ PrintQueryStatus(PGresult *results)
* Returns true if the query executed successfully, false otherwise.
*/
static bool
-PrintQueryResults(PGresult *results)
+PrintQueryResults(PGresult *results,
+ bool binary,
+ bool noseps)
{
bool success;
const char *cmdstatus;
@@ -774,7 +786,7 @@ PrintQueryResults(PGresult *results)
{
case PGRES_TUPLES_OK:
/* print the data ... */
- success = PrintQueryTuples(results);
+ success = PrintQueryTuples(results, binary, noseps);
/* if it's INSERT/UPDATE/DELETE RETURNING, also print status */
cmdstatus = PQcmdStatus(results);
if (strncmp(cmdstatus, "INSERT", 6) == 0 ||
@@ -830,7 +842,9 @@ PrintQueryResults(PGresult *results)
* Returns true if the query executed successfully, false otherwise.
*/
bool
-SendQuery(const char *query)
+SendQuery(const char *query,
+ bool binary,
+ bool noseps)
{
PGresult *results;
PGTransactionStatusType transaction_status;
@@ -928,7 +942,10 @@ SendQuery(const char *query)
if (pset.timing)
INSTR_TIME_SET_CURRENT(before);
- results = PQexec(pset.db, query);
+ if (! binary)
+ results = PQexec(pset.db, query);
+ else
+ results = PQexecParams(pset.db, query,0, NULL, NULL, NULL, NULL, 1);
/* these operations are included in the timing result: */
ResetCancelConn();
@@ -943,7 +960,7 @@ SendQuery(const char *query)
/* but printing results isn't: */
if (OK && results)
- OK = PrintQueryResults(results);
+ OK = PrintQueryResults(results, binary, noseps);
}
else
{
diff --git a/src/bin/psql/common.h b/src/bin/psql/common.h
index f9f3b1f..0fb9ccc 100644
--- a/src/bin/psql/common.h
+++ b/src/bin/psql/common.h
@@ -47,7 +47,7 @@ extern void ResetCancelConn(void);
extern PGresult *PSQLexec(const char *query, bool start_xact);
-extern bool SendQuery(const char *query);
+extern bool SendQuery(const char *query, bool binary, bool noseps);
extern bool is_superuser(void);
extern bool standard_strings(void);
diff --git a/src/bin/psql/copy.c b/src/bin/psql/copy.c
index a31d789..05e5b1f 100644
--- a/src/bin/psql/copy.c
+++ b/src/bin/psql/copy.c
@@ -316,7 +316,7 @@ do_copy(const char *args)
/* Run it like a user command, interposing the data source or sink. */
save_file = *override_file;
*override_file = copystream;
- success = SendQuery(query.data);
+ success = SendQuery(query.data, false, false);
*override_file = save_file;
termPQExpBuffer(&query);
diff --git a/src/bin/psql/mainloop.c b/src/bin/psql/mainloop.c
index 6e31d5c..92a1ee1 100644
--- a/src/bin/psql/mainloop.c
+++ b/src/bin/psql/mainloop.c
@@ -254,7 +254,7 @@ MainLoop(FILE *source)
}
/* execute query */
- success = SendQuery(query_buf->data);
+ success = SendQuery(query_buf->data, false, false);
slashCmdStatus = success ? PSQL_CMD_SEND : PSQL_CMD_ERROR;
/* transfer query to previous_buf by pointer-swapping */
@@ -304,16 +304,20 @@ MainLoop(FILE *source)
success = slashCmdStatus != PSQL_CMD_ERROR;
- if ((slashCmdStatus == PSQL_CMD_SEND || slashCmdStatus == PSQL_CMD_NEWEDIT) &&
+ if ((slashCmdStatus == PSQL_CMD_SEND || slashCmdStatus == PSQL_CMD_SEND_BIN ||
+ slashCmdStatus == PSQL_CMD_SEND_BIN_NS || slashCmdStatus == PSQL_CMD_NEWEDIT) &&
query_buf->len == 0)
{
/* copy previous buffer to current for handling */
appendPQExpBufferStr(query_buf, previous_buf->data);
}
- if (slashCmdStatus == PSQL_CMD_SEND)
+ if (slashCmdStatus == PSQL_CMD_SEND || slashCmdStatus == PSQL_CMD_SEND_BIN || slashCmdStatus == PSQL_CMD_SEND_BIN_NS)
{
- success = SendQuery(query_buf->data);
+ bool usebin = slashCmdStatus != PSQL_CMD_SEND;
+ bool noseps = slashCmdStatus == PSQL_CMD_SEND_BIN_NS;
+
+ success = SendQuery(query_buf->data, usebin, noseps);
/* transfer query to previous_buf by pointer-swapping */
{
@@ -384,7 +388,7 @@ MainLoop(FILE *source)
pg_send_history(history_buf);
/* execute query */
- success = SendQuery(query_buf->data);
+ success = SendQuery(query_buf->data, false, false);
if (!success && die_on_error)
successResult = EXIT_USER;
diff --git a/src/bin/psql/print.c b/src/bin/psql/print.c
index 466c255..e0c75bb 100644
--- a/src/bin/psql/print.c
+++ b/src/bin/psql/print.c
@@ -2411,6 +2411,53 @@ printTable(const printTableContent *cont, FILE *fout, FILE *flog)
}
/*
+ * Use this to print query results when results are produced in binary
+ * rather than text.
+ *
+ */
+void
+printQueryBin(const PGresult *result, const printQueryOpt *opt, FILE *fout, FILE *flog, bool noseps)
+{
+
+ int fields, rows, fld, row, flen;
+ char *val;
+
+ if (cancel_pressed)
+ return;
+
+ fields = PQnfields(result);
+ rows = PQntuples(result);
+
+ for (row = 0; row < rows; row++)
+ {
+ for (fld = 0; fld < fields; fld++)
+ {
+ if (! noseps && fld > 0)
+ {
+ if (opt->topt.fieldSep.separator_zero)
+ fputc(0, fout);
+ else
+ fprintf(fout, "%s", opt->topt.fieldSep.separator);
+ }
+ if (PQgetisnull(result,row,fld))
+ continue;
+ val = PQgetvalue(result,row,fld);
+ flen = PQgetlength(result,row,fld);
+ fwrite(val, flen, 1, fout);
+ }
+ if (!noseps)
+ {
+ if (opt->topt.recordSep.separator_zero)
+ fputc(0, fout);
+ else
+ fprintf(fout, "%s", opt->topt.recordSep.separator);
+ }
+ }
+
+
+}
+
+/*
* Use this to print query results
*
* It calls printTable with all the things set straight.
diff --git a/src/bin/psql/print.h b/src/bin/psql/print.h
index 63ba4a5..18a613b 100644
--- a/src/bin/psql/print.h
+++ b/src/bin/psql/print.h
@@ -173,6 +173,8 @@ extern void printTableCleanup(printTableContent *const content);
extern void printTable(const printTableContent *cont, FILE *fout, FILE *flog);
extern void printQuery(const PGresult *result, const printQueryOpt *opt,
FILE *fout, FILE *flog);
+extern void printQueryBin(const PGresult *result, const printQueryOpt *opt,
+ FILE *fout, FILE *flog, bool noseps);
extern void setDecimalLocale(void);
extern const printTextFormat *get_line_style(const printTableOpt *opt);
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index a59f45b..296e5b0 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -317,7 +317,7 @@ main(int argc, char *argv[])
if (pset.echo == PSQL_ECHO_ALL)
puts(options.action_string);
- successResult = SendQuery(options.action_string)
+ successResult = SendQuery(options.action_string, false, false)
? EXIT_SUCCESS : EXIT_FAILURE;
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
