Skip to content

Commit ee7fa66

Browse files
peterepetere
committed
PL/Python: Add result metadata functions
Add result object functions .colnames, .coltypes, .coltypmods to obtain information about the result column names and types, which was previously not possible in the PL/Python SPI interface. reviewed by Abhijit Menon-Sen
1 parent c6ea8cc commit ee7fa66

File tree

6 files changed

+80
-5
lines changed

6 files changed

+80
-5
lines changed

‎doc/src/sgml/plpython.sgml

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -886,9 +886,12 @@ $$ LANGUAGE plpythonu;
886886
list or dictionary object. The result object can be accessed by
887887
row number and column name. It has these additional methods:
888888
<function>nrows</function> which returns the number of rows
889-
returned by the query, and <function>status</function> which is the
890-
<function>SPI_execute()</function> return value. The result object
891-
can be modified.
889+
returned by the query, <function>status</function> which is the
890+
<function>SPI_execute()</function> return value,
891+
<function>colnames</function> which is the list of column names,
892+
<function>coltypes</function> which is the list of column type OIDs,
893+
and <function>coltypmods</function> which is the list of type-specific type
894+
modifiers for the columns. The result object can be modified.
892895
</para>
893896

894897
<para>

‎src/pl/plpython/expected/plpython_spi.out

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -117,16 +117,25 @@ SELECT join_sequences(sequences) FROM sequences
117117
--
118118
CREATE FUNCTION result_nrows_test() RETURNS int
119119
AS $$
120-
plan = plpy.prepare("SELECT 1 UNION SELECT 2")
120+
plan = plpy.prepare("SELECT 1 AS foo, '11'::text AS bar UNION SELECT 2, '22'")
121121
plpy.info(plan.status()) # not really documented or useful
122122
result = plpy.execute(plan)
123123
if result.status() > 0:
124+
plpy.info(result.colnames())
125+
plpy.info(result.coltypes())
126+
plpy.info(result.coltypmods())
124127
return result.nrows()
125128
else:
126129
return None
127130
$$ LANGUAGE plpythonu;
128131
SELECT result_nrows_test();
129132
INFO: True
133+
CONTEXT: PL/Python function "result_nrows_test"
134+
INFO: ['foo', 'bar']
135+
CONTEXT: PL/Python function "result_nrows_test"
136+
INFO: [23, 25]
137+
CONTEXT: PL/Python function "result_nrows_test"
138+
INFO: [-1, -1]
130139
CONTEXT: PL/Python function "result_nrows_test"
131140
result_nrows_test
132141
-------------------

‎src/pl/plpython/plpy_resultobject.c

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,9 @@
1212

1313

1414
static void PLy_result_dealloc(PyObject *arg);
15+
static PyObject *PLy_result_colnames(PyObject *self, PyObject *unused);
16+
static PyObject *PLy_result_coltypes(PyObject *self, PyObject *unused);
17+
static PyObject *PLy_result_coltypmods(PyObject *self, PyObject *unused);
1518
static PyObject *PLy_result_nrows(PyObject *self, PyObject *args);
1619
static PyObject *PLy_result_status(PyObject *self, PyObject *args);
1720
static Py_ssize_t PLy_result_length(PyObject *arg);
@@ -35,6 +38,9 @@ static PySequenceMethods PLy_result_as_sequence = {
3538
};
3639

3740
static PyMethodDef PLy_result_methods[] = {
41+
{"colnames", PLy_result_colnames, METH_NOARGS, NULL},
42+
{"coltypes", PLy_result_coltypes, METH_NOARGS, NULL},
43+
{"coltypmods", PLy_result_coltypmods, METH_NOARGS, NULL},
3844
{"nrows", PLy_result_nrows, METH_VARARGS, NULL},
3945
{"status", PLy_result_status, METH_VARARGS, NULL},
4046
{NULL, NULL, 0, NULL}
@@ -96,6 +102,7 @@ PLy_result_new(void)
96102
ob->status = Py_None;
97103
ob->nrows = PyInt_FromLong(-1);
98104
ob->rows = PyList_New(0);
105+
ob->tupdesc = NULL;
99106

100107
return (PyObject *) ob;
101108
}
@@ -108,10 +115,57 @@ PLy_result_dealloc(PyObject *arg)
108115
Py_XDECREF(ob->nrows);
109116
Py_XDECREF(ob->rows);
110117
Py_XDECREF(ob->status);
118+
if (ob->tupdesc)
119+
{
120+
FreeTupleDesc(ob->tupdesc);
121+
ob->tupdesc = NULL;
122+
}
111123

112124
arg->ob_type->tp_free(arg);
113125
}
114126

127+
static PyObject *
128+
PLy_result_colnames(PyObject *self, PyObject *unused)
129+
{
130+
PLyResultObject *ob = (PLyResultObject *) self;
131+
PyObject *list;
132+
int i;
133+
134+
list = PyList_New(ob->tupdesc->natts);
135+
for (i = 0; i < ob->tupdesc->natts; i++)
136+
PyList_SET_ITEM(list, i, PyString_FromString(NameStr(ob->tupdesc->attrs[i]->attname)));
137+
138+
return list;
139+
}
140+
141+
static PyObject *
142+
PLy_result_coltypes(PyObject *self, PyObject *unused)
143+
{
144+
PLyResultObject *ob = (PLyResultObject *) self;
145+
PyObject *list;
146+
int i;
147+
148+
list = PyList_New(ob->tupdesc->natts);
149+
for (i = 0; i < ob->tupdesc->natts; i++)
150+
PyList_SET_ITEM(list, i, PyInt_FromLong(ob->tupdesc->attrs[i]->atttypid));
151+
152+
return list;
153+
}
154+
155+
static PyObject *
156+
PLy_result_coltypmods(PyObject *self, PyObject *unused)
157+
{
158+
PLyResultObject *ob = (PLyResultObject *) self;
159+
PyObject *list;
160+
int i;
161+
162+
list = PyList_New(ob->tupdesc->natts);
163+
for (i = 0; i < ob->tupdesc->natts; i++)
164+
PyList_SET_ITEM(list, i, PyInt_FromLong(ob->tupdesc->attrs[i]->atttypmod));
165+
166+
return list;
167+
}
168+
115169
static PyObject *
116170
PLy_result_nrows(PyObject *self, PyObject *args)
117171
{

‎src/pl/plpython/plpy_resultobject.h

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5,13 +5,17 @@
55
#ifndef PLPY_RESULTOBJECT_H
66
#define PLPY_RESULTOBJECT_H
77

8+
#include "access/tupdesc.h"
9+
10+
811
typedef struct PLyResultObject
912
{
1013
PyObject_HEAD
1114
/* HeapTuple *tuples; */
1215
PyObject *nrows; /* number of rows returned by query */
1316
PyObject *rows; /* data rows, or None if no data returned */
1417
PyObject *status; /* query status, SPI_OK_*, or SPI_ERR_* */
18+
TupleDesc tupdesc;
1519
} PLyResultObject;
1620

1721
extern void PLy_result_init_type(void);

‎src/pl/plpython/plpy_spi.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -398,6 +398,8 @@ PLy_spi_execute_fetch_result(SPITupleTable *tuptable, int rows, int status)
398398
oldcontext = CurrentMemoryContext;
399399
PG_TRY();
400400
{
401+
result->tupdesc = CreateTupleDescCopy(tuptable->tupdesc);
402+
401403
if (rows)
402404
{
403405
Py_DECREF(result->rows);

‎src/pl/plpython/sql/plpython_spi.sql

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -95,10 +95,13 @@ SELECT join_sequences(sequences) FROM sequences
9595

9696
CREATE FUNCTION result_nrows_test() RETURNS int
9797
AS $$
98-
plan = plpy.prepare("SELECT 1 UNION SELECT 2")
98+
plan = plpy.prepare("SELECT 1 AS foo, '11'::text AS bar UNION SELECT 2, '22'")
9999
plpy.info(plan.status()) # not really documented or useful
100100
result = plpy.execute(plan)
101101
if result.status() > 0:
102+
plpy.info(result.colnames())
103+
plpy.info(result.coltypes())
104+
plpy.info(result.coltypmods())
102105
return result.nrows()
103106
else:
104107
return None

0 commit comments

Comments
 (0)