Thread: Add partial :-variable expansion to psql \copy

Fabien COELHO <coelho@cri.ensmp.fr> writes:
> I've been biten by psql's \copy lack of variable expansion, in a limited-access docker-inside-VM context where COPY
isnot a viable option and hardwired names are not desirable. The attached patch allows \copy to use variable's values
inplace of table and file names: 

Hm ... I'm on board with the general idea of the feature, but I find
this implementation quite horrid.  I would rather see us adjust the
lexing rules in psqlscanslash.l so that variable expansion happens
there when collecting \copy arguments.  This would eliminate at
least part of the distinction between OT_WHOLE_LINE and OT_NORMAL
modes, and we'd have to have some discussion about how far to go
there.  Or maybe just change exec_command_copy to use OT_NORMAL
not OT_WHOLE_LINE?  If we modify the behavior of OT_WHOLE_LINE
then the ability to expand variables would start to apply in the
other commands using that, notably \!.  I think that's at least
potentially good, but perhaps the blast radius of such a change
is too large.

Anyway, my feeling about it is that \copy parsing is a huge hack
right now, and I'd rather see it become less of a hack, that is
more like other psql commands, instead of getting even hackier.

            regards, tom lane

On 31/03/2025 17:09, Tom Lane wrote:

Fabien COELHO <coelho@cri.ensmp.fr> writes:

[...] The attached patch allows \copy to use variable's values in place of table and file names:

Hm ... I'm on board with the general idea of the feature, but I find
this implementation quite horrid.

Indeed, I just added the stuff in the already quite peculiar manual lexer/parser for \copy. I did not think of addressing the why it is like that issue and try to fix it :-)

  I would rather see us adjust the
lexing rules in psqlscanslash.l so that variable expansion happens
there when collecting \copy arguments.  This would eliminate at
least part of the distinction between OT_WHOLE_LINE and OT_NORMAL
modes, and we'd have to have some discussion about how far to go
there.  Or maybe just change exec_command_copy to use OT_NORMAL
not OT_WHOLE_LINE?  If we modify the behavior of OT_WHOLE_LINE
then the ability to expand variables would start to apply in the
other commands using that, notably \!.  I think that's at least
potentially good, but perhaps the blast radius of such a change
is too large.

I'm not sure that such \copy salvage to using lex is easy because:

(1) it seems that is the only command which is really full SQL hidden in a backslash command

(2) on one line without requiring a final ';',

(3) the client needs to actually parse it and modify it to some degree before sending it to the server.

so the implication for trying to maintain compatibility without adding weirdness seem slim.

Anyway, my feeling about it is that \copy parsing is a huge hack
right now,

 and I'd rather see it become less of a hack, that is
more like other psql commands, instead of getting even hackier.

I think that I'll have a try with Corey suggestion to extend COPY rather than change \copy.

--

Fabien.

Hello Corey,

If we could do this:

    COPY :"myschema".:"mytable" FROM STDIN \g < :"myfilename"

that would fit our patterns most cleanly, but we would probably create a parsing hassle for ourselves if we ever wanted to mix pipe-to with pipe-from. It would also require checking on every command, when uploaded \copy commands make up a very small percentage of commands issued. So I don't think there's a good way around the asymmetry of COPY TO being a regular \g-able command, whereas COPY FROM will always require some other send-command.

Perhaps we create a new command \copyfrom:

    COPY :"myschema".:"mytable" :options FROM STDIN \copyfrom :"myfilename"

    COPY :"myschema".:"mytable" :options FROM STDIN \copyfrom :"my_complex_command" |

If we had something like that we might be able to replace all existing uses of \copy.

Indeed, I like the idea of extending psql handling of COPY rather than trying to salvage \copy. I do not like that it is probably more work for significantly larger patch.

There are 4 cases to address: input/output cross join file/program, and as you pointed out the output ones are already handled.

I'm hesitating about the right syntax, though, for an input backslash command which in effect would really only apply to COPY? ISTM that \g* is used for "go", i.e. a semi-colon replacement which executes the SQL, and we should want the same thing, which suggests:

COPY "foo" FROM STDIN \gi filename

COPY "foo" FROM STDIN \gi command...|

Another drawback is that it creates an error path:

COPY "foo" FROM 'server-side-file' \gi 'client-side-file'

--

Fabien.

Re: Fabien Coelho
> (1) it seems that is the only command which is really full SQL hidden in a
> backslash command

Perhaps this form could be improved by changing `\copy (select) to file`
to something like `select \gcopy (to file)`. That might make :expansion
in the "select" part easier to handle.

I've heard several complaints that `\copy (select)` can't be wrapped
over several lines, so offering the alternative syntax in parallel to
\copy would also solve another problem.

Christoph

    Christoph Berg wrote:

> Perhaps this form could be improved by changing `\copy (select) to file`
> to something like `select \gcopy (to file)`. That might make :expansion
> in the "select" part easier to handle.

In this direction (COPY TO), it was already taken care of by
commit 6d3ede5f1c654f923b2767b0b0c3b09569adaa18  [1]
a few years ago.

That is, the following sequence already works fine:

\set filename '/tmp/foo'
\set column relname
\set table pg_class

COPY (select :"column" from :"table") TO STDOUT \g :filename

It's also mentioned in the manual through that paragraph in \copy
"
Tip
  Another way to obtain the same result as \copy ... to is to use the
  SQL COPY ... TO STDOUT command and terminate it with \g filename or
  \g |program. Unlike \copy, this method allows the command to span
  multiple lines; also, variable interpolation and backquote expansion
  can be used.
"

[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6d3ede5f1c654f923b2767b0b0c3b09569adaa18


Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/