Postgresql copy to csv automated error on variable in loop

Question

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;

This was answered here: stackoverflow.com/questions/29890149/… — Avagut
– Avagut, Commented Apr 27, 2015 at 8:56

valentin · Accepted Answer · 2015-04-25 19:59:27Z

2

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

logically your loop is in plus.

answered Apr 25, 2015 at 19:59

valentin

3,62817 silver badges23 bronze badges

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

1 Comment

Avagut Over a year ago

I am not sure I have understood your answer, I have added more information to the question why I am looping through

Collectives™ on Stack Overflow

Postgresql copy to csv automated error on variable in loop

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