blob: b042ae926e77ae35c2488f74a7d243d120b8c5d1 (
plain)
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
|
-- 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;
-- 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;
-- 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');
update trigger_test set v = 'update' where i = 1;
delete from trigger_test;
DROP TRIGGER show_trigger_data_trig on trigger_test;
DROP FUNCTION trigger_data();
|
