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,
