Dynamically-generated table-name in PostgreSQL COPY command

Question

This PostgreSQL COPY command works:

copy tablename from E'c:\\abc\\a.txt';

but I want the tablename to be dynamically generated. How can I do this?

Answer 1

You need to build a string, concatenating in the dynamic table name, and then use execute. Note that you escape the ' by ''. This also includes a dynamic name to save the file too. You need to replace savedir with the actual directory you are using.

CREATE OR REPLACE FUNCTION dynamicCopy(tablename text, outname text) RETURNS VOID AS $$

DECLARE STATEMENT TEXT;

BEGIN

  STATEMENT := 'COPY (select * from ' || quote_ident(tablename) || ') to ''savedir' || outname ||'.txt''';

  EXECUTE STATEMENT;

END;

$$ LANGUAGE 'plpgsql';

EDIT:

Since I first wrote this, I have discovered the format function, which I think is generally easier to read than SQL generated with the concatenation operator || and more flexible.

CREATE OR REPLACE FUNCTION dynamicCopy(tablename text, outname text) RETURNS VOID AS 
$BODY$
BEGIN
  EXECUTE FORMAT('COPY (SELECT * FROM %s) TO ''savedir%s.csv''',
                  tablename, 
                  outname);
END
$BODY$ 
LANGUAGE plpgsql;

See the official docs for a full discussion: https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN