Thread: Getting return value from .callproc

On 06/28/2016 12:05 AM, Larry Sevilla wrote:
> 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)

Why are you running psql via sudo? You should only need to run "psql
postgres".

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

Return values from Postgres functions are just like other expression
values. The most straightforward way to retrieve your function's return
value would be something like this:

cur.execute('select personnelinsert(%s, %s)',[34567,'Galilei, Galileo'])
retval = cur.fetchone()[0]

Read the manual for more about retrieval methods:
http://initd.org/psycopg/docs/cursor.html#fetch


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

I've never messed with OUT parameters or callproc, but the manual seems
pretty clear:

http://initd.org/psycopg/docs/cursor.html?highlight=callproc#cursor.callproc


--
Jonathan Rogers
Socialserve.com by Emphasys Software
jrogers@emphasys-software.com

--
Jonathan Rogers
Socialserve.com by Emphasys Software
jrogers@emphasys-software.com

On 06/28/2016 12:05 AM, Larry Sevilla wrote:
> 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)

Why are you running psql via sudo? You should only need to run "psql postgres".

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

Return values from Postgres functions are just like other expression values. The most straightforward way to retrieve
yourfunction's return value would be something like this: 

cur.execute('select personnelinsert(%s, %s)',[34567,'Galilei, Galileo'])
retval = cur.fetchone()[0]

Read the manual for more about retrieval methods:
http://initd.org/psycopg/docs/cursor.html#fetch


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

I've never messed with OUT parameters or callproc, but the manual seems pretty clear:

http://initd.org/psycopg/docs/cursor.html?highlight=callproc#cursor.callproc


--
Jonathan Rogers
Socialserve.com by Emphasys Software
jrogers@emphasys-software.com

On 06/27/2016 09:58 PM, Larry Sevilla wrote:
> psql postgres
> gives err:
> psql; FATAL: role "root" does not exist.  (as root)
> psql; FATAL: role "user" does not exist. (as user)

If you do not specify the -U switch(user) for psql it will use the
system user that you are logged in as. If that user does not have a
Postgres role then you get the above. To avoid the above and get the
behavior you are getting with sudo, do the following as any system user:

psql -U postgres -d postgres

See:
https://www.postgresql.org/docs/9.4/static/app-psql.html

Usage
Connecting to a Database

By the way the -d postgres means you are connecting to one of the
Postgres 'system' databases. That is not really where you want to create
objects. I would suggest creating you own database in the cluster for
doing your work.

https://www.postgresql.org/docs/9.4/static/sql-createdatabase.html


>
>     >
>     >
>     > 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?
>
>     Return values from Postgres functions are just like other expression
>     values. The most straightforward way to retrieve your function's return
>     value would be something like this:
>
>     cur.execute('select personnelinsert(%s, %s)',[34567,'Galilei, Galileo'])
>     retval = cur.fetchone()[0]

This is what I do also.


--
Adrian Klaver
adrian.klaver@aklaver.com