diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-16 19:46:48 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-16 19:46:48 +0000 |
| commit | 311bcfc6b3acdd6fd152798c7f287ddf74fa2a98 (patch) | |
| tree | 0ec307299b1dada3701e42f4ca6eda57d708261e /src/pl/plpython/sql | |
| parent | Initial commit. (diff) | |
| download | postgresql-15-311bcfc6b3acdd6fd152798c7f287ddf74fa2a98.tar.xz postgresql-15-311bcfc6b3acdd6fd152798c7f287ddf74fa2a98.zip | |
Adding upstream version 15.4.upstream/15.4upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'src/pl/plpython/sql')
23 files changed, 3297 insertions, 0 deletions
diff --git a/src/pl/plpython/sql/plpython_call.sql b/src/pl/plpython/sql/plpython_call.sql new file mode 100644 index 0000000..daa4bc3 --- /dev/null +++ b/src/pl/plpython/sql/plpython_call.sql @@ -0,0 +1,80 @@ +-- +-- 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(); + + +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; + + +-- output arguments + +CREATE PROCEDURE test_proc5(INOUT a text) +LANGUAGE plpython3u +AS $$ +return [a + '+' + a] +$$; + +CALL test_proc5('abc'); + + +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); + + +-- 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 +$$; + + +DROP PROCEDURE test_proc1; +DROP PROCEDURE test_proc2; +DROP PROCEDURE test_proc3; + +DROP TABLE test1; diff --git a/src/pl/plpython/sql/plpython_composite.sql b/src/pl/plpython/sql/plpython_composite.sql new file mode 100644 index 0000000..2175770 --- /dev/null +++ b/src/pl/plpython/sql/plpython_composite.sql @@ -0,0 +1,224 @@ +CREATE FUNCTION multiout_simple(OUT i integer, OUT j integer) AS $$ +return (1, 2) +$$ LANGUAGE plpython3u; + +SELECT multiout_simple(); +SELECT * FROM multiout_simple(); +SELECT i, j + 2 FROM multiout_simple(); +SELECT (multiout_simple()).j + 3; + +CREATE FUNCTION multiout_simple_setof(n integer = 1, OUT integer, OUT integer) RETURNS SETOF record AS $$ +return [(1, 2)] * n +$$ LANGUAGE plpython3u; + +SELECT multiout_simple_setof(); +SELECT * FROM multiout_simple_setof(); +SELECT * FROM multiout_simple_setof(3); + +CREATE FUNCTION multiout_record_as(typ text, + first text, OUT first text, + second integer, OUT second integer, + retnull boolean) RETURNS record AS $$ +if retnull: + return None +if typ == 'dict': + return { 'first': first, 'second': second, 'additionalfield': 'must not cause trouble' } +elif typ == 'tuple': + return ( first, second ) +elif typ == 'list': + return [ first, second ] +elif typ == 'obj': + class type_record: pass + type_record.first = first + type_record.second = second + return type_record +elif typ == 'str': + return "('%s',%r)" % (first, second) +$$ LANGUAGE plpython3u; + +SELECT * FROM multiout_record_as('dict', 'foo', 1, 'f'); +SELECT multiout_record_as('dict', 'foo', 1, 'f'); + +SELECT * FROM multiout_record_as('dict', null, null, false); +SELECT * FROM multiout_record_as('dict', 'one', null, false); +SELECT * FROM multiout_record_as('dict', null, 2, false); +SELECT * FROM multiout_record_as('dict', 'three', 3, false); +SELECT * FROM multiout_record_as('dict', null, null, true); + +SELECT * FROM multiout_record_as('tuple', null, null, false); +SELECT * FROM multiout_record_as('tuple', 'one', null, false); +SELECT * FROM multiout_record_as('tuple', null, 2, false); +SELECT * FROM multiout_record_as('tuple', 'three', 3, false); +SELECT * FROM multiout_record_as('tuple', null, null, true); + +SELECT * FROM multiout_record_as('list', null, null, false); +SELECT * FROM multiout_record_as('list', 'one', null, false); +SELECT * FROM multiout_record_as('list', null, 2, false); +SELECT * FROM multiout_record_as('list', 'three', 3, false); +SELECT * FROM multiout_record_as('list', null, null, true); + +SELECT * FROM multiout_record_as('obj', null, null, false); +SELECT * FROM multiout_record_as('obj', 'one', null, false); +SELECT * FROM multiout_record_as('obj', null, 2, false); +SELECT * FROM multiout_record_as('obj', 'three', 3, false); +SELECT * FROM multiout_record_as('obj', null, null, true); + +SELECT * FROM multiout_record_as('str', 'one', 1, false); +SELECT * FROM multiout_record_as('str', 'one', 2, false); + +SELECT *, s IS NULL AS snull FROM multiout_record_as('tuple', 'xxx', NULL, 'f') AS f(f, s); +SELECT *, f IS NULL AS fnull, s IS NULL AS snull FROM multiout_record_as('tuple', 'xxx', 1, 't') AS f(f, s); +SELECT * FROM multiout_record_as('obj', NULL, 10, 'f'); + +CREATE FUNCTION multiout_setof(n integer, + OUT power_of_2 integer, + OUT length integer) RETURNS SETOF record AS $$ +for i in range(n): + power = 2 ** i + length = plpy.execute("select length('%d')" % power)[0]['length'] + yield power, length +$$ LANGUAGE plpython3u; + +SELECT * FROM multiout_setof(3); +SELECT multiout_setof(5); + +CREATE FUNCTION multiout_return_table() RETURNS TABLE (x integer, y text) AS $$ +return [{'x': 4, 'y' :'four'}, + {'x': 7, 'y' :'seven'}, + {'x': 0, 'y' :'zero'}] +$$ LANGUAGE plpython3u; + +SELECT * FROM multiout_return_table(); + +CREATE FUNCTION multiout_array(OUT integer[], OUT text) RETURNS SETOF record AS $$ +yield [[1], 'a'] +yield [[1,2], 'b'] +yield [[1,2,3], None] +$$ LANGUAGE plpython3u; + +SELECT * FROM multiout_array(); + +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(); +SELECT * FROM multiout_composite(); + +-- composite OUT parameters in functions returning RECORD not supported yet +CREATE FUNCTION multiout_composite(INOUT n integer, OUT type_record) AS $$ +return (n, (n * 2, n * 3)) +$$ LANGUAGE 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); +SELECT * FROM multiout_table_type_setof('dict', 'f', 3); +SELECT * FROM multiout_table_type_setof('dict', 'f', 7); +SELECT * FROM multiout_table_type_setof('tuple', 'f', 2); +SELECT * FROM multiout_table_type_setof('tuple', 'f', 3); +SELECT * FROM multiout_table_type_setof('list', 'f', 2); +SELECT * FROM multiout_table_type_setof('list', 'f', 3); +SELECT * FROM multiout_table_type_setof('obj', 'f', 4); +SELECT * FROM multiout_table_type_setof('obj', 'f', 5); +SELECT * FROM multiout_table_type_setof('str', 'f', 6); +SELECT * FROM multiout_table_type_setof('str', 'f', 7); +SELECT * FROM multiout_table_type_setof('dict', 't', 3); + +-- check what happens if a type changes under us + +CREATE TABLE changing ( + i integer, + j integer +); + +CREATE FUNCTION changing_test(OUT n integer, OUT changing) RETURNS SETOF record AS $$ +return [(1, {'i': 1, 'j': 2}), + (1, (3, 4))] +$$ LANGUAGE plpython3u; + +SELECT * FROM changing_test(); +ALTER TABLE changing DROP COLUMN j; +SELECT * FROM changing_test(); +SELECT * FROM changing_test(); +ALTER TABLE changing ADD COLUMN j integer; +SELECT * FROM changing_test(); + +-- tables of composite types + +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(); + +-- 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); +SELECT * FROM return_record('abc') AS r(t text, val bigint); +SELECT * FROM return_record('abc') AS r(t text, val integer); +SELECT * FROM return_record('abc') AS r(t varchar(30), val integer); +SELECT * FROM return_record('abc') AS r(t varchar(100), val integer); +SELECT * FROM return_record('999') AS r(val text, t integer); + +CREATE FUNCTION return_record_2(t text) RETURNS record AS $$ +return {'v1':1,'v2':2,t:3} +$$ LANGUAGE plpython3u; + +SELECT * FROM return_record_2('v3') AS (v3 int, v2 int, v1 int); +SELECT * FROM return_record_2('v3') AS (v2 int, v3 int, v1 int); +SELECT * FROM return_record_2('v4') AS (v1 int, v4 int, v2 int); +SELECT * FROM return_record_2('v4') AS (v1 int, v4 int, v2 int); +-- error +SELECT * FROM return_record_2('v4') AS (v1 int, v3 int, v2 int); +-- works +SELECT * FROM return_record_2('v3') AS (v1 int, v3 int, v2 int); +SELECT * FROM return_record_2('v3') AS (v1 int, v2 int, v3 int); + +-- 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(); + +-- 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(); diff --git a/src/pl/plpython/sql/plpython_do.sql b/src/pl/plpython/sql/plpython_do.sql new file mode 100644 index 0000000..d494132 --- /dev/null +++ b/src/pl/plpython/sql/plpython_do.sql @@ -0,0 +1,3 @@ +DO $$ plpy.notice("This is plpython3u.") $$ LANGUAGE plpython3u; + +DO $$ raise Exception("error test") $$ LANGUAGE plpython3u; diff --git a/src/pl/plpython/sql/plpython_drop.sql b/src/pl/plpython/sql/plpython_drop.sql new file mode 100644 index 0000000..e4f373b --- /dev/null +++ b/src/pl/plpython/sql/plpython_drop.sql @@ -0,0 +1,6 @@ +-- +-- 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/sql/plpython_ereport.sql b/src/pl/plpython/sql/plpython_ereport.sql new file mode 100644 index 0000000..d4f6223 --- /dev/null +++ b/src/pl/plpython/sql/plpython_ereport.sql @@ -0,0 +1,135 @@ +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(); + +DO $$ plpy.info('other types', detail=(10, 20)) $$ LANGUAGE plpython3u; + +DO $$ +import time; +from datetime import date +plpy.info('other types', detail=date(2016, 2, 26)) +$$ LANGUAGE plpython3u; + +DO $$ +basket = ['apple', 'orange', 'apple', 'pear', 'orange', 'banana'] +plpy.info('other types', detail=basket) +$$ LANGUAGE plpython3u; + +-- should fail +DO $$ plpy.info('wrong sqlstate', sqlstate='54444A') $$ LANGUAGE plpython3u; +DO $$ plpy.info('unsupported argument', blabla='fooboo') $$ LANGUAGE plpython3u; +DO $$ plpy.info('first message', message='second message') $$ LANGUAGE plpython3u; +DO $$ plpy.info('first message', 'second message', message='third message') $$ LANGUAGE plpython3u; + +-- 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'); +SELECT raise_exception('message text', 'detail text', _sqlstate => 'YY333'); +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'); + +SELECT raise_exception(_message => 'message text', + _hint => 'hint text', + _schema_name => 'schema text', + _column_name => 'column text', + _constraint_name => 'constraint text'); + +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; +$$; + +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; + +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; diff --git a/src/pl/plpython/sql/plpython_error.sql b/src/pl/plpython/sql/plpython_error.sql new file mode 100644 index 0000000..11f14ec --- /dev/null +++ b/src/pl/plpython/sql/plpython_error.sql @@ -0,0 +1,346 @@ +-- 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; + +/* 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(); +/* Run the function twice to check if the hashtable entry gets cleaned up */ +SELECT python_syntax_error(); + +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(); + + +/* 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'); + + +/* 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(); + + +/* 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'); + + +/* 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'); + + +/* 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'); + + +/* 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'); + +/* 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(); + +/* 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(); + +/* 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(); + +/* 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(); + +/* 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(); +SELECT sql_error(); +SELECT python_from_sql_error(); +SELECT 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); +SELECT specific_exception(NULL); +SELECT specific_exception(2); + +/* 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(); + +/* 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(); + +/* 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(); + +/* 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(); diff --git a/src/pl/plpython/sql/plpython_global.sql b/src/pl/plpython/sql/plpython_global.sql new file mode 100644 index 0000000..96d2049 --- /dev/null +++ b/src/pl/plpython/sql/plpython_global.sql @@ -0,0 +1,38 @@ +-- +-- 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(); +SELECT static_test(); +SELECT global_test_one(); +SELECT global_test_two(); diff --git a/src/pl/plpython/sql/plpython_import.sql b/src/pl/plpython/sql/plpython_import.sql new file mode 100644 index 0000000..3031eef --- /dev/null +++ b/src/pl/plpython/sql/plpython_import.sql @@ -0,0 +1,68 @@ +-- 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(); +SELECT import_succeed(); + +-- test import and simple argument handling +-- +SELECT import_test_one('sha hash of this string'); + +-- test import and tuple argument handling +-- +select import_test_two(users) from users where fname = 'willem'; diff --git a/src/pl/plpython/sql/plpython_newline.sql b/src/pl/plpython/sql/plpython_newline.sql new file mode 100644 index 0000000..cb22ba9 --- /dev/null +++ b/src/pl/plpython/sql/plpython_newline.sql @@ -0,0 +1,20 @@ +-- +-- 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(); +SELECT newline_cr(); +SELECT newline_crlf(); diff --git a/src/pl/plpython/sql/plpython_params.sql b/src/pl/plpython/sql/plpython_params.sql new file mode 100644 index 0000000..8bab488 --- /dev/null +++ b/src/pl/plpython/sql/plpython_params.sql @@ -0,0 +1,42 @@ +-- +-- 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); +SELECT test_param_names1(1,'text'); +SELECT test_param_names2(users) from users; +SELECT test_param_names2(NULL); +SELECT test_param_names3(1); diff --git a/src/pl/plpython/sql/plpython_populate.sql b/src/pl/plpython/sql/plpython_populate.sql new file mode 100644 index 0000000..cc1e19b --- /dev/null +++ b/src/pl/plpython/sql/plpython_populate.sql @@ -0,0 +1,27 @@ +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/sql/plpython_quote.sql b/src/pl/plpython/sql/plpython_quote.sql new file mode 100644 index 0000000..a1133e7 --- /dev/null +++ b/src/pl/plpython/sql/plpython_quote.sql @@ -0,0 +1,33 @@ +-- 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); + +SELECT quote(t, 'nullable') FROM (VALUES + ('abc'), + ('a''bc'), + ('''abc'''), + (''), + (''''), + (NULL)) AS v(t); + +SELECT quote(t, 'ident') FROM (VALUES + ('abc'), + ('a b c'), + ('a " ''abc''')) AS v(t); diff --git a/src/pl/plpython/sql/plpython_record.sql b/src/pl/plpython/sql/plpython_record.sql new file mode 100644 index 0000000..52bad8b --- /dev/null +++ b/src/pl/plpython/sql/plpython_record.sql @@ -0,0 +1,163 @@ +-- +-- 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); +SELECT * FROM test_table_record_as('dict', 'one', null, false); +SELECT * FROM test_table_record_as('dict', null, 2, false); +SELECT * FROM test_table_record_as('dict', 'three', 3, false); +SELECT * FROM test_table_record_as('dict', null, null, true); + +SELECT * FROM test_table_record_as('tuple', null, null, false); +SELECT * FROM test_table_record_as('tuple', 'one', null, false); +SELECT * FROM test_table_record_as('tuple', null, 2, false); +SELECT * FROM test_table_record_as('tuple', 'three', 3, false); +SELECT * FROM test_table_record_as('tuple', null, null, true); + +SELECT * FROM test_table_record_as('list', null, null, false); +SELECT * FROM test_table_record_as('list', 'one', null, false); +SELECT * FROM test_table_record_as('list', null, 2, false); +SELECT * FROM test_table_record_as('list', 'three', 3, false); +SELECT * FROM test_table_record_as('list', null, null, true); + +SELECT * FROM test_table_record_as('obj', null, null, false); +SELECT * FROM test_table_record_as('obj', 'one', null, false); +SELECT * FROM test_table_record_as('obj', null, 2, false); +SELECT * FROM test_table_record_as('obj', 'three', 3, false); +SELECT * FROM test_table_record_as('obj', null, null, true); + +SELECT * FROM test_type_record_as('dict', null, null, false); +SELECT * FROM test_type_record_as('dict', 'one', null, false); +SELECT * FROM test_type_record_as('dict', null, 2, false); +SELECT * FROM test_type_record_as('dict', 'three', 3, false); +SELECT * FROM test_type_record_as('dict', null, null, true); + +SELECT * FROM test_type_record_as('tuple', null, null, false); +SELECT * FROM test_type_record_as('tuple', 'one', null, false); +SELECT * FROM test_type_record_as('tuple', null, 2, false); +SELECT * FROM test_type_record_as('tuple', 'three', 3, false); +SELECT * FROM test_type_record_as('tuple', null, null, true); + +SELECT * FROM test_type_record_as('list', null, null, false); +SELECT * FROM test_type_record_as('list', 'one', null, false); +SELECT * FROM test_type_record_as('list', null, 2, false); +SELECT * FROM test_type_record_as('list', 'three', 3, false); +SELECT * FROM test_type_record_as('list', null, null, true); + +SELECT * FROM test_type_record_as('obj', null, null, false); +SELECT * FROM test_type_record_as('obj', 'one', null, false); +SELECT * FROM test_type_record_as('obj', null, 2, false); +SELECT * FROM test_type_record_as('obj', 'three', 3, false); +SELECT * FROM test_type_record_as('obj', null, null, true); + +SELECT * FROM test_type_record_as('str', 'one', 1, false); + +SELECT * FROM test_in_out_params('test_in'); +SELECT * FROM test_in_out_params_multi('test_in'); +SELECT * FROM test_inout_params('test_in'); + +-- 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); + +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); + +-- errors cases + +CREATE FUNCTION test_type_record_error1() RETURNS type_record AS $$ + return { 'first': 'first' } +$$ LANGUAGE plpython3u; + +SELECT * FROM test_type_record_error1(); + + +CREATE FUNCTION test_type_record_error2() RETURNS type_record AS $$ + return [ 'first' ] +$$ LANGUAGE plpython3u; + +SELECT * FROM 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(); + +CREATE FUNCTION test_type_record_error4() RETURNS type_record AS $$ + return 'foo' +$$ LANGUAGE plpython3u; + +SELECT * FROM test_type_record_error4(); diff --git a/src/pl/plpython/sql/plpython_schema.sql b/src/pl/plpython/sql/plpython_schema.sql new file mode 100644 index 0000000..a5bdbda --- /dev/null +++ b/src/pl/plpython/sql/plpython_schema.sql @@ -0,0 +1,39 @@ +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/sql/plpython_setof.sql b/src/pl/plpython/sql/plpython_setof.sql new file mode 100644 index 0000000..4cfb101 --- /dev/null +++ b/src/pl/plpython/sql/plpython_setof.sql @@ -0,0 +1,97 @@ +-- +-- Test returning SETOF +-- + +CREATE FUNCTION test_setof_error() RETURNS SETOF text AS $$ +return 37 +$$ LANGUAGE plpython3u; + +SELECT 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'); +SELECT test_setof_as_list(1, 'list'); +SELECT test_setof_as_list(2, 'list'); +SELECT test_setof_as_list(2, null); + +SELECT test_setof_as_tuple(0, 'tuple'); +SELECT test_setof_as_tuple(1, 'tuple'); +SELECT test_setof_as_tuple(2, 'tuple'); +SELECT test_setof_as_tuple(2, null); + +SELECT test_setof_as_iterator(0, 'list'); +SELECT test_setof_as_iterator(1, 'list'); +SELECT test_setof_as_iterator(2, 'list'); +SELECT test_setof_as_iterator(2, null); + +SELECT test_setof_spi_in_iterator(); + +-- 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); + +-- interleaved execution of such a function +SELECT ugly(1,3), ugly(7,8); + +-- 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(); +SELECT * FROM get_user_records(); + +-- 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(); +SELECT * FROM get_user_records2(); diff --git a/src/pl/plpython/sql/plpython_spi.sql b/src/pl/plpython/sql/plpython_spi.sql new file mode 100644 index 0000000..fcd113a --- /dev/null +++ b/src/pl/plpython/sql/plpython_spi.sql @@ -0,0 +1,322 @@ +-- +-- 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'); +select spi_prepared_plan_test_one('doe'); +select spi_prepared_plan_test_two('smith'); +select spi_prepared_plan_test_nested('smith'); + +SELECT join_sequences(sequences) FROM sequences; +SELECT join_sequences(sequences) FROM sequences + WHERE join_sequences(sequences) ~* '^A'; +SELECT join_sequences(sequences) FROM sequences + WHERE join_sequences(sequences) ~* '^B'; + +SELECT spi_recursive_sum(10); + +-- +-- 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'$$); +SELECT result_metadata_test($$CREATE TEMPORARY TABLE foo1 (a int, b text)$$); + +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$$); +SELECT result_nrows_test($$CREATE TEMPORARY TABLE foo2 (a int, b text)$$); +SELECT result_nrows_test($$INSERT INTO foo2 VALUES (1, 'one'), (2, 'two')$$); +SELECT result_nrows_test($$UPDATE foo2 SET b = '' WHERE a = 2$$); + +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$$); +SELECT result_len_test($$CREATE TEMPORARY TABLE foo3 (a int, b text)$$); +SELECT result_len_test($$INSERT INTO foo3 VALUES (1, 'one'), (2, 'two')$$); +SELECT result_len_test($$UPDATE foo3 SET b= '' WHERE a = 2$$); + +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(); + +CREATE FUNCTION result_empty_test() RETURNS void +AS $$ +result = plpy.execute("select 1 where false") + +plpy.info(result[:]) + +$$ LANGUAGE plpython3u; + +SELECT result_empty_test(); + +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$$); +SELECT result_str_test($$CREATE TEMPORARY TABLE foo1 (a int, b text)$$); + +-- 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(); +SELECT double_cursor_close(); +SELECT cursor_fetch(); +SELECT cursor_mix_next_and_fetch(); +SELECT fetch_after_close(); +SELECT next_after_close(); +SELECT cursor_fetch_next_empty(); +SELECT cursor_plan(); +SELECT cursor_plan_wrong_args(); +SELECT plan_composite_args(); diff --git a/src/pl/plpython/sql/plpython_subtransaction.sql b/src/pl/plpython/sql/plpython_subtransaction.sql new file mode 100644 index 0000000..c65c380 --- /dev/null +++ b/src/pl/plpython/sql/plpython_subtransaction.sql @@ -0,0 +1,262 @@ +-- +-- 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(); +SELECT * FROM subtransaction_tbl; +TRUNCATE subtransaction_tbl; +SELECT subtransaction_ctx_test('SPI'); +SELECT * FROM subtransaction_tbl; +TRUNCATE subtransaction_tbl; +SELECT subtransaction_ctx_test('Python'); +SELECT * FROM subtransaction_tbl; +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(); +SELECT * FROM subtransaction_tbl; +TRUNCATE subtransaction_tbl; + +SELECT subtransaction_nested_test('t'); +SELECT * FROM subtransaction_tbl; +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(); +SELECT * FROM subtransaction_tbl; +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(); +SELECT subtransaction_enter_without_exit(); +SELECT subtransaction_exit_twice(); +SELECT subtransaction_enter_twice(); +SELECT subtransaction_exit_same_subtransaction_twice(); +SELECT subtransaction_enter_same_subtransaction_twice(); +SELECT subtransaction_enter_subtransaction_in_with(); +SELECT subtransaction_exit_subtransaction_in_with(); + +-- Make sure we don't get a "current transaction is aborted" error +SELECT 1 as test; + +-- 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(); +SELECT * FROM subtransaction_tbl; +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(); + +-- 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(); +SELECT * FROM subtransaction_tbl; +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(); +SELECT * FROM subtransaction_tbl; + +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(); +SELECT cursor_aborted_subxact(); +SELECT cursor_plan_aborted_subxact(); +SELECT cursor_close_aborted_subxact(); diff --git a/src/pl/plpython/sql/plpython_test.sql b/src/pl/plpython/sql/plpython_test.sql new file mode 100644 index 0000000..aa22a27 --- /dev/null +++ b/src/pl/plpython/sql/plpython_test.sql @@ -0,0 +1,52 @@ +-- 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(); + +-- check 2/3 versioning +CREATE FUNCTION stupidn() RETURNS text AS 'return "zarkon"' LANGUAGE plpython3u; + +select stupidn(); + +-- 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; + + +-- 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(); + +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(); diff --git a/src/pl/plpython/sql/plpython_transaction.sql b/src/pl/plpython/sql/plpython_transaction.sql new file mode 100644 index 0000000..c939ba7 --- /dev/null +++ b/src/pl/plpython/sql/plpython_transaction.sql @@ -0,0 +1,182 @@ +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; + + +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; + + +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(); + +SELECT * FROM test1; + + +-- 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(); + +SELECT * FROM test1; + + +-- 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(); + + +-- commit inside subtransaction (prohibited) +DO LANGUAGE plpython3u $$ +s = plpy.subtransaction() +s.enter() +plpy.commit() +$$; + + +-- 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; + +-- check that this doesn't leak a holdable portal +SELECT * FROM pg_cursors; + + +-- 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() +$$; + +SELECT * FROM test1; + +SELECT * FROM pg_cursors; + + +-- 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; + +SELECT * FROM pg_cursors; + + +-- 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; + +SELECT * FROM pg_cursors; + + +-- 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') +$$; + +SELECT * FROM testpk; +SELECT * FROM testfk; + +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)") +$$; + +SELECT * FROM testpk; +SELECT * FROM testfk; + + +DROP TABLE test1; +DROP TABLE test2; diff --git a/src/pl/plpython/sql/plpython_trigger.sql b/src/pl/plpython/sql/plpython_trigger.sql new file mode 100644 index 0000000..e5504b9 --- /dev/null +++ b/src/pl/plpython/sql/plpython_trigger.sql @@ -0,0 +1,469 @@ +-- 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; + +-- 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; + + +-- 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'); +update trigger_test set v = 'update' where i = 1; +delete from trigger_test; +truncate table trigger_test; + +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); +update trigger_test_generated set i = 11 where i = 1; +delete from trigger_test_generated; + +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'); +update trigger_test_view set v = 'update' where i = 1; +delete from trigger_test_view; + +DROP FUNCTION trigger_data() CASCADE; +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'); + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +DROP TRIGGER stupid_trigger7 ON trigger_test; + + +-- calling a trigger function directly + +SELECT stupid7(); + + +-- +-- Null values +-- + +SELECT * FROM trigger_test; + +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; + + +-- +-- 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; +UPDATE pb SET a = 'b'; +SELECT * FROM pb; + + +-- triggers for tables with composite types + +CREATE TABLE comp1 (i integer, j boolean); +CREATE TYPE comp2 AS (k integer, l boolean); + +CREATE TABLE composite_trigger_test (f1 comp1, f2 comp2); + +CREATE FUNCTION composite_trigger_f() RETURNS trigger AS $$ + TD['new']['f1'] = (3, False) + TD['new']['f2'] = {'k': 7, 'l': 'yes', 'ignored': 10} + return 'MODIFY' +$$ LANGUAGE 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; + + +-- 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; + + +-- 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; + +-- 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; +DROP TABLE a; +INSERT INTO b DEFAULT VALUES; +SELECT * FROM b; + +-- 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'; + +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); +SELECT * FROM trigger_test_generated; diff --git a/src/pl/plpython/sql/plpython_types.sql b/src/pl/plpython/sql/plpython_types.sql new file mode 100644 index 0000000..0985a9c --- /dev/null +++ b/src/pl/plpython/sql/plpython_types.sql @@ -0,0 +1,622 @@ +-- +-- 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); +SELECT * FROM test_type_conversion_bool(false); +SELECT * FROM test_type_conversion_bool(null); + + +-- 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); +SELECT * FROM test_type_conversion_bool_other(1); +SELECT * FROM test_type_conversion_bool_other(2); +SELECT * FROM test_type_conversion_bool_other(3); +SELECT * FROM test_type_conversion_bool_other(4); +SELECT * FROM test_type_conversion_bool_other(5); + + +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'); +SELECT * FROM test_type_conversion_char(null); + + +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); +SELECT * FROM test_type_conversion_int2(-100::int2); +SELECT * FROM test_type_conversion_int2(null); + + +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); +SELECT * FROM test_type_conversion_int4(-100); +SELECT * FROM test_type_conversion_int4(null); + + +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); +SELECT * FROM test_type_conversion_int8(-100); +SELECT * FROM test_type_conversion_int8(5000000000); +SELECT * FROM test_type_conversion_int8(null); + + +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); +SELECT * FROM test_type_conversion_numeric(-100); +SELECT * FROM test_type_conversion_numeric(100.0); +SELECT * FROM test_type_conversion_numeric(100.00); +SELECT * FROM test_type_conversion_numeric(5000000000.5); +SELECT * FROM test_type_conversion_numeric(1234567890.0987654321); +SELECT * FROM test_type_conversion_numeric(-1234567890.0987654321); +SELECT * FROM test_type_conversion_numeric(null); + + +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); +SELECT * FROM test_type_conversion_float4(-100); +SELECT * FROM test_type_conversion_float4(5000.5); +SELECT * FROM test_type_conversion_float4(null); + + +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); +SELECT * FROM test_type_conversion_float8(-100); +SELECT * FROM test_type_conversion_float8(5000000000.5); +SELECT * FROM test_type_conversion_float8(null); +SELECT * FROM test_type_conversion_float8(100100100.654321); + + +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); +SELECT * FROM test_type_conversion_oid(2147483649); +SELECT * FROM test_type_conversion_oid(null); + + +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'); +SELECT * FROM test_type_conversion_text(null); + + +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'); +SELECT * FROM test_type_conversion_bytea(E'null\\000byte'); +SELECT * FROM test_type_conversion_bytea(null); + + +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; + + +-- +-- 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); +SELECT * FROM test_type_conversion_booltrue(false, true); +SELECT * FROM test_type_conversion_booltrue(true, false); + + +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); +SELECT * FROM test_type_conversion_uint2(100::uint2, -50); +SELECT * FROM test_type_conversion_uint2(null, 1); + + +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); +SELECT * FROM test_type_conversion_nnint(null, 20); +SELECT * FROM test_type_conversion_nnint(10, null); + + +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'); +SELECT * FROM test_type_conversion_bytea10('hello world', 'hello wold'); +SELECT * FROM test_type_conversion_bytea10('hello word', 'hello world'); +SELECT * FROM test_type_conversion_bytea10(null, 'hello word'); +SELECT * FROM test_type_conversion_bytea10('hello word', null); + + +-- +-- 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]); +SELECT * FROM test_type_conversion_array_int4(ARRAY[0,-100,55]); +SELECT * FROM test_type_conversion_array_int4(ARRAY[NULL,1]); +SELECT * FROM test_type_conversion_array_int4(ARRAY[]::integer[]); +SELECT * FROM test_type_conversion_array_int4(NULL); +SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]); +SELECT * FROM test_type_conversion_array_int4(ARRAY[[[1,2,NULL],[NULL,5,6]],[[NULL,8,9],[10,11,12]]]); +SELECT * FROM test_type_conversion_array_int4('[2:4]={1,2,3}'); + +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[]); + +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[]); + +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[]); + + +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); + +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); + +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); + +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); + +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']); +SELECT * FROM test_type_conversion_array_text(ARRAY[['foo', 'bar'],['foo2', 'bar2']]); + + +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]); + + +CREATE FUNCTION test_type_conversion_array_mixed1() RETURNS text[] AS $$ +return [123, 'abc'] +$$ LANGUAGE plpython3u; + +SELECT * FROM test_type_conversion_array_mixed1(); + + +CREATE FUNCTION test_type_conversion_array_mixed2() RETURNS int[] AS $$ +return [123, 'abc'] +$$ LANGUAGE plpython3u; + +SELECT * FROM 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(); + +CREATE OR REPLACE FUNCTION test_type_conversion_md_array_out() RETURNS text[] AS $$ +return [[], []] +$$ LANGUAGE plpython3u; + +select 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 + +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 + +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 + +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 + + +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(); + +CREATE FUNCTION test_type_conversion_mdarray_malformed2() RETURNS text[] AS $$ +return [[1,2,3], "abc"] +$$ LANGUAGE plpython3u; + +SELECT * FROM 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(); + +CREATE FUNCTION test_type_conversion_mdarray_toodeep() RETURNS int[] AS $$ +return [[[[[[[1]]]]]]] +$$ LANGUAGE plpython3u; + +SELECT * FROM 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(); + + +CREATE FUNCTION test_type_conversion_array_string() RETURNS text[] AS $$ +return 'abc' +$$ LANGUAGE plpython3u; + +SELECT * FROM test_type_conversion_array_string(); + +CREATE FUNCTION test_type_conversion_array_tuple() RETURNS text[] AS $$ +return ('abc', 'def') +$$ LANGUAGE plpython3u; + +SELECT * FROM test_type_conversion_array_tuple(); + +CREATE FUNCTION test_type_conversion_array_error() RETURNS int[] AS $$ +return 5 +$$ LANGUAGE plpython3u; + +SELECT * FROM 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); +SELECT * FROM test_type_conversion_array_domain(NULL::ordered_pair_domain); + +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(); + + +-- +-- 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]); + +CREATE FUNCTION test_build_uint2_array(x int2) RETURNS uint2[] AS $$ +return [x, x] +$$ LANGUAGE plpython3u; + +select test_build_uint2_array(1::int2); +select test_build_uint2_array(-1::int2); -- fail + +-- +-- 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]); +select test_type_conversion_domain_array(array[4,2]); -- fail + +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]); +select test_type_conversion_domain_array2(array[4,2]); -- fail + +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]); + + +--- +--- 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; + +ALTER TABLE employee DROP bonus; + +SELECT name, test_composite_table_input(employee.*) FROM employee; + +ALTER TABLE employee ADD bonus integer; +UPDATE employee SET bonus = 10; + +SELECT name, test_composite_table_input(employee.*) FROM employee; + +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)); + +ALTER TYPE named_pair RENAME TO named_pair_2; + +SELECT test_composite_type_input(row(1, 2)); + + +-- +-- 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); +SELECT nnint_test(3, null); -- fail + + +-- +-- 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)); +SELECT read_ordered_named_pair(row(2, 1)); -- fail + +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); +SELECT build_ordered_named_pair(2,1); -- fail + +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); +SELECT build_ordered_named_pairs(2,1); -- fail + + +-- +-- 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 + + +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 + + +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) + + +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(); diff --git a/src/pl/plpython/sql/plpython_unicode.sql b/src/pl/plpython/sql/plpython_unicode.sql new file mode 100644 index 0000000..14f7b4e --- /dev/null +++ b/src/pl/plpython/sql/plpython_unicode.sql @@ -0,0 +1,45 @@ +-- +-- 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(); +INSERT INTO unicode_test (testvalue) VALUES ('test'); +SELECT * FROM unicode_test; +SELECT unicode_plan1(); +SELECT unicode_plan2(); diff --git a/src/pl/plpython/sql/plpython_void.sql b/src/pl/plpython/sql/plpython_void.sql new file mode 100644 index 0000000..5a1a671 --- /dev/null +++ b/src/pl/plpython/sql/plpython_void.sql @@ -0,0 +1,22 @@ +-- +-- 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"; +SELECT test_void_func2(); -- should fail +SELECT test_return_none(), test_return_none() IS NULL AS "is null"; |