Thread: plpython return setof and yield
Hi,
I am kind of new into python, and I have been trying to port some plperl functions into plpython, but I've run up into a problem.
Imagine the following plpython code.
CREATE OR REPLACE FUNCTION greet (how text)
RETURNS SETOF greeting
AS $$
rv = plpy.execute("SELECT 1")
for article in range(10) :
for other in range(10) :
if (other == 1) : continue
yield([article,other])
$$LANGUAGE plpythonu;
When executing the function on the psql console I always the this error.
netbo-dev=# select * from greet('Nuno');
ERROR: error fetching next item from iterator
If I comment the first line:
rv = plpy.execute("select 1")
Turning the code into this:
CREATE OR REPLACE FUNCTION greet (how text)
RETURNS SETOF greeting
AS $$
#rv = plpy.execute("SELECT 1")
for article in range(10) :
for other in range(10) :
if (other == 1) : continue
yield([article,other])
$$LANGUAGE plpythonu;
The code works:
netbo-dev=# select * from greet('Nuno');
how | who
-----+-----
0 | 0
0 | 2
0 | 3
0 | 4
0 | 5
0 | 6
I know the example code is not the best, but What I was tryng to do is execute some SQL then process it and return it back.
I also know I could just generate the rows and place them in a list and then return it, but I would like to know why the yield function does'nt work in this case.
This was tried on with:
PostgreSQL 8.3.7 and Python 2.5.1
Thanks,
Nuno Mota
I am kind of new into python, and I have been trying to port some plperl functions into plpython, but I've run up into a problem.
Imagine the following plpython code.
CREATE OR REPLACE FUNCTION greet (how text)
RETURNS SETOF greeting
AS $$
rv = plpy.execute("SELECT 1")
for article in range(10) :
for other in range(10) :
if (other == 1) : continue
yield([article,other])
$$LANGUAGE plpythonu;
When executing the function on the psql console I always the this error.
netbo-dev=# select * from greet('Nuno');
ERROR: error fetching next item from iterator
If I comment the first line:
rv = plpy.execute("select 1")
Turning the code into this:
CREATE OR REPLACE FUNCTION greet (how text)
RETURNS SETOF greeting
AS $$
#rv = plpy.execute("SELECT 1")
for article in range(10) :
for other in range(10) :
if (other == 1) : continue
yield([article,other])
$$LANGUAGE plpythonu;
The code works:
netbo-dev=# select * from greet('Nuno');
how | who
-----+-----
0 | 0
0 | 2
0 | 3
0 | 4
0 | 5
0 | 6
I know the example code is not the best, but What I was tryng to do is execute some SQL then process it and return it back.
I also know I could just generate the rows and place them in a list and then return it, but I would like to know why the yield function does'nt work in this case.
This was tried on with:
PostgreSQL 8.3.7 and Python 2.5.1
Thanks,
Nuno Mota
On Sunday 16 August 2009 5:13:51 pm Nuno Mota wrote:
> Hi,
>
> I am kind of new into python, and I have been trying to port some plperl
> functions into plpython, but I've run up into a problem.
>
> Imagine the following plpython code.
>
> CREATE OR REPLACE FUNCTION greet (how text)
> RETURNS SETOF greeting
> AS $$
> rv = plpy.execute("SELECT 1")
>
> for article in range(10) :
> for other in range(10) :
> if (other == 1) : continue
> yield([article,other])
> $$LANGUAGE plpythonu;
>
> When executing the function on the psql console I always the this error.
>
> netbo-dev=# select * from greet('Nuno');
> ERROR: error fetching next item from iterator
>
> If I comment the first line:
>
> rv = plpy.execute("select 1")
>
> Turning the code into this:
>
> CREATE OR REPLACE FUNCTION greet (how text)
> RETURNS SETOF greeting
> AS $$
> #rv = plpy.execute("SELECT 1")
>
> for article in range(10) :
> for other in range(10) :
> if (other == 1) : continue
> yield([article,other])
> $$LANGUAGE plpythonu;
>
> The code works:
>
> netbo-dev=# select * from greet('Nuno');
> how | who
> -----+-----
> 0 | 0
> 0 | 2
> 0 | 3
> 0 | 4
> 0 | 5
> 0 | 6
>
> I know the example code is not the best, but What I was tryng to do is
> execute some SQL then process it and return it back.
>
> I also know I could just generate the rows and place them in a list and
> then return it, but I would like to know why the yield function does'nt
> work in this case.
>
> This was tried on with:
>
> PostgreSQL 8.3.7 and Python 2.5.1
>
> Thanks,
> Nuno Mota
As a guess, in the first example you have the function creating two result sets
without declaring which one to return. In the second example you are using the
generator alone.
--
Adrian Klaver
aklaver@comcast.net
Thank you very much,
Can i ask one more question ?
How do I declare the correct result set to return, this in first example code, is it possible ?
Thank you again,
Nuno Mota
--
Nuno Mota <nmota@net-bo.com>
Management Solutions for POS
Tlf: 351 225322000
Mob: 351 936272176
Fax: 351 226177662
http://www.net-bo.com
Can i ask one more question ?
How do I declare the correct result set to return, this in first example code, is it possible ?
Thank you again,
Nuno Mota
On Mon, Aug 17, 2009 at 3:21 PM, Adrian Klaver <aklaver@comcast.net> wrote:
As a guess, in the first example you have the function creating two result setsOn Sunday 16 August 2009 5:13:51 pm Nuno Mota wrote:
> Hi,
>
> I am kind of new into python, and I have been trying to port some plperl
> functions into plpython, but I've run up into a problem.
>
> Imagine the following plpython code.
>
> CREATE OR REPLACE FUNCTION greet (how text)
> RETURNS SETOF greeting
> AS $$
> rv = plpy.execute("SELECT 1")
>
> for article in range(10) :
> for other in range(10) :
> if (other == 1) : continue
> yield([article,other])
> $$LANGUAGE plpythonu;
>
> When executing the function on the psql console I always the this error.
>
> netbo-dev=# select * from greet('Nuno');
> ERROR: error fetching next item from iterator
>
> If I comment the first line:
>
> rv = plpy.execute("select 1")
>
> Turning the code into this:
>
> CREATE OR REPLACE FUNCTION greet (how text)
> RETURNS SETOF greeting
> AS $$
> #rv = plpy.execute("SELECT 1")
>
> for article in range(10) :
> for other in range(10) :
> if (other == 1) : continue
> yield([article,other])
> $$LANGUAGE plpythonu;
>
> The code works:
>
> netbo-dev=# select * from greet('Nuno');
> how | who
> -----+-----
> 0 | 0
> 0 | 2
> 0 | 3
> 0 | 4
> 0 | 5
> 0 | 6
>
> I know the example code is not the best, but What I was tryng to do is
> execute some SQL then process it and return it back.
>
> I also know I could just generate the rows and place them in a list and
> then return it, but I would like to know why the yield function does'nt
> work in this case.
>
> This was tried on with:
>
> PostgreSQL 8.3.7 and Python 2.5.1
>
> Thanks,
> Nuno Mota
without declaring which one to return. In the second example you are using the
generator alone.
--
Adrian Klaver
aklaver@comcast.net
--
Nuno Mota <nmota@net-bo.com>
Management Solutions for POS
Tlf: 351 225322000
Mob: 351 936272176
Fax: 351 226177662
http://www.net-bo.com
----- "Nuno Mota" <nmota@net-bo.com> wrote: > Thank you very much, > > Can i ask one more question ? > > How do I declare the correct result set to return, this in first > example code, is it possible ? > > Thank you again, > Nuno Mota > > From here: http://www.postgresql.org/docs/8.3/interactive/plpython-funcs.html "The result is returned from the Python code in the usual way, with return or yield (in case of a result-set statement)." Adrian Klaver aklaver@comcast.net
I have fixed the problem populating a list and returning it.
Well I guess I have to learn a litte bit more of python to understand how to use yield when using multiple result sets.
Thank you anyway
--
Nuno Mota <nmota@net-bo.com>
Management Solutions for POS
Tlf: 351 225322000
Mob: 351 936272176
Fax: 351 226177662
http://www.net-bo.com
Well I guess I have to learn a litte bit more of python to understand how to use yield when using multiple result sets.
Thank you anyway
On Mon, Aug 17, 2009 at 7:23 PM, Adrian Klaver <aklaver@comcast.net> wrote:
From here:
----- "Nuno Mota" <nmota@net-bo.com> wrote:
> Thank you very much,
>
> Can i ask one more question ?
>
> How do I declare the correct result set to return, this in first
> example code, is it possible ?
>
> Thank you again,
> Nuno Mota
>
>
http://www.postgresql.org/docs/8.3/interactive/plpython-funcs.html
"The result is returned from the Python code in the usual way, with return or yield (in case of a result-set statement). "
--
Nuno Mota <nmota@net-bo.com>
Management Solutions for POS
Tlf: 351 225322000
Mob: 351 936272176
Fax: 351 226177662
http://www.net-bo.com
Ok, now I think I understand since I already have a result set from the query I cannot use yield.
The other example works because I am generating only one result set.
--
Nuno Mota <nmota@net-bo.com>
Management Solutions for POS
Tlf: 351 225322000
Mob: 351 936272176
Fax: 351 226177662
http://www.net-bo.com
The other example works because I am generating only one result set.
On Mon, Aug 17, 2009 at 7:55 PM, Nuno Mota <nmota@net-bo.com> wrote:
I have fixed the problem populating a list and returning it.
Well I guess I have to learn a litte bit more of python to understand how to use yield when using multiple result sets.
Thank you anywayOn Mon, Aug 17, 2009 at 7:23 PM, Adrian Klaver <aklaver@comcast.net> wrote:From here:
----- "Nuno Mota" <nmota@net-bo.com> wrote:
> Thank you very much,
>
> Can i ask one more question ?
>
> How do I declare the correct result set to return, this in first
> example code, is it possible ?
>
> Thank you again,
> Nuno Mota
>
>
http://www.postgresql.org/docs/8.3/interactive/plpython-funcs.html
"The result is returned from the Python code in the usual way, with return or yield (in case of a result-set statement). "
--
Nuno Mota <nmota@net-bo.com>Management Solutions for POS
Tlf: 351 225322000
Mob: 351 936272176
Fax: 351 226177662
http://www.net-bo.com
--
Nuno Mota <nmota@net-bo.com>
Management Solutions for POS
Tlf: 351 225322000
Mob: 351 936272176
Fax: 351 226177662
http://www.net-bo.com