I try to create a dynamic function that exports data specific ids controlled by a for, but when I run the function it shows me the error that there is no column i ,
I would like to concatenate the variable i with the name of the output file
CREATE OR REPLACE FUNCTION mifuncion() RETURNS void AS $$
BEGIN
FOR i IN 1..5 LOOP
copy (select nombre,dni,edad from test where id=i) TO 'C:\Users\Usuario\Documents\user'+i+'.csv' WITH CSV HEADER;
END LOOP;
END;
$$ LANGUAGE plpgsql;
COPY doesn't support query parameters, so you can't use PL/PGSQL variables with COPY.
You should use dynamic SQL with EXECUTE instead. You could declare variables to store query statement and file path, and then execute it like this
EXECUTE FORMAT('COPY (%s) TO %L WITH CSV HEADER', your_query, file_path);
OR
EXECUTE FORMAT('COPY (%s) TO %L WITH CSV HEADER', 'SELECT nombre, dni, edad FROM test WHERE id = ' || i, 'C:\Users\Usuario\Documents\user' || i || '.csv');
You could use dynamic SQL with EXECUTE instead,like this
statement :='COPY (select nombre,dni,edad from test where id=' || i || ') to ''C:\Users\Usuario\Documents\user' || i ||'.csv''';
EXECUTE statement;