\set VERBOSITY terse

SET search_path = 'public';
CREATE EXTENSION pg_pathman;
CREATE SCHEMA test_update_triggers;



create table test_update_triggers.test (val int not null);
select create_hash_partitions('test_update_triggers.test', 'val', 2,
							  partition_names := array[
								'test_update_triggers.test_1',
								'test_update_triggers.test_2']);


create or replace function test_update_triggers.test_trigger() returns trigger as $$
begin
	raise notice '%', format('%s %s %s (%s)', TG_WHEN, TG_OP, TG_LEVEL, TG_TABLE_NAME);

	if TG_OP::text = 'DELETE'::text then
		return old;
	else
		return new;
	end if; end;
$$ language plpgsql;


/* Enable our precious custom node */
set pg_pathman.enable_partitionrouter = t;


/*
 * Statement level triggers
 */

create trigger bus before update ON test_update_triggers.test
	execute procedure test_update_triggers.test_trigger ();
create trigger bds before delete ON test_update_triggers.test
	execute procedure test_update_triggers.test_trigger ();
create trigger bis before insert ON test_update_triggers.test
	execute procedure test_update_triggers.test_trigger ();


create trigger aus after update ON test_update_triggers.test
	execute procedure test_update_triggers.test_trigger ();
create trigger ads after delete ON test_update_triggers.test
	execute procedure test_update_triggers.test_trigger ();
create trigger ais after insert ON test_update_triggers.test
	execute procedure test_update_triggers.test_trigger ();


create trigger bus before update ON test_update_triggers.test_1
	execute procedure test_update_triggers.test_trigger ();
create trigger bds before delete ON test_update_triggers.test_1
	execute procedure test_update_triggers.test_trigger ();
create trigger bis before insert ON test_update_triggers.test_1
	execute procedure test_update_triggers.test_trigger ();

create trigger aus after update ON test_update_triggers.test_1
	execute procedure test_update_triggers.test_trigger ();
create trigger ads after delete ON test_update_triggers.test_1
	execute procedure test_update_triggers.test_trigger ();
create trigger ais after insert ON test_update_triggers.test_1
	execute procedure test_update_triggers.test_trigger ();


create trigger bus before update ON test_update_triggers.test_2
	execute procedure test_update_triggers.test_trigger ();
create trigger bds before delete ON test_update_triggers.test_2
	execute procedure test_update_triggers.test_trigger ();
create trigger bis before insert ON test_update_triggers.test_2
	execute procedure test_update_triggers.test_trigger ();

create trigger aus after update ON test_update_triggers.test_2
	execute procedure test_update_triggers.test_trigger ();
create trigger ads after delete ON test_update_triggers.test_2
	execute procedure test_update_triggers.test_trigger ();
create trigger ais after insert ON test_update_triggers.test_2
	execute procedure test_update_triggers.test_trigger ();


/* multiple values */
insert into test_update_triggers.test select generate_series(1, 200);

update test_update_triggers.test set val = val + 1;
update test_update_triggers.test set val = val + 1;
update test_update_triggers.test set val = val + 1;
update test_update_triggers.test set val = val + 1;
update test_update_triggers.test set val = val + 1;

select count(distinct val) from test_update_triggers.test;


truncate test_update_triggers.test;


/*
 * Row level triggers
 */

create trigger bu before update ON test_update_triggers.test_1
	for each row execute procedure test_update_triggers.test_trigger ();
create trigger bd before delete ON test_update_triggers.test_1
	for each row execute procedure test_update_triggers.test_trigger ();
create trigger bi before insert ON test_update_triggers.test_1
	for each row execute procedure test_update_triggers.test_trigger ();

create trigger au after update ON test_update_triggers.test_1
	for each row execute procedure test_update_triggers.test_trigger ();
create trigger ad after delete ON test_update_triggers.test_1
	for each row execute procedure test_update_triggers.test_trigger ();
create trigger ai after insert ON test_update_triggers.test_1
	for each row execute procedure test_update_triggers.test_trigger ();


create trigger bu before update ON test_update_triggers.test_2
	for each row execute procedure test_update_triggers.test_trigger ();
create trigger bd before delete ON test_update_triggers.test_2
	for each row execute procedure test_update_triggers.test_trigger ();
create trigger bi before insert ON test_update_triggers.test_2
	for each row execute procedure test_update_triggers.test_trigger ();

create trigger au after update ON test_update_triggers.test_2
	for each row execute procedure test_update_triggers.test_trigger ();
create trigger ad after delete ON test_update_triggers.test_2
	for each row execute procedure test_update_triggers.test_trigger ();
create trigger ai after insert ON test_update_triggers.test_2
	for each row execute procedure test_update_triggers.test_trigger ();


/* single value */
insert into test_update_triggers.test values (1);

update test_update_triggers.test set val = val + 1 returning *, tableoid::regclass;
update test_update_triggers.test set val = val + 1 returning *, tableoid::regclass;
update test_update_triggers.test set val = val + 1 returning *, tableoid::regclass;
update test_update_triggers.test set val = val + 1 returning *, tableoid::regclass;
update test_update_triggers.test set val = val + 1 returning *, tableoid::regclass;

select count(distinct val) from test_update_triggers.test;


DROP SCHEMA test_update_triggers CASCADE;
DROP EXTENSION pg_pathman CASCADE;
