Re: [HACKERS] PL/Python result metadata
At 2012-01-11 22:05:34 +0200, pete...@gmx.net wrote: > > I propose to add two functions to the result object: > > .colnames() returns a list of column names (strings) > .coltypes() returns a list of type OIDs (integers) […] > > Patch attached. Comments welcome. Applies, builds, passes tests. Code looks simple and good. Ready for committer, unless you want to add a typmod accessor too. -- ams -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/Python result metadata
Peter Eisentraut writes: > I deliberately chose not to do that, because the PL/Python API is > intentionally totally different from the standard DB-API, and mixing in > some semi-conforming look-alike would be quite confusing from both ends. Fair enough. > I think we should stick with the PL/Python API being a small layer on > top of SPI, and let the likes of plpydbapi handle the rest. I'm discovering that, and again, fair enough :) Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/Python result metadata
On ons, 2012-01-11 at 22:52 +0100, Dimitri Fontaine wrote: > Peter Eisentraut writes: > > .colnames() returns a list of column names (strings) > > .coltypes() returns a list of type OIDs (integers) > > > > I just made that up because there is no guidance in the other standard > > PLs for this sort of thing, AFAICT. > > What about having the same or comparable API as in psycopg or DB API > > http://initd.org/psycopg/docs/cursor.html > > You could expose a py.description structure? I deliberately chose not to do that, because the PL/Python API is intentionally totally different from the standard DB-API, and mixing in some semi-conforming look-alike would be quite confusing from both ends. I think we should stick with the PL/Python API being a small layer on top of SPI, and let the likes of plpydbapi handle the rest. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/Python result metadata
On ons, 2012-01-11 at 17:16 -0300, Alvaro Herrera wrote: > > I propose to add two functions to the result object: > > > > .colnames() returns a list of column names (strings) > > .coltypes() returns a list of type OIDs (integers) > > No typmods? Didn't think about that, but could be added using similar interface and code. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/Python result metadata
On 11/01/12 22:52, Dimitri Fontaine wrote: > Peter Eisentraut writes: >> .colnames() returns a list of column names (strings) >> .coltypes() returns a list of type OIDs (integers) >> >> I just made that up because there is no guidance in the other standard >> PLs for this sort of thing, AFAICT. > > What about having the same or comparable API as in psycopg or DB API > > http://initd.org/psycopg/docs/cursor.html > > You could expose a py.description structure? +1 for providing a read-only result.description. Not sure if it's worth it to follow DB-API there, but maybe yes. Perhaps we could have a result.description_ex information that's PG-specific or just not present in PEP 249, like the typmod, collation and so on. J -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/Python result metadata
Peter Eisentraut writes: > .colnames() returns a list of column names (strings) > .coltypes() returns a list of type OIDs (integers) > > I just made that up because there is no guidance in the other standard > PLs for this sort of thing, AFAICT. What about having the same or comparable API as in psycopg or DB API http://initd.org/psycopg/docs/cursor.html You could expose a py.description structure? -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/Python result metadata
Excerpts from Peter Eisentraut's message of mié ene 11 17:05:34 -0300 2012: > There is currently no reliable way to retrieve from a result object in > PL/Python the number, name, or type of the result columns. You can get > the number and name if the query returned more than zero rows by looking > at the row dicts, but that is unreliable. The type information isn't > available at all. > > I propose to add two functions to the result object: > > .colnames() returns a list of column names (strings) > .coltypes() returns a list of type OIDs (integers) No typmods? -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PL/Python result metadata
There is currently no reliable way to retrieve from a result object in
PL/Python the number, name, or type of the result columns. You can get
the number and name if the query returned more than zero rows by looking
at the row dicts, but that is unreliable. The type information isn't
available at all.
I propose to add two functions to the result object:
.colnames() returns a list of column names (strings)
.coltypes() returns a list of type OIDs (integers)
I just made that up because there is no guidance in the other standard
PLs for this sort of thing, AFAICT.
Patch attached. Comments welcome.
diff --git i/doc/src/sgml/plpython.sgml w/doc/src/sgml/plpython.sgml
index 618f8d0..69c9c90 100644
--- i/doc/src/sgml/plpython.sgml
+++ w/doc/src/sgml/plpython.sgml
@@ -886,9 +886,11 @@ $$ LANGUAGE plpythonu;
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. The result object
- can be modified.
+ returned by the query, status which is the
+ SPI_execute() return value,
+ colnames which is the list of column names, and
+ coltypes which is the list of column type OIDs. The
+ result object can be modified.
diff --git i/src/pl/plpython/expected/plpython_spi.out w/src/pl/plpython/expected/plpython_spi.out
index 3b4d7a3..cd76147 100644
--- i/src/pl/plpython/expected/plpython_spi.out
+++ w/src/pl/plpython/expected/plpython_spi.out
@@ -117,10 +117,12 @@ SELECT join_sequences(sequences) FROM sequences
--
CREATE FUNCTION result_nrows_test() RETURNS int
AS $$
-plan = plpy.prepare("SELECT 1 UNION SELECT 2")
+plan = plpy.prepare("SELECT 1 AS foo, '11'::text AS bar UNION SELECT 2, '22'")
plpy.info(plan.status()) # not really documented or useful
result = plpy.execute(plan)
if result.status() > 0:
+ plpy.info(result.colnames())
+ plpy.info(result.coltypes())
return result.nrows()
else:
return None
@@ -128,6 +130,10 @@ $$ LANGUAGE plpythonu;
SELECT result_nrows_test();
INFO: True
CONTEXT: PL/Python function "result_nrows_test"
+INFO: ['foo', 'bar']
+CONTEXT: PL/Python function "result_nrows_test"
+INFO: [23, 25]
+CONTEXT: PL/Python function "result_nrows_test"
result_nrows_test
---
2
diff --git i/src/pl/plpython/plpy_resultobject.c w/src/pl/plpython/plpy_resultobject.c
index bf46a16..e7d14d4 100644
--- i/src/pl/plpython/plpy_resultobject.c
+++ w/src/pl/plpython/plpy_resultobject.c
@@ -12,6 +12,8 @@
static void PLy_result_dealloc(PyObject *arg);
+static PyObject *PLy_result_colnames(PyObject *self, PyObject *unused);
+static PyObject *PLy_result_coltypes(PyObject *self, PyObject *unused);
static PyObject *PLy_result_nrows(PyObject *self, PyObject *args);
static PyObject *PLy_result_status(PyObject *self, PyObject *args);
static Py_ssize_t PLy_result_length(PyObject *arg);
@@ -35,6 +37,8 @@ static PySequenceMethods PLy_result_as_sequence = {
};
static PyMethodDef PLy_result_methods[] = {
+ {"colnames", PLy_result_colnames, METH_NOARGS, NULL},
+ {"coltypes", PLy_result_coltypes, METH_NOARGS, NULL},
{"nrows", PLy_result_nrows, METH_VARARGS, NULL},
{"status", PLy_result_status, METH_VARARGS, NULL},
{NULL, NULL, 0, NULL}
@@ -96,6 +100,7 @@ PLy_result_new(void)
ob->status = Py_None;
ob->nrows = PyInt_FromLong(-1);
ob->rows = PyList_New(0);
+ ob->tupdesc = NULL;
return (PyObject *) ob;
}
@@ -108,11 +113,44 @@ PLy_result_dealloc(PyObject *arg)
Py_XDECREF(ob->nrows);
Py_XDECREF(ob->rows);
Py_XDECREF(ob->status);
+ if (ob->tupdesc)
+ {
+ FreeTupleDesc(ob->tupdesc);
+ ob->tupdesc = NULL;
+ }
arg->ob_type->tp_free(arg);
}
static PyObject *
+PLy_result_colnames(PyObject *self, PyObject *unused)
+{
+ PLyResultObject *ob = (PLyResultObject *) self;
+ PyObject *list;
+ int i;
+
+ list = PyList_New(ob->tupdesc->natts);
+ for (i = 0; i < ob->tupdesc->natts; i++)
+ PyList_SET_ITEM(list, i, PyString_FromString(NameStr(ob->tupdesc->attrs[i]->attname)));
+
+ return list;
+}
+
+static PyObject *
+PLy_result_coltypes(PyObject *self, PyObject *unused)
+{
+ PLyResultObject *ob = (PLyResultObject *) self;
+ PyObject *list;
+ int i;
+
+ list = PyList_New(ob->tupdesc->natts);
+ for (i = 0; i < ob->tupdesc->natts; i++)
+ PyList_SET_ITEM(list, i, PyInt_FromLong(ob->tupdesc->attrs[i]->atttypid));
+
+ return list;
+}
+
+static PyObject *
PLy_result_nrows(PyObject *self, PyObject *args)
{
PLyResultObject *ob = (PLyResultObject *) self;
diff --git i/src/pl/plpython/plpy_resultobject.h w/src/pl/plpython/plpy_resultobject.h
index 719828a..1b37d1d 100644
--- i/src/pl/plpython/plpy_resultobject.h
+++ w/src/pl/plpython/plpy_resultobject.h
@@ -5,6 +5,9 @@
#ifndef PLPY_RESULTOBJECT_H
#define PLPY_RESULTOBJECT_H
+#include "access/tupdesc.h"
+
+
typedef struct PLyResultObject
{
PyObject_HEAD
@@ -12,6 +15,7 @@ t
