CREATE LANGUAGE plperlu;

\set ECHO none
\i allow_on_condition.sql

--SET client_min_messages=debug;

\set ECHO all

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

-- deny --
UPDATE test1 SET id = 5 WHERE id = 1;

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

-- 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');
-- 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 \'');
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;

-- allow --
DELETE FROM test4 WHERE id = 2;

DROP TRIGGER test4_allow_trigger ON test4;
DROP TABLE test4;

DROP LANGUAGE plperlu CASCADE;