Re: COPY vs \COPY FROM PROGRAM $$ quoting difference?

On Fri, Sep 29, 2017 at 11:54 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Fri, Sep 29, 2017 at 9:27 AM, Alexander Stoddard <
> alexander(dot)stoddard(at)gmail(dot)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!