Thread: [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
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 sBut 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'] );
Try adding the following to your function to see what your function sees as being the value of the p_item_texts argument.
RAISE ERROR '%', p_item_texts;
I'm assuming that:
self.cur_.execute( sql, (doc_id, isins, ) )
performs dynamic string substitution as opposed to generating a prepared statement. The placeholder values supplied to a prepared statement are treated as literals.
You may also want turn on statement logging in the server.
David J.
On Wed, Aug 23, 2017 at 4:23 PM, Frank Foerster <ivaypoint@gmail.com> wrote: > Any ideas ? commit? -- Daniele
Thanks, that was it. I did not commit as i was calling "only" a select-statement.
Thanks
2017-08-23 18:20 GMT+02:00 Daniele Varrazzo <daniele.varrazzo@gmail.com>:
On Wed, Aug 23, 2017 at 4:23 PM, Frank Foerster <ivaypoint@gmail.com> wrote:
> Any ideas ?
commit?
-- Daniele
Hello, On Wed, 2017-08-23 at 17:23 +0200, Frank Foerster wrote: > > > > 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'] ); > > > I don't use python but the traditional way to call a function is:- select api_dev.add_texts_to_item( 444, array['PGADM1','PGADM2'] ); HTH, Rob