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?
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 |