diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 9915731..4f95f86 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1944,6 +1944,28 @@ hello 10 + + + \gstore [ filename ] + \gstore [ |command ] + \gbstore [ filename ] + \gbstore [ |command ] + + + Sends the current query input buffer to the server and stores + the result to an output file specified in the query or pipes the output + to a shell command. The file or command are written to only if the query + successfully returns exactly one, non-null row and column. If the + query fails or does not return data, an error is raised. + +=> SELECT avatar FROM users WHERE id = 123 +-> \gbstore ~/avatar.png + + + + + + \h or \help [ command ] diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 4139b77..33f4559 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 e1b04de..a6aaebe 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 09baf87..be26ff0 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 4c7c3b1..1c5a68d 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 7709112..36e5c1a 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", @@ -3279,8 +3280,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 = "\\"; diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out index 464436a..b2aedbe 100644 --- a/src/test/regress/expected/psql.out +++ b/src/test/regress/expected/psql.out @@ -2714,3 +2714,24 @@ NOTICE: foo CONTEXT: PL/pgSQL function inline_code_block line 3 at RAISE ERROR: bar CONTEXT: PL/pgSQL function inline_code_block line 4 at RAISE +-- should fail +select 'Hello','Hello' +\gstore foofile +more than one column returned for \gstore or \gbstore +select E'\\xDEADBEEF'::bytea, E'\\xDEADBEEF'::bytea +\gbstore foofile +more than one column returned for \gstore or \gbstore +select 'hello' union all select 'hello' +\gstore foofile +more than one row returned for \gstore or \gbstore +select E'\\xDEADBEEF'::bytea union all E'\\xDEADBEEF'::bytea +\gbstore foofile +ERROR: syntax error at or near "E'\\xDEADBEEF'" +LINE 1: select E'\\xDEADBEEF'::bytea union all E'\\xDEADBEEF'::bytea + ^ +select 'hello' where false +\gstore foofile +no rows returned for \gstore or \gbstore +select E'\\xDEADBEEF'::bytea where false +\gbstore foofile +no rows returned for \gstore or \gbstore diff --git a/src/test/regress/input/misc.source b/src/test/regress/input/misc.source index dd2d1b2..39cde61 100644 --- a/src/test/regress/input/misc.source +++ b/src/test/regress/input/misc.source @@ -273,3 +273,28 @@ drop table oldstyle_test; -- -- rewrite rules -- + +-- +-- psql gstore, gbstore commands +-- +CREATE TABLE test_store(a text, b bytea); +INSERT INTO test_store values('AHOJ', E'\\xDEADBEEF'); +SELECT md5(a) a, md5(b) b FROM test_store; + +SELECT a FROM test_store +\gstore @abs_builddir@/data/test_store.txt +SELECT b FROM test_store +\gbstore @abs_builddir@/data/test_store.bin + +\lo_import @abs_builddir@/data/test_store.txt +\set lo_oid :LASTOID +SELECT md5(lo_get(:lo_oid)); +\lo_unlink :lo_oid + +\lo_import @abs_builddir@/data/test_store.bin +\set lo_oid :LASTOID +SELECT md5(lo_get(:lo_oid)); +\lo_unlink :lo_oid + +DROP TABLE test_store; + diff --git a/src/test/regress/output/misc.source b/src/test/regress/output/misc.source index 574ef0d..eef17ec 100644 --- a/src/test/regress/output/misc.source +++ b/src/test/regress/output/misc.source @@ -708,3 +708,37 @@ drop table oldstyle_test; -- -- rewrite rules -- +-- +-- psql gstore, gbstore commands +-- +CREATE TABLE test_store(a text, b bytea); +INSERT INTO test_store values('AHOJ', E'\\xDEADBEEF'); +SELECT md5(a) a, md5(b) b FROM test_store; + a | b +----------------------------------+---------------------------------- + 5d75193725cfb92ce9aee96b5380db06 | 2f249230a8e7c2bf6005ccd2679259ec +(1 row) + +SELECT a FROM test_store +\gstore @abs_builddir@/data/test_store.txt +SELECT b FROM test_store +\gbstore @abs_builddir@/data/test_store.bin +\lo_import @abs_builddir@/data/test_store.txt +\set lo_oid :LASTOID +SELECT md5(lo_get(:lo_oid)); + md5 +---------------------------------- + 5d75193725cfb92ce9aee96b5380db06 +(1 row) + +\lo_unlink :lo_oid +\lo_import @abs_builddir@/data/test_store.bin +\set lo_oid :LASTOID +SELECT md5(lo_get(:lo_oid)); + md5 +---------------------------------- + 2f249230a8e7c2bf6005ccd2679259ec +(1 row) + +\lo_unlink :lo_oid +DROP TABLE test_store; diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql index 900aa7e..7edc1e8 100644 --- a/src/test/regress/sql/psql.sql +++ b/src/test/regress/sql/psql.sql @@ -379,3 +379,22 @@ begin raise notice 'foo'; raise exception 'bar'; end $$; + +-- should fail +select 'Hello','Hello' +\gstore foofile + +select E'\\xDEADBEEF'::bytea, E'\\xDEADBEEF'::bytea +\gbstore foofile + +select 'hello' union all select 'hello' +\gstore foofile + +select E'\\xDEADBEEF'::bytea union all E'\\xDEADBEEF'::bytea +\gbstore foofile + +select 'hello' where false +\gstore foofile + +select E'\\xDEADBEEF'::bytea where false +\gbstore foofile