On 27/01/11 00:41, Jan Urbański wrote:
> I'm also attaching an updated version that should apply on top of my
> github refactor branch (or incrementally over the new set of refactor
> patches that I will post shortly to the refactor thread).
Attached is a patch for master, as the refactorings have already been
merged.
Jan
diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile
index 16d78ae..167393e 100644
*** a/src/pl/plpython/Makefile
--- b/src/pl/plpython/Makefile
*************** REGRESS = \
*** 79,84 ****
--- 79,85 ----
plpython_types \
plpython_error \
plpython_unicode \
+ 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
index ...1576588 .
*** a/src/pl/plpython/expected/plpython_composite.out
--- b/src/pl/plpython/expected/plpython_composite.out
***************
*** 0 ****
--- 1,309 ----
+ 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)
+
diff --git a/src/pl/plpython/expected/plpython_record.out b/src/pl/plpython/expected/plpython_record.out
index c8c4f9d..7c60089 100644
*** a/src/pl/plpython/expected/plpython_record.out
--- b/src/pl/plpython/expected/plpython_record.out
*************** $$ LANGUAGE plpythonu;
*** 42,51 ****
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';
$$ LANGUAGE plpythonu;
CREATE FUNCTION test_inout_params(first inout text) AS $$
return first + '_inout';
--- 42,50 ----
CREATE FUNCTION test_in_out_params(first in text, second out text) AS $$
return first + '_in_to_out';
$$ LANGUAGE plpythonu;
CREATE FUNCTION test_in_out_params_multi(first in text,
second out text, third out text) AS $$
! return (first + '_record_in_to_out_1', first + '_record_in_to_out_2');
$$ LANGUAGE plpythonu;
CREATE FUNCTION test_inout_params(first inout text) AS $$
return first + '_inout';
*************** SELECT * FROM test_in_out_params('test_i
*** 297,305 ****
test_in_in_to_out
(1 row)
- -- this doesn't work yet :-(
SELECT * FROM test_in_out_params_multi('test_in');
! ERROR: PL/Python functions cannot return type record
SELECT * FROM test_inout_params('test_in');
first
---------------
--- 296,307 ----
test_in_in_to_out
(1 row)
SELECT * FROM test_in_out_params_multi('test_in');
! 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 e04da22..2ef66a8 100644
*** a/src/pl/plpython/expected/plpython_trigger.out
--- b/src/pl/plpython/expected/plpython_trigger.out
*************** SELECT * FROM pb;
*** 549,551 ****
--- 549,569 ----
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 aafe556..0a800c5 100644
*** a/src/pl/plpython/plpython.c
--- b/src/pl/plpython/plpython.c
*************** typedef int Py_ssize_t;
*** 100,105 ****
--- 100,106 ----
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "parser/parse_type.h"
+ #include "parser/parse_coerce.h"
#include "tcop/tcopprot.h"
#include "utils/builtins.h"
#include "utils/hsearch.h"
*************** typedef struct PLyDatumToOb
*** 130,135 ****
--- 131,137 ----
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;
*************** typedef struct PLyObToDatum
*** 162,167 ****
--- 164,170 ----
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;
*************** static void PLy_input_datum_func(PLyType
*** 339,344 ****
--- 342,348 ----
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);
*************** static PyObject *PLyDict_FromTuple(PLyTy
*** 356,367 ****
static Datum PLyObject_ToBool(PLyObToDatum *, int32, PyObject *);
static Datum PLyObject_ToBytea(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 *);
/*
* Currently active plpython function
--- 360,373 ----
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 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
*************** PLy_function_handler(FunctionCallInfo fc
*** 1120,1136 ****
}
else if (proc->result.is_rowtype >= 1)
{
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);
if (tuple != NULL)
{
--- 1126,1144 ----
}
else if (proc->result.is_rowtype >= 1)
{
+ TupleDesc desc;
HeapTuple tuple = NULL;
! /* 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)
{
*************** PLy_function_build_args(FunctionCallInfo
*** 1257,1262 ****
--- 1265,1288 ----
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();
{
*************** PLy_procedure_create(HeapTuple procTup,
*** 1419,1450 ****
procStruct->prorettype);
rvTypeStruct = (Form_pg_type) GETSTRUCT(rvTypeTup);
! /* Disallow pseudotype result, except for void */
! if (rvTypeStruct->typtype == TYPTYPE_PSEUDO &&
! procStruct->prorettype != VOIDOID)
{
if (procStruct->prorettype == TRIGGEROID)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("trigger functions can only be called as triggers")));
! else
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("PL/Python functions cannot return type %s",
! format_type_be(procStruct->prorettype))));
}
! if (rvTypeStruct->typtype == TYPTYPE_COMPOSITE)
{
/*
* Tuple: set up later, during first call to
* PLy_function_handler
*/
proc->result.out.d.typoid = procStruct->prorettype;
proc->result.is_rowtype = 2;
}
else
PLy_output_datum_func(&proc->result, rvTypeTup);
ReleaseSysCache(rvTypeTup);
}
--- 1445,1481 ----
procStruct->prorettype);
rvTypeStruct = (Form_pg_type) GETSTRUCT(rvTypeTup);
! /* 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 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))));
}
! 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);
}
*************** PLy_input_tuple_funcs(PLyTypeInfo *arg,
*** 1727,1732 ****
--- 1758,1802 ----
}
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
+ */
+ bool can_skip = false;
+
+ if (arg->is_rowtype == 1)
+ {
+ int i;
+
+ /* the functions are already set, check the attributes */
+ Assert(arg->out.r.natts == desc->natts);
+ can_skip = true;
+
+ for (i = 0; i < arg->out.r.natts; i++)
+ {
+ if (!IsBinaryCoercible(arg->out.r.atts[i].typoid,
+ desc->attrs[i]->atttypid))
+ can_skip = false;
+ }
+ }
+
+ if (can_skip)
+ 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;
*************** PLy_output_datum_func2(PLyObToDatum *arg
*** 1782,1787 ****
--- 1852,1858 ----
perm_fmgr_info(typeStruct->typinput, &arg->typfunc);
arg->typoid = HeapTupleGetOid(typeTup);
+ arg->typmod = -1;
arg->typioparam = getTypeIOParam(typeTup);
arg->typbyval = typeStruct->typbyval;
*************** PLy_output_datum_func2(PLyObToDatum *arg
*** 1804,1809 ****
--- 1875,1886 ----
break;
}
+ /* Composite types need their own input routine, though */
+ if (typeStruct->typtype == TYPTYPE_COMPOSITE)
+ {
+ arg->func = PLyObject_ToComposite;
+ }
+
if (element_type)
{
char dummy_delim;
*************** PLy_output_datum_func2(PLyObToDatum *arg
*** 1821,1826 ****
--- 1898,1904 ----
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);
*************** PLy_input_datum_func2(PLyDatumToOb *arg,
*** 1846,1851 ****
--- 1924,1930 ----
/* 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;
*************** PLy_input_datum_func2(PLyDatumToOb *arg,
*** 1892,1897 ****
--- 1971,1977 ----
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);
*************** PLyDict_FromTuple(PLyTypeInfo *info, Hea
*** 2095,2100 ****
--- 2175,2203 ----
}
/*
+ * 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
*************** PLyObject_ToBytea(PLyObToDatum *arg, int
*** 2157,2162 ****
--- 2260,2309 ----
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.
*************** PLySequence_ToArray(PLyObToDatum *arg, i
*** 2260,2268 ****
}
static HeapTuple
! PLyMapping_ToTuple(PLyTypeInfo *info, PyObject *mapping)
{
- TupleDesc desc;
HeapTuple tuple;
Datum *values;
bool *nulls;
--- 2407,2414 ----
}
static HeapTuple
! PLyMapping_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *mapping)
{
HeapTuple tuple;
Datum *values;
bool *nulls;
*************** PLyMapping_ToTuple(PLyTypeInfo *info, Py
*** 2270,2276 ****
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);
--- 2416,2421 ----
*************** PLyMapping_ToTuple(PLyTypeInfo *info, Py
*** 2331,2339 ****
static HeapTuple
! PLySequence_ToTuple(PLyTypeInfo *info, PyObject *sequence)
{
- TupleDesc desc;
HeapTuple tuple;
Datum *values;
bool *nulls;
--- 2476,2483 ----
static HeapTuple
! PLySequence_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *sequence)
{
HeapTuple tuple;
Datum *values;
bool *nulls;
*************** PLySequence_ToTuple(PLyTypeInfo *info, P
*** 2347,2353 ****
* 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++)
{
--- 2491,2496 ----
*************** PLySequence_ToTuple(PLyTypeInfo *info, P
*** 2415,2429 ****
static HeapTuple
! PLyObject_ToTuple(PLyTypeInfo *info, 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);
--- 2558,2570 ----
static HeapTuple
! PLyGenericObject_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *object)
{
HeapTuple tuple;
Datum *values;
bool *nulls;
volatile int i;
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
index ...db4bd73 .
*** a/src/pl/plpython/sql/plpython_composite.sql
--- b/src/pl/plpython/sql/plpython_composite.sql
***************
*** 0 ****
--- 1,153 ----
+ 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);
diff --git a/src/pl/plpython/sql/plpython_record.sql b/src/pl/plpython/sql/plpython_record.sql
index 5a41565..d727e60 100644
*** a/src/pl/plpython/sql/plpython_record.sql
--- b/src/pl/plpython/sql/plpython_record.sql
*************** CREATE FUNCTION test_in_out_params(first
*** 49,58 ****
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';
$$ LANGUAGE plpythonu;
CREATE FUNCTION test_inout_params(first inout text) AS $$
--- 49,57 ----
return first + '_in_to_out';
$$ LANGUAGE plpythonu;
CREATE FUNCTION test_in_out_params_multi(first in text,
second out text, third out text) AS $$
! return (first + '_record_in_to_out_1', first + '_record_in_to_out_2');
$$ LANGUAGE plpythonu;
CREATE FUNCTION test_inout_params(first inout text) AS $$
*************** SELECT * FROM test_type_record_as('obj',
*** 110,116 ****
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');
--- 109,114 ----
diff --git a/src/pl/plpython/sql/plpython_trigger.sql b/src/pl/plpython/sql/plpython_trigger.sql
index 4994d8f..2afdf51 100644
*** a/src/pl/plpython/sql/plpython_trigger.sql
--- b/src/pl/plpython/sql/plpython_trigger.sql
*************** INSERT INTO pb VALUES ('a', '2010-10-09
*** 326,328 ****
--- 326,348 ----
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;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
