Re: 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