| From: | Frank Foerster <ivaypoint(at)gmail(dot)com> | 
|---|---|
| To: | pgsql-general(at)postgresql(dot)org | 
| Subject: | Function not inserting rows | 
| Date: | 2017-08-23 15:23:15 | 
| Message-ID: | CAJfZCxA1z=H8k5pAX5ZyW-FLLYGtppR_cSQJLVCLBHSNGSh1Tw@mail.gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | David G. Johnston | 2017-08-23 16:04:47 | Re: Function not inserting rows | 
| Previous Message | Igor Korot | 2017-08-23 13:54:57 | Re: What is the proper query |