text: --
  -- TRIGGERS
  --
  
  create table pkeys (pkey1 int4 not null, pkey2 text not null);
  create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
  create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
  
  create index fkeys_i on fkeys (fkey1, fkey2);
  create index fkeys2_i on fkeys2 (fkey21, fkey22);
  create index fkeys2p_i on fkeys2 (pkey23);
  
  insert into pkeys values (10, '1');
  insert into pkeys values (20, '2');
  insert into pkeys values (30, '3');
  insert into pkeys values (40, '4');
  insert into pkeys values (50, '5');
  insert into pkeys values (60, '6');
  create unique index pkeys_i on pkeys (pkey1, pkey2);
  
  --
  -- For fkeys:
  -- 	(fkey1, fkey2)	--> pkeys (pkey1, pkey2)
  -- 	(fkey3)		--> fkeys2 (pkey23)
  --
  create trigger check_fkeys_pkey_exist
  	before insert or update on fkeys
  	for each row
  	execute procedure
  	check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
  
  create trigger check_fkeys_pkey2_exist
  	before insert or update on fkeys
  	for each row
  	execute procedure check_primary_key ('fkey3', 'fkeys2', 'pkey23');
  
  --
  -- For fkeys2:
  -- 	(fkey21, fkey22)	--> pkeys (pkey1, pkey2)
  --
  create trigger check_fkeys2_pkey_exist
  	before insert or update on fkeys2
  	for each row
  	execute procedure
  	check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
  
  -- Test comments
  COMMENT ON TRIGGER check_fkeys2_pkey_bad ON fkeys2 IS 'wrong';
  COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS 'right';
  COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS NULL;
  
  --
  -- For pkeys:
  -- 	ON DELETE/UPDATE (pkey1, pkey2) CASCADE:
  -- 		fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22)
  --
  create trigger check_pkeys_fkey_cascade
  	before delete or update on pkeys
  	for each row
  	execute procedure
  	check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',
  	'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22');
  
  --
  -- For fkeys2:
  -- 	ON DELETE/UPDATE (pkey23) RESTRICT:
  -- 		fkeys (fkey3)
  --
  create trigger check_fkeys2_fkey_restrict
  	before delete or update on fkeys2
  	for each row
  	execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');
  
  insert into fkeys2 values (10, '1', 1);
  insert into fkeys2 values (30, '3', 2);
  insert into fkeys2 values (40, '4', 5);
  insert into fkeys2 values (50, '5', 3);
  -- no key in pkeys
  insert into fkeys2 values (70, '5', 3);
  
  insert into fkeys values (10, '1', 2);
  insert into fkeys values (30, '3', 3);
  insert into fkeys values (40, '4', 2);
  insert into fkeys values (50, '5', 2);
  -- no key in pkeys
  insert into fkeys values (70, '5', 1);
  -- no key in fkeys2
  insert into fkeys values (60, '6', 4);
  
  delete from pkeys where pkey1 = 30 and pkey2 = '3';
  delete from pkeys where pkey1 = 40 and pkey2 = '4';
  update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
  update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
  
  DROP TABLE pkeys;
  DROP TABLE fkeys;
  DROP TABLE fkeys2;
  
  -- -- I've disabled the funny_dup17 test because the new semantics
  -- -- of AFTER ROW triggers, which get now fired at the end of a
  -- -- query always, cause funny_dup17 to enter an endless loop.
  -- --
  -- --      Jan
  --
  -- create table dup17 (x int4);
  --
  -- create trigger dup17_before
  -- 	before insert on dup17
  -- 	for each row
  -- 	execute procedure
  -- 	funny_dup17 ()
  -- ;
  --
  -- insert into dup17 values (17);
  -- select count(*) from dup17;
  -- insert into dup17 values (17);
  -- select count(*) from dup17;
  --
  -- drop trigger dup17_before on dup17;
  --
  -- create trigger dup17_after
  -- 	after insert on dup17
  -- 	for each row
  -- 	execute procedure
  -- 	funny_dup17 ()
  -- ;
  -- insert into dup17 values (13);
  -- select count(*) from dup17 where x = 13;
  -- insert into dup17 values (13);
  -- select count(*) from dup17 where x = 13;
  --
  -- DROP TABLE dup17;
  
  -- Check behavior when trigger returns unmodified trigtuple
  create table trigtest (f1 int, f2 text);
  
  create trigger trigger_return_old
  	before insert or delete or update on trigtest
  	for each row execute procedure trigger_return_old();
  
  insert into trigtest values(1, 'foo');
  select * from trigtest;
  update trigtest set f2 = f2 || 'bar';
  select * from trigtest;
  delete from trigtest;
  select * from trigtest;
  
  drop table trigtest;
  
  create sequence ttdummy_seq increment 10 start 0 minvalue 0;
  
  create table tttest (
  	price_id	int4,
  	price_val	int4,
  	price_on	int4,
  	price_off	int4 default 999999
  );
  
  create trigger ttdummy
  	before delete or update on tttest
  	for each row
  	execute procedure
  	ttdummy (price_on, price_off);
  
  create trigger ttserial
  	before insert or update on tttest
  	for each row
  	execute procedure
  	autoinc (price_on, ttdummy_seq);
  
  insert into tttest values (1, 1, null);
  insert into tttest values (2, 2, null);
  insert into tttest values (3, 3, 0);
  
  select * from tttest;
  delete from tttest where price_id = 2;
  select * from tttest;
  -- what do we see ?
  
  -- get current prices
  select * from tttest where price_off = 999999;
  
  -- change price for price_id == 3
  update tttest set price_val = 30 where price_id = 3;
  select * from tttest;
  
  -- now we want to change pric_id in ALL tuples
  -- this gets us not what we need
  update tttest set price_id = 5 where price_id = 3;
  select * from tttest;
  
  -- restore data as before last update:
  select set_ttdummy(0);
  delete from tttest where price_id = 5;
  update tttest set price_off = 999999 where price_val = 30;
  select * from tttest;
  
  -- and try change price_id now!
  update tttest set price_id = 5 where price_id = 3;
  select * from tttest;
  -- isn't it what we need ?
  
  select set_ttdummy(1);
  
  -- we want to correct some "date"
  update tttest set price_on = -1 where price_id = 1;
  -- but this doesn't work
  
  -- try in this way
  select set_ttdummy(0);
  update tttest set price_on = -1 where price_id = 1;
  select * from tttest;
  -- isn't it what we need ?
  
  -- get price for price_id == 5 as it was @ "date" 35
  select * from tttest where price_on <= 35 and price_off > 35 and price_id = 5;
  
  drop table tttest;
  drop sequence ttdummy_seq;
  
  --
  -- tests for per-statement triggers
  --
  
  CREATE TABLE log_table (tstamp timestamp default timeofday()::timestamp);
  
  CREATE TABLE main_table (a int, b int);
  
  COPY main_table (a,b) FROM stdin;
  5	10
  20	20
  30	10
  50	35
  80	15
  \.
  
  CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
  BEGIN
  	RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
  	RETURN NULL;
  END;';
  
  CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
  FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt');
  
  CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
  FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt');
  
  --
  -- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified,
  -- CREATE TRIGGER should default to 'FOR EACH STATEMENT'
  --
  CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_table
  EXECUTE PROCEDURE trigger_func('after_upd_stmt');
  
  CREATE TRIGGER after_upd_row_trig AFTER UPDATE ON main_table
  FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_row');
  
  INSERT INTO main_table DEFAULT VALUES;
  
  UPDATE main_table SET a = a + 1 WHERE b < 30;
  -- UPDATE that effects zero rows should still call per-statement trigger
  UPDATE main_table SET a = a + 2 WHERE b > 100;
  
  -- COPY should fire per-row and per-statement INSERT triggers
  COPY main_table (a, b) FROM stdin;
  30	40
  50	60
  \.
  
  SELECT * FROM main_table ORDER BY a, b;
  
  --
  -- test triggers with WHEN clause
  --
  
  CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table
  FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('modified_a');
  CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table
  FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE PROCEDURE trigger_func('modified_any');
  CREATE TRIGGER insert_a AFTER INSERT ON main_table
  FOR EACH ROW WHEN (NEW.a = 123) EXECUTE PROCEDURE trigger_func('insert_a');
  CREATE TRIGGER delete_a AFTER DELETE ON main_table
  FOR EACH ROW WHEN (OLD.a = 123) EXECUTE PROCEDURE trigger_func('delete_a');
  CREATE TRIGGER insert_when BEFORE INSERT ON main_table
  FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('insert_when');
  CREATE TRIGGER delete_when AFTER DELETE ON main_table
  FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('delete_when');
  INSERT INTO main_table (a) VALUES (123), (456);
  COPY main_table FROM stdin;
  123	999
  456	999
  \.
  DELETE FROM main_table WHERE a IN (123, 456);
  UPDATE main_table SET a = 50, b = 60;
  SELECT * FROM main_table ORDER BY a, b;
  SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
  SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
  SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any';
  DROP TRIGGER modified_a ON main_table;
  DROP TRIGGER modified_any ON main_table;
  DROP TRIGGER insert_a ON main_table;
  DROP TRIGGER delete_a ON main_table;
  DROP TRIGGER insert_when ON main_table;
  DROP TRIGGER delete_when ON main_table;
  
  -- Test column-level triggers
  DROP TRIGGER after_upd_row_trig ON main_table;
  
  CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON main_table
  FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_a_row');
  CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON main_table
  FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_b_row');
  CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON main_table
  FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_a_b_row');
  
  CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON main_table
  FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_upd_a_stmt');
  CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON main_table
  FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_upd_b_stmt');
  
  SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig';
  
  UPDATE main_table SET a = 50;
  UPDATE main_table SET b = 10;
  
  --
  -- Test case for bug with BEFORE trigger followed by AFTER trigger with WHEN
  --
  
  CREATE TABLE some_t (some_col boolean NOT NULL);
  CREATE FUNCTION dummy_update_func() RETURNS trigger AS $$
  BEGIN
    RAISE NOTICE 'dummy_update_func(%) called: action = %, old = %, new = %',
      TG_ARGV[0], TG_OP, OLD, NEW;
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  CREATE TRIGGER some_trig_before BEFORE UPDATE ON some_t FOR EACH ROW
    EXECUTE PROCEDURE dummy_update_func('before');
  CREATE TRIGGER some_trig_aftera AFTER UPDATE ON some_t FOR EACH ROW
    WHEN (NOT OLD.some_col AND NEW.some_col)
    EXECUTE PROCEDURE dummy_update_func('aftera');
  CREATE TRIGGER some_trig_afterb AFTER UPDATE ON some_t FOR EACH ROW
    WHEN (NOT NEW.some_col)
    EXECUTE PROCEDURE dummy_update_func('afterb');
  INSERT INTO some_t VALUES (TRUE);
  UPDATE some_t SET some_col = TRUE;
  UPDATE some_t SET some_col = FALSE;
  UPDATE some_t SET some_col = TRUE;
  DROP TABLE some_t;
  
  -- bogus cases
  CREATE TRIGGER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_table
  FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_and_col');
  CREATE TRIGGER error_upd_a_a BEFORE UPDATE OF a, a ON main_table
  FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_a_a');
  CREATE TRIGGER error_ins_a BEFORE INSERT OF a ON main_table
  FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_ins_a');
  CREATE TRIGGER error_ins_when BEFORE INSERT OR UPDATE ON main_table
  FOR EACH ROW WHEN (OLD.a <> NEW.a)
  EXECUTE PROCEDURE trigger_func('error_ins_old');
  CREATE TRIGGER error_del_when BEFORE DELETE OR UPDATE ON main_table
  FOR EACH ROW WHEN (OLD.a <> NEW.a)
  EXECUTE PROCEDURE trigger_func('error_del_new');
  CREATE TRIGGER error_del_when BEFORE INSERT OR UPDATE ON main_table
  FOR EACH ROW WHEN (NEW.tableoid <> 0)
  EXECUTE PROCEDURE trigger_func('error_when_sys_column');
  CREATE TRIGGER error_stmt_when BEFORE UPDATE OF a ON main_table
  FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*)
  EXECUTE PROCEDURE trigger_func('error_stmt_when');
  
  -- check dependency restrictions
  ALTER TABLE main_table DROP COLUMN b;
  -- this should succeed, but we'll roll it back to keep the triggers around
  begin;
  DROP TRIGGER after_upd_a_b_row_trig ON main_table;
  DROP TRIGGER after_upd_b_row_trig ON main_table;
  DROP TRIGGER after_upd_b_stmt_trig ON main_table;
  ALTER TABLE main_table DROP COLUMN b;
  rollback;
  
  -- Test enable/disable triggers
  
  create table trigtest (i serial primary key);
  -- test that disabling RI triggers works
  create table trigtest2 (i int references trigtest(i) on delete cascade);
  
  create function trigtest() returns trigger as $$
  begin
  	raise notice '% % % %', TG_RELNAME, TG_OP, TG_WHEN, TG_LEVEL;
  	return new;
  end;$$ language plpgsql;
  
  create trigger trigtest_b_row_tg before insert or update or delete on trigtest
  for each row execute procedure trigtest();
  create trigger trigtest_a_row_tg after insert or update or delete on trigtest
  for each row execute procedure trigtest();
  create trigger trigtest_b_stmt_tg before insert or update or delete on trigtest
  for each statement execute procedure trigtest();
  create trigger trigtest_a_stmt_tg after insert or update or delete on trigtest
  for each statement execute procedure trigtest();
  
  insert into trigtest default values;
  alter table trigtest disable trigger trigtest_b_row_tg;
  insert into trigtest default values;
  alter table trigtest disable trigger user;
  insert into trigtest default values;
  alter table trigtest enable trigger trigtest_a_stmt_tg;
  insert into trigtest default values;
  insert into trigtest2 values(1);
  insert into trigtest2 values(2);
  delete from trigtest where i=2;
  select * from trigtest2;
  alter table trigtest disable trigger all;
  delete from trigtest where i=1;
  select * from trigtest2;
  -- ensure we still insert, even when all triggers are disabled
  insert into trigtest default values;
  select *  from trigtest;
  drop table trigtest2;
  drop table trigtest;
  
  
  -- dump trigger data
  CREATE TABLE trigger_test (
          i int,
          v varchar
  );
  
  CREATE OR REPLACE FUNCTION trigger_data()  RETURNS trigger
  LANGUAGE plpgsql AS $$
  
  declare
  
  	argstr text;
  	relid text;
  
  begin
  
  	relid := TG_relid::regclass;
  
  	-- plpgsql can't discover its trigger data in a hash like perl and python
  	-- can, or by a sort of reflection like tcl can,
  	-- so we have to hard code the names.
  	raise NOTICE 'TG_NAME: %', TG_name;
  	raise NOTICE 'TG_WHEN: %', TG_when;
  	raise NOTICE 'TG_LEVEL: %', TG_level;
  	raise NOTICE 'TG_OP: %', TG_op;
  	raise NOTICE 'TG_RELID::regclass: %', relid;
  	raise NOTICE 'TG_RELNAME: %', TG_relname;
  	raise NOTICE 'TG_TABLE_NAME: %', TG_table_name;
  	raise NOTICE 'TG_TABLE_SCHEMA: %', TG_table_schema;
  	raise NOTICE 'TG_NARGS: %', TG_nargs;
  
  	argstr := '[';
  	for i in 0 .. TG_nargs - 1 loop
  		if i > 0 then
  			argstr := argstr || ', ';
  		end if;
  		argstr := argstr || TG_argv[i];
  	end loop;
  	argstr := argstr || ']';
  	raise NOTICE 'TG_ARGV: %', argstr;
  
  	if TG_OP != 'INSERT' then
  		raise NOTICE 'OLD: %', OLD;
  	end if;
  
  	if TG_OP != 'DELETE' then
  		raise NOTICE 'NEW: %', NEW;
  	end if;
  
  	if TG_OP = 'DELETE' then
  		return OLD;
  	else
  		return NEW;
  	end if;
  
  end;
  $$;
  
  CREATE TRIGGER show_trigger_data_trig
  BEFORE INSERT OR UPDATE OR DELETE ON trigger_test
  FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
  
  insert into trigger_test values(1,'insert');
  update trigger_test set v = 'update' where i = 1;
  delete from trigger_test;
  
  DROP TRIGGER show_trigger_data_trig on trigger_test;
  
  DROP FUNCTION trigger_data();
  
  DROP TABLE trigger_test;
  
  --
  -- Test use of row comparisons on OLD/NEW
  --
  
  CREATE TABLE trigger_test (f1 int, f2 text, f3 text);
  
  -- this is the obvious (and wrong...) way to compare rows
  CREATE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$
  begin
  	if row(old.*) = row(new.*) then
  		raise notice 'row % not changed', new.f1;
  	else
  		raise notice 'row % changed', new.f1;
  	end if;
  	return new;
  end$$;
  
  CREATE TRIGGER t
  BEFORE UPDATE ON trigger_test
  FOR EACH ROW EXECUTE PROCEDURE mytrigger();
  
  INSERT INTO trigger_test VALUES(1, 'foo', 'bar');
  INSERT INTO trigger_test VALUES(2, 'baz', 'quux');
  
  UPDATE trigger_test SET f3 = 'bar';
  UPDATE trigger_test SET f3 = NULL;
  -- this demonstrates that the above isn't really working as desired:
  UPDATE trigger_test SET f3 = NULL;
  
  -- the right way when considering nulls is
  CREATE OR REPLACE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$
  begin
  	if row(old.*) is distinct from row(new.*) then
  		raise notice 'row % changed', new.f1;
  	else
  		raise notice 'row % not changed', new.f1;
  	end if;
  	return new;
  end$$;
  
  UPDATE trigger_test SET f3 = 'bar';
  UPDATE trigger_test SET f3 = NULL;
  UPDATE trigger_test SET f3 = NULL;
  
  DROP TABLE trigger_test;
  
  DROP FUNCTION mytrigger();
  
  -- Test snapshot management in serializable transactions involving triggers
  -- per bug report in 6bc73d4c0910042358k3d1adff3qa36f8df75198ecea@mail.gmail.com
  CREATE FUNCTION serializable_update_trig() RETURNS trigger LANGUAGE plpgsql AS
  $$
  declare
  	rec record;
  begin
  	new.description = 'updated in trigger';
  	return new;
  end;
  $$;
  
  CREATE TABLE serializable_update_tab (
  	id int,
  	filler  text,
  	description text
  );
  
  CREATE TRIGGER serializable_update_trig BEFORE UPDATE ON serializable_update_tab
  	FOR EACH ROW EXECUTE PROCEDURE serializable_update_trig();
  
  INSERT INTO serializable_update_tab SELECT a, repeat('xyzxz', 100), 'new'
  	FROM generate_series(1, 50) a;
  
  BEGIN;
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  UPDATE serializable_update_tab SET description = 'no no', id = 1 WHERE id = 1;
  COMMIT;
  SELECT description FROM serializable_update_tab WHERE id = 1;
  DROP TABLE serializable_update_tab;
  
  -- minimal update trigger
  
  CREATE TABLE min_updates_test (
  	f1	text,
  	f2 int,
  	f3 int);
  
  CREATE TABLE min_updates_test_oids (
  	f1	text,
  	f2 int,
  	f3 int) WITH OIDS;
  
  INSERT INTO min_updates_test VALUES ('a',1,2),('b','2',null);
  
  INSERT INTO min_updates_test_oids VALUES ('a',1,2),('b','2',null);
  
  CREATE TRIGGER z_min_update
  BEFORE UPDATE ON min_updates_test
  FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
  
  CREATE TRIGGER z_min_update
  BEFORE UPDATE ON min_updates_test_oids
  FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
  
  \set QUIET false
  
  UPDATE min_updates_test SET f1 = f1;
  
  UPDATE min_updates_test SET f2 = f2 + 1;
  
  UPDATE min_updates_test SET f3 = 2 WHERE f3 is null;
  
  UPDATE min_updates_test_oids SET f1 = f1;
  
  UPDATE min_updates_test_oids SET f2 = f2 + 1;
  
  UPDATE min_updates_test_oids SET f3 = 2 WHERE f3 is null;
  
  \set QUIET true
  
  SELECT * FROM min_updates_test;
  
  SELECT * FROM min_updates_test_oids;
  
  DROP TABLE min_updates_test;
  
  DROP TABLE min_updates_test_oids;
  
  --
  -- Test triggers on views
  --
  
  CREATE VIEW main_view AS SELECT a, b FROM main_table;
  
  -- VIEW trigger function
  CREATE OR REPLACE FUNCTION view_trigger() RETURNS trigger
  LANGUAGE plpgsql AS $$
  declare
      argstr text := '';
  begin
      for i in 0 .. TG_nargs - 1 loop
          if i > 0 then
              argstr := argstr || ', ';
          end if;
          argstr := argstr || TG_argv[i];
      end loop;
  
      raise notice '% % % % (%)', TG_RELNAME, TG_WHEN, TG_OP, TG_LEVEL, argstr;
  
      if TG_LEVEL = 'ROW' then
          if TG_OP = 'INSERT' then
              raise NOTICE 'NEW: %', NEW;
              INSERT INTO main_table VALUES (NEW.a, NEW.b);
              RETURN NEW;
          end if;
  
          if TG_OP = 'UPDATE' then
              raise NOTICE 'OLD: %, NEW: %', OLD, NEW;
              UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b;
              if NOT FOUND then RETURN NULL; end if;
              RETURN NEW;
          end if;
  
          if TG_OP = 'DELETE' then
              raise NOTICE 'OLD: %', OLD;
              DELETE FROM main_table WHERE a = OLD.a AND b = OLD.b;
              if NOT FOUND then RETURN NULL; end if;
              RETURN OLD;
          end if;
      end if;
  
      RETURN NULL;
  end;
  $$;
  
  -- Before row triggers aren't allowed on views
  CREATE TRIGGER invalid_trig BEFORE INSERT ON main_view
  FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row');
  
  CREATE TRIGGER invalid_trig BEFORE UPDATE ON main_view
  FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row');
  
  CREATE TRIGGER invalid_trig BEFORE DELETE ON main_view
  FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row');
  
  -- After row triggers aren't allowed on views
  CREATE TRIGGER invalid_trig AFTER INSERT ON main_view
  FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row');
  
  CREATE TRIGGER invalid_trig AFTER UPDATE ON main_view
  FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row');
  
  CREATE TRIGGER invalid_trig AFTER DELETE ON main_view
  FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row');
  
  -- Truncate triggers aren't allowed on views
  CREATE TRIGGER invalid_trig BEFORE TRUNCATE ON main_view
  EXECUTE PROCEDURE trigger_func('before_tru_row');
  
  CREATE TRIGGER invalid_trig AFTER TRUNCATE ON main_view
  EXECUTE PROCEDURE trigger_func('before_tru_row');
  
  -- INSTEAD OF triggers aren't allowed on tables
  CREATE TRIGGER invalid_trig INSTEAD OF INSERT ON main_table
  FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins');
  
  CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_table
  FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
  
  CREATE TRIGGER invalid_trig INSTEAD OF DELETE ON main_table
  FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
  
  -- Don't support WHEN clauses with INSTEAD OF triggers
  CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view
  FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE view_trigger('instead_of_upd');
  
  -- Don't support column-level INSTEAD OF triggers
  CREATE TRIGGER invalid_trig INSTEAD OF UPDATE OF a ON main_view
  FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
  
  -- Don't support statement-level INSTEAD OF triggers
  CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view
  EXECUTE PROCEDURE view_trigger('instead_of_upd');
  
  -- Valid INSTEAD OF triggers
  CREATE TRIGGER instead_of_insert_trig INSTEAD OF INSERT ON main_view
  FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins');
  
  CREATE TRIGGER instead_of_update_trig INSTEAD OF UPDATE ON main_view
  FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
  
  CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view
  FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
  
  -- Valid BEFORE statement VIEW triggers
  CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view
  FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt');
  
  CREATE TRIGGER before_upd_stmt_trig BEFORE UPDATE ON main_view
  FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt');
  
  CREATE TRIGGER before_del_stmt_trig BEFORE DELETE ON main_view
  FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt');
  
  -- Valid AFTER statement VIEW triggers
  CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_view
  FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt');
  
  CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_view
  FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt');
  
  CREATE TRIGGER after_del_stmt_trig AFTER DELETE ON main_view
  FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt');
  
  \set QUIET false
  
  -- Insert into view using trigger
  INSERT INTO main_view VALUES (20, 30);
  INSERT INTO main_view VALUES (21, 31) RETURNING a, b;
  
  -- Table trigger will prevent updates
  UPDATE main_view SET b = 31 WHERE a = 20;
  UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b;
  
  -- Remove table trigger to allow updates
  DROP TRIGGER before_upd_a_row_trig ON main_table;
  UPDATE main_view SET b = 31 WHERE a = 20;
  UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b;
  
  -- Before and after stmt triggers should fire even when no rows are affected
  UPDATE main_view SET b = 0 WHERE false;
  
  -- Delete from view using trigger
  DELETE FROM main_view WHERE a IN (20,21);
  DELETE FROM main_view WHERE a = 31 RETURNING a, b;
  
  \set QUIET true
  
  -- Describe view should list triggers
  \d main_view
  
  -- Test dropping view triggers
  DROP TRIGGER instead_of_insert_trig ON main_view;
  DROP TRIGGER instead_of_delete_trig ON main_view;
  \d+ main_view
  DROP VIEW main_view;
  
  --
  -- Test triggers on a join view
  --
  CREATE TABLE country_table (
      country_id        serial primary key,
      country_name    text unique not null,
      continent        text not null
  );
  
  INSERT INTO country_table (country_name, continent)
      VALUES ('Japan', 'Asia'),
             ('UK', 'Europe'),
             ('USA', 'North America')
      RETURNING *;
  
  CREATE TABLE city_table (
      city_id        serial primary key,
      city_name    text not null,
      population    bigint,
      country_id    int references country_table
  );
  
  CREATE VIEW city_view AS
      SELECT city_id, city_name, population, country_name, continent
      FROM city_table ci
      LEFT JOIN country_table co ON co.country_id = ci.country_id;
  
  CREATE FUNCTION city_insert() RETURNS trigger LANGUAGE plpgsql AS $$
  declare
      ctry_id int;
  begin
      if NEW.country_name IS NOT NULL then
          SELECT country_id, continent INTO ctry_id, NEW.continent
              FROM country_table WHERE country_name = NEW.country_name;
          if NOT FOUND then
              raise exception 'No such country: "%"', NEW.country_name;
          end if;
      else
          NEW.continent := NULL;
      end if;
  
      if NEW.city_id IS NOT NULL then
          INSERT INTO city_table
              VALUES(NEW.city_id, NEW.city_name, NEW.population, ctry_id);
      else
          INSERT INTO city_table(city_name, population, country_id)
              VALUES(NEW.city_name, NEW.population, ctry_id)
              RETURNING city_id INTO NEW.city_id;
      end if;
  
      RETURN NEW;
  end;
  $$;
  
  CREATE TRIGGER city_insert_trig INSTEAD OF INSERT ON city_view
  FOR EACH ROW EXECUTE PROCEDURE city_insert();
  
  CREATE FUNCTION city_delete() RETURNS trigger LANGUAGE plpgsql AS $$
  begin
      DELETE FROM city_table WHERE city_id = OLD.city_id;
      if NOT FOUND then RETURN NULL; end if;
      RETURN OLD;
  end;
  $$;
  
  CREATE TRIGGER city_delete_trig INSTEAD OF DELETE ON city_view
  FOR EACH ROW EXECUTE PROCEDURE city_delete();
  
  CREATE FUNCTION city_update() RETURNS trigger LANGUAGE plpgsql AS $$
  declare
      ctry_id int;
  begin
      if NEW.country_name IS DISTINCT FROM OLD.country_name then
          SELECT country_id, continent INTO ctry_id, NEW.continent
              FROM country_table WHERE country_name = NEW.country_name;
          if NOT FOUND then
              raise exception 'No such country: "%"', NEW.country_name;
          end if;
  
          UPDATE city_table SET city_name = NEW.city_name,
                                population = NEW.population,
                                country_id = ctry_id
              WHERE city_id = OLD.city_id;
      else
          UPDATE city_table SET city_name = NEW.city_name,
                                population = NEW.population
              WHERE city_id = OLD.city_id;
          NEW.continent := OLD.continent;
      end if;
  
      if NOT FOUND then RETURN NULL; end if;
      RETURN NEW;
  end;
  $$;
  
  CREATE TRIGGER city_update_trig INSTEAD OF UPDATE ON city_view
  FOR EACH ROW EXECUTE PROCEDURE city_update();
  
  \set QUIET false
  
  -- INSERT .. RETURNING
  INSERT INTO city_view(city_name) VALUES('Tokyo') RETURNING *;
  INSERT INTO city_view(city_name, population) VALUES('London', 7556900) RETURNING *;
  INSERT INTO city_view(city_name, country_name) VALUES('Washington DC', 'USA') RETURNING *;
  INSERT INTO city_view(city_id, city_name) VALUES(123456, 'New York') RETURNING *;
  INSERT INTO city_view VALUES(234567, 'Birmingham', 1016800, 'UK', 'EU') RETURNING *;
  
  -- UPDATE .. RETURNING
  UPDATE city_view SET country_name = 'Japon' WHERE city_name = 'Tokyo'; -- error
  UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Takyo'; -- no match
  UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Tokyo' RETURNING *; -- OK
  
  UPDATE city_view SET population = 13010279 WHERE city_name = 'Tokyo' RETURNING *;
  UPDATE city_view SET country_name = 'UK' WHERE city_name = 'New York' RETURNING *;
  UPDATE city_view SET country_name = 'USA', population = 8391881 WHERE city_name = 'New York' RETURNING *;
  UPDATE city_view SET continent = 'EU' WHERE continent = 'Europe' RETURNING *;
  UPDATE city_view v1 SET country_name = v2.country_name FROM city_view v2
      WHERE v2.city_name = 'Birmingham' AND v1.city_name = 'London' RETURNING *;
  
  -- DELETE .. RETURNING
  DELETE FROM city_view WHERE city_name = 'Birmingham' RETURNING *;
  
  \set QUIET true
  
  -- read-only view with WHERE clause
  CREATE VIEW european_city_view AS
      SELECT * FROM city_view WHERE continent = 'Europe';
  SELECT count(*) FROM european_city_view;
  
  CREATE FUNCTION no_op_trig_fn() RETURNS trigger LANGUAGE plpgsql
  AS 'begin RETURN NULL; end';
  
  CREATE TRIGGER no_op_trig INSTEAD OF INSERT OR UPDATE OR DELETE
  ON european_city_view FOR EACH ROW EXECUTE PROCEDURE no_op_trig_fn();
  
  \set QUIET false
  
  INSERT INTO european_city_view VALUES (0, 'x', 10000, 'y', 'z');
  UPDATE european_city_view SET population = 10000;
  DELETE FROM european_city_view;
  
  \set QUIET true
  
  -- rules bypassing no-op triggers
  CREATE RULE european_city_insert_rule AS ON INSERT TO european_city_view
  DO INSTEAD INSERT INTO city_view
  VALUES (NEW.city_id, NEW.city_name, NEW.population, NEW.country_name, NEW.continent)
  RETURNING *;
  
  CREATE RULE european_city_update_rule AS ON UPDATE TO european_city_view
  DO INSTEAD UPDATE city_view SET
      city_name = NEW.city_name,
      population = NEW.population,
      country_name = NEW.country_name
  WHERE city_id = OLD.city_id
  RETURNING NEW.*;
  
  CREATE RULE european_city_delete_rule AS ON DELETE TO european_city_view
  DO INSTEAD DELETE FROM city_view WHERE city_id = OLD.city_id RETURNING *;
  
  \set QUIET false
  
  -- INSERT not limited by view's WHERE clause, but UPDATE AND DELETE are
  INSERT INTO european_city_view(city_name, country_name)
      VALUES ('Cambridge', 'USA') RETURNING *;
  UPDATE european_city_view SET country_name = 'UK'
      WHERE city_name = 'Cambridge';
  DELETE FROM european_city_view WHERE city_name = 'Cambridge';
  
  -- UPDATE and DELETE via rule and trigger
  UPDATE city_view SET country_name = 'UK'
      WHERE city_name = 'Cambridge' RETURNING *;
  UPDATE european_city_view SET population = 122800
      WHERE city_name = 'Cambridge' RETURNING *;
  DELETE FROM european_city_view WHERE city_name = 'Cambridge' RETURNING *;
  
  -- join UPDATE test
  UPDATE city_view v SET population = 599657
      FROM city_table ci, country_table co
      WHERE ci.city_name = 'Washington DC' and co.country_name = 'USA'
      AND v.city_id = ci.city_id AND v.country_name = co.country_name
      RETURNING co.country_id, v.country_name,
                v.city_id, v.city_name, v.population;
  
  \set QUIET true
  
  SELECT * FROM city_view;
  
  DROP TABLE city_table CASCADE;
  DROP TABLE country_table;
  
  
  -- Test pg_trigger_depth()
  
  create table depth_a (id int not null primary key);
  create table depth_b (id int not null primary key);
  create table depth_c (id int not null primary key);
  
  create function depth_a_tf() returns trigger
    language plpgsql as $$
  begin
    raise notice '%: depth = %', tg_name, pg_trigger_depth();
    insert into depth_b values (new.id);
    raise notice '%: depth = %', tg_name, pg_trigger_depth();
    return new;
  end;
  $$;
  create trigger depth_a_tr before insert on depth_a
    for each row execute procedure depth_a_tf();
  
  create function depth_b_tf() returns trigger
    language plpgsql as $$
  begin
    raise notice '%: depth = %', tg_name, pg_trigger_depth();
    begin
      execute 'insert into depth_c values (' || new.id::text || ')';
    exception
      when sqlstate 'U9999' then
        raise notice 'SQLSTATE = U9999: depth = %', pg_trigger_depth();
    end;
    raise notice '%: depth = %', tg_name, pg_trigger_depth();
    if new.id = 1 then
      execute 'insert into depth_c values (' || new.id::text || ')';
    end if;
    return new;
  end;
  $$;
  create trigger depth_b_tr before insert on depth_b
    for each row execute procedure depth_b_tf();
  
  create function depth_c_tf() returns trigger
    language plpgsql as $$
  begin
    raise notice '%: depth = %', tg_name, pg_trigger_depth();
    if new.id = 1 then
      raise exception sqlstate 'U9999';
    end if;
    raise notice '%: depth = %', tg_name, pg_trigger_depth();
    return new;
  end;
  $$;
  create trigger depth_c_tr before insert on depth_c
    for each row execute procedure depth_c_tf();
  
  select pg_trigger_depth();
  insert into depth_a values (1);
  select pg_trigger_depth();
  insert into depth_a values (2);
  select pg_trigger_depth();
  
  drop table depth_a, depth_b, depth_c;
  drop function depth_a_tf();
  drop function depth_b_tf();
  drop function depth_c_tf();
  
  --
  -- Test updates to rows during firing of BEFORE ROW triggers.
  -- As of 9.2, such cases should be rejected (see bug #6123).
  --
  
  create temp table parent (
      aid int not null primary key,
      val1 text,
      val2 text,
      val3 text,
      val4 text,
      bcnt int not null default 0);
  create temp table child (
      bid int not null primary key,
      aid int not null,
      val1 text);
  
  create function parent_upd_func()
    returns trigger language plpgsql as
  $$
  begin
    if old.val1 <> new.val1 then
      new.val2 = new.val1;
      delete from child where child.aid = new.aid and child.val1 = new.val1;
    end if;
    return new;
  end;
  $$;
  create trigger parent_upd_trig before update on parent
    for each row execute procedure parent_upd_func();
  
  create function parent_del_func()
    returns trigger language plpgsql as
  $$
  begin
    delete from child where aid = old.aid;
    return old;
  end;
  $$;
  create trigger parent_del_trig before delete on parent
    for each row execute procedure parent_del_func();
  
  create function child_ins_func()
    returns trigger language plpgsql as
  $$
  begin
    update parent set bcnt = bcnt + 1 where aid = new.aid;
    return new;
  end;
  $$;
  create trigger child_ins_trig after insert on child
    for each row execute procedure child_ins_func();
  
  create function child_del_func()
    returns trigger language plpgsql as
  $$
  begin
    update parent set bcnt = bcnt - 1 where aid = old.aid;
    return old;
  end;
  $$;
  create trigger child_del_trig after delete on child
    for each row execute procedure child_del_func();
  
  insert into parent values (1, 'a', 'a', 'a', 'a', 0);
  insert into child values (10, 1, 'b');
  select * from parent; select * from child;
  
  update parent set val1 = 'b' where aid = 1; -- should fail
  select * from parent; select * from child;
  
  delete from parent where aid = 1; -- should fail
  select * from parent; select * from child;
  
  -- replace the trigger function with one that restarts the deletion after
  -- having modified a child
  create or replace function parent_del_func()
    returns trigger language plpgsql as
  $$
  begin
    delete from child where aid = old.aid;
    if found then
      delete from parent where aid = old.aid;
      return null; -- cancel outer deletion
    end if;
    return old;
  end;
  $$;
  
  delete from parent where aid = 1;
  select * from parent; select * from child;
  
  drop table parent, child;
  
  drop function parent_upd_func();
  drop function parent_del_func();
  drop function child_ins_func();
  drop function child_del_func();
  
  -- similar case, but with a self-referencing FK so that parent and child
  -- rows can be affected by a single operation
  
  create temp table self_ref_trigger (
      id int primary key,
      parent int references self_ref_trigger,
      data text,
      nchildren int not null default 0
  );
  
  create function self_ref_trigger_ins_func()
    returns trigger language plpgsql as
  $$
  begin
    if new.parent is not null then
      update self_ref_trigger set nchildren = nchildren + 1
        where id = new.parent;
    end if;
    return new;
  end;
  $$;
  create trigger self_ref_trigger_ins_trig before insert on self_ref_trigger
    for each row execute procedure self_ref_trigger_ins_func();
  
  create function self_ref_trigger_del_func()
    returns trigger language plpgsql as
  $$
  begin
    if old.parent is not null then
      update self_ref_trigger set nchildren = nchildren - 1
        where id = old.parent;
    end if;
    return old;
  end;
  $$;
  create trigger self_ref_trigger_del_trig before delete on self_ref_trigger
    for each row execute procedure self_ref_trigger_del_func();
  
  insert into self_ref_trigger values (1, null, 'root');
  insert into self_ref_trigger values (2, 1, 'root child A');
  insert into self_ref_trigger values (3, 1, 'root child B');
  insert into self_ref_trigger values (4, 2, 'grandchild 1');
  insert into self_ref_trigger values (5, 3, 'grandchild 2');
  
  update self_ref_trigger set data = 'root!' where id = 1;
  
  select * from self_ref_trigger;
  
  delete from self_ref_trigger;
  
  select * from self_ref_trigger;
  
  drop table self_ref_trigger;
  drop function self_ref_trigger_ins_func();
  drop function self_ref_trigger_del_func();
  
  --
  -- Check that statement triggers work correctly even with all children excluded
  --
  
  create table stmt_trig_on_empty_upd (a int);
  create table stmt_trig_on_empty_upd1 () inherits (stmt_trig_on_empty_upd);
  create function update_stmt_notice() returns trigger as $$
  begin
  	raise notice 'updating %', TG_TABLE_NAME;
  	return null;
  end;
  $$ language plpgsql;
  create trigger before_stmt_trigger
  	before update on stmt_trig_on_empty_upd
  	execute procedure update_stmt_notice();
  create trigger before_stmt_trigger
  	before update on stmt_trig_on_empty_upd1
  	execute procedure update_stmt_notice();
  
  -- inherited no-op update
  update stmt_trig_on_empty_upd set a = a where false returning a+1 as aa;
  -- simple no-op update
  update stmt_trig_on_empty_upd1 set a = a where false returning a+1 as aa;
  
  drop table stmt_trig_on_empty_upd cascade;
  drop function update_stmt_notice();
  