Array of composite types returned from python
I've endeavored to enable the return of arrays of composite types from code
written in PL/Python. It seems that this can be accomplished though a very
minor change to the code:
On line 401 in the file src/pl/plpython/plpy_typeio.c, remove the error
report "PL/Python functions cannot return type." and replace it with the
command
arg->func = PLyObject_ToComposite;
From all that I can see, this does exactly what I want. A python list of
tuples is converted to an array of composite types in SQL.
I ran the main and python regression suites for both python2 and python3
with assert enabled. The only discrepancies I got were ones that were due to
the output expecting an error. When I altered the .out files to the expected
behavior, it matched just fine.
Am I missing anything, (ie memory leak, undesirable behavior elsewhere)?
-Ed
Ed Behn / Staff Engineer / Airline and Network Services
Information Management Services
2551 Riva Road, Annapolis, MD 21401 USA
Phone: 410.266.4426 / Cell: 240.696.7443
ebehn@arinc.com
<http://www.rockwellcollins.com/> www.rockwellcollins.com
On Thu, Mar 20, 2014 at 4:54 PM, Behn, Edward (EBEHN) <EBEHN@arinc.com> wrote:
I've endeavored to enable the return of arrays of composite types from code written in PL/Python. It seems that this can be accomplished though a very minor change to the code:
On line 401 in the file src/pl/plpython/plpy_typeio.c, remove the error report "PL/Python functions cannot return type..." and replace it with the command
arg->func = PLyObject_ToComposite;
From all that I can see, this does exactly what I want. A python list of tuples is converted to an array of composite types in SQL.
I ran the main and python regression suites for both python2 and python3 with assert enabled. The only discrepancies I got were ones that were due to the output expecting an error. When I altered the .out files to the expected behavior, it matched just fine.
Am I missing anything, (ie memory leak, undesirable behavior elsewhere)?
Don't know, but I'd definitely submit that patch to the next open
fest. That's a very useful gain for such a small change.
merlin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Attached is the patch for the code change described below:
Project Name : Allow return array of composites from PL/Python
Currently, a SQL array of non-composite variables can be returned from
PL/Python code by returning a iterable object. A SQL composite value may be
returned by returning a iterable or a subscriptable object. However,
returning a list of objects that each represent a composite variable is not
converted to an SQL array of composite variables. This code change allows
that conversion to take place. This allows for smooth, elegant interface
between SQL and PL/Python.
This is a patch against MASTER
The patch compiles successfully. All the standard regression tests pass.
Some modifications are needed for the .out files in order for the PL/Python
regression tests to pass. This is due to the fact that the current .out
files expect errors when a python array of composite is converted, where my
modifications expect success. All tests have been performed with both
Python2 and Python3.
-Ed
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Behn, Edward
(EBEHN)
Sent: Thursday, March 20, 2014 5:54 PM
To: pgsql-hackers@postgresql.org
Subject: [HACKERS] Array of composite types returned from python
I've endeavored to enable the return of arrays of composite types from code
written in PL/Python. It seems that this can be accomplished though a very
minor change to the code:
On line 401 in the file src/pl/plpython/plpy_typeio.c, remove the error
report "PL/Python functions cannot return type." and replace it with the
command
arg->func = PLyObject_ToComposite;
From all that I can see, this does exactly what I want. A python list of
tuples is converted to an array of composite types in SQL.
I ran the main and python regression suites for both python2 and python3
with assert enabled. The only discrepancies I got were ones that were due to
the output expecting an error. When I altered the .out files to the expected
behavior, it matched just fine.
Am I missing anything, (ie memory leak, undesirable behavior elsewhere)?
-Ed
Ed Behn / Staff Engineer / Airline and Network Services
Information Management Services
2551 Riva Road, Annapolis, MD 21401 USA
Phone: 410.266.4426 / Cell: 240.696.7443
ebehn@arinc.com
<http://www.rockwellcollins.com/> www.rockwellcollins.com
Attachments:
PLPythonCompositeArrays_v1.patchapplication/octet-stream; name=PLPythonCompositeArrays_v1.patchDownload
diff --git a/src/pl/plpython/expected/plpython_composite.out b/src/pl/plpython/expected/plpython_composite.out
index 61b3046..e7a7edb 100644
--- a/src/pl/plpython/expected/plpython_composite.out
+++ b/src/pl/plpython/expected/plpython_composite.out
@@ -270,9 +270,13 @@ yield {'tab': [['first', 1], ['second', 2]],
{'first': 'fourth', 'second': 4}]}
$$ LANGUAGE plpythonu;
SELECT * FROM composite_types_table();
-ERROR: PL/Python functions cannot return type table_record[]
-DETAIL: PL/Python does not support conversion to arrays of row types.
-CONTEXT: PL/Python function "composite_types_table"
+ tab | typ
+----------------------------+----------------------------
+ {"(first,1)","(second,2)"} | {"(third,3)","(fourth,4)"}
+ {"(first,1)","(second,2)"} | {"(third,3)","(fourth,4)"}
+ {"(first,1)","(second,2)"} | {"(third,3)","(fourth,4)"}
+(3 rows)
+
-- check what happens if the output record descriptor changes
CREATE FUNCTION return_record(t text) RETURNS record AS $$
return {'t': t, 'val': 10}
diff --git a/src/pl/plpython/expected/plpython_types.out b/src/pl/plpython/expected/plpython_types.out
index b98318c..9576905 100644
--- a/src/pl/plpython/expected/plpython_types.out
+++ b/src/pl/plpython/expected/plpython_types.out
@@ -632,13 +632,12 @@ PL/Python function "test_type_conversion_array_mixed2"
CREATE FUNCTION test_type_conversion_array_record() RETURNS type_record[] AS $$
return [None]
$$ LANGUAGE plpythonu;
-ERROR: PL/Python functions cannot return type type_record[]
-DETAIL: PL/Python does not support conversion to arrays of row types.
SELECT * FROM test_type_conversion_array_record();
-ERROR: function test_type_conversion_array_record() does not exist
-LINE 1: SELECT * FROM test_type_conversion_array_record();
- ^
-HINT: No function matches the given name and argument types. You might need to add explicit type casts.
+ test_type_conversion_array_record
+-----------------------------------
+ {NULL}
+(1 row)
+
CREATE FUNCTION test_type_conversion_array_string() RETURNS text[] AS $$
return 'abc'
$$ LANGUAGE plpythonu;
diff --git a/src/pl/plpython/expected/plpython_types_3.out b/src/pl/plpython/expected/plpython_types_3.out
index e104356..f38e2a7 100644
--- a/src/pl/plpython/expected/plpython_types_3.out
+++ b/src/pl/plpython/expected/plpython_types_3.out
@@ -632,13 +632,12 @@ PL/Python function "test_type_conversion_array_mixed2"
CREATE FUNCTION test_type_conversion_array_record() RETURNS type_record[] AS $$
return [None]
$$ LANGUAGE plpython3u;
-ERROR: PL/Python functions cannot return type type_record[]
-DETAIL: PL/Python does not support conversion to arrays of row types.
SELECT * FROM test_type_conversion_array_record();
-ERROR: function test_type_conversion_array_record() does not exist
-LINE 1: SELECT * FROM test_type_conversion_array_record();
- ^
-HINT: No function matches the given name and argument types. You might need to add explicit type casts.
+ test_type_conversion_array_record
+-----------------------------------
+ {NULL}
+(1 row)
+
CREATE FUNCTION test_type_conversion_array_string() RETURNS text[] AS $$
return 'abc'
$$ LANGUAGE plpython3u;
diff --git a/src/pl/plpython/plpy_typeio.c b/src/pl/plpython/plpy_typeio.c
index 7a5e581..2fc9230 100644
--- a/src/pl/plpython/plpy_typeio.c
+++ b/src/pl/plpython/plpy_typeio.c
@@ -404,11 +404,7 @@ PLy_output_datum_func2(PLyObToDatum *arg, HeapTuple typeTup)
Oid funcid;
if (type_is_rowtype(element_type))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("PL/Python functions cannot return type %s",
- format_type_be(arg->typoid)),
- errdetail("PL/Python does not support conversion to arrays of row types.")));
+ arg->func = PLyObject_ToComposite;
arg->elm = PLy_malloc0(sizeof(*arg->elm));
arg->elm->func = arg->func;
@@ -834,11 +830,6 @@ PLySequence_ToArray(PLyObToDatum *arg, int32 typmod, PyObject *plrv)
else
{
nulls[i] = false;
-
- /*
- * We don't support arrays of row types yet, so the first argument
- * can be NULL.
- */
elems[i] = arg->elm->func(arg->elm, -1, obj);
}
Py_XDECREF(obj);