~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security

-- these triggers are dedicated to HPHC of RI who
-- decided that my kid's name was william not willem, and
-- vigorously resisted all efforts at correction.  they have
-- since gone bankrupt...
CREATE FUNCTION users_insert() returns trigger
	AS
'if TD["new"]["fname"] == None or TD["new"]["lname"] == None:
	return "SKIP"
if TD["new"]["username"] == None:
	TD["new"]["username"] = TD["new"]["fname"][:1] + "_" + TD["new"]["lname"]
	rv = "MODIFY"
else:
	rv = None
if TD["new"]["fname"] == "william":
	TD["new"]["fname"] = TD["args"][0]
	rv = "MODIFY"
return rv'
	LANGUAGE plpythonu;
CREATE FUNCTION users_update() returns trigger
	AS
'if TD["event"] == "UPDATE":
	if TD["old"]["fname"] != TD["new"]["fname"] and TD["old"]["fname"] == TD["args"][0]:
		return "SKIP"
return None'
	LANGUAGE plpythonu;
CREATE FUNCTION users_delete() RETURNS trigger
	AS
'if TD["old"]["fname"] == TD["args"][0]:
	return "SKIP"
return None'
	LANGUAGE plpythonu;
CREATE TRIGGER users_insert_trig BEFORE INSERT ON users FOR EACH ROW
	EXECUTE PROCEDURE users_insert ('willem');
CREATE TRIGGER users_update_trig BEFORE UPDATE ON users FOR EACH ROW
	EXECUTE PROCEDURE users_update ('willem');
CREATE TRIGGER users_delete_trig BEFORE DELETE ON users FOR EACH ROW
	EXECUTE PROCEDURE users_delete ('willem');
-- quick peek at the table
--
SELECT * FROM users;
 fname  | lname | username | userid 
--------+-------+----------+--------
 jane   | doe   | j_doe    |      1
 john   | doe   | johnd    |      2
 willem | doe   | w_doe    |      3
 rick   | smith | slash    |      4
(4 rows)

-- should fail
--
UPDATE users SET fname = 'william' WHERE fname = 'willem';
-- should modify william to willem and create username
--
INSERT INTO users (fname, lname) VALUES ('william', 'smith');
INSERT INTO users (fname, lname, username) VALUES ('charles', 'darwin', 'beagle');
SELECT * FROM users;
  fname  | lname  | username | userid 
---------+--------+----------+--------
 jane    | doe    | j_doe    |      1
 john    | doe    | johnd    |      2
 willem  | doe    | w_doe    |      3
 rick    | smith  | slash    |      4
 willem  | smith  | w_smith  |      5
 charles | darwin | beagle   |      6
(6 rows)

-- dump trigger data
CREATE TABLE trigger_test
	(i int, v text );
CREATE FUNCTION trigger_data() RETURNS trigger LANGUAGE plpythonu AS $$

if 'relid' in TD:
	TD['relid'] = "bogus:12345"

skeys = list(TD.keys())
skeys.sort()
for key in skeys:
	val = TD[key]
	plpy.notice("TD[" + key + "] => " + str(val))

return None

$$;
CREATE TRIGGER show_trigger_data_trig_before
BEFORE INSERT OR UPDATE OR DELETE ON trigger_test
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
CREATE TRIGGER show_trigger_data_trig_after
AFTER INSERT OR UPDATE OR DELETE ON trigger_test
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
CREATE TRIGGER show_trigger_data_trig_stmt
BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON trigger_test
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_data(23,'skidoo');
insert into trigger_test values(1,'insert');
NOTICE:  TD[args] => ['23', 'skidoo']
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[event] => INSERT
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[level] => STATEMENT
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[name] => show_trigger_data_trig_stmt
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[new] => None
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[old] => None
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[relid] => bogus:12345
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_name] => trigger_test
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_schema] => public
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[when] => BEFORE
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[args] => ['23', 'skidoo']
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[event] => INSERT
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[level] => ROW
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[name] => show_trigger_data_trig_before
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[new] => {'i': 1, 'v': 'insert'}
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[old] => None
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[relid] => bogus:12345
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_name] => trigger_test
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_schema] => public
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[when] => BEFORE
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[args] => ['23', 'skidoo']
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[event] => INSERT
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[level] => ROW
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[name] => show_trigger_data_trig_after
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[new] => {'i': 1, 'v': 'insert'}
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[old] => None
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[relid] => bogus:12345
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_name] => trigger_test
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_schema] => public
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[when] => AFTER
CONTEXT:  PL/Python function "trigger_data"
update trigger_test set v = 'update' where i = 1;
NOTICE:  TD[args] => ['23', 'skidoo']
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[event] => UPDATE
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[level] => STATEMENT
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[name] => show_trigger_data_trig_stmt
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[new] => None
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[old] => None
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[relid] => bogus:12345
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_name] => trigger_test
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_schema] => public
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[when] => BEFORE
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[args] => ['23', 'skidoo']
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[event] => UPDATE
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[level] => ROW
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[name] => show_trigger_data_trig_before
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[new] => {'i': 1, 'v': 'update'}
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[old] => {'i': 1, 'v': 'insert'}
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[relid] => bogus:12345
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_name] => trigger_test
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_schema] => public
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[when] => BEFORE
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[args] => ['23', 'skidoo']
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[event] => UPDATE
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[level] => ROW
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[name] => show_trigger_data_trig_after
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[new] => {'i': 1, 'v': 'update'}
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[old] => {'i': 1, 'v': 'insert'}
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[relid] => bogus:12345
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_name] => trigger_test
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_schema] => public
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[when] => AFTER
CONTEXT:  PL/Python function "trigger_data"
delete from trigger_test;
NOTICE:  TD[args] => ['23', 'skidoo']
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[event] => DELETE
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[level] => STATEMENT
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[name] => show_trigger_data_trig_stmt
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[new] => None
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[old] => None
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[relid] => bogus:12345
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_name] => trigger_test
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_schema] => public
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[when] => BEFORE
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[args] => ['23', 'skidoo']
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[event] => DELETE
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[level] => ROW
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[name] => show_trigger_data_trig_before
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[new] => None
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[old] => {'i': 1, 'v': 'update'}
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[relid] => bogus:12345
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_name] => trigger_test
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_schema] => public
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[when] => BEFORE
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[args] => ['23', 'skidoo']
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[event] => DELETE
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[level] => ROW
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[name] => show_trigger_data_trig_after
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[new] => None
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[old] => {'i': 1, 'v': 'update'}
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[relid] => bogus:12345
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_name] => trigger_test
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_schema] => public
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[when] => AFTER
CONTEXT:  PL/Python function "trigger_data"
truncate table trigger_test;
NOTICE:  TD[args] => ['23', 'skidoo']
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[event] => TRUNCATE
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[level] => STATEMENT
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[name] => show_trigger_data_trig_stmt
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[new] => None
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[old] => None
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[relid] => bogus:12345
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_name] => trigger_test
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_schema] => public
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[when] => BEFORE
CONTEXT:  PL/Python function "trigger_data"
DROP TRIGGER show_trigger_data_trig_stmt on trigger_test;
DROP TRIGGER show_trigger_data_trig_before on trigger_test;
DROP TRIGGER show_trigger_data_trig_after on trigger_test;
insert into trigger_test values(1,'insert');
CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test;
CREATE TRIGGER show_trigger_data_trig
INSTEAD OF INSERT OR UPDATE OR DELETE ON trigger_test_view
FOR EACH ROW EXECUTE PROCEDURE trigger_data(24,'skidoo view');
insert into trigger_test_view values(2,'insert');
NOTICE:  TD[args] => ['24', 'skidoo view']
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[event] => INSERT
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[level] => ROW
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[name] => show_trigger_data_trig
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[new] => {'i': 2, 'v': 'insert'}
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[old] => None
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[relid] => bogus:12345
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_name] => trigger_test_view
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_schema] => public
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[when] => INSTEAD OF
CONTEXT:  PL/Python function "trigger_data"
update trigger_test_view set v = 'update' where i = 1;
NOTICE:  TD[args] => ['24', 'skidoo view']
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[event] => UPDATE
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[level] => ROW
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[name] => show_trigger_data_trig
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[new] => {'i': 1, 'v': 'update'}
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[old] => {'i': 1, 'v': 'insert'}
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[relid] => bogus:12345
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_name] => trigger_test_view
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_schema] => public
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[when] => INSTEAD OF
CONTEXT:  PL/Python function "trigger_data"
delete from trigger_test_view;
NOTICE:  TD[args] => ['24', 'skidoo view']
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[event] => DELETE
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[level] => ROW
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[name] => show_trigger_data_trig
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[new] => None
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[old] => {'i': 1, 'v': 'insert'}
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[relid] => bogus:12345
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_name] => trigger_test_view
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[table_schema] => public
CONTEXT:  PL/Python function "trigger_data"
NOTICE:  TD[when] => INSTEAD OF
CONTEXT:  PL/Python function "trigger_data"
DROP FUNCTION trigger_data() CASCADE;
NOTICE:  drop cascades to trigger show_trigger_data_trig on view trigger_test_view
DROP VIEW trigger_test_view;
delete from trigger_test;
--
-- trigger error handling
--
INSERT INTO trigger_test VALUES (0, 'zero');
-- returning non-string from trigger function
CREATE FUNCTION stupid1() RETURNS trigger
AS $$
    return 37
$$ LANGUAGE plpythonu;
CREATE TRIGGER stupid_trigger1
BEFORE INSERT ON trigger_test
FOR EACH ROW EXECUTE PROCEDURE stupid1();
INSERT INTO trigger_test VALUES (1, 'one');
ERROR:  unexpected return value from trigger procedure
DETAIL:  Expected None or a string.
CONTEXT:  PL/Python function "stupid1"
DROP TRIGGER stupid_trigger1 ON trigger_test;
-- returning MODIFY from DELETE trigger
CREATE FUNCTION stupid2() RETURNS trigger
AS $$
    return "MODIFY"
$$ LANGUAGE plpythonu;
CREATE TRIGGER stupid_trigger2
BEFORE DELETE ON trigger_test
FOR EACH ROW EXECUTE PROCEDURE stupid2();
DELETE FROM trigger_test WHERE i = 0;
WARNING:  PL/Python trigger function returned "MODIFY" in a DELETE trigger -- ignored
CONTEXT:  PL/Python function "stupid2"
DROP TRIGGER stupid_trigger2 ON trigger_test;
INSERT INTO trigger_test VALUES (0, 'zero');
-- returning unrecognized string from trigger function
CREATE FUNCTION stupid3() RETURNS trigger
AS $$
    return "foo"
$$ LANGUAGE plpythonu;
CREATE TRIGGER stupid_trigger3
BEFORE UPDATE ON trigger_test
FOR EACH ROW EXECUTE PROCEDURE stupid3();
UPDATE trigger_test SET v = 'null' WHERE i = 0;
ERROR:  unexpected return value from trigger procedure
DETAIL:  Expected None, "OK", "SKIP", or "MODIFY".
CONTEXT:  PL/Python function "stupid3"
DROP TRIGGER stupid_trigger3 ON trigger_test;
-- Unicode variant
CREATE FUNCTION stupid3u() RETURNS trigger
AS $$
    return u"foo"
$$ LANGUAGE plpythonu;
CREATE TRIGGER stupid_trigger3
BEFORE UPDATE ON trigger_test
FOR EACH ROW EXECUTE PROCEDURE stupid3u();
UPDATE trigger_test SET v = 'null' WHERE i = 0;
ERROR:  unexpected return value from trigger procedure
DETAIL:  Expected None, "OK", "SKIP", or "MODIFY".
CONTEXT:  PL/Python function "stupid3u"
DROP TRIGGER stupid_trigger3 ON trigger_test;
-- deleting the TD dictionary
CREATE FUNCTION stupid4() RETURNS trigger
AS $$
    del TD["new"]
    return "MODIFY";
$$ LANGUAGE plpythonu;
CREATE TRIGGER stupid_trigger4
BEFORE UPDATE ON trigger_test
FOR EACH ROW EXECUTE PROCEDURE stupid4();
UPDATE trigger_test SET v = 'null' WHERE i = 0;
ERROR:  TD["new"] deleted, cannot modify row
CONTEXT:  while modifying trigger row
PL/Python function "stupid4"
DROP TRIGGER stupid_trigger4 ON trigger_test;
-- TD not a dictionary
CREATE FUNCTION stupid5() RETURNS trigger
AS $$
    TD["new"] = ['foo', 'bar']
    return "MODIFY";
$$ LANGUAGE plpythonu;
CREATE TRIGGER stupid_trigger5
BEFORE UPDATE ON trigger_test
FOR EACH ROW EXECUTE PROCEDURE stupid5();
UPDATE trigger_test SET v = 'null' WHERE i = 0;
ERROR:  TD["new"] is not a dictionary
CONTEXT:  while modifying trigger row
PL/Python function "stupid5"
DROP TRIGGER stupid_trigger5 ON trigger_test;
-- TD not having string keys
CREATE FUNCTION stupid6() RETURNS trigger
AS $$
    TD["new"] = {1: 'foo', 2: 'bar'}
    return "MODIFY";
$$ LANGUAGE plpythonu;
CREATE TRIGGER stupid_trigger6
BEFORE UPDATE ON trigger_test
FOR EACH ROW EXECUTE PROCEDURE stupid6();
UPDATE trigger_test SET v = 'null' WHERE i = 0;
ERROR:  TD["new"] dictionary key at ordinal position 0 is not a string
CONTEXT:  while modifying trigger row
PL/Python function "stupid6"
DROP TRIGGER stupid_trigger6 ON trigger_test;
-- TD keys not corresponding to row columns
CREATE FUNCTION stupid7() RETURNS trigger
AS $$
    TD["new"] = {'a': 'foo', 'b': 'bar'}
    return "MODIFY";
$$ LANGUAGE plpythonu;
CREATE TRIGGER stupid_trigger7
BEFORE UPDATE ON trigger_test
FOR EACH ROW EXECUTE PROCEDURE stupid7();
UPDATE trigger_test SET v = 'null' WHERE i = 0;
ERROR:  key "a" found in TD["new"] does not exist as a column in the triggering row
CONTEXT:  while modifying trigger row
PL/Python function "stupid7"
DROP TRIGGER stupid_trigger7 ON trigger_test;
-- Unicode variant
CREATE FUNCTION stupid7u() RETURNS trigger
AS $$
    TD["new"] = {u'a': 'foo', u'b': 'bar'}
    return "MODIFY"
$$ LANGUAGE plpythonu;
CREATE TRIGGER stupid_trigger7
BEFORE UPDATE ON trigger_test
FOR EACH ROW EXECUTE PROCEDURE stupid7u();
UPDATE trigger_test SET v = 'null' WHERE i = 0;
ERROR:  key "a" found in TD["new"] does not exist as a column in the triggering row
CONTEXT:  while modifying trigger row
PL/Python function "stupid7u"
DROP TRIGGER stupid_trigger7 ON trigger_test;
-- calling a trigger function directly
SELECT stupid7();
ERROR:  trigger functions can only be called as triggers
--
-- Null values
--
SELECT * FROM trigger_test;
 i |  v   
---+------
 0 | zero
(1 row)

CREATE FUNCTION test_null() RETURNS trigger
AS $$
    TD["new"]['v'] = None
    return "MODIFY"
$$ LANGUAGE plpythonu;
CREATE TRIGGER test_null_trigger
BEFORE UPDATE ON trigger_test
FOR EACH ROW EXECUTE PROCEDURE test_null();
UPDATE trigger_test SET v = 'null' WHERE i = 0;
DROP TRIGGER test_null_trigger ON trigger_test;
SELECT * FROM trigger_test;
 i | v 
---+---
 0 | 
(1 row)

--
-- Test that triggers honor typmod when assigning to tuple fields,
-- as per an early 9.0 bug report
--
SET DateStyle = 'ISO';
CREATE FUNCTION set_modif_time() RETURNS trigger AS $$
    TD['new']['modif_time'] = '2010-10-13 21:57:28.930486'
    return 'MODIFY'
$$ LANGUAGE plpythonu;
CREATE TABLE pb (a TEXT, modif_time TIMESTAMP(0) WITHOUT TIME ZONE);
CREATE TRIGGER set_modif_time BEFORE UPDATE ON pb
  FOR EACH ROW EXECUTE PROCEDURE set_modif_time();
INSERT INTO pb VALUES ('a', '2010-10-09 21:57:33.930486');
SELECT * FROM pb;
 a |     modif_time      
---+---------------------
 a | 2010-10-09 21:57:34
(1 row)

UPDATE pb SET a = 'b';
SELECT * FROM pb;
 a |     modif_time      
---+---------------------
 b | 2010-10-13 21:57:29
(1 row)

-- triggers for tables with composite types
CREATE TABLE comp1 (i integer, j boolean);
CREATE TYPE comp2 AS (k integer, l boolean);
CREATE TABLE composite_trigger_test (f1 comp1, f2 comp2);
CREATE FUNCTION composite_trigger_f() RETURNS trigger AS $$
    TD['new']['f1'] = (3, False)
    TD['new']['f2'] = {'k': 7, 'l': 'yes', 'ignored': 10}
    return 'MODIFY'
$$ LANGUAGE plpythonu;
CREATE TRIGGER composite_trigger BEFORE INSERT ON composite_trigger_test
  FOR EACH ROW EXECUTE PROCEDURE composite_trigger_f();
INSERT INTO composite_trigger_test VALUES (NULL, NULL);
SELECT * FROM composite_trigger_test;
  f1   |  f2   
-------+-------
 (3,f) | (7,t)
(1 row)

-- triggers with composite type columns (bug #6559)
CREATE TABLE composite_trigger_noop_test (f1 comp1, f2 comp2);
CREATE FUNCTION composite_trigger_noop_f() RETURNS trigger AS $$
    return 'MODIFY'
$$ LANGUAGE plpythonu;
CREATE TRIGGER composite_trigger_noop BEFORE INSERT ON composite_trigger_noop_test
  FOR EACH ROW EXECUTE PROCEDURE composite_trigger_noop_f();
INSERT INTO composite_trigger_noop_test VALUES (NULL, NULL);
INSERT INTO composite_trigger_noop_test VALUES (ROW(1, 'f'), NULL);
INSERT INTO composite_trigger_noop_test VALUES (ROW(NULL, 't'), ROW(1, 'f'));
SELECT * FROM composite_trigger_noop_test;
  f1   |  f2   
-------+-------
       | 
 (1,f) | 
 (,t)  | (1,f)
(3 rows)

-- nested composite types
CREATE TYPE comp3 AS (c1 comp1, c2 comp2, m integer);
CREATE TABLE composite_trigger_nested_test(c comp3);
CREATE FUNCTION composite_trigger_nested_f() RETURNS trigger AS $$
    return 'MODIFY'
$$ LANGUAGE plpythonu;
CREATE TRIGGER composite_trigger_nested BEFORE INSERT ON composite_trigger_nested_test
  FOR EACH ROW EXECUTE PROCEDURE composite_trigger_nested_f();
INSERT INTO composite_trigger_nested_test VALUES (NULL);
INSERT INTO composite_trigger_nested_test VALUES (ROW(ROW(1, 'f'), NULL, 3));
INSERT INTO composite_trigger_nested_test VALUES (ROW(ROW(NULL, 't'), ROW(1, 'f'), NULL));
SELECT * FROM composite_trigger_nested_test;
         c         
-------------------
 
 ("(1,f)",,3)
 ("(,t)","(1,f)",)
(3 rows)

-- check that using a function as a trigger over two tables works correctly
CREATE FUNCTION trig1234() RETURNS trigger LANGUAGE plpythonu AS $$
    TD["new"]["data"] = '1234'
    return 'MODIFY'
$$;
CREATE TABLE a(data text);
CREATE TABLE b(data int); -- different type conversion
CREATE TRIGGER a_t BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE trig1234();
CREATE TRIGGER b_t BEFORE INSERT ON b FOR EACH ROW EXECUTE PROCEDURE trig1234();
INSERT INTO a DEFAULT VALUES;
SELECT * FROM a;
 data 
------
 1234
(1 row)

DROP TABLE a;
INSERT INTO b DEFAULT VALUES;
SELECT * FROM b;
 data 
------
 1234
(1 row)