Re: COPY TO (query) allows no function arguments

On Wed, Mar 10, 2021 at 8:17 AM Joe Conway <mail(at)joeconway(dot)com> wrote:

> EXECUTE $$COPY (SELECT * FROM $$ || myparam || $$) TO '/tmp/a.txt'$$;
>

This is not the same command as originally presented, in particular this
uses myparam as an identifier (which can never be a variable) while the
original used myparam as a value (which usually can be a variable). That
said I'm not sure at the moment where exactly the breakdown is that results
in myparam being checked as a column name (and thus not found) instead of
as a variable name (and thus found and replaced with a literal, which
SELECT can handle just fine).

If COPY itself can be parameterized/prepared (I'm unsure and not able to
test right now) then it should be able to accept variables when used in a
function - within the rules for variable substitution.

David J.

As an aside, the format() combined with EXECUTE in pl/pgsql is much more
readable as well as makes preventing SQL injection trivial.