Skip to content

Commit 8b6010b

Browse files
tglsfdctglsfdc
committed
Improve support for composite types in PL/Python.
Allow PL/Python functions to return arrays of composite types. Also, fix the restriction that plpy.prepare/plpy.execute couldn't handle query parameters or result columns of composite types. In passing, adopt a saner arrangement for where to release the tupledesc reference counts acquired via lookup_rowtype_tupdesc. The callers of PLyObject_ToCompositeDatum were doing the lookups, but then the releases happened somewhere down inside subroutines of PLyObject_ToCompositeDatum, which is bizarre and bug-prone. Instead release in the same function that acquires the refcount. Ed Behn and Ronan Dunklau, reviewed by Abhijit Menon-Sen
1 parent f545d23 commit 8b6010b

11 files changed

+70
-50
lines changed

‎doc/src/sgml/plpython.sgml

Lines changed: 0 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1026,13 +1026,6 @@ rv = plpy.execute(plan, ["name"], 5)
10261026
<para>
10271027
Query parameters and result row fields are converted between PostgreSQL
10281028
and Python data types as described in <xref linkend="plpython-data">.
1029-
The exception is that composite types are currently not supported: They
1030-
will be rejected as query parameters and are converted to strings when
1031-
appearing in a query result. As a workaround for the latter problem, the
1032-
query can sometimes be rewritten so that the composite type result
1033-
appears as a result row rather than as a field of the result row.
1034-
Alternatively, the resulting string could be parsed apart by hand, but
1035-
this approach is not recommended because it is not future-proof.
10361029
</para>
10371030

10381031
<para>

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

Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -257,7 +257,7 @@ SELECT * FROM changing_test();
257257
1 | (3,4)
258258
(2 rows)
259259

260-
-- tables of composite types (not yet implemented)
260+
-- tables of composite types
261261
CREATE FUNCTION composite_types_table(OUT tab table_record[], OUT typ type_record[] ) RETURNS SETOF record AS $$
262262
yield {'tab': [['first', 1], ['second', 2]],
263263
'typ': [{'first': 'third', 'second': 3},
@@ -270,9 +270,13 @@ yield {'tab': [['first', 1], ['second', 2]],
270270
{'first': 'fourth', 'second': 4}]}
271271
$$ LANGUAGE plpythonu;
272272
SELECT * FROM composite_types_table();
273-
ERROR: PL/Python functions cannot return type table_record[]
274-
DETAIL: PL/Python does not support conversion to arrays of row types.
275-
CONTEXT: PL/Python function "composite_types_table"
273+
tab | typ
274+
----------------------------+----------------------------
275+
{"(first,1)","(second,2)"} | {"(third,3)","(fourth,4)"}
276+
{"(first,1)","(second,2)"} | {"(third,3)","(fourth,4)"}
277+
{"(first,1)","(second,2)"} | {"(third,3)","(fourth,4)"}
278+
(3 rows)
279+
276280
-- check what happens if the output record descriptor changes
277281
CREATE FUNCTION return_record(t text) RETURNS record AS $$
278282
return {'t': t, 'val': 10}

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

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -376,6 +376,15 @@ plan = plpy.prepare("select fname, lname from users where fname like $1 || '%'",
376376
["text"])
377377
c = plpy.cursor(plan, ["a", "b"])
378378
$$ LANGUAGE plpythonu;
379+
CREATE TYPE test_composite_type AS (
380+
a1 int,
381+
a2 varchar
382+
);
383+
CREATE OR REPLACE FUNCTION plan_composite_args() RETURNS test_composite_type AS $$
384+
plan = plpy.prepare("select $1 as c1", ["test_composite_type"])
385+
res = plpy.execute(plan, [{"a1": 3, "a2": "label"}])
386+
return res[0]["c1"]
387+
$$ LANGUAGE plpythonu;
379388
SELECT simple_cursor_test();
380389
simple_cursor_test
381390
--------------------
@@ -432,3 +441,9 @@ CONTEXT: Traceback (most recent call last):
432441
PL/Python function "cursor_plan_wrong_args", line 4, in <module>
433442
c = plpy.cursor(plan, ["a", "b"])
434443
PL/Python function "cursor_plan_wrong_args"
444+
SELECT plan_composite_args();
445+
plan_composite_args
446+
---------------------
447+
(3,label)
448+
(1 row)
449+

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

Lines changed: 6 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -630,15 +630,14 @@ ERROR: invalid input syntax for integer: "abc"
630630
CONTEXT: while creating return value
631631
PL/Python function "test_type_conversion_array_mixed2"
632632
CREATE FUNCTION test_type_conversion_array_record() RETURNS type_record[] AS $$
633-
return [None]
633+
return [{'first': 'one', 'second': 42}, {'first': 'two', 'second': 11}]
634634
$$ LANGUAGE plpythonu;
635-
ERROR: PL/Python functions cannot return type type_record[]
636-
DETAIL: PL/Python does not support conversion to arrays of row types.
637635
SELECT * FROM test_type_conversion_array_record();
638-
ERROR: function test_type_conversion_array_record() does not exist
639-
LINE 1: SELECT * FROM test_type_conversion_array_record();
640-
^
641-
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
636+
test_type_conversion_array_record
637+
-----------------------------------
638+
{"(one,42)","(two,11)"}
639+
(1 row)
640+
642641
CREATE FUNCTION test_type_conversion_array_string() RETURNS text[] AS $$
643642
return 'abc'
644643
$$ LANGUAGE plpythonu;

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

Lines changed: 6 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -630,15 +630,14 @@ ERROR: invalid input syntax for integer: "abc"
630630
CONTEXT: while creating return value
631631
PL/Python function "test_type_conversion_array_mixed2"
632632
CREATE FUNCTION test_type_conversion_array_record() RETURNS type_record[] AS $$
633-
return [None]
633+
return [{'first': 'one', 'second': 42}, {'first': 'two', 'second': 11}]
634634
$$ LANGUAGE plpython3u;
635-
ERROR: PL/Python functions cannot return type type_record[]
636-
DETAIL: PL/Python does not support conversion to arrays of row types.
637635
SELECT * FROM test_type_conversion_array_record();
638-
ERROR: function test_type_conversion_array_record() does not exist
639-
LINE 1: SELECT * FROM test_type_conversion_array_record();
640-
^
641-
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
636+
test_type_conversion_array_record
637+
-----------------------------------
638+
{"(one,42)","(two,11)"}
639+
(1 row)
640+
642641
CREATE FUNCTION test_type_conversion_array_string() RETURNS text[] AS $$
643642
return 'abc'
644643
$$ LANGUAGE plpython3u;

‎src/pl/plpython/plpy_exec.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -194,6 +194,8 @@ PLy_exec_function(FunctionCallInfo fcinfo, PLyProcedure *proc)
194194

195195
rv = PLyObject_ToCompositeDatum(&proc->result, desc, plrv);
196196
fcinfo->isnull = (rv == (Datum) NULL);
197+
198+
ReleaseTupleDesc(desc);
197199
}
198200
else
199201
{

‎src/pl/plpython/plpy_spi.c

Lines changed: 1 addition & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -130,12 +130,7 @@ PLy_spi_prepare(PyObject *self, PyObject *args)
130130

131131
plan->types[i] = typeId;
132132
typeStruct = (Form_pg_type) GETSTRUCT(typeTup);
133-
if (typeStruct->typtype != TYPTYPE_COMPOSITE)
134-
PLy_output_datum_func(&plan->args[i], typeTup);
135-
else
136-
ereport(ERROR,
137-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
138-
errmsg("plpy.prepare does not support composite types")));
133+
PLy_output_datum_func(&plan->args[i], typeTup);
139134
ReleaseSysCache(typeTup);
140135
}
141136

‎src/pl/plpython/plpy_typeio.c

Lines changed: 18 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -404,11 +404,7 @@ PLy_output_datum_func2(PLyObToDatum *arg, HeapTuple typeTup)
404404
Oid funcid;
405405

406406
if (type_is_rowtype(element_type))
407-
ereport(ERROR,
408-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
409-
errmsg("PL/Python functions cannot return type %s",
410-
format_type_be(arg->typoid)),
411-
errdetail("PL/Python does not support conversion to arrays of row types.")));
407+
arg->func = PLyObject_ToComposite;
412408

413409
arg->elm = PLy_malloc0(sizeof(*arg->elm));
414410
arg->elm->func = arg->func;
@@ -742,6 +738,8 @@ PLyObject_ToComposite(PLyObToDatum *arg, int32 typmod, PyObject *plrv)
742738
*/
743739
rv = PLyObject_ToCompositeDatum(&info, desc, plrv);
744740

741+
ReleaseTupleDesc(desc);
742+
745743
PLy_typeinfo_dealloc(&info);
746744

747745
return rv;
@@ -835,11 +833,6 @@ PLySequence_ToArray(PLyObToDatum *arg, int32 typmod, PyObject *plrv)
835833
else
836834
{
837835
nulls[i] = false;
838-
839-
/*
840-
* We don't support arrays of row types yet, so the first argument
841-
* can be NULL.
842-
*/
843836
elems[i] = arg->elm->func(arg->elm, -1, obj);
844837
}
845838
Py_XDECREF(obj);
@@ -872,7 +865,6 @@ PLyString_ToComposite(PLyTypeInfo *info, TupleDesc desc, PyObject *string)
872865
PLy_output_datum_func2(&info->out.d, typeTup);
873866

874867
ReleaseSysCache(typeTup);
875-
ReleaseTupleDesc(desc);
876868

877869
return PLyObject_ToDatum(&info->out.d, info->out.d.typmod, string);
878870
}
@@ -881,6 +873,7 @@ PLyString_ToComposite(PLyTypeInfo *info, TupleDesc desc, PyObject *string)
881873
static Datum
882874
PLyMapping_ToComposite(PLyTypeInfo *info, TupleDesc desc, PyObject *mapping)
883875
{
876+
Datum result;
884877
HeapTuple tuple;
885878
Datum *values;
886879
bool *nulls;
@@ -943,17 +936,20 @@ PLyMapping_ToComposite(PLyTypeInfo *info, TupleDesc desc, PyObject *mapping)
943936
}
944937

945938
tuple = heap_form_tuple(desc, values, nulls);
946-
ReleaseTupleDesc(desc);
939+
result = heap_copy_tuple_as_datum(tuple, desc);
940+
heap_freetuple(tuple);
941+
947942
pfree(values);
948943
pfree(nulls);
949944

950-
return HeapTupleGetDatum(tuple);
945+
return result;
951946
}
952947

953948

954949
static Datum
955950
PLySequence_ToComposite(PLyTypeInfo *info, TupleDesc desc, PyObject *sequence)
956951
{
952+
Datum result;
957953
HeapTuple tuple;
958954
Datum *values;
959955
bool *nulls;
@@ -1029,17 +1025,20 @@ PLySequence_ToComposite(PLyTypeInfo *info, TupleDesc desc, PyObject *sequence)
10291025
}
10301026

10311027
tuple = heap_form_tuple(desc, values, nulls);
1032-
ReleaseTupleDesc(desc);
1028+
result = heap_copy_tuple_as_datum(tuple, desc);
1029+
heap_freetuple(tuple);
1030+
10331031
pfree(values);
10341032
pfree(nulls);
10351033

1036-
return HeapTupleGetDatum(tuple);
1034+
return result;
10371035
}
10381036

10391037

10401038
static Datum
10411039
PLyGenericObject_ToComposite(PLyTypeInfo *info, TupleDesc desc, PyObject *object)
10421040
{
1041+
Datum result;
10431042
HeapTuple tuple;
10441043
Datum *values;
10451044
bool *nulls;
@@ -1101,11 +1100,13 @@ PLyGenericObject_ToComposite(PLyTypeInfo *info, TupleDesc desc, PyObject *object
11011100
}
11021101

11031102
tuple = heap_form_tuple(desc, values, nulls);
1104-
ReleaseTupleDesc(desc);
1103+
result = heap_copy_tuple_as_datum(tuple, desc);
1104+
heap_freetuple(tuple);
1105+
11051106
pfree(values);
11061107
pfree(nulls);
11071108

1108-
return HeapTupleGetDatum(tuple);
1109+
return result;
11091110
}
11101111

11111112
/*

‎src/pl/plpython/sql/plpython_composite.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -125,7 +125,7 @@ SELECT * FROM changing_test();
125125
ALTER TABLE changing ADD COLUMN j integer;
126126
SELECT * FROM changing_test();
127127

128-
-- tables of composite types (not yet implemented)
128+
-- tables of composite types
129129

130130
CREATE FUNCTION composite_types_table(OUT tab table_record[], OUT typ type_record[] ) RETURNS SETOF record AS $$
131131
yield {'tab': [['first', 1], ['second', 2]],

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

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -284,6 +284,17 @@ plan = plpy.prepare("select fname, lname from users where fname like $1 || '%'",
284284
c = plpy.cursor(plan, ["a", "b"])
285285
$$ LANGUAGE plpythonu;
286286

287+
CREATE TYPE test_composite_type AS (
288+
a1 int,
289+
a2 varchar
290+
);
291+
292+
CREATE OR REPLACE FUNCTION plan_composite_args() RETURNS test_composite_type AS $$
293+
plan = plpy.prepare("select $1 as c1", ["test_composite_type"])
294+
res = plpy.execute(plan, [{"a1": 3, "a2": "label"}])
295+
return res[0]["c1"]
296+
$$ LANGUAGE plpythonu;
297+
287298
SELECT simple_cursor_test();
288299
SELECT double_cursor_close();
289300
SELECT cursor_fetch();
@@ -293,3 +304,4 @@ SELECT next_after_close();
293304
SELECT cursor_fetch_next_empty();
294305
SELECT cursor_plan();
295306
SELECT cursor_plan_wrong_args();
307+
SELECT plan_composite_args();

‎src/pl/plpython/sql/plpython_types.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -269,7 +269,7 @@ SELECT * FROM test_type_conversion_array_mixed2();
269269

270270

271271
CREATE FUNCTION test_type_conversion_array_record() RETURNS type_record[] AS $$
272-
return [None]
272+
return [{'first': 'one', 'second': 42}, {'first': 'two', 'second': 11}]
273273
$$ LANGUAGE plpythonu;
274274

275275
SELECT * FROM test_type_conversion_array_record();

0 commit comments

Comments
 (0)