Hi I am sending set of patches - for simpler testing these patches are independent in this moment.
These patches are replacement of my previous patches in this area: COPY RAW and fileref variables. 1. parametrized queries support - the psql variables can be passed as query parameters 2. \gstore, \gbstore - save returned (binary) value to file 3. \set_from_file. \set_from_bfile - set a variable from (binary) file The code is simple - there are not any change in critical or complex parts of psql. Regards Pavel Comments, notes?
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 = "\\";
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 9915731..b16670d 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>PARAMETRIZED_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 a7789df..b20f8f5 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.parametrized_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 bdcb58f..6d8eda7 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 a69c4dd..3c62146 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, _(" PARAMETRIZED_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 37dfa4d..9638a5b 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 86832a8..4a34f29 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 8cfe9d2..4e75bd8 100644 --- a/src/bin/psql/settings.h +++ b/src/bin/psql/settings.h @@ -120,6 +120,7 @@ typedef struct _psqlSettings * functions. */ bool autocommit; + bool parametrized_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 7ce05fb..6059e44 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.parametrized_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 +parametrized_queries_hook(const char *newval) +{ + pset.parametrized_queries = ParseVariableBool(newval, "PARAMETRIZED_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, "PARAMETRIZED_QUERIES", parametrized_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 cd64c39..8a38671 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -3216,8 +3216,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|PARAMETRIZED_QUERIES|" + "QUIET|SINGLELINE|SINGLESTEP")) COMPLETE_WITH_LIST_CS2("on", "off"); else if (TailMatchesCS1("COMP_KEYWORD_CASE")) COMPLETE_WITH_LIST_CS4("lower", "upper", @@ -3709,7 +3709,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", + "PARAMETRIZED_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 55067b4..06a6519 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 1f10ecc..3854117 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 a52929d..53210b2 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..7fed568 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 +-- parametrized queries +\set PARAMETRIZED_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 PARAMETRIZED_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..69e2df1 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 $$; + +-- parametrized queries +\set PARAMETRIZED_QUERIES off +\set a1 'AHOJ SVETE' + +-- should fail +SELECT :a1; + +-- ok +SELECT :'a1'; + +\set PARAMETRIZED_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 escapeaed 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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers