-- start testing
create table rtest (
	id integer primary key,
	dat text
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "rtest_pkey" for table "rtest"
create trigger rtest_triga after insert or update or delete on rtest
for each row execute procedure pgq.sqltriga('que');
-- simple test
insert into rtest values (1, 'value1');
NOTICE:  insert_event(que, I, (dat,id) values ('value1','1'), public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
update rtest set dat = 'value2';
NOTICE:  insert_event(que, U, dat='value2' where id='1', public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
delete from rtest;
NOTICE:  insert_event(que, D, id='1', public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
-- test new fields
alter table rtest add column dat2 text;
insert into rtest values (1, 'value1');
NOTICE:  insert_event(que, I, (dat,id) values ('value1','1'), public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
update rtest set dat = 'value2';
NOTICE:  insert_event(que, U, dat='value2' where id='1', public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
delete from rtest;
NOTICE:  insert_event(que, D, id='1', public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
-- test field ignore
drop trigger rtest_triga on rtest;
create trigger rtest_triga after insert or update or delete on rtest
for each row execute procedure pgq.sqltriga('que2', 'ignore=dat2');
insert into rtest values (1, '666', 'newdat');
NOTICE:  insert_event(que2, I, (dat,id) values ('666','1'), public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
update rtest set dat = 5, dat2 = 'newdat2';
NOTICE:  insert_event(que2, U, dat='5' where id='1', public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
update rtest set dat = 6;
NOTICE:  insert_event(que2, U, dat='6' where id='1', public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
delete from rtest;
NOTICE:  insert_event(que2, D, id='1', public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
-- test hashed pkey
drop trigger rtest_triga on rtest;
create trigger rtest_triga after insert or update or delete on rtest
for each row execute procedure pgq.sqltriga('que2', 'ignore=dat2&pkey=dat,hashtext(dat)');
insert into rtest values (1, '666', 'newdat');
NOTICE:  insert_event(que2, I, (dat,id) values ('666','1'), public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
update rtest set dat = 5, dat2 = 'newdat2';
NOTICE:  insert_event(que2, U, dat='5' where dat='5' and hashtext(dat) = hashtext('5'), public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
update rtest set dat = 6;
NOTICE:  insert_event(que2, U, dat='6' where dat='6' and hashtext(dat) = hashtext('6'), public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
delete from rtest;
NOTICE:  insert_event(que2, D, dat='6' and hashtext(dat) = hashtext('6'), public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
-- test wrong key
drop trigger rtest_triga on rtest;
create trigger rtest_triga after insert or update or delete on rtest
for each row execute procedure pgq.sqltriga('que3');
insert into rtest values (1, 0, 'non-null');
NOTICE:  insert_event(que3, I, (dat,id) values ('0','1'), public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
insert into rtest values (2, 0, NULL);
NOTICE:  insert_event(que3, I, (dat,id) values ('0','2'), public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
update rtest set dat2 = 'non-null2' where id=1;
NOTICE:  insert_event(que3, U, id='1' where id='1', public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
update rtest set dat2 = NULL where id=1;
NOTICE:  insert_event(que3, U, id='1' where id='1', public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
update rtest set dat2 = 'new-nonnull' where id=2;
NOTICE:  insert_event(que3, U, id='2' where id='2', public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
delete from rtest where id=1;
NOTICE:  insert_event(que3, D, id='1', public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"
delete from rtest where id=2;
NOTICE:  insert_event(que3, D, id='2', public.rtest)
CONTEXT:  SQL statement "SELECT pgq.insert_event($1, $2, $3, $4, null, null, null)"