\set VERBOSITY 'terse'
set client_min_messages = 'warning';
-- start testing
create table rtest (
	id integer primary key,
	dat text
);
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');
WARNING:  insert_event(que, I, (id,dat) values ('1','value1'), public.rtest)
update rtest set dat = 'value2';
WARNING:  insert_event(que, U, dat='value2' where id='1', public.rtest)
delete from rtest;
WARNING:  insert_event(que, D, id='1', public.rtest)
-- test new fields
alter table rtest add column dat2 text;
insert into rtest values (1, 'value1');
WARNING:  insert_event(que, I, (id,dat,dat2) values ('1','value1',null), public.rtest)
update rtest set dat = 'value2';
WARNING:  insert_event(que, U, dat='value2' where id='1', public.rtest)
delete from rtest;
WARNING:  insert_event(que, D, id='1', public.rtest)
-- 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');
WARNING:  insert_event(que2, I, (id,dat) values ('1','666'), public.rtest)
update rtest set dat = 5, dat2 = 'newdat2';
WARNING:  insert_event(que2, U, dat='5' where id='1', public.rtest)
update rtest set dat = 6;
WARNING:  insert_event(que2, U, dat='6' where id='1', public.rtest)
delete from rtest;
WARNING:  insert_event(que2, D, id='1', public.rtest)
-- 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');
-- update rtest set dat = 5, dat2 = 'newdat2';
-- update rtest set dat = 6;
-- delete from rtest;
-- 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');
WARNING:  insert_event(que3, I, (id,dat,dat2) values ('1','0','non-null'), public.rtest)
insert into rtest values (2, 0, NULL);
WARNING:  insert_event(que3, I, (id,dat,dat2) values ('2','0',null), public.rtest)
update rtest set dat2 = 'non-null2' where id=1;
WARNING:  insert_event(que3, U, dat2='non-null2' where id='1', public.rtest)
update rtest set dat2 = NULL where id=1;
WARNING:  insert_event(que3, U, dat2=NULL where id='1', public.rtest)
update rtest set dat2 = 'new-nonnull' where id=2;
WARNING:  insert_event(que3, U, dat2='new-nonnull' where id='2', public.rtest)
delete from rtest where id=1;
WARNING:  insert_event(que3, D, id='1', public.rtest)
delete from rtest where id=2;
WARNING:  insert_event(que3, D, id='2', public.rtest)
-- test missing pkey
create table nopkey (dat text);
create trigger nopkey_triga after insert or update or delete on nopkey
for each row execute procedure pgq.sqltriga('que3');
insert into nopkey values ('foo');
WARNING:  insert_event(que3, I, (dat) values ('foo'), public.nopkey)
update nopkey set dat = 'bat';
ERROR:  Update/Delete on table without pkey
delete from nopkey;
ERROR:  Update/Delete on table without pkey
-- test custom pkey
create table custom_pkey (dat1 text not null, dat2 int2 not null, dat3 text);
create trigger custom_triga after insert or update or delete on custom_pkey
for each row execute procedure pgq.sqltriga('que3', 'pkey=dat1,dat2');
insert into custom_pkey values ('foo', '2');
WARNING:  insert_event(que3, I, (dat1,dat2,dat3) values ('foo','2',null), public.custom_pkey)
update custom_pkey set dat3 = 'bat';
WARNING:  insert_event(que3, U, dat3='bat' where dat1='foo' and dat2='2', public.custom_pkey)
delete from custom_pkey;
WARNING:  insert_event(que3, D, dat1='foo' and dat2='2', public.custom_pkey)
-- test custom fields
create table custom_fields (
    dat1 text not null primary key,
    dat2 int2 not null,
    dat3 text,
    _pgq_ev_type text default 'my_type',
    _pgq_ev_extra1 text default 'e1',
    _pgq_ev_extra2 text default 'e2',
    _pgq_ev_extra3 text default 'e3',
    _pgq_ev_extra4 text default 'e4'
);
create trigger customf_triga after insert or update or delete on custom_fields
for each row execute procedure pgq.sqltriga('que3');
insert into custom_fields values ('foo', '2');
WARNING:  insert_event(que3, my_type, (dat1,dat2,dat3) values ('foo','2',null), e1)
update custom_fields set dat3 = 'bat';
WARNING:  insert_event(que3, my_type, dat3='bat' where dat1='foo', e1)
delete from custom_fields;
WARNING:  insert_event(que3, my_type, dat1='foo', e1)
-- test custom expression
create table custom_expr (
    dat1 text not null primary key,
    dat2 int2 not null,
    dat3 text
);
create trigger customex_triga after insert or update or delete on custom_expr
for each row execute procedure pgq.sqltriga('que3', 'ev_extra1=''test='' || dat1', 'ev_type=dat3');
insert into custom_expr values ('foo', '2');
WARNING:  insert_event(que3, <NULL>, (dat1,dat2,dat3) values ('foo','2',null), test=foo)
update custom_expr set dat3 = 'bat';
WARNING:  insert_event(que3, bat, dat3='bat' where dat1='foo', test=foo)
delete from custom_expr;
WARNING:  insert_event(que3, bat, dat1='foo', test=foo)
-- test pk update
insert into rtest values (1, 'value1');
WARNING:  insert_event(que3, I, (id,dat,dat2) values ('1','value1',null), public.rtest)
update rtest set id = 2;
ERROR:  primary key update not allowed