From cfe380a6dd0dc7335ad83c3e0789357dc554fad3 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Thu, 13 Aug 2009 20:50:05 +0000 Subject: [PATCH] Augment test coverage in PL/Python, especially for error conditions. --- src/pl/plpython/expected/plpython_params.out | 6 + src/pl/plpython/expected/plpython_record.out | 31 +++ src/pl/plpython/expected/plpython_schema.out | 8 - src/pl/plpython/expected/plpython_setof.out | 7 + src/pl/plpython/expected/plpython_test.out | 24 +++ src/pl/plpython/expected/plpython_trigger.out | 287 +++++++++++++++++++++++++- src/pl/plpython/plpython.c | 4 +- src/pl/plpython/sql/plpython_params.sql | 1 + src/pl/plpython/sql/plpython_record.sql | 36 ++++ src/pl/plpython/sql/plpython_schema.sql | 10 - src/pl/plpython/sql/plpython_setof.sql | 7 + src/pl/plpython/sql/plpython_spi.sql | 1 - src/pl/plpython/sql/plpython_test.sql | 15 ++ src/pl/plpython/sql/plpython_trigger.sql | 170 ++++++++++++++- 14 files changed, 573 insertions(+), 34 deletions(-) diff --git a/src/pl/plpython/expected/plpython_params.out b/src/pl/plpython/expected/plpython_params.out index 2b609890c4..3ec3396dc6 100644 --- a/src/pl/plpython/expected/plpython_params.out +++ b/src/pl/plpython/expected/plpython_params.out @@ -43,6 +43,12 @@ SELECT test_param_names2(users) from users; {'lname': 'smith', 'username': 'slash', 'userid': 4, 'fname': 'rick'} (4 rows) +SELECT test_param_names2(NULL); + test_param_names2 +------------------- + None +(1 row) + SELECT test_param_names3(1); test_param_names3 ------------------- diff --git a/src/pl/plpython/expected/plpython_record.out b/src/pl/plpython/expected/plpython_record.out index d5cf0fa592..9e4645d56c 100644 --- a/src/pl/plpython/expected/plpython_record.out +++ b/src/pl/plpython/expected/plpython_record.out @@ -1,6 +1,14 @@ -- -- 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 @@ -298,3 +306,26 @@ SELECT * FROM test_inout_params('test_in'); test_in_inout (1 row) +-- errors cases +CREATE FUNCTION test_type_record_error1() RETURNS type_record AS $$ + return { 'first': 'first' } +$$ LANGUAGE plpythonu; +SELECT * FROM test_type_record_error1(); +ERROR: key "second" not found in mapping +HINT: To return null in a column, add the value None to the mapping with the key named after the column. +CONTEXT: PL/Python function "test_type_record_error1" +CREATE FUNCTION test_type_record_error2() RETURNS type_record AS $$ + return [ 'first' ] +$$ LANGUAGE plpythonu; +SELECT * FROM test_type_record_error2(); +ERROR: length of returned sequence did not match number of columns in row +CONTEXT: PL/Python function "test_type_record_error2" +CREATE FUNCTION test_type_record_error3() RETURNS type_record AS $$ + class type_record: pass + type_record.first = 'first' + return type_record +$$ LANGUAGE plpythonu; +SELECT * FROM test_type_record_error3(); +ERROR: attribute "second" does not exist in Python object +HINT: To return null in a column, let the returned object have an attribute named after column with value None. +CONTEXT: PL/Python function "test_type_record_error3" diff --git a/src/pl/plpython/expected/plpython_schema.out b/src/pl/plpython/expected/plpython_schema.out index 91930ceabf..e94e7bbcf8 100644 --- a/src/pl/plpython/expected/plpython_schema.out +++ b/src/pl/plpython/expected/plpython_schema.out @@ -41,11 +41,3 @@ CREATE TABLE xsequences ( sequence text not null ) ; CREATE INDEX xsequences_pid_idx ON xsequences(pid) ; -CREATE TABLE table_record ( - first text, - second int4 - ) ; -CREATE TYPE type_record AS ( - first text, - second int4 - ) ; diff --git a/src/pl/plpython/expected/plpython_setof.out b/src/pl/plpython/expected/plpython_setof.out index 797c142aa6..03a97194c8 100644 --- a/src/pl/plpython/expected/plpython_setof.out +++ b/src/pl/plpython/expected/plpython_setof.out @@ -1,6 +1,13 @@ -- -- Test returning SETOF -- +CREATE FUNCTION test_setof_error() RETURNS SETOF text AS $$ +return 37 +$$ LANGUAGE plpythonu; +SELECT test_setof_error(); +ERROR: returned object cannot be iterated +DETAIL: PL/Python set-returning functions must return an iterable object. +CONTEXT: PL/Python function "test_setof_error" CREATE FUNCTION test_setof_as_list(count integer, content text) RETURNS SETOF text AS $$ return [ content ]*count $$ LANGUAGE plpythonu; diff --git a/src/pl/plpython/expected/plpython_test.out b/src/pl/plpython/expected/plpython_test.out index 5c12a73226..5cce4e290f 100644 --- a/src/pl/plpython/expected/plpython_test.out +++ b/src/pl/plpython/expected/plpython_test.out @@ -26,3 +26,27 @@ select argument_test_one(users, fname, lname) from users where lname = 'doe' ord willem doe => {fname: willem, lname: doe, userid: 3, username: w_doe} (3 rows) +CREATE FUNCTION elog_test() RETURNS void +AS $$ +plpy.debug('debug') +plpy.log('log') +plpy.info('info') +plpy.info(37) +plpy.info('info', 37, [1, 2, 3]) +plpy.notice('notice') +plpy.warning('warning') +plpy.error('error') +$$ LANGUAGE plpythonu; +SELECT elog_test(); +INFO: ('info',) +CONTEXT: PL/Python function "elog_test" +INFO: (37,) +CONTEXT: PL/Python function "elog_test" +INFO: ('info', 37, [1, 2, 3]) +CONTEXT: PL/Python function "elog_test" +NOTICE: ('notice',) +CONTEXT: PL/Python function "elog_test" +WARNING: ('warning',) +CONTEXT: PL/Python function "elog_test" +ERROR: ('error',) +CONTEXT: PL/Python function "elog_test" diff --git a/src/pl/plpython/expected/plpython_trigger.out b/src/pl/plpython/expected/plpython_trigger.out index ac23b9c648..75914047ce 100644 --- a/src/pl/plpython/expected/plpython_trigger.out +++ b/src/pl/plpython/expected/plpython_trigger.out @@ -81,17 +81,43 @@ for key in skeys: return None $$; -CREATE TRIGGER show_trigger_data_trig +CREATE TRIGGER show_trigger_data_trig_before BEFORE INSERT OR UPDATE OR DELETE ON trigger_test FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); +CREATE TRIGGER show_trigger_data_trig_after +AFTER INSERT OR UPDATE OR DELETE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); +CREATE TRIGGER show_trigger_data_trig_stmt +BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON trigger_test +FOR EACH STATEMENT EXECUTE PROCEDURE trigger_data(23,'skidoo'); insert into trigger_test values(1,'insert'); NOTICE: ("TD[args] => ['23', 'skidoo']",) CONTEXT: PL/Python function "trigger_data" NOTICE: ('TD[event] => INSERT',) CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[level] => STATEMENT',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[name] => show_trigger_data_trig_stmt',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[new] => None',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[old] => None',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[relid] => bogus:12345',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[table_name] => trigger_test',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[table_schema] => public',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[when] => BEFORE',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ("TD[args] => ['23', 'skidoo']",) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[event] => INSERT',) +CONTEXT: PL/Python function "trigger_data" NOTICE: ('TD[level] => ROW',) CONTEXT: PL/Python function "trigger_data" -NOTICE: ('TD[name] => show_trigger_data_trig',) +NOTICE: ('TD[name] => show_trigger_data_trig_before',) CONTEXT: PL/Python function "trigger_data" NOTICE: ("TD[new] => {'i': 1, 'v': 'insert'}",) CONTEXT: PL/Python function "trigger_data" @@ -105,14 +131,54 @@ NOTICE: ('TD[table_schema] => public',) CONTEXT: PL/Python function "trigger_data" NOTICE: ('TD[when] => BEFORE',) CONTEXT: PL/Python function "trigger_data" +NOTICE: ("TD[args] => ['23', 'skidoo']",) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[event] => INSERT',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[level] => ROW',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[name] => show_trigger_data_trig_after',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ("TD[new] => {'i': 1, 'v': 'insert'}",) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[old] => None',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[relid] => bogus:12345',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[table_name] => trigger_test',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[table_schema] => public',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[when] => AFTER',) +CONTEXT: PL/Python function "trigger_data" update trigger_test set v = 'update' where i = 1; NOTICE: ("TD[args] => ['23', 'skidoo']",) CONTEXT: PL/Python function "trigger_data" NOTICE: ('TD[event] => UPDATE',) CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[level] => STATEMENT',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[name] => show_trigger_data_trig_stmt',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[new] => None',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[old] => None',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[relid] => bogus:12345',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[table_name] => trigger_test',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[table_schema] => public',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[when] => BEFORE',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ("TD[args] => ['23', 'skidoo']",) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[event] => UPDATE',) +CONTEXT: PL/Python function "trigger_data" NOTICE: ('TD[level] => ROW',) CONTEXT: PL/Python function "trigger_data" -NOTICE: ('TD[name] => show_trigger_data_trig',) +NOTICE: ('TD[name] => show_trigger_data_trig_before',) CONTEXT: PL/Python function "trigger_data" NOTICE: ("TD[new] => {'i': 1, 'v': 'update'}",) CONTEXT: PL/Python function "trigger_data" @@ -126,14 +192,74 @@ NOTICE: ('TD[table_schema] => public',) CONTEXT: PL/Python function "trigger_data" NOTICE: ('TD[when] => BEFORE',) CONTEXT: PL/Python function "trigger_data" +NOTICE: ("TD[args] => ['23', 'skidoo']",) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[event] => UPDATE',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[level] => ROW',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[name] => show_trigger_data_trig_after',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ("TD[new] => {'i': 1, 'v': 'update'}",) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ("TD[old] => {'i': 1, 'v': 'insert'}",) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[relid] => bogus:12345',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[table_name] => trigger_test',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[table_schema] => public',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[when] => AFTER',) +CONTEXT: PL/Python function "trigger_data" delete from trigger_test; NOTICE: ("TD[args] => ['23', 'skidoo']",) CONTEXT: PL/Python function "trigger_data" NOTICE: ('TD[event] => DELETE',) CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[level] => STATEMENT',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[name] => show_trigger_data_trig_stmt',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[new] => None',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[old] => None',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[relid] => bogus:12345',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[table_name] => trigger_test',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[table_schema] => public',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[when] => BEFORE',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ("TD[args] => ['23', 'skidoo']",) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[event] => DELETE',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[level] => ROW',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[name] => show_trigger_data_trig_before',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[new] => None',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ("TD[old] => {'i': 1, 'v': 'update'}",) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[relid] => bogus:12345',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[table_name] => trigger_test',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[table_schema] => public',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[when] => BEFORE',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ("TD[args] => ['23', 'skidoo']",) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[event] => DELETE',) +CONTEXT: PL/Python function "trigger_data" NOTICE: ('TD[level] => ROW',) CONTEXT: PL/Python function "trigger_data" -NOTICE: ('TD[name] => show_trigger_data_trig',) +NOTICE: ('TD[name] => show_trigger_data_trig_after',) CONTEXT: PL/Python function "trigger_data" NOTICE: ('TD[new] => None',) CONTEXT: PL/Python function "trigger_data" @@ -145,9 +271,154 @@ NOTICE: ('TD[table_name] => trigger_test',) CONTEXT: PL/Python function "trigger_data" NOTICE: ('TD[table_schema] => public',) CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[when] => AFTER',) +CONTEXT: PL/Python function "trigger_data" +truncate table trigger_test; +NOTICE: ("TD[args] => ['23', 'skidoo']",) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[event] => TRUNCATE',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[level] => STATEMENT',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[name] => show_trigger_data_trig_stmt',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[new] => None',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[old] => None',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[relid] => bogus:12345',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[table_name] => trigger_test',) +CONTEXT: PL/Python function "trigger_data" +NOTICE: ('TD[table_schema] => public',) +CONTEXT: PL/Python function "trigger_data" NOTICE: ('TD[when] => BEFORE',) CONTEXT: PL/Python function "trigger_data" - -DROP TRIGGER show_trigger_data_trig on trigger_test; - -DROP FUNCTION trigger_data(); +DROP FUNCTION trigger_data() CASCADE; +NOTICE: drop cascades to 3 other objects +DETAIL: drop cascades to trigger show_trigger_data_trig_before on table trigger_test +drop cascades to trigger show_trigger_data_trig_after on table trigger_test +drop cascades to trigger show_trigger_data_trig_stmt on table 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 plpythonu; +CREATE TRIGGER stupid_trigger1 +BEFORE INSERT ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE stupid1(); +INSERT INTO trigger_test VALUES (1, 'one'); +ERROR: unexpected return value from trigger procedure +DETAIL: Expected None or a string. +CONTEXT: PL/Python function "stupid1" +DROP TRIGGER stupid_trigger1 ON trigger_test; +-- returning MODIFY from DELETE trigger +CREATE FUNCTION stupid2() RETURNS trigger +AS $$ + return "MODIFY" +$$ LANGUAGE plpythonu; +CREATE TRIGGER stupid_trigger2 +BEFORE DELETE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE stupid2(); +DELETE FROM trigger_test WHERE i = 0; +WARNING: PL/Python trigger function returned "MODIFY" in a DELETE trigger -- ignored +CONTEXT: PL/Python function "stupid2" +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 plpythonu; +CREATE TRIGGER stupid_trigger3 +BEFORE UPDATE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE stupid3(); +UPDATE trigger_test SET v = 'null' WHERE i = 0; +ERROR: unexpected return value from trigger procedure +DETAIL: Expected None, "OK", "SKIP", or "MODIFY". +CONTEXT: PL/Python function "stupid3" +DROP TRIGGER stupid_trigger3 ON trigger_test; +-- deleting the TD dictionary +CREATE FUNCTION stupid4() RETURNS trigger +AS $$ + del TD["new"] + return "MODIFY"; +$$ LANGUAGE plpythonu; +CREATE TRIGGER stupid_trigger4 +BEFORE UPDATE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE stupid4(); +UPDATE trigger_test SET v = 'null' WHERE i = 0; +ERROR: TD["new"] deleted, cannot modify row +CONTEXT: PL/Python function "stupid4" +DROP TRIGGER stupid_trigger4 ON trigger_test; +-- TD not a dictionary +CREATE FUNCTION stupid5() RETURNS trigger +AS $$ + TD["new"] = ['foo', 'bar'] + return "MODIFY"; +$$ LANGUAGE plpythonu; +CREATE TRIGGER stupid_trigger5 +BEFORE UPDATE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE stupid5(); +UPDATE trigger_test SET v = 'null' WHERE i = 0; +ERROR: TD["new"] is not a dictionary +CONTEXT: PL/Python function "stupid5" +DROP TRIGGER stupid_trigger5 ON trigger_test; +-- TD not having string keys +CREATE FUNCTION stupid6() RETURNS trigger +AS $$ + TD["new"] = {1: 'foo', 2: 'bar'} + return "MODIFY"; +$$ LANGUAGE plpythonu; +CREATE TRIGGER stupid_trigger6 +BEFORE UPDATE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE stupid6(); +UPDATE trigger_test SET v = 'null' WHERE i = 0; +ERROR: TD["new"] dictionary key at ordinal position 0 is not a string +CONTEXT: PL/Python function "stupid6" +DROP TRIGGER stupid_trigger6 ON trigger_test; +-- TD keys not corresponding to row columns +CREATE FUNCTION stupid7() RETURNS trigger +AS $$ + TD["new"] = {'a': 'foo', 'b': 'bar'} + return "MODIFY"; +$$ LANGUAGE plpythonu; +CREATE TRIGGER stupid_trigger7 +BEFORE UPDATE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE stupid7(); +UPDATE trigger_test SET v = 'null' WHERE i = 0; +ERROR: key "a" found in TD["new"] does not exist as a column in the triggering row +CONTEXT: PL/Python function "stupid7" +DROP TRIGGER stupid_trigger7 ON trigger_test; +-- calling a trigger function directly +SELECT stupid7(); +ERROR: trigger functions can only be called as triggers +-- +-- Null values +-- +SELECT * FROM trigger_test; + i | v +---+------ + 0 | zero +(1 row) + +CREATE FUNCTION test_null() RETURNS trigger +AS $$ + TD["new"]['v'] = None + return "MODIFY" +$$ LANGUAGE plpythonu; +CREATE TRIGGER test_null_trigger +BEFORE UPDATE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE test_null(); +UPDATE trigger_test SET v = 'null' WHERE i = 0; +DROP TRIGGER test_null_trigger ON trigger_test; +SELECT * FROM trigger_test; + i | v +---+--- + 0 | +(1 row) + diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c index e68c89080c..c52e67e662 100644 --- a/src/pl/plpython/plpython.c +++ b/src/pl/plpython/plpython.c @@ -1,7 +1,7 @@ /********************************************************************** * plpython.c - python as a procedural language for PostgreSQL * - * $PostgreSQL: pgsql/src/pl/plpython/plpython.c,v 1.123 2009/07/20 08:01:06 petere Exp $ + * $PostgreSQL: pgsql/src/pl/plpython/plpython.c,v 1.124 2009/08/13 20:50:05 petere Exp $ * ********************************************************************* */ @@ -538,7 +538,7 @@ PLy_modify_tuple(PLyProcedure *proc, PyObject *pltd, TriggerData *tdata, platt = PyList_GetItem(plkeys, i); if (!PyString_Check(platt)) ereport(ERROR, - (errmsg("name of TD[\"new\"] attribute at ordinal position %d is not a string", i))); + (errmsg("TD[\"new\"] dictionary key at ordinal position %d is not a string", i))); attn = SPI_fnumber(tupdesc, PyString_AsString(platt)); if (attn == SPI_ERROR_NOATTRIBUTE) ereport(ERROR, diff --git a/src/pl/plpython/sql/plpython_params.sql b/src/pl/plpython/sql/plpython_params.sql index f8b610b505..d97e0b8549 100644 --- a/src/pl/plpython/sql/plpython_params.sql +++ b/src/pl/plpython/sql/plpython_params.sql @@ -31,4 +31,5 @@ $$ LANGUAGE plpythonu; 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_record.sql b/src/pl/plpython/sql/plpython_record.sql index 5bd8102107..5a41565a9d 100644 --- a/src/pl/plpython/sql/plpython_record.sql +++ b/src/pl/plpython/sql/plpython_record.sql @@ -2,6 +2,17 @@ -- 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 @@ -102,3 +113,28 @@ SELECT * FROM test_in_out_params('test_in'); -- this doesn't work yet :-( SELECT * FROM test_in_out_params_multi('test_in'); SELECT * FROM test_inout_params('test_in'); + + +-- errors cases + +CREATE FUNCTION test_type_record_error1() RETURNS type_record AS $$ + return { 'first': 'first' } +$$ LANGUAGE plpythonu; + +SELECT * FROM test_type_record_error1(); + + +CREATE FUNCTION test_type_record_error2() RETURNS type_record AS $$ + return [ 'first' ] +$$ LANGUAGE plpythonu; + +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 plpythonu; + +SELECT * FROM test_type_record_error3(); diff --git a/src/pl/plpython/sql/plpython_schema.sql b/src/pl/plpython/sql/plpython_schema.sql index 92681c1662..1cb84d620e 100644 --- a/src/pl/plpython/sql/plpython_schema.sql +++ b/src/pl/plpython/sql/plpython_schema.sql @@ -38,13 +38,3 @@ CREATE TABLE xsequences ( sequence text not null ) ; CREATE INDEX xsequences_pid_idx ON xsequences(pid) ; - -CREATE TABLE table_record ( - first text, - second int4 - ) ; - -CREATE TYPE type_record AS ( - first text, - second int4 - ) ; diff --git a/src/pl/plpython/sql/plpython_setof.sql b/src/pl/plpython/sql/plpython_setof.sql index 881d90222f..e036d569f2 100644 --- a/src/pl/plpython/sql/plpython_setof.sql +++ b/src/pl/plpython/sql/plpython_setof.sql @@ -2,6 +2,13 @@ -- Test returning SETOF -- +CREATE FUNCTION test_setof_error() RETURNS SETOF text AS $$ +return 37 +$$ LANGUAGE plpythonu; + +SELECT test_setof_error(); + + CREATE FUNCTION test_setof_as_list(count integer, content text) RETURNS SETOF text AS $$ return [ content ]*count $$ LANGUAGE plpythonu; diff --git a/src/pl/plpython/sql/plpython_spi.sql b/src/pl/plpython/sql/plpython_spi.sql index 866a0d5feb..c663298525 100644 --- a/src/pl/plpython/sql/plpython_spi.sql +++ b/src/pl/plpython/sql/plpython_spi.sql @@ -1,5 +1,4 @@ - -- nested calls -- diff --git a/src/pl/plpython/sql/plpython_test.sql b/src/pl/plpython/sql/plpython_test.sql index 38e236f146..d45299420f 100644 --- a/src/pl/plpython/sql/plpython_test.sql +++ b/src/pl/plpython/sql/plpython_test.sql @@ -19,3 +19,18 @@ return words' LANGUAGE plpythonu; select argument_test_one(users, fname, lname) from users where lname = 'doe' order by 1; + + +CREATE FUNCTION elog_test() RETURNS void +AS $$ +plpy.debug('debug') +plpy.log('log') +plpy.info('info') +plpy.info(37) +plpy.info('info', 37, [1, 2, 3]) +plpy.notice('notice') +plpy.warning('warning') +plpy.error('error') +$$ LANGUAGE plpythonu; + +SELECT elog_test(); diff --git a/src/pl/plpython/sql/plpython_trigger.sql b/src/pl/plpython/sql/plpython_trigger.sql index b042ae926e..ce1a737a84 100644 --- a/src/pl/plpython/sql/plpython_trigger.sql +++ b/src/pl/plpython/sql/plpython_trigger.sql @@ -82,14 +82,174 @@ return None $$; -CREATE TRIGGER show_trigger_data_trig +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; - -DROP TRIGGER show_trigger_data_trig on trigger_test; - -DROP FUNCTION trigger_data(); +truncate table trigger_test; + +DROP FUNCTION trigger_data() CASCADE; + + +-- +-- 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 plpythonu; + +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 plpythonu; + +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 plpythonu; + +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; + + +-- deleting the TD dictionary + +CREATE FUNCTION stupid4() RETURNS trigger +AS $$ + del TD["new"] + return "MODIFY"; +$$ LANGUAGE plpythonu; + +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 plpythonu; + +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 plpythonu; + +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"] = {'a': 'foo', 'b': 'bar'} + return "MODIFY"; +$$ LANGUAGE plpythonu; + +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; + + +-- 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 plpythonu; + +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; -- 2.11.0