Hi
I propose a new type of psql variables - file references. The content of
file reference is specified by referenced file. It allows simple inserting
large data without necessity of manual escaping or using LO api.
When I wrote the patch, I used parametrized queries for these data instead
escaped strings - the code is not much bigger, and the error messages are
much more friendly if query is not bloated by bigger content. The text mode
is used only - when escaping is not required, then content is implicitly
transformed to bytea. By default the content of file is bytea. When use
requires escaping, then he enforces text escaping - because it has sense
only for text type.
postgres=# \setfileref a ~/test2.xml
postgres=# \setfileref b ~/avatar.gif
postgres=# insert into test values(convert_from(:a, 'latin2')::xml, :b); --
xml is passed as bytea
postgres=# insert into test values(:'a', :b); -- xml is passed via unknown
text value
The content of file reference variables is not persistent in memory.
Comments, notes?
Regards
Pavel
commit 077c71b1f8ae24ccf2f3723e1e4ca5bf05bca0d3
Author: Pavel Stehule <pavel.steh...@gooddata.com>
Date: Wed Aug 31 17:15:33 2016 +0200
initial
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 4aaf657..3150510 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1355,6 +1355,32 @@ exec_command(const char *cmd,
free(envval);
}
+ /* \setfileref - set variable by reference on file */
+ else if (strcmp(cmd, "setfileref") == 0)
+ {
+ char *name = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, false);
+
+ char *ref = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, false);
+
+ success = false;
+
+ if (!name || !ref)
+ {
+ psql_error("\\%s: missing required argument\n", cmd);
+ success = false;
+ }
+ else
+ {
+ if (!SetFileRef(pset.vars, name, ref))
+ {
+ psql_error("\\%s: error while setting variable\n", cmd);
+ success = false;
+ }
+ }
+ }
+
/* \sf -- show a function's source code */
else if (strcmp(cmd, "sf") == 0 || strcmp(cmd, "sf+") == 0)
{
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 7399950..3c1db17 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -32,7 +32,6 @@ static bool ExecQueryUsingCursor(const char *query, double *elapsed_msec);
static bool command_no_begin(const char *query);
static bool is_select_command(const char *query);
-
/*
* openQueryOutputFile --- attempt to open a query output file
*
@@ -108,6 +107,123 @@ setQFout(const char *fname)
return true;
}
+void
+psql_reset_query_params(void)
+{
+ int i;
+
+ for (i = 0; i < pset.nparams; i++)
+ if (pset.params[i] != NULL)
+ {
+ PQfreemem(pset.params[i]);
+ pset.params[i] = NULL;
+ }
+
+ pset.nparams = 0;
+}
+
+/*
+ * Load a content of the file_ref related file to query params buffer.
+ * When escaping is requested, then the text content is expected.
+ * Without escaping the bytea content is expected and related bytea
+ * escaping is processed.
+ */
+#define BYTEAOID 17
+#define UNKNOWNOID 0
+
+static char *
+get_file_ref_content(const char *value, bool escape, bool as_ident)
+{
+ PQExpBufferData buffer;
+ FILE *fd = NULL;
+ char *fname;
+ char *escaped_value;
+ char line[1024];
+ size_t size;
+
+ fname = pstrdup(value);
+
+ expand_tilde(&fname);
+ if (!fname)
+ {
+ psql_error("missing valid path to file\n");
+ return NULL;
+ }
+
+ canonicalize_path(fname);
+
+ fd = fopen(fname, PG_BINARY_R);
+ if (!fd)
+ {
+ psql_error("%s: %s\n", fname, strerror(errno));
+ PQfreemem(fname);
+ return NULL;
+ }
+
+ /* can append another parameter */
+ if (pset.nparams >= MAX_BINARY_PARAMS)
+ {
+ psql_error("too much binary parameters");
+ PQfreemem(fname);
+ return NULL;
+ }
+
+ if (!pset.db)
+ {
+ psql_error("cannot escape without active connection\n");
+ PQfreemem(fname);
+ return NULL;
+ }
+
+ initPQExpBuffer(&buffer);
+
+ while ((size = fread(line, 1, sizeof(line), fd)) > 0)
+ appendBinaryPQExpBuffer(&buffer, line, size);
+
+ if (ferror(fd))
+ {
+ psql_error("%s: %s\n", fname, strerror(errno));
+ PQfreemem(fname);
+ termPQExpBuffer(&buffer);
+ return NULL;
+ }
+
+ if (escape)
+ {
+ if (as_ident)
+ escaped_value =
+ PQescapeIdentifier(pset.db, buffer.data, buffer.len);
+ else
+ escaped_value =
+ PQescapeLiteral(pset.db, buffer.data, buffer.len);
+ pset.paramTypes[pset.nparams] = UNKNOWNOID;
+ }
+ else
+ {
+ escaped_value = (char *)
+ PQescapeByteaConn(pset.db,
+ (const unsigned char *) buffer.data, buffer.len, &size);
+ pset.paramTypes[pset.nparams] = BYTEAOID;
+ }
+
+ /* fname, buffer is not necessary longer */
+ PQfreemem(fname);
+ termPQExpBuffer(&buffer);
+
+ if (escaped_value == NULL)
+ {
+ const char *error = PQerrorMessage(pset.db);
+
+ psql_error("%s", error);
+ return NULL;
+ }
+
+ pset.params[pset.nparams] = escaped_value;
+
+ snprintf(line, sizeof(line) - 1, "$%d", ++pset.nparams);
+
+ return pstrdup(line);
+}
/*
* Variable-fetching callback for flex lexer
@@ -124,11 +240,15 @@ psql_get_variable(const char *varname, bool escape, bool as_ident)
{
char *result;
const char *value;
+ bool is_file_ref;
- value = GetVariable(pset.vars, varname);
+ value = (char *) GetVariableOrFileRef(pset.vars, varname, &is_file_ref);
if (!value)
return NULL;
+ if (is_file_ref)
+ return get_file_ref_content(value, escape, as_ident);
+
if (escape)
{
char *escaped_value;
@@ -1235,7 +1355,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,
+ pset.paramTypes,
+ (const char * const *) pset.params,
+ NULL,
+ NULL,
+ 0);
+ else
+ results = PQexec(pset.db, query);
/* these operations are included in the timing result: */
ResetCancelConn();
@@ -1380,7 +1509,6 @@ sendquery_cleanup:
return OK;
}
-
/*
* ExecQueryUsingCursor: run a SELECT-like query using a cursor
*
diff --git a/src/bin/psql/common.h b/src/bin/psql/common.h
index bdcb58f..4f46b9c 100644
--- a/src/bin/psql/common.h
+++ b/src/bin/psql/common.h
@@ -38,6 +38,8 @@ extern int PSQLexecWatch(const char *query, const printQueryOpt *opt);
extern bool SendQuery(const char *query);
+void psql_reset_query_params(void);
+
extern bool is_superuser(void);
extern bool standard_strings(void);
extern const char *session_username(void);
diff --git a/src/bin/psql/mainloop.c b/src/bin/psql/mainloop.c
index 37dfa4d..23fd6d3 100644
--- a/src/bin/psql/mainloop.c
+++ b/src/bin/psql/mainloop.c
@@ -23,7 +23,6 @@ const PsqlScanCallbacks psqlscan_callbacks = {
psql_error
};
-
/*
* Main processing loop for reading lines of input
* and sending them to the backend.
@@ -403,6 +402,9 @@ MainLoop(FILE *source)
psql_scan_finish(scan_state);
free(line);
+ /* reset binary parameters */
+ psql_reset_query_params();
+
if (slashCmdStatus == PSQL_CMD_TERMINATE)
{
successResult = EXIT_SUCCESS;
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 8cfe9d2..2874704 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -77,6 +77,8 @@ enum trivalue
TRI_YES
};
+#define MAX_BINARY_PARAMS 32
+
typedef struct _psqlSettings
{
PGconn *db; /* connection to backend */
@@ -135,6 +137,9 @@ typedef struct _psqlSettings
const char *prompt3;
PGVerbosity verbosity; /* current error verbosity level */
PGContextVisibility show_context; /* current context display level */
+ int nparams;
+ Oid paramTypes[MAX_BINARY_PARAMS];
+ char *params[MAX_BINARY_PARAMS];
} PsqlSettings;
extern PsqlSettings pset;
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index 111593c..1ed40aa 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -106,6 +106,7 @@ main(int argc, char *argv[])
char *password = NULL;
char *password_prompt = NULL;
bool new_pass;
+ int i;
set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("psql"));
@@ -139,6 +140,10 @@ main(int argc, char *argv[])
pset.cur_cmd_source = stdin;
pset.cur_cmd_interactive = false;
+ pset.nparams = 0;
+ for (i = 0; i < MAX_BINARY_PARAMS; i++)
+ pset.params[i] = NULL;
+
/* We rely on unmentioned fields of pset.popt to start out 0/false/NULL */
pset.popt.topt.format = PRINT_ALIGNED;
pset.popt.topt.border = 1;
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 1345e4e..97460ee 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1292,7 +1292,7 @@ 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",
+ "\\s", "\\set", "\\setenv", "\\setfileref", "\\sf", "\\sv", "\\t", "\\T",
"\\timing", "\\unset", "\\x", "\\w", "\\watch", "\\z", "\\!", NULL
};
@@ -3107,6 +3107,12 @@ psql_completion(const char *text, int start, int end)
matches = completion_matches(text, complete_from_files);
}
+ else if (Matches2("\\setfileref", MatchAny))
+ {
+ completion_charp = "\\";
+ matches = completion_matches(text, complete_from_files);
+ }
+
/*
* Finally, we look through the list of "things", such as TABLE, INDEX and
* check if that was the previous word. If so, execute the query to get a
diff --git a/src/bin/psql/variables.c b/src/bin/psql/variables.c
index f43f418..1c12719 100644
--- a/src/bin/psql/variables.c
+++ b/src/bin/psql/variables.c
@@ -71,6 +71,31 @@ GetVariable(VariableSpace space, const char *name)
if (strcmp(current->name, name) == 0)
{
/* this is correct answer when value is NULL, too */
+
+ return current->value;
+ }
+ }
+
+ return NULL;
+}
+
+const char *
+GetVariableOrFileRef(VariableSpace space, const char *name, bool *is_file_ref)
+{
+ struct _variable *current;
+
+ if (!space)
+ return NULL;
+
+ for (current = space->next; current; current = current->next)
+ {
+ if (strcmp(current->name, name) == 0)
+ {
+ /* this is correct answer when value is NULL, too */
+
+ if (is_file_ref)
+ *is_file_ref = current->is_file_ref;
+
return current->value;
}
}
@@ -178,7 +203,7 @@ PrintVariables(VariableSpace space)
for (ptr = space->next; ptr; ptr = ptr->next)
{
if (ptr->value)
- printf("%s = '%s'\n", ptr->name, ptr->value);
+ printf("%s = %s'%s'\n", ptr->name, ptr->is_file_ref ? "^" : "", ptr->value);
if (cancel_pressed)
break;
}
@@ -218,6 +243,47 @@ SetVariable(VariableSpace space, const char *name, const char *value)
/* not present, make new entry */
current = pg_malloc(sizeof *current);
current->name = pg_strdup(name);
+ current->is_file_ref = false;
+ current->value = pg_strdup(value);
+ current->assign_hook = NULL;
+ current->next = NULL;
+ previous->next = current;
+ return true;
+}
+
+bool
+SetFileRef(VariableSpace space, const char *name, const char *value)
+{
+ struct _variable *current,
+ *previous;
+
+ if (!space)
+ return false;
+
+ if (!valid_variable_name(name))
+ return false;
+
+ if (!value)
+ return DeleteVariable(space, name);
+
+ for (previous = space, current = space->next;
+ current;
+ previous = current, current = current->next)
+ {
+ if (strcmp(current->name, name) == 0)
+ {
+ /* found entry, so update */
+ if (current->value)
+ free(current->value);
+ current->value = pg_strdup(value);
+ return true;
+ }
+ }
+
+ /* not present, make new entry */
+ current = pg_malloc(sizeof *current);
+ current->is_file_ref = true;
+ current->name = pg_strdup(name);
current->value = pg_strdup(value);
current->assign_hook = NULL;
current->next = NULL;
diff --git a/src/bin/psql/variables.h b/src/bin/psql/variables.h
index d7a05a1..8b24441 100644
--- a/src/bin/psql/variables.h
+++ b/src/bin/psql/variables.h
@@ -26,6 +26,7 @@ struct _variable
{
char *name;
char *value;
+ bool is_file_ref;
VariableAssignHook assign_hook;
struct _variable *next;
};
@@ -34,6 +35,7 @@ typedef struct _variable *VariableSpace;
VariableSpace CreateVariableSpace(void);
const char *GetVariable(VariableSpace space, const char *name);
+const char *GetVariableOrFileRef(VariableSpace space, const char *name, bool *is_file_ref);
bool ParseVariableBool(const char *value, const char *name);
int ParseVariableNum(const char *val,
@@ -49,6 +51,7 @@ int GetVariableNum(VariableSpace space,
void PrintVariables(VariableSpace space);
bool SetVariable(VariableSpace space, const char *name, const char *value);
+bool SetFileRef(VariableSpace space, const char *name, const char *value);
bool SetVariableAssignHook(VariableSpace space, const char *name, VariableAssignHook hook);
bool SetVariableBool(VariableSpace space, const char *name);
bool DeleteVariable(VariableSpace space, const char *name);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
