[GENERAL] Function not inserting rows

Hi,

i have the following question: 

Given an empty database with only schema api_dev in it, a table and a function is created as follows:

CREATE TABLE api_dev.item_texts

(

  item_id integer,

  item_text text

)

WITH (

  OIDS=FALSE

);

CREATE OR REPLACE FUNCTION api_dev.add_texts_to_item(

    p_item_id integer,

    p_item_texts text[])

  RETURNS boolean AS

$BODY$

BEGIN

   insert into api_dev.item_texts( item_id, item_text ) 

   (

select p_item_id, unnest( p_item_texts )

   );

   return true;

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

  

  

When i call this function in pgadmin (3, 1.22.2) like this:

select * from api_dev.add_texts_to_item( 444, array['PGADM1', 'PGADM2'] );

  

i get the true-result and the table will have two rows:

444, PGADM1

444, PGADM2  

  

Now (this is NOT a Python question), when i connect with the same user via Python psycopg2 to the same database via the following function:

  def add_texts_to_item( self, item_id, texts ):

        sql = "select * from api_dev.add_texts_to_item( %s, %s );"

        self.cur_.execute( sql, (doc_id, isins, ) )

        data = self.cur_.fetchone()

        if data is None:

            return None

        return data[0]  

  

I will also get the true result, but nothing is being added. But the SQL-Statement that gets to the DB is identical. When i force a syntax error into the statement to see the actual statement it creates, like this:

        sql = "select * from api_dev.add_texts_to_item( %s, %s ); x x"

i get the following python-error:

psycopg2.ProgrammingError: FEHLER:  Syntaxfehler bei »s«

LINE 1: ...dd_texts_to_item( 1234, ARRAY['PSYCOPG1', 'PSYCOPG2'] ); s s

But the created statement looks syntax-wise identical to the pgadmin-statement (except for the forced error of course):

select * from api_dev.add_texts_to_item( 444, array['PGADM1', 'PGADM2'] );

When i change the return type of the pgsql-function from true to false, i will also get the respective result back in Python, so it is actually calling the psql-function and i can also see it in the logs, but nothing gets inserted.

I noticed this behavior first in a Linux 64 bit / 9.6.3 machine and then reproduced this isolated sample on Windows 7 64 bit/ 9.6.2 version.

Any ideas ?

Thanks