Re: Error at dynamic generated copy... - Mailing list pgsql-general

On 08/12/2016 09:34 AM, Edmundo Robles wrote:
> 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.

I think there is a bigger problem. From one of attempts:

ERROR:  cannot COPY to/from client in PL/pgSQL

>
> On Fri, Aug 12, 2016 at 11:11 AM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>         <mailto: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>
>         <mailto:adrian.klaver@aklaver.com
>         <mailto:adrian.klaver@aklaver.com>>
>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com