0

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 ...
                                     ^
  • You need to use dynamic SQL: http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN –  Nov 16 '15 at 12:22
  • Thanks for the suggestion...but i already have used this but still error –  Nov 16 '15 at 12:48
  • Not sure what you mean with "*I already have used this*" - there is no dynamic SQL in your question. –  Nov 16 '15 at 12:48
  • execute 'copy quote_ident(' || table_name || ')' || ' from ' || file_path || 'WITH DELIMITER AS ' || ',' || ' CSV QUOTE AS ' || '"'; –  Nov 16 '15 at 12:49
  • Update the question with the new syntax and error message. – Clodoaldo Neto Nov 16 '15 at 13:17
  • Related question: [COPY with dynamic file name](http://stackoverflow.com/questions/16019508/copy-with-dynamic-file-name/16021835) – Erwin Brandstetter Nov 16 '15 at 13:36

0 Answers0