Patch to add support for text/int arrays for plpythonu
Hi,
Attached is a small patch for adding support for INT[] and TEXT[] to
plpythonu.
There are also some small tests for this copypasted to the end of the mail.
I'd like to get this into CVS but as it's probably a bit late for 8.4 I
wouldn't mind it going into the first commitfest for 8.5.
- Hannu
---SELECT test_int_array[3] FROM return_int_array(ARRAY[1,2,3]) AS
test_int_array;
CREATE OR REPLACE FUNCTION return_int_array(int_array INT[])
RETURNS int[]
AS $$
plpy.info("Type: %s %r" % (type(int_array), int_array))
return int_array
$$ LANGUAGE plpythonu;
---SELECT jee[3] FROM return_text_array(ARRAY['test1','test2','test3'])
AS test_text_array;
CREATE OR REPLACE FUNCTION return_text_array(text_array TEXT[])
RETURNS TEXT[]
AS $$
plpy.info("Type: %s %r" % (type(text_array), text_array))
return text_array
$$ LANGUAGE plpythonu;
--CREATE TABLE test_table (id SERIAL PRIMARY KEY, bar INT[], bar2 TEXT[]);
--SELECT * FROM test_text_array_plan(ARRAY['test1', 'test2']);
CREATE OR REPLACE FUNCTION test_text_array_plan(test_array TEXT[])
RETURNS void
AS $$
plan = plpy.prepare("INSERT INTO test_table (bar) VALUES ($1)", ["TEXT[]"])
results = plpy.execute(plan, [test_array])
$$ LANGUAGE plpythonu;
--SELECT * FROM test_int_array_plan(ARRAY[1,2,3,4,5,6,7,8,9,10]);
CREATE OR REPLACE FUNCTION test_int_array_plan(test_array INT[])
RETURNS void
AS $$
plan = plpy.prepare("INSERT INTO test_table (bar2) VALUES ($1)", ["INT[]"])
results = plpy.execute(plan, [test_array])
$$ LANGUAGE plpythonu;
Attachments:
plpythonarray.difftext/plain; name=plpythonarray.diff; x-mac-creator=0; x-mac-type=0Download
diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c
index e29a02e..e4e7a4b 100644
--- a/src/pl/plpython/plpython.c
+++ b/src/pl/plpython/plpython.c
@@ -261,6 +261,8 @@ static PyObject *PLyFloat_FromString(const char *);
static PyObject *PLyInt_FromString(const char *);
static PyObject *PLyLong_FromString(const char *);
static PyObject *PLyString_FromString(const char *);
+static PyObject *PLyIntArray_FromString(const char *);
+static PyObject *PLyTextArray_FromString(const char *);
static HeapTuple PLyMapping_ToTuple(PLyTypeInfo *, PyObject *);
static HeapTuple PLySequence_ToTuple(PLyTypeInfo *, PyObject *);
@@ -936,6 +938,13 @@ PLy_function_handler(FunctionCallInfo fcinfo, PLyProcedure * proc)
if (!plrv_so)
PLy_elog(ERROR, "could not create string representation of Python object in PL/Python function \"%s\" while creating return value", proc->proname);
plrv_sc = PyString_AsString(plrv_so);
+ if (PyList_Check (plrv))
+ {
+ /* Switch Python form to PostgreSQL convention for arrays */
+ plrv_sc[0] = '{';
+ plrv_sc[strlen(plrv_sc) - 1] = '}';
+ }
+
rv = InputFunctionCall(&proc->result.out.d.typfunc,
plrv_sc,
proc->result.out.d.typioparam,
@@ -1633,6 +1642,12 @@ PLy_input_datum_func2(PLyDatumToOb * arg, Oid typeOid, HeapTuple typeTup)
case INT8OID:
arg->func = PLyLong_FromString;
break;
+ case INT4ARRAYOID:
+ arg->func = PLyIntArray_FromString;
+ break;
+ case TEXTARRAYOID:
+ arg->func = PLyTextArray_FromString;
+ break;
default:
arg->func = PLyString_FromString;
break;
@@ -1701,6 +1716,44 @@ PLyInt_FromString(const char *src)
return PyInt_FromLong(v);
}
+static PyObject *
+PLyIntArray_FromString(const char *src)
+{
+ PyObject *volatile list;
+ char *token, *brkt, *eptr, *str_to_parse = (char *) src + 1; /* skip '{' at src[0] */
+ long v;
+
+ errno = 0;
+ list = PyList_New (0);
+
+ for (token = strtok_r(str_to_parse, ",}", &brkt); token; token = strtok_r(NULL, ",}", &brkt))
+ {
+ v = strtol(token, &eptr, 0);
+ if (PyList_Append (list, PyInt_FromLong(v)) != 0)
+ break;
+ }
+
+ return list;
+}
+
+static PyObject *
+PLyTextArray_FromString(const char *src)
+{
+ PyObject *volatile list;
+ char *token, *brkt, *str_to_parse = (char *) src + 1; /* skip '{' at src[0] */
+
+ errno = 0;
+ list = PyList_New (0);
+
+ for (token = strtok_r(str_to_parse, ",}", &brkt); token; token = strtok_r(NULL, ",}", &brkt))
+ {
+ if (PyList_Append (list, PyString_FromString(token)) != 0)
+ break;
+ }
+
+ return list;
+}
+
static PyObject *
PLyLong_FromString(const char *src)
{
@@ -2406,8 +2459,8 @@ PLy_spi_prepare(PyObject * self, PyObject * args)
********************************************************/
parseTypeString(sptr, &typeId, &typmod);
-
- typeTup = SearchSysCache(TYPEOID,
+
+ typeTup = SearchSysCache(TYPEOID,
ObjectIdGetDatum(typeId),
0, 0, 0);
if (!HeapTupleIsValid(typeTup))
@@ -2471,15 +2524,14 @@ PLy_spi_execute(PyObject * self, PyObject * args)
char *query;
PyObject *plan;
PyObject *list = NULL;
- long limit = 0;
-
+ long limit = 0;
/* Can't execute more if we have an unhandled error */
if (PLy_error_in_progress)
{
PLy_exception_set(PLy_exc_error, "transaction aborted");
return NULL;
}
-
+
if (PyArg_ParseTuple(args, "s|l", &query, &limit))
return PLy_spi_execute_query(query, limit);
@@ -2501,7 +2553,6 @@ PLy_spi_execute_plan(PyObject * ob, PyObject * list, long limit)
rv;
PLyPlanObject *plan;
MemoryContext oldcontext;
-
if (list != NULL)
{
if (!PySequence_Check(list) || PyString_Check(list))
@@ -2513,18 +2564,17 @@ PLy_spi_execute_plan(PyObject * ob, PyObject * list, long limit)
}
else
nargs = 0;
-
plan = (PLyPlanObject *) ob;
if (nargs != plan->nargs)
{
char *sv;
PyObject *so = PyObject_Str(list);
-
if (!so)
PLy_elog(ERROR, "PL/Python function \"%s\" could not execute plan",
PLy_procedure_name(PLy_curr_procedure));
sv = PyString_AsString(so);
+
PLy_exception_set(PLy_exc_spi_error,
dngettext(TEXTDOMAIN, "Expected sequence of %d argument, got %d: %s", "Expected sequence of %d arguments, got %d: %s", plan->nargs),
plan->nargs, nargs, sv);
@@ -2532,7 +2582,6 @@ PLy_spi_execute_plan(PyObject * ob, PyObject * list, long limit)
return NULL;
}
-
oldcontext = CurrentMemoryContext;
PG_TRY();
{
@@ -2541,10 +2590,13 @@ PLy_spi_execute_plan(PyObject * ob, PyObject * list, long limit)
for (j = 0; j < nargs; j++)
{
- PyObject *elem,
- *so;
+ PyObject *elem, *so;
+ int list_element = 0;
elem = PySequence_GetItem(list, j);
+ if (PySequence_Check(elem))
+ list_element = 1;
+
if (elem != Py_None)
{
so = PyObject_Str(elem);
@@ -2552,16 +2604,19 @@ PLy_spi_execute_plan(PyObject * ob, PyObject * list, long limit)
PLy_elog(ERROR, "PL/Python function \"%s\" could not execute plan",
PLy_procedure_name(PLy_curr_procedure));
Py_DECREF(elem);
-
+
PG_TRY();
{
char *sv = PyString_AsString(so);
-
- plan->values[j] =
- InputFunctionCall(&(plan->args[j].out.d.typfunc),
- sv,
- plan->args[j].out.d.typioparam,
- -1);
+ if (list_element)
+ {
+ sv[0] = '{';
+ sv[strlen(sv) - 1] = '}';
+ }
+ plan->values[j] = InputFunctionCall(&(plan->args[j].out.d.typfunc),
+ sv,
+ plan->args[j].out.d.typioparam,
+ -1);
}
PG_CATCH();
{
"Valtonen, Hannu" <hannu.valtonen@hut.fi> writes:
Attached is a small patch for adding support for INT[] and TEXT[] to
plpythonu.
Why in the world would you confine the feature to just two data types?
This seems like a fundamentally incorrect approach.
regards, tom lane
On 4/26/09 6:23 PM, Tom Lane wrote:
Why in the world would you confine the feature to just two data types?
This seems like a fundamentally incorrect approach.
The reason why I wrote it that way is because that's the way plpythonu's
conversion to python and back is set up.
Without my patch it currently knows how to convert BOOLOID, FLOAT4OID,
FLOAT8OID, NUMERICOID, INT2OID, INT4OID, INT8OID from pgsql form to
python form and back again as function return types. All the rest of the
pgsql types are converted into python strings with 1:1 textual
representation from pgsql. (This includes all array types)
What my patch basically changes is that instead of making an INT4/TEXT
array a python string, it converts them to python list()'s with
corresponding types. The rest are still being converted into python
strings. So I started with INT[] and TEXT[] because those were the ones
I actually needed myself but extending conversion support to for example
to FLOATs/NUMERICs wouldn't be that hard.
It also basically corresponds with postgresql/src/pl/plpython/TODO's:
"* Allow arrays as function arguments and return values. (almost done)"
- Hannu