Always better with a file attached :-/
Sorry for the noise.
--
Fabien.
________________________________
De : Fabien COELHO <[email protected]>
Envoyé : lundi 31 mars 2025 13:48
À : PostgreSQL Hackers <[email protected]>
Objet : Add partial :-variable expansion to psql \copy
Hello,
I've been biten by psql's \copy lack of variable expansion, in a limited-access
docker-inside-VM context where COPY is not a viable option and hardwired names
are not desirable. The attached patch allows \copy to use variable's values in
place of table and file names:
```psql
\set table 'some table'
\set input 'some file name.csv'
\copy :"table" from :'input' with (format csv)
```
--
Fabien.
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index f7c8bc16a7f..445443613bc 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1165,8 +1165,8 @@ SELECT $1 \parse stmt1
Because of this, special parsing rules apply to the <command>\copy</command>
meta-command. Unlike most other meta-commands, the entire remainder
of the line is always taken to be the arguments of <command>\copy</command>,
- and neither variable interpolation nor backquote expansion are
- performed in the arguments.
+ variable interpolation is restricted to the table and file names only,
+ and backquote expansion is not performed in the arguments.
</para>
<tip>
@@ -1178,7 +1178,9 @@ SELECT $1 \parse stmt1
or <literal>\g |<replaceable>program</replaceable></literal>.
Unlike <literal>\copy</literal>, this method allows the command to
span multiple lines; also, variable interpolation and backquote
- expansion can be used.
+ expansion can be used; however, <literal>COPY</literal> interacts
+ with the server-side file system, so it does not work with a remote
+ connection and client-side files.
</para>
</tip>
diff --git a/src/bin/psql/copy.c b/src/bin/psql/copy.c
index 92c955b637a..aa030c33d5f 100644
--- a/src/bin/psql/copy.c
+++ b/src/bin/psql/copy.c
@@ -104,7 +104,7 @@ parse_slash_copy(const char *args)
result->before_tofrom = pg_strdup(""); /* initialize for appending */
token = strtokx(args, whitespace, ".,()", "\"",
- 0, false, false, pset.encoding);
+ 0, false, false, true, pset.encoding);
if (!token)
goto error;
@@ -113,7 +113,7 @@ parse_slash_copy(const char *args)
{
xstrcat(&result->before_tofrom, token);
token = strtokx(NULL, whitespace, ".,()", "\"",
- 0, false, false, pset.encoding);
+ 0, false, false, true, pset.encoding);
if (!token)
goto error;
}
@@ -128,7 +128,8 @@ parse_slash_copy(const char *args)
xstrcat(&result->before_tofrom, " ");
xstrcat(&result->before_tofrom, token);
token = strtokx(NULL, whitespace, "()", "\"'",
- nonstd_backslash, true, false, pset.encoding);
+ nonstd_backslash, true, false, false,
+ pset.encoding);
if (!token)
goto error;
if (token[0] == '(')
@@ -141,7 +142,7 @@ parse_slash_copy(const char *args)
xstrcat(&result->before_tofrom, " ");
xstrcat(&result->before_tofrom, token);
token = strtokx(NULL, whitespace, ".,()", "\"",
- 0, false, false, pset.encoding);
+ 0, false, false, false, pset.encoding);
if (!token)
goto error;
@@ -154,12 +155,12 @@ parse_slash_copy(const char *args)
/* handle schema . table */
xstrcat(&result->before_tofrom, token);
token = strtokx(NULL, whitespace, ".,()", "\"",
- 0, false, false, pset.encoding);
+ 0, false, false, false, pset.encoding);
if (!token)
goto error;
xstrcat(&result->before_tofrom, token);
token = strtokx(NULL, whitespace, ".,()", "\"",
- 0, false, false, pset.encoding);
+ 0, false, false, false, pset.encoding);
if (!token)
goto error;
}
@@ -172,7 +173,7 @@ parse_slash_copy(const char *args)
xstrcat(&result->before_tofrom, " ");
xstrcat(&result->before_tofrom, token);
token = strtokx(NULL, whitespace, "()", "\"",
- 0, false, false, pset.encoding);
+ 0, false, false, false, pset.encoding);
if (!token)
goto error;
if (token[0] == ')')
@@ -181,7 +182,7 @@ parse_slash_copy(const char *args)
xstrcat(&result->before_tofrom, " ");
xstrcat(&result->before_tofrom, token);
token = strtokx(NULL, whitespace, ".,()", "\"",
- 0, false, false, pset.encoding);
+ 0, false, false, false, pset.encoding);
if (!token)
goto error;
}
@@ -193,9 +194,9 @@ parse_slash_copy(const char *args)
else
goto error;
- /* { 'filename' | PROGRAM 'command' | STDIN | STDOUT | PSTDIN | PSTDOUT } */
+ /* { 'filename' | :varname | PROGRAM 'command' | STDIN | STDOUT | PSTDIN | PSTDOUT } */
token = strtokx(NULL, whitespace, ";", "'",
- 0, false, false, pset.encoding);
+ 0, false, false, true, pset.encoding);
if (!token)
goto error;
@@ -204,7 +205,7 @@ parse_slash_copy(const char *args)
int toklen;
token = strtokx(NULL, whitespace, ";", "'",
- 0, false, false, pset.encoding);
+ 0, false, false, true, pset.encoding);
if (!token)
goto error;
@@ -242,7 +243,7 @@ parse_slash_copy(const char *args)
/* Collect the rest of the line (COPY options) */
token = strtokx(NULL, "", NULL, NULL,
- 0, false, false, pset.encoding);
+ 0, false, false, false, pset.encoding);
if (token)
result->after_tofrom = pg_strdup(token);
diff --git a/src/bin/psql/stringutils.c b/src/bin/psql/stringutils.c
index f613413553e..41ecfc669fd 100644
--- a/src/bin/psql/stringutils.c
+++ b/src/bin/psql/stringutils.c
@@ -30,6 +30,7 @@
* e_strings - if true, treat E'...' syntax as a valid token
* del_quotes - if true, strip quotes from the returned token, else return
* it exactly as found in the string
+ * variable - if true, allow token from :-variable
* encoding - the active character-set encoding
*
* Characters in 'delim', if any, will be returned as single-character
@@ -56,8 +57,10 @@ strtokx(const char *s,
char escape,
bool e_strings,
bool del_quotes,
+ bool variable,
int encoding)
{
+ static size_t storage_size = 0; /* allocated size of storage */
static char *storage = NULL; /* store the local copy of the users
* string here */
static char *string = NULL; /* pointer into storage where to continue on
@@ -77,7 +80,8 @@ strtokx(const char *s,
* tokens. 2X the space is a gross overestimate, but it's unlikely
* that this code will be used on huge strings anyway.
*/
- storage = pg_malloc(2 * strlen(s) + 1);
+ storage_size = 2 * strlen(s) + 1;
+ storage = pg_malloc(storage_size);
strcpy(storage, s);
string = storage;
}
@@ -96,9 +100,86 @@ strtokx(const char *s,
free(storage);
storage = NULL;
string = NULL;
+ storage_size = 0;
return NULL;
}
+ /* handle a :-variable name */
+ if (variable && *start == ':')
+ {
+ char *varname = start + 1;
+ char *value;
+ char *next;
+ char qc = '\0';
+
+ /* :'var' or :"var" */
+ if (quote && strchr(quote, *varname))
+ {
+ qc = *varname;
+ p = varname = varname + 1;
+
+ while (*p && *p != qc)
+ p++;
+
+ /* no end quote, parse error */
+ if (! *p)
+ return NULL;
+
+ /* end quote is end of variable name */
+ *p = '\0';
+
+ next = p+1;
+ }
+ else /* :var */
+ {
+ p = varname;
+
+ /* find end-of variable */
+ while (*p && strchr(whitespace, *p))
+ p++;
+
+ if (*p) /* space */
+ {
+ *p = '\0';
+ next = p+1;
+ }
+ else /* storage end there */
+ next = p;
+ }
+
+ value = psql_get_variable(varname,
+ qc == '\'' ? PQUOTE_SQL_LITERAL :
+ qc == '"' ? PQUOTE_SQL_IDENT :
+ PQUOTE_PLAIN,
+ NULL);
+
+ if (!value) /* unexpected undefined variable, parse error */
+ return NULL;
+
+ /* deal with malloc'ed value by embedding it in storage */
+ if (strlen(string) < strlen(value))
+ {
+ int shift = strlen(value) - strlen(start);
+ int string_offset = string - storage;
+ int next_offset = next - storage;
+ int remainder = storage_size - next_offset;
+
+ storage_size += shift;
+ storage = pg_realloc(storage, storage_size);
+ string = storage + string_offset;
+ next = storage + next_offset;
+ memmove(next + shift, next, remainder);
+ next += shift;
+ }
+
+ strcpy(string, value);
+ free(value);
+ start = string;
+ string = next;
+
+ return start;
+ }
+
/* test if delimiter character */
if (delim && strchr(delim, *start))
{
diff --git a/src/bin/psql/stringutils.h b/src/bin/psql/stringutils.h
index 5b91e964103..560139dc2f0 100644
--- a/src/bin/psql/stringutils.h
+++ b/src/bin/psql/stringutils.h
@@ -17,6 +17,7 @@ extern char *strtokx(const char *s,
char escape,
bool e_strings,
bool del_quotes,
+ bool variable,
int encoding);
extern void strip_quotes(char *source, char quote, char escape, int encoding);
diff --git a/src/bin/psql/t/001_basic.pl b/src/bin/psql/t/001_basic.pl
index 739cb439708..70eb35d6bbc 100644
--- a/src/bin/psql/t/001_basic.pl
+++ b/src/bin/psql/t/001_basic.pl
@@ -364,6 +364,21 @@ psql_like(
3|test|2022-07-05 00:00:00/,
'\copy from with DEFAULT');
+# Test copy with :-variables, reusing previous table
+my $more_csv_data = "$tempdir/copy more data.csv";
+append_to_file($more_csv_data, "4,Susie,1970-10-14\n");
+append_to_file($more_csv_data, "5,Hobbes,2020-07-29\n");
+
+psql_like(
+ $node,
+ "TRUNCATE copy_default;
+ \\set table copy_default
+ \\set input '$more_csv_data'
+ \\copy :\"table\" from :'input' with (format 'csv')
+ SELECT * FROM :table WHERE id = 5;",
+ qr/5|Hobbes|2020-07-29/,
+ '\copy using :-variables');
+
# Check \watch
# Note: the interval value is parsed with locale-aware strtod()
psql_like(
