Getting return value from .callproc

From: Larry Sevilla <sevilla(dot)larry(dot)oss(at)gmail(dot)com>
To: psycopg(at)postgresql(dot)org
Subject: Getting return value from .callproc
Date: 2016-06-28 04:05:11
Message-ID: CACgwW8EZ2yq4_i7oDN6ht2NHFUfo8zfxnJbUWQHV93dTU6ryOQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: psycopg

My background is MS SQL and VB.

I'm new to Postgresql, Python & psycopg2.

I have a problem in getting return value from a UDF in PostgreSQL into
Python.

using PGAdmin3.

table created:
-----
CREATE TABLE public.personnel
(
companyid integer NOT NULL,
fullname character varying(100) NOT NULL,
ts timestamp without time zone NOT NULL,
serialid smallint NOT NULL DEFAULT
nextval('personnel_serialid_seq'::regclass),
CONSTRAINT personnel_pkey PRIMARY KEY (serialid),
CONSTRAINT personnel_companyid_key UNIQUE (companyid),
CONSTRAINT personnel_fullname_key UNIQUE (fullname)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.personnel
OWNER TO postgres;
-----

UDF to insert data and return the serialid:
-----
CREATE OR REPLACE FUNCTION public.personnelinsert(
incompanyid integer,
infullname character varying)
RETURNS integer AS
$BODY$
DECLARE
retval integer;
BEGIN
INSERT INTO personnel
(
companyid ,
fullname ,
ts
)
VALUES
(
incompanyid ,
infullname ,
now()
)
RETURNING serialid INTO retval;
RETURN retval;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.personnelinsert(integer, character varying)
OWNER TO postgres;
-----

using PGAdmin's query:
-----
select personnelinsert(12345,'Newton, Isaac');
-----
I got personnelinsert = 1 (ok)

using "sudo -u postgres psql postgres"
-----
select personnelinsert(23456,'Einstein, Albert');
-----
I got personnelinsert = 2 (ok)

But if I use Python with psycopg2
-----
import psycopg2

conn=psycopg2.connect(host='localhost',database='postgres',user='postgres',password='postgres')
cur=conn.cursor()

retval = cur.callproc('personnelinsert',[34567,'Galilei, Galileo'])

conn.commit()

cur.close()
conn.close()

print(retval)
-----
retval = [34567,'Galilei, Galileo']
(I'm expecting retval/serialid value of 3, not the parameters I sent)

Q1: From Python, is there a way I can retrieve the value of
"retval/serialid" of my UDF?

Q2: I'm trying the OUT parameter, but cannot figure out. How can I access
the OUT parameter from Python?

Responses

Browse psycopg by date

  From Date Subject
Next Message Jonathan Rogers 2016-06-28 04:28:27 Re: Getting return value from .callproc
Previous Message Philipp H. v. Loewenfeld 2016-06-26 19:19:15 possible SQL injection problem in ZPsycopgDA