1

I am trying to export data from a table to get product wise csv files.I have created a function as below but Postgres throw an error on the variable rec which contains the product when I try to run it. Can anyone assist with troubleshooting my function to find the error? When I was testing it earlier, if I insert the data into a table it works as expected.

CREATE or replace FUNCTION exportdata() 
RETURNS SETOF record AS
$$
DECLARE
 rec text;
BEGIN
 FOR rec IN 
(
Select distinct t.products from trndailyprices as t
) 
 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;
Avagut
  • 924
  • 3
  • 18
  • 34
  • This was answered here: http://stackoverflow.com/questions/29890149/error-on-dynamic-csv-file-export-using-plpgsql-copy-to-csv-in-a-function?noredirect=1#29890564 – Avagut Apr 27 '15 at 08:56

1 Answers1

2
 Copy (Select * from trndailyprices as 
    order by effectivedate) To 'C:/testQ/test.csv' With CSV;

logically your loop is in plus.

valentin
  • 3,498
  • 15
  • 23
  • I am not sure I have understood your answer, I have added more information to the question why I am looping through – Avagut Apr 26 '15 at 00:09