CODEWITHSUNDEEP
sqlpostgresql
DarkFenix
DarkFenix

Reputation: 746

Does not exist column when creating postgresql function

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;

Upvotes: 0

Views: 374

Answers (2)

Trung Duong
Trung Duong

Reputation: 3475

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');

Upvotes: 2

FancyXun
FancyXun

Reputation: 1298

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;

Upvotes: 0

Related Questions