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
|
drop function pgq.insert_event(text, text, text, text, text, text, text);
create or replace function pgq.insert_event(que text, ev_type text, ev_data text, x1 text, x2 text, x3 text, x4 text)
returns bigint as $$
begin
raise notice 'insert_event(%, %, %, %)', que, ev_type, ev_data, x1;
return 1;
end;
$$ language plpgsql;
create table udata (
id serial primary key,
txt text,
bin bytea
);
NOTICE: CREATE TABLE will create implicit sequence "udata_id_seq" for serial column "udata.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "udata_pkey" for table "udata"
create trigger utest AFTER insert or update or delete ON udata
for each row execute procedure pgq.logutriga('udata_que');
insert into udata (txt) values ('text1');
NOTICE: insert_event(udata_que, I:id, id=1&txt=text1&bin, public.udata)
CONTEXT: SQL statement "select pgq.insert_event($1, $2, $3, $4, $5, null, null)"
insert into udata (bin) values (E'bi\tn\\000bin');
NOTICE: insert_event(udata_que, I:id, id=2&txt&bin=bi%5c011n%5c000bin, public.udata)
CONTEXT: SQL statement "select pgq.insert_event($1, $2, $3, $4, $5, null, null)"
-- test missing pkey
create table nopkey2 (dat text);
create trigger nopkey_triga2 after insert or update or delete on nopkey2
for each row execute procedure pgq.logutriga('que3');
insert into nopkey2 values ('foo');
NOTICE: insert_event(que3, I:, dat=foo, public.nopkey2)
CONTEXT: SQL statement "select pgq.insert_event($1, $2, $3, $4, $5, null, null)"
update nopkey2 set dat = 'bat';
ERROR: Update/Delete on table without pkey
delete from nopkey2;
ERROR: Update/Delete on table without pkey
-- test custom pkey
create table ucustom_pkey (dat1 text not null, dat2 int2 not null, dat3 text);
create trigger ucustom_triga after insert or update or delete on ucustom_pkey
--for each row execute procedure pgq.logutriga('que3', 'pkey=dat1,dat2');
for each row execute procedure pgq.logutriga('que3');
insert into ucustom_pkey values ('foo', '2');
NOTICE: insert_event(que3, I:, dat1=foo&dat2=2&dat3, public.ucustom_pkey)
CONTEXT: SQL statement "select pgq.insert_event($1, $2, $3, $4, $5, null, null)"
update ucustom_pkey set dat3 = 'bat';
ERROR: Update/Delete on table without pkey
delete from ucustom_pkey;
ERROR: Update/Delete on table without pkey
|
