CREATE LANGUAGE plperlu; CREATE SCHEMA tools; \set ECHO none -- check simple inserts/updates -- CREATE TEMP TABLE test1(id INTEGER); CREATE TRIGGER test1_allow_trigger BEFORE INSERT OR UPDATE ON test1 FOR EACH ROW EXECUTE PROCEDURE allow_on_condition('Less than 5', 'Argument should be less than 5', '%s < 5', 'NEW.id'); -- allow -- INSERT INTO test1 VALUES(1); -- deny -- INSERT INTO test1 VALUES(10); ERROR: error from Perl trigger function: error from Perl function: Argument should be less than 5 (assertion Less than 5) at line 5. at line 36. -- deny -- UPDATE test1 SET id = 5 WHERE id = 1; ERROR: error from Perl trigger function: error from Perl function: Argument should be less than 5 (assertion Less than 5) at line 5. at line 36. DROP TRIGGER test1_allow_trigger ON test1; DROP TABLE test1; -- check non-standard column names -- CREATE TEMP TABLE test2("id'\" INTEGER); INSERT INTO test2 VALUES(1); CREATE TRIGGER test2_allow_trigger BEFORE INSERT OR UPDATE ON test2 FOR EACH ROW EXECUTE PROCEDURE allow_on_condition('42', 'New value is not 42', '%s = 42', E'NEW.id''\\'); -- deny -- INSERT INTO test2 VALUES(30); ERROR: error from Perl trigger function: error from Perl function: New value is not 42 (assertion 42) at line 5. at line 36. -- allow -- INSERT INTO test2 VALUES(42); -- allow -- UPDATE test2 SET "id'\" = 42; DROP TRIGGER test2_allow_trigger ON test2; DROP TABLE test2; -- check string data -- CREATE TEMP TABLE test3(name VARCHAR); CREATE TRIGGER test3_allow_trigger BEFORE INSERT OR UPDATE on test3 FOR EACH ROW EXECUTE PROCEDURE allow_on_condition('PostgreSQL is free', E'string should be \'PostgreSQL is free\'', 'lower(%s) = lower(''PostgreSQL is free'')', 'NEW.name'); -- deny -- INSERT INTO test3 VALUES('PosgreSQL is fun'); ERROR: error from Perl trigger function: error from Perl function: string should be 'PostgreSQL is free' (assertion PostgreSQL is free) at line 5. at line 36. -- allow -- INSERT INTO test3 VALUES('PostgreSQL is free'); -- deny, but shouldn't fail -- INSERT INTO test3 VALUES(E'PostgreSQL supports "escape" strings to display characters like \\ or \''); ERROR: error from Perl trigger function: error from Perl function: string should be 'PostgreSQL is free' (assertion PostgreSQL is free) at line 5. at line 36. DROP TRIGGER test3_allow_trigger ON test3; DROP TABLE test3; -- check DELETEs -- CREATE TEMP TABLE test4(id INTEGER, date date); CREATE TRIGGER test4_allow_trigger BEFORE DELETE ON test4 FOR EACH ROW EXECUTE PROCEDURE allow_on_condition('Before July 4th 2009', '', '%s < ''July 04, 2009''::date', 'OLD.date'); -- populate the table with data -- INSERT INTO test4 VALUES(1, 'July 05, 2009'::date); INSERT INTO test4 VALUES(2, 'July 03, 2009'::date); -- deny -- DELETE FROM test4 WHERE id = 1; ERROR: error from Perl trigger function: error from Perl function: Assertion message is NULL (assertion Before July 4th 2009) at line 5. at line 36. -- allow -- DELETE FROM test4 WHERE id = 2; DROP TRIGGER test4_allow_trigger ON test4; DROP TABLE test4; DROP SCHEMA tools CASCADE; NOTICE: drop cascades to function tools.assert(text,boolean,text) DROP LANGUAGE plperlu CASCADE; NOTICE: drop cascades to function allow_on_condition()