Re: Getting return value from .callproc

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(dot)klaver(at)aklaver(dot)com