Re: Error at dynamic generated copy...

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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com