Error on Dynamic csv file export using plpgsql copy to csv in a function

Question

I am trying to filter a postgresql table for rows that have a product id as a foreign key. For each product id, I need to export 1 csv each to a folder eg , prod1.csv, prod2.csv etc. I have tried to create the function below to automate this but the function is failing when I run it. Can anyone kindly assist me to fix the function or recommend a better approach?

CREATE or replace FUNCTION exportdata() 
RETURNS SETOF record AS
$$
DECLARE
 rec text;
BEGIN
 FOR rec IN 
(
Select distinct t.products from trndailyprices as t --Get the list of products in the table
) 
 LOOP
    Copy (
    Select * from trndailyprices as t
    where t.products = rec   ---1st record is product1
    order by t.effectivedate) 
    To 'C:/testQ/' ||rec || '.csv' With CSV;---expected file is product1.csv for 1st record
 END LOOP;
END;
$$ LANGUAGE plpgsql;

Possible duplicates: stackoverflow.com/questions/20636947/…, stackoverflow.com/questions/20476110/…, stackoverflow.com/questions/16019508/… — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 28, 2015 at 1:43

Vivek S. · Accepted Answer · 2015-04-27 09:01:13Z

3

Try this

CREATE or replace FUNCTION exportdata() 
RETURNS void AS -- use void because you're not returning anything 
$$
DECLARE
 rec text;
BEGIN
 FOR rec IN 
Select distinct t.products from trndailyprices as t 
 LOOP
EXECUTE -- you need to use EXECUTE Keyword 
    format('Copy (
    Select * from trndailyprices as t
    where t.products =''%s''
    order by t.effectivedate) 
    To ''C:/testQ/%s.csv'' With CSV;',rec,rec);
 END LOOP;
END;
$$ LANGUAGE plpgsql;

Modified :

create or replace function exportdata_1() returns void as 
$$
declare
rec record;
begin
for rec in
select format('copy(select * from trndailyprices where products=''%s'') to ''%s'' with csv',product,'D:/testQ/'||product||'.csv;') scr from(
Select distinct products from trndailyprices )t
loop
execute rec.scr;
end loop;
end;
$$
language plpgsql

edited Apr 27, 2015 at 9:01

answered Apr 27, 2015 at 8:17

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

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

1 Comment

Avagut Over a year ago

Thank you very much for your quick answer, it has worked exactly as needed. I had not thought of placeholders! :-)

Collectives™ on Stack Overflow

Error on Dynamic csv file export using plpgsql copy to csv in a function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related