COPY csv using custom filename path

Question

I'm getting some issues while trying to export a query to CSV using the COPY function.

The COPY runs ok and exports the query successfully if not using custom filenames on the TO.

The issue is related to add a "datestamp" (kinda) to the filename created.

declare var1 varchar(25);
DECLARE STATEMENT TEXT;
select into var1 current_date -1;
STATEMENT := 'COPY (SELECT * from myTable) To ''E'C:\\Exports\\export_'||var1||'.csv' ''With CSV';
EXECUTE STATEMENT;

In this case, var1 gets a value like 2013-12-16 and I need to add that to the filename to obtain export_2012-12-16.csv

I'm assuming that the ' are misplaced. I've tried several combinations without success and off course the error is ERROR: syntax error at or near "C".

score 1 · Accepted Answer · edited May 23 '17 at 12:14

plpgsql code could work like this:

...
DECLARE
   var1 text;
BEGIN
var1 := to_char(current_date - 1, 'YYYY-MM-DD');
EXECUTE $$COPY (SELECT * from myTable)
          TO E'C:\\Exports\\export_$$ || var1 || $$.csv' WITH CSV$$;
...

Your quotes got tangled. Using dollar-quoting to simplify. Note that syntax highlighting here on SO is misleading because it does not understand dollar-quoting.

DECLARE is only needed once (not an error though). Plus, BEGIN was missing.

And to_char() makes the text representation of a date independent of the locale.

COPY csv using custom filename path

1 Answers1

Linked