Re: Does PLPythonU support COPY table FROM string?

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

--
Adrian Klaver
adrian.klaver@aklaver.com