On Fri, Sep 29, 2017 at 11:54 AM, David G. Johnston < david.g.johns...@gmail.com> wrote:
> On Fri, Sep 29, 2017 at 9:27 AM, Alexander Stoddard < > alexander.stodd...@gmail.com> 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!
