Error at dynamic generated copy...

From: Edmundo Robles <edmundo(at)sw-argos(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Error at dynamic generated copy...
Date: 2016-08-12 14:11:26
Message-ID: CAOXzpYCeSSikNRk1Doeq-cE9AefhADwExU4trOpeEmMCKess5Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2016-08-12 14:43:05 Re: Error at dynamic generated copy...
Previous Message Adrian Klaver 2016-08-12 14:06:53 Re: pgbasebackup is failing after truncate