Search Postgresql Archives

Error at dynamic generated copy...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi!
 I hope you could help me... 
I  tried to generate the next copy instruction  in a function:

copy tablefoo (id_foo, foo_name, foo_lastname, foo_age) FROM stdin  DELIMITER as  '|' ;
12060157|John|Doe|33
\.


** The commands to generate  the copy are:
    CREATE OR REPLACE FUNCTION insert_tablefoo(id integer, name varchar, lastname varchar, age integer)

    ... declarations ...


    qry=format('copy tablefoo (id_foo, foo_name, foo_lastname, foo_age) FROM stdin  DELIMITER as  '||chr(39) || '|' || chr(39) || ';' || chr(13) ||'%s|%s|%s|%s' || chr(13) || '\.',id,name,lastname,age);

   execute(qry);


   ... more declarations 
   end <- end function, returns  an integer.


** then,  i  call the function:
    select insert_tablefoo(12321,'John','Doe',33);

**and get the next error:

ERROR:  syntax error at or near "12321"
LINE 2: 12321|John|Doe|33

********** Error **********


after many tests, the problem is concatenate the newline, because  if  the instruction generated is copied to a file and insert manually the newline the query works well.

i tried  with chr(10),  '\n', '\r', split  the qry string ( execute qry_copy || E'\\n' || qry_data || E'\\n\\.')
but always get the same error  :( 



ERROR: syntax error at or near "12321"
SQL state: 42601




**  By the way, i tried to replace an insert  with copy  because  the insert takes  more than 3 minutes to insert a single record.


Regards and thanks in advance.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux