From bc411f25c14f65b459de5dbc96ac79e7741ef9ee Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Sat, 26 Feb 2011 16:53:11 +0200 Subject: [PATCH] Table function support for PL/Python MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit This allows functions with multiple OUT parameters returning both one or multiple records (RECORD or SETOF RECORD). Jan Urbański, reviewed by Hitoshi Harada --- doc/src/sgml/plpython.sgml | 36 ++- src/pl/plpython/Makefile | 1 + src/pl/plpython/expected/plpython_composite.out | 361 ++++++++++++++++++++++++ src/pl/plpython/expected/plpython_record.out | 14 +- src/pl/plpython/expected/plpython_trigger.out | 18 ++ src/pl/plpython/plpython.c | 177 ++++++++++-- src/pl/plpython/sql/plpython_composite.sql | 168 +++++++++++ src/pl/plpython/sql/plpython_record.sql | 4 +- src/pl/plpython/sql/plpython_trigger.sql | 20 ++ 9 files changed, 748 insertions(+), 51 deletions(-) create mode 100644 src/pl/plpython/expected/plpython_composite.out create mode 100644 src/pl/plpython/sql/plpython_composite.sql diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml index 7c869a8719..94d42bfaaa 100644 --- a/doc/src/sgml/plpython.sgml +++ b/doc/src/sgml/plpython.sgml @@ -390,18 +390,6 @@ $$ LANGUAGE plpythonu; return type and the Python data type of the actual return object are not flagged; the value will be converted in any case. - - - - PL/Python functions cannot return - either type RECORD or SETOF RECORD. A - workaround is to write a PL/pgSQL - function that creates a temporary table, have it call the - PL/Python function to fill the table, - and then have the PL/pgSQL function - return the generic RECORD from the temporary table. - - @@ -593,6 +581,17 @@ $$ LANGUAGE plpythonu; + + + Functions with OUT parameters are also supported. For example: + +CREATE FUNCTION multiout_simple(OUT i integer, OUT j integer) AS $$ +return (1, 2) +$$ LANGUAGE plpythonu; + +SELECT * FROM multiout_simple(); + + @@ -692,6 +691,19 @@ $$ LANGUAGE plpythonu; + + + Set-returning functions with OUT parameters + (using RETURNS SETOF record) are also + supported. For example: + +CREATE FUNCTION multiout_simple_setof(n integer, OUT integer, OUT integer) RETURNS SETOF record AS $$ +return [(1, 2)] * n +$$ LANGUAGE plpythonu; + +SELECT * FROM multiout_simple_setof(3); + + diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile index 17fa86880e..376c7467c7 100644 --- a/src/pl/plpython/Makefile +++ b/src/pl/plpython/Makefile @@ -80,6 +80,7 @@ REGRESS = \ plpython_error \ plpython_unicode \ plpython_quote \ + plpython_composite \ plpython_drop # where to find psql for running the tests PSQLDIR = $(bindir) diff --git a/src/pl/plpython/expected/plpython_composite.out b/src/pl/plpython/expected/plpython_composite.out new file mode 100644 index 0000000000..61b3046790 --- /dev/null +++ b/src/pl/plpython/expected/plpython_composite.out @@ -0,0 +1,361 @@ +CREATE FUNCTION multiout_simple(OUT i integer, OUT j integer) AS $$ +return (1, 2) +$$ LANGUAGE plpythonu; +SELECT multiout_simple(); + multiout_simple +----------------- + (1,2) +(1 row) + +SELECT * FROM multiout_simple(); + i | j +---+--- + 1 | 2 +(1 row) + +SELECT i, j + 2 FROM multiout_simple(); + i | ?column? +---+---------- + 1 | 4 +(1 row) + +SELECT (multiout_simple()).j + 3; + ?column? +---------- + 5 +(1 row) + +CREATE FUNCTION multiout_simple_setof(n integer = 1, OUT integer, OUT integer) RETURNS SETOF record AS $$ +return [(1, 2)] * n +$$ LANGUAGE plpythonu; +SELECT multiout_simple_setof(); + multiout_simple_setof +----------------------- + (1,2) +(1 row) + +SELECT * FROM multiout_simple_setof(); + column1 | column2 +---------+--------- + 1 | 2 +(1 row) + +SELECT * FROM multiout_simple_setof(3); + column1 | column2 +---------+--------- + 1 | 2 + 1 | 2 + 1 | 2 +(3 rows) + +CREATE FUNCTION multiout_record_as(typ text, + first text, OUT first text, + second integer, OUT second integer, + retnull boolean) RETURNS record AS $$ +if retnull: + return None +if typ == 'dict': + return { 'first': first, 'second': second, 'additionalfield': 'must not cause trouble' } +elif typ == 'tuple': + return ( first, second ) +elif typ == 'list': + return [ first, second ] +elif typ == 'obj': + class type_record: pass + type_record.first = first + type_record.second = second + return type_record +$$ LANGUAGE plpythonu; +SELECT * FROM multiout_record_as('dict', 'foo', 1, 'f'); + first | second +-------+-------- + foo | 1 +(1 row) + +SELECT multiout_record_as('dict', 'foo', 1, 'f'); + multiout_record_as +-------------------- + (foo,1) +(1 row) + +SELECT *, s IS NULL AS snull FROM multiout_record_as('tuple', 'xxx', NULL, 'f') AS f(f, s); + f | s | snull +-----+---+------- + xxx | | t +(1 row) + +SELECT *, f IS NULL AS fnull, s IS NULL AS snull FROM multiout_record_as('tuple', 'xxx', 1, 't') AS f(f, s); + f | s | fnull | snull +---+---+-------+------- + | | t | t +(1 row) + +SELECT * FROM multiout_record_as('obj', NULL, 10, 'f'); + first | second +-------+-------- + | 10 +(1 row) + +CREATE FUNCTION multiout_setof(n integer, + OUT power_of_2 integer, + OUT length integer) RETURNS SETOF record AS $$ +for i in range(n): + power = 2 ** i + length = plpy.execute("select length('%d')" % power)[0]['length'] + yield power, length +$$ LANGUAGE plpythonu; +SELECT * FROM multiout_setof(3); + power_of_2 | length +------------+-------- + 1 | 1 + 2 | 1 + 4 | 1 +(3 rows) + +SELECT multiout_setof(5); + multiout_setof +---------------- + (1,1) + (2,1) + (4,1) + (8,1) + (16,2) +(5 rows) + +CREATE FUNCTION multiout_return_table() RETURNS TABLE (x integer, y text) AS $$ +return [{'x': 4, 'y' :'four'}, + {'x': 7, 'y' :'seven'}, + {'x': 0, 'y' :'zero'}] +$$ LANGUAGE plpythonu; +SELECT * FROM multiout_return_table(); + x | y +---+------- + 4 | four + 7 | seven + 0 | zero +(3 rows) + +CREATE FUNCTION multiout_array(OUT integer[], OUT text) RETURNS SETOF record AS $$ +yield [[1], 'a'] +yield [[1,2], 'b'] +yield [[1,2,3], None] +$$ LANGUAGE plpythonu; +SELECT * FROM multiout_array(); + column1 | column2 +---------+--------- + {1} | a + {1,2} | b + {1,2,3} | +(3 rows) + +CREATE FUNCTION singleout_composite(OUT type_record) AS $$ +return {'first': 1, 'second': 2} +$$ LANGUAGE plpythonu; +CREATE FUNCTION multiout_composite(OUT type_record) RETURNS SETOF type_record AS $$ +return [{'first': 1, 'second': 2}, + {'first': 3, 'second': 4 }] +$$ LANGUAGE plpythonu; +SELECT * FROM singleout_composite(); + first | second +-------+-------- + 1 | 2 +(1 row) + +SELECT * FROM multiout_composite(); + first | second +-------+-------- + 1 | 2 + 3 | 4 +(2 rows) + +-- composite OUT parameters in functions returning RECORD not supported yet +CREATE FUNCTION multiout_composite(INOUT n integer, OUT type_record) AS $$ +return (n, (n * 2, n * 3)) +$$ LANGUAGE plpythonu; +CREATE FUNCTION multiout_table_type_setof(typ text, returnnull boolean, INOUT n integer, OUT table_record) RETURNS SETOF record AS $$ +if returnnull: + d = None +elif typ == 'dict': + d = {'first': n * 2, 'second': n * 3, 'extra': 'not important'} +elif typ == 'tuple': + d = (n * 2, n * 3) +elif typ == 'obj': + class d: pass + d.first = n * 2 + d.second = n * 3 +for i in range(n): + yield (i, d) +$$ LANGUAGE plpythonu; +SELECT * FROM multiout_composite(2); + n | column2 +---+--------- + 2 | (4,6) +(1 row) + +SELECT * FROM multiout_table_type_setof('dict', 'f', 3); + n | column2 +---+--------- + 0 | (6,9) + 1 | (6,9) + 2 | (6,9) +(3 rows) + +SELECT * FROM multiout_table_type_setof('tuple', 'f', 2); + n | column2 +---+--------- + 0 | (4,6) + 1 | (4,6) +(2 rows) + +SELECT * FROM multiout_table_type_setof('obj', 'f', 4); + n | column2 +---+--------- + 0 | (8,12) + 1 | (8,12) + 2 | (8,12) + 3 | (8,12) +(4 rows) + +SELECT * FROM multiout_table_type_setof('dict', 't', 3); + n | column2 +---+--------- + 0 | + 1 | + 2 | +(3 rows) + +-- check what happens if a type changes under us +CREATE TABLE changing ( + i integer, + j integer +); +CREATE FUNCTION changing_test(OUT n integer, OUT changing) RETURNS SETOF record AS $$ +return [(1, {'i': 1, 'j': 2}), + (1, (3, 4))] +$$ LANGUAGE plpythonu; +SELECT * FROM changing_test(); + n | column2 +---+--------- + 1 | (1,2) + 1 | (3,4) +(2 rows) + +ALTER TABLE changing DROP COLUMN j; +SELECT * FROM changing_test(); +ERROR: length of returned sequence did not match number of columns in row +CONTEXT: while creating return value +PL/Python function "changing_test" +SELECT * FROM changing_test(); +ERROR: length of returned sequence did not match number of columns in row +CONTEXT: while creating return value +PL/Python function "changing_test" +ALTER TABLE changing ADD COLUMN j integer; +SELECT * FROM changing_test(); + n | column2 +---+--------- + 1 | (1,2) + 1 | (3,4) +(2 rows) + +-- tables of composite types (not yet implemented) +CREATE FUNCTION composite_types_table(OUT tab table_record[], OUT typ type_record[] ) RETURNS SETOF record AS $$ +yield {'tab': [['first', 1], ['second', 2]], + 'typ': [{'first': 'third', 'second': 3}, + {'first': 'fourth', 'second': 4}]} +yield {'tab': [['first', 1], ['second', 2]], + 'typ': [{'first': 'third', 'second': 3}, + {'first': 'fourth', 'second': 4}]} +yield {'tab': [['first', 1], ['second', 2]], + 'typ': [{'first': 'third', 'second': 3}, + {'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" +-- check what happens if the output record descriptor changes +CREATE FUNCTION return_record(t text) RETURNS record AS $$ +return {'t': t, 'val': 10} +$$ LANGUAGE plpythonu; +SELECT * FROM return_record('abc') AS r(t text, val integer); + t | val +-----+----- + abc | 10 +(1 row) + +SELECT * FROM return_record('abc') AS r(t text, val bigint); + t | val +-----+----- + abc | 10 +(1 row) + +SELECT * FROM return_record('abc') AS r(t text, val integer); + t | val +-----+----- + abc | 10 +(1 row) + +SELECT * FROM return_record('abc') AS r(t varchar(30), val integer); + t | val +-----+----- + abc | 10 +(1 row) + +SELECT * FROM return_record('abc') AS r(t varchar(100), val integer); + t | val +-----+----- + abc | 10 +(1 row) + +SELECT * FROM return_record('999') AS r(val text, t integer); + val | t +-----+----- + 10 | 999 +(1 row) + +CREATE FUNCTION return_record_2(t text) RETURNS record AS $$ +return {'v1':1,'v2':2,t:3} +$$ LANGUAGE plpythonu; +SELECT * FROM return_record_2('v3') AS (v3 int, v2 int, v1 int); + v3 | v2 | v1 +----+----+---- + 3 | 2 | 1 +(1 row) + +SELECT * FROM return_record_2('v3') AS (v2 int, v3 int, v1 int); + v2 | v3 | v1 +----+----+---- + 2 | 3 | 1 +(1 row) + +SELECT * FROM return_record_2('v4') AS (v1 int, v4 int, v2 int); + v1 | v4 | v2 +----+----+---- + 1 | 3 | 2 +(1 row) + +SELECT * FROM return_record_2('v4') AS (v1 int, v4 int, v2 int); + v1 | v4 | v2 +----+----+---- + 1 | 3 | 2 +(1 row) + +-- error +SELECT * FROM return_record_2('v4') AS (v1 int, v3 int, v2 int); +ERROR: key "v3" not found in mapping +HINT: To return null in a column, add the value None to the mapping with the key named after the column. +CONTEXT: while creating return value +PL/Python function "return_record_2" +-- works +SELECT * FROM return_record_2('v3') AS (v1 int, v3 int, v2 int); + v1 | v3 | v2 +----+----+---- + 1 | 3 | 2 +(1 row) + +SELECT * FROM return_record_2('v3') AS (v1 int, v2 int, v3 int); + v1 | v2 | v3 +----+----+---- + 1 | 2 | 3 +(1 row) + diff --git a/src/pl/plpython/expected/plpython_record.out b/src/pl/plpython/expected/plpython_record.out index 770f764d6f..7c600896ec 100644 --- a/src/pl/plpython/expected/plpython_record.out +++ b/src/pl/plpython/expected/plpython_record.out @@ -42,12 +42,10 @@ $$ LANGUAGE plpythonu; CREATE FUNCTION test_in_out_params(first in text, second out text) AS $$ return first + '_in_to_out'; $$ LANGUAGE plpythonu; --- this doesn't work yet :-( CREATE FUNCTION test_in_out_params_multi(first in text, second out text, third out text) AS $$ -return first + '_record_in_to_out'; +return (first + '_record_in_to_out_1', first + '_record_in_to_out_2'); $$ LANGUAGE plpythonu; -ERROR: PL/Python functions cannot return type record CREATE FUNCTION test_inout_params(first inout text) AS $$ return first + '_inout'; $$ LANGUAGE plpythonu; @@ -298,12 +296,12 @@ SELECT * FROM test_in_out_params('test_in'); test_in_in_to_out (1 row) --- this doesn't work yet :-( SELECT * FROM test_in_out_params_multi('test_in'); -ERROR: function test_in_out_params_multi(unknown) does not exist -LINE 1: SELECT * FROM test_in_out_params_multi('test_in'); - ^ -HINT: No function matches the given name and argument types. You might need to add explicit type casts. + second | third +----------------------------+---------------------------- + test_in_record_in_to_out_1 | test_in_record_in_to_out_2 +(1 row) + SELECT * FROM test_inout_params('test_in'); first --------------- diff --git a/src/pl/plpython/expected/plpython_trigger.out b/src/pl/plpython/expected/plpython_trigger.out index e04da221f7..2ef66a8f06 100644 --- a/src/pl/plpython/expected/plpython_trigger.out +++ b/src/pl/plpython/expected/plpython_trigger.out @@ -549,3 +549,21 @@ SELECT * FROM pb; b | 2010-10-13 21:57:29 (1 row) +-- triggers for tables with composite types +CREATE TABLE comp1 (i integer, j boolean); +CREATE TYPE comp2 AS (k integer, l boolean); +CREATE TABLE composite_trigger_test (f1 comp1, f2 comp2); +CREATE FUNCTION composite_trigger_f() RETURNS trigger AS $$ + TD['new']['f1'] = (3, False) + TD['new']['f2'] = {'k': 7, 'l': 'yes', 'ignored': 10} + return 'MODIFY' +$$ LANGUAGE plpythonu; +CREATE TRIGGER composite_trigger BEFORE INSERT ON composite_trigger_test + FOR EACH ROW EXECUTE PROCEDURE composite_trigger_f(); +INSERT INTO composite_trigger_test VALUES (NULL, NULL); +SELECT * FROM composite_trigger_test; + f1 | f2 +-------+------- + (3,f) | (7,t) +(1 row) + diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c index 3013fc8a79..e5832782fb 100644 --- a/src/pl/plpython/plpython.c +++ b/src/pl/plpython/plpython.c @@ -132,6 +132,7 @@ typedef struct PLyDatumToOb PLyDatumToObFunc func; FmgrInfo typfunc; /* The type's output function */ Oid typoid; /* The OID of the type */ + int32 typmod; /* The typmod of the type */ Oid typioparam; bool typbyval; int16 typlen; @@ -164,6 +165,7 @@ typedef struct PLyObToDatum PLyObToDatumFunc func; FmgrInfo typfunc; /* The type's input function */ Oid typoid; /* The OID of the type */ + int32 typmod; /* The typmod of the type */ Oid typioparam; bool typbyval; int16 typlen; @@ -348,6 +350,7 @@ static void PLy_input_datum_func(PLyTypeInfo *, Oid, HeapTuple); static void PLy_input_datum_func2(PLyDatumToOb *, Oid, HeapTuple); static void PLy_output_tuple_funcs(PLyTypeInfo *, TupleDesc); static void PLy_input_tuple_funcs(PLyTypeInfo *, TupleDesc); +static void PLy_output_record_funcs(PLyTypeInfo *, TupleDesc); /* conversion functions */ static PyObject *PLyBool_FromBool(PLyDatumToOb *arg, Datum d); @@ -365,12 +368,14 @@ static PyObject *PLyDict_FromTuple(PLyTypeInfo *, HeapTuple, TupleDesc); static Datum PLyObject_ToBool(PLyObToDatum *, int32, PyObject *); static Datum PLyObject_ToBytea(PLyObToDatum *, int32, PyObject *); +static Datum PLyObject_ToComposite(PLyObToDatum *, int32, PyObject *); static Datum PLyObject_ToDatum(PLyObToDatum *, int32, PyObject *); static Datum PLySequence_ToArray(PLyObToDatum *, int32, PyObject *); -static HeapTuple PLyMapping_ToTuple(PLyTypeInfo *, PyObject *); -static HeapTuple PLySequence_ToTuple(PLyTypeInfo *, PyObject *); -static HeapTuple PLyObject_ToTuple(PLyTypeInfo *, PyObject *); +static HeapTuple PLyObject_ToTuple(PLyTypeInfo *, TupleDesc, PyObject *); +static HeapTuple PLyMapping_ToTuple(PLyTypeInfo *, TupleDesc, PyObject *); +static HeapTuple PLySequence_ToTuple(PLyTypeInfo *, TupleDesc, PyObject *); +static HeapTuple PLyGenericObject_ToTuple(PLyTypeInfo *, TupleDesc, PyObject *); /* * Currently active plpython function @@ -1165,17 +1170,19 @@ PLy_function_handler(FunctionCallInfo fcinfo, PLyProcedure *proc) } else if (proc->result.is_rowtype >= 1) { + TupleDesc desc; HeapTuple tuple = NULL; - if (PySequence_Check(plrv)) - /* composite type as sequence (tuple, list etc) */ - tuple = PLySequence_ToTuple(&proc->result, plrv); - else if (PyMapping_Check(plrv)) - /* composite type as mapping (currently only dict) */ - tuple = PLyMapping_ToTuple(&proc->result, plrv); - else - /* returned as smth, must provide method __getattr__(name) */ - tuple = PLyObject_ToTuple(&proc->result, plrv); + /* make sure it's not an unnamed record */ + Assert((proc->result.out.d.typoid == RECORDOID && + proc->result.out.d.typmod != -1) || + (proc->result.out.d.typoid != RECORDOID && + proc->result.out.d.typmod == -1)); + + desc = lookup_rowtype_tupdesc(proc->result.out.d.typoid, + proc->result.out.d.typmod); + + tuple = PLyObject_ToTuple(&proc->result, desc, plrv); if (tuple != NULL) { @@ -1307,6 +1314,21 @@ PLy_function_build_args(FunctionCallInfo fcinfo, PLyProcedure *proc) PLy_elog(ERROR, "PyDict_SetItemString() failed, while setting up arguments"); arg = NULL; } + + /* Set up output conversion for functions returning RECORD */ + if (proc->result.out.d.typoid == RECORDOID) + { + TupleDesc desc; + + if (get_call_result_type(fcinfo, NULL, &desc) != TYPEFUNC_COMPOSITE) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("function returning record called in context " + "that cannot accept type record"))); + + /* cache the output conversion functions */ + PLy_output_record_funcs(&(proc->result), desc); + } } PG_CATCH(); { @@ -1508,32 +1530,37 @@ PLy_procedure_create(HeapTuple procTup, Oid fn_oid, bool is_trigger) procStruct->prorettype); rvTypeStruct = (Form_pg_type) GETSTRUCT(rvTypeTup); - /* Disallow pseudotype result, except for void */ - if (rvTypeStruct->typtype == TYPTYPE_PSEUDO && - procStruct->prorettype != VOIDOID) + /* Disallow pseudotype result, except for void or record */ + if (rvTypeStruct->typtype == TYPTYPE_PSEUDO) { if (procStruct->prorettype == TRIGGEROID) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("trigger functions can only be called as triggers"))); - else + else if (procStruct->prorettype != VOIDOID && + procStruct->prorettype != RECORDOID) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("PL/Python functions cannot return type %s", - format_type_be(procStruct->prorettype)))); + errmsg("PL/Python functions cannot return type %s", + format_type_be(procStruct->prorettype)))); } - if (rvTypeStruct->typtype == TYPTYPE_COMPOSITE) + if (rvTypeStruct->typtype == TYPTYPE_COMPOSITE || + procStruct->prorettype == RECORDOID) { /* * Tuple: set up later, during first call to * PLy_function_handler */ proc->result.out.d.typoid = procStruct->prorettype; + proc->result.out.d.typmod = -1; proc->result.is_rowtype = 2; } else + { + /* do the real work */ PLy_output_datum_func(&proc->result, rvTypeTup); + } ReleaseSysCache(rvTypeTup); } @@ -1843,6 +1870,29 @@ PLy_input_tuple_funcs(PLyTypeInfo *arg, TupleDesc desc) } static void +PLy_output_record_funcs(PLyTypeInfo *arg, TupleDesc desc) +{ + /* + * If the output record functions are already set, we just have to check + * if the record descriptor has not changed + */ + if ((arg->is_rowtype == 1) && + (arg->out.d.typmod != -1) && + (arg->out.d.typmod == desc->tdtypmod)) + return; + + /* bless the record to make it known to the typcache lookup code */ + BlessTupleDesc(desc); + /* save the freshly generated typmod */ + arg->out.d.typmod = desc->tdtypmod; + /* proceed with normal I/O function caching */ + PLy_output_tuple_funcs(arg, desc); + /* it should change is_rowtype to 1, so we won't go through this again + * unless the the output record description changes */ + Assert(arg->is_rowtype == 1); +} + +static void PLy_output_tuple_funcs(PLyTypeInfo *arg, TupleDesc desc) { int i; @@ -1898,6 +1948,7 @@ PLy_output_datum_func2(PLyObToDatum *arg, HeapTuple typeTup) perm_fmgr_info(typeStruct->typinput, &arg->typfunc); arg->typoid = HeapTupleGetOid(typeTup); + arg->typmod = -1; arg->typioparam = getTypeIOParam(typeTup); arg->typbyval = typeStruct->typbyval; @@ -1920,6 +1971,12 @@ PLy_output_datum_func2(PLyObToDatum *arg, HeapTuple typeTup) break; } + /* Composite types need their own input routine, though */ + if (typeStruct->typtype == TYPTYPE_COMPOSITE) + { + arg->func = PLyObject_ToComposite; + } + if (element_type) { char dummy_delim; @@ -1937,6 +1994,7 @@ PLy_output_datum_func2(PLyObToDatum *arg, HeapTuple typeTup) arg->func = PLySequence_ToArray; arg->elm->typoid = element_type; + arg->elm->typmod = -1; get_type_io_data(element_type, IOFunc_input, &arg->elm->typlen, &arg->elm->typbyval, &arg->elm->typalign, &dummy_delim, &arg->elm->typioparam, &funcid); @@ -1962,6 +2020,7 @@ PLy_input_datum_func2(PLyDatumToOb *arg, Oid typeOid, HeapTuple typeTup) /* Get the type's conversion information */ perm_fmgr_info(typeStruct->typoutput, &arg->typfunc); arg->typoid = HeapTupleGetOid(typeTup); + arg->typmod = -1; arg->typioparam = getTypeIOParam(typeTup); arg->typbyval = typeStruct->typbyval; arg->typlen = typeStruct->typlen; @@ -2008,6 +2067,7 @@ PLy_input_datum_func2(PLyDatumToOb *arg, Oid typeOid, HeapTuple typeTup) arg->elm->func = arg->func; arg->func = PLyList_FromArray; arg->elm->typoid = element_type; + arg->elm->typmod = -1; get_type_io_data(element_type, IOFunc_output, &arg->elm->typlen, &arg->elm->typbyval, &arg->elm->typalign, &dummy_delim, &arg->elm->typioparam, &funcid); @@ -2214,6 +2274,29 @@ PLyDict_FromTuple(PLyTypeInfo *info, HeapTuple tuple, TupleDesc desc) } /* + * Convert a Python object to a PostgreSQL tuple, using all supported + * conversion methods: tuple as a sequence, as a mapping or as an object that + * has __getattr__ support. + */ +static HeapTuple +PLyObject_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *plrv) +{ + HeapTuple tuple; + + if (PySequence_Check(plrv)) + /* composite type as sequence (tuple, list etc) */ + tuple = PLySequence_ToTuple(info, desc, plrv); + else if (PyMapping_Check(plrv)) + /* composite type as mapping (currently only dict) */ + tuple = PLyMapping_ToTuple(info, desc, plrv); + else + /* returned as smth, must provide method __getattr__(name) */ + tuple = PLyGenericObject_ToTuple(info, desc, plrv); + + return tuple; +} + +/* * Convert a Python object to a PostgreSQL bool datum. This can't go * through the generic conversion function, because Python attaches a * Boolean value to everything, more things than the PostgreSQL bool @@ -2276,6 +2359,50 @@ PLyObject_ToBytea(PLyObToDatum *arg, int32 typmod, PyObject *plrv) return rv; } + +/* + * Convert a Python object to a composite type. First look up the type's + * description, then route the Python object through the conversion function + * for obtaining PostgreSQL tuples. + */ +static Datum +PLyObject_ToComposite(PLyObToDatum *arg, int32 typmod, PyObject *plrv) +{ + HeapTuple tuple = NULL; + Datum rv; + PLyTypeInfo info; + TupleDesc desc; + + if (typmod != -1) + elog(ERROR, "received unnamed record type as input"); + + /* Create a dummy PLyTypeInfo */ + MemSet(&info, 0, sizeof(PLyTypeInfo)); + PLy_typeinfo_init(&info); + /* Mark it as needing output routines lookup */ + info.is_rowtype = 2; + + desc = lookup_rowtype_tupdesc(arg->typoid, arg->typmod); + + /* + * This will set up the dummy PLyTypeInfo's output conversion routines, + * since we left is_rowtype as 2. A future optimisation could be caching + * that info instead of looking it up every time a tuple is returned from + * the function. + */ + tuple = PLyObject_ToTuple(&info, desc, plrv); + + PLy_typeinfo_dealloc(&info); + + if (tuple != NULL) + rv = HeapTupleGetDatum(tuple); + else + rv = (Datum) NULL; + + return rv; +} + + /* * Generic conversion function: Convert PyObject to cstring and * cstring into PostgreSQL type. @@ -2379,9 +2506,8 @@ PLySequence_ToArray(PLyObToDatum *arg, int32 typmod, PyObject *plrv) } static HeapTuple -PLyMapping_ToTuple(PLyTypeInfo *info, PyObject *mapping) +PLyMapping_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *mapping) { - TupleDesc desc; HeapTuple tuple; Datum *values; bool *nulls; @@ -2389,7 +2515,6 @@ PLyMapping_ToTuple(PLyTypeInfo *info, PyObject *mapping) Assert(PyMapping_Check(mapping)); - desc = lookup_rowtype_tupdesc(info->out.d.typoid, -1); if (info->is_rowtype == 2) PLy_output_tuple_funcs(info, desc); Assert(info->is_rowtype == 1); @@ -2450,9 +2575,8 @@ PLyMapping_ToTuple(PLyTypeInfo *info, PyObject *mapping) static HeapTuple -PLySequence_ToTuple(PLyTypeInfo *info, PyObject *sequence) +PLySequence_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *sequence) { - TupleDesc desc; HeapTuple tuple; Datum *values; bool *nulls; @@ -2466,7 +2590,6 @@ PLySequence_ToTuple(PLyTypeInfo *info, PyObject *sequence) * can ignore exceeding items or assume missing ones as null but to avoid * plpython developer's errors we are strict here */ - desc = lookup_rowtype_tupdesc(info->out.d.typoid, -1); idx = 0; for (i = 0; i < desc->natts; i++) { @@ -2534,15 +2657,13 @@ PLySequence_ToTuple(PLyTypeInfo *info, PyObject *sequence) static HeapTuple -PLyObject_ToTuple(PLyTypeInfo *info, PyObject *object) +PLyGenericObject_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *object) { - TupleDesc desc; HeapTuple tuple; Datum *values; bool *nulls; volatile int i; - desc = lookup_rowtype_tupdesc(info->out.d.typoid, -1); if (info->is_rowtype == 2) PLy_output_tuple_funcs(info, desc); Assert(info->is_rowtype == 1); diff --git a/src/pl/plpython/sql/plpython_composite.sql b/src/pl/plpython/sql/plpython_composite.sql new file mode 100644 index 0000000000..cee98f288d --- /dev/null +++ b/src/pl/plpython/sql/plpython_composite.sql @@ -0,0 +1,168 @@ +CREATE FUNCTION multiout_simple(OUT i integer, OUT j integer) AS $$ +return (1, 2) +$$ LANGUAGE plpythonu; + +SELECT multiout_simple(); +SELECT * FROM multiout_simple(); +SELECT i, j + 2 FROM multiout_simple(); +SELECT (multiout_simple()).j + 3; + +CREATE FUNCTION multiout_simple_setof(n integer = 1, OUT integer, OUT integer) RETURNS SETOF record AS $$ +return [(1, 2)] * n +$$ LANGUAGE plpythonu; + +SELECT multiout_simple_setof(); +SELECT * FROM multiout_simple_setof(); +SELECT * FROM multiout_simple_setof(3); + +CREATE FUNCTION multiout_record_as(typ text, + first text, OUT first text, + second integer, OUT second integer, + retnull boolean) RETURNS record AS $$ +if retnull: + return None +if typ == 'dict': + return { 'first': first, 'second': second, 'additionalfield': 'must not cause trouble' } +elif typ == 'tuple': + return ( first, second ) +elif typ == 'list': + return [ first, second ] +elif typ == 'obj': + class type_record: pass + type_record.first = first + type_record.second = second + return type_record +$$ LANGUAGE plpythonu; + +SELECT * FROM multiout_record_as('dict', 'foo', 1, 'f'); +SELECT multiout_record_as('dict', 'foo', 1, 'f'); +SELECT *, s IS NULL AS snull FROM multiout_record_as('tuple', 'xxx', NULL, 'f') AS f(f, s); +SELECT *, f IS NULL AS fnull, s IS NULL AS snull FROM multiout_record_as('tuple', 'xxx', 1, 't') AS f(f, s); +SELECT * FROM multiout_record_as('obj', NULL, 10, 'f'); + +CREATE FUNCTION multiout_setof(n integer, + OUT power_of_2 integer, + OUT length integer) RETURNS SETOF record AS $$ +for i in range(n): + power = 2 ** i + length = plpy.execute("select length('%d')" % power)[0]['length'] + yield power, length +$$ LANGUAGE plpythonu; + +SELECT * FROM multiout_setof(3); +SELECT multiout_setof(5); + +CREATE FUNCTION multiout_return_table() RETURNS TABLE (x integer, y text) AS $$ +return [{'x': 4, 'y' :'four'}, + {'x': 7, 'y' :'seven'}, + {'x': 0, 'y' :'zero'}] +$$ LANGUAGE plpythonu; + +SELECT * FROM multiout_return_table(); + +CREATE FUNCTION multiout_array(OUT integer[], OUT text) RETURNS SETOF record AS $$ +yield [[1], 'a'] +yield [[1,2], 'b'] +yield [[1,2,3], None] +$$ LANGUAGE plpythonu; + +SELECT * FROM multiout_array(); + +CREATE FUNCTION singleout_composite(OUT type_record) AS $$ +return {'first': 1, 'second': 2} +$$ LANGUAGE plpythonu; + +CREATE FUNCTION multiout_composite(OUT type_record) RETURNS SETOF type_record AS $$ +return [{'first': 1, 'second': 2}, + {'first': 3, 'second': 4 }] +$$ LANGUAGE plpythonu; + +SELECT * FROM singleout_composite(); +SELECT * FROM multiout_composite(); + +-- composite OUT parameters in functions returning RECORD not supported yet +CREATE FUNCTION multiout_composite(INOUT n integer, OUT type_record) AS $$ +return (n, (n * 2, n * 3)) +$$ LANGUAGE plpythonu; + +CREATE FUNCTION multiout_table_type_setof(typ text, returnnull boolean, INOUT n integer, OUT table_record) RETURNS SETOF record AS $$ +if returnnull: + d = None +elif typ == 'dict': + d = {'first': n * 2, 'second': n * 3, 'extra': 'not important'} +elif typ == 'tuple': + d = (n * 2, n * 3) +elif typ == 'obj': + class d: pass + d.first = n * 2 + d.second = n * 3 +for i in range(n): + yield (i, d) +$$ LANGUAGE plpythonu; + +SELECT * FROM multiout_composite(2); +SELECT * FROM multiout_table_type_setof('dict', 'f', 3); +SELECT * FROM multiout_table_type_setof('tuple', 'f', 2); +SELECT * FROM multiout_table_type_setof('obj', 'f', 4); +SELECT * FROM multiout_table_type_setof('dict', 't', 3); + +-- check what happens if a type changes under us + +CREATE TABLE changing ( + i integer, + j integer +); + +CREATE FUNCTION changing_test(OUT n integer, OUT changing) RETURNS SETOF record AS $$ +return [(1, {'i': 1, 'j': 2}), + (1, (3, 4))] +$$ LANGUAGE plpythonu; + +SELECT * FROM changing_test(); +ALTER TABLE changing DROP COLUMN j; +SELECT * FROM changing_test(); +SELECT * FROM changing_test(); +ALTER TABLE changing ADD COLUMN j integer; +SELECT * FROM changing_test(); + +-- tables of composite types (not yet implemented) + +CREATE FUNCTION composite_types_table(OUT tab table_record[], OUT typ type_record[] ) RETURNS SETOF record AS $$ +yield {'tab': [['first', 1], ['second', 2]], + 'typ': [{'first': 'third', 'second': 3}, + {'first': 'fourth', 'second': 4}]} +yield {'tab': [['first', 1], ['second', 2]], + 'typ': [{'first': 'third', 'second': 3}, + {'first': 'fourth', 'second': 4}]} +yield {'tab': [['first', 1], ['second', 2]], + 'typ': [{'first': 'third', 'second': 3}, + {'first': 'fourth', 'second': 4}]} +$$ LANGUAGE plpythonu; + +SELECT * FROM composite_types_table(); + +-- check what happens if the output record descriptor changes +CREATE FUNCTION return_record(t text) RETURNS record AS $$ +return {'t': t, 'val': 10} +$$ LANGUAGE plpythonu; + +SELECT * FROM return_record('abc') AS r(t text, val integer); +SELECT * FROM return_record('abc') AS r(t text, val bigint); +SELECT * FROM return_record('abc') AS r(t text, val integer); +SELECT * FROM return_record('abc') AS r(t varchar(30), val integer); +SELECT * FROM return_record('abc') AS r(t varchar(100), val integer); +SELECT * FROM return_record('999') AS r(val text, t integer); + +CREATE FUNCTION return_record_2(t text) RETURNS record AS $$ +return {'v1':1,'v2':2,t:3} +$$ LANGUAGE plpythonu; + +SELECT * FROM return_record_2('v3') AS (v3 int, v2 int, v1 int); +SELECT * FROM return_record_2('v3') AS (v2 int, v3 int, v1 int); +SELECT * FROM return_record_2('v4') AS (v1 int, v4 int, v2 int); +SELECT * FROM return_record_2('v4') AS (v1 int, v4 int, v2 int); +-- error +SELECT * FROM return_record_2('v4') AS (v1 int, v3 int, v2 int); +-- works +SELECT * FROM return_record_2('v3') AS (v1 int, v3 int, v2 int); +SELECT * FROM return_record_2('v3') AS (v1 int, v2 int, v3 int); diff --git a/src/pl/plpython/sql/plpython_record.sql b/src/pl/plpython/sql/plpython_record.sql index 5a41565a9d..d727e6054f 100644 --- a/src/pl/plpython/sql/plpython_record.sql +++ b/src/pl/plpython/sql/plpython_record.sql @@ -49,10 +49,9 @@ CREATE FUNCTION test_in_out_params(first in text, second out text) AS $$ return first + '_in_to_out'; $$ LANGUAGE plpythonu; --- this doesn't work yet :-( CREATE FUNCTION test_in_out_params_multi(first in text, second out text, third out text) AS $$ -return first + '_record_in_to_out'; +return (first + '_record_in_to_out_1', first + '_record_in_to_out_2'); $$ LANGUAGE plpythonu; CREATE FUNCTION test_inout_params(first inout text) AS $$ @@ -110,7 +109,6 @@ SELECT * FROM test_type_record_as('obj', 'three', 3, false); SELECT * FROM test_type_record_as('obj', null, null, true); SELECT * FROM test_in_out_params('test_in'); --- this doesn't work yet :-( SELECT * FROM test_in_out_params_multi('test_in'); SELECT * FROM test_inout_params('test_in'); diff --git a/src/pl/plpython/sql/plpython_trigger.sql b/src/pl/plpython/sql/plpython_trigger.sql index 4994d8fe7b..2afdf51127 100644 --- a/src/pl/plpython/sql/plpython_trigger.sql +++ b/src/pl/plpython/sql/plpython_trigger.sql @@ -326,3 +326,23 @@ INSERT INTO pb VALUES ('a', '2010-10-09 21:57:33.930486'); SELECT * FROM pb; UPDATE pb SET a = 'b'; SELECT * FROM pb; + + +-- triggers for tables with composite types + +CREATE TABLE comp1 (i integer, j boolean); +CREATE TYPE comp2 AS (k integer, l boolean); + +CREATE TABLE composite_trigger_test (f1 comp1, f2 comp2); + +CREATE FUNCTION composite_trigger_f() RETURNS trigger AS $$ + TD['new']['f1'] = (3, False) + TD['new']['f2'] = {'k': 7, 'l': 'yes', 'ignored': 10} + return 'MODIFY' +$$ LANGUAGE plpythonu; + +CREATE TRIGGER composite_trigger BEFORE INSERT ON composite_trigger_test + FOR EACH ROW EXECUTE PROCEDURE composite_trigger_f(); + +INSERT INTO composite_trigger_test VALUES (NULL, NULL); +SELECT * FROM composite_trigger_test; -- 2.11.0