Hi
a independent implementation of parametrized queries can looks like
attached patch:
this code is simple without any unexpected behave.
parameters are used when user doesn't require escaping and when
PARAMETRIZED_QUERIES variable is on
Regards
Pavel
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index a7789df..57dd8f0 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 "from_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 from_query)
{
char *result;
const char *value;
@@ -130,6 +134,26 @@ psql_get_variable(const char *varname, bool escape, bool as_ident)
if (!value)
return NULL;
+ if (from_query && pset.parametrized_queries)
+ {
+ if (!escape && !as_ident)
+ {
+ char printbuf[5];
+
+ if (pset.nparams > MAX_QUERY_PARAMS)
+ {
+ psql_error("too much parameters (more than %d)\n",
+ MAX_QUERY_PARAMS);
+ return NULL;
+ }
+
+ pset.params[pset.nparams++] = value;
+ snprintf(printbuf, sizeof(printbuf) - 1, "$%d", pset.nparams);
+
+ return pstrdup(printbuf);
+ }
+ }
+
if (escape)
{
char *escaped_value;
@@ -1287,7 +1311,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 +1415,9 @@ SendQuery(const char *query)
ClearOrSaveResult(results);
+ /* the number of query parameters are not necessary now */
+ pset.nparams = 0;
+
/* Possible microtiming output */
if (pset.timing)
PrintTiming(elapsed_msec);
@@ -1488,7 +1524,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..79f8c91 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 from_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..cb6a2ee 100644
--- a/src/bin/psql/mainloop.c
+++ b/src/bin/psql/mainloop.c
@@ -403,6 +403,9 @@ MainLoop(FILE *source)
psql_scan_finish(scan_state);
free(line);
+ /* reset a number of query parameters */
+ pset.nparams = 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..77e4611 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -77,6 +77,8 @@ enum trivalue
TRI_YES
};
+#define MAX_QUERY_PARAMS 64
+
typedef struct _psqlSettings
{
PGconn *db; /* connection to backend */
@@ -120,6 +122,7 @@ typedef struct _psqlSettings
* functions.
*/
bool autocommit;
+ bool parametrized_queries;
bool on_error_stop;
bool quiet;
bool singleline;
@@ -135,6 +138,8 @@ 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 */
+ const char *params[MAX_QUERY_PARAMS]; /* query parameters */
} PsqlSettings;
extern PsqlSettings pset;
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index 7ce05fb..f8fceee 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -139,6 +139,8 @@ 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;
/* We rely on unmentioned fields of pset.popt to start out 0/false/NULL */
pset.popt.topt.format = PRINT_ALIGNED;
@@ -793,6 +795,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 +998,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 b556c00..03984c5 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3191,8 +3191,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",
@@ -3684,7 +3684,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';
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
