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?

EXECUTE format(...). Search for "postgres dynamic sql copy". — Craig Ringer
– Craig Ringer, Commented Sep 13, 2017 at 16:09

Vao Tsun · Accepted Answer · 2017-09-13 16:49:40Z

3

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$

edited Sep 13, 2017 at 16:49

answered Sep 13, 2017 at 16:40

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Dynamic COPY Statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related