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
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
|
\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
|
