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()