Postgres Dynamic COPY Statement

Question

I'm trying to run a query that does a foreach of each element of an array, and then copies this to a directory with a custom named file. Here's the code I have presently:

DO
$do$
declare
    x varchar;
    y varchar;
    arr varchar[] := array['item1','item2'];
begin
    foreach x in array arr
    loop
        y := concat('C:\Outputs\output_' , x , '.csv');
        copy (select * from pdtable where pdtable.area = x) to y With CSV;
    end loop;
end
$do$

However this encounters an error in the copy statment at the to y statement. Is there an alternative way to do this?

Answer 1

https://www.postgresql.org/docs/current/static/functions-string.html

DO
$do$
declare
    x varchar;
    y varchar;
    arr varchar[] := array['item1','item2'];
begin
    foreach x in array arr
    loop
        y := concat('C:\Outputs\output_' , x , '.csv');
        execute format('copy (select * from pdtable where pdtable.area = %L) to %L With CSV',x,y);
    end loop;
end
$do$