Copy Csv Using Custom Filename Path

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.

Solution 1:

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.