Does not exist column when creating postgresql function

Question

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;

Trung Duong · Accepted Answer · 2017-04-18 03:08:46Z

2

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

edited Apr 18, 2017 at 3:08

answered Apr 17, 2017 at 6:42

Trung Duong

3,4752 gold badges10 silver badges10 bronze badges

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

1 Comment

DarkFenix Over a year ago

Could you tell me how I concatenate my query with the execute? Because when I do it gives me error

EXECUTE FORMAT('COPY (%L) TO %L WITH CSV HEADER',select nombre,dni,edad from test where id=i, 'C:\Users\Usuario\Documents\user'+i+'.csv');

FancyXun · Accepted Answer · 2017-04-18 00:59:01Z

0

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;

answered Apr 18, 2017 at 0:59

FancyXun

1,2981 gold badge10 silver badges17 bronze badges

Collectives™ on Stack Overflow

Does not exist column when creating postgresql function

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related