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);
The secondary problem.
Could not the above be written as:
copy tablefoo (id_foo, foo_name, foo_lastname, foo_age) FROM stdin
DELIMITER as '|' ;
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.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
