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
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
|
-- these triggers are dedicated to HPHC of RI who
-- decided that my kid's name was william not willem, and
-- vigorously resisted all efforts at correction. they have
-- since gone bankrupt...
CREATE FUNCTION users_insert() returns trigger
AS
'if TD["new"]["fname"] == None or TD["new"]["lname"] == None:
return "SKIP"
if TD["new"]["username"] == None:
TD["new"]["username"] = TD["new"]["fname"][:1] + "_" + TD["new"]["lname"]
rv = "MODIFY"
else:
rv = None
if TD["new"]["fname"] == "william":
TD["new"]["fname"] = TD["args"][0]
rv = "MODIFY"
return rv'
LANGUAGE plpythonu;
CREATE FUNCTION users_update() returns trigger
AS
'if TD["event"] == "UPDATE":
if TD["old"]["fname"] != TD["new"]["fname"] and TD["old"]["fname"] == TD["args"][0]:
return "SKIP"
return None'
LANGUAGE plpythonu;
CREATE FUNCTION users_delete() RETURNS trigger
AS
'if TD["old"]["fname"] == TD["args"][0]:
return "SKIP"
return None'
LANGUAGE plpythonu;
CREATE TRIGGER users_insert_trig BEFORE INSERT ON users FOR EACH ROW
EXECUTE PROCEDURE users_insert ('willem');
CREATE TRIGGER users_update_trig BEFORE UPDATE ON users FOR EACH ROW
EXECUTE PROCEDURE users_update ('willem');
CREATE TRIGGER users_delete_trig BEFORE DELETE ON users FOR EACH ROW
EXECUTE PROCEDURE users_delete ('willem');
-- quick peek at the table
--
SELECT * FROM users;
fname | lname | username | userid
--------+-------+----------+--------
jane | doe | j_doe | 1
john | doe | johnd | 2
willem | doe | w_doe | 3
rick | smith | slash | 4
(4 rows)
-- should fail
--
UPDATE users SET fname = 'william' WHERE fname = 'willem';
-- should modify william to willem and create username
--
INSERT INTO users (fname, lname) VALUES ('william', 'smith');
INSERT INTO users (fname, lname, username) VALUES ('charles', 'darwin', 'beagle');
SELECT * FROM users;
fname | lname | username | userid
---------+--------+----------+--------
jane | doe | j_doe | 1
john | doe | johnd | 2
willem | doe | w_doe | 3
rick | smith | slash | 4
willem | smith | w_smith | 5
charles | darwin | beagle | 6
(6 rows)
-- dump trigger data
CREATE TABLE trigger_test
(i int, v text );
CREATE FUNCTION trigger_data() returns trigger language plpythonu as $$
if TD.has_key('relid'):
TD['relid'] = "bogus:12345"
skeys = TD.keys()
skeys.sort()
for key in skeys:
val = TD[key]
plpy.notice("TD[" + key + "] => " + str(val))
return None
$$;
CREATE TRIGGER show_trigger_data_trig
BEFORE INSERT OR UPDATE OR DELETE ON trigger_test
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
insert into trigger_test values(1,'insert');
NOTICE: ("TD[args] => ['23', 'skidoo']",)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[event] => INSERT',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[level] => ROW',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[name] => show_trigger_data_trig',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ("TD[new] => {'i': 1, 'v': 'insert'}",)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[old] => None',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[relid] => bogus:12345',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[table_name] => trigger_test',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[table_schema] => public',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[when] => BEFORE',)
CONTEXT: PL/Python function "trigger_data"
update trigger_test set v = 'update' where i = 1;
NOTICE: ("TD[args] => ['23', 'skidoo']",)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[event] => UPDATE',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[level] => ROW',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[name] => show_trigger_data_trig',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ("TD[new] => {'i': 1, 'v': 'update'}",)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ("TD[old] => {'i': 1, 'v': 'insert'}",)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[relid] => bogus:12345',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[table_name] => trigger_test',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[table_schema] => public',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[when] => BEFORE',)
CONTEXT: PL/Python function "trigger_data"
delete from trigger_test;
NOTICE: ("TD[args] => ['23', 'skidoo']",)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[event] => DELETE',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[level] => ROW',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[name] => show_trigger_data_trig',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[new] => None',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ("TD[old] => {'i': 1, 'v': 'update'}",)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[relid] => bogus:12345',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[table_name] => trigger_test',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[table_schema] => public',)
CONTEXT: PL/Python function "trigger_data"
NOTICE: ('TD[when] => BEFORE',)
CONTEXT: PL/Python function "trigger_data"
DROP TRIGGER show_trigger_data_trig on trigger_test;
DROP FUNCTION trigger_data();
|
