COPY TO (query) allows no function arguments

On 3/10/21 9:48 AM, Färber, Franz-Josef (StMUK) wrote:

it is as simple as the subject says:

The COPY TO (query) statement does not allow the passing of function arguments –
is this by intent or a bug?

This is not a bug -- next time if you are uncertain please try the general list
first.

In any case, you cannot use the passed in argument text as part of a simple SQL
command -- you have to build the command from text and then execute it. For example:

8<------------------------
CREATE TABLE b(id int);
INSERT INTO b VALUES(1),(2),(42);

CREATE OR REPLACE FUNCTION a(myparam text)
RETURNS void LANGUAGE plpgsql AS $func$
BEGIN
EXECUTE $$COPY (SELECT * FROM $$ || myparam || $$) TO '/tmp/a.txt'$$;
END;
$func$;

SELECT a('b');
SELECT pg_read_file('/tmp/a.txt');
pg_read_file
--------------
1 +
2 +
42 +

(1 row)
8<------------------------

HTH,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

On Wed, Mar 10, 2021 at 8:17 AM Joe Conway <mail@joeconway.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.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

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.

COPY is a utility command, so it doesn't accept parameters. Basically
only the big four DML commands (SELECT/INSERT/UPDATE/DELETE) can take
parameters. For everything else, you've got to build a string and
EXECUTE it, as Joe illustrated.

regards, tom lane