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;

Answer 1

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