1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
|
-- 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, (id,dat) values ('1','value1'), public.rtest)
CONTEXT: SQL statement "select pgq.insert_event($1, $2, $3, $4, $5, 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, $5, 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, $5, null, null)"
-- test new fields
alter table rtest add column dat2 text;
insert into rtest values (1, 'value1');
NOTICE: insert_event(que, I, (id,dat,dat2) values ('1','value1',null), public.rtest)
CONTEXT: SQL statement "select pgq.insert_event($1, $2, $3, $4, $5, 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, $5, 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, $5, 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, (id,dat) values ('1','666'), public.rtest)
CONTEXT: SQL statement "select pgq.insert_event($1, $2, $3, $4, $5, 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, $5, 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, $5, 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, $5, 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');
-- 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');
NOTICE: insert_event(que3, I, (id,dat,dat2) values ('1','0','non-null'), public.rtest)
CONTEXT: SQL statement "select pgq.insert_event($1, $2, $3, $4, $5, null, null)"
insert into rtest values (2, 0, NULL);
NOTICE: insert_event(que3, I, (id,dat,dat2) values ('2','0',null), public.rtest)
CONTEXT: SQL statement "select pgq.insert_event($1, $2, $3, $4, $5, null, null)"
update rtest set dat2 = 'non-null2' where id=1;
NOTICE: insert_event(que3, U, dat2='non-null2' where id='1', public.rtest)
CONTEXT: SQL statement "select pgq.insert_event($1, $2, $3, $4, $5, null, null)"
update rtest set dat2 = NULL where id=1;
NOTICE: insert_event(que3, U, dat2=NULL where id='1', public.rtest)
CONTEXT: SQL statement "select pgq.insert_event($1, $2, $3, $4, $5, null, null)"
update rtest set dat2 = 'new-nonnull' where id=2;
NOTICE: insert_event(que3, U, dat2='new-nonnull' where id='2', public.rtest)
CONTEXT: SQL statement "select pgq.insert_event($1, $2, $3, $4, $5, 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, $5, 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, $5, null, null)"
-- 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');
NOTICE: insert_event(que3, I, (dat) values ('foo'), public.nopkey)
CONTEXT: SQL statement "select pgq.insert_event($1, $2, $3, $4, $5, null, null)"
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');
NOTICE: insert_event(que3, I, (dat1,dat2,dat3) values ('foo','2',null), public.custom_pkey)
CONTEXT: SQL statement "select pgq.insert_event($1, $2, $3, $4, $5, null, null)"
update custom_pkey set dat3 = 'bat';
NOTICE: insert_event(que3, U, dat3='bat' where dat1='foo' and dat2='2', public.custom_pkey)
CONTEXT: SQL statement "select pgq.insert_event($1, $2, $3, $4, $5, null, null)"
delete from custom_pkey;
NOTICE: insert_event(que3, D, dat1='foo' and dat2='2', public.custom_pkey)
CONTEXT: SQL statement "select pgq.insert_event($1, $2, $3, $4, $5, null, null)"
|
