diff options
Diffstat (limited to 'src/pl/plpython/expected')
25 files changed, 5713 insertions, 0 deletions
diff --git a/src/pl/plpython/expected/README b/src/pl/plpython/expected/README new file mode 100644 index 0000000..388c553 --- /dev/null +++ b/src/pl/plpython/expected/README @@ -0,0 +1,3 @@ +Guide to alternative expected files: + +plpython_error_5.out Python 3.5 and newer diff --git a/src/pl/plpython/expected/plpython_call.out b/src/pl/plpython/expected/plpython_call.out new file mode 100644 index 0000000..4c06900 --- /dev/null +++ b/src/pl/plpython/expected/plpython_call.out @@ -0,0 +1,75 @@ +-- +-- Tests for procedures / CALL syntax +-- +CREATE PROCEDURE test_proc1() +LANGUAGE plpython3u +AS $$ +pass +$$; +CALL test_proc1(); +-- error: can't return non-None +CREATE PROCEDURE test_proc2() +LANGUAGE plpython3u +AS $$ +return 5 +$$; +CALL test_proc2(); +ERROR: PL/Python procedure did not return None +CONTEXT: PL/Python procedure "test_proc2" +CREATE TABLE test1 (a int); +CREATE PROCEDURE test_proc3(x int) +LANGUAGE plpython3u +AS $$ +plpy.execute("INSERT INTO test1 VALUES (%s)" % x) +$$; +CALL test_proc3(55); +SELECT * FROM test1; + a +---- + 55 +(1 row) + +-- output arguments +CREATE PROCEDURE test_proc5(INOUT a text) +LANGUAGE plpython3u +AS $$ +return [a + '+' + a] +$$; +CALL test_proc5('abc'); + a +--------- + abc+abc +(1 row) + +CREATE PROCEDURE test_proc6(a int, INOUT b int, INOUT c int) +LANGUAGE plpython3u +AS $$ +return (b * a, c * a) +$$; +CALL test_proc6(2, 3, 4); + b | c +---+--- + 6 | 8 +(1 row) + +-- OUT parameters +CREATE PROCEDURE test_proc9(IN a int, OUT b int) +LANGUAGE plpython3u +AS $$ +plpy.notice("a: %s" % (a)) +return (a * 2,) +$$; +DO $$ +DECLARE _a int; _b int; +BEGIN + _a := 10; _b := 30; + CALL test_proc9(_a, _b); + RAISE NOTICE '_a: %, _b: %', _a, _b; +END +$$; +NOTICE: a: 10 +NOTICE: _a: 10, _b: 20 +DROP PROCEDURE test_proc1; +DROP PROCEDURE test_proc2; +DROP PROCEDURE test_proc3; +DROP TABLE test1; diff --git a/src/pl/plpython/expected/plpython_composite.out b/src/pl/plpython/expected/plpython_composite.out new file mode 100644 index 0000000..bb101e0 --- /dev/null +++ b/src/pl/plpython/expected/plpython_composite.out @@ -0,0 +1,594 @@ +CREATE FUNCTION multiout_simple(OUT i integer, OUT j integer) AS $$ +return (1, 2) +$$ LANGUAGE plpython3u; +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 plpython3u; +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 +elif typ == 'str': + return "('%s',%r)" % (first, second) +$$ LANGUAGE plpython3u; +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 * FROM multiout_record_as('dict', null, null, false); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM multiout_record_as('dict', 'one', null, false); + first | second +-------+-------- + one | +(1 row) + +SELECT * FROM multiout_record_as('dict', null, 2, false); + first | second +-------+-------- + | 2 +(1 row) + +SELECT * FROM multiout_record_as('dict', 'three', 3, false); + first | second +-------+-------- + three | 3 +(1 row) + +SELECT * FROM multiout_record_as('dict', null, null, true); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM multiout_record_as('tuple', null, null, false); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM multiout_record_as('tuple', 'one', null, false); + first | second +-------+-------- + one | +(1 row) + +SELECT * FROM multiout_record_as('tuple', null, 2, false); + first | second +-------+-------- + | 2 +(1 row) + +SELECT * FROM multiout_record_as('tuple', 'three', 3, false); + first | second +-------+-------- + three | 3 +(1 row) + +SELECT * FROM multiout_record_as('tuple', null, null, true); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM multiout_record_as('list', null, null, false); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM multiout_record_as('list', 'one', null, false); + first | second +-------+-------- + one | +(1 row) + +SELECT * FROM multiout_record_as('list', null, 2, false); + first | second +-------+-------- + | 2 +(1 row) + +SELECT * FROM multiout_record_as('list', 'three', 3, false); + first | second +-------+-------- + three | 3 +(1 row) + +SELECT * FROM multiout_record_as('list', null, null, true); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM multiout_record_as('obj', null, null, false); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM multiout_record_as('obj', 'one', null, false); + first | second +-------+-------- + one | +(1 row) + +SELECT * FROM multiout_record_as('obj', null, 2, false); + first | second +-------+-------- + | 2 +(1 row) + +SELECT * FROM multiout_record_as('obj', 'three', 3, false); + first | second +-------+-------- + three | 3 +(1 row) + +SELECT * FROM multiout_record_as('obj', null, null, true); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM multiout_record_as('str', 'one', 1, false); + first | second +-------+-------- + 'one' | 1 +(1 row) + +SELECT * FROM multiout_record_as('str', 'one', 2, false); + first | second +-------+-------- + 'one' | 2 +(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 plpython3u; +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 plpython3u; +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 plpython3u; +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 plpython3u; +CREATE FUNCTION multiout_composite(OUT type_record) RETURNS SETOF type_record AS $$ +return [{'first': 1, 'second': 2}, + {'first': 3, 'second': 4 }] +$$ LANGUAGE plpython3u; +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 plpython3u; +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 == 'list': + d = [ n * 2, n * 3 ] +elif typ == 'obj': + class d: pass + d.first = n * 2 + d.second = n * 3 +elif typ == 'str': + d = "(%r,%r)" % (n * 2, n * 3) +for i in range(n): + yield (i, d) +$$ LANGUAGE plpython3u; +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('dict', 'f', 7); + n | column2 +---+--------- + 0 | (14,21) + 1 | (14,21) + 2 | (14,21) + 3 | (14,21) + 4 | (14,21) + 5 | (14,21) + 6 | (14,21) +(7 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('tuple', 'f', 3); + n | column2 +---+--------- + 0 | (6,9) + 1 | (6,9) + 2 | (6,9) +(3 rows) + +SELECT * FROM multiout_table_type_setof('list', 'f', 2); + n | column2 +---+--------- + 0 | (4,6) + 1 | (4,6) +(2 rows) + +SELECT * FROM multiout_table_type_setof('list', 'f', 3); + n | column2 +---+--------- + 0 | (6,9) + 1 | (6,9) + 2 | (6,9) +(3 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('obj', 'f', 5); + n | column2 +---+--------- + 0 | (10,15) + 1 | (10,15) + 2 | (10,15) + 3 | (10,15) + 4 | (10,15) +(5 rows) + +SELECT * FROM multiout_table_type_setof('str', 'f', 6); + n | column2 +---+--------- + 0 | (12,18) + 1 | (12,18) + 2 | (12,18) + 3 | (12,18) + 4 | (12,18) + 5 | (12,18) +(6 rows) + +SELECT * FROM multiout_table_type_setof('str', 'f', 7); + n | column2 +---+--------- + 0 | (14,21) + 1 | (14,21) + 2 | (14,21) + 3 | (14,21) + 4 | (14,21) + 5 | (14,21) + 6 | (14,21) +(7 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 plpython3u; +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 +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 plpython3u; +SELECT * FROM composite_types_table(); + tab | typ +----------------------------+---------------------------- + {"(first,1)","(second,2)"} | {"(third,3)","(fourth,4)"} + {"(first,1)","(second,2)"} | {"(third,3)","(fourth,4)"} + {"(first,1)","(second,2)"} | {"(third,3)","(fourth,4)"} +(3 rows) + +-- check what happens if the output record descriptor changes +CREATE FUNCTION return_record(t text) RETURNS record AS $$ +return {'t': t, 'val': 10} +$$ LANGUAGE plpython3u; +SELECT * FROM return_record('abc') AS r(t text, val integer); + t | val +-----+----- + abc | 10 +(1 row) + +SELECT * FROM return_record('abc') AS r(t text, val bigint); + t | val +-----+----- + abc | 10 +(1 row) + +SELECT * FROM return_record('abc') AS r(t text, val integer); + t | val +-----+----- + abc | 10 +(1 row) + +SELECT * FROM return_record('abc') AS r(t varchar(30), val integer); + t | val +-----+----- + abc | 10 +(1 row) + +SELECT * FROM return_record('abc') AS r(t varchar(100), val integer); + t | val +-----+----- + abc | 10 +(1 row) + +SELECT * FROM return_record('999') AS r(val text, t integer); + val | t +-----+----- + 10 | 999 +(1 row) + +CREATE FUNCTION return_record_2(t text) RETURNS record AS $$ +return {'v1':1,'v2':2,t:3} +$$ LANGUAGE plpython3u; +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) + +-- multi-dimensional array of composite types. +CREATE FUNCTION composite_type_as_list() RETURNS type_record[] AS $$ + return [[('first', 1), ('second', 1)], [('first', 2), ('second', 2)], [('first', 3), ('second', 3)]]; +$$ LANGUAGE plpython3u; +SELECT * FROM composite_type_as_list(); + composite_type_as_list +------------------------------------------------------------------------------------ + {{"(first,1)","(second,1)"},{"(first,2)","(second,2)"},{"(first,3)","(second,3)"}} +(1 row) + +-- Starting with PostgreSQL 10, a composite type in an array cannot be +-- represented as a Python list, because it's ambiguous with multi-dimensional +-- arrays. So this throws an error now. The error should contain a useful hint +-- on the issue. +CREATE FUNCTION composite_type_as_list_broken() RETURNS type_record[] AS $$ + return [['first', 1]]; +$$ LANGUAGE plpython3u; +SELECT * FROM composite_type_as_list_broken(); +ERROR: malformed record literal: "first" +DETAIL: Missing left parenthesis. +HINT: To return a composite type in an array, return the composite type as a Python tuple, e.g., "[('foo',)]". +CONTEXT: while creating return value +PL/Python function "composite_type_as_list_broken" diff --git a/src/pl/plpython/expected/plpython_do.out b/src/pl/plpython/expected/plpython_do.out new file mode 100644 index 0000000..d131a4c --- /dev/null +++ b/src/pl/plpython/expected/plpython_do.out @@ -0,0 +1,8 @@ +DO $$ plpy.notice("This is plpython3u.") $$ LANGUAGE plpython3u; +NOTICE: This is plpython3u. +DO $$ raise Exception("error test") $$ LANGUAGE plpython3u; +ERROR: Exception: error test +CONTEXT: Traceback (most recent call last): + PL/Python anonymous code block, line 1, in <module> + raise Exception("error test") +PL/Python anonymous code block diff --git a/src/pl/plpython/expected/plpython_drop.out b/src/pl/plpython/expected/plpython_drop.out new file mode 100644 index 0000000..97bb54a --- /dev/null +++ b/src/pl/plpython/expected/plpython_drop.out @@ -0,0 +1,5 @@ +-- +-- For paranoia's sake, don't leave an untrusted language sitting around +-- +SET client_min_messages = WARNING; +DROP EXTENSION plpython3u CASCADE; diff --git a/src/pl/plpython/expected/plpython_ereport.out b/src/pl/plpython/expected/plpython_ereport.out new file mode 100644 index 0000000..74dcc41 --- /dev/null +++ b/src/pl/plpython/expected/plpython_ereport.out @@ -0,0 +1,214 @@ +CREATE FUNCTION elog_test() RETURNS void +AS $$ +plpy.debug('debug', detail='some detail') +plpy.log('log', detail='some detail') +plpy.info('info', detail='some detail') +plpy.info() +plpy.info('the question', detail=42); +plpy.info('This is message text.', + detail='This is detail text', + hint='This is hint text.', + sqlstate='XX000', + schema_name='any info about schema', + table_name='any info about table', + column_name='any info about column', + datatype_name='any info about datatype', + constraint_name='any info about constraint') +plpy.notice('notice', detail='some detail') +plpy.warning('warning', detail='some detail') +plpy.error('stop on error', detail='some detail', hint='some hint') +$$ LANGUAGE plpython3u; +SELECT elog_test(); +INFO: info +DETAIL: some detail +INFO: () +INFO: the question +DETAIL: 42 +INFO: This is message text. +DETAIL: This is detail text +HINT: This is hint text. +NOTICE: notice +DETAIL: some detail +WARNING: warning +DETAIL: some detail +ERROR: plpy.Error: stop on error +DETAIL: some detail +HINT: some hint +CONTEXT: Traceback (most recent call last): + PL/Python function "elog_test", line 18, in <module> + plpy.error('stop on error', detail='some detail', hint='some hint') +PL/Python function "elog_test" +DO $$ plpy.info('other types', detail=(10, 20)) $$ LANGUAGE plpython3u; +INFO: other types +DETAIL: (10, 20) +DO $$ +import time; +from datetime import date +plpy.info('other types', detail=date(2016, 2, 26)) +$$ LANGUAGE plpython3u; +INFO: other types +DETAIL: 2016-02-26 +DO $$ +basket = ['apple', 'orange', 'apple', 'pear', 'orange', 'banana'] +plpy.info('other types', detail=basket) +$$ LANGUAGE plpython3u; +INFO: other types +DETAIL: ['apple', 'orange', 'apple', 'pear', 'orange', 'banana'] +-- should fail +DO $$ plpy.info('wrong sqlstate', sqlstate='54444A') $$ LANGUAGE plpython3u; +ERROR: ValueError: invalid SQLSTATE code +CONTEXT: Traceback (most recent call last): + PL/Python anonymous code block, line 1, in <module> + plpy.info('wrong sqlstate', sqlstate='54444A') +PL/Python anonymous code block +DO $$ plpy.info('unsupported argument', blabla='fooboo') $$ LANGUAGE plpython3u; +ERROR: TypeError: 'blabla' is an invalid keyword argument for this function +CONTEXT: Traceback (most recent call last): + PL/Python anonymous code block, line 1, in <module> + plpy.info('unsupported argument', blabla='fooboo') +PL/Python anonymous code block +DO $$ plpy.info('first message', message='second message') $$ LANGUAGE plpython3u; +ERROR: TypeError: argument 'message' given by name and position +CONTEXT: Traceback (most recent call last): + PL/Python anonymous code block, line 1, in <module> + plpy.info('first message', message='second message') +PL/Python anonymous code block +DO $$ plpy.info('first message', 'second message', message='third message') $$ LANGUAGE plpython3u; +ERROR: TypeError: argument 'message' given by name and position +CONTEXT: Traceback (most recent call last): + PL/Python anonymous code block, line 1, in <module> + plpy.info('first message', 'second message', message='third message') +PL/Python anonymous code block +-- raise exception in python, handle exception in plgsql +CREATE OR REPLACE FUNCTION raise_exception(_message text, _detail text DEFAULT NULL, _hint text DEFAULT NULL, + _sqlstate text DEFAULT NULL, + _schema_name text DEFAULT NULL, + _table_name text DEFAULT NULL, + _column_name text DEFAULT NULL, + _datatype_name text DEFAULT NULL, + _constraint_name text DEFAULT NULL) +RETURNS void AS $$ +kwargs = { + "message": _message, "detail": _detail, "hint": _hint, + "sqlstate": _sqlstate, "schema_name": _schema_name, "table_name": _table_name, + "column_name": _column_name, "datatype_name": _datatype_name, + "constraint_name": _constraint_name +} +# ignore None values +plpy.error(**dict((k, v) for k, v in iter(kwargs.items()) if v)) +$$ LANGUAGE plpython3u; +SELECT raise_exception('hello', 'world'); +ERROR: plpy.Error: hello +DETAIL: world +CONTEXT: Traceback (most recent call last): + PL/Python function "raise_exception", line 9, in <module> + plpy.error(**dict((k, v) for k, v in iter(kwargs.items()) if v)) +PL/Python function "raise_exception" +SELECT raise_exception('message text', 'detail text', _sqlstate => 'YY333'); +ERROR: plpy.Error: message text +DETAIL: detail text +CONTEXT: Traceback (most recent call last): + PL/Python function "raise_exception", line 9, in <module> + plpy.error(**dict((k, v) for k, v in iter(kwargs.items()) if v)) +PL/Python function "raise_exception" +SELECT raise_exception(_message => 'message text', + _detail => 'detail text', + _hint => 'hint text', + _sqlstate => 'XX555', + _schema_name => 'schema text', + _table_name => 'table text', + _column_name => 'column text', + _datatype_name => 'datatype text', + _constraint_name => 'constraint text'); +ERROR: plpy.Error: message text +DETAIL: detail text +HINT: hint text +CONTEXT: Traceback (most recent call last): + PL/Python function "raise_exception", line 9, in <module> + plpy.error(**dict((k, v) for k, v in iter(kwargs.items()) if v)) +PL/Python function "raise_exception" +SELECT raise_exception(_message => 'message text', + _hint => 'hint text', + _schema_name => 'schema text', + _column_name => 'column text', + _constraint_name => 'constraint text'); +ERROR: plpy.Error: message text +HINT: hint text +CONTEXT: Traceback (most recent call last): + PL/Python function "raise_exception", line 9, in <module> + plpy.error(**dict((k, v) for k, v in iter(kwargs.items()) if v)) +PL/Python function "raise_exception" +DO $$ +DECLARE + __message text; + __detail text; + __hint text; + __sqlstate text; + __schema_name text; + __table_name text; + __column_name text; + __datatype_name text; + __constraint_name text; +BEGIN + BEGIN + PERFORM raise_exception(_message => 'message text', + _detail => 'detail text', + _hint => 'hint text', + _sqlstate => 'XX555', + _schema_name => 'schema text', + _table_name => 'table text', + _column_name => 'column text', + _datatype_name => 'datatype text', + _constraint_name => 'constraint text'); + EXCEPTION WHEN SQLSTATE 'XX555' THEN + GET STACKED DIAGNOSTICS __message = MESSAGE_TEXT, + __detail = PG_EXCEPTION_DETAIL, + __hint = PG_EXCEPTION_HINT, + __sqlstate = RETURNED_SQLSTATE, + __schema_name = SCHEMA_NAME, + __table_name = TABLE_NAME, + __column_name = COLUMN_NAME, + __datatype_name = PG_DATATYPE_NAME, + __constraint_name = CONSTRAINT_NAME; + RAISE NOTICE 'handled exception' + USING DETAIL = format('message:(%s), detail:(%s), hint: (%s), sqlstate: (%s), ' + 'schema_name:(%s), table_name:(%s), column_name:(%s), datatype_name:(%s), constraint_name:(%s)', + __message, __detail, __hint, __sqlstate, __schema_name, + __table_name, __column_name, __datatype_name, __constraint_name); + END; +END; +$$; +NOTICE: handled exception +DETAIL: message:(plpy.Error: message text), detail:(detail text), hint: (hint text), sqlstate: (XX555), schema_name:(schema text), table_name:(table text), column_name:(column text), datatype_name:(datatype text), constraint_name:(constraint text) +DO $$ +try: + plpy.execute("select raise_exception(_message => 'my message', _sqlstate => 'XX987', _hint => 'some hint', _table_name => 'users_tab', _datatype_name => 'user_type')") +except Exception as e: + plpy.info(e.spidata) + raise e +$$ LANGUAGE plpython3u; +INFO: (119577128, None, 'some hint', None, 0, None, 'users_tab', None, 'user_type', None) +ERROR: plpy.SPIError: plpy.Error: my message +HINT: some hint +CONTEXT: Traceback (most recent call last): + PL/Python anonymous code block, line 6, in <module> + raise e + PL/Python anonymous code block, line 3, in __plpython_inline_block + plpy.execute("select raise_exception(_message => 'my message', _sqlstate => 'XX987', _hint => 'some hint', _table_name => 'users_tab', _datatype_name => 'user_type')") +PL/Python anonymous code block +DO $$ +try: + plpy.error(message = 'my message', sqlstate = 'XX987', hint = 'some hint', table_name = 'users_tab', datatype_name = 'user_type') +except Exception as e: + plpy.info('sqlstate: %s, hint: %s, table_name: %s, datatype_name: %s' % (e.sqlstate, e.hint, e.table_name, e.datatype_name)) + raise e +$$ LANGUAGE plpython3u; +INFO: sqlstate: XX987, hint: some hint, table_name: users_tab, datatype_name: user_type +ERROR: plpy.Error: my message +HINT: some hint +CONTEXT: Traceback (most recent call last): + PL/Python anonymous code block, line 6, in <module> + raise e + PL/Python anonymous code block, line 3, in __plpython_inline_block + plpy.error(message = 'my message', sqlstate = 'XX987', hint = 'some hint', table_name = 'users_tab', datatype_name = 'user_type') +PL/Python anonymous code block diff --git a/src/pl/plpython/expected/plpython_error.out b/src/pl/plpython/expected/plpython_error.out new file mode 100644 index 0000000..68722b0 --- /dev/null +++ b/src/pl/plpython/expected/plpython_error.out @@ -0,0 +1,460 @@ +-- test error handling, i forgot to restore Warn_restart in +-- the trigger handler once. the errors and subsequent core dump were +-- interesting. +/* Flat out Python syntax error + */ +CREATE FUNCTION python_syntax_error() RETURNS text + AS +'.syntaxerror' + LANGUAGE plpython3u; +ERROR: could not compile PL/Python function "python_syntax_error" +DETAIL: SyntaxError: invalid syntax (<string>, line 2) +/* With check_function_bodies = false the function should get defined + * and the error reported when called + */ +SET check_function_bodies = false; +CREATE FUNCTION python_syntax_error() RETURNS text + AS +'.syntaxerror' + LANGUAGE plpython3u; +SELECT python_syntax_error(); +ERROR: could not compile PL/Python function "python_syntax_error" +DETAIL: SyntaxError: invalid syntax (<string>, line 2) +/* Run the function twice to check if the hashtable entry gets cleaned up */ +SELECT python_syntax_error(); +ERROR: could not compile PL/Python function "python_syntax_error" +DETAIL: SyntaxError: invalid syntax (<string>, line 2) +RESET check_function_bodies; +/* Flat out syntax error + */ +CREATE FUNCTION sql_syntax_error() RETURNS text + AS +'plpy.execute("syntax error")' + LANGUAGE plpython3u; +SELECT sql_syntax_error(); +ERROR: spiexceptions.SyntaxError: syntax error at or near "syntax" +LINE 1: syntax error + ^ +QUERY: syntax error +CONTEXT: Traceback (most recent call last): + PL/Python function "sql_syntax_error", line 1, in <module> + plpy.execute("syntax error") +PL/Python function "sql_syntax_error" +/* check the handling of uncaught python exceptions + */ +CREATE FUNCTION exception_index_invalid(text) RETURNS text + AS +'return args[1]' + LANGUAGE plpython3u; +SELECT exception_index_invalid('test'); +ERROR: IndexError: list index out of range +CONTEXT: Traceback (most recent call last): + PL/Python function "exception_index_invalid", line 1, in <module> + return args[1] +PL/Python function "exception_index_invalid" +/* check handling of nested exceptions + */ +CREATE FUNCTION exception_index_invalid_nested() RETURNS text + AS +'rv = plpy.execute("SELECT test5(''foo'')") +return rv[0]' + LANGUAGE plpython3u; +SELECT exception_index_invalid_nested(); +ERROR: spiexceptions.UndefinedFunction: function test5(unknown) does not exist +LINE 1: SELECT test5('foo') + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +QUERY: SELECT test5('foo') +CONTEXT: Traceback (most recent call last): + PL/Python function "exception_index_invalid_nested", line 1, in <module> + rv = plpy.execute("SELECT test5('foo')") +PL/Python function "exception_index_invalid_nested" +/* a typo + */ +CREATE FUNCTION invalid_type_uncaught(a text) RETURNS text + AS +'if "plan" not in SD: + q = "SELECT fname FROM users WHERE lname = $1" + SD["plan"] = plpy.prepare(q, [ "test" ]) +rv = plpy.execute(SD["plan"], [ a ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpython3u; +SELECT invalid_type_uncaught('rick'); +ERROR: spiexceptions.UndefinedObject: type "test" does not exist +CONTEXT: Traceback (most recent call last): + PL/Python function "invalid_type_uncaught", line 3, in <module> + SD["plan"] = plpy.prepare(q, [ "test" ]) +PL/Python function "invalid_type_uncaught" +/* for what it's worth catch the exception generated by + * the typo, and return None + */ +CREATE FUNCTION invalid_type_caught(a text) RETURNS text + AS +'if "plan" not in SD: + q = "SELECT fname FROM users WHERE lname = $1" + try: + SD["plan"] = plpy.prepare(q, [ "test" ]) + except plpy.SPIError as ex: + plpy.notice(str(ex)) + return None +rv = plpy.execute(SD["plan"], [ a ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpython3u; +SELECT invalid_type_caught('rick'); +NOTICE: type "test" does not exist + invalid_type_caught +--------------------- + +(1 row) + +/* for what it's worth catch the exception generated by + * the typo, and reraise it as a plain error + */ +CREATE FUNCTION invalid_type_reraised(a text) RETURNS text + AS +'if "plan" not in SD: + q = "SELECT fname FROM users WHERE lname = $1" + try: + SD["plan"] = plpy.prepare(q, [ "test" ]) + except plpy.SPIError as ex: + plpy.error(str(ex)) +rv = plpy.execute(SD["plan"], [ a ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpython3u; +SELECT invalid_type_reraised('rick'); +ERROR: plpy.Error: type "test" does not exist +CONTEXT: Traceback (most recent call last): + PL/Python function "invalid_type_reraised", line 6, in <module> + plpy.error(str(ex)) +PL/Python function "invalid_type_reraised" +/* no typo no messing about + */ +CREATE FUNCTION valid_type(a text) RETURNS text + AS +'if "plan" not in SD: + SD["plan"] = plpy.prepare("SELECT fname FROM users WHERE lname = $1", [ "text" ]) +rv = plpy.execute(SD["plan"], [ a ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpython3u; +SELECT valid_type('rick'); + valid_type +------------ + +(1 row) + +/* error in nested functions to get a traceback +*/ +CREATE FUNCTION nested_error() RETURNS text + AS +'def fun1(): + plpy.error("boom") + +def fun2(): + fun1() + +def fun3(): + fun2() + +fun3() +return "not reached" +' + LANGUAGE plpython3u; +SELECT nested_error(); +ERROR: plpy.Error: boom +CONTEXT: Traceback (most recent call last): + PL/Python function "nested_error", line 10, in <module> + fun3() + PL/Python function "nested_error", line 8, in fun3 + fun2() + PL/Python function "nested_error", line 5, in fun2 + fun1() + PL/Python function "nested_error", line 2, in fun1 + plpy.error("boom") +PL/Python function "nested_error" +/* raising plpy.Error is just like calling plpy.error +*/ +CREATE FUNCTION nested_error_raise() RETURNS text + AS +'def fun1(): + raise plpy.Error("boom") + +def fun2(): + fun1() + +def fun3(): + fun2() + +fun3() +return "not reached" +' + LANGUAGE plpython3u; +SELECT nested_error_raise(); +ERROR: plpy.Error: boom +CONTEXT: Traceback (most recent call last): + PL/Python function "nested_error_raise", line 10, in <module> + fun3() + PL/Python function "nested_error_raise", line 8, in fun3 + fun2() + PL/Python function "nested_error_raise", line 5, in fun2 + fun1() + PL/Python function "nested_error_raise", line 2, in fun1 + raise plpy.Error("boom") +PL/Python function "nested_error_raise" +/* using plpy.warning should not produce a traceback +*/ +CREATE FUNCTION nested_warning() RETURNS text + AS +'def fun1(): + plpy.warning("boom") + +def fun2(): + fun1() + +def fun3(): + fun2() + +fun3() +return "you''ve been warned" +' + LANGUAGE plpython3u; +SELECT nested_warning(); +WARNING: boom + nested_warning +-------------------- + you've been warned +(1 row) + +/* AttributeError at toplevel used to give segfaults with the traceback +*/ +CREATE FUNCTION toplevel_attribute_error() RETURNS void AS +$$ +plpy.nonexistent +$$ LANGUAGE plpython3u; +SELECT toplevel_attribute_error(); +ERROR: AttributeError: 'module' object has no attribute 'nonexistent' +CONTEXT: Traceback (most recent call last): + PL/Python function "toplevel_attribute_error", line 2, in <module> + plpy.nonexistent +PL/Python function "toplevel_attribute_error" +/* Calling PL/Python functions from SQL and vice versa should not lose context. + */ +CREATE OR REPLACE FUNCTION python_traceback() RETURNS void AS $$ +def first(): + second() + +def second(): + third() + +def third(): + plpy.execute("select sql_error()") + +first() +$$ LANGUAGE plpython3u; +CREATE OR REPLACE FUNCTION sql_error() RETURNS void AS $$ +begin + select 1/0; +end +$$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION python_from_sql_error() RETURNS void AS $$ +begin + select python_traceback(); +end +$$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION sql_from_python_error() RETURNS void AS $$ +plpy.execute("select sql_error()") +$$ LANGUAGE plpython3u; +SELECT python_traceback(); +ERROR: spiexceptions.DivisionByZero: division by zero +CONTEXT: Traceback (most recent call last): + PL/Python function "python_traceback", line 11, in <module> + first() + PL/Python function "python_traceback", line 3, in first + second() + PL/Python function "python_traceback", line 6, in second + third() + PL/Python function "python_traceback", line 9, in third + plpy.execute("select sql_error()") +PL/Python function "python_traceback" +SELECT sql_error(); +ERROR: division by zero +CONTEXT: SQL statement "select 1/0" +PL/pgSQL function sql_error() line 3 at SQL statement +SELECT python_from_sql_error(); +ERROR: spiexceptions.DivisionByZero: division by zero +CONTEXT: Traceback (most recent call last): + PL/Python function "python_traceback", line 11, in <module> + first() + PL/Python function "python_traceback", line 3, in first + second() + PL/Python function "python_traceback", line 6, in second + third() + PL/Python function "python_traceback", line 9, in third + plpy.execute("select sql_error()") +PL/Python function "python_traceback" +SQL statement "select python_traceback()" +PL/pgSQL function python_from_sql_error() line 3 at SQL statement +SELECT sql_from_python_error(); +ERROR: spiexceptions.DivisionByZero: division by zero +CONTEXT: Traceback (most recent call last): + PL/Python function "sql_from_python_error", line 2, in <module> + plpy.execute("select sql_error()") +PL/Python function "sql_from_python_error" +/* check catching specific types of exceptions + */ +CREATE TABLE specific ( + i integer PRIMARY KEY +); +CREATE FUNCTION specific_exception(i integer) RETURNS void AS +$$ +from plpy import spiexceptions +try: + plpy.execute("insert into specific values (%s)" % (i or "NULL")); +except spiexceptions.NotNullViolation as e: + plpy.notice("Violated the NOT NULL constraint, sqlstate %s" % e.sqlstate) +except spiexceptions.UniqueViolation as e: + plpy.notice("Violated the UNIQUE constraint, sqlstate %s" % e.sqlstate) +$$ LANGUAGE plpython3u; +SELECT specific_exception(2); + specific_exception +-------------------- + +(1 row) + +SELECT specific_exception(NULL); +NOTICE: Violated the NOT NULL constraint, sqlstate 23502 + specific_exception +-------------------- + +(1 row) + +SELECT specific_exception(2); +NOTICE: Violated the UNIQUE constraint, sqlstate 23505 + specific_exception +-------------------- + +(1 row) + +/* SPI errors in PL/Python functions should preserve the SQLSTATE value + */ +CREATE FUNCTION python_unique_violation() RETURNS void AS $$ +plpy.execute("insert into specific values (1)") +plpy.execute("insert into specific values (1)") +$$ LANGUAGE plpython3u; +CREATE FUNCTION catch_python_unique_violation() RETURNS text AS $$ +begin + begin + perform python_unique_violation(); + exception when unique_violation then + return 'ok'; + end; + return 'not reached'; +end; +$$ language plpgsql; +SELECT catch_python_unique_violation(); + catch_python_unique_violation +------------------------------- + ok +(1 row) + +/* manually starting subtransactions - a bad idea + */ +CREATE FUNCTION manual_subxact() RETURNS void AS $$ +plpy.execute("savepoint save") +plpy.execute("create table foo(x integer)") +plpy.execute("rollback to save") +$$ LANGUAGE plpython3u; +SELECT manual_subxact(); +ERROR: plpy.SPIError: SPI_execute failed: SPI_ERROR_TRANSACTION +CONTEXT: Traceback (most recent call last): + PL/Python function "manual_subxact", line 2, in <module> + plpy.execute("savepoint save") +PL/Python function "manual_subxact" +/* same for prepared plans + */ +CREATE FUNCTION manual_subxact_prepared() RETURNS void AS $$ +save = plpy.prepare("savepoint save") +rollback = plpy.prepare("rollback to save") +plpy.execute(save) +plpy.execute("create table foo(x integer)") +plpy.execute(rollback) +$$ LANGUAGE plpython3u; +SELECT manual_subxact_prepared(); +ERROR: plpy.SPIError: SPI_execute_plan failed: SPI_ERROR_TRANSACTION +CONTEXT: Traceback (most recent call last): + PL/Python function "manual_subxact_prepared", line 4, in <module> + plpy.execute(save) +PL/Python function "manual_subxact_prepared" +/* raising plpy.spiexception.* from python code should preserve sqlstate + */ +CREATE FUNCTION plpy_raise_spiexception() RETURNS void AS $$ +raise plpy.spiexceptions.DivisionByZero() +$$ LANGUAGE plpython3u; +DO $$ +BEGIN + SELECT plpy_raise_spiexception(); +EXCEPTION WHEN division_by_zero THEN + -- NOOP +END +$$ LANGUAGE plpgsql; +/* setting a custom sqlstate should be handled + */ +CREATE FUNCTION plpy_raise_spiexception_override() RETURNS void AS $$ +exc = plpy.spiexceptions.DivisionByZero() +exc.sqlstate = 'SILLY' +raise exc +$$ LANGUAGE plpython3u; +DO $$ +BEGIN + SELECT plpy_raise_spiexception_override(); +EXCEPTION WHEN SQLSTATE 'SILLY' THEN + -- NOOP +END +$$ LANGUAGE plpgsql; +/* test the context stack trace for nested execution levels + */ +CREATE FUNCTION notice_innerfunc() RETURNS int AS $$ +plpy.execute("DO LANGUAGE plpython3u $x$ plpy.notice('inside DO') $x$") +return 1 +$$ LANGUAGE plpython3u; +CREATE FUNCTION notice_outerfunc() RETURNS int AS $$ +plpy.execute("SELECT notice_innerfunc()") +return 1 +$$ LANGUAGE plpython3u; +\set SHOW_CONTEXT always +SELECT notice_outerfunc(); +NOTICE: inside DO +CONTEXT: PL/Python anonymous code block +SQL statement "DO LANGUAGE plpython3u $x$ plpy.notice('inside DO') $x$" +PL/Python function "notice_innerfunc" +SQL statement "SELECT notice_innerfunc()" +PL/Python function "notice_outerfunc" + notice_outerfunc +------------------ + 1 +(1 row) + +/* test error logged with an underlying exception that includes a detail + * string (bug #18070). + */ +CREATE FUNCTION python_error_detail() RETURNS SETOF text AS $$ + plan = plpy.prepare("SELECT to_date('xy', 'DD') d") + for row in plpy.cursor(plan): + yield row['d'] +$$ LANGUAGE plpython3u; +SELECT python_error_detail(); +ERROR: error fetching next item from iterator +DETAIL: spiexceptions.InvalidDatetimeFormat: invalid value "xy" for "DD" +CONTEXT: Traceback (most recent call last): +PL/Python function "python_error_detail" diff --git a/src/pl/plpython/expected/plpython_error_5.out b/src/pl/plpython/expected/plpython_error_5.out new file mode 100644 index 0000000..fd9cd73 --- /dev/null +++ b/src/pl/plpython/expected/plpython_error_5.out @@ -0,0 +1,460 @@ +-- test error handling, i forgot to restore Warn_restart in +-- the trigger handler once. the errors and subsequent core dump were +-- interesting. +/* Flat out Python syntax error + */ +CREATE FUNCTION python_syntax_error() RETURNS text + AS +'.syntaxerror' + LANGUAGE plpython3u; +ERROR: could not compile PL/Python function "python_syntax_error" +DETAIL: SyntaxError: invalid syntax (<string>, line 2) +/* With check_function_bodies = false the function should get defined + * and the error reported when called + */ +SET check_function_bodies = false; +CREATE FUNCTION python_syntax_error() RETURNS text + AS +'.syntaxerror' + LANGUAGE plpython3u; +SELECT python_syntax_error(); +ERROR: could not compile PL/Python function "python_syntax_error" +DETAIL: SyntaxError: invalid syntax (<string>, line 2) +/* Run the function twice to check if the hashtable entry gets cleaned up */ +SELECT python_syntax_error(); +ERROR: could not compile PL/Python function "python_syntax_error" +DETAIL: SyntaxError: invalid syntax (<string>, line 2) +RESET check_function_bodies; +/* Flat out syntax error + */ +CREATE FUNCTION sql_syntax_error() RETURNS text + AS +'plpy.execute("syntax error")' + LANGUAGE plpython3u; +SELECT sql_syntax_error(); +ERROR: spiexceptions.SyntaxError: syntax error at or near "syntax" +LINE 1: syntax error + ^ +QUERY: syntax error +CONTEXT: Traceback (most recent call last): + PL/Python function "sql_syntax_error", line 1, in <module> + plpy.execute("syntax error") +PL/Python function "sql_syntax_error" +/* check the handling of uncaught python exceptions + */ +CREATE FUNCTION exception_index_invalid(text) RETURNS text + AS +'return args[1]' + LANGUAGE plpython3u; +SELECT exception_index_invalid('test'); +ERROR: IndexError: list index out of range +CONTEXT: Traceback (most recent call last): + PL/Python function "exception_index_invalid", line 1, in <module> + return args[1] +PL/Python function "exception_index_invalid" +/* check handling of nested exceptions + */ +CREATE FUNCTION exception_index_invalid_nested() RETURNS text + AS +'rv = plpy.execute("SELECT test5(''foo'')") +return rv[0]' + LANGUAGE plpython3u; +SELECT exception_index_invalid_nested(); +ERROR: spiexceptions.UndefinedFunction: function test5(unknown) does not exist +LINE 1: SELECT test5('foo') + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +QUERY: SELECT test5('foo') +CONTEXT: Traceback (most recent call last): + PL/Python function "exception_index_invalid_nested", line 1, in <module> + rv = plpy.execute("SELECT test5('foo')") +PL/Python function "exception_index_invalid_nested" +/* a typo + */ +CREATE FUNCTION invalid_type_uncaught(a text) RETURNS text + AS +'if "plan" not in SD: + q = "SELECT fname FROM users WHERE lname = $1" + SD["plan"] = plpy.prepare(q, [ "test" ]) +rv = plpy.execute(SD["plan"], [ a ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpython3u; +SELECT invalid_type_uncaught('rick'); +ERROR: spiexceptions.UndefinedObject: type "test" does not exist +CONTEXT: Traceback (most recent call last): + PL/Python function "invalid_type_uncaught", line 3, in <module> + SD["plan"] = plpy.prepare(q, [ "test" ]) +PL/Python function "invalid_type_uncaught" +/* for what it's worth catch the exception generated by + * the typo, and return None + */ +CREATE FUNCTION invalid_type_caught(a text) RETURNS text + AS +'if "plan" not in SD: + q = "SELECT fname FROM users WHERE lname = $1" + try: + SD["plan"] = plpy.prepare(q, [ "test" ]) + except plpy.SPIError as ex: + plpy.notice(str(ex)) + return None +rv = plpy.execute(SD["plan"], [ a ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpython3u; +SELECT invalid_type_caught('rick'); +NOTICE: type "test" does not exist + invalid_type_caught +--------------------- + +(1 row) + +/* for what it's worth catch the exception generated by + * the typo, and reraise it as a plain error + */ +CREATE FUNCTION invalid_type_reraised(a text) RETURNS text + AS +'if "plan" not in SD: + q = "SELECT fname FROM users WHERE lname = $1" + try: + SD["plan"] = plpy.prepare(q, [ "test" ]) + except plpy.SPIError as ex: + plpy.error(str(ex)) +rv = plpy.execute(SD["plan"], [ a ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpython3u; +SELECT invalid_type_reraised('rick'); +ERROR: plpy.Error: type "test" does not exist +CONTEXT: Traceback (most recent call last): + PL/Python function "invalid_type_reraised", line 6, in <module> + plpy.error(str(ex)) +PL/Python function "invalid_type_reraised" +/* no typo no messing about + */ +CREATE FUNCTION valid_type(a text) RETURNS text + AS +'if "plan" not in SD: + SD["plan"] = plpy.prepare("SELECT fname FROM users WHERE lname = $1", [ "text" ]) +rv = plpy.execute(SD["plan"], [ a ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpython3u; +SELECT valid_type('rick'); + valid_type +------------ + +(1 row) + +/* error in nested functions to get a traceback +*/ +CREATE FUNCTION nested_error() RETURNS text + AS +'def fun1(): + plpy.error("boom") + +def fun2(): + fun1() + +def fun3(): + fun2() + +fun3() +return "not reached" +' + LANGUAGE plpython3u; +SELECT nested_error(); +ERROR: plpy.Error: boom +CONTEXT: Traceback (most recent call last): + PL/Python function "nested_error", line 10, in <module> + fun3() + PL/Python function "nested_error", line 8, in fun3 + fun2() + PL/Python function "nested_error", line 5, in fun2 + fun1() + PL/Python function "nested_error", line 2, in fun1 + plpy.error("boom") +PL/Python function "nested_error" +/* raising plpy.Error is just like calling plpy.error +*/ +CREATE FUNCTION nested_error_raise() RETURNS text + AS +'def fun1(): + raise plpy.Error("boom") + +def fun2(): + fun1() + +def fun3(): + fun2() + +fun3() +return "not reached" +' + LANGUAGE plpython3u; +SELECT nested_error_raise(); +ERROR: plpy.Error: boom +CONTEXT: Traceback (most recent call last): + PL/Python function "nested_error_raise", line 10, in <module> + fun3() + PL/Python function "nested_error_raise", line 8, in fun3 + fun2() + PL/Python function "nested_error_raise", line 5, in fun2 + fun1() + PL/Python function "nested_error_raise", line 2, in fun1 + raise plpy.Error("boom") +PL/Python function "nested_error_raise" +/* using plpy.warning should not produce a traceback +*/ +CREATE FUNCTION nested_warning() RETURNS text + AS +'def fun1(): + plpy.warning("boom") + +def fun2(): + fun1() + +def fun3(): + fun2() + +fun3() +return "you''ve been warned" +' + LANGUAGE plpython3u; +SELECT nested_warning(); +WARNING: boom + nested_warning +-------------------- + you've been warned +(1 row) + +/* AttributeError at toplevel used to give segfaults with the traceback +*/ +CREATE FUNCTION toplevel_attribute_error() RETURNS void AS +$$ +plpy.nonexistent +$$ LANGUAGE plpython3u; +SELECT toplevel_attribute_error(); +ERROR: AttributeError: module 'plpy' has no attribute 'nonexistent' +CONTEXT: Traceback (most recent call last): + PL/Python function "toplevel_attribute_error", line 2, in <module> + plpy.nonexistent +PL/Python function "toplevel_attribute_error" +/* Calling PL/Python functions from SQL and vice versa should not lose context. + */ +CREATE OR REPLACE FUNCTION python_traceback() RETURNS void AS $$ +def first(): + second() + +def second(): + third() + +def third(): + plpy.execute("select sql_error()") + +first() +$$ LANGUAGE plpython3u; +CREATE OR REPLACE FUNCTION sql_error() RETURNS void AS $$ +begin + select 1/0; +end +$$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION python_from_sql_error() RETURNS void AS $$ +begin + select python_traceback(); +end +$$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION sql_from_python_error() RETURNS void AS $$ +plpy.execute("select sql_error()") +$$ LANGUAGE plpython3u; +SELECT python_traceback(); +ERROR: spiexceptions.DivisionByZero: division by zero +CONTEXT: Traceback (most recent call last): + PL/Python function "python_traceback", line 11, in <module> + first() + PL/Python function "python_traceback", line 3, in first + second() + PL/Python function "python_traceback", line 6, in second + third() + PL/Python function "python_traceback", line 9, in third + plpy.execute("select sql_error()") +PL/Python function "python_traceback" +SELECT sql_error(); +ERROR: division by zero +CONTEXT: SQL statement "select 1/0" +PL/pgSQL function sql_error() line 3 at SQL statement +SELECT python_from_sql_error(); +ERROR: spiexceptions.DivisionByZero: division by zero +CONTEXT: Traceback (most recent call last): + PL/Python function "python_traceback", line 11, in <module> + first() + PL/Python function "python_traceback", line 3, in first + second() + PL/Python function "python_traceback", line 6, in second + third() + PL/Python function "python_traceback", line 9, in third + plpy.execute("select sql_error()") +PL/Python function "python_traceback" +SQL statement "select python_traceback()" +PL/pgSQL function python_from_sql_error() line 3 at SQL statement +SELECT sql_from_python_error(); +ERROR: spiexceptions.DivisionByZero: division by zero +CONTEXT: Traceback (most recent call last): + PL/Python function "sql_from_python_error", line 2, in <module> + plpy.execute("select sql_error()") +PL/Python function "sql_from_python_error" +/* check catching specific types of exceptions + */ +CREATE TABLE specific ( + i integer PRIMARY KEY +); +CREATE FUNCTION specific_exception(i integer) RETURNS void AS +$$ +from plpy import spiexceptions +try: + plpy.execute("insert into specific values (%s)" % (i or "NULL")); +except spiexceptions.NotNullViolation as e: + plpy.notice("Violated the NOT NULL constraint, sqlstate %s" % e.sqlstate) +except spiexceptions.UniqueViolation as e: + plpy.notice("Violated the UNIQUE constraint, sqlstate %s" % e.sqlstate) +$$ LANGUAGE plpython3u; +SELECT specific_exception(2); + specific_exception +-------------------- + +(1 row) + +SELECT specific_exception(NULL); +NOTICE: Violated the NOT NULL constraint, sqlstate 23502 + specific_exception +-------------------- + +(1 row) + +SELECT specific_exception(2); +NOTICE: Violated the UNIQUE constraint, sqlstate 23505 + specific_exception +-------------------- + +(1 row) + +/* SPI errors in PL/Python functions should preserve the SQLSTATE value + */ +CREATE FUNCTION python_unique_violation() RETURNS void AS $$ +plpy.execute("insert into specific values (1)") +plpy.execute("insert into specific values (1)") +$$ LANGUAGE plpython3u; +CREATE FUNCTION catch_python_unique_violation() RETURNS text AS $$ +begin + begin + perform python_unique_violation(); + exception when unique_violation then + return 'ok'; + end; + return 'not reached'; +end; +$$ language plpgsql; +SELECT catch_python_unique_violation(); + catch_python_unique_violation +------------------------------- + ok +(1 row) + +/* manually starting subtransactions - a bad idea + */ +CREATE FUNCTION manual_subxact() RETURNS void AS $$ +plpy.execute("savepoint save") +plpy.execute("create table foo(x integer)") +plpy.execute("rollback to save") +$$ LANGUAGE plpython3u; +SELECT manual_subxact(); +ERROR: plpy.SPIError: SPI_execute failed: SPI_ERROR_TRANSACTION +CONTEXT: Traceback (most recent call last): + PL/Python function "manual_subxact", line 2, in <module> + plpy.execute("savepoint save") +PL/Python function "manual_subxact" +/* same for prepared plans + */ +CREATE FUNCTION manual_subxact_prepared() RETURNS void AS $$ +save = plpy.prepare("savepoint save") +rollback = plpy.prepare("rollback to save") +plpy.execute(save) +plpy.execute("create table foo(x integer)") +plpy.execute(rollback) +$$ LANGUAGE plpython3u; +SELECT manual_subxact_prepared(); +ERROR: plpy.SPIError: SPI_execute_plan failed: SPI_ERROR_TRANSACTION +CONTEXT: Traceback (most recent call last): + PL/Python function "manual_subxact_prepared", line 4, in <module> + plpy.execute(save) +PL/Python function "manual_subxact_prepared" +/* raising plpy.spiexception.* from python code should preserve sqlstate + */ +CREATE FUNCTION plpy_raise_spiexception() RETURNS void AS $$ +raise plpy.spiexceptions.DivisionByZero() +$$ LANGUAGE plpython3u; +DO $$ +BEGIN + SELECT plpy_raise_spiexception(); +EXCEPTION WHEN division_by_zero THEN + -- NOOP +END +$$ LANGUAGE plpgsql; +/* setting a custom sqlstate should be handled + */ +CREATE FUNCTION plpy_raise_spiexception_override() RETURNS void AS $$ +exc = plpy.spiexceptions.DivisionByZero() +exc.sqlstate = 'SILLY' +raise exc +$$ LANGUAGE plpython3u; +DO $$ +BEGIN + SELECT plpy_raise_spiexception_override(); +EXCEPTION WHEN SQLSTATE 'SILLY' THEN + -- NOOP +END +$$ LANGUAGE plpgsql; +/* test the context stack trace for nested execution levels + */ +CREATE FUNCTION notice_innerfunc() RETURNS int AS $$ +plpy.execute("DO LANGUAGE plpython3u $x$ plpy.notice('inside DO') $x$") +return 1 +$$ LANGUAGE plpython3u; +CREATE FUNCTION notice_outerfunc() RETURNS int AS $$ +plpy.execute("SELECT notice_innerfunc()") +return 1 +$$ LANGUAGE plpython3u; +\set SHOW_CONTEXT always +SELECT notice_outerfunc(); +NOTICE: inside DO +CONTEXT: PL/Python anonymous code block +SQL statement "DO LANGUAGE plpython3u $x$ plpy.notice('inside DO') $x$" +PL/Python function "notice_innerfunc" +SQL statement "SELECT notice_innerfunc()" +PL/Python function "notice_outerfunc" + notice_outerfunc +------------------ + 1 +(1 row) + +/* test error logged with an underlying exception that includes a detail + * string (bug #18070). + */ +CREATE FUNCTION python_error_detail() RETURNS SETOF text AS $$ + plan = plpy.prepare("SELECT to_date('xy', 'DD') d") + for row in plpy.cursor(plan): + yield row['d'] +$$ LANGUAGE plpython3u; +SELECT python_error_detail(); +ERROR: error fetching next item from iterator +DETAIL: spiexceptions.InvalidDatetimeFormat: invalid value "xy" for "DD" +CONTEXT: Traceback (most recent call last): +PL/Python function "python_error_detail" diff --git a/src/pl/plpython/expected/plpython_global.out b/src/pl/plpython/expected/plpython_global.out new file mode 100644 index 0000000..a4cfb14 --- /dev/null +++ b/src/pl/plpython/expected/plpython_global.out @@ -0,0 +1,52 @@ +-- +-- check static and global data (SD and GD) +-- +CREATE FUNCTION global_test_one() returns text + AS +'if "global_test" not in SD: + SD["global_test"] = "set by global_test_one" +if "global_test" not in GD: + GD["global_test"] = "set by global_test_one" +return "SD: " + SD["global_test"] + ", GD: " + GD["global_test"]' + LANGUAGE plpython3u; +CREATE FUNCTION global_test_two() returns text + AS +'if "global_test" not in SD: + SD["global_test"] = "set by global_test_two" +if "global_test" not in GD: + GD["global_test"] = "set by global_test_two" +return "SD: " + SD["global_test"] + ", GD: " + GD["global_test"]' + LANGUAGE plpython3u; +CREATE FUNCTION static_test() returns int4 + AS +'if "call" in SD: + SD["call"] = SD["call"] + 1 +else: + SD["call"] = 1 +return SD["call"] +' + LANGUAGE plpython3u; +SELECT static_test(); + static_test +------------- + 1 +(1 row) + +SELECT static_test(); + static_test +------------- + 2 +(1 row) + +SELECT global_test_one(); + global_test_one +-------------------------------------------------------- + SD: set by global_test_one, GD: set by global_test_one +(1 row) + +SELECT global_test_two(); + global_test_two +-------------------------------------------------------- + SD: set by global_test_two, GD: set by global_test_one +(1 row) + diff --git a/src/pl/plpython/expected/plpython_import.out b/src/pl/plpython/expected/plpython_import.out new file mode 100644 index 0000000..854e989 --- /dev/null +++ b/src/pl/plpython/expected/plpython_import.out @@ -0,0 +1,79 @@ +-- import python modules +CREATE FUNCTION import_fail() returns text + AS +'try: + import foosocket +except ImportError: + return "failed as expected" +return "succeeded, that wasn''t supposed to happen"' + LANGUAGE plpython3u; +CREATE FUNCTION import_succeed() returns text + AS +'try: + import array + import bisect + import calendar + import cmath + import errno + import math + import operator + import random + import re + import string + import time +except Exception as ex: + plpy.notice("import failed -- %s" % str(ex)) + return "failed, that wasn''t supposed to happen" +return "succeeded, as expected"' + LANGUAGE plpython3u; +CREATE FUNCTION import_test_one(p text) RETURNS text + AS +'try: + import hashlib + digest = hashlib.sha1(p.encode("ascii")) +except ImportError: + import sha + digest = sha.new(p) +return digest.hexdigest()' + LANGUAGE plpython3u; +CREATE FUNCTION import_test_two(u users) RETURNS text + AS +'plain = u["fname"] + u["lname"] +try: + import hashlib + digest = hashlib.sha1(plain.encode("ascii")) +except ImportError: + import sha + digest = sha.new(plain); +return "sha hash of " + plain + " is " + digest.hexdigest()' + LANGUAGE plpython3u; +-- import python modules +-- +SELECT import_fail(); + import_fail +-------------------- + failed as expected +(1 row) + +SELECT import_succeed(); + import_succeed +------------------------ + succeeded, as expected +(1 row) + +-- test import and simple argument handling +-- +SELECT import_test_one('sha hash of this string'); + import_test_one +------------------------------------------ + a04e23cb9b1a09cd1051a04a7c571aae0f90346c +(1 row) + +-- test import and tuple argument handling +-- +select import_test_two(users) from users where fname = 'willem'; + import_test_two +------------------------------------------------------------------- + sha hash of willemdoe is 3cde6b574953b0ca937b4d76ebc40d534d910759 +(1 row) + diff --git a/src/pl/plpython/expected/plpython_newline.out b/src/pl/plpython/expected/plpython_newline.out new file mode 100644 index 0000000..2bc1492 --- /dev/null +++ b/src/pl/plpython/expected/plpython_newline.out @@ -0,0 +1,30 @@ +-- +-- Universal Newline Support +-- +CREATE OR REPLACE FUNCTION newline_lf() RETURNS integer AS +E'x = 100\ny = 23\nreturn x + y\n' +LANGUAGE plpython3u; +CREATE OR REPLACE FUNCTION newline_cr() RETURNS integer AS +E'x = 100\ry = 23\rreturn x + y\r' +LANGUAGE plpython3u; +CREATE OR REPLACE FUNCTION newline_crlf() RETURNS integer AS +E'x = 100\r\ny = 23\r\nreturn x + y\r\n' +LANGUAGE plpython3u; +SELECT newline_lf(); + newline_lf +------------ + 123 +(1 row) + +SELECT newline_cr(); + newline_cr +------------ + 123 +(1 row) + +SELECT newline_crlf(); + newline_crlf +-------------- + 123 +(1 row) + diff --git a/src/pl/plpython/expected/plpython_params.out b/src/pl/plpython/expected/plpython_params.out new file mode 100644 index 0000000..d1a36f3 --- /dev/null +++ b/src/pl/plpython/expected/plpython_params.out @@ -0,0 +1,64 @@ +-- +-- Test named and nameless parameters +-- +CREATE FUNCTION test_param_names0(integer, integer) RETURNS int AS $$ +return args[0] + args[1] +$$ LANGUAGE plpython3u; +CREATE FUNCTION test_param_names1(a0 integer, a1 text) RETURNS boolean AS $$ +assert a0 == args[0] +assert a1 == args[1] +return True +$$ LANGUAGE plpython3u; +CREATE FUNCTION test_param_names2(u users) RETURNS text AS $$ +assert u == args[0] +if isinstance(u, dict): + # stringify dict the hard way because otherwise the order is implementation-dependent + u_keys = list(u.keys()) + u_keys.sort() + s = '{' + ', '.join([repr(k) + ': ' + repr(u[k]) for k in u_keys]) + '}' +else: + s = str(u) +return s +$$ LANGUAGE plpython3u; +-- use deliberately wrong parameter names +CREATE FUNCTION test_param_names3(a0 integer) RETURNS boolean AS $$ +try: + assert a1 == args[0] + return False +except NameError as e: + assert e.args[0].find("a1") > -1 + return True +$$ LANGUAGE plpython3u; +SELECT test_param_names0(2,7); + test_param_names0 +------------------- + 9 +(1 row) + +SELECT test_param_names1(1,'text'); + test_param_names1 +------------------- + t +(1 row) + +SELECT test_param_names2(users) from users; + test_param_names2 +----------------------------------------------------------------------- + {'fname': 'jane', 'lname': 'doe', 'userid': 1, 'username': 'j_doe'} + {'fname': 'john', 'lname': 'doe', 'userid': 2, 'username': 'johnd'} + {'fname': 'willem', 'lname': 'doe', 'userid': 3, 'username': 'w_doe'} + {'fname': 'rick', 'lname': 'smith', 'userid': 4, 'username': 'slash'} +(4 rows) + +SELECT test_param_names2(NULL); + test_param_names2 +------------------- + None +(1 row) + +SELECT test_param_names3(1); + test_param_names3 +------------------- + t +(1 row) + diff --git a/src/pl/plpython/expected/plpython_populate.out b/src/pl/plpython/expected/plpython_populate.out new file mode 100644 index 0000000..4db75b0 --- /dev/null +++ b/src/pl/plpython/expected/plpython_populate.out @@ -0,0 +1,22 @@ +INSERT INTO users (fname, lname, username) VALUES ('jane', 'doe', 'j_doe'); +INSERT INTO users (fname, lname, username) VALUES ('john', 'doe', 'johnd'); +INSERT INTO users (fname, lname, username) VALUES ('willem', 'doe', 'w_doe'); +INSERT INTO users (fname, lname, username) VALUES ('rick', 'smith', 'slash'); +-- multi table tests +-- +INSERT INTO taxonomy (name) VALUES ('HIV I') ; +INSERT INTO taxonomy (name) VALUES ('HIV II') ; +INSERT INTO taxonomy (name) VALUES ('HCV') ; +INSERT INTO entry (accession, txid) VALUES ('A00001', '1') ; +INSERT INTO entry (accession, txid) VALUES ('A00002', '1') ; +INSERT INTO entry (accession, txid) VALUES ('A00003', '1') ; +INSERT INTO entry (accession, txid) VALUES ('A00004', '2') ; +INSERT INTO entry (accession, txid) VALUES ('A00005', '2') ; +INSERT INTO entry (accession, txid) VALUES ('A00006', '3') ; +INSERT INTO sequences (sequence, eid, product, multipart) VALUES ('ABCDEF', 1, 'env', 'true') ; +INSERT INTO xsequences (sequence, pid) VALUES ('GHIJKL', 1) ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 2, 'env') ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 3, 'env') ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 4, 'gag') ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 5, 'env') ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 6, 'ns1') ; diff --git a/src/pl/plpython/expected/plpython_quote.out b/src/pl/plpython/expected/plpython_quote.out new file mode 100644 index 0000000..1fbe93d --- /dev/null +++ b/src/pl/plpython/expected/plpython_quote.out @@ -0,0 +1,56 @@ +-- test quoting functions +CREATE FUNCTION quote(t text, how text) RETURNS text AS $$ + if how == "literal": + return plpy.quote_literal(t) + elif how == "nullable": + return plpy.quote_nullable(t) + elif how == "ident": + return plpy.quote_ident(t) + else: + raise plpy.Error("unrecognized quote type %s" % how) +$$ LANGUAGE plpython3u; +SELECT quote(t, 'literal') FROM (VALUES + ('abc'), + ('a''bc'), + ('''abc'''), + (''), + (''''), + ('xyzv')) AS v(t); + quote +----------- + 'abc' + 'a''bc' + '''abc''' + '' + '''' + 'xyzv' +(6 rows) + +SELECT quote(t, 'nullable') FROM (VALUES + ('abc'), + ('a''bc'), + ('''abc'''), + (''), + (''''), + (NULL)) AS v(t); + quote +----------- + 'abc' + 'a''bc' + '''abc''' + '' + '''' + NULL +(6 rows) + +SELECT quote(t, 'ident') FROM (VALUES + ('abc'), + ('a b c'), + ('a " ''abc''')) AS v(t); + quote +-------------- + abc + "a b c" + "a "" 'abc'" +(3 rows) + diff --git a/src/pl/plpython/expected/plpython_record.out b/src/pl/plpython/expected/plpython_record.out new file mode 100644 index 0000000..31de198 --- /dev/null +++ b/src/pl/plpython/expected/plpython_record.out @@ -0,0 +1,373 @@ +-- +-- Test returning tuples +-- +CREATE TABLE table_record ( + first text, + second int4 + ) ; +CREATE TYPE type_record AS ( + first text, + second int4 + ) ; +CREATE FUNCTION test_table_record_as(typ text, first text, second integer, retnull boolean) RETURNS table_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 plpython3u; +CREATE FUNCTION test_type_record_as(typ text, first text, second integer, retnull boolean) RETURNS type_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 +elif typ == 'str': + return "('%s',%r)" % (first, second) +$$ LANGUAGE plpython3u; +CREATE FUNCTION test_in_out_params(first in text, second out text) AS $$ +return first + '_in_to_out'; +$$ LANGUAGE plpython3u; +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 plpython3u; +CREATE FUNCTION test_inout_params(first inout text) AS $$ +return first + '_inout'; +$$ LANGUAGE plpython3u; +-- Test tuple returning functions +SELECT * FROM test_table_record_as('dict', null, null, false); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM test_table_record_as('dict', 'one', null, false); + first | second +-------+-------- + one | +(1 row) + +SELECT * FROM test_table_record_as('dict', null, 2, false); + first | second +-------+-------- + | 2 +(1 row) + +SELECT * FROM test_table_record_as('dict', 'three', 3, false); + first | second +-------+-------- + three | 3 +(1 row) + +SELECT * FROM test_table_record_as('dict', null, null, true); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM test_table_record_as('tuple', null, null, false); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM test_table_record_as('tuple', 'one', null, false); + first | second +-------+-------- + one | +(1 row) + +SELECT * FROM test_table_record_as('tuple', null, 2, false); + first | second +-------+-------- + | 2 +(1 row) + +SELECT * FROM test_table_record_as('tuple', 'three', 3, false); + first | second +-------+-------- + three | 3 +(1 row) + +SELECT * FROM test_table_record_as('tuple', null, null, true); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM test_table_record_as('list', null, null, false); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM test_table_record_as('list', 'one', null, false); + first | second +-------+-------- + one | +(1 row) + +SELECT * FROM test_table_record_as('list', null, 2, false); + first | second +-------+-------- + | 2 +(1 row) + +SELECT * FROM test_table_record_as('list', 'three', 3, false); + first | second +-------+-------- + three | 3 +(1 row) + +SELECT * FROM test_table_record_as('list', null, null, true); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM test_table_record_as('obj', null, null, false); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM test_table_record_as('obj', 'one', null, false); + first | second +-------+-------- + one | +(1 row) + +SELECT * FROM test_table_record_as('obj', null, 2, false); + first | second +-------+-------- + | 2 +(1 row) + +SELECT * FROM test_table_record_as('obj', 'three', 3, false); + first | second +-------+-------- + three | 3 +(1 row) + +SELECT * FROM test_table_record_as('obj', null, null, true); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM test_type_record_as('dict', null, null, false); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM test_type_record_as('dict', 'one', null, false); + first | second +-------+-------- + one | +(1 row) + +SELECT * FROM test_type_record_as('dict', null, 2, false); + first | second +-------+-------- + | 2 +(1 row) + +SELECT * FROM test_type_record_as('dict', 'three', 3, false); + first | second +-------+-------- + three | 3 +(1 row) + +SELECT * FROM test_type_record_as('dict', null, null, true); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM test_type_record_as('tuple', null, null, false); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM test_type_record_as('tuple', 'one', null, false); + first | second +-------+-------- + one | +(1 row) + +SELECT * FROM test_type_record_as('tuple', null, 2, false); + first | second +-------+-------- + | 2 +(1 row) + +SELECT * FROM test_type_record_as('tuple', 'three', 3, false); + first | second +-------+-------- + three | 3 +(1 row) + +SELECT * FROM test_type_record_as('tuple', null, null, true); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM test_type_record_as('list', null, null, false); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM test_type_record_as('list', 'one', null, false); + first | second +-------+-------- + one | +(1 row) + +SELECT * FROM test_type_record_as('list', null, 2, false); + first | second +-------+-------- + | 2 +(1 row) + +SELECT * FROM test_type_record_as('list', 'three', 3, false); + first | second +-------+-------- + three | 3 +(1 row) + +SELECT * FROM test_type_record_as('list', null, null, true); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM test_type_record_as('obj', null, null, false); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM test_type_record_as('obj', 'one', null, false); + first | second +-------+-------- + one | +(1 row) + +SELECT * FROM test_type_record_as('obj', null, 2, false); + first | second +-------+-------- + | 2 +(1 row) + +SELECT * FROM test_type_record_as('obj', 'three', 3, false); + first | second +-------+-------- + three | 3 +(1 row) + +SELECT * FROM test_type_record_as('obj', null, null, true); + first | second +-------+-------- + | +(1 row) + +SELECT * FROM test_type_record_as('str', 'one', 1, false); + first | second +-------+-------- + 'one' | 1 +(1 row) + +SELECT * FROM test_in_out_params('test_in'); + second +------------------- + 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 +--------------- + test_in_inout +(1 row) + +-- try changing the return types and call functions again +ALTER TABLE table_record DROP COLUMN first; +ALTER TABLE table_record DROP COLUMN second; +ALTER TABLE table_record ADD COLUMN first text; +ALTER TABLE table_record ADD COLUMN second int4; +SELECT * FROM test_table_record_as('obj', 'one', 1, false); + first | second +-------+-------- + one | 1 +(1 row) + +ALTER TYPE type_record DROP ATTRIBUTE first; +ALTER TYPE type_record DROP ATTRIBUTE second; +ALTER TYPE type_record ADD ATTRIBUTE first text; +ALTER TYPE type_record ADD ATTRIBUTE second int4; +SELECT * FROM test_type_record_as('obj', 'one', 1, false); + first | second +-------+-------- + one | 1 +(1 row) + +-- errors cases +CREATE FUNCTION test_type_record_error1() RETURNS type_record AS $$ + return { 'first': 'first' } +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_record_error1(); +ERROR: key "second" 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 "test_type_record_error1" +CREATE FUNCTION test_type_record_error2() RETURNS type_record AS $$ + return [ 'first' ] +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_record_error2(); +ERROR: length of returned sequence did not match number of columns in row +CONTEXT: while creating return value +PL/Python function "test_type_record_error2" +CREATE FUNCTION test_type_record_error3() RETURNS type_record AS $$ + class type_record: pass + type_record.first = 'first' + return type_record +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_record_error3(); +ERROR: attribute "second" does not exist in Python object +HINT: To return null in a column, let the returned object have an attribute named after column with value None. +CONTEXT: while creating return value +PL/Python function "test_type_record_error3" +CREATE FUNCTION test_type_record_error4() RETURNS type_record AS $$ + return 'foo' +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_record_error4(); +ERROR: malformed record literal: "foo" +DETAIL: Missing left parenthesis. +CONTEXT: while creating return value +PL/Python function "test_type_record_error4" diff --git a/src/pl/plpython/expected/plpython_schema.out b/src/pl/plpython/expected/plpython_schema.out new file mode 100644 index 0000000..23d94f6 --- /dev/null +++ b/src/pl/plpython/expected/plpython_schema.out @@ -0,0 +1,33 @@ +CREATE TABLE users ( + fname text not null, + lname text not null, + username text, + userid serial, + PRIMARY KEY(lname, fname) + ) ; +CREATE INDEX users_username_idx ON users(username); +CREATE INDEX users_fname_idx ON users(fname); +CREATE INDEX users_lname_idx ON users(lname); +CREATE INDEX users_userid_idx ON users(userid); +CREATE TABLE taxonomy ( + id serial primary key, + name text unique + ) ; +CREATE TABLE entry ( + accession text not null primary key, + eid serial unique, + txid int2 not null references taxonomy(id) + ) ; +CREATE TABLE sequences ( + eid int4 not null references entry(eid), + pid serial primary key, + product text not null, + sequence text not null, + multipart bool default 'false' + ) ; +CREATE INDEX sequences_product_idx ON sequences(product) ; +CREATE TABLE xsequences ( + pid int4 not null references sequences(pid), + sequence text not null + ) ; +CREATE INDEX xsequences_pid_idx ON xsequences(pid) ; diff --git a/src/pl/plpython/expected/plpython_setof.out b/src/pl/plpython/expected/plpython_setof.out new file mode 100644 index 0000000..3940940 --- /dev/null +++ b/src/pl/plpython/expected/plpython_setof.out @@ -0,0 +1,200 @@ +-- +-- Test returning SETOF +-- +CREATE FUNCTION test_setof_error() RETURNS SETOF text AS $$ +return 37 +$$ LANGUAGE plpython3u; +SELECT test_setof_error(); +ERROR: returned object cannot be iterated +DETAIL: PL/Python set-returning functions must return an iterable object. +CONTEXT: PL/Python function "test_setof_error" +CREATE FUNCTION test_setof_as_list(count integer, content text) RETURNS SETOF text AS $$ +return [ content ]*count +$$ LANGUAGE plpython3u; +CREATE FUNCTION test_setof_as_tuple(count integer, content text) RETURNS SETOF text AS $$ +t = () +for i in range(count): + t += ( content, ) +return t +$$ LANGUAGE plpython3u; +CREATE FUNCTION test_setof_as_iterator(count integer, content text) RETURNS SETOF text AS $$ +class producer: + def __init__ (self, icount, icontent): + self.icontent = icontent + self.icount = icount + def __iter__ (self): + return self + def __next__ (self): + if self.icount == 0: + raise StopIteration + self.icount -= 1 + return self.icontent +return producer(count, content) +$$ LANGUAGE plpython3u; +CREATE FUNCTION test_setof_spi_in_iterator() RETURNS SETOF text AS +$$ + for s in ('Hello', 'Brave', 'New', 'World'): + plpy.execute('select 1') + yield s + plpy.execute('select 2') +$$ +LANGUAGE plpython3u; +-- Test set returning functions +SELECT test_setof_as_list(0, 'list'); + test_setof_as_list +-------------------- +(0 rows) + +SELECT test_setof_as_list(1, 'list'); + test_setof_as_list +-------------------- + list +(1 row) + +SELECT test_setof_as_list(2, 'list'); + test_setof_as_list +-------------------- + list + list +(2 rows) + +SELECT test_setof_as_list(2, null); + test_setof_as_list +-------------------- + + +(2 rows) + +SELECT test_setof_as_tuple(0, 'tuple'); + test_setof_as_tuple +--------------------- +(0 rows) + +SELECT test_setof_as_tuple(1, 'tuple'); + test_setof_as_tuple +--------------------- + tuple +(1 row) + +SELECT test_setof_as_tuple(2, 'tuple'); + test_setof_as_tuple +--------------------- + tuple + tuple +(2 rows) + +SELECT test_setof_as_tuple(2, null); + test_setof_as_tuple +--------------------- + + +(2 rows) + +SELECT test_setof_as_iterator(0, 'list'); + test_setof_as_iterator +------------------------ +(0 rows) + +SELECT test_setof_as_iterator(1, 'list'); + test_setof_as_iterator +------------------------ + list +(1 row) + +SELECT test_setof_as_iterator(2, 'list'); + test_setof_as_iterator +------------------------ + list + list +(2 rows) + +SELECT test_setof_as_iterator(2, null); + test_setof_as_iterator +------------------------ + + +(2 rows) + +SELECT test_setof_spi_in_iterator(); + test_setof_spi_in_iterator +---------------------------- + Hello + Brave + New + World +(4 rows) + +-- set-returning function that modifies its parameters +CREATE OR REPLACE FUNCTION ugly(x int, lim int) RETURNS SETOF int AS $$ +global x +while x <= lim: + yield x + x = x + 1 +$$ LANGUAGE plpython3u; +SELECT ugly(1, 5); + ugly +------ + 1 + 2 + 3 + 4 + 5 +(5 rows) + +-- interleaved execution of such a function +SELECT ugly(1,3), ugly(7,8); + ugly | ugly +------+------ + 1 | 7 + 2 | 8 + 3 | +(3 rows) + +-- returns set of named-composite-type tuples +CREATE OR REPLACE FUNCTION get_user_records() +RETURNS SETOF users +AS $$ + return plpy.execute("SELECT * FROM users ORDER BY username") +$$ LANGUAGE plpython3u; +SELECT get_user_records(); + get_user_records +---------------------- + (jane,doe,j_doe,1) + (john,doe,johnd,2) + (rick,smith,slash,4) + (willem,doe,w_doe,3) +(4 rows) + +SELECT * FROM get_user_records(); + fname | lname | username | userid +--------+-------+----------+-------- + jane | doe | j_doe | 1 + john | doe | johnd | 2 + rick | smith | slash | 4 + willem | doe | w_doe | 3 +(4 rows) + +-- same, but returning set of RECORD +CREATE OR REPLACE FUNCTION get_user_records2() +RETURNS TABLE(fname text, lname text, username text, userid int) +AS $$ + return plpy.execute("SELECT * FROM users ORDER BY username") +$$ LANGUAGE plpython3u; +SELECT get_user_records2(); + get_user_records2 +---------------------- + (jane,doe,j_doe,1) + (john,doe,johnd,2) + (rick,smith,slash,4) + (willem,doe,w_doe,3) +(4 rows) + +SELECT * FROM get_user_records2(); + fname | lname | username | userid +--------+-------+----------+-------- + jane | doe | j_doe | 1 + john | doe | johnd | 2 + rick | smith | slash | 4 + willem | doe | w_doe | 3 +(4 rows) + diff --git a/src/pl/plpython/expected/plpython_spi.out b/src/pl/plpython/expected/plpython_spi.out new file mode 100644 index 0000000..8853e25 --- /dev/null +++ b/src/pl/plpython/expected/plpython_spi.out @@ -0,0 +1,466 @@ +-- +-- nested calls +-- +CREATE FUNCTION nested_call_one(a text) RETURNS text + AS +'q = "SELECT nested_call_two(''%s'')" % a +r = plpy.execute(q) +return r[0]' + LANGUAGE plpython3u ; +CREATE FUNCTION nested_call_two(a text) RETURNS text + AS +'q = "SELECT nested_call_three(''%s'')" % a +r = plpy.execute(q) +return r[0]' + LANGUAGE plpython3u ; +CREATE FUNCTION nested_call_three(a text) RETURNS text + AS +'return a' + LANGUAGE plpython3u ; +-- some spi stuff +CREATE FUNCTION spi_prepared_plan_test_one(a text) RETURNS text + AS +'if "myplan" not in SD: + q = "SELECT count(*) FROM users WHERE lname = $1" + SD["myplan"] = plpy.prepare(q, [ "text" ]) +try: + rv = plpy.execute(SD["myplan"], [a]) + return "there are " + str(rv[0]["count"]) + " " + str(a) + "s" +except Exception as ex: + plpy.error(str(ex)) +return None +' + LANGUAGE plpython3u; +CREATE FUNCTION spi_prepared_plan_test_two(a text) RETURNS text + AS +'if "myplan" not in SD: + q = "SELECT count(*) FROM users WHERE lname = $1" + SD["myplan"] = plpy.prepare(q, [ "text" ]) +try: + rv = SD["myplan"].execute([a]) + return "there are " + str(rv[0]["count"]) + " " + str(a) + "s" +except Exception as ex: + plpy.error(str(ex)) +return None +' + LANGUAGE plpython3u; +CREATE FUNCTION spi_prepared_plan_test_nested(a text) RETURNS text + AS +'if "myplan" not in SD: + q = "SELECT spi_prepared_plan_test_one(''%s'') as count" % a + SD["myplan"] = plpy.prepare(q) +try: + rv = plpy.execute(SD["myplan"]) + if len(rv): + return rv[0]["count"] +except Exception as ex: + plpy.error(str(ex)) +return None +' + LANGUAGE plpython3u; +CREATE FUNCTION join_sequences(s sequences) RETURNS text + AS +'if not s["multipart"]: + return s["sequence"] +q = "SELECT sequence FROM xsequences WHERE pid = ''%s''" % s["pid"] +rv = plpy.execute(q) +seq = s["sequence"] +for r in rv: + seq = seq + r["sequence"] +return seq +' + LANGUAGE plpython3u; +CREATE FUNCTION spi_recursive_sum(a int) RETURNS int + AS +'r = 0 +if a > 1: + r = plpy.execute("SELECT spi_recursive_sum(%d) as a" % (a-1))[0]["a"] +return a + r +' + LANGUAGE plpython3u; +-- +-- spi and nested calls +-- +select nested_call_one('pass this along'); + nested_call_one +----------------------------------------------------------------- + {'nested_call_two': "{'nested_call_three': 'pass this along'}"} +(1 row) + +select spi_prepared_plan_test_one('doe'); + spi_prepared_plan_test_one +---------------------------- + there are 3 does +(1 row) + +select spi_prepared_plan_test_two('smith'); + spi_prepared_plan_test_two +---------------------------- + there are 1 smiths +(1 row) + +select spi_prepared_plan_test_nested('smith'); + spi_prepared_plan_test_nested +------------------------------- + there are 1 smiths +(1 row) + +SELECT join_sequences(sequences) FROM sequences; + join_sequences +---------------- + ABCDEFGHIJKL + ABCDEF + ABCDEF + ABCDEF + ABCDEF + ABCDEF +(6 rows) + +SELECT join_sequences(sequences) FROM sequences + WHERE join_sequences(sequences) ~* '^A'; + join_sequences +---------------- + ABCDEFGHIJKL + ABCDEF + ABCDEF + ABCDEF + ABCDEF + ABCDEF +(6 rows) + +SELECT join_sequences(sequences) FROM sequences + WHERE join_sequences(sequences) ~* '^B'; + join_sequences +---------------- +(0 rows) + +SELECT spi_recursive_sum(10); + spi_recursive_sum +------------------- + 55 +(1 row) + +-- +-- plan and result objects +-- +CREATE FUNCTION result_metadata_test(cmd text) RETURNS int +AS $$ +plan = plpy.prepare(cmd) +plpy.info(plan.status()) # not really documented or useful +result = plpy.execute(plan) +if result.status() > 0: + plpy.info(result.colnames()) + plpy.info(result.coltypes()) + plpy.info(result.coltypmods()) + return result.nrows() +else: + return None +$$ LANGUAGE plpython3u; +SELECT result_metadata_test($$SELECT 1 AS foo, '11'::text AS bar UNION SELECT 2, '22'$$); +INFO: True +INFO: ['foo', 'bar'] +INFO: [23, 25] +INFO: [-1, -1] + result_metadata_test +---------------------- + 2 +(1 row) + +SELECT result_metadata_test($$CREATE TEMPORARY TABLE foo1 (a int, b text)$$); +INFO: True +ERROR: plpy.Error: command did not produce a result set +CONTEXT: Traceback (most recent call last): + PL/Python function "result_metadata_test", line 6, in <module> + plpy.info(result.colnames()) +PL/Python function "result_metadata_test" +CREATE FUNCTION result_nrows_test(cmd text) RETURNS int +AS $$ +result = plpy.execute(cmd) +return result.nrows() +$$ LANGUAGE plpython3u; +SELECT result_nrows_test($$SELECT 1$$); + result_nrows_test +------------------- + 1 +(1 row) + +SELECT result_nrows_test($$CREATE TEMPORARY TABLE foo2 (a int, b text)$$); + result_nrows_test +------------------- + 0 +(1 row) + +SELECT result_nrows_test($$INSERT INTO foo2 VALUES (1, 'one'), (2, 'two')$$); + result_nrows_test +------------------- + 2 +(1 row) + +SELECT result_nrows_test($$UPDATE foo2 SET b = '' WHERE a = 2$$); + result_nrows_test +------------------- + 1 +(1 row) + +CREATE FUNCTION result_len_test(cmd text) RETURNS int +AS $$ +result = plpy.execute(cmd) +return len(result) +$$ LANGUAGE plpython3u; +SELECT result_len_test($$SELECT 1$$); + result_len_test +----------------- + 1 +(1 row) + +SELECT result_len_test($$CREATE TEMPORARY TABLE foo3 (a int, b text)$$); + result_len_test +----------------- + 0 +(1 row) + +SELECT result_len_test($$INSERT INTO foo3 VALUES (1, 'one'), (2, 'two')$$); + result_len_test +----------------- + 0 +(1 row) + +SELECT result_len_test($$UPDATE foo3 SET b= '' WHERE a = 2$$); + result_len_test +----------------- + 0 +(1 row) + +CREATE FUNCTION result_subscript_test() RETURNS void +AS $$ +result = plpy.execute("SELECT 1 AS c UNION ALL SELECT 2 " + "UNION ALL SELECT 3 UNION ALL SELECT 4") + +plpy.info(result[1]['c']) +plpy.info(result[-1]['c']) + +plpy.info([item['c'] for item in result[1:3]]) +plpy.info([item['c'] for item in result[::2]]) + +result[-1] = {'c': 1000} +result[:2] = [{'c': 10}, {'c': 100}] +plpy.info([item['c'] for item in result[:]]) + +# raises TypeError, catch so further tests could be added +try: + plpy.info(result['foo']) +except TypeError: + pass +else: + assert False, "TypeError not raised" + +$$ LANGUAGE plpython3u; +SELECT result_subscript_test(); +INFO: 2 +INFO: 4 +INFO: [2, 3] +INFO: [1, 3] +INFO: [10, 100, 3, 1000] + result_subscript_test +----------------------- + +(1 row) + +CREATE FUNCTION result_empty_test() RETURNS void +AS $$ +result = plpy.execute("select 1 where false") + +plpy.info(result[:]) + +$$ LANGUAGE plpython3u; +SELECT result_empty_test(); +INFO: [] + result_empty_test +------------------- + +(1 row) + +CREATE FUNCTION result_str_test(cmd text) RETURNS text +AS $$ +plan = plpy.prepare(cmd) +result = plpy.execute(plan) +return str(result) +$$ LANGUAGE plpython3u; +SELECT result_str_test($$SELECT 1 AS foo UNION SELECT 2$$); + result_str_test +------------------------------------------------------------ + <PLyResult status=5 nrows=2 rows=[{'foo': 1}, {'foo': 2}]> +(1 row) + +SELECT result_str_test($$CREATE TEMPORARY TABLE foo1 (a int, b text)$$); + result_str_test +-------------------------------------- + <PLyResult status=4 nrows=0 rows=[]> +(1 row) + +-- cursor objects +CREATE FUNCTION simple_cursor_test() RETURNS int AS $$ +res = plpy.cursor("select fname, lname from users") +does = 0 +for row in res: + if row['lname'] == 'doe': + does += 1 +return does +$$ LANGUAGE plpython3u; +CREATE FUNCTION double_cursor_close() RETURNS int AS $$ +res = plpy.cursor("select fname, lname from users") +res.close() +res.close() +$$ LANGUAGE plpython3u; +CREATE FUNCTION cursor_fetch() RETURNS int AS $$ +res = plpy.cursor("select fname, lname from users") +assert len(res.fetch(3)) == 3 +assert len(res.fetch(3)) == 1 +assert len(res.fetch(3)) == 0 +assert len(res.fetch(3)) == 0 +try: + # use next() or __next__(), the method name changed in + # http://www.python.org/dev/peps/pep-3114/ + try: + res.next() + except AttributeError: + res.__next__() +except StopIteration: + pass +else: + assert False, "StopIteration not raised" +$$ LANGUAGE plpython3u; +CREATE FUNCTION cursor_mix_next_and_fetch() RETURNS int AS $$ +res = plpy.cursor("select fname, lname from users order by fname") +assert len(res.fetch(2)) == 2 + +item = None +try: + item = res.next() +except AttributeError: + item = res.__next__() +assert item['fname'] == 'rick' + +assert len(res.fetch(2)) == 1 +$$ LANGUAGE plpython3u; +CREATE FUNCTION fetch_after_close() RETURNS int AS $$ +res = plpy.cursor("select fname, lname from users") +res.close() +try: + res.fetch(1) +except ValueError: + pass +else: + assert False, "ValueError not raised" +$$ LANGUAGE plpython3u; +CREATE FUNCTION next_after_close() RETURNS int AS $$ +res = plpy.cursor("select fname, lname from users") +res.close() +try: + try: + res.next() + except AttributeError: + res.__next__() +except ValueError: + pass +else: + assert False, "ValueError not raised" +$$ LANGUAGE plpython3u; +CREATE FUNCTION cursor_fetch_next_empty() RETURNS int AS $$ +res = plpy.cursor("select fname, lname from users where false") +assert len(res.fetch(1)) == 0 +try: + try: + res.next() + except AttributeError: + res.__next__() +except StopIteration: + pass +else: + assert False, "StopIteration not raised" +$$ LANGUAGE plpython3u; +CREATE FUNCTION cursor_plan() RETURNS SETOF text AS $$ +plan = plpy.prepare( + "select fname, lname from users where fname like $1 || '%' order by fname", + ["text"]) +for row in plpy.cursor(plan, ["w"]): + yield row['fname'] +for row in plan.cursor(["j"]): + yield row['fname'] +$$ LANGUAGE plpython3u; +CREATE FUNCTION cursor_plan_wrong_args() RETURNS SETOF text AS $$ +plan = plpy.prepare("select fname, lname from users where fname like $1 || '%'", + ["text"]) +c = plpy.cursor(plan, ["a", "b"]) +$$ LANGUAGE plpython3u; +CREATE TYPE test_composite_type AS ( + a1 int, + a2 varchar +); +CREATE OR REPLACE FUNCTION plan_composite_args() RETURNS test_composite_type AS $$ +plan = plpy.prepare("select $1 as c1", ["test_composite_type"]) +res = plpy.execute(plan, [{"a1": 3, "a2": "label"}]) +return res[0]["c1"] +$$ LANGUAGE plpython3u; +SELECT simple_cursor_test(); + simple_cursor_test +-------------------- + 3 +(1 row) + +SELECT double_cursor_close(); + double_cursor_close +--------------------- + +(1 row) + +SELECT cursor_fetch(); + cursor_fetch +-------------- + +(1 row) + +SELECT cursor_mix_next_and_fetch(); + cursor_mix_next_and_fetch +--------------------------- + +(1 row) + +SELECT fetch_after_close(); + fetch_after_close +------------------- + +(1 row) + +SELECT next_after_close(); + next_after_close +------------------ + +(1 row) + +SELECT cursor_fetch_next_empty(); + cursor_fetch_next_empty +------------------------- + +(1 row) + +SELECT cursor_plan(); + cursor_plan +------------- + willem + jane + john +(3 rows) + +SELECT cursor_plan_wrong_args(); +ERROR: TypeError: Expected sequence of 1 argument, got 2: ['a', 'b'] +CONTEXT: Traceback (most recent call last): + PL/Python function "cursor_plan_wrong_args", line 4, in <module> + c = plpy.cursor(plan, ["a", "b"]) +PL/Python function "cursor_plan_wrong_args" +SELECT plan_composite_args(); + plan_composite_args +--------------------- + (3,label) +(1 row) + diff --git a/src/pl/plpython/expected/plpython_subtransaction.out b/src/pl/plpython/expected/plpython_subtransaction.out new file mode 100644 index 0000000..43d9277 --- /dev/null +++ b/src/pl/plpython/expected/plpython_subtransaction.out @@ -0,0 +1,401 @@ +-- +-- Test explicit subtransactions +-- +-- Test table to see if transactions get properly rolled back +CREATE TABLE subtransaction_tbl ( + i integer +); +CREATE FUNCTION subtransaction_ctx_test(what_error text = NULL) RETURNS text +AS $$ +with plpy.subtransaction(): + plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)") + plpy.execute("INSERT INTO subtransaction_tbl VALUES (2)") + if what_error == "SPI": + plpy.execute("INSERT INTO subtransaction_tbl VALUES ('oops')") + elif what_error == "Python": + raise Exception("Python exception") +$$ LANGUAGE plpython3u; +SELECT subtransaction_ctx_test(); + subtransaction_ctx_test +------------------------- + +(1 row) + +SELECT * FROM subtransaction_tbl; + i +--- + 1 + 2 +(2 rows) + +TRUNCATE subtransaction_tbl; +SELECT subtransaction_ctx_test('SPI'); +ERROR: spiexceptions.InvalidTextRepresentation: invalid input syntax for type integer: "oops" +LINE 1: INSERT INTO subtransaction_tbl VALUES ('oops') + ^ +QUERY: INSERT INTO subtransaction_tbl VALUES ('oops') +CONTEXT: Traceback (most recent call last): + PL/Python function "subtransaction_ctx_test", line 6, in <module> + plpy.execute("INSERT INTO subtransaction_tbl VALUES ('oops')") +PL/Python function "subtransaction_ctx_test" +SELECT * FROM subtransaction_tbl; + i +--- +(0 rows) + +TRUNCATE subtransaction_tbl; +SELECT subtransaction_ctx_test('Python'); +ERROR: Exception: Python exception +CONTEXT: Traceback (most recent call last): + PL/Python function "subtransaction_ctx_test", line 8, in <module> + raise Exception("Python exception") +PL/Python function "subtransaction_ctx_test" +SELECT * FROM subtransaction_tbl; + i +--- +(0 rows) + +TRUNCATE subtransaction_tbl; +-- Nested subtransactions +CREATE FUNCTION subtransaction_nested_test(swallow boolean = 'f') RETURNS text +AS $$ +plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)") +with plpy.subtransaction(): + plpy.execute("INSERT INTO subtransaction_tbl VALUES (2)") + try: + with plpy.subtransaction(): + plpy.execute("INSERT INTO subtransaction_tbl VALUES (3)") + plpy.execute("error") + except plpy.SPIError as e: + if not swallow: + raise + plpy.notice("Swallowed %s(%r)" % (e.__class__.__name__, e.args[0])) +return "ok" +$$ LANGUAGE plpython3u; +SELECT subtransaction_nested_test(); +ERROR: spiexceptions.SyntaxError: syntax error at or near "error" +LINE 1: error + ^ +QUERY: error +CONTEXT: Traceback (most recent call last): + PL/Python function "subtransaction_nested_test", line 8, in <module> + plpy.execute("error") +PL/Python function "subtransaction_nested_test" +SELECT * FROM subtransaction_tbl; + i +--- +(0 rows) + +TRUNCATE subtransaction_tbl; +SELECT subtransaction_nested_test('t'); +NOTICE: Swallowed SyntaxError('syntax error at or near "error"') + subtransaction_nested_test +---------------------------- + ok +(1 row) + +SELECT * FROM subtransaction_tbl; + i +--- + 1 + 2 +(2 rows) + +TRUNCATE subtransaction_tbl; +-- Nested subtransactions that recursively call code dealing with +-- subtransactions +CREATE FUNCTION subtransaction_deeply_nested_test() RETURNS text +AS $$ +plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)") +with plpy.subtransaction(): + plpy.execute("INSERT INTO subtransaction_tbl VALUES (2)") + plpy.execute("SELECT subtransaction_nested_test('t')") +return "ok" +$$ LANGUAGE plpython3u; +SELECT subtransaction_deeply_nested_test(); +NOTICE: Swallowed SyntaxError('syntax error at or near "error"') + subtransaction_deeply_nested_test +----------------------------------- + ok +(1 row) + +SELECT * FROM subtransaction_tbl; + i +--- + 1 + 2 + 1 + 2 +(4 rows) + +TRUNCATE subtransaction_tbl; +-- Error conditions from not opening/closing subtransactions +CREATE FUNCTION subtransaction_exit_without_enter() RETURNS void +AS $$ +plpy.subtransaction().__exit__(None, None, None) +$$ LANGUAGE plpython3u; +CREATE FUNCTION subtransaction_enter_without_exit() RETURNS void +AS $$ +plpy.subtransaction().__enter__() +$$ LANGUAGE plpython3u; +CREATE FUNCTION subtransaction_exit_twice() RETURNS void +AS $$ +plpy.subtransaction().__enter__() +plpy.subtransaction().__exit__(None, None, None) +plpy.subtransaction().__exit__(None, None, None) +$$ LANGUAGE plpython3u; +CREATE FUNCTION subtransaction_enter_twice() RETURNS void +AS $$ +plpy.subtransaction().__enter__() +plpy.subtransaction().__enter__() +$$ LANGUAGE plpython3u; +CREATE FUNCTION subtransaction_exit_same_subtransaction_twice() RETURNS void +AS $$ +s = plpy.subtransaction() +s.__enter__() +s.__exit__(None, None, None) +s.__exit__(None, None, None) +$$ LANGUAGE plpython3u; +CREATE FUNCTION subtransaction_enter_same_subtransaction_twice() RETURNS void +AS $$ +s = plpy.subtransaction() +s.__enter__() +s.__enter__() +s.__exit__(None, None, None) +$$ LANGUAGE plpython3u; +-- No warnings here, as the subtransaction gets indeed closed +CREATE FUNCTION subtransaction_enter_subtransaction_in_with() RETURNS void +AS $$ +with plpy.subtransaction() as s: + s.__enter__() +$$ LANGUAGE plpython3u; +CREATE FUNCTION subtransaction_exit_subtransaction_in_with() RETURNS void +AS $$ +try: + with plpy.subtransaction() as s: + s.__exit__(None, None, None) +except ValueError as e: + raise ValueError(e) +$$ LANGUAGE plpython3u; +SELECT subtransaction_exit_without_enter(); +ERROR: ValueError: this subtransaction has not been entered +CONTEXT: Traceback (most recent call last): + PL/Python function "subtransaction_exit_without_enter", line 2, in <module> + plpy.subtransaction().__exit__(None, None, None) +PL/Python function "subtransaction_exit_without_enter" +SELECT subtransaction_enter_without_exit(); +WARNING: forcibly aborting a subtransaction that has not been exited + subtransaction_enter_without_exit +----------------------------------- + +(1 row) + +SELECT subtransaction_exit_twice(); +WARNING: forcibly aborting a subtransaction that has not been exited +ERROR: ValueError: this subtransaction has not been entered +CONTEXT: Traceback (most recent call last): + PL/Python function "subtransaction_exit_twice", line 3, in <module> + plpy.subtransaction().__exit__(None, None, None) +PL/Python function "subtransaction_exit_twice" +SELECT subtransaction_enter_twice(); +WARNING: forcibly aborting a subtransaction that has not been exited +WARNING: forcibly aborting a subtransaction that has not been exited + subtransaction_enter_twice +---------------------------- + +(1 row) + +SELECT subtransaction_exit_same_subtransaction_twice(); +ERROR: ValueError: this subtransaction has already been exited +CONTEXT: Traceback (most recent call last): + PL/Python function "subtransaction_exit_same_subtransaction_twice", line 5, in <module> + s.__exit__(None, None, None) +PL/Python function "subtransaction_exit_same_subtransaction_twice" +SELECT subtransaction_enter_same_subtransaction_twice(); +WARNING: forcibly aborting a subtransaction that has not been exited +ERROR: ValueError: this subtransaction has already been entered +CONTEXT: Traceback (most recent call last): + PL/Python function "subtransaction_enter_same_subtransaction_twice", line 4, in <module> + s.__enter__() +PL/Python function "subtransaction_enter_same_subtransaction_twice" +SELECT subtransaction_enter_subtransaction_in_with(); +ERROR: ValueError: this subtransaction has already been entered +CONTEXT: Traceback (most recent call last): + PL/Python function "subtransaction_enter_subtransaction_in_with", line 3, in <module> + s.__enter__() +PL/Python function "subtransaction_enter_subtransaction_in_with" +SELECT subtransaction_exit_subtransaction_in_with(); +ERROR: ValueError: this subtransaction has already been exited +CONTEXT: Traceback (most recent call last): + PL/Python function "subtransaction_exit_subtransaction_in_with", line 6, in <module> + raise ValueError(e) +PL/Python function "subtransaction_exit_subtransaction_in_with" +-- Make sure we don't get a "current transaction is aborted" error +SELECT 1 as test; + test +------ + 1 +(1 row) + +-- Mix explicit subtransactions and normal SPI calls +CREATE FUNCTION subtransaction_mix_explicit_and_implicit() RETURNS void +AS $$ +p = plpy.prepare("INSERT INTO subtransaction_tbl VALUES ($1)", ["integer"]) +try: + with plpy.subtransaction(): + plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)") + plpy.execute(p, [2]) + plpy.execute(p, ["wrong"]) +except plpy.SPIError: + plpy.warning("Caught a SPI error from an explicit subtransaction") + +try: + plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)") + plpy.execute(p, [2]) + plpy.execute(p, ["wrong"]) +except plpy.SPIError: + plpy.warning("Caught a SPI error") +$$ LANGUAGE plpython3u; +SELECT subtransaction_mix_explicit_and_implicit(); +WARNING: Caught a SPI error from an explicit subtransaction +WARNING: Caught a SPI error + subtransaction_mix_explicit_and_implicit +------------------------------------------ + +(1 row) + +SELECT * FROM subtransaction_tbl; + i +--- + 1 + 2 +(2 rows) + +TRUNCATE subtransaction_tbl; +-- Alternative method names for Python <2.6 +CREATE FUNCTION subtransaction_alternative_names() RETURNS void +AS $$ +s = plpy.subtransaction() +s.enter() +s.exit(None, None, None) +$$ LANGUAGE plpython3u; +SELECT subtransaction_alternative_names(); + subtransaction_alternative_names +---------------------------------- + +(1 row) + +-- try/catch inside a subtransaction block +CREATE FUNCTION try_catch_inside_subtransaction() RETURNS void +AS $$ +with plpy.subtransaction(): + plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)") + try: + plpy.execute("INSERT INTO subtransaction_tbl VALUES ('a')") + except plpy.SPIError: + plpy.notice("caught") +$$ LANGUAGE plpython3u; +SELECT try_catch_inside_subtransaction(); +NOTICE: caught + try_catch_inside_subtransaction +--------------------------------- + +(1 row) + +SELECT * FROM subtransaction_tbl; + i +--- + 1 +(1 row) + +TRUNCATE subtransaction_tbl; +ALTER TABLE subtransaction_tbl ADD PRIMARY KEY (i); +CREATE FUNCTION pk_violation_inside_subtransaction() RETURNS void +AS $$ +with plpy.subtransaction(): + plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)") + try: + plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)") + except plpy.SPIError: + plpy.notice("caught") +$$ LANGUAGE plpython3u; +SELECT pk_violation_inside_subtransaction(); +NOTICE: caught + pk_violation_inside_subtransaction +------------------------------------ + +(1 row) + +SELECT * FROM subtransaction_tbl; + i +--- + 1 +(1 row) + +DROP TABLE subtransaction_tbl; +-- cursor/subtransactions interactions +CREATE FUNCTION cursor_in_subxact() RETURNS int AS $$ +with plpy.subtransaction(): + cur = plpy.cursor("select * from generate_series(1, 20) as gen(i)") + cur.fetch(10) +fetched = cur.fetch(10); +return int(fetched[5]["i"]) +$$ LANGUAGE plpython3u; +CREATE FUNCTION cursor_aborted_subxact() RETURNS int AS $$ +try: + with plpy.subtransaction(): + cur = plpy.cursor("select * from generate_series(1, 20) as gen(i)") + cur.fetch(10); + plpy.execute("select no_such_function()") +except plpy.SPIError: + fetched = cur.fetch(10) + return int(fetched[5]["i"]) +return 0 # not reached +$$ LANGUAGE plpython3u; +CREATE FUNCTION cursor_plan_aborted_subxact() RETURNS int AS $$ +try: + with plpy.subtransaction(): + plpy.execute('create temporary table tmp(i) ' + 'as select generate_series(1, 10)') + plan = plpy.prepare("select i from tmp") + cur = plpy.cursor(plan) + plpy.execute("select no_such_function()") +except plpy.SPIError: + fetched = cur.fetch(5) + return fetched[2]["i"] +return 0 # not reached +$$ LANGUAGE plpython3u; +CREATE FUNCTION cursor_close_aborted_subxact() RETURNS boolean AS $$ +try: + with plpy.subtransaction(): + cur = plpy.cursor('select 1') + plpy.execute("select no_such_function()") +except plpy.SPIError: + cur.close() + return True +return False # not reached +$$ LANGUAGE plpython3u; +SELECT cursor_in_subxact(); + cursor_in_subxact +------------------- + 16 +(1 row) + +SELECT cursor_aborted_subxact(); +ERROR: ValueError: iterating a cursor in an aborted subtransaction +CONTEXT: Traceback (most recent call last): + PL/Python function "cursor_aborted_subxact", line 8, in <module> + fetched = cur.fetch(10) +PL/Python function "cursor_aborted_subxact" +SELECT cursor_plan_aborted_subxact(); +ERROR: ValueError: iterating a cursor in an aborted subtransaction +CONTEXT: Traceback (most recent call last): + PL/Python function "cursor_plan_aborted_subxact", line 10, in <module> + fetched = cur.fetch(5) +PL/Python function "cursor_plan_aborted_subxact" +SELECT cursor_close_aborted_subxact(); +ERROR: ValueError: closing a cursor in an aborted subtransaction +CONTEXT: Traceback (most recent call last): + PL/Python function "cursor_close_aborted_subxact", line 7, in <module> + cur.close() +PL/Python function "cursor_close_aborted_subxact" diff --git a/src/pl/plpython/expected/plpython_test.out b/src/pl/plpython/expected/plpython_test.out new file mode 100644 index 0000000..13c1411 --- /dev/null +++ b/src/pl/plpython/expected/plpython_test.out @@ -0,0 +1,93 @@ +-- first some tests of basic functionality +CREATE EXTENSION plpython3u; +-- really stupid function just to get the module loaded +CREATE FUNCTION stupid() RETURNS text AS 'return "zarkon"' LANGUAGE plpython3u; +select stupid(); + stupid +-------- + zarkon +(1 row) + +-- check 2/3 versioning +CREATE FUNCTION stupidn() RETURNS text AS 'return "zarkon"' LANGUAGE plpython3u; +select stupidn(); + stupidn +--------- + zarkon +(1 row) + +-- test multiple arguments and odd characters in function name +CREATE FUNCTION "Argument test #1"(u users, a1 text, a2 text) RETURNS text + AS +'keys = list(u.keys()) +keys.sort() +out = [] +for key in keys: + out.append("%s: %s" % (key, u[key])) +words = a1 + " " + a2 + " => {" + ", ".join(out) + "}" +return words' + LANGUAGE plpython3u; +select "Argument test #1"(users, fname, lname) from users where lname = 'doe' order by 1; + Argument test #1 +----------------------------------------------------------------------- + jane doe => {fname: jane, lname: doe, userid: 1, username: j_doe} + john doe => {fname: john, lname: doe, userid: 2, username: johnd} + willem doe => {fname: willem, lname: doe, userid: 3, username: w_doe} +(3 rows) + +-- check module contents +CREATE FUNCTION module_contents() RETURNS SETOF text AS +$$ +contents = list(filter(lambda x: not x.startswith("__"), dir(plpy))) +contents.sort() +return contents +$$ LANGUAGE plpython3u; +select module_contents(); + module_contents +----------------- + Error + Fatal + SPIError + commit + cursor + debug + error + execute + fatal + info + log + notice + prepare + quote_ident + quote_literal + quote_nullable + rollback + spiexceptions + subtransaction + warning +(20 rows) + +CREATE FUNCTION elog_test_basic() RETURNS void +AS $$ +plpy.debug('debug') +plpy.log('log') +plpy.info('info') +plpy.info(37) +plpy.info() +plpy.info('info', 37, [1, 2, 3]) +plpy.notice('notice') +plpy.warning('warning') +plpy.error('error') +$$ LANGUAGE plpython3u; +SELECT elog_test_basic(); +INFO: info +INFO: 37 +INFO: () +INFO: ('info', 37, [1, 2, 3]) +NOTICE: notice +WARNING: warning +ERROR: plpy.Error: error +CONTEXT: Traceback (most recent call last): + PL/Python function "elog_test_basic", line 10, in <module> + plpy.error('error') +PL/Python function "elog_test_basic" diff --git a/src/pl/plpython/expected/plpython_transaction.out b/src/pl/plpython/expected/plpython_transaction.out new file mode 100644 index 0000000..659ccef --- /dev/null +++ b/src/pl/plpython/expected/plpython_transaction.out @@ -0,0 +1,250 @@ +CREATE TABLE test1 (a int, b text); +CREATE PROCEDURE transaction_test1() +LANGUAGE plpython3u +AS $$ +for i in range(0, 10): + plpy.execute("INSERT INTO test1 (a) VALUES (%d)" % i) + if i % 2 == 0: + plpy.commit() + else: + plpy.rollback() +$$; +CALL transaction_test1(); +SELECT * FROM test1; + a | b +---+--- + 0 | + 2 | + 4 | + 6 | + 8 | +(5 rows) + +TRUNCATE test1; +DO +LANGUAGE plpython3u +$$ +for i in range(0, 10): + plpy.execute("INSERT INTO test1 (a) VALUES (%d)" % i) + if i % 2 == 0: + plpy.commit() + else: + plpy.rollback() +$$; +SELECT * FROM test1; + a | b +---+--- + 0 | + 2 | + 4 | + 6 | + 8 | +(5 rows) + +TRUNCATE test1; +-- not allowed in a function +CREATE FUNCTION transaction_test2() RETURNS int +LANGUAGE plpython3u +AS $$ +for i in range(0, 10): + plpy.execute("INSERT INTO test1 (a) VALUES (%d)" % i) + if i % 2 == 0: + plpy.commit() + else: + plpy.rollback() +return 1 +$$; +SELECT transaction_test2(); +ERROR: spiexceptions.InvalidTransactionTermination: invalid transaction termination +CONTEXT: Traceback (most recent call last): + PL/Python function "transaction_test2", line 5, in <module> + plpy.commit() +PL/Python function "transaction_test2" +SELECT * FROM test1; + a | b +---+--- +(0 rows) + +-- also not allowed if procedure is called from a function +CREATE FUNCTION transaction_test3() RETURNS int +LANGUAGE plpython3u +AS $$ +plpy.execute("CALL transaction_test1()") +return 1 +$$; +SELECT transaction_test3(); +ERROR: spiexceptions.InvalidTransactionTermination: spiexceptions.InvalidTransactionTermination: invalid transaction termination +CONTEXT: Traceback (most recent call last): + PL/Python function "transaction_test3", line 2, in <module> + plpy.execute("CALL transaction_test1()") +PL/Python function "transaction_test3" +SELECT * FROM test1; + a | b +---+--- +(0 rows) + +-- DO block inside function +CREATE FUNCTION transaction_test4() RETURNS int +LANGUAGE plpython3u +AS $$ +plpy.execute("DO LANGUAGE plpython3u $x$ plpy.commit() $x$") +return 1 +$$; +SELECT transaction_test4(); +ERROR: spiexceptions.InvalidTransactionTermination: spiexceptions.InvalidTransactionTermination: invalid transaction termination +CONTEXT: Traceback (most recent call last): + PL/Python function "transaction_test4", line 2, in <module> + plpy.execute("DO LANGUAGE plpython3u $x$ plpy.commit() $x$") +PL/Python function "transaction_test4" +-- commit inside subtransaction (prohibited) +DO LANGUAGE plpython3u $$ +s = plpy.subtransaction() +s.enter() +plpy.commit() +$$; +WARNING: forcibly aborting a subtransaction that has not been exited +ERROR: spiexceptions.InvalidTransactionTermination: cannot commit while a subtransaction is active +CONTEXT: Traceback (most recent call last): + PL/Python anonymous code block, line 4, in <module> + plpy.commit() +PL/Python anonymous code block +-- commit inside cursor loop +CREATE TABLE test2 (x int); +INSERT INTO test2 VALUES (0), (1), (2), (3), (4); +TRUNCATE test1; +DO LANGUAGE plpython3u $$ +for row in plpy.cursor("SELECT * FROM test2 ORDER BY x"): + plpy.execute("INSERT INTO test1 (a) VALUES (%s)" % row['x']) + plpy.commit() +$$; +SELECT * FROM test1; + a | b +---+--- + 0 | + 1 | + 2 | + 3 | + 4 | +(5 rows) + +-- check that this doesn't leak a holdable portal +SELECT * FROM pg_cursors; + name | statement | is_holdable | is_binary | is_scrollable | creation_time +------+-----------+-------------+-----------+---------------+--------------- +(0 rows) + +-- error in cursor loop with commit +TRUNCATE test1; +DO LANGUAGE plpython3u $$ +for row in plpy.cursor("SELECT * FROM test2 ORDER BY x"): + plpy.execute("INSERT INTO test1 (a) VALUES (12/(%s-2))" % row['x']) + plpy.commit() +$$; +ERROR: spiexceptions.DivisionByZero: division by zero +CONTEXT: Traceback (most recent call last): + PL/Python anonymous code block, line 3, in <module> + plpy.execute("INSERT INTO test1 (a) VALUES (12/(%s-2))" % row['x']) +PL/Python anonymous code block +SELECT * FROM test1; + a | b +-----+--- + -6 | + -12 | +(2 rows) + +SELECT * FROM pg_cursors; + name | statement | is_holdable | is_binary | is_scrollable | creation_time +------+-----------+-------------+-----------+---------------+--------------- +(0 rows) + +-- rollback inside cursor loop +TRUNCATE test1; +DO LANGUAGE plpython3u $$ +for row in plpy.cursor("SELECT * FROM test2 ORDER BY x"): + plpy.execute("INSERT INTO test1 (a) VALUES (%s)" % row['x']) + plpy.rollback() +$$; +SELECT * FROM test1; + a | b +---+--- +(0 rows) + +SELECT * FROM pg_cursors; + name | statement | is_holdable | is_binary | is_scrollable | creation_time +------+-----------+-------------+-----------+---------------+--------------- +(0 rows) + +-- first commit then rollback inside cursor loop +TRUNCATE test1; +DO LANGUAGE plpython3u $$ +for row in plpy.cursor("SELECT * FROM test2 ORDER BY x"): + plpy.execute("INSERT INTO test1 (a) VALUES (%s)" % row['x']) + if row['x'] % 2 == 0: + plpy.commit() + else: + plpy.rollback() +$$; +SELECT * FROM test1; + a | b +---+--- + 0 | + 2 | + 4 | +(3 rows) + +SELECT * FROM pg_cursors; + name | statement | is_holdable | is_binary | is_scrollable | creation_time +------+-----------+-------------+-----------+---------------+--------------- +(0 rows) + +-- check handling of an error during COMMIT +CREATE TABLE testpk (id int PRIMARY KEY); +CREATE TABLE testfk(f1 int REFERENCES testpk DEFERRABLE INITIALLY DEFERRED); +DO LANGUAGE plpython3u $$ +# this insert will fail during commit: +plpy.execute("INSERT INTO testfk VALUES (0)") +plpy.commit() +plpy.warning('should not get here') +$$; +ERROR: spiexceptions.ForeignKeyViolation: insert or update on table "testfk" violates foreign key constraint "testfk_f1_fkey" +DETAIL: Key (f1)=(0) is not present in table "testpk". +CONTEXT: Traceback (most recent call last): + PL/Python anonymous code block, line 4, in <module> + plpy.commit() +PL/Python anonymous code block +SELECT * FROM testpk; + id +---- +(0 rows) + +SELECT * FROM testfk; + f1 +---- +(0 rows) + +DO LANGUAGE plpython3u $$ +# this insert will fail during commit: +plpy.execute("INSERT INTO testfk VALUES (0)") +try: + plpy.commit() +except Exception as e: + plpy.info('sqlstate: %s' % (e.sqlstate)) +# these inserts should work: +plpy.execute("INSERT INTO testpk VALUES (1)") +plpy.execute("INSERT INTO testfk VALUES (1)") +$$; +INFO: sqlstate: 23503 +SELECT * FROM testpk; + id +---- + 1 +(1 row) + +SELECT * FROM testfk; + f1 +---- + 1 +(1 row) + +DROP TABLE test1; +DROP TABLE test2; diff --git a/src/pl/plpython/expected/plpython_trigger.out b/src/pl/plpython/expected/plpython_trigger.out new file mode 100644 index 0000000..dd1ca32 --- /dev/null +++ b/src/pl/plpython/expected/plpython_trigger.out @@ -0,0 +1,620 @@ +-- these triggers are dedicated to HPHC of RI who +-- decided that my kid's name was william not willem, and +-- vigorously resisted all efforts at correction. they have +-- since gone bankrupt... +CREATE FUNCTION users_insert() returns trigger + AS +'if TD["new"]["fname"] == None or TD["new"]["lname"] == None: + return "SKIP" +if TD["new"]["username"] == None: + TD["new"]["username"] = TD["new"]["fname"][:1] + "_" + TD["new"]["lname"] + rv = "MODIFY" +else: + rv = None +if TD["new"]["fname"] == "william": + TD["new"]["fname"] = TD["args"][0] + rv = "MODIFY" +return rv' + LANGUAGE plpython3u; +CREATE FUNCTION users_update() returns trigger + AS +'if TD["event"] == "UPDATE": + if TD["old"]["fname"] != TD["new"]["fname"] and TD["old"]["fname"] == TD["args"][0]: + return "SKIP" +return None' + LANGUAGE plpython3u; +CREATE FUNCTION users_delete() RETURNS trigger + AS +'if TD["old"]["fname"] == TD["args"][0]: + return "SKIP" +return None' + LANGUAGE plpython3u; +CREATE TRIGGER users_insert_trig BEFORE INSERT ON users FOR EACH ROW + EXECUTE PROCEDURE users_insert ('willem'); +CREATE TRIGGER users_update_trig BEFORE UPDATE ON users FOR EACH ROW + EXECUTE PROCEDURE users_update ('willem'); +CREATE TRIGGER users_delete_trig BEFORE DELETE ON users FOR EACH ROW + EXECUTE PROCEDURE users_delete ('willem'); +-- quick peek at the table +-- +SELECT * FROM users; + fname | lname | username | userid +--------+-------+----------+-------- + jane | doe | j_doe | 1 + john | doe | johnd | 2 + willem | doe | w_doe | 3 + rick | smith | slash | 4 +(4 rows) + +-- should fail +-- +UPDATE users SET fname = 'william' WHERE fname = 'willem'; +-- should modify william to willem and create username +-- +INSERT INTO users (fname, lname) VALUES ('william', 'smith'); +INSERT INTO users (fname, lname, username) VALUES ('charles', 'darwin', 'beagle'); +SELECT * FROM users; + fname | lname | username | userid +---------+--------+----------+-------- + jane | doe | j_doe | 1 + john | doe | johnd | 2 + willem | doe | w_doe | 3 + rick | smith | slash | 4 + willem | smith | w_smith | 5 + charles | darwin | beagle | 6 +(6 rows) + +-- dump trigger data +CREATE TABLE trigger_test + (i int, v text ); +CREATE TABLE trigger_test_generated ( + i int, + j int GENERATED ALWAYS AS (i * 2) STORED +); +CREATE FUNCTION trigger_data() RETURNS trigger LANGUAGE plpython3u AS $$ + +if 'relid' in TD: + TD['relid'] = "bogus:12345" + +skeys = list(TD.keys()) +skeys.sort() +for key in skeys: + val = TD[key] + if not isinstance(val, dict): + plpy.notice("TD[" + key + "] => " + str(val)) + else: + # print dicts the hard way because otherwise the order is implementation-dependent + valkeys = list(val.keys()) + valkeys.sort() + plpy.notice("TD[" + key + "] => " + '{' + ', '.join([repr(k) + ': ' + repr(val[k]) for k in valkeys]) + '}') + +return None + +$$; +CREATE TRIGGER show_trigger_data_trig_before +BEFORE INSERT OR UPDATE OR DELETE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); +CREATE TRIGGER show_trigger_data_trig_after +AFTER INSERT OR UPDATE OR DELETE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); +CREATE TRIGGER show_trigger_data_trig_stmt +BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON trigger_test +FOR EACH STATEMENT EXECUTE PROCEDURE trigger_data(23,'skidoo'); +insert into trigger_test values(1,'insert'); +NOTICE: TD[args] => ['23', 'skidoo'] +NOTICE: TD[event] => INSERT +NOTICE: TD[level] => STATEMENT +NOTICE: TD[name] => show_trigger_data_trig_stmt +NOTICE: TD[new] => None +NOTICE: TD[old] => None +NOTICE: TD[relid] => bogus:12345 +NOTICE: TD[table_name] => trigger_test +NOTICE: TD[table_schema] => public +NOTICE: TD[when] => BEFORE +NOTICE: TD[args] => ['23', 'skidoo'] +NOTICE: TD[event] => INSERT +NOTICE: TD[level] => ROW +NOTICE: TD[name] => show_trigger_data_trig_before +NOTICE: TD[new] => {'i': 1, 'v': 'insert'} +NOTICE: TD[old] => None +NOTICE: TD[relid] => bogus:12345 +NOTICE: TD[table_name] => trigger_test +NOTICE: TD[table_schema] => public +NOTICE: TD[when] => BEFORE +NOTICE: TD[args] => ['23', 'skidoo'] +NOTICE: TD[event] => INSERT +NOTICE: TD[level] => ROW +NOTICE: TD[name] => show_trigger_data_trig_after +NOTICE: TD[new] => {'i': 1, 'v': 'insert'} +NOTICE: TD[old] => None +NOTICE: TD[relid] => bogus:12345 +NOTICE: TD[table_name] => trigger_test +NOTICE: TD[table_schema] => public +NOTICE: TD[when] => AFTER +update trigger_test set v = 'update' where i = 1; +NOTICE: TD[args] => ['23', 'skidoo'] +NOTICE: TD[event] => UPDATE +NOTICE: TD[level] => STATEMENT +NOTICE: TD[name] => show_trigger_data_trig_stmt +NOTICE: TD[new] => None +NOTICE: TD[old] => None +NOTICE: TD[relid] => bogus:12345 +NOTICE: TD[table_name] => trigger_test +NOTICE: TD[table_schema] => public +NOTICE: TD[when] => BEFORE +NOTICE: TD[args] => ['23', 'skidoo'] +NOTICE: TD[event] => UPDATE +NOTICE: TD[level] => ROW +NOTICE: TD[name] => show_trigger_data_trig_before +NOTICE: TD[new] => {'i': 1, 'v': 'update'} +NOTICE: TD[old] => {'i': 1, 'v': 'insert'} +NOTICE: TD[relid] => bogus:12345 +NOTICE: TD[table_name] => trigger_test +NOTICE: TD[table_schema] => public +NOTICE: TD[when] => BEFORE +NOTICE: TD[args] => ['23', 'skidoo'] +NOTICE: TD[event] => UPDATE +NOTICE: TD[level] => ROW +NOTICE: TD[name] => show_trigger_data_trig_after +NOTICE: TD[new] => {'i': 1, 'v': 'update'} +NOTICE: TD[old] => {'i': 1, 'v': 'insert'} +NOTICE: TD[relid] => bogus:12345 +NOTICE: TD[table_name] => trigger_test +NOTICE: TD[table_schema] => public +NOTICE: TD[when] => AFTER +delete from trigger_test; +NOTICE: TD[args] => ['23', 'skidoo'] +NOTICE: TD[event] => DELETE +NOTICE: TD[level] => STATEMENT +NOTICE: TD[name] => show_trigger_data_trig_stmt +NOTICE: TD[new] => None +NOTICE: TD[old] => None +NOTICE: TD[relid] => bogus:12345 +NOTICE: TD[table_name] => trigger_test +NOTICE: TD[table_schema] => public +NOTICE: TD[when] => BEFORE +NOTICE: TD[args] => ['23', 'skidoo'] +NOTICE: TD[event] => DELETE +NOTICE: TD[level] => ROW +NOTICE: TD[name] => show_trigger_data_trig_before +NOTICE: TD[new] => None +NOTICE: TD[old] => {'i': 1, 'v': 'update'} +NOTICE: TD[relid] => bogus:12345 +NOTICE: TD[table_name] => trigger_test +NOTICE: TD[table_schema] => public +NOTICE: TD[when] => BEFORE +NOTICE: TD[args] => ['23', 'skidoo'] +NOTICE: TD[event] => DELETE +NOTICE: TD[level] => ROW +NOTICE: TD[name] => show_trigger_data_trig_after +NOTICE: TD[new] => None +NOTICE: TD[old] => {'i': 1, 'v': 'update'} +NOTICE: TD[relid] => bogus:12345 +NOTICE: TD[table_name] => trigger_test +NOTICE: TD[table_schema] => public +NOTICE: TD[when] => AFTER +truncate table trigger_test; +NOTICE: TD[args] => ['23', 'skidoo'] +NOTICE: TD[event] => TRUNCATE +NOTICE: TD[level] => STATEMENT +NOTICE: TD[name] => show_trigger_data_trig_stmt +NOTICE: TD[new] => None +NOTICE: TD[old] => None +NOTICE: TD[relid] => bogus:12345 +NOTICE: TD[table_name] => trigger_test +NOTICE: TD[table_schema] => public +NOTICE: TD[when] => BEFORE +DROP TRIGGER show_trigger_data_trig_stmt on trigger_test; +DROP TRIGGER show_trigger_data_trig_before on trigger_test; +DROP TRIGGER show_trigger_data_trig_after on trigger_test; +CREATE TRIGGER show_trigger_data_trig_before +BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_generated +FOR EACH ROW EXECUTE PROCEDURE trigger_data(); +CREATE TRIGGER show_trigger_data_trig_after +AFTER INSERT OR UPDATE OR DELETE ON trigger_test_generated +FOR EACH ROW EXECUTE PROCEDURE trigger_data(); +insert into trigger_test_generated (i) values (1); +NOTICE: TD[args] => None +NOTICE: TD[event] => INSERT +NOTICE: TD[level] => ROW +NOTICE: TD[name] => show_trigger_data_trig_before +NOTICE: TD[new] => {'i': 1} +NOTICE: TD[old] => None +NOTICE: TD[relid] => bogus:12345 +NOTICE: TD[table_name] => trigger_test_generated +NOTICE: TD[table_schema] => public +NOTICE: TD[when] => BEFORE +NOTICE: TD[args] => None +NOTICE: TD[event] => INSERT +NOTICE: TD[level] => ROW +NOTICE: TD[name] => show_trigger_data_trig_after +NOTICE: TD[new] => {'i': 1, 'j': 2} +NOTICE: TD[old] => None +NOTICE: TD[relid] => bogus:12345 +NOTICE: TD[table_name] => trigger_test_generated +NOTICE: TD[table_schema] => public +NOTICE: TD[when] => AFTER +update trigger_test_generated set i = 11 where i = 1; +NOTICE: TD[args] => None +NOTICE: TD[event] => UPDATE +NOTICE: TD[level] => ROW +NOTICE: TD[name] => show_trigger_data_trig_before +NOTICE: TD[new] => {'i': 11} +NOTICE: TD[old] => {'i': 1, 'j': 2} +NOTICE: TD[relid] => bogus:12345 +NOTICE: TD[table_name] => trigger_test_generated +NOTICE: TD[table_schema] => public +NOTICE: TD[when] => BEFORE +NOTICE: TD[args] => None +NOTICE: TD[event] => UPDATE +NOTICE: TD[level] => ROW +NOTICE: TD[name] => show_trigger_data_trig_after +NOTICE: TD[new] => {'i': 11, 'j': 22} +NOTICE: TD[old] => {'i': 1, 'j': 2} +NOTICE: TD[relid] => bogus:12345 +NOTICE: TD[table_name] => trigger_test_generated +NOTICE: TD[table_schema] => public +NOTICE: TD[when] => AFTER +delete from trigger_test_generated; +NOTICE: TD[args] => None +NOTICE: TD[event] => DELETE +NOTICE: TD[level] => ROW +NOTICE: TD[name] => show_trigger_data_trig_before +NOTICE: TD[new] => None +NOTICE: TD[old] => {'i': 11, 'j': 22} +NOTICE: TD[relid] => bogus:12345 +NOTICE: TD[table_name] => trigger_test_generated +NOTICE: TD[table_schema] => public +NOTICE: TD[when] => BEFORE +NOTICE: TD[args] => None +NOTICE: TD[event] => DELETE +NOTICE: TD[level] => ROW +NOTICE: TD[name] => show_trigger_data_trig_after +NOTICE: TD[new] => None +NOTICE: TD[old] => {'i': 11, 'j': 22} +NOTICE: TD[relid] => bogus:12345 +NOTICE: TD[table_name] => trigger_test_generated +NOTICE: TD[table_schema] => public +NOTICE: TD[when] => AFTER +DROP TRIGGER show_trigger_data_trig_before ON trigger_test_generated; +DROP TRIGGER show_trigger_data_trig_after ON trigger_test_generated; +insert into trigger_test values(1,'insert'); +CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test; +CREATE TRIGGER show_trigger_data_trig +INSTEAD OF INSERT OR UPDATE OR DELETE ON trigger_test_view +FOR EACH ROW EXECUTE PROCEDURE trigger_data(24,'skidoo view'); +insert into trigger_test_view values(2,'insert'); +NOTICE: TD[args] => ['24', 'skidoo view'] +NOTICE: TD[event] => INSERT +NOTICE: TD[level] => ROW +NOTICE: TD[name] => show_trigger_data_trig +NOTICE: TD[new] => {'i': 2, 'v': 'insert'} +NOTICE: TD[old] => None +NOTICE: TD[relid] => bogus:12345 +NOTICE: TD[table_name] => trigger_test_view +NOTICE: TD[table_schema] => public +NOTICE: TD[when] => INSTEAD OF +update trigger_test_view set v = 'update' where i = 1; +NOTICE: TD[args] => ['24', 'skidoo view'] +NOTICE: TD[event] => UPDATE +NOTICE: TD[level] => ROW +NOTICE: TD[name] => show_trigger_data_trig +NOTICE: TD[new] => {'i': 1, 'v': 'update'} +NOTICE: TD[old] => {'i': 1, 'v': 'insert'} +NOTICE: TD[relid] => bogus:12345 +NOTICE: TD[table_name] => trigger_test_view +NOTICE: TD[table_schema] => public +NOTICE: TD[when] => INSTEAD OF +delete from trigger_test_view; +NOTICE: TD[args] => ['24', 'skidoo view'] +NOTICE: TD[event] => DELETE +NOTICE: TD[level] => ROW +NOTICE: TD[name] => show_trigger_data_trig +NOTICE: TD[new] => None +NOTICE: TD[old] => {'i': 1, 'v': 'insert'} +NOTICE: TD[relid] => bogus:12345 +NOTICE: TD[table_name] => trigger_test_view +NOTICE: TD[table_schema] => public +NOTICE: TD[when] => INSTEAD OF +DROP FUNCTION trigger_data() CASCADE; +NOTICE: drop cascades to trigger show_trigger_data_trig on view trigger_test_view +DROP VIEW trigger_test_view; +delete from trigger_test; +-- +-- trigger error handling +-- +INSERT INTO trigger_test VALUES (0, 'zero'); +-- returning non-string from trigger function +CREATE FUNCTION stupid1() RETURNS trigger +AS $$ + return 37 +$$ LANGUAGE plpython3u; +CREATE TRIGGER stupid_trigger1 +BEFORE INSERT ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE stupid1(); +INSERT INTO trigger_test VALUES (1, 'one'); +ERROR: unexpected return value from trigger procedure +DETAIL: Expected None or a string. +CONTEXT: PL/Python function "stupid1" +DROP TRIGGER stupid_trigger1 ON trigger_test; +-- returning MODIFY from DELETE trigger +CREATE FUNCTION stupid2() RETURNS trigger +AS $$ + return "MODIFY" +$$ LANGUAGE plpython3u; +CREATE TRIGGER stupid_trigger2 +BEFORE DELETE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE stupid2(); +DELETE FROM trigger_test WHERE i = 0; +WARNING: PL/Python trigger function returned "MODIFY" in a DELETE trigger -- ignored +DROP TRIGGER stupid_trigger2 ON trigger_test; +INSERT INTO trigger_test VALUES (0, 'zero'); +-- returning unrecognized string from trigger function +CREATE FUNCTION stupid3() RETURNS trigger +AS $$ + return "foo" +$$ LANGUAGE plpython3u; +CREATE TRIGGER stupid_trigger3 +BEFORE UPDATE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE stupid3(); +UPDATE trigger_test SET v = 'null' WHERE i = 0; +ERROR: unexpected return value from trigger procedure +DETAIL: Expected None, "OK", "SKIP", or "MODIFY". +CONTEXT: PL/Python function "stupid3" +DROP TRIGGER stupid_trigger3 ON trigger_test; +-- Unicode variant +CREATE FUNCTION stupid3u() RETURNS trigger +AS $$ + return "foo" +$$ LANGUAGE plpython3u; +CREATE TRIGGER stupid_trigger3 +BEFORE UPDATE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE stupid3u(); +UPDATE trigger_test SET v = 'null' WHERE i = 0; +ERROR: unexpected return value from trigger procedure +DETAIL: Expected None, "OK", "SKIP", or "MODIFY". +CONTEXT: PL/Python function "stupid3u" +DROP TRIGGER stupid_trigger3 ON trigger_test; +-- deleting the TD dictionary +CREATE FUNCTION stupid4() RETURNS trigger +AS $$ + del TD["new"] + return "MODIFY"; +$$ LANGUAGE plpython3u; +CREATE TRIGGER stupid_trigger4 +BEFORE UPDATE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE stupid4(); +UPDATE trigger_test SET v = 'null' WHERE i = 0; +ERROR: TD["new"] deleted, cannot modify row +CONTEXT: while modifying trigger row +PL/Python function "stupid4" +DROP TRIGGER stupid_trigger4 ON trigger_test; +-- TD not a dictionary +CREATE FUNCTION stupid5() RETURNS trigger +AS $$ + TD["new"] = ['foo', 'bar'] + return "MODIFY"; +$$ LANGUAGE plpython3u; +CREATE TRIGGER stupid_trigger5 +BEFORE UPDATE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE stupid5(); +UPDATE trigger_test SET v = 'null' WHERE i = 0; +ERROR: TD["new"] is not a dictionary +CONTEXT: while modifying trigger row +PL/Python function "stupid5" +DROP TRIGGER stupid_trigger5 ON trigger_test; +-- TD not having string keys +CREATE FUNCTION stupid6() RETURNS trigger +AS $$ + TD["new"] = {1: 'foo', 2: 'bar'} + return "MODIFY"; +$$ LANGUAGE plpython3u; +CREATE TRIGGER stupid_trigger6 +BEFORE UPDATE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE stupid6(); +UPDATE trigger_test SET v = 'null' WHERE i = 0; +ERROR: TD["new"] dictionary key at ordinal position 0 is not a string +CONTEXT: while modifying trigger row +PL/Python function "stupid6" +DROP TRIGGER stupid_trigger6 ON trigger_test; +-- TD keys not corresponding to row columns +CREATE FUNCTION stupid7() RETURNS trigger +AS $$ + TD["new"] = {'v': 'foo', 'a': 'bar'} + return "MODIFY"; +$$ LANGUAGE plpython3u; +CREATE TRIGGER stupid_trigger7 +BEFORE UPDATE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE stupid7(); +UPDATE trigger_test SET v = 'null' WHERE i = 0; +ERROR: key "a" found in TD["new"] does not exist as a column in the triggering row +CONTEXT: while modifying trigger row +PL/Python function "stupid7" +DROP TRIGGER stupid_trigger7 ON trigger_test; +-- Unicode variant +CREATE FUNCTION stupid7u() RETURNS trigger +AS $$ + TD["new"] = {'v': 'foo', 'a': 'bar'} + return "MODIFY" +$$ LANGUAGE plpython3u; +CREATE TRIGGER stupid_trigger7 +BEFORE UPDATE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE stupid7u(); +UPDATE trigger_test SET v = 'null' WHERE i = 0; +ERROR: key "a" found in TD["new"] does not exist as a column in the triggering row +CONTEXT: while modifying trigger row +PL/Python function "stupid7u" +DROP TRIGGER stupid_trigger7 ON trigger_test; +-- calling a trigger function directly +SELECT stupid7(); +ERROR: trigger functions can only be called as triggers +-- +-- Null values +-- +SELECT * FROM trigger_test; + i | v +---+------ + 0 | zero +(1 row) + +CREATE FUNCTION test_null() RETURNS trigger +AS $$ + TD["new"]['v'] = None + return "MODIFY" +$$ LANGUAGE plpython3u; +CREATE TRIGGER test_null_trigger +BEFORE UPDATE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE test_null(); +UPDATE trigger_test SET v = 'null' WHERE i = 0; +DROP TRIGGER test_null_trigger ON trigger_test; +SELECT * FROM trigger_test; + i | v +---+--- + 0 | +(1 row) + +-- +-- Test that triggers honor typmod when assigning to tuple fields, +-- as per an early 9.0 bug report +-- +SET DateStyle = 'ISO'; +CREATE FUNCTION set_modif_time() RETURNS trigger AS $$ + TD['new']['modif_time'] = '2010-10-13 21:57:28.930486' + return 'MODIFY' +$$ LANGUAGE plpython3u; +CREATE TABLE pb (a TEXT, modif_time TIMESTAMP(0) WITHOUT TIME ZONE); +CREATE TRIGGER set_modif_time BEFORE UPDATE ON pb + FOR EACH ROW EXECUTE PROCEDURE set_modif_time(); +INSERT INTO pb VALUES ('a', '2010-10-09 21:57:33.930486'); +SELECT * FROM pb; + a | modif_time +---+--------------------- + a | 2010-10-09 21:57:34 +(1 row) + +UPDATE pb SET a = 'b'; +SELECT * FROM pb; + a | modif_time +---+--------------------- + 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 plpython3u; +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) + +-- triggers with composite type columns (bug #6559) +CREATE TABLE composite_trigger_noop_test (f1 comp1, f2 comp2); +CREATE FUNCTION composite_trigger_noop_f() RETURNS trigger AS $$ + return 'MODIFY' +$$ LANGUAGE plpython3u; +CREATE TRIGGER composite_trigger_noop BEFORE INSERT ON composite_trigger_noop_test + FOR EACH ROW EXECUTE PROCEDURE composite_trigger_noop_f(); +INSERT INTO composite_trigger_noop_test VALUES (NULL, NULL); +INSERT INTO composite_trigger_noop_test VALUES (ROW(1, 'f'), NULL); +INSERT INTO composite_trigger_noop_test VALUES (ROW(NULL, 't'), ROW(1, 'f')); +SELECT * FROM composite_trigger_noop_test; + f1 | f2 +-------+------- + | + (1,f) | + (,t) | (1,f) +(3 rows) + +-- nested composite types +CREATE TYPE comp3 AS (c1 comp1, c2 comp2, m integer); +CREATE TABLE composite_trigger_nested_test(c comp3); +CREATE FUNCTION composite_trigger_nested_f() RETURNS trigger AS $$ + return 'MODIFY' +$$ LANGUAGE plpython3u; +CREATE TRIGGER composite_trigger_nested BEFORE INSERT ON composite_trigger_nested_test + FOR EACH ROW EXECUTE PROCEDURE composite_trigger_nested_f(); +INSERT INTO composite_trigger_nested_test VALUES (NULL); +INSERT INTO composite_trigger_nested_test VALUES (ROW(ROW(1, 'f'), NULL, 3)); +INSERT INTO composite_trigger_nested_test VALUES (ROW(ROW(NULL, 't'), ROW(1, 'f'), NULL)); +SELECT * FROM composite_trigger_nested_test; + c +------------------- + + ("(1,f)",,3) + ("(,t)","(1,f)",) +(3 rows) + +-- check that using a function as a trigger over two tables works correctly +CREATE FUNCTION trig1234() RETURNS trigger LANGUAGE plpython3u AS $$ + TD["new"]["data"] = '1234' + return 'MODIFY' +$$; +CREATE TABLE a(data text); +CREATE TABLE b(data int); -- different type conversion +CREATE TRIGGER a_t BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE trig1234(); +CREATE TRIGGER b_t BEFORE INSERT ON b FOR EACH ROW EXECUTE PROCEDURE trig1234(); +INSERT INTO a DEFAULT VALUES; +SELECT * FROM a; + data +------ + 1234 +(1 row) + +DROP TABLE a; +INSERT INTO b DEFAULT VALUES; +SELECT * FROM b; + data +------ + 1234 +(1 row) + +-- check that SQL run in trigger code can see transition tables +CREATE TABLE transition_table_test (id int, name text); +INSERT INTO transition_table_test VALUES (1, 'a'); +CREATE FUNCTION transition_table_test_f() RETURNS trigger LANGUAGE plpython3u AS +$$ + rv = plpy.execute("SELECT * FROM old_table") + assert(rv.nrows() == 1) + plpy.info("old: " + str(rv[0]["id"]) + " -> " + rv[0]["name"]) + rv = plpy.execute("SELECT * FROM new_table") + assert(rv.nrows() == 1) + plpy.info("new: " + str(rv[0]["id"]) + " -> " + rv[0]["name"]) + return None +$$; +CREATE TRIGGER a_t AFTER UPDATE ON transition_table_test + REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table + FOR EACH STATEMENT EXECUTE PROCEDURE transition_table_test_f(); +UPDATE transition_table_test SET name = 'b'; +INFO: old: 1 -> a +INFO: new: 1 -> b +DROP TABLE transition_table_test; +DROP FUNCTION transition_table_test_f(); +-- dealing with generated columns +CREATE FUNCTION generated_test_func1() RETURNS trigger +LANGUAGE plpython3u +AS $$ +TD['new']['j'] = 5 # not allowed +return 'MODIFY' +$$; +CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_generated +FOR EACH ROW EXECUTE PROCEDURE generated_test_func1(); +TRUNCATE trigger_test_generated; +INSERT INTO trigger_test_generated (i) VALUES (1); +ERROR: cannot set generated column "j" +CONTEXT: while modifying trigger row +PL/Python function "generated_test_func1" +SELECT * FROM trigger_test_generated; + i | j +---+--- +(0 rows) + diff --git a/src/pl/plpython/expected/plpython_types.out b/src/pl/plpython/expected/plpython_types.out new file mode 100644 index 0000000..8a680e1 --- /dev/null +++ b/src/pl/plpython/expected/plpython_types.out @@ -0,0 +1,1069 @@ +-- +-- Test data type behavior +-- +-- +-- Base/common types +-- +CREATE FUNCTION test_type_conversion_bool(x bool) RETURNS bool AS $$ +plpy.info(x, type(x)) +return x +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_bool(true); +INFO: (True, <class 'bool'>) + test_type_conversion_bool +--------------------------- + t +(1 row) + +SELECT * FROM test_type_conversion_bool(false); +INFO: (False, <class 'bool'>) + test_type_conversion_bool +--------------------------- + f +(1 row) + +SELECT * FROM test_type_conversion_bool(null); +INFO: (None, <class 'NoneType'>) + test_type_conversion_bool +--------------------------- + +(1 row) + +-- test various other ways to express Booleans in Python +CREATE FUNCTION test_type_conversion_bool_other(n int) RETURNS bool AS $$ +# numbers +if n == 0: + ret = 0 +elif n == 1: + ret = 5 +# strings +elif n == 2: + ret = '' +elif n == 3: + ret = 'fa' # true in Python, false in PostgreSQL +# containers +elif n == 4: + ret = [] +elif n == 5: + ret = [0] +plpy.info(ret, not not ret) +return ret +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_bool_other(0); +INFO: (0, False) + test_type_conversion_bool_other +--------------------------------- + f +(1 row) + +SELECT * FROM test_type_conversion_bool_other(1); +INFO: (5, True) + test_type_conversion_bool_other +--------------------------------- + t +(1 row) + +SELECT * FROM test_type_conversion_bool_other(2); +INFO: ('', False) + test_type_conversion_bool_other +--------------------------------- + f +(1 row) + +SELECT * FROM test_type_conversion_bool_other(3); +INFO: ('fa', True) + test_type_conversion_bool_other +--------------------------------- + t +(1 row) + +SELECT * FROM test_type_conversion_bool_other(4); +INFO: ([], False) + test_type_conversion_bool_other +--------------------------------- + f +(1 row) + +SELECT * FROM test_type_conversion_bool_other(5); +INFO: ([0], True) + test_type_conversion_bool_other +--------------------------------- + t +(1 row) + +CREATE FUNCTION test_type_conversion_char(x char) RETURNS char AS $$ +plpy.info(x, type(x)) +return x +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_char('a'); +INFO: ('a', <class 'str'>) + test_type_conversion_char +--------------------------- + a +(1 row) + +SELECT * FROM test_type_conversion_char(null); +INFO: (None, <class 'NoneType'>) + test_type_conversion_char +--------------------------- + +(1 row) + +CREATE FUNCTION test_type_conversion_int2(x int2) RETURNS int2 AS $$ +plpy.info(x, type(x)) +return x +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_int2(100::int2); +INFO: (100, <class 'int'>) + test_type_conversion_int2 +--------------------------- + 100 +(1 row) + +SELECT * FROM test_type_conversion_int2(-100::int2); +INFO: (-100, <class 'int'>) + test_type_conversion_int2 +--------------------------- + -100 +(1 row) + +SELECT * FROM test_type_conversion_int2(null); +INFO: (None, <class 'NoneType'>) + test_type_conversion_int2 +--------------------------- + +(1 row) + +CREATE FUNCTION test_type_conversion_int4(x int4) RETURNS int4 AS $$ +plpy.info(x, type(x)) +return x +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_int4(100); +INFO: (100, <class 'int'>) + test_type_conversion_int4 +--------------------------- + 100 +(1 row) + +SELECT * FROM test_type_conversion_int4(-100); +INFO: (-100, <class 'int'>) + test_type_conversion_int4 +--------------------------- + -100 +(1 row) + +SELECT * FROM test_type_conversion_int4(null); +INFO: (None, <class 'NoneType'>) + test_type_conversion_int4 +--------------------------- + +(1 row) + +CREATE FUNCTION test_type_conversion_int8(x int8) RETURNS int8 AS $$ +plpy.info(x, type(x)) +return x +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_int8(100); +INFO: (100, <class 'int'>) + test_type_conversion_int8 +--------------------------- + 100 +(1 row) + +SELECT * FROM test_type_conversion_int8(-100); +INFO: (-100, <class 'int'>) + test_type_conversion_int8 +--------------------------- + -100 +(1 row) + +SELECT * FROM test_type_conversion_int8(5000000000); +INFO: (5000000000, <class 'int'>) + test_type_conversion_int8 +--------------------------- + 5000000000 +(1 row) + +SELECT * FROM test_type_conversion_int8(null); +INFO: (None, <class 'NoneType'>) + test_type_conversion_int8 +--------------------------- + +(1 row) + +CREATE FUNCTION test_type_conversion_numeric(x numeric) RETURNS numeric AS $$ +# print just the class name, not the type, to avoid differences +# between decimal and cdecimal +plpy.info(str(x), x.__class__.__name__) +return x +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_numeric(100); +INFO: ('100', 'Decimal') + test_type_conversion_numeric +------------------------------ + 100 +(1 row) + +SELECT * FROM test_type_conversion_numeric(-100); +INFO: ('-100', 'Decimal') + test_type_conversion_numeric +------------------------------ + -100 +(1 row) + +SELECT * FROM test_type_conversion_numeric(100.0); +INFO: ('100.0', 'Decimal') + test_type_conversion_numeric +------------------------------ + 100.0 +(1 row) + +SELECT * FROM test_type_conversion_numeric(100.00); +INFO: ('100.00', 'Decimal') + test_type_conversion_numeric +------------------------------ + 100.00 +(1 row) + +SELECT * FROM test_type_conversion_numeric(5000000000.5); +INFO: ('5000000000.5', 'Decimal') + test_type_conversion_numeric +------------------------------ + 5000000000.5 +(1 row) + +SELECT * FROM test_type_conversion_numeric(1234567890.0987654321); +INFO: ('1234567890.0987654321', 'Decimal') + test_type_conversion_numeric +------------------------------ + 1234567890.0987654321 +(1 row) + +SELECT * FROM test_type_conversion_numeric(-1234567890.0987654321); +INFO: ('-1234567890.0987654321', 'Decimal') + test_type_conversion_numeric +------------------------------ + -1234567890.0987654321 +(1 row) + +SELECT * FROM test_type_conversion_numeric(null); +INFO: ('None', 'NoneType') + test_type_conversion_numeric +------------------------------ + +(1 row) + +CREATE FUNCTION test_type_conversion_float4(x float4) RETURNS float4 AS $$ +plpy.info(x, type(x)) +return x +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_float4(100); +INFO: (100.0, <class 'float'>) + test_type_conversion_float4 +----------------------------- + 100 +(1 row) + +SELECT * FROM test_type_conversion_float4(-100); +INFO: (-100.0, <class 'float'>) + test_type_conversion_float4 +----------------------------- + -100 +(1 row) + +SELECT * FROM test_type_conversion_float4(5000.5); +INFO: (5000.5, <class 'float'>) + test_type_conversion_float4 +----------------------------- + 5000.5 +(1 row) + +SELECT * FROM test_type_conversion_float4(null); +INFO: (None, <class 'NoneType'>) + test_type_conversion_float4 +----------------------------- + +(1 row) + +CREATE FUNCTION test_type_conversion_float8(x float8) RETURNS float8 AS $$ +plpy.info(x, type(x)) +return x +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_float8(100); +INFO: (100.0, <class 'float'>) + test_type_conversion_float8 +----------------------------- + 100 +(1 row) + +SELECT * FROM test_type_conversion_float8(-100); +INFO: (-100.0, <class 'float'>) + test_type_conversion_float8 +----------------------------- + -100 +(1 row) + +SELECT * FROM test_type_conversion_float8(5000000000.5); +INFO: (5000000000.5, <class 'float'>) + test_type_conversion_float8 +----------------------------- + 5000000000.5 +(1 row) + +SELECT * FROM test_type_conversion_float8(null); +INFO: (None, <class 'NoneType'>) + test_type_conversion_float8 +----------------------------- + +(1 row) + +SELECT * FROM test_type_conversion_float8(100100100.654321); +INFO: (100100100.654321, <class 'float'>) + test_type_conversion_float8 +----------------------------- + 100100100.654321 +(1 row) + +CREATE FUNCTION test_type_conversion_oid(x oid) RETURNS oid AS $$ +plpy.info(x, type(x)) +return x +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_oid(100); +INFO: (100, <class 'int'>) + test_type_conversion_oid +-------------------------- + 100 +(1 row) + +SELECT * FROM test_type_conversion_oid(2147483649); +INFO: (2147483649, <class 'int'>) + test_type_conversion_oid +-------------------------- + 2147483649 +(1 row) + +SELECT * FROM test_type_conversion_oid(null); +INFO: (None, <class 'NoneType'>) + test_type_conversion_oid +-------------------------- + +(1 row) + +CREATE FUNCTION test_type_conversion_text(x text) RETURNS text AS $$ +plpy.info(x, type(x)) +return x +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_text('hello world'); +INFO: ('hello world', <class 'str'>) + test_type_conversion_text +--------------------------- + hello world +(1 row) + +SELECT * FROM test_type_conversion_text(null); +INFO: (None, <class 'NoneType'>) + test_type_conversion_text +--------------------------- + +(1 row) + +CREATE FUNCTION test_type_conversion_bytea(x bytea) RETURNS bytea AS $$ +plpy.info(x, type(x)) +return x +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_bytea('hello world'); +INFO: (b'hello world', <class 'bytes'>) + test_type_conversion_bytea +---------------------------- + \x68656c6c6f20776f726c64 +(1 row) + +SELECT * FROM test_type_conversion_bytea(E'null\\000byte'); +INFO: (b'null\x00byte', <class 'bytes'>) + test_type_conversion_bytea +---------------------------- + \x6e756c6c0062797465 +(1 row) + +SELECT * FROM test_type_conversion_bytea(null); +INFO: (None, <class 'NoneType'>) + test_type_conversion_bytea +---------------------------- + +(1 row) + +CREATE FUNCTION test_type_marshal() RETURNS bytea AS $$ +import marshal +return marshal.dumps('hello world') +$$ LANGUAGE plpython3u; +CREATE FUNCTION test_type_unmarshal(x bytea) RETURNS text AS $$ +import marshal +try: + return marshal.loads(x) +except ValueError as e: + return 'FAILED: ' + str(e) +$$ LANGUAGE plpython3u; +SELECT test_type_unmarshal(x) FROM test_type_marshal() x; + test_type_unmarshal +--------------------- + hello world +(1 row) + +-- +-- Domains +-- +CREATE DOMAIN booltrue AS bool CHECK (VALUE IS TRUE OR VALUE IS NULL); +CREATE FUNCTION test_type_conversion_booltrue(x booltrue, y bool) RETURNS booltrue AS $$ +return y +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_booltrue(true, true); + test_type_conversion_booltrue +------------------------------- + t +(1 row) + +SELECT * FROM test_type_conversion_booltrue(false, true); +ERROR: value for domain booltrue violates check constraint "booltrue_check" +SELECT * FROM test_type_conversion_booltrue(true, false); +ERROR: value for domain booltrue violates check constraint "booltrue_check" +CONTEXT: while creating return value +PL/Python function "test_type_conversion_booltrue" +CREATE DOMAIN uint2 AS int2 CHECK (VALUE >= 0); +CREATE FUNCTION test_type_conversion_uint2(x uint2, y int) RETURNS uint2 AS $$ +plpy.info(x, type(x)) +return y +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_uint2(100::uint2, 50); +INFO: (100, <class 'int'>) + test_type_conversion_uint2 +---------------------------- + 50 +(1 row) + +SELECT * FROM test_type_conversion_uint2(100::uint2, -50); +INFO: (100, <class 'int'>) +ERROR: value for domain uint2 violates check constraint "uint2_check" +CONTEXT: while creating return value +PL/Python function "test_type_conversion_uint2" +SELECT * FROM test_type_conversion_uint2(null, 1); +INFO: (None, <class 'NoneType'>) + test_type_conversion_uint2 +---------------------------- + 1 +(1 row) + +CREATE DOMAIN nnint AS int CHECK (VALUE IS NOT NULL); +CREATE FUNCTION test_type_conversion_nnint(x nnint, y int) RETURNS nnint AS $$ +return y +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_nnint(10, 20); + test_type_conversion_nnint +---------------------------- + 20 +(1 row) + +SELECT * FROM test_type_conversion_nnint(null, 20); +ERROR: value for domain nnint violates check constraint "nnint_check" +SELECT * FROM test_type_conversion_nnint(10, null); +ERROR: value for domain nnint violates check constraint "nnint_check" +CONTEXT: while creating return value +PL/Python function "test_type_conversion_nnint" +CREATE DOMAIN bytea10 AS bytea CHECK (octet_length(VALUE) = 10 AND VALUE IS NOT NULL); +CREATE FUNCTION test_type_conversion_bytea10(x bytea10, y bytea) RETURNS bytea10 AS $$ +plpy.info(x, type(x)) +return y +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_bytea10('hello wold', 'hello wold'); +INFO: (b'hello wold', <class 'bytes'>) + test_type_conversion_bytea10 +------------------------------ + \x68656c6c6f20776f6c64 +(1 row) + +SELECT * FROM test_type_conversion_bytea10('hello world', 'hello wold'); +ERROR: value for domain bytea10 violates check constraint "bytea10_check" +SELECT * FROM test_type_conversion_bytea10('hello word', 'hello world'); +INFO: (b'hello word', <class 'bytes'>) +ERROR: value for domain bytea10 violates check constraint "bytea10_check" +CONTEXT: while creating return value +PL/Python function "test_type_conversion_bytea10" +SELECT * FROM test_type_conversion_bytea10(null, 'hello word'); +ERROR: value for domain bytea10 violates check constraint "bytea10_check" +SELECT * FROM test_type_conversion_bytea10('hello word', null); +INFO: (b'hello word', <class 'bytes'>) +ERROR: value for domain bytea10 violates check constraint "bytea10_check" +CONTEXT: while creating return value +PL/Python function "test_type_conversion_bytea10" +-- +-- Arrays +-- +CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS int4[] AS $$ +plpy.info(x, type(x)) +return x +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_array_int4(ARRAY[0, 100]); +INFO: ([0, 100], <class 'list'>) + test_type_conversion_array_int4 +--------------------------------- + {0,100} +(1 row) + +SELECT * FROM test_type_conversion_array_int4(ARRAY[0,-100,55]); +INFO: ([0, -100, 55], <class 'list'>) + test_type_conversion_array_int4 +--------------------------------- + {0,-100,55} +(1 row) + +SELECT * FROM test_type_conversion_array_int4(ARRAY[NULL,1]); +INFO: ([None, 1], <class 'list'>) + test_type_conversion_array_int4 +--------------------------------- + {NULL,1} +(1 row) + +SELECT * FROM test_type_conversion_array_int4(ARRAY[]::integer[]); +INFO: ([], <class 'list'>) + test_type_conversion_array_int4 +--------------------------------- + {} +(1 row) + +SELECT * FROM test_type_conversion_array_int4(NULL); +INFO: (None, <class 'NoneType'>) + test_type_conversion_array_int4 +--------------------------------- + +(1 row) + +SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]); +INFO: ([[1, 2, 3], [4, 5, 6]], <class 'list'>) + test_type_conversion_array_int4 +--------------------------------- + {{1,2,3},{4,5,6}} +(1 row) + +SELECT * FROM test_type_conversion_array_int4(ARRAY[[[1,2,NULL],[NULL,5,6]],[[NULL,8,9],[10,11,12]]]); +INFO: ([[[1, 2, None], [None, 5, 6]], [[None, 8, 9], [10, 11, 12]]], <class 'list'>) + test_type_conversion_array_int4 +--------------------------------------------------- + {{{1,2,NULL},{NULL,5,6}},{{NULL,8,9},{10,11,12}}} +(1 row) + +SELECT * FROM test_type_conversion_array_int4('[2:4]={1,2,3}'); +INFO: ([1, 2, 3], <class 'list'>) + test_type_conversion_array_int4 +--------------------------------- + {1,2,3} +(1 row) + +CREATE FUNCTION test_type_conversion_array_int8(x int8[]) RETURNS int8[] AS $$ +plpy.info(x, type(x)) +return x +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_array_int8(ARRAY[[[1,2,NULL],[NULL,5,6]],[[NULL,8,9],[10,11,12]]]::int8[]); +INFO: ([[[1, 2, None], [None, 5, 6]], [[None, 8, 9], [10, 11, 12]]], <class 'list'>) + test_type_conversion_array_int8 +--------------------------------------------------- + {{{1,2,NULL},{NULL,5,6}},{{NULL,8,9},{10,11,12}}} +(1 row) + +CREATE FUNCTION test_type_conversion_array_date(x date[]) RETURNS date[] AS $$ +plpy.info(x, type(x)) +return x +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_array_date(ARRAY[[['2016-09-21','2016-09-22',NULL],[NULL,'2016-10-21','2016-10-22']], + [[NULL,'2016-11-21','2016-10-21'],['2015-09-21','2015-09-22','2014-09-21']]]::date[]); +INFO: ([[['09-21-2016', '09-22-2016', None], [None, '10-21-2016', '10-22-2016']], [[None, '11-21-2016', '10-21-2016'], ['09-21-2015', '09-22-2015', '09-21-2014']]], <class 'list'>) + test_type_conversion_array_date +--------------------------------------------------------------------------------------------------------------------------------- + {{{09-21-2016,09-22-2016,NULL},{NULL,10-21-2016,10-22-2016}},{{NULL,11-21-2016,10-21-2016},{09-21-2015,09-22-2015,09-21-2014}}} +(1 row) + +CREATE FUNCTION test_type_conversion_array_timestamp(x timestamp[]) RETURNS timestamp[] AS $$ +plpy.info(x, type(x)) +return x +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_array_timestamp(ARRAY[[['2016-09-21 15:34:24.078792-04','2016-10-22 11:34:24.078795-04',NULL], + [NULL,'2016-10-21 11:34:25.078792-04','2016-10-21 11:34:24.098792-04']], + [[NULL,'2016-01-21 11:34:24.078792-04','2016-11-21 11:34:24.108792-04'], + ['2015-09-21 11:34:24.079792-04','2014-09-21 11:34:24.078792-04','2013-09-21 11:34:24.078792-04']]]::timestamp[]); +INFO: ([[['Wed Sep 21 15:34:24.078792 2016', 'Sat Oct 22 11:34:24.078795 2016', None], [None, 'Fri Oct 21 11:34:25.078792 2016', 'Fri Oct 21 11:34:24.098792 2016']], [[None, 'Thu Jan 21 11:34:24.078792 2016', 'Mon Nov 21 11:34:24.108792 2016'], ['Mon Sep 21 11:34:24.079792 2015', 'Sun Sep 21 11:34:24.078792 2014', 'Sat Sep 21 11:34:24.078792 2013']]], <class 'list'>) + test_type_conversion_array_timestamp +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + {{{"Wed Sep 21 15:34:24.078792 2016","Sat Oct 22 11:34:24.078795 2016",NULL},{NULL,"Fri Oct 21 11:34:25.078792 2016","Fri Oct 21 11:34:24.098792 2016"}},{{NULL,"Thu Jan 21 11:34:24.078792 2016","Mon Nov 21 11:34:24.108792 2016"},{"Mon Sep 21 11:34:24.079792 2015","Sun Sep 21 11:34:24.078792 2014","Sat Sep 21 11:34:24.078792 2013"}}} +(1 row) + +CREATE OR REPLACE FUNCTION pyreturnmultidemint4(h int4, i int4, j int4, k int4 ) RETURNS int4[] AS $BODY$ +m = [[[[x for x in range(h)] for y in range(i)] for z in range(j)] for w in range(k)] +plpy.info(m, type(m)) +return m +$BODY$ LANGUAGE plpython3u; +select pyreturnmultidemint4(8,5,3,2); +INFO: ([[[[0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7]], [[0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7]], [[0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7]]], [[[0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7]], [[0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7]], [[0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7]]]], <class 'list'>) + pyreturnmultidemint4 +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + {{{{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7}},{{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7}},{{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7}}},{{{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7}},{{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7}},{{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7}}}} +(1 row) + +CREATE OR REPLACE FUNCTION pyreturnmultidemint8(h int4, i int4, j int4, k int4 ) RETURNS int8[] AS $BODY$ +m = [[[[x for x in range(h)] for y in range(i)] for z in range(j)] for w in range(k)] +plpy.info(m, type(m)) +return m +$BODY$ LANGUAGE plpython3u; +select pyreturnmultidemint8(5,5,3,2); +INFO: ([[[[0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4]], [[0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4]], [[0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4]]], [[[0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4]], [[0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4]], [[0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4]]]], <class 'list'>) + pyreturnmultidemint8 +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + {{{{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4}},{{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4}},{{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4}}},{{{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4}},{{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4}},{{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4}}}} +(1 row) + +CREATE OR REPLACE FUNCTION pyreturnmultidemfloat4(h int4, i int4, j int4, k int4 ) RETURNS float4[] AS $BODY$ +m = [[[[x for x in range(h)] for y in range(i)] for z in range(j)] for w in range(k)] +plpy.info(m, type(m)) +return m +$BODY$ LANGUAGE plpython3u; +select pyreturnmultidemfloat4(6,5,3,2); +INFO: ([[[[0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5]], [[0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5]], [[0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5]]], [[[0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5]], [[0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5]], [[0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5]]]], <class 'list'>) + pyreturnmultidemfloat4 +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + {{{{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5}},{{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5}},{{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5}}},{{{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5}},{{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5}},{{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5}}}} +(1 row) + +CREATE OR REPLACE FUNCTION pyreturnmultidemfloat8(h int4, i int4, j int4, k int4 ) RETURNS float8[] AS $BODY$ +m = [[[[x for x in range(h)] for y in range(i)] for z in range(j)] for w in range(k)] +plpy.info(m, type(m)) +return m +$BODY$ LANGUAGE plpython3u; +select pyreturnmultidemfloat8(7,5,3,2); +INFO: ([[[[0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6]], [[0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6]], [[0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6]]], [[[0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6]], [[0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6]], [[0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6]]]], <class 'list'>) + pyreturnmultidemfloat8 +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + {{{{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6}},{{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6}},{{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6}}},{{{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6}},{{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6}},{{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6}}}} +(1 row) + +CREATE FUNCTION test_type_conversion_array_text(x text[]) RETURNS text[] AS $$ +plpy.info(x, type(x)) +return x +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_array_text(ARRAY['foo', 'bar']); +INFO: (['foo', 'bar'], <class 'list'>) + test_type_conversion_array_text +--------------------------------- + {foo,bar} +(1 row) + +SELECT * FROM test_type_conversion_array_text(ARRAY[['foo', 'bar'],['foo2', 'bar2']]); +INFO: ([['foo', 'bar'], ['foo2', 'bar2']], <class 'list'>) + test_type_conversion_array_text +--------------------------------- + {{foo,bar},{foo2,bar2}} +(1 row) + +CREATE FUNCTION test_type_conversion_array_bytea(x bytea[]) RETURNS bytea[] AS $$ +plpy.info(x, type(x)) +return x +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_array_bytea(ARRAY[E'\\xdeadbeef'::bytea, NULL]); +INFO: ([b'\xde\xad\xbe\xef', None], <class 'list'>) + test_type_conversion_array_bytea +---------------------------------- + {"\\xdeadbeef",NULL} +(1 row) + +CREATE FUNCTION test_type_conversion_array_mixed1() RETURNS text[] AS $$ +return [123, 'abc'] +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_array_mixed1(); + test_type_conversion_array_mixed1 +----------------------------------- + {123,abc} +(1 row) + +CREATE FUNCTION test_type_conversion_array_mixed2() RETURNS int[] AS $$ +return [123, 'abc'] +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_array_mixed2(); +ERROR: invalid input syntax for type integer: "abc" +CONTEXT: while creating return value +PL/Python function "test_type_conversion_array_mixed2" +-- check output of multi-dimensional arrays +CREATE FUNCTION test_type_conversion_md_array_out() RETURNS text[] AS $$ +return [['a'], ['b'], ['c']] +$$ LANGUAGE plpython3u; +select test_type_conversion_md_array_out(); + test_type_conversion_md_array_out +----------------------------------- + {{a},{b},{c}} +(1 row) + +CREATE OR REPLACE FUNCTION test_type_conversion_md_array_out() RETURNS text[] AS $$ +return [[], []] +$$ LANGUAGE plpython3u; +select test_type_conversion_md_array_out(); + test_type_conversion_md_array_out +----------------------------------- + {} +(1 row) + +CREATE OR REPLACE FUNCTION test_type_conversion_md_array_out() RETURNS text[] AS $$ +return [[], [1]] +$$ LANGUAGE plpython3u; +select test_type_conversion_md_array_out(); -- fail +ERROR: multidimensional arrays must have array expressions with matching dimensions +CONTEXT: while creating return value +PL/Python function "test_type_conversion_md_array_out" +CREATE OR REPLACE FUNCTION test_type_conversion_md_array_out() RETURNS text[] AS $$ +return [[], 1] +$$ LANGUAGE plpython3u; +select test_type_conversion_md_array_out(); -- fail +ERROR: multidimensional arrays must have array expressions with matching dimensions +CONTEXT: while creating return value +PL/Python function "test_type_conversion_md_array_out" +CREATE OR REPLACE FUNCTION test_type_conversion_md_array_out() RETURNS text[] AS $$ +return [1, []] +$$ LANGUAGE plpython3u; +select test_type_conversion_md_array_out(); -- fail +ERROR: multidimensional arrays must have array expressions with matching dimensions +CONTEXT: while creating return value +PL/Python function "test_type_conversion_md_array_out" +CREATE OR REPLACE FUNCTION test_type_conversion_md_array_out() RETURNS text[] AS $$ +return [[1], [[]]] +$$ LANGUAGE plpython3u; +select test_type_conversion_md_array_out(); -- fail +ERROR: multidimensional arrays must have array expressions with matching dimensions +CONTEXT: while creating return value +PL/Python function "test_type_conversion_md_array_out" +CREATE FUNCTION test_type_conversion_mdarray_malformed() RETURNS int[] AS $$ +return [[1,2,3],[4,5]] +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_mdarray_malformed(); +ERROR: multidimensional arrays must have array expressions with matching dimensions +CONTEXT: while creating return value +PL/Python function "test_type_conversion_mdarray_malformed" +CREATE FUNCTION test_type_conversion_mdarray_malformed2() RETURNS text[] AS $$ +return [[1,2,3], "abc"] +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_mdarray_malformed2(); +ERROR: multidimensional arrays must have array expressions with matching dimensions +CONTEXT: while creating return value +PL/Python function "test_type_conversion_mdarray_malformed2" +CREATE FUNCTION test_type_conversion_mdarray_malformed3() RETURNS text[] AS $$ +return ["abc", [1,2,3]] +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_mdarray_malformed3(); +ERROR: multidimensional arrays must have array expressions with matching dimensions +CONTEXT: while creating return value +PL/Python function "test_type_conversion_mdarray_malformed3" +CREATE FUNCTION test_type_conversion_mdarray_toodeep() RETURNS int[] AS $$ +return [[[[[[[1]]]]]]] +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_mdarray_toodeep(); +ERROR: number of array dimensions exceeds the maximum allowed (6) +CONTEXT: while creating return value +PL/Python function "test_type_conversion_mdarray_toodeep" +CREATE FUNCTION test_type_conversion_array_record() RETURNS type_record[] AS $$ +return [{'first': 'one', 'second': 42}, {'first': 'two', 'second': 11}] +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_array_record(); + test_type_conversion_array_record +----------------------------------- + {"(one,42)","(two,11)"} +(1 row) + +CREATE FUNCTION test_type_conversion_array_string() RETURNS text[] AS $$ +return 'abc' +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_array_string(); + test_type_conversion_array_string +----------------------------------- + {a,b,c} +(1 row) + +CREATE FUNCTION test_type_conversion_array_tuple() RETURNS text[] AS $$ +return ('abc', 'def') +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_array_tuple(); + test_type_conversion_array_tuple +---------------------------------- + {abc,def} +(1 row) + +CREATE FUNCTION test_type_conversion_array_error() RETURNS int[] AS $$ +return 5 +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_array_error(); +ERROR: return value of function with array return type is not a Python sequence +CONTEXT: while creating return value +PL/Python function "test_type_conversion_array_error" +-- +-- Domains over arrays +-- +CREATE DOMAIN ordered_pair_domain AS integer[] CHECK (array_length(VALUE,1)=2 AND VALUE[1] < VALUE[2]); +CREATE FUNCTION test_type_conversion_array_domain(x ordered_pair_domain) RETURNS ordered_pair_domain AS $$ +plpy.info(x, type(x)) +return x +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_array_domain(ARRAY[0, 100]::ordered_pair_domain); +INFO: ([0, 100], <class 'list'>) + test_type_conversion_array_domain +----------------------------------- + {0,100} +(1 row) + +SELECT * FROM test_type_conversion_array_domain(NULL::ordered_pair_domain); +INFO: (None, <class 'NoneType'>) + test_type_conversion_array_domain +----------------------------------- + +(1 row) + +CREATE FUNCTION test_type_conversion_array_domain_check_violation() RETURNS ordered_pair_domain AS $$ +return [2,1] +$$ LANGUAGE plpython3u; +SELECT * FROM test_type_conversion_array_domain_check_violation(); +ERROR: value for domain ordered_pair_domain violates check constraint "ordered_pair_domain_check" +CONTEXT: while creating return value +PL/Python function "test_type_conversion_array_domain_check_violation" +-- +-- Arrays of domains +-- +CREATE FUNCTION test_read_uint2_array(x uint2[]) RETURNS uint2 AS $$ +plpy.info(x, type(x)) +return x[0] +$$ LANGUAGE plpython3u; +select test_read_uint2_array(array[1::uint2]); +INFO: ([1], <class 'list'>) + test_read_uint2_array +----------------------- + 1 +(1 row) + +CREATE FUNCTION test_build_uint2_array(x int2) RETURNS uint2[] AS $$ +return [x, x] +$$ LANGUAGE plpython3u; +select test_build_uint2_array(1::int2); + test_build_uint2_array +------------------------ + {1,1} +(1 row) + +select test_build_uint2_array(-1::int2); -- fail +ERROR: value for domain uint2 violates check constraint "uint2_check" +CONTEXT: while creating return value +PL/Python function "test_build_uint2_array" +-- +-- ideally this would work, but for now it doesn't, because the return value +-- is [[2,4], [2,4]] which our conversion code thinks should become a 2-D +-- integer array, not an array of arrays. +-- +CREATE FUNCTION test_type_conversion_domain_array(x integer[]) + RETURNS ordered_pair_domain[] AS $$ +return [x, x] +$$ LANGUAGE plpython3u; +select test_type_conversion_domain_array(array[2,4]); +ERROR: return value of function with array return type is not a Python sequence +CONTEXT: while creating return value +PL/Python function "test_type_conversion_domain_array" +select test_type_conversion_domain_array(array[4,2]); -- fail +ERROR: return value of function with array return type is not a Python sequence +CONTEXT: while creating return value +PL/Python function "test_type_conversion_domain_array" +CREATE FUNCTION test_type_conversion_domain_array2(x ordered_pair_domain) + RETURNS integer AS $$ +plpy.info(x, type(x)) +return x[1] +$$ LANGUAGE plpython3u; +select test_type_conversion_domain_array2(array[2,4]); +INFO: ([2, 4], <class 'list'>) + test_type_conversion_domain_array2 +------------------------------------ + 4 +(1 row) + +select test_type_conversion_domain_array2(array[4,2]); -- fail +ERROR: value for domain ordered_pair_domain violates check constraint "ordered_pair_domain_check" +CREATE FUNCTION test_type_conversion_array_domain_array(x ordered_pair_domain[]) + RETURNS ordered_pair_domain AS $$ +plpy.info(x, type(x)) +return x[0] +$$ LANGUAGE plpython3u; +select test_type_conversion_array_domain_array(array[array[2,4]::ordered_pair_domain]); +INFO: ([[2, 4]], <class 'list'>) + test_type_conversion_array_domain_array +----------------------------------------- + {2,4} +(1 row) + +--- +--- Composite types +--- +CREATE TABLE employee ( + name text, + basesalary integer, + bonus integer +); +INSERT INTO employee VALUES ('John', 100, 10), ('Mary', 200, 10); +CREATE OR REPLACE FUNCTION test_composite_table_input(e employee) RETURNS integer AS $$ +return e['basesalary'] + e['bonus'] +$$ LANGUAGE plpython3u; +SELECT name, test_composite_table_input(employee.*) FROM employee; + name | test_composite_table_input +------+---------------------------- + John | 110 + Mary | 210 +(2 rows) + +ALTER TABLE employee DROP bonus; +SELECT name, test_composite_table_input(employee.*) FROM employee; +ERROR: KeyError: 'bonus' +CONTEXT: Traceback (most recent call last): + PL/Python function "test_composite_table_input", line 2, in <module> + return e['basesalary'] + e['bonus'] +PL/Python function "test_composite_table_input" +ALTER TABLE employee ADD bonus integer; +UPDATE employee SET bonus = 10; +SELECT name, test_composite_table_input(employee.*) FROM employee; + name | test_composite_table_input +------+---------------------------- + John | 110 + Mary | 210 +(2 rows) + +CREATE TYPE named_pair AS ( + i integer, + j integer +); +CREATE OR REPLACE FUNCTION test_composite_type_input(p named_pair) RETURNS integer AS $$ +return sum(p.values()) +$$ LANGUAGE plpython3u; +SELECT test_composite_type_input(row(1, 2)); + test_composite_type_input +--------------------------- + 3 +(1 row) + +ALTER TYPE named_pair RENAME TO named_pair_2; +SELECT test_composite_type_input(row(1, 2)); + test_composite_type_input +--------------------------- + 3 +(1 row) + +-- +-- Domains within composite +-- +CREATE TYPE nnint_container AS (f1 int, f2 nnint); +CREATE FUNCTION nnint_test(x int, y int) RETURNS nnint_container AS $$ +return {'f1': x, 'f2': y} +$$ LANGUAGE plpython3u; +SELECT nnint_test(null, 3); + nnint_test +------------ + (,3) +(1 row) + +SELECT nnint_test(3, null); -- fail +ERROR: value for domain nnint violates check constraint "nnint_check" +CONTEXT: while creating return value +PL/Python function "nnint_test" +-- +-- Domains of composite +-- +CREATE DOMAIN ordered_named_pair AS named_pair_2 CHECK((VALUE).i <= (VALUE).j); +CREATE FUNCTION read_ordered_named_pair(p ordered_named_pair) RETURNS integer AS $$ +return p['i'] + p['j'] +$$ LANGUAGE plpython3u; +SELECT read_ordered_named_pair(row(1, 2)); + read_ordered_named_pair +------------------------- + 3 +(1 row) + +SELECT read_ordered_named_pair(row(2, 1)); -- fail +ERROR: value for domain ordered_named_pair violates check constraint "ordered_named_pair_check" +CREATE FUNCTION build_ordered_named_pair(i int, j int) RETURNS ordered_named_pair AS $$ +return {'i': i, 'j': j} +$$ LANGUAGE plpython3u; +SELECT build_ordered_named_pair(1,2); + build_ordered_named_pair +-------------------------- + (1,2) +(1 row) + +SELECT build_ordered_named_pair(2,1); -- fail +ERROR: value for domain ordered_named_pair violates check constraint "ordered_named_pair_check" +CONTEXT: while creating return value +PL/Python function "build_ordered_named_pair" +CREATE FUNCTION build_ordered_named_pairs(i int, j int) RETURNS ordered_named_pair[] AS $$ +return [{'i': i, 'j': j}, {'i': i, 'j': j+1}] +$$ LANGUAGE plpython3u; +SELECT build_ordered_named_pairs(1,2); + build_ordered_named_pairs +--------------------------- + {"(1,2)","(1,3)"} +(1 row) + +SELECT build_ordered_named_pairs(2,1); -- fail +ERROR: value for domain ordered_named_pair violates check constraint "ordered_named_pair_check" +CONTEXT: while creating return value +PL/Python function "build_ordered_named_pairs" +-- +-- Prepared statements +-- +CREATE OR REPLACE FUNCTION test_prep_bool_input() RETURNS int +LANGUAGE plpython3u +AS $$ +plan = plpy.prepare("SELECT CASE WHEN $1 THEN 1 ELSE 0 END AS val", ['boolean']) +rv = plpy.execute(plan, ['fa'], 5) # 'fa' is true in Python +return rv[0]['val'] +$$; +SELECT test_prep_bool_input(); -- 1 + test_prep_bool_input +---------------------- + 1 +(1 row) + +CREATE OR REPLACE FUNCTION test_prep_bool_output() RETURNS bool +LANGUAGE plpython3u +AS $$ +plan = plpy.prepare("SELECT $1 = 1 AS val", ['int']) +rv = plpy.execute(plan, [0], 5) +plpy.info(rv[0]) +return rv[0]['val'] +$$; +SELECT test_prep_bool_output(); -- false +INFO: {'val': False} + test_prep_bool_output +----------------------- + f +(1 row) + +CREATE OR REPLACE FUNCTION test_prep_bytea_input(bb bytea) RETURNS int +LANGUAGE plpython3u +AS $$ +plan = plpy.prepare("SELECT octet_length($1) AS val", ['bytea']) +rv = plpy.execute(plan, [bb], 5) +return rv[0]['val'] +$$; +SELECT test_prep_bytea_input(E'a\\000b'); -- 3 (embedded null formerly truncated value) + test_prep_bytea_input +----------------------- + 3 +(1 row) + +CREATE OR REPLACE FUNCTION test_prep_bytea_output() RETURNS bytea +LANGUAGE plpython3u +AS $$ +plan = plpy.prepare("SELECT decode('aa00bb', 'hex') AS val") +rv = plpy.execute(plan, [], 5) +plpy.info(rv[0]) +return rv[0]['val'] +$$; +SELECT test_prep_bytea_output(); +INFO: {'val': b'\xaa\x00\xbb'} + test_prep_bytea_output +------------------------ + \xaa00bb +(1 row) + diff --git a/src/pl/plpython/expected/plpython_unicode.out b/src/pl/plpython/expected/plpython_unicode.out new file mode 100644 index 0000000..fd54b0b --- /dev/null +++ b/src/pl/plpython/expected/plpython_unicode.out @@ -0,0 +1,56 @@ +-- +-- Unicode handling +-- +-- Note: this test case is known to fail if the database encoding is +-- EUC_CN, EUC_JP, EUC_KR, or EUC_TW, for lack of any equivalent to +-- U+00A0 (no-break space) in those encodings. However, testing with +-- plain ASCII data would be rather useless, so we must live with that. +-- +SET client_encoding TO UTF8; +CREATE TABLE unicode_test ( + testvalue text NOT NULL +); +CREATE FUNCTION unicode_return() RETURNS text AS E' +return "\\xA0" +' LANGUAGE plpython3u; +CREATE FUNCTION unicode_trigger() RETURNS trigger AS E' +TD["new"]["testvalue"] = "\\xA0" +return "MODIFY" +' LANGUAGE plpython3u; +CREATE TRIGGER unicode_test_bi BEFORE INSERT ON unicode_test + FOR EACH ROW EXECUTE PROCEDURE unicode_trigger(); +CREATE FUNCTION unicode_plan1() RETURNS text AS E' +plan = plpy.prepare("SELECT $1 AS testvalue", ["text"]) +rv = plpy.execute(plan, ["\\xA0"], 1) +return rv[0]["testvalue"] +' LANGUAGE plpython3u; +CREATE FUNCTION unicode_plan2() RETURNS text AS E' +plan = plpy.prepare("SELECT $1 || $2 AS testvalue", ["text", "text"]) +rv = plpy.execute(plan, ["foo", "bar"], 1) +return rv[0]["testvalue"] +' LANGUAGE plpython3u; +SELECT unicode_return(); + unicode_return +---------------- + +(1 row) + +INSERT INTO unicode_test (testvalue) VALUES ('test'); +SELECT * FROM unicode_test; + testvalue +----------- + +(1 row) + +SELECT unicode_plan1(); + unicode_plan1 +--------------- + +(1 row) + +SELECT unicode_plan2(); + unicode_plan2 +--------------- + foobar +(1 row) + diff --git a/src/pl/plpython/expected/plpython_void.out b/src/pl/plpython/expected/plpython_void.out new file mode 100644 index 0000000..07d0760 --- /dev/null +++ b/src/pl/plpython/expected/plpython_void.out @@ -0,0 +1,30 @@ +-- +-- Tests for functions that return void +-- +CREATE FUNCTION test_void_func1() RETURNS void AS $$ +x = 10 +$$ LANGUAGE plpython3u; +-- illegal: can't return non-None value in void-returning func +CREATE FUNCTION test_void_func2() RETURNS void AS $$ +return 10 +$$ LANGUAGE plpython3u; +CREATE FUNCTION test_return_none() RETURNS int AS $$ +None +$$ LANGUAGE plpython3u; +-- Tests for functions returning void +SELECT test_void_func1(), test_void_func1() IS NULL AS "is null"; + test_void_func1 | is null +-----------------+--------- + | f +(1 row) + +SELECT test_void_func2(); -- should fail +ERROR: PL/Python function with return type "void" did not return None +CONTEXT: while creating return value +PL/Python function "test_void_func2" +SELECT test_return_none(), test_return_none() IS NULL AS "is null"; + test_return_none | is null +------------------+--------- + | t +(1 row) + |