I am using postgresql query to copy data from csv file and insert that data into another table..the query works fine but i want that query to be put into a function so that i can use this function in my c# application. Here is the query which works fine...
copy emp_temp from 'd:\\temp.csv' WITH DELIMITER AS ',' CSV QUOTE AS '"';
here emp_temp is the table and temp.csv is my file.
but
when i am using the above function like
create or replace function CopyToTable(file_path text,table_name text) returns void as
$body$
begin
copy quote_ident(table_name) from file_path WITH DELIMITER AS ',' CSV QUOTE AS '"';
end;
$body$
language plpgsql volatile
It shows error like
ERROR: syntax error at or near "file_path"...
From some of the suggestions i also tried using dynamic sql
create or replace function CopyToTable(file_path text,table_name text) returns void as
$body$
begin
execute 'copy quote_ident(' || table_name || ')' || ' from ' ||
file_path || 'WITH DELIMITER AS ' || ',' || ' CSV QUOTE AS ' || '"';
end;
$body$
language plpgsql volatile
The function compiles successfully...but when i am giving parameters by running the function
select * from CopyToTable('d:\\temp.csv','emp_temp')
it shows error..
ERROR: syntax error at or near "d"
LINE 1: copy quote_ident(emp_temp) from d:\\temp.csvWITH DELIMITER ...
^