Hi
Thank you for review
2017-01-09 17:24 GMT+01:00 Jason O'Donnell <[email protected]>:
> The following review has been posted through the commitfest application:
> make installcheck-world: tested, passed
> Implements feature: tested, passed
> Spec compliant: not tested
> Documentation: tested, failed
>
> Pavel,
>
> gstore/gbstore:
>
> The functionality worked as expected - one row, one column results of
> queries can be sent to a file or shell. It would be nice if a test case
> was included that proves results more than one row, one column wide will
> fail.
>
fixed
>
> The documentation included is awkward to read. How about:
>
> "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. "
>
super
>
>
> Parameterized Queries:
>
> The functionality proposed works as expected. Throughout the
> documentation, code and test cases the word "Parameterized" is spelled
> incorrectly: "PARAMETRIZED_QUERIES"
>
fixed
>
>
> set_from_file/set_from_bfile:
>
> The functionality proposed worked fine, I was able to set variables in sql
> from files. Minor typo in the documentation:
> "The content is escapeaed as bytea value."
>
fixed
>
> Hope this helps!
>
> Jason O'Donnell
> Crunchy Data
>
> The new status of this patch is: Waiting on Author
>
> --
> Sent via pgsql-hackers mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
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
</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 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.
+<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 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
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 9915731..e47e8d5 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3353,6 +3353,17 @@ bar
</varlistentry>
<varlistentry>
+ <term><varname>PARAMETERIZED_QUERIES</varname></term>
+ <listitem>
+ <para>
+ The psql's variables can be injected to query text (by default) or
+ passed as query parameters when this variable is set
+ <literal>on</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><varname>PORT</varname></term>
<listitem>
<para>
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index e1b04de..94c2993 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -119,9 +119,13 @@ setQFout(const char *fname)
* If "escape" is true, return the value suitably quoted and escaped,
* as an identifier or string literal depending on "as_ident".
* (Failure in escaping should lead to returning NULL.)
+ *
+ * When "inside_query" is true, then the variable can be passed as query parameter,
+ * when it is not used as identifier (as_ident:false), when escape is not required
+ * (escaping changes the content).
*/
char *
-psql_get_variable(const char *varname, bool escape, bool as_ident)
+psql_get_variable(const char *varname, bool escape, bool as_ident, bool inside_query)
{
char *result;
const char *value;
@@ -130,6 +134,35 @@ psql_get_variable(const char *varname, bool escape, bool as_ident)
if (!value)
return NULL;
+ if (inside_query && pset.parameterized_queries)
+ {
+ if (!escape && !as_ident)
+ {
+ char printbuf[10];
+
+ if (pset.nparams >= pset.max_params)
+ {
+ /* create or realloc params array */
+ if (pset.max_params > 0)
+ {
+ pset.max_params += 16;
+ pset.params = (const char **) pg_realloc(pset.params,
+ sizeof(const char *) * pset.max_params);
+ }
+ else
+ {
+ pset.max_params = 16;
+ pset.params = (const char **) pg_malloc(sizeof(const char *) * pset.max_params);
+ }
+ }
+
+ pset.params[pset.nparams++] = value;
+ snprintf(printbuf, sizeof(printbuf) - 1, "$%d", pset.nparams);
+
+ return pstrdup(printbuf);
+ }
+ }
+
if (escape)
{
char *escaped_value;
@@ -1287,7 +1320,16 @@ SendQuery(const char *query)
if (pset.timing)
INSTR_TIME_SET_CURRENT(before);
- results = PQexec(pset.db, query);
+ if (pset.nparams > 0)
+ results = PQexecParams(pset.db, query,
+ pset.nparams,
+ NULL,
+ (const char * const *) pset.params,
+ NULL,
+ NULL,
+ 0);
+ else
+ results = PQexec(pset.db, query);
/* these operations are included in the timing result: */
ResetCancelConn();
@@ -1382,6 +1424,15 @@ SendQuery(const char *query)
ClearOrSaveResult(results);
+ /* the number of query parameters are not necessary now */
+ pset.nparams = 0;
+ if (pset.max_params > 0)
+ {
+ free(pset.params);
+ pset.params = NULL;
+ pset.max_params = 0;
+ }
+
/* Possible microtiming output */
if (pset.timing)
PrintTiming(elapsed_msec);
@@ -1488,7 +1539,16 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec)
appendPQExpBuffer(&buf, "DECLARE _psql_cursor NO SCROLL CURSOR FOR\n%s",
query);
- results = PQexec(pset.db, buf.data);
+ if (pset.nparams > 0)
+ results = PQexecParams(pset.db, buf.data,
+ pset.nparams,
+ NULL,
+ (const char * const *) pset.params,
+ NULL,
+ NULL,
+ 0);
+ else
+ results = PQexec(pset.db, buf.data);
OK = AcceptResult(results) &&
(PQresultStatus(results) == PGRES_COMMAND_OK);
ClearOrSaveResult(results);
diff --git a/src/bin/psql/common.h b/src/bin/psql/common.h
index dad0eb8..4dba1e1 100644
--- a/src/bin/psql/common.h
+++ b/src/bin/psql/common.h
@@ -18,7 +18,7 @@
extern bool openQueryOutputFile(const char *fname, FILE **fout, bool *is_pipe);
extern bool setQFout(const char *fname);
-extern char *psql_get_variable(const char *varname, bool escape, bool as_ident);
+extern char *psql_get_variable(const char *varname, bool escape, bool as_ident, bool inside_query);
extern void psql_error(const char *fmt,...) pg_attribute_printf(1, 2);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 09baf87..1f4ab2c 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -325,7 +325,7 @@ helpVariables(unsigned short int pager)
* Windows builds currently print one more line than non-Windows builds.
* Using the larger number is fine.
*/
- output = PageOutput(88, pager ? &(pset.popt.topt) : NULL);
+ output = PageOutput(90, pager ? &(pset.popt.topt) : NULL);
fprintf(output, _("List of specially treated variables\n\n"));
@@ -352,6 +352,8 @@ helpVariables(unsigned short int pager)
fprintf(output, _(" LASTOID value of the last affected OID\n"));
fprintf(output, _(" ON_ERROR_ROLLBACK if set, an error doesn't stop a transaction (uses implicit savepoints)\n"));
fprintf(output, _(" ON_ERROR_STOP stop batch execution after error\n"));
+ fprintf(output, _(" PARAMETERIZED_QUERIES\n"
+ " pass psql's variables as query parameters\n"));
fprintf(output, _(" PORT server port of the current connection\n"));
fprintf(output, _(" PROMPT1 specifies the standard psql prompt\n"));
fprintf(output, _(" PROMPT2 specifies the prompt used when a statement continues from a previous line\n"));
diff --git a/src/bin/psql/mainloop.c b/src/bin/psql/mainloop.c
index bb306a4..c4828fe 100644
--- a/src/bin/psql/mainloop.c
+++ b/src/bin/psql/mainloop.c
@@ -403,6 +403,15 @@ MainLoop(FILE *source)
psql_scan_finish(scan_state);
free(line);
+ /* reset a number of query parameters */
+ pset.nparams = 0;
+ if (pset.max_params > 0)
+ {
+ free(pset.params);
+ pset.params = NULL;
+ pset.max_params = 0;
+ }
+
if (slashCmdStatus == PSQL_CMD_TERMINATE)
{
successResult = EXIT_SUCCESS;
diff --git a/src/bin/psql/psqlscanslash.l b/src/bin/psql/psqlscanslash.l
index 5b7953b..3e58303 100644
--- a/src/bin/psql/psqlscanslash.l
+++ b/src/bin/psql/psqlscanslash.l
@@ -243,6 +243,7 @@ other .
yyleng - 1);
value = cur_state->callbacks->get_variable(varname,
false,
+ false,
false);
free(varname);
@@ -271,7 +272,7 @@ other .
ECHO;
else
{
- psqlscan_escape_variable(cur_state, yytext, yyleng, false);
+ psqlscan_escape_variable(cur_state, yytext, yyleng, false, false);
*option_quote = ':';
}
unquoted_option_chars = 0;
@@ -283,7 +284,7 @@ other .
ECHO;
else
{
- psqlscan_escape_variable(cur_state, yytext, yyleng, true);
+ psqlscan_escape_variable(cur_state, yytext, yyleng, true, false);
*option_quote = ':';
}
unquoted_option_chars = 0;
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 4c7c3b1..7d21e46 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -120,6 +120,7 @@ typedef struct _psqlSettings
* functions.
*/
bool autocommit;
+ bool parameterized_queries;
bool on_error_stop;
bool quiet;
bool singleline;
@@ -135,6 +136,9 @@ typedef struct _psqlSettings
const char *prompt3;
PGVerbosity verbosity; /* current error verbosity level */
PGContextVisibility show_context; /* current context display level */
+ int nparams; /* number of query parameters */
+ int max_params; /* max size of current parameters array */
+ const char **params; /* query parameters */
} PsqlSettings;
extern PsqlSettings pset;
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index 85aac4a..441a0ca 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -139,6 +139,10 @@ main(int argc, char *argv[])
pset.last_error_result = NULL;
pset.cur_cmd_source = stdin;
pset.cur_cmd_interactive = false;
+ pset.parameterized_queries = false;
+ pset.nparams = 0;
+ pset.max_params = 0;
+ pset.params = NULL;
/* We rely on unmentioned fields of pset.popt to start out 0/false/NULL */
pset.popt.topt.format = PRINT_ALIGNED;
@@ -793,6 +797,12 @@ autocommit_hook(const char *newval)
}
static void
+parameterized_queries_hook(const char *newval)
+{
+ pset.parameterized_queries = ParseVariableBool(newval, "PARAMETERIZED_QUERIES");
+}
+
+static void
on_error_stop_hook(const char *newval)
{
pset.on_error_stop = ParseVariableBool(newval, "ON_ERROR_STOP");
@@ -990,6 +1000,7 @@ EstablishVariableSpace(void)
SetVariableAssignHook(pset.vars, "ON_ERROR_ROLLBACK", on_error_rollback_hook);
SetVariableAssignHook(pset.vars, "COMP_KEYWORD_CASE", comp_keyword_case_hook);
SetVariableAssignHook(pset.vars, "HISTCONTROL", histcontrol_hook);
+ SetVariableAssignHook(pset.vars, "PARAMETERIZED_QUERIES", parameterized_queries_hook);
SetVariableAssignHook(pset.vars, "PROMPT1", prompt1_hook);
SetVariableAssignHook(pset.vars, "PROMPT2", prompt2_hook);
SetVariableAssignHook(pset.vars, "PROMPT3", prompt3_hook);
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 7709112..6f614df 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3255,8 +3255,8 @@ psql_completion(const char *text, int start, int end)
}
else if (TailMatchesCS2("\\set", MatchAny))
{
- if (TailMatchesCS1("AUTOCOMMIT|ON_ERROR_STOP|QUIET|"
- "SINGLELINE|SINGLESTEP"))
+ if (TailMatchesCS1("AUTOCOMMIT|ON_ERROR_STOP|PARAMETERIZED_QUERIES|"
+ "QUIET|SINGLELINE|SINGLESTEP"))
COMPLETE_WITH_LIST_CS2("on", "off");
else if (TailMatchesCS1("COMP_KEYWORD_CASE"))
COMPLETE_WITH_LIST_CS4("lower", "upper",
@@ -3748,7 +3748,7 @@ complete_from_variables(const char *text, const char *prefix, const char *suffix
"AUTOCOMMIT", "COMP_KEYWORD_CASE", "DBNAME", "ECHO", "ECHO_HIDDEN",
"ENCODING", "FETCH_COUNT", "HISTCONTROL", "HISTFILE", "HISTSIZE",
"HOST", "IGNOREEOF", "LASTOID", "ON_ERROR_ROLLBACK", "ON_ERROR_STOP",
- "PORT", "PROMPT1", "PROMPT2", "PROMPT3", "QUIET",
+ "PARAMETERIZED_QUERIES", "PORT", "PROMPT1", "PROMPT2", "PROMPT3", "QUIET",
"SHOW_CONTEXT", "SINGLELINE", "SINGLESTEP",
"USER", "VERBOSITY", NULL
};
diff --git a/src/fe_utils/psqlscan.l b/src/fe_utils/psqlscan.l
index 1b29341..6ab9c0b 100644
--- a/src/fe_utils/psqlscan.l
+++ b/src/fe_utils/psqlscan.l
@@ -700,7 +700,8 @@ other .
if (cur_state->callbacks->get_variable)
value = cur_state->callbacks->get_variable(varname,
false,
- false);
+ false,
+ true);
else
value = NULL;
@@ -736,11 +737,11 @@ other .
}
:'{variable_char}+' {
- psqlscan_escape_variable(cur_state, yytext, yyleng, false);
+ psqlscan_escape_variable(cur_state, yytext, yyleng, false, true);
}
:\"{variable_char}+\" {
- psqlscan_escape_variable(cur_state, yytext, yyleng, true);
+ psqlscan_escape_variable(cur_state, yytext, yyleng, true, true);
}
/*
@@ -1401,7 +1402,7 @@ psqlscan_extract_substring(PsqlScanState state, const char *txt, int len)
*/
void
psqlscan_escape_variable(PsqlScanState state, const char *txt, int len,
- bool as_ident)
+ bool as_ident, bool from_query)
{
char *varname;
char *value;
@@ -1409,7 +1410,8 @@ psqlscan_escape_variable(PsqlScanState state, const char *txt, int len,
/* Variable lookup. */
varname = psqlscan_extract_substring(state, txt + 2, len - 3);
if (state->callbacks->get_variable)
- value = state->callbacks->get_variable(varname, true, as_ident);
+ value = state->callbacks->get_variable(varname,
+ true, as_ident, from_query);
else
value = NULL;
free(varname);
diff --git a/src/include/fe_utils/psqlscan.h b/src/include/fe_utils/psqlscan.h
index 21c4f22..e14764d 100644
--- a/src/include/fe_utils/psqlscan.h
+++ b/src/include/fe_utils/psqlscan.h
@@ -53,7 +53,8 @@ typedef struct PsqlScanCallbacks
{
/* Fetch value of a variable, as a pfree'able string; NULL if unknown */
/* This pointer can be NULL if no variable substitution is wanted */
- char *(*get_variable) (const char *varname, bool escape, bool as_ident);
+ char *(*get_variable) (const char *varname,
+ bool escape, bool as_ident, bool from_query);
/* Print an error message someplace appropriate */
/* (very old gcc versions don't support attributes on function pointers) */
#if defined(__GNUC__) && __GNUC__ < 4
diff --git a/src/include/fe_utils/psqlscan_int.h b/src/include/fe_utils/psqlscan_int.h
index 0fddc7a..1fb2793 100644
--- a/src/include/fe_utils/psqlscan_int.h
+++ b/src/include/fe_utils/psqlscan_int.h
@@ -139,6 +139,7 @@ extern char *psqlscan_extract_substring(PsqlScanState state,
const char *txt, int len);
extern void psqlscan_escape_variable(PsqlScanState state,
const char *txt, int len,
- bool as_ident);
+ bool as_ident,
+ bool from_query);
#endif /* PSQLSCAN_INT_H */
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 464436a..4f3c580 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -2714,3 +2714,36 @@ 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
+-- parameterized queries
+\set PARAMETERIZED_QUERIES off
+\set a1 'AHOJ SVETE'
+-- should fail
+SELECT :a1;
+ERROR: column "ahoj" does not exist
+LINE 1: SELECT AHOJ SVETE;
+ ^
+-- ok
+SELECT :'a1';
+ ?column?
+------------
+ AHOJ SVETE
+(1 row)
+
+\set PARAMETERIZED_QUERIES on
+-- should fail - unknown type
+SELECT :a1;
+ERROR: could not determine data type of parameter $1
+-- ok
+SELECT :a1::text;
+ text
+------------
+ AHOJ SVETE
+(1 row)
+
+-- returns true, when value passed as parameter is same as client side evaluated variable
+SELECT :a1 = :'a1';
+ ?column?
+----------
+ t
+(1 row)
+
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 900aa7e..fbc9302 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -379,3 +379,23 @@ begin
raise notice 'foo';
raise exception 'bar';
end $$;
+
+-- parameterized queries
+\set PARAMETERIZED_QUERIES off
+\set a1 'AHOJ SVETE'
+
+-- should fail
+SELECT :a1;
+
+-- ok
+SELECT :'a1';
+
+\set PARAMETERIZED_QUERIES on
+-- should fail - unknown type
+SELECT :a1;
+
+-- ok
+SELECT :a1::text;
+
+-- returns true, when value passed as parameter is same as client side evaluated variable
+SELECT :a1 = :'a1';
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 9915731..1d77569 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2697,6 +2697,33 @@ lo_import 152801
<varlistentry>
+ <term><literal>\set_from_bfile <replaceable class="parameter">name</replaceable> <replaceable class="parameter">filename</replaceable></literal></term>
+
+ <listitem>
+ <para>
+ Sets the <application>psql</> variable <replaceable
+ class="parameter">name</replaceable> by content of
+ binary file <replaceable class="parameter">filename</replaceable>.
+ The content is escaped as bytea value.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
+ <varlistentry>
+ <term><literal>\set_from_file <replaceable class="parameter">name</replaceable> <replaceable class="parameter">filename</replaceable></literal></term>
+
+ <listitem>
+ <para>
+ Sets the <application>psql</> variable <replaceable
+ class="parameter">name</replaceable> by content of
+ text file <replaceable class="parameter">filename</replaceable>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
+ <varlistentry>
<term><literal>\setenv <replaceable class="parameter">name</replaceable> [ <replaceable class="parameter">value</replaceable> ]</literal></term>
<listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index a9a2fdb..bcc1793 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -82,6 +82,7 @@ static void minimal_error_message(PGresult *res);
static void printSSLInfo(void);
static bool printPsetInfo(const char *param, struct printQueryOpt *popt);
static char *pset_value_string(const char *param, struct printQueryOpt *popt);
+static bool read_file(char *fname, PQExpBuffer rawbuf);
#ifdef WIN32
static void checkWin32Codepage(void);
@@ -1318,6 +1319,82 @@ exec_command(const char *cmd,
free(opt0);
}
+ /* \set_from_file, \set_from_bfile -- set variable/option command from file */
+ else if (strcmp(cmd, "set_from_file") == 0 || strcmp(cmd, "set_from_bfile") == 0)
+ {
+ char *varname = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, false);
+
+ if (!varname)
+ {
+ psql_error("\\%s: missing required argument\n", cmd);
+ success = false;
+ }
+ else
+ {
+ char *fname = psql_scan_slash_option(scan_state,
+ OT_FILEPIPE, NULL, true);
+
+ if (!fname)
+ {
+ psql_error("\\%s: missing required argument\n", cmd);
+ success = false;
+ }
+ else
+ {
+ PQExpBufferData rawbuf;
+
+ initPQExpBuffer(&rawbuf);
+
+ expand_tilde(&fname);
+ canonicalize_path(fname);
+
+ if (read_file(fname, &rawbuf))
+ {
+ char *newval;
+
+ /* do bytea escaping when it is required */
+ if (strcmp(cmd, "set_from_bfile") == 0)
+ {
+ size_t escaped_size;
+
+ newval = (char *) PQescapeByteaConn(pset.db,
+ (const unsigned char *) rawbuf.data, rawbuf.len,
+ &escaped_size);
+ }
+ else
+ newval = rawbuf.data;
+
+ if (!newval)
+ {
+ psql_error("%s\n", PQerrorMessage(pset.db));
+ success = false;
+ }
+ else
+ {
+ if (!SetVariable(pset.vars, varname, newval))
+ {
+ psql_error("\\%s: error while setting variable\n", cmd);
+ success = false;
+ }
+
+ /* release Bytea escaped result */
+ if (newval != rawbuf.data)
+ PQfreemem(newval);
+ }
+ }
+ else
+ success = false;
+
+ /* release raw content */
+ termPQExpBuffer(&rawbuf);
+
+ if (fname)
+ free(fname);
+ }
+ }
+ free(varname);
+ }
/* \setenv -- set environment command */
else if (strcmp(cmd, "setenv") == 0)
@@ -3657,3 +3734,53 @@ minimal_error_message(PGresult *res)
destroyPQExpBuffer(msg);
}
+
+/*
+ * file-content-fetching callback for read file content commands.
+ */
+static bool
+read_file(char *fname, PQExpBuffer rawbuf)
+{
+ FILE *fd;
+ bool result = false;
+
+ fd = fopen(fname, PG_BINARY_R);
+ if (fd)
+ {
+ struct stat fst;
+
+ if (fstat(fileno(fd), &fst) != -1)
+ {
+ if (S_ISREG(fst.st_mode))
+ {
+ if (fst.st_size <= ((int64) 1024) * 1024 * 1024)
+ {
+ size_t size;
+ char buf[512];
+
+ while ((size = fread(buf, 1, sizeof(buf), fd)) > 0)
+ appendBinaryPQExpBuffer(rawbuf, buf, size);
+
+ if (ferror(fd))
+ psql_error("%s: %s\n", fname, strerror(errno));
+ else if (PQExpBufferBroken(rawbuf))
+ psql_error("out of memory\n");
+ else
+ result = true;
+ }
+ else
+ psql_error("%s is too big (greather than 1GB)\n", fname);
+ }
+ else
+ psql_error("%s is not regular file\n", fname);
+ }
+ else
+ psql_error("%s: %s\n", fname, strerror(errno));
+
+ fclose(fd);
+ }
+ else
+ psql_error("%s: %s\n", fname, strerror(errno));
+
+ return result;
+}
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index cd64c39..c22046a 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1339,8 +1339,9 @@ psql_completion(const char *text, int start, int end)
"\\f", "\\g", "\\gexec", "\\gset", "\\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",
- "\\timing", "\\unset", "\\x", "\\w", "\\watch", "\\z", "\\!", NULL
+ "\\s", "\\set", "\\setenv", "\\set_from_bfile", "\\set_from_file",
+ "\\sf", "\\sv", "\\t", "\\T", "\\timing", "\\unset", "\\x",
+ "\\w", "\\watch", "\\z", "\\!", NULL
};
(void) end; /* "end" is not used */
@@ -3236,6 +3237,15 @@ psql_completion(const char *text, int start, int end)
else if (TailMatchesCS1("VERBOSITY"))
COMPLETE_WITH_LIST_CS3("default", "verbose", "terse");
}
+ else if (TailMatchesCS1("\\set_from_bfile|\\set_from_file"))
+ {
+ matches = complete_from_variables(text, "", "", false);
+ }
+ else if (TailMatchesCS2("\\set_from_bfile|\\set_from_file", MatchAny))
+ {
+ completion_charp = "\\";
+ matches = completion_matches(text, complete_from_files);
+ }
else if (TailMatchesCS1("\\sf*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
else if (TailMatchesCS1("\\sv*"))
diff --git a/src/test/regress/input/misc.source b/src/test/regress/input/misc.source
index dd2d1b2..eead8ae 100644
--- a/src/test/regress/input/misc.source
+++ b/src/test/regress/input/misc.source
@@ -273,3 +273,21 @@ drop table oldstyle_test;
--
-- rewrite rules
--
+
+---
+--- load file and store it to variable
+---
+CREATE TABLE test_setref(a text, b bytea);
+
+-- use two different ways for import data - result should be same
+\lo_import @abs_builddir@/data/hash.data
+\set lo_oid :LASTOID
+INSERT INTO test_setref (b) VALUES(lo_get(:lo_oid));
+\lo_unlink :lo_oid
+SELECT md5(b) FROM test_setref;
+TRUNCATE test_setref;
+
+\set_from_file var1 @abs_builddir@/data/hash.data
+\set_from_bfile var2 @abs_builddir@/data/hash.data
+INSERT INTO test_setref(a,b) VALUES(:'var1', :'var2');
+SELECT md5(a), md5(b) FROM test_setref;
diff --git a/src/test/regress/output/misc.source b/src/test/regress/output/misc.source
index 574ef0d..52c78fe 100644
--- a/src/test/regress/output/misc.source
+++ b/src/test/regress/output/misc.source
@@ -708,3 +708,28 @@ drop table oldstyle_test;
--
-- rewrite rules
--
+---
+--- load file and store it to variable
+---
+CREATE TABLE test_setref(a text, b bytea);
+-- use two different ways for import data - result should be same
+\lo_import @abs_builddir@/data/hash.data
+\set lo_oid :LASTOID
+INSERT INTO test_setref (b) VALUES(lo_get(:lo_oid));
+\lo_unlink :lo_oid
+SELECT md5(b) FROM test_setref;
+ md5
+----------------------------------
+ e446fe6ea5a347e69670633412c7f8cb
+(1 row)
+
+TRUNCATE test_setref;
+\set_from_file var1 @abs_builddir@/data/hash.data
+\set_from_bfile var2 @abs_builddir@/data/hash.data
+INSERT INTO test_setref(a,b) VALUES(:'var1', :'var2');
+SELECT md5(a), md5(b) FROM test_setref;
+ md5 | md5
+----------------------------------+----------------------------------
+ e446fe6ea5a347e69670633412c7f8cb | e446fe6ea5a347e69670633412c7f8cb
+(1 row)
+
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
