Re: Getting return value from .callproc

psql postgres
gives err:
psql; FATAL: role "root" does not exist. (as root)
psql; FATAL: role "user" does not exist. (as user)

thx for the sample / guide in execute( UDF ) and fetchone.
most samples of execute are SQL statements, I have to see UDF samples.

On Tue, Jun 28, 2016 at 12:40 PM, Jonathan Rogers <jrogers(at)socialserve(dot)com>
wrote:

> 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(at)emphasys-software(dot)com
>
> --
> Jonathan Rogers
> Socialserve.com by Emphasys Software
> jrogers(at)emphasys-software(dot)com
>
>
> --
> Sent via psycopg mailing list (psycopg(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/psycopg
>