Use function variable in dynamic COPY statement

Question

According to docs of PostgreSQL it is possible to copy data to csv file right from a query without using an intermediate table. I am curious how to do that.

CREATE OR REPLACE FUNCTION m_tbl(my_var integer)
    RETURNS void AS
$BODY$
DECLARE
BEGIN

   COPY (
       select my_var
   )
   TO 'c:/temp/out.csv';

END;
$$ LANGUAGE plpgsql;

I get an error: no such column 'my_var'.

Answer 1

Yes, it is possible to COPY from any query, whether or not it refers to a table.

However, COPY is a non-plannable statement, a utility statement. It doesn't support query parameters - and query parameters are how PL/PgSQL implements the insertion of variables into statements.

So you can't use PL/PgSQL variables with COPY.

You must instead use dynamic SQL with EXECUTE. See the Pl/PgSQL documentation for examples. There are lots of examples here on Stack Overflow and on https://dba.stackexchange.com/ too.

Something like:

EXECUTE format('
   COPY (
       select %L
   )
   TO ''c:/temp/out.csv'';
', my_var);

The same applies if you want the file path to be dynamic - you'd use:

EXECUTE format('
   COPY (
       select %L
   )
   TO %L;
', my_var, 'file_name.csv');

It also works for dynamic column names but you would use %I (for identifier, like "my_name") instead of %L for literal like 'my_value'. For details on %I and %L, see the documentation for format.