Re: Error at dynamic generated copy...

don't worry. I still working in this copy, how can i do to insert
newlines to generate the copy dynamically and avoid the syntax error.

On Fri, Aug 12, 2016 at 11:11 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 08/12/2016 08:15 AM, Edmundo Robles wrote:
>
>> Adrian, your'e right, the real problem is the slow insert, I have
>> many devices reporting to the server and saving their state each
>> minute so there is a moment where i reach the limit of connections and
>> the monitor device send a exception and crash.
>>
>>
>> The table grows a lot, current have more than 13,000,000 records, plus
>> have many indexes, that the reason why is slow to insert That's why i
>> tried to use copy.
>>
>
>
> My COPY example turned out to be a bust, so ignore. Sorry.
>
>
>>
>>
>> On Fri, Aug 12, 2016 at 9:43 AM, Adrian Klaver
>> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>>
>> 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 <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>