Postgres how can I pass variables to Copy command

Here and here you can find two different options (either via a bash script or a sql script with variables) to solve your problem.

Since you are using windows the only viable solution is the one with the variable in the sql file. With Windows you should also be able to use the psql command line utility to execute your sqlfile and pass the path as a parameter like this:

-- Example sql file content:
COPY (SELECT * FROM my_table) TO :path CSV DELIMITER ',' HEADER;

Command line example

psql -f /tmp/file.sql -v path='~/Desktop/' || my_variable || '.csv'

Another option i just found would be to output the content of the csv to stdout and forwarding that to file on the windows cmd level. Example code (based on this answer Linux Example code):

psql -c "COPY (SELECT * FROM my_table) TO STDOUT CSV DELIMITER ',' HEADER;" > '~/Desktop/' || my_variable || '.csv'

EDIT1 based on the new requirement that the variable comes out of the postgresql database:

I quickly built a For loop which can loop over the result of a separate query and then execute a sql query for each of the values in the result:

Example code:

DO $$
  declare
    result record;
BEGIN
    FOR result IN Select * FROM (VALUES ('one'), ('two'), ('three')) AS t (path) LOOP 
        RAISE NOTICE 'path: ~/Desktop/%.csv', test.path;
    END LOOP;
END; $$;

This produces the following output:
NOTICE:  path: ~/Desktop/one.csv
NOTICE:  path: ~/Desktop/two.csv
NOTICE:  path: ~/Desktop/three.csv

You can substitue the Select * FROM (VALUES ('one'), ('two'), ('three')) AS t (path) with any query which produces the table result which contains one path per row.

Second you can substitue the RAISE NOTICE 'path: ~/Desktop/%.csv', test.path; with your copy query

You can use EXECUTE command. A working example is:

sql_copy_command= ' COPY abc_schema_name.xyz_table_name FROM ''' || masterFilePath ||''' DELIMITER '';'' CSV HEADER ' ;

RAISE NOTICE 'sql_copy_command: % ', sql_copy_command; EXECUTE sql_copy_command;