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:
<function>nrows</function> which returns the number of rows
- returned by the query, and <function>status</function> which is the
- <function>SPI_execute()</function> return value. The result object
- can be modified.
+ returned by the query, <function>status</function> which is the
+ <function>SPI_execute()</function> return value,
+ <function>colnames</function> which is the list of column names, and
+ <function>coltypes</function> which is the list of column type OIDs. The
+ result object can be modified.
</para>
<para>
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 @@ typedef struct PLyResultObject
PyObject *nrows; /* number of rows returned by query */
PyObject *rows; /* data rows, or None if no data returned */
PyObject *status; /* query status, SPI_OK_*, or SPI_ERR_* */
+ TupleDesc tupdesc;
} PLyResultObject;
extern void PLy_result_init_type(void);
diff --git i/src/pl/plpython/plpy_spi.c w/src/pl/plpython/plpy_spi.c
index 3afb109..0d63c4f 100644
--- i/src/pl/plpython/plpy_spi.c
+++ w/src/pl/plpython/plpy_spi.c
@@ -398,6 +398,8 @@ PLy_spi_execute_fetch_result(SPITupleTable *tuptable, int rows, int status)
oldcontext = CurrentMemoryContext;
PG_TRY();
{
+ result->tupdesc = CreateTupleDescCopy(tuptable->tupdesc);
+
if (rows)
{
Py_DECREF(result->rows);
diff --git i/src/pl/plpython/sql/plpython_spi.sql w/src/pl/plpython/sql/plpython_spi.sql
index 874b31e..06db298 100644
--- i/src/pl/plpython/sql/plpython_spi.sql
+++ w/src/pl/plpython/sql/plpython_spi.sql
@@ -95,10 +95,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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
