Re: PL/Python: How do I use result methods?

I'm having my first try at a PL/Python function. It is also a first try
at Python, so the answer to this may be obvious.

According to the docs (8.0):

Calling plpy.execute with a query string and an optional limit
argument causes that query to be run and the result to be
returned in a result object. The result object emulates a list
or dictionary object. The result object can be accessed by row
number and column name. It has these additional methods: nrows
which returns the number of rows returned by the query, and
status which is the SPI_execute() return value.

What exactly is the syntax for using the nrows and status methods?

I am trying to use it like this:

...
arec = plpy.execute(plan, [ addrno ], 1)
plpy.info("Executed plan")
n = plpy.nrows(arec)
plpy.info(" got nrows")
...

This gives the output:
advacs=# select core.formatted_address(1);
INFO: ('Prepared plan',)
INFO: ('Executed plan',)
ERROR: plpython: function "formatted_address" failed
DETAIL: exceptions.AttributeError: 'module' object has no attribute 'nrows'

If I use
n = arec.nrows

the message becomes
DETAIL: exceptions.SystemError: error return without exception set

so how do you use it?

--
Oliver Elphick olly(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"But be ye doers of the word, and not hearers only,
deceiving your own selves." James 1:22

On Sat, Oct 16, 2004 at 03:34:29PM +0100, Oliver Elphick wrote:
>
> According to the docs (8.0):
>
> Calling plpy.execute with a query string and an optional limit
> argument causes that query to be run and the result to be
> returned in a result object. The result object emulates a list
> or dictionary object. The result object can be accessed by row
> number and column name. It has these additional methods: nrows
> which returns the number of rows returned by the query, and
> status which is the SPI_execute() return value.
>
> What exactly is the syntax for using the nrows and status methods?
>
> I am trying to use it like this:
>
> ...
> arec = plpy.execute(plan, [ addrno ], 1)
> plpy.info("Executed plan")
> n = plpy.nrows(arec)

The documentation describes nrows as being a method of the result
object, so I'd guess it should be used thusly:

n = arec.nrows()

I tried that with the following function:

CREATE OR REPLACE FUNCTION test() RETURNS INTEGER AS $$
rv = plpy.execute('SELECT usename FROM pg_user')
n = rv.nrows()
return 1
$$ LANGUAGE plpythonu;

The function failed:

test=> SELECT test();
ERROR: plpython: function "test" failed
DETAIL: exceptions.SystemError: error return without exception set

Digging through plpython.c I discovered the following:

#ifdef NOT_USED
/* Appear to be unused */
static PyMethodDef PLy_result_methods[] = {
{"fetch", (PyCFunction) PLy_result_fetch, METH_VARARGS, NULL,},
{"nrows", (PyCFunction) PLy_result_nrows, METH_VARARGS, NULL},
{"status", (PyCFunction) PLy_result_status, METH_VARARGS, NULL},
{NULL, NULL, 0, NULL}
};
#endif

It looks like nrows(), status(), and fetch() aren't available as
result object methods. I don't know what would happen if you
un-#ifdef-ed that block and the two others that are related;
perhaps one of the developers can tell us what's going on.

If the methods aren't available then they probably shouldn't be
mentioned in the doc.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Sat, Oct 16, 2004 at 10:56:00AM -0600, Michael Fuhr wrote:

> It looks like nrows(), status(), and fetch() aren't available as
> result object methods. I don't know what would happen if you
> un-#ifdef-ed that block and the two others that are related;
> perhaps one of the developers can tell us what's going on.
>
> If the methods aren't available then they probably shouldn't be
> mentioned in the doc.

Any comments on this? The 8.0.0rc1 PL/Python documentation,
Section 39.3 "Database Access", still mentions the nrows and
status methods, but they don't work. Here's Oliver's original
message and my followup:

http://archives.postgresql.org/pgsql-interfaces/2004-10/msg00019.php
http://archives.postgresql.org/pgsql-interfaces/2004-10/msg00020.php

I expect it's too late to fix the code for 8.0, so I'm wondering
if the doc should be updated not to mention these methods, or to
mention them but say that they don't work yet.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr <mike(at)fuhr(dot)org> writes:
> Any comments on this? The 8.0.0rc1 PL/Python documentation,
> Section 39.3 "Database Access", still mentions the nrows and
> status methods, but they don't work. Here's Oliver's original
> message and my followup:

> http://archives.postgresql.org/pgsql-interfaces/2004-10/msg00019.php
> http://archives.postgresql.org/pgsql-interfaces/2004-10/msg00020.php

> I expect it's too late to fix the code for 8.0, so I'm wondering
> if the doc should be updated not to mention these methods, or to
> mention them but say that they don't work yet.

It looks like someone #ifdef'd out those sections after observing that
the PLy_result_methods table isn't used anyplace. Perhaps the place
where it should have been used got lost in some earlier patch?

The fetch() method appears to be stubbed out anyhow, so merely
reconnecting the table doesn't look like it would bring that function
into the ranks of working features anyway. However the other two look
like they might do something useful.

Just out of curiosity, what sort of patch would it take to enable these
functions? If it's at all nontrivial I'd vote to hold over to 8.1,
but if it's a line or two of code that got lost at some point, it would
seem like a reasonable bug fix ...

regards, tom lane

On Sun, Dec 05, 2004 at 03:15:33PM -0500, Tom Lane wrote:

> It looks like someone #ifdef'd out those sections after observing that
> the PLy_result_methods table isn't used anyplace. Perhaps the place
> where it should have been used got lost in some earlier patch?

I think the #ifdef's happened in 1.13 (2001/11/16), in case that
brings up any old memories:

http://developer.postgresql.org/cvsweb.cgi/pgsql/src/pl/plpython/plpython.c.diff?r1=1.12;r2=1.13

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

I wrote:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
>> Any comments on this? The 8.0.0rc1 PL/Python documentation,
>> Section 39.3 "Database Access", still mentions the nrows and
>> status methods, but they don't work. Here's Oliver's original
>> message and my followup:

>> http://archives.postgresql.org/pgsql-interfaces/2004-10/msg00019.php
>> http://archives.postgresql.org/pgsql-interfaces/2004-10/msg00020.php

> It looks like someone #ifdef'd out those sections after observing that
> the PLy_result_methods table isn't used anyplace. Perhaps the place
> where it should have been used got lost in some earlier patch?

> Just out of curiosity, what sort of patch would it take to enable these
> functions? If it's at all nontrivial I'd vote to hold over to 8.1,
> but if it's a line or two of code that got lost at some point, it would
> seem like a reasonable bug fix ...

Comparing the result and plan method types made it pretty obvious how
those methods are supposed to be hooked up, and it was indeed a
one-liner omission in the original source code. So I've fixed it.

regards, tom lane