CREATE LANGUAGE plperlu; \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('%s < 5', 'NEW.id'); -- allow -- INSERT INTO test1 VALUES(1); -- deny -- INSERT INTO test1 VALUES(10); ERROR: expression 10 < 5 is false, INSERT is not allowed at line 40. CONTEXT: PL/Perl function "allow_on_condition" -- deny -- UPDATE test1 SET id = 5 WHERE id = 1; ERROR: expression 5 < 5 is false, UPDATE is not allowed at line 40. CONTEXT: PL/Perl function "allow_on_condition" 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('%s = 42', E'NEW.id''\\'); -- deny -- INSERT INTO test2 VALUES(30); ERROR: expression 30 = 42 is false, INSERT is not allowed at line 40. CONTEXT: PL/Perl function "allow_on_condition" -- 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('lower(%s) = lower(''PostgreSQL is free'')', 'NEW.name'); -- deny -- INSERT INTO test3 VALUES('PosgreSQL is fun'); ERROR: expression lower(E'PosgreSQL is fun') = lower('PostgreSQL is free') is false, INSERT is not allowed at line 40. CONTEXT: PL/Perl function "allow_on_condition" -- 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: expression lower(E'PostgreSQL supports "escape" strings to display characters like \\ or \'') = lower('PostgreSQL is free') is false, INSERT is not allowed at line 40. CONTEXT: PL/Perl function "allow_on_condition" 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('%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: expression E'07-05-2009' < 'July 04, 2009'::date is false, DELETE is not allowed at line 40. CONTEXT: PL/Perl function "allow_on_condition" -- allow -- DELETE FROM test4 WHERE id = 2; DROP TRIGGER test4_allow_trigger ON test4; DROP TABLE test4; DROP LANGUAGE plperlu CASCADE; NOTICE: drop cascades to function allow_on_condition()