CREATE OR REPLACE FUNCTION check_trigger_data() RETURNS TRIGGER LANGUAGE python AS $python$ import Postgres def check_td(td, orientation, timing, dm): assert td.args == ('arg1','arg2','arg3') assert td.orientation == orientation assert td.timing == timing assert td.manipulation == dm assert td.trigger_name.startswith("trigger_checker_") assert td.trigger_catalog == Postgres.current_database assert td.table_catalog == Postgres.current_database assert td.trigger_schema == td.table_schema assert td.trigger_schema == "public" assert td.table_name == "wood" assert isinstance(td.type, Postgres.Type) assert td.type.typname == "wood" assert len(td.type.descriptor) == 1 assert str(td.type(('reltype',))[0]) == 'reltype' def before_delete(td, old): check_td(td, "ROW", "BEFORE", "DELETE") def after_delete(td, old): check_td(td, "ROW", "AFTER", "DELETE") def before_insert(td, new): check_td(td, "ROW", "BEFORE", "INSERT") def after_insert(td, new): check_td(td, "ROW", "AFTER", "INSERT") def before_update(td, old, new): check_td(td, "ROW", "BEFORE", "UPDATE") def after_update(td, old, new): check_td(td, "ROW", "AFTER", "UPDATE") ## statement level ## def before_truncate_statement(td): check_td(td, "STATEMENT", "BEFORE", "TRUNCATE") def after_truncate_statement(td, olds): assert olds is None check_td(td, "STATEMENT", "AFTER", "TRUNCATE") def before_delete_statement(td): check_td(td, "STATEMENT", "BEFORE", "DELETE") def after_delete_statement(td, olds): assert olds is None check_td(td, "STATEMENT", "AFTER", "DELETE") def before_insert_statement(td): check_td(td, "STATEMENT", "BEFORE", "INSERT") def after_insert_statement(td, news): assert news is None check_td(td, "STATEMENT", "AFTER", "INSERT") def before_update_statement(td): check_td(td, "STATEMENT", "BEFORE", "UPDATE") def after_update_statement(td, olds, news): assert olds is None assert news is None check_td(td, "STATEMENT", "AFTER", "UPDATE") $python$; CREATE OR REPLACE FUNCTION stop_event() RETURNS TRIGGER LANGUAGE python AS $python$ from Postgres import StopEvent def f(*args): raise StopEvent() before_insert = before_delete = before_update = f after_insert = after_delete = after_update = f $python$; CREATE OR REPLACE FUNCTION pass() RETURNS TRIGGER LANGUAGE python AS $python$ def f(*args): pass before_insert = before_delete = before_update = f after_insert = after_delete = after_update = f $python$; CREATE OR REPLACE FUNCTION return_new() RETURNS TRIGGER LANGUAGE python AS $python$ def before_update(td, old, new): return new def before_insert(td, new): return new $python$; CREATE OR REPLACE FUNCTION return_old() RETURNS TRIGGER LANGUAGE python AS $python$ def before_update(td, old, new): return old def before_delete(td, old): return old $python$; CREATE OR REPLACE FUNCTION raise_error() RETURNS TRIGGER LANGUAGE python AS $python$ def f(*args): raise ValueError("dirty data. dirty") before_insert = before_update = before_delete = \ after_insert = after_update = after_delete = f $python$; CREATE OR REPLACE FUNCTION insert_instead () RETURNS TRIGGER LANGUAGE python AS $python$ def before_update(td, *args): return ("instead",) after_update = after_insert = before_insert = before_update $python$; CREATE TABLE wood (info text); INSERT INTO wood VALUES ('already there'); SELECT * FROM wood ORDER BY info; -- CREATE TRIGGER dontdelete BEFORE DELETE ON wood FOR EACH ROW EXECUTE PROCEDURE stop_event(); DELETE FROM wood; DROP TRIGGER dontdelete ON wood; SELECT *, 'should exist' FROM wood ORDER BY info; -- CREATE TRIGGER dodelete BEFORE DELETE ON wood FOR EACH ROW EXECUTE PROCEDURE pass(); DELETE FROM wood; DROP TRIGGER dodelete ON wood; SELECT *, 'FAILURE WHEN ROWS PRESENT' FROM wood ORDER BY info; -- INSERT INTO wood VALUES ('already there'); CREATE TRIGGER dodelete BEFORE DELETE ON wood FOR EACH ROW EXECUTE PROCEDURE return_old(); DELETE FROM wood; DROP TRIGGER dodelete ON wood; SELECT *, 'FAILURE WHEN ROWS PRESENT' FROM wood ORDER BY info; -- CREATE TRIGGER raise_error BEFORE INSERT ON wood FOR EACH ROW EXECUTE PROCEDURE raise_error(); CREATE TRIGGER insert_instead BEFORE INSERT ON wood FOR EACH ROW EXECUTE PROCEDURE insert_instead(); INSERT INTO wood VALUES ('should never exist'); DROP TRIGGER insert_instead ON wood; DROP TRIGGER raise_error ON wood; SELECT * FROM wood ORDER BY info; -- INSERT INTO wood VALUES ('not updated'); CREATE TRIGGER dont_update BEFORE UPDATE ON wood FOR EACH ROW EXECUTE PROCEDURE stop_event(); UPDATE wood SET info = 'updated' WHERE info = 'not updated'; DROP TRIGGER dont_update ON wood; -- Should show "info: updated" SELECT * FROM wood ORDER BY info; -- -- Test return_new on UPDATE CREATE TRIGGER do_update BEFORE UPDATE ON wood FOR EACH ROW EXECUTE PROCEDURE return_new(); UPDATE wood SET info = 'updated' WHERE info = 'not updated'; DROP TRIGGER do_update ON wood; -- Should show "info: updated" SELECT * FROM wood; -- -- Test different tuple returned on UPDATE CREATE TRIGGER do_update_instead BEFORE UPDATE ON wood FOR EACH ROW EXECUTE PROCEDURE insert_instead(); UPDATE wood SET info = 'SHOULD NOT BE SET' WHERE info = 'updated'; DROP TRIGGER do_update_instead ON wood; -- Should show "info: instead" SELECT * FROM wood; -- AFTER -- -- Can't stop events afterwards CREATE TRIGGER try_not_delete AFTER DELETE ON wood FOR EACH ROW EXECUTE PROCEDURE stop_event(); DELETE FROM wood; DROP TRIGGER try_not_delete ON wood; -- Ignored CREATE TRIGGER try_insert_instead AFTER INSERT ON wood FOR EACH ROW EXECUTE PROCEDURE insert_instead(); INSERT INTO wood VALUES ('SHOULD EXIST'); DROP TRIGGER try_insert_instead ON wood; -- Ignored CREATE TRIGGER try_update_instead AFTER UPDATE ON wood FOR EACH ROW EXECUTE PROCEDURE insert_instead(); UPDATE wood SET info = 'SHOULD BE UPDATED'; DROP TRIGGER try_update_instead ON wood; -- -- Check Postgres.TriggerData -- -- CREATE TRIGGER trigger_checker_befores BEFORE DELETE OR UPDATE OR INSERT ON wood FOR EACH ROW EXECUTE PROCEDURE check_trigger_data('arg1','arg2','arg3'); CREATE TRIGGER trigger_checker_afters AFTER DELETE OR UPDATE OR INSERT ON wood FOR EACH ROW EXECUTE PROCEDURE check_trigger_data('arg1','arg2','arg3'); CREATE TRIGGER trigger_checker_statement_befores BEFORE DELETE OR UPDATE OR INSERT OR TRUNCATE ON wood FOR STATEMENT EXECUTE PROCEDURE check_trigger_data('arg1','arg2','arg3'); CREATE TRIGGER trigger_checker_statement_afters AFTER DELETE OR UPDATE OR INSERT OR TRUNCATE ON wood FOR STATEMENT EXECUTE PROCEDURE check_trigger_data('arg1','arg2','arg3'); -- Run outside & inside a transaction and, for paranoia, inside a savepoint. -- The trigger data is cached, so it's important to identify that new trigger -- data is being constructed for each orientation, timing, and manipulation. INSERT INTO wood VALUES ('foo'); UPDATE wood SET info = 'bar' WHERE info = 'foo'; DELETE FROM wood; TRUNCATE wood; BEGIN; INSERT INTO wood VALUES ('foo'); UPDATE wood SET info = 'bar' WHERE info = 'foo'; DELETE FROM wood; TRUNCATE wood; COMMIT; BEGIN; SAVEPOINT a; INSERT INTO wood VALUES ('foo'); UPDATE wood SET info = 'bar' WHERE info = 'foo'; DELETE FROM wood; TRUNCATE wood; RELEASE a; COMMIT; DROP TRIGGER trigger_checker_statement_afters ON wood; DROP TRIGGER trigger_checker_statement_befores ON wood; DROP TRIGGER trigger_checker_afters ON wood; DROP TRIGGER trigger_checker_befores ON wood; -- validate that trigger name is being validated -- "trigger_checker" is off by one character, '_' CREATE TRIGGER trigger_checker BEFORE DELETE OR UPDATE OR INSERT ON wood FOR EACH ROW EXECUTE PROCEDURE check_trigger_data('arg1','arg2','arg3'); INSERT INTO wood VALUES ('WRONG'); DROP TRIGGER trigger_checker ON wood; -- validate that trigger args are being checked -- the check function expects three arguments: arg1, arg2, and arg3 CREATE TRIGGER trigger_checker_ BEFORE DELETE OR UPDATE OR INSERT ON wood FOR EACH ROW EXECUTE PROCEDURE check_trigger_data('arg1','arg2'); INSERT INTO wood VALUES ('WRONG'); DROP TRIGGER trigger_checker_ ON wood;