On Fri, Sep 29, 2017 at 11:54 AM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
I found what seems to be an odd difference between COPY and \copy parsing.[...]COPY dest_table FROM PROGRAM $$ sed 's/x/y/' | etc... $$To my surprise this worked with COPY but not \COPY which failed with:\copy: parse error at "$$"Is this an undocumented difference? Is this even the appropriate email list to ask this kind of question or report such a difference?This is the correct place for seeking such clarification. The docs cannot cover every possible thing people might do and these lists (-general in particular) are here to fill in the gaps.The negative condition that "psql" itself doesn't understand dollar-quoting is not documented. Dollar-quoting is documented as a server-interpreted SQL Syntax feature and only applies there.While the commands are similar COPY is server-side SQL while \copy is a psql meta-command that psql converts to SQL, executes, obtains the results, and processes. Note that the server would never see "PROGRAM $$" since the server would be unable to access the local program being referred to. The server sees "FROM stdin" and psql feeds the results of the PROGRAM invocation to the server over that pipe.David J.
Thank you, David. That helps makes sense of everything. There is the situation where psql is executed by a non-superuser on the server. But the docs make clear that only STDOUT / STDIN, not not named files or commands are allowed in that case. So I now realize I would just have been trading a parse error for a security one had my dollar-quoting worked with \copy!
