Home Postgres Dynamic COPY Statement
Reply: 1

Postgres Dynamic COPY Statement

gregor
1#
gregor Published in 2017-09-13 15:40:04Z

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?

Vao Tsun
2#
Vao Tsun Reply to 2017-09-13 16:49:40Z

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$
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.31098 second(s) , Gzip On .

© 2016 Powered by cudou.com design MATCHINFO