Re: Error at dynamic generated copy...

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(dot)klaver(at)aklaver(dot)com