REVIEW: PL/Python table functions
Started by Hitoshi Haradaalmost 15 years ago11 messages
This is a review for https://commitfest.postgresql.org/action/patch_view?id=460
== Submission ==
The patch applies and compiles with success, on the top of the general
refactor patch. It is possible it cannot in HEAD now that the part of
the refactor patch applied in the core. I'll check it after the whole
of refactor gets in the core.
make installcheck passes all 18 tests.
== Usability and Feature ==
The patch works fine in general. I created some functions for my tests like:
CREATE OR REPLACE FUNCTION twitter_response(q text, OUT from_user
text, OUT message text) RETURNS SETOF record AS $$
import sys
import urllib2
import simplejson
def fetch(q):
url = 'http://search.twitter.com/search.json?q=%s' % (q)
response = urllib2.urlopen(url)
return response.read()
root = simplejson.loads(fetch(q))
for result in root['results']:
from_user = result['from_user']
message = result['text']
yield from_user, message
$$ LANGUAGE plpythonu STRICT;
and other versions that modify parameter types, return type and the
code body itself.
One issue is typmod of record type.
regression=# create or replace function func1(t text) returns record
as $$ return {'name': t, 'value': 0} $$ language plpythonu;
regression=# select * from func1('jan') as (name text, value bigint);
name | value
------+-------
jan | 0
(1 row)
regression=# select * from func1('jan') as (name text, value int);
ERROR: function return row and query-specified return row do not match
DETAIL: Returned type bigint at ordinal position 2, but query expects integer.
It seems that typmod of PLyTypeInfo is not updated over statements
within the session. I saw the error doesn't occur when I re-connect to
the backend after the error.
== Performance ==
I didn't test performance regression. My static code analysis doesn't
tell it has critical performance issue.
I mark this as "Waiting on Author" for the typmod issue.
Regards,
--
Hitoshi Harada
Re: REVIEW: PL/Python table functions
On 22/01/11 11:15, Hitoshi Harada wrote:
This is a review for https://commitfest.postgresql.org/action/patch_view?id=460
Thanks,
One issue is typmod of record type.
regression=# create or replace function func1(t text) returns record
as $$ return {'name': t, 'value': 0} $$ language plpythonu;
regression=# select * from func1('jan') as (name text, value bigint);
name | value
------+-------
jan | 0
(1 row)regression=# select * from func1('jan') as (name text, value int);
ERROR: function return row and query-specified return row do not match
DETAIL: Returned type bigint at ordinal position 2, but query expects integer.
That's a bug, thanks for spotting it.
It seems that typmod of PLyTypeInfo is not updated over statements
within the session. I saw the error doesn't occur when I re-connect to
the backend after the error.
I tracked it down to caching the I/O functions for the return type. Your
example shows that you can't just discover the output record type once,
but you have to recheck whether the returned record's structure has not
changed between calls.
I implemented it by looping over the attributes of the output record and
checking if type for which we have already cached the I/O function is
binary coercible to the type that's actually in the record. This allows
the code to skip recaching the functions in the common case when the
record stays the same, and fixes the bug you found.
Attached is a new patch for table function support and an incremental
patch with the change I did. The new patch for table functions does not
apply to HEAD, it's a refinement of the previous table-functions patch.
After the refactor patches are all applied or rejected, I'll post a
frech patch that applies cleanly to HEAD.
Thanks again,
Jan
Attachments:
plpython-table-functions-incremental.patchtext/x-patch; name=plpython-table-functions-incremental.patchDownload
diff --git a/src/pl/plpython/expected/plpython_composite.out b/src/pl/plpython/expected/plpython_composite.out
index 70a4571..1576588 100644
*** a/src/pl/plpython/expected/plpython_composite.out
--- b/src/pl/plpython/expected/plpython_composite.out
*************** SELECT * FROM composite_types_table();
*** 273,275 ****
--- 273,309 ----
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/plpython.c b/src/pl/plpython/plpython.c
index cc7a007..1f93c7b 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/lsyscache.h"
*************** static void PLy_input_datum_func(PLyType
*** 334,339 ****
--- 335,341 ----
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);
*************** PLy_function_build_args(FunctionCallInfo
*** 1262,1269 ****
}
/* Set up output conversion for functions returning RECORD */
! if (proc->result.out.d.typoid == RECORDOID &&
! proc->result.is_rowtype > 1)
{
TupleDesc desc;
--- 1264,1270 ----
}
/* Set up output conversion for functions returning RECORD */
! if (proc->result.out.d.typoid == RECORDOID)
{
TupleDesc desc;
*************** PLy_function_build_args(FunctionCallInfo
*** 1275,1288 ****
errmsg("function returning record called in context "
"that cannot accept type record")));
}
! /* bless the record to make it known to the typcache lookup code */
! BlessTupleDesc(desc);
! /* save the freshly generated typmod */
! proc->result.out.d.typmod = desc->tdtypmod;
! /* proceed with normal I/O function caching */
! PLy_output_tuple_funcs(&(proc->result), desc);
! /* it should change is_rowtype to 1, so we won't go through this again */
! Assert(proc->result.is_rowtype == 1);
}
}
PG_CATCH();
--- 1276,1284 ----
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_input_tuple_funcs(PLyTypeInfo *arg,
*** 1769,1774 ****
--- 1765,1809 ----
}
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;
diff --git a/src/pl/plpython/sql/plpython_composite.sql b/src/pl/plpython/sql/plpython_composite.sql
index 1330e26..db4bd73 100644
*** a/src/pl/plpython/sql/plpython_composite.sql
--- b/src/pl/plpython/sql/plpython_composite.sql
*************** yield {'tab': [['first', 1], ['second',
*** 140,142 ****
--- 140,153 ----
$$ 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);
plpython-table-functions.patchtext/x-patch; name=plpython-table-functions.patchDownload
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 2307627..1f93c7b 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/lsyscache.h"
*************** typedef struct PLyDatumToOb
*** 129,134 ****
--- 130,136 ----
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
*** 161,166 ****
--- 163,169 ----
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
*** 332,337 ****
--- 335,341 ----
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
*** 349,360 ****
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
--- 353,366 ----
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
*** 1112,1128 ****
}
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)
{
--- 1118,1136 ----
}
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
*** 1254,1259 ****
--- 1262,1285 ----
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_output_conversion(PLyProce
*** 1373,1400 ****
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
--- 1399,1428 ----
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 or unnamed record: 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
*************** PLy_input_tuple_funcs(PLyTypeInfo *arg,
*** 1737,1742 ****
--- 1765,1809 ----
}
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
*** 1792,1797 ****
--- 1859,1865 ----
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
*** 1814,1819 ****
--- 1882,1893 ----
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
*** 1831,1836 ****
--- 1905,1911 ----
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,
*** 1856,1861 ****
--- 1931,1937 ----
/* 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,
*** 1902,1907 ****
--- 1978,1984 ----
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
*** 2102,2107 ****
--- 2179,2207 ----
}
/*
+ * 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
*** 2164,2169 ****
--- 2264,2313 ----
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
*** 2267,2275 ****
}
static HeapTuple
! PLyMapping_ToTuple(PLyTypeInfo *info, PyObject *mapping)
{
- TupleDesc desc;
HeapTuple tuple;
Datum *values;
bool *nulls;
--- 2411,2418 ----
}
static HeapTuple
! PLyMapping_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *mapping)
{
HeapTuple tuple;
Datum *values;
bool *nulls;
*************** PLyMapping_ToTuple(PLyTypeInfo *info, Py
*** 2277,2283 ****
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);
--- 2420,2425 ----
*************** PLyMapping_ToTuple(PLyTypeInfo *info, Py
*** 2338,2346 ****
static HeapTuple
! PLySequence_ToTuple(PLyTypeInfo *info, PyObject *sequence)
{
- TupleDesc desc;
HeapTuple tuple;
Datum *values;
bool *nulls;
--- 2480,2487 ----
static HeapTuple
! PLySequence_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *sequence)
{
HeapTuple tuple;
Datum *values;
bool *nulls;
*************** PLySequence_ToTuple(PLyTypeInfo *info, P
*** 2353,2359 ****
* 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)
{
--- 2494,2499 ----
*************** PLySequence_ToTuple(PLyTypeInfo *info, P
*** 2421,2435 ****
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);
--- 2561,2573 ----
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;
Re: REVIEW: PL/Python table functions
2011/1/23 Jan Urbański <wulczer@wulczer.org>:
On 22/01/11 11:15, Hitoshi Harada wrote:
This is a review for https://commitfest.postgresql.org/action/patch_view?id=460
Thanks,
One issue is typmod of record type.
regression=# create or replace function func1(t text) returns record
as $$ return {'name': t, 'value': 0} $$ language plpythonu;
regression=# select * from func1('jan') as (name text, value bigint);
name | value
------+-------
jan | 0
(1 row)regression=# select * from func1('jan') as (name text, value int);
ERROR: function return row and query-specified return row do not match
DETAIL: Returned type bigint at ordinal position 2, but query expects integer.That's a bug, thanks for spotting it.
It seems that typmod of PLyTypeInfo is not updated over statements
within the session. I saw the error doesn't occur when I re-connect to
the backend after the error.I tracked it down to caching the I/O functions for the return type. Your
example shows that you can't just discover the output record type once,
but you have to recheck whether the returned record's structure has not
changed between calls.I implemented it by looping over the attributes of the output record and
checking if type for which we have already cached the I/O function is
binary coercible to the type that's actually in the record. This allows
the code to skip recaching the functions in the common case when the
record stays the same, and fixes the bug you found.Attached is a new patch for table function support and an incremental
patch with the change I did. The new patch for table functions does not
apply to HEAD, it's a refinement of the previous table-functions patch.
After the refactor patches are all applied or rejected, I'll post a
frech patch that applies cleanly to HEAD.
I tested the new incremental patch and the previous example works
fine. I don't know if this can be handled properly but another example
is:
regression=# create function func1(t text) returns record as $$ return
{'name':t, 'value':0}; $$ language plpythonu ;
CREATE FUNCTION
regression=# select * from func1('jan') as (name text, value bigint);
name | value
------+-------
jan | 0
(1 row)
regression=# select * from func1('jan') as (value text, name bigint);
value | name
-------+------
jan | 0
(1 row)
where value and name don't point to the correct data. Your
data-type-check logic might not be enough.
Regards,
--
Hitoshi Harada
Re: REVIEW: PL/Python table functions
On 24/01/11 05:42, Hitoshi Harada wrote:
2011/1/23 Jan Urbański <wulczer@wulczer.org>:
On 22/01/11 11:15, Hitoshi Harada wrote:
I tested the new incremental patch and the previous example works
fine. I don't know if this can be handled properly but another example
is:regression=# create function func1(t text) returns record as $$ return
{'name':t, 'value':0}; $$ language plpythonu ;
CREATE FUNCTION
regression=# select * from func1('jan') as (name text, value bigint);
name | value
------+-------
jan | 0
(1 row)regression=# select * from func1('jan') as (value text, name bigint);
value | name
-------+------
jan | 0
(1 row)where value and name don't point to the correct data. Your
data-type-check logic might not be enough.
I have to admit that I don't 100% understand what's happening when you
have a function that returns RECORD and has no OUT parameters, but I did
a quick check with PL/pgSQL and it behaves the same:
create or replace function rec(t text) returns record as $$
declare
r record;
begin
select t as name, 0 as value into r;
return r;
end;
$$ language plpgsql;
pl_regression=# select * from rec('jan') as (value text, name integer);
value | name
-------+------
jan | 0
(1 row)
pl_regression=# select * from rec('jan') as (name text, value integer);
name | value
------+-------
jan | 0
(1 row)
So PL/pgSQL seems to work positionally, whereas PL/Python uses the
variable names when fetching them from the mapping Python returned. All
in all, it works the same as in other PLs, so at least it's consistent.
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).
Cheers,
Jan
Attachments:
plpython-table-functions.patchtext/x-patch; name=plpython-table-functions.patchDownload
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 601b590..c97fc69 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;
Re: REVIEW: PL/Python table functions
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
Attachments:
plpython-table-functions.patchtext/x-patch; name=plpython-table-functions.patchDownload
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;
Re: REVIEW: PL/Python table functions
2011/1/28 Jan Urbański <wulczer@wulczer.org>:
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.
Sorry, but could you update your patch? Patching it against HEAD today
makes rej.
patching file `src/pl/plpython/Makefile'
patching file `src/pl/plpython/expected/plpython_composite.out'
patching file `src/pl/plpython/expected/plpython_record.out'
Hunk #1 succeeded at 42 with fuzz 2.
Hunk #2 FAILED at 296.
1 out of 2 hunks FAILED -- saving rejects to
src/pl/plpython/expected/plpython_record.out.rej
patching file `src/pl/plpython/expected/plpython_trigger.out'
patching file `src/pl/plpython/plpython.c'
Hunk #1 succeeded at 100 with fuzz 2.
Hunk #2 succeeded at 132 (offset 1 line).
Hunk #4 succeeded at 346 (offset 4 lines).
Hunk #6 succeeded at 1166 (offset 40 lines).
Hunk #8 succeeded at 1485 (offset 40 lines).
Hunk #10 succeeded at 1892 (offset 40 lines).
Hunk #12 succeeded at 1938 (offset 40 lines).
Hunk #14 succeeded at 2011 (offset 40 lines).
Hunk #16 succeeded at 2300 (offset 40 lines).
Hunk #18 succeeded at 2456 (offset 40 lines).
Hunk #20 succeeded at 2531 (offset 40 lines).
patching file `src/pl/plpython/sql/plpython_composite.sql'
patching file `src/pl/plpython/sql/plpython_record.sql'
patching file `src/pl/plpython/sql/plpython_trigger.sql'
Regards,
--
Hitoshi Harada
Re: REVIEW: PL/Python table functions
On 04/02/11 16:26, Hitoshi Harada wrote:
2011/1/28 Jan Urbański <wulczer@wulczer.org>:
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.Sorry, but could you update your patch? Patching it against HEAD today
makes rej.
Sure, here's an updated patch.
Jan
Attachments:
plpython-table-functions.patchtext/x-patch; name=plpython-table-functions.patchDownload
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 770f764..7c60089 100644
*** a/src/pl/plpython/expected/plpython_record.out
--- b/src/pl/plpython/expected/plpython_record.out
*************** $$ LANGUAGE plpythonu;
*** 42,53 ****
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;
- ERROR: PL/Python functions cannot return type record
CREATE FUNCTION test_inout_params(first inout text) AS $$
return first + '_inout';
$$ LANGUAGE plpythonu;
--- 42,51 ----
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';
$$ LANGUAGE plpythonu;
*************** SELECT * FROM test_in_out_params('test_i
*** 298,309 ****
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.
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 fff7de7..e3ac8de 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 "access/xact.h"
#include "utils/builtins.h"
*************** typedef struct PLyDatumToOb
*** 131,136 ****
--- 132,138 ----
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
*** 163,168 ****
--- 165,171 ----
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
*** 343,348 ****
--- 346,352 ----
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
*** 360,371 ****
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
--- 364,377 ----
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
*** 1160,1176 ****
}
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)
{
--- 1166,1184 ----
}
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
*** 1297,1302 ****
--- 1305,1328 ----
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,
*** 1459,1490 ****
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);
}
--- 1485,1521 ----
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,
*** 1767,1772 ****
--- 1798,1842 ----
}
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
*** 1822,1827 ****
--- 1892,1898 ----
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
*** 1844,1849 ****
--- 1915,1926 ----
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
*** 1861,1866 ****
--- 1938,1944 ----
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,
*** 1886,1891 ****
--- 1964,1970 ----
/* 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,
*** 1932,1937 ****
--- 2011,2017 ----
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
*** 2135,2140 ****
--- 2215,2243 ----
}
/*
+ * 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
*** 2197,2202 ****
--- 2300,2349 ----
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
*** 2300,2308 ****
}
static HeapTuple
! PLyMapping_ToTuple(PLyTypeInfo *info, PyObject *mapping)
{
- TupleDesc desc;
HeapTuple tuple;
Datum *values;
bool *nulls;
--- 2447,2454 ----
}
static HeapTuple
! PLyMapping_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *mapping)
{
HeapTuple tuple;
Datum *values;
bool *nulls;
*************** PLyMapping_ToTuple(PLyTypeInfo *info, Py
*** 2310,2316 ****
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);
--- 2456,2461 ----
*************** PLyMapping_ToTuple(PLyTypeInfo *info, Py
*** 2371,2379 ****
static HeapTuple
! PLySequence_ToTuple(PLyTypeInfo *info, PyObject *sequence)
{
- TupleDesc desc;
HeapTuple tuple;
Datum *values;
bool *nulls;
--- 2516,2523 ----
static HeapTuple
! PLySequence_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *sequence)
{
HeapTuple tuple;
Datum *values;
bool *nulls;
*************** PLySequence_ToTuple(PLyTypeInfo *info, P
*** 2387,2393 ****
* 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++)
{
--- 2531,2536 ----
*************** PLySequence_ToTuple(PLyTypeInfo *info, P
*** 2455,2469 ****
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);
--- 2598,2610 ----
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;
Re: REVIEW: PL/Python table functions
2011/2/7 Jan Urbański <wulczer@wulczer.org>:
On 04/02/11 16:26, Hitoshi Harada wrote:
2011/1/28 Jan Urbański <wulczer@wulczer.org>:
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.Sorry, but could you update your patch? Patching it against HEAD today
makes rej.Sure, here's an updated patch.
Thanks,
I revisited the problem of typeinfo cache, and I guess this is not
what you want;
db1=# create function func1(t text) returns record as $$ return
{'v1':1,'v2':2,t:3} $$ language plpythonu;
CREATE FUNCTION
db1=# select * from func1('v3') as (v3 int, v2 int, v1 int);
v3 | v2 | v1
----+----+----
3 | 2 | 1
(1 row)
db1=# select * from func1('v3') as (v1 int, v2 int, v3 int);
v1 | v2 | v3
----+----+----
3 | 2 | 1
(1 row)
db1=# select * from func1('v4') as (v1 int, v2 int, v3 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 "func1"
db1=# select * from func1('v4') as (v1 int, v2 int, v4 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 "func1"
The PL/pgSQL case you pointed earlier is consistent because it fetches
the values positionally. The column name is only an on-demand
labeling. However, for mapping dict of python into the table row
should always map it by key. At least the function author (including
me :P) expects it.
Regards,
--
Hitoshi Harada
Re: REVIEW: PL/Python table functions
On 07/02/11 06:10, Hitoshi Harada wrote:
2011/2/7 Jan Urbański <wulczer@wulczer.org>:
On 04/02/11 16:26, Hitoshi Harada wrote:
2011/1/28 Jan Urbański <wulczer@wulczer.org>:
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.Sorry, but could you update your patch? Patching it against HEAD today
makes rej.Sure, here's an updated patch.
Thanks,
I revisited the problem of typeinfo cache, and I guess this is not
what you want;[problems, now reflected in new regression tests]
The PL/pgSQL case you pointed earlier is consistent because it fetches
the values positionally. The column name is only an on-demand
labeling. However, for mapping dict of python into the table row
should always map it by key. At least the function author (including
me :P) expects it.
Yes, you're right. I tried to be too cute checking if the arguments are
binary coercible to the fit the new record description. This time I'm
just checking if the record descriptor changed at all, and if so
recaching the I/O funcs.
I hope this version does the right thing, while still avoiding the
performance hit of looking up I/O funcs every time a row is returned.
Actually, PL/Perl *does* look up the I/O funcs every time, so in the
worst case I can just drop this optimisation. But let's hope I got it
right this time :)
Thanks again for the review,
Jan
Attachments:
plpython-table-functions.patchtext/x-patch; name=plpython-table-functions.patchDownload
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 ...a8f8a62 .
*** a/src/pl/plpython/expected/plpython_composite.out
--- b/src/pl/plpython/expected/plpython_composite.out
***************
*** 0 ****
--- 1,355 ----
+ 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)
+
+ 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 770f764..7c60089 100644
*** a/src/pl/plpython/expected/plpython_record.out
--- b/src/pl/plpython/expected/plpython_record.out
*************** $$ LANGUAGE plpythonu;
*** 42,53 ****
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;
- ERROR: PL/Python functions cannot return type record
CREATE FUNCTION test_inout_params(first inout text) AS $$
return first + '_inout';
$$ LANGUAGE plpythonu;
--- 42,51 ----
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';
$$ LANGUAGE plpythonu;
*************** SELECT * FROM test_in_out_params('test_i
*** 298,309 ****
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.
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 fff7de7..10696bc 100644
*** a/src/pl/plpython/plpython.c
--- b/src/pl/plpython/plpython.c
*************** typedef struct PLyDatumToOb
*** 131,136 ****
--- 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
*** 163,168 ****
--- 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
*** 343,348 ****
--- 345,351 ----
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
*** 360,371 ****
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
--- 363,376 ----
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
*** 1160,1176 ****
}
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)
{
--- 1165,1183 ----
}
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
*** 1297,1302 ****
--- 1304,1327 ----
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,
*** 1459,1490 ****
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);
}
--- 1484,1520 ----
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,
*** 1767,1772 ****
--- 1797,1825 ----
}
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;
*************** PLy_output_datum_func2(PLyObToDatum *arg
*** 1822,1827 ****
--- 1875,1881 ----
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
*** 1844,1849 ****
--- 1898,1909 ----
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
*** 1861,1866 ****
--- 1921,1927 ----
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,
*** 1886,1891 ****
--- 1947,1953 ----
/* 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,
*** 1932,1937 ****
--- 1994,2000 ----
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
*** 2135,2140 ****
--- 2198,2226 ----
}
/*
+ * 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
*** 2197,2202 ****
--- 2283,2332 ----
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
*** 2300,2308 ****
}
static HeapTuple
! PLyMapping_ToTuple(PLyTypeInfo *info, PyObject *mapping)
{
- TupleDesc desc;
HeapTuple tuple;
Datum *values;
bool *nulls;
--- 2430,2437 ----
}
static HeapTuple
! PLyMapping_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *mapping)
{
HeapTuple tuple;
Datum *values;
bool *nulls;
*************** PLyMapping_ToTuple(PLyTypeInfo *info, Py
*** 2310,2316 ****
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);
--- 2439,2444 ----
*************** PLyMapping_ToTuple(PLyTypeInfo *info, Py
*** 2371,2379 ****
static HeapTuple
! PLySequence_ToTuple(PLyTypeInfo *info, PyObject *sequence)
{
- TupleDesc desc;
HeapTuple tuple;
Datum *values;
bool *nulls;
--- 2499,2506 ----
static HeapTuple
! PLySequence_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *sequence)
{
HeapTuple tuple;
Datum *values;
bool *nulls;
*************** PLySequence_ToTuple(PLyTypeInfo *info, P
*** 2387,2393 ****
* 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++)
{
--- 2514,2519 ----
*************** PLySequence_ToTuple(PLyTypeInfo *info, P
*** 2455,2469 ****
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);
--- 2581,2593 ----
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 ...e1e2cc5 .
*** a/src/pl/plpython/sql/plpython_composite.sql
--- b/src/pl/plpython/sql/plpython_composite.sql
***************
*** 0 ****
--- 1,167 ----
+ 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);
+
+ 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 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;
Re: REVIEW: PL/Python table functions
2011/2/9 Jan Urbański <wulczer@wulczer.org>:
I hope this version does the right thing, while still avoiding the
performance hit of looking up I/O funcs every time a row is returned.
Actually, PL/Perl *does* look up the I/O funcs every time, so in the
worst case I can just drop this optimisation. But let's hope I got it
right this time :)
I tested it on the issue above and things around trigger, and looked
good to me. Although I'm not sure if I understand the code overall,
but the modification where I'm unclear seems covered by the regression
tests.
I mark this "Ready for Committer."
Regards,
--
Hitoshi Harada
