0

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;
Vivek S.
  • 19,945
  • 7
  • 68
  • 85
Avagut
  • 924
  • 3
  • 18
  • 34
  • do you have `productid` in table `trndailyprices` ? – Vivek S. Apr 27 '15 at 08:15
  • Possible duplicates: http://stackoverflow.com/questions/20636947/copy-csv-using-custom-filename-path/20639369#20639369, http://stackoverflow.com/questions/20476110/plpgsql-select-statement-in-for-loop-to-create-multiple-csv-files/20476679#20476679, http://stackoverflow.com/questions/16019508/copy-with-dynamic-file-name/16021835#16021835 – Erwin Brandstetter Apr 28 '15 at 01:43

1 Answers1

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
Vivek S.
  • 19,945
  • 7
  • 68
  • 85
  • Thank you very much for your quick answer, it has worked exactly as needed. I had not thought of placeholders! :-) – Avagut Apr 27 '15 at 08:54