With result rows in plpython returned as dicts rather than lists, we ran
into issues with a need to preserve the column order in the resultset.
Of course, dicts in python have an arbitrary, non-random order. It's
consistent in the result but does not match the order in the query. Our
use case was a third party to a customer and ourselves who required a
specific order of columns in CSVs sent to them.
Has there ever been any interest in adding a keyword option for
returning row lists rather than dicts? I couldn't find any, so I
experimented a little and came up with the attached patch. I tested this
a little...managed to make it not segfault at the very least. :) I'm
not even close to a guru so there's probably at least one mistake.
As an example:
test=# do language plpythonu $$
a = plpy.execute("""
SELECT 1 as a, 2 as b, NULL as c, ARRAY[1,2,3] as d;
""", return_list=True)
for row in a:
plpy.notice(repr(row))
$$;
NOTICE: [1, 2, None, [1, 2, 3]]
CONTEXT: PL/Python anonymous code block
DO
I didn't test with Python 3/plpython3u.
diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c
index 972b205..bda4181 100644
--- a/src/pl/plpython/plpython.c
+++ b/src/pl/plpython/plpython.c
@@ -344,6 +344,7 @@ static PyObject *PLyBytes_FromBytea(PLyDatumToOb *arg, Datum d);
static PyObject *PLyString_FromDatum(PLyDatumToOb *arg, Datum d);
static PyObject *PLyList_FromArray(PLyDatumToOb *arg, Datum d);
+static PyObject *PLyList_FromTuple(PLyTypeInfo *, HeapTuple, TupleDesc);
static PyObject *PLyDict_FromTuple(PLyTypeInfo *, HeapTuple, TupleDesc);
static Datum PLyObject_ToBool(PLyTypeInfo *, PLyObToDatum *,
@@ -2039,6 +2040,52 @@ PLyList_FromArray(PLyDatumToOb *arg, Datum d)
}
static PyObject *
+PLyList_FromTuple(PLyTypeInfo *info, HeapTuple tuple, TupleDesc desc)
+{
+ PyObject *volatile list;
+ int i;
+
+ if (info->is_rowtype != 1)
+ elog(ERROR, "PLyTypeInfo structure describes a datam");
+
+ list = PyList_New(0);
+ if (list == NULL)
+ PLy_elog(ERROR, "could not create new list");
+
+ PG_TRY();
+ {
+ for (i = 0; i < info->in.r.natts; i++)
+ {
+ Datum vattr;
+ bool is_null;
+ PyObject *value;
+
+ if (desc->attrs[i]->attisdropped)
+ continue;
+
+ vattr = heap_getattr(tuple, (i + 1), desc, &is_null);
+
+ if (is_null || info->in.r.atts[i].func == NULL)
+ PyList_Append(list, Py_None);
+ else
+ {
+ value = (info->in.r.atts[i].func) (&info->in.r.atts[i], vattr);
+ PyList_Append(list, value);
+ Py_DECREF(value);
+ }
+ }
+ }
+ PG_CATCH();
+ {
+ Py_DECREF(list);
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ return list;
+}
+
+static PyObject *
PLyDict_FromTuple(PLyTypeInfo *info, HeapTuple tuple, TupleDesc desc)
{
PyObject *volatile dict;
@@ -2491,10 +2538,10 @@ static int PLy_result_ass_slice(PyObject *, Py_ssize_t, Py_ssize_t, PyObject *);
static PyObject *PLy_spi_prepare(PyObject *, PyObject *);
-static PyObject *PLy_spi_execute(PyObject *, PyObject *);
-static PyObject *PLy_spi_execute_query(char *query, long limit);
-static PyObject *PLy_spi_execute_plan(PyObject *, PyObject *, long);
-static PyObject *PLy_spi_execute_fetch_result(SPITupleTable *, int, int);
+static PyObject *PLy_spi_execute(PyObject *, PyObject *, PyObject *);
+static PyObject *PLy_spi_execute_query(char *query, long limit, PyObject *);
+static PyObject *PLy_spi_execute_plan(PyObject *, PyObject *, long, PyObject *);
+static PyObject *PLy_spi_execute_fetch_result(SPITupleTable *, int, int, PyObject *);
static PyMethodDef PLy_plan_methods[] = {
@@ -2608,7 +2655,7 @@ static PyMethodDef PLy_methods[] = {
/*
* execute a plan or query
*/
- {"execute", PLy_spi_execute, METH_VARARGS, NULL},
+ {"execute", (PyCFunction)PLy_spi_execute, METH_VARARGS | METH_KEYWORDS, NULL},
{NULL, NULL, 0, NULL}
};
@@ -2929,12 +2976,15 @@ PLy_spi_prepare(PyObject *self, PyObject *args)
* execute(plan=plan, values=(foo, bar), limit=5)
*/
static PyObject *
-PLy_spi_execute(PyObject *self, PyObject *args)
+PLy_spi_execute(PyObject *self, PyObject *args, PyObject *keywds)
{
- char *query;
- PyObject *plan;
- PyObject *list = NULL;
- long limit = 0;
+ char *query;
+ PyObject *plan;
+ PyObject *list = NULL;
+ long limit = 0;
+ PyObject *return_list = Py_False;
+ static char *query_kwlist[] = {"query", "limit", "return_list", NULL};
+ static char *plan_kwlist[] = {"plan", "list", "limit", "return_list", NULL};
/* Can't execute more if we have an unhandled error */
if (PLy_error_in_progress)
@@ -2943,21 +2993,21 @@ PLy_spi_execute(PyObject *self, PyObject *args)
return NULL;
}
- if (PyArg_ParseTuple(args, "s|l", &query, &limit))
- return PLy_spi_execute_query(query, limit);
+ if (PyArg_ParseTupleAndKeywords(args, keywds, "s|lO", query_kwlist, &query, &limit, &return_list))
+ return PLy_spi_execute_query(query, limit, return_list);
PyErr_Clear();
- if (PyArg_ParseTuple(args, "O|Ol", &plan, &list, &limit) &&
+ if (PyArg_ParseTupleAndKeywords(args, keywds, "O|OlO", plan_kwlist, &plan, &list, &limit, &return_list) &&
is_PLyPlanObject(plan))
- return PLy_spi_execute_plan(plan, list, limit);
+ return PLy_spi_execute_plan(plan, list, limit, return_list);
PLy_exception_set(PLy_exc_error, "plpy.execute expected a query or a plan");
return NULL;
}
static PyObject *
-PLy_spi_execute_plan(PyObject *ob, PyObject *list, long limit)
+PLy_spi_execute_plan(PyObject *ob, PyObject *list, long limit, PyObject *return_list)
{
volatile int nargs;
int i,
@@ -3089,11 +3139,11 @@ PLy_spi_execute_plan(PyObject *ob, PyObject *list, long limit)
return NULL;
}
- return PLy_spi_execute_fetch_result(SPI_tuptable, SPI_processed, rv);
+ return PLy_spi_execute_fetch_result(SPI_tuptable, SPI_processed, rv, return_list);
}
static PyObject *
-PLy_spi_execute_query(char *query, long limit)
+PLy_spi_execute_query(char *query, long limit, PyObject *return_list)
{
int rv;
volatile MemoryContext oldcontext;
@@ -3125,11 +3175,11 @@ PLy_spi_execute_query(char *query, long limit)
return NULL;
}
- return PLy_spi_execute_fetch_result(SPI_tuptable, SPI_processed, rv);
+ return PLy_spi_execute_fetch_result(SPI_tuptable, SPI_processed, rv, return_list);
}
static PyObject *
-PLy_spi_execute_fetch_result(SPITupleTable *tuptable, int rows, int status)
+PLy_spi_execute_fetch_result(SPITupleTable *tuptable, int rows, int status, PyObject *return_list)
{
PLyResultObject *result;
volatile MemoryContext oldcontext;
@@ -3145,8 +3195,9 @@ PLy_spi_execute_fetch_result(SPITupleTable *tuptable, int rows, int status)
}
else if (status > 0 && tuptable != NULL)
{
- PLyTypeInfo args;
- int i;
+ PLyTypeInfo args;
+ int i;
+ PyObject *row;
Py_DECREF(result->nrows);
result->nrows = PyInt_FromLong(rows);
@@ -3163,8 +3214,14 @@ PLy_spi_execute_fetch_result(SPITupleTable *tuptable, int rows, int status)
PLy_input_tuple_funcs(&args, tuptable->tupdesc);
for (i = 0; i < rows; i++)
{
- PyObject *row = PLyDict_FromTuple(&args, tuptable->vals[i],
+ if (return_list == Py_True)
+ {
+ row = PLyList_FromTuple(&args, tuptable->vals[i],
tuptable->tupdesc);
+ } else {
+ row = PLyDict_FromTuple(&args, tuptable->vals[i],
+ tuptable->tupdesc);
+ }
PyList_SetItem(result->rows, i, row);
}
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
