Re: Error at dynamic generated copy... - Mailing list pgsql-general

On 08/12/2016 07:11 AM, Edmundo Robles wrote:

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.

The above seems to be the real problem.

Can you describe more what you are doing when you INSERT?

Regards and thanks in advance.

--
Adrian Klaver
adrian.klaver@aklaver.com