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