Postgres Pro
Downloads
Home   >   mailing lists

Getting return value from .callproc - Mailing list psycopg

From Larry Sevilla
Subject Getting return value from .callproc
Date
Msg-id CACgwW8EZ2yq4_i7oDN6ht2NHFUfo8zfxnJbUWQHV93dTU6ryOQ@mail.gmail.com
Whole thread Raw
Responses Re: Getting return value from .callproc  (Jonathan Rogers <jrogers@socialserve.com>)
Re: Getting return value from .callproc  (Jonathan Rogers <jrogers@emphasys-software.com>)
List psycopg
Tree view
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?

psycopg by date:

Previous
From: "Philipp H. v. Loewenfeld"
Date:
Subject: possible SQL injection problem in ZPsycopgDA
Next
From: Jonathan Rogers
Date:
Subject: Re: Getting return value from .callproc