On 08/28/2015 08:32 AM, Dominik Czarnota wrote:
I am launching it from postgres plpythonu function (postgres version: PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit).The error can be reproduced using the SQL below: DROP TABLE IF EXISTS test; CREATE TABLE test(field1 integer, field2 integer); CREATE OR REPLACE FUNCTION example() RETURNS VOID AS $$ plpy.execute("COPY test (field1, field2) FROM STDIN DELIMITER',';\n1,2\n\\.") $$ LANGUAGE 'plpythonu'; select example(); Maybe there is a way to get into the underlying psycopg2 driver and call some method that would do COPY FROM, but I can't find anything related to it in the docs.
The only thing I can think of is using io.BytesIO() to create an in memory file and then reading from that. I have done it using psycopg2, but not in plpythonu so I would mark this untested.
Otherwise, could you explain more where the list is coming from and it's size as there may be other ways to attack this.
2015-08-28 17:15 GMT+02:00 Adrian Klaver <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>>: On 08/28/2015 05:58 AM, Dominik Czarnota wrote: Hello, Is there any possibility to make COPY from list of records in PLPythonU? I've tried to simply call `plpy.execute(query)` with such query: COPY table (field1, field2, field3) FROM STDIN DELIMITER','; val1,val2,val3 \. But it fails with not so explicit error: ProgrammingError: (psycopg2.ProgrammingError) spiexceptions.SyntaxError: syntax error at or near "val1" LINE 2: val1,val2,val3 ^ The above is a psycopg2 error, so where are you running this and how? However the same thing works in psql. After some googling I found out that it doesn't work inside sql functions because there is no STDIN ( http://www.postgresql.org/message-id/000501c5acc9$83747aa0$0501a8c0@plexus ). I've got working solution which was saving tmp file and then calling `COPY table FROM 'path'` but that's not cool. Did I miss something in plpythonu api? If not, where can I post "feature request" for that? Or can you point me place where I could dig into the code and implement it? ---- Dominik Czarnota -- Adrian Klaver adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
-- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
