1--
2-- TRIGGERS
3--
4create table pkeys (pkey1 int4 not null, pkey2 text not null);
5create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
6create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
7create index fkeys_i on fkeys (fkey1, fkey2);
8create index fkeys2_i on fkeys2 (fkey21, fkey22);
9create index fkeys2p_i on fkeys2 (pkey23);
10insert into pkeys values (10, '1');
11insert into pkeys values (20, '2');
12insert into pkeys values (30, '3');
13insert into pkeys values (40, '4');
14insert into pkeys values (50, '5');
15insert into pkeys values (60, '6');
16create unique index pkeys_i on pkeys (pkey1, pkey2);
17--
18-- For fkeys:
19-- 	(fkey1, fkey2)	--> pkeys (pkey1, pkey2)
20-- 	(fkey3)		--> fkeys2 (pkey23)
21--
22create trigger check_fkeys_pkey_exist
23	before insert or update on fkeys
24	for each row
25	execute function
26	check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
27create trigger check_fkeys_pkey2_exist
28	before insert or update on fkeys
29	for each row
30	execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23');
31--
32-- For fkeys2:
33-- 	(fkey21, fkey22)	--> pkeys (pkey1, pkey2)
34--
35create trigger check_fkeys2_pkey_exist
36	before insert or update on fkeys2
37	for each row
38	execute procedure
39	check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
40-- Test comments
41COMMENT ON TRIGGER check_fkeys2_pkey_bad ON fkeys2 IS 'wrong';
42ERROR:  trigger "check_fkeys2_pkey_bad" for table "fkeys2" does not exist
43COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS 'right';
44COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS NULL;
45--
46-- For pkeys:
47-- 	ON DELETE/UPDATE (pkey1, pkey2) CASCADE:
48-- 		fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22)
49--
50create trigger check_pkeys_fkey_cascade
51	before delete or update on pkeys
52	for each row
53	execute procedure
54	check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',
55	'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22');
56--
57-- For fkeys2:
58-- 	ON DELETE/UPDATE (pkey23) RESTRICT:
59-- 		fkeys (fkey3)
60--
61create trigger check_fkeys2_fkey_restrict
62	before delete or update on fkeys2
63	for each row
64	execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');
65insert into fkeys2 values (10, '1', 1);
66insert into fkeys2 values (30, '3', 2);
67insert into fkeys2 values (40, '4', 5);
68insert into fkeys2 values (50, '5', 3);
69-- no key in pkeys
70insert into fkeys2 values (70, '5', 3);
71ERROR:  tuple references non-existent key
72DETAIL:  Trigger "check_fkeys2_pkey_exist" found tuple referencing non-existent key in "pkeys".
73insert into fkeys values (10, '1', 2);
74insert into fkeys values (30, '3', 3);
75insert into fkeys values (40, '4', 2);
76insert into fkeys values (50, '5', 2);
77-- no key in pkeys
78insert into fkeys values (70, '5', 1);
79ERROR:  tuple references non-existent key
80DETAIL:  Trigger "check_fkeys_pkey_exist" found tuple referencing non-existent key in "pkeys".
81-- no key in fkeys2
82insert into fkeys values (60, '6', 4);
83ERROR:  tuple references non-existent key
84DETAIL:  Trigger "check_fkeys_pkey2_exist" found tuple referencing non-existent key in "fkeys2".
85delete from pkeys where pkey1 = 30 and pkey2 = '3';
86NOTICE:  check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
87ERROR:  "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys"
88CONTEXT:  SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 "
89delete from pkeys where pkey1 = 40 and pkey2 = '4';
90NOTICE:  check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
91NOTICE:  check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
92update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
93NOTICE:  check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
94ERROR:  "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys"
95CONTEXT:  SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 "
96update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
97NOTICE:  check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
98NOTICE:  check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
99SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
100       action_order, action_condition, action_orientation, action_timing,
101       action_reference_old_table, action_reference_new_table
102  FROM information_schema.triggers
103  WHERE event_object_table in ('pkeys', 'fkeys', 'fkeys2')
104  ORDER BY trigger_name COLLATE "C", 2;
105        trigger_name        | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table
106----------------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+----------------------------
107 check_fkeys2_fkey_restrict | DELETE             | public              | fkeys2             |            1 |                  | ROW                | BEFORE        |                            |
108 check_fkeys2_fkey_restrict | UPDATE             | public              | fkeys2             |            1 |                  | ROW                | BEFORE        |                            |
109 check_fkeys2_pkey_exist    | INSERT             | public              | fkeys2             |            1 |                  | ROW                | BEFORE        |                            |
110 check_fkeys2_pkey_exist    | UPDATE             | public              | fkeys2             |            2 |                  | ROW                | BEFORE        |                            |
111 check_fkeys_pkey2_exist    | INSERT             | public              | fkeys              |            1 |                  | ROW                | BEFORE        |                            |
112 check_fkeys_pkey2_exist    | UPDATE             | public              | fkeys              |            1 |                  | ROW                | BEFORE        |                            |
113 check_fkeys_pkey_exist     | INSERT             | public              | fkeys              |            2 |                  | ROW                | BEFORE        |                            |
114 check_fkeys_pkey_exist     | UPDATE             | public              | fkeys              |            2 |                  | ROW                | BEFORE        |                            |
115 check_pkeys_fkey_cascade   | DELETE             | public              | pkeys              |            1 |                  | ROW                | BEFORE        |                            |
116 check_pkeys_fkey_cascade   | UPDATE             | public              | pkeys              |            1 |                  | ROW                | BEFORE        |                            |
117(10 rows)
118
119DROP TABLE pkeys;
120DROP TABLE fkeys;
121DROP TABLE fkeys2;
122-- Check behavior when trigger returns unmodified trigtuple
123create table trigtest (f1 int, f2 text);
124create trigger trigger_return_old
125	before insert or delete or update on trigtest
126	for each row execute procedure trigger_return_old();
127insert into trigtest values(1, 'foo');
128select * from trigtest;
129 f1 | f2
130----+-----
131  1 | foo
132(1 row)
133
134update trigtest set f2 = f2 || 'bar';
135select * from trigtest;
136 f1 | f2
137----+-----
138  1 | foo
139(1 row)
140
141delete from trigtest;
142select * from trigtest;
143 f1 | f2
144----+----
145(0 rows)
146
147-- Also check what happens when such a trigger runs before or after others
148create function f1_times_10() returns trigger as
149$$ begin new.f1 := new.f1 * 10; return new; end $$ language plpgsql;
150create trigger trigger_alpha
151	before insert or update on trigtest
152	for each row execute procedure f1_times_10();
153insert into trigtest values(1, 'foo');
154select * from trigtest;
155 f1 | f2
156----+-----
157 10 | foo
158(1 row)
159
160update trigtest set f2 = f2 || 'bar';
161select * from trigtest;
162 f1 | f2
163----+-----
164 10 | foo
165(1 row)
166
167delete from trigtest;
168select * from trigtest;
169 f1 | f2
170----+----
171(0 rows)
172
173create trigger trigger_zed
174	before insert or update on trigtest
175	for each row execute procedure f1_times_10();
176insert into trigtest values(1, 'foo');
177select * from trigtest;
178 f1  | f2
179-----+-----
180 100 | foo
181(1 row)
182
183update trigtest set f2 = f2 || 'bar';
184select * from trigtest;
185  f1  | f2
186------+-----
187 1000 | foo
188(1 row)
189
190delete from trigtest;
191select * from trigtest;
192 f1 | f2
193----+----
194(0 rows)
195
196drop trigger trigger_alpha on trigtest;
197insert into trigtest values(1, 'foo');
198select * from trigtest;
199 f1 | f2
200----+-----
201 10 | foo
202(1 row)
203
204update trigtest set f2 = f2 || 'bar';
205select * from trigtest;
206 f1  | f2
207-----+-----
208 100 | foo
209(1 row)
210
211delete from trigtest;
212select * from trigtest;
213 f1 | f2
214----+----
215(0 rows)
216
217drop table trigtest;
218-- Check behavior with an implicit column default, too (bug #16644)
219create table trigtest (
220  a integer,
221  b bool default true not null,
222  c text default 'xyzzy' not null);
223create trigger trigger_return_old
224	before insert or delete or update on trigtest
225	for each row execute procedure trigger_return_old();
226insert into trigtest values(1);
227select * from trigtest;
228 a | b |   c
229---+---+-------
230 1 | t | xyzzy
231(1 row)
232
233alter table trigtest add column d integer default 42 not null;
234select * from trigtest;
235 a | b |   c   | d
236---+---+-------+----
237 1 | t | xyzzy | 42
238(1 row)
239
240update trigtest set a = 2 where a = 1 returning *;
241 a | b |   c   | d
242---+---+-------+----
243 1 | t | xyzzy | 42
244(1 row)
245
246select * from trigtest;
247 a | b |   c   | d
248---+---+-------+----
249 1 | t | xyzzy | 42
250(1 row)
251
252alter table trigtest drop column b;
253select * from trigtest;
254 a |   c   | d
255---+-------+----
256 1 | xyzzy | 42
257(1 row)
258
259update trigtest set a = 2 where a = 1 returning *;
260 a |   c   | d
261---+-------+----
262 1 | xyzzy | 42
263(1 row)
264
265select * from trigtest;
266 a |   c   | d
267---+-------+----
268 1 | xyzzy | 42
269(1 row)
270
271drop table trigtest;
272create sequence ttdummy_seq increment 10 start 0 minvalue 0;
273create table tttest (
274	price_id	int4,
275	price_val	int4,
276	price_on	int4,
277	price_off	int4 default 999999
278);
279create trigger ttdummy
280	before delete or update on tttest
281	for each row
282	execute procedure
283	ttdummy (price_on, price_off);
284create trigger ttserial
285	before insert or update on tttest
286	for each row
287	execute procedure
288	autoinc (price_on, ttdummy_seq);
289insert into tttest values (1, 1, null);
290insert into tttest values (2, 2, null);
291insert into tttest values (3, 3, 0);
292select * from tttest;
293 price_id | price_val | price_on | price_off
294----------+-----------+----------+-----------
295        1 |         1 |       10 |    999999
296        2 |         2 |       20 |    999999
297        3 |         3 |       30 |    999999
298(3 rows)
299
300delete from tttest where price_id = 2;
301select * from tttest;
302 price_id | price_val | price_on | price_off
303----------+-----------+----------+-----------
304        1 |         1 |       10 |    999999
305        3 |         3 |       30 |    999999
306        2 |         2 |       20 |        40
307(3 rows)
308
309-- what do we see ?
310-- get current prices
311select * from tttest where price_off = 999999;
312 price_id | price_val | price_on | price_off
313----------+-----------+----------+-----------
314        1 |         1 |       10 |    999999
315        3 |         3 |       30 |    999999
316(2 rows)
317
318-- change price for price_id == 3
319update tttest set price_val = 30 where price_id = 3;
320select * from tttest;
321 price_id | price_val | price_on | price_off
322----------+-----------+----------+-----------
323        1 |         1 |       10 |    999999
324        2 |         2 |       20 |        40
325        3 |        30 |       50 |    999999
326        3 |         3 |       30 |        50
327(4 rows)
328
329-- now we want to change pric_id in ALL tuples
330-- this gets us not what we need
331update tttest set price_id = 5 where price_id = 3;
332select * from tttest;
333 price_id | price_val | price_on | price_off
334----------+-----------+----------+-----------
335        1 |         1 |       10 |    999999
336        2 |         2 |       20 |        40
337        3 |         3 |       30 |        50
338        5 |        30 |       60 |    999999
339        3 |        30 |       50 |        60
340(5 rows)
341
342-- restore data as before last update:
343select set_ttdummy(0);
344 set_ttdummy
345-------------
346           1
347(1 row)
348
349delete from tttest where price_id = 5;
350update tttest set price_off = 999999 where price_val = 30;
351select * from tttest;
352 price_id | price_val | price_on | price_off
353----------+-----------+----------+-----------
354        1 |         1 |       10 |    999999
355        2 |         2 |       20 |        40
356        3 |         3 |       30 |        50
357        3 |        30 |       50 |    999999
358(4 rows)
359
360-- and try change price_id now!
361update tttest set price_id = 5 where price_id = 3;
362select * from tttest;
363 price_id | price_val | price_on | price_off
364----------+-----------+----------+-----------
365        1 |         1 |       10 |    999999
366        2 |         2 |       20 |        40
367        5 |         3 |       30 |        50
368        5 |        30 |       50 |    999999
369(4 rows)
370
371-- isn't it what we need ?
372select set_ttdummy(1);
373 set_ttdummy
374-------------
375           0
376(1 row)
377
378-- we want to correct some "date"
379update tttest set price_on = -1 where price_id = 1;
380ERROR:  ttdummy (tttest): you cannot change price_on and/or price_off columns (use set_ttdummy)
381-- but this doesn't work
382-- try in this way
383select set_ttdummy(0);
384 set_ttdummy
385-------------
386           1
387(1 row)
388
389update tttest set price_on = -1 where price_id = 1;
390select * from tttest;
391 price_id | price_val | price_on | price_off
392----------+-----------+----------+-----------
393        2 |         2 |       20 |        40
394        5 |         3 |       30 |        50
395        5 |        30 |       50 |    999999
396        1 |         1 |       -1 |    999999
397(4 rows)
398
399-- isn't it what we need ?
400-- get price for price_id == 5 as it was @ "date" 35
401select * from tttest where price_on <= 35 and price_off > 35 and price_id = 5;
402 price_id | price_val | price_on | price_off
403----------+-----------+----------+-----------
404        5 |         3 |       30 |        50
405(1 row)
406
407drop table tttest;
408drop sequence ttdummy_seq;
409--
410-- tests for per-statement triggers
411--
412CREATE TABLE log_table (tstamp timestamp default timeofday()::timestamp);
413CREATE TABLE main_table (a int unique, b int);
414COPY main_table (a,b) FROM stdin;
415CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
416BEGIN
417	RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
418	RETURN NULL;
419END;';
420CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
421FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt');
422CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
423FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt');
424--
425-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified,
426-- CREATE TRIGGER should default to 'FOR EACH STATEMENT'
427--
428CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_table
429EXECUTE PROCEDURE trigger_func('after_upd_stmt');
430-- Both insert and update statement level triggers (before and after) should
431-- fire.  Doesn't fire UPDATE before trigger, but only because one isn't
432-- defined.
433INSERT INTO main_table (a, b) VALUES (5, 10) ON CONFLICT (a)
434  DO UPDATE SET b = EXCLUDED.b;
435NOTICE:  trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
436NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
437NOTICE:  trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
438CREATE TRIGGER after_upd_row_trig AFTER UPDATE ON main_table
439FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_row');
440INSERT INTO main_table DEFAULT VALUES;
441NOTICE:  trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
442NOTICE:  trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
443UPDATE main_table SET a = a + 1 WHERE b < 30;
444NOTICE:  trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW
445NOTICE:  trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW
446NOTICE:  trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW
447NOTICE:  trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW
448NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
449-- UPDATE that effects zero rows should still call per-statement trigger
450UPDATE main_table SET a = a + 2 WHERE b > 100;
451NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
452-- constraint now unneeded
453ALTER TABLE main_table DROP CONSTRAINT main_table_a_key;
454-- COPY should fire per-row and per-statement INSERT triggers
455COPY main_table (a, b) FROM stdin;
456NOTICE:  trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
457NOTICE:  trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
458SELECT * FROM main_table ORDER BY a, b;
459 a  | b
460----+----
461  6 | 10
462 21 | 20
463 30 | 40
464 31 | 10
465 50 | 35
466 50 | 60
467 81 | 15
468    |
469(8 rows)
470
471--
472-- test triggers with WHEN clause
473--
474CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table
475FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('modified_a');
476CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table
477FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE PROCEDURE trigger_func('modified_any');
478CREATE TRIGGER insert_a AFTER INSERT ON main_table
479FOR EACH ROW WHEN (NEW.a = 123) EXECUTE PROCEDURE trigger_func('insert_a');
480CREATE TRIGGER delete_a AFTER DELETE ON main_table
481FOR EACH ROW WHEN (OLD.a = 123) EXECUTE PROCEDURE trigger_func('delete_a');
482CREATE TRIGGER insert_when BEFORE INSERT ON main_table
483FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('insert_when');
484CREATE TRIGGER delete_when AFTER DELETE ON main_table
485FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('delete_when');
486SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
487       action_order, action_condition, action_orientation, action_timing,
488       action_reference_old_table, action_reference_new_table
489  FROM information_schema.triggers
490  WHERE event_object_table IN ('main_table')
491  ORDER BY trigger_name COLLATE "C", 2;
492     trigger_name     | event_manipulation | event_object_schema | event_object_table | action_order |        action_condition        | action_orientation | action_timing | action_reference_old_table | action_reference_new_table
493----------------------+--------------------+---------------------+--------------------+--------------+--------------------------------+--------------------+---------------+----------------------------+----------------------------
494 after_ins_stmt_trig  | INSERT             | public              | main_table         |            1 |                                | STATEMENT          | AFTER         |                            |
495 after_upd_row_trig   | UPDATE             | public              | main_table         |            1 |                                | ROW                | AFTER         |                            |
496 after_upd_stmt_trig  | UPDATE             | public              | main_table         |            1 |                                | STATEMENT          | AFTER         |                            |
497 before_ins_stmt_trig | INSERT             | public              | main_table         |            1 |                                | STATEMENT          | BEFORE        |                            |
498 delete_a             | DELETE             | public              | main_table         |            1 | (old.a = 123)                  | ROW                | AFTER         |                            |
499 delete_when          | DELETE             | public              | main_table         |            1 | true                           | STATEMENT          | AFTER         |                            |
500 insert_a             | INSERT             | public              | main_table         |            1 | (new.a = 123)                  | ROW                | AFTER         |                            |
501 insert_when          | INSERT             | public              | main_table         |            2 | true                           | STATEMENT          | BEFORE        |                            |
502 modified_a           | UPDATE             | public              | main_table         |            1 | (old.a <> new.a)               | ROW                | BEFORE        |                            |
503 modified_any         | UPDATE             | public              | main_table         |            2 | (old.* IS DISTINCT FROM new.*) | ROW                | BEFORE        |                            |
504(10 rows)
505
506INSERT INTO main_table (a) VALUES (123), (456);
507NOTICE:  trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
508NOTICE:  trigger_func(insert_when) called: action = INSERT, when = BEFORE, level = STATEMENT
509NOTICE:  trigger_func(insert_a) called: action = INSERT, when = AFTER, level = ROW
510NOTICE:  trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
511COPY main_table FROM stdin;
512NOTICE:  trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
513NOTICE:  trigger_func(insert_when) called: action = INSERT, when = BEFORE, level = STATEMENT
514NOTICE:  trigger_func(insert_a) called: action = INSERT, when = AFTER, level = ROW
515NOTICE:  trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
516DELETE FROM main_table WHERE a IN (123, 456);
517NOTICE:  trigger_func(delete_a) called: action = DELETE, when = AFTER, level = ROW
518NOTICE:  trigger_func(delete_a) called: action = DELETE, when = AFTER, level = ROW
519NOTICE:  trigger_func(delete_when) called: action = DELETE, when = AFTER, level = STATEMENT
520UPDATE main_table SET a = 50, b = 60;
521NOTICE:  trigger_func(modified_any) called: action = UPDATE, when = BEFORE, level = ROW
522NOTICE:  trigger_func(modified_any) called: action = UPDATE, when = BEFORE, level = ROW
523NOTICE:  trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW
524NOTICE:  trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW
525NOTICE:  trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW
526NOTICE:  trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW
527NOTICE:  trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW
528NOTICE:  trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW
529NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
530SELECT * FROM main_table ORDER BY a, b;
531 a  | b
532----+----
533  6 | 10
534 21 | 20
535 30 | 40
536 31 | 10
537 50 | 35
538 50 | 60
539 81 | 15
540    |
541(8 rows)
542
543SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
544                                                             pg_get_triggerdef
545-------------------------------------------------------------------------------------------------------------------------------------------
546 CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.a <> new.a) EXECUTE FUNCTION trigger_func('modified_a')
547(1 row)
548
549SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
550                                                                 pg_get_triggerdef
551----------------------------------------------------------------------------------------------------------------------------------------------------
552 CREATE TRIGGER modified_a BEFORE UPDATE OF a ON public.main_table FOR EACH ROW WHEN ((old.a <> new.a)) EXECUTE FUNCTION trigger_func('modified_a')
553(1 row)
554
555SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any';
556                                                                      pg_get_triggerdef
557-------------------------------------------------------------------------------------------------------------------------------------------------------------
558 CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.* IS DISTINCT FROM new.*) EXECUTE FUNCTION trigger_func('modified_any')
559(1 row)
560
561-- Test RENAME TRIGGER
562ALTER TRIGGER modified_a ON main_table RENAME TO modified_modified_a;
563SELECT count(*) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
564 count
565-------
566     0
567(1 row)
568
569SELECT count(*) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_modified_a';
570 count
571-------
572     1
573(1 row)
574
575DROP TRIGGER modified_modified_a ON main_table;
576DROP TRIGGER modified_any ON main_table;
577DROP TRIGGER insert_a ON main_table;
578DROP TRIGGER delete_a ON main_table;
579DROP TRIGGER insert_when ON main_table;
580DROP TRIGGER delete_when ON main_table;
581-- Test WHEN condition accessing system columns.
582create table table_with_oids(a int);
583insert into table_with_oids values (1);
584create trigger oid_unchanged_trig after update on table_with_oids
585	for each row
586	when (new.tableoid = old.tableoid AND new.tableoid <> 0)
587	execute procedure trigger_func('after_upd_oid_unchanged');
588update table_with_oids set a = a + 1;
589NOTICE:  trigger_func(after_upd_oid_unchanged) called: action = UPDATE, when = AFTER, level = ROW
590drop table table_with_oids;
591-- Test column-level triggers
592DROP TRIGGER after_upd_row_trig ON main_table;
593CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON main_table
594FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_a_row');
595CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON main_table
596FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_b_row');
597CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON main_table
598FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_a_b_row');
599CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON main_table
600FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_upd_a_stmt');
601CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON main_table
602FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_upd_b_stmt');
603SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig';
604                                                                pg_get_triggerdef
605-------------------------------------------------------------------------------------------------------------------------------------------------
606 CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON public.main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_a_b_row')
607(1 row)
608
609UPDATE main_table SET a = 50;
610NOTICE:  trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
611NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
612NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
613NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
614NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
615NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
616NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
617NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
618NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
619NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
620UPDATE main_table SET b = 10;
621NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
622NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
623NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
624NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
625NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
626NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
627NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
628NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
629NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
630NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
631NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
632NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
633NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
634NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
635NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
636NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
637NOTICE:  trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
638NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
639--
640-- Test case for bug with BEFORE trigger followed by AFTER trigger with WHEN
641--
642CREATE TABLE some_t (some_col boolean NOT NULL);
643CREATE FUNCTION dummy_update_func() RETURNS trigger AS $$
644BEGIN
645  RAISE NOTICE 'dummy_update_func(%) called: action = %, old = %, new = %',
646    TG_ARGV[0], TG_OP, OLD, NEW;
647  RETURN NEW;
648END;
649$$ LANGUAGE plpgsql;
650CREATE TRIGGER some_trig_before BEFORE UPDATE ON some_t FOR EACH ROW
651  EXECUTE PROCEDURE dummy_update_func('before');
652CREATE TRIGGER some_trig_aftera AFTER UPDATE ON some_t FOR EACH ROW
653  WHEN (NOT OLD.some_col AND NEW.some_col)
654  EXECUTE PROCEDURE dummy_update_func('aftera');
655CREATE TRIGGER some_trig_afterb AFTER UPDATE ON some_t FOR EACH ROW
656  WHEN (NOT NEW.some_col)
657  EXECUTE PROCEDURE dummy_update_func('afterb');
658INSERT INTO some_t VALUES (TRUE);
659UPDATE some_t SET some_col = TRUE;
660NOTICE:  dummy_update_func(before) called: action = UPDATE, old = (t), new = (t)
661UPDATE some_t SET some_col = FALSE;
662NOTICE:  dummy_update_func(before) called: action = UPDATE, old = (t), new = (f)
663NOTICE:  dummy_update_func(afterb) called: action = UPDATE, old = (t), new = (f)
664UPDATE some_t SET some_col = TRUE;
665NOTICE:  dummy_update_func(before) called: action = UPDATE, old = (f), new = (t)
666NOTICE:  dummy_update_func(aftera) called: action = UPDATE, old = (f), new = (t)
667DROP TABLE some_t;
668-- bogus cases
669CREATE TRIGGER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_table
670FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_and_col');
671ERROR:  duplicate trigger events specified at or near "ON"
672LINE 1: ...ER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_ta...
673                                                             ^
674CREATE TRIGGER error_upd_a_a BEFORE UPDATE OF a, a ON main_table
675FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_a_a');
676ERROR:  column "a" specified more than once
677CREATE TRIGGER error_ins_a BEFORE INSERT OF a ON main_table
678FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_ins_a');
679ERROR:  syntax error at or near "OF"
680LINE 1: CREATE TRIGGER error_ins_a BEFORE INSERT OF a ON main_table
681                                                 ^
682CREATE TRIGGER error_ins_when BEFORE INSERT OR UPDATE ON main_table
683FOR EACH ROW WHEN (OLD.a <> NEW.a)
684EXECUTE PROCEDURE trigger_func('error_ins_old');
685ERROR:  INSERT trigger's WHEN condition cannot reference OLD values
686LINE 2: FOR EACH ROW WHEN (OLD.a <> NEW.a)
687                           ^
688CREATE TRIGGER error_del_when BEFORE DELETE OR UPDATE ON main_table
689FOR EACH ROW WHEN (OLD.a <> NEW.a)
690EXECUTE PROCEDURE trigger_func('error_del_new');
691ERROR:  DELETE trigger's WHEN condition cannot reference NEW values
692LINE 2: FOR EACH ROW WHEN (OLD.a <> NEW.a)
693                                    ^
694CREATE TRIGGER error_del_when BEFORE INSERT OR UPDATE ON main_table
695FOR EACH ROW WHEN (NEW.tableoid <> 0)
696EXECUTE PROCEDURE trigger_func('error_when_sys_column');
697ERROR:  BEFORE trigger's WHEN condition cannot reference NEW system columns
698LINE 2: FOR EACH ROW WHEN (NEW.tableoid <> 0)
699                           ^
700CREATE TRIGGER error_stmt_when BEFORE UPDATE OF a ON main_table
701FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*)
702EXECUTE PROCEDURE trigger_func('error_stmt_when');
703ERROR:  statement trigger's WHEN condition cannot reference column values
704LINE 2: FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*)
705                                 ^
706-- check dependency restrictions
707ALTER TABLE main_table DROP COLUMN b;
708ERROR:  cannot drop column b of table main_table because other objects depend on it
709DETAIL:  trigger after_upd_b_row_trig on table main_table depends on column b of table main_table
710trigger after_upd_a_b_row_trig on table main_table depends on column b of table main_table
711trigger after_upd_b_stmt_trig on table main_table depends on column b of table main_table
712HINT:  Use DROP ... CASCADE to drop the dependent objects too.
713-- this should succeed, but we'll roll it back to keep the triggers around
714begin;
715DROP TRIGGER after_upd_a_b_row_trig ON main_table;
716DROP TRIGGER after_upd_b_row_trig ON main_table;
717DROP TRIGGER after_upd_b_stmt_trig ON main_table;
718ALTER TABLE main_table DROP COLUMN b;
719rollback;
720-- Test enable/disable triggers
721create table trigtest (i serial primary key);
722-- test that disabling RI triggers works
723create table trigtest2 (i int references trigtest(i) on delete cascade);
724create function trigtest() returns trigger as $$
725begin
726	raise notice '% % % %', TG_TABLE_NAME, TG_OP, TG_WHEN, TG_LEVEL;
727	return new;
728end;$$ language plpgsql;
729create trigger trigtest_b_row_tg before insert or update or delete on trigtest
730for each row execute procedure trigtest();
731create trigger trigtest_a_row_tg after insert or update or delete on trigtest
732for each row execute procedure trigtest();
733create trigger trigtest_b_stmt_tg before insert or update or delete on trigtest
734for each statement execute procedure trigtest();
735create trigger trigtest_a_stmt_tg after insert or update or delete on trigtest
736for each statement execute procedure trigtest();
737insert into trigtest default values;
738NOTICE:  trigtest INSERT BEFORE STATEMENT
739NOTICE:  trigtest INSERT BEFORE ROW
740NOTICE:  trigtest INSERT AFTER ROW
741NOTICE:  trigtest INSERT AFTER STATEMENT
742alter table trigtest disable trigger trigtest_b_row_tg;
743insert into trigtest default values;
744NOTICE:  trigtest INSERT BEFORE STATEMENT
745NOTICE:  trigtest INSERT AFTER ROW
746NOTICE:  trigtest INSERT AFTER STATEMENT
747alter table trigtest disable trigger user;
748insert into trigtest default values;
749alter table trigtest enable trigger trigtest_a_stmt_tg;
750insert into trigtest default values;
751NOTICE:  trigtest INSERT AFTER STATEMENT
752set session_replication_role = replica;
753insert into trigtest default values;  -- does not trigger
754alter table trigtest enable always trigger trigtest_a_stmt_tg;
755insert into trigtest default values;  -- now it does
756NOTICE:  trigtest INSERT AFTER STATEMENT
757reset session_replication_role;
758insert into trigtest2 values(1);
759insert into trigtest2 values(2);
760delete from trigtest where i=2;
761NOTICE:  trigtest DELETE AFTER STATEMENT
762select * from trigtest2;
763 i
764---
765 1
766(1 row)
767
768alter table trigtest disable trigger all;
769delete from trigtest where i=1;
770select * from trigtest2;
771 i
772---
773 1
774(1 row)
775
776-- ensure we still insert, even when all triggers are disabled
777insert into trigtest default values;
778select *  from trigtest;
779 i
780---
781 3
782 4
783 5
784 6
785 7
786(5 rows)
787
788drop table trigtest2;
789drop table trigtest;
790-- dump trigger data
791CREATE TABLE trigger_test (
792        i int,
793        v varchar
794);
795CREATE OR REPLACE FUNCTION trigger_data()  RETURNS trigger
796LANGUAGE plpgsql AS $$
797
798declare
799
800	argstr text;
801	relid text;
802
803begin
804
805	relid := TG_relid::regclass;
806
807	-- plpgsql can't discover its trigger data in a hash like perl and python
808	-- can, or by a sort of reflection like tcl can,
809	-- so we have to hard code the names.
810	raise NOTICE 'TG_NAME: %', TG_name;
811	raise NOTICE 'TG_WHEN: %', TG_when;
812	raise NOTICE 'TG_LEVEL: %', TG_level;
813	raise NOTICE 'TG_OP: %', TG_op;
814	raise NOTICE 'TG_RELID::regclass: %', relid;
815	raise NOTICE 'TG_RELNAME: %', TG_relname;
816	raise NOTICE 'TG_TABLE_NAME: %', TG_table_name;
817	raise NOTICE 'TG_TABLE_SCHEMA: %', TG_table_schema;
818	raise NOTICE 'TG_NARGS: %', TG_nargs;
819
820	argstr := '[';
821	for i in 0 .. TG_nargs - 1 loop
822		if i > 0 then
823			argstr := argstr || ', ';
824		end if;
825		argstr := argstr || TG_argv[i];
826	end loop;
827	argstr := argstr || ']';
828	raise NOTICE 'TG_ARGV: %', argstr;
829
830	if TG_OP != 'INSERT' then
831		raise NOTICE 'OLD: %', OLD;
832	end if;
833
834	if TG_OP != 'DELETE' then
835		raise NOTICE 'NEW: %', NEW;
836	end if;
837
838	if TG_OP = 'DELETE' then
839		return OLD;
840	else
841		return NEW;
842	end if;
843
844end;
845$$;
846CREATE TRIGGER show_trigger_data_trig
847BEFORE INSERT OR UPDATE OR DELETE ON trigger_test
848FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
849insert into trigger_test values(1,'insert');
850NOTICE:  TG_NAME: show_trigger_data_trig
851NOTICE:  TG_WHEN: BEFORE
852NOTICE:  TG_LEVEL: ROW
853NOTICE:  TG_OP: INSERT
854NOTICE:  TG_RELID::regclass: trigger_test
855NOTICE:  TG_RELNAME: trigger_test
856NOTICE:  TG_TABLE_NAME: trigger_test
857NOTICE:  TG_TABLE_SCHEMA: public
858NOTICE:  TG_NARGS: 2
859NOTICE:  TG_ARGV: [23, skidoo]
860NOTICE:  NEW: (1,insert)
861update trigger_test set v = 'update' where i = 1;
862NOTICE:  TG_NAME: show_trigger_data_trig
863NOTICE:  TG_WHEN: BEFORE
864NOTICE:  TG_LEVEL: ROW
865NOTICE:  TG_OP: UPDATE
866NOTICE:  TG_RELID::regclass: trigger_test
867NOTICE:  TG_RELNAME: trigger_test
868NOTICE:  TG_TABLE_NAME: trigger_test
869NOTICE:  TG_TABLE_SCHEMA: public
870NOTICE:  TG_NARGS: 2
871NOTICE:  TG_ARGV: [23, skidoo]
872NOTICE:  OLD: (1,insert)
873NOTICE:  NEW: (1,update)
874delete from trigger_test;
875NOTICE:  TG_NAME: show_trigger_data_trig
876NOTICE:  TG_WHEN: BEFORE
877NOTICE:  TG_LEVEL: ROW
878NOTICE:  TG_OP: DELETE
879NOTICE:  TG_RELID::regclass: trigger_test
880NOTICE:  TG_RELNAME: trigger_test
881NOTICE:  TG_TABLE_NAME: trigger_test
882NOTICE:  TG_TABLE_SCHEMA: public
883NOTICE:  TG_NARGS: 2
884NOTICE:  TG_ARGV: [23, skidoo]
885NOTICE:  OLD: (1,update)
886DROP TRIGGER show_trigger_data_trig on trigger_test;
887DROP FUNCTION trigger_data();
888DROP TABLE trigger_test;
889--
890-- Test use of row comparisons on OLD/NEW
891--
892CREATE TABLE trigger_test (f1 int, f2 text, f3 text);
893-- this is the obvious (and wrong...) way to compare rows
894CREATE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$
895begin
896	if row(old.*) = row(new.*) then
897		raise notice 'row % not changed', new.f1;
898	else
899		raise notice 'row % changed', new.f1;
900	end if;
901	return new;
902end$$;
903CREATE TRIGGER t
904BEFORE UPDATE ON trigger_test
905FOR EACH ROW EXECUTE PROCEDURE mytrigger();
906INSERT INTO trigger_test VALUES(1, 'foo', 'bar');
907INSERT INTO trigger_test VALUES(2, 'baz', 'quux');
908UPDATE trigger_test SET f3 = 'bar';
909NOTICE:  row 1 not changed
910NOTICE:  row 2 changed
911UPDATE trigger_test SET f3 = NULL;
912NOTICE:  row 1 changed
913NOTICE:  row 2 changed
914-- this demonstrates that the above isn't really working as desired:
915UPDATE trigger_test SET f3 = NULL;
916NOTICE:  row 1 changed
917NOTICE:  row 2 changed
918-- the right way when considering nulls is
919CREATE OR REPLACE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$
920begin
921	if row(old.*) is distinct from row(new.*) then
922		raise notice 'row % changed', new.f1;
923	else
924		raise notice 'row % not changed', new.f1;
925	end if;
926	return new;
927end$$;
928UPDATE trigger_test SET f3 = 'bar';
929NOTICE:  row 1 changed
930NOTICE:  row 2 changed
931UPDATE trigger_test SET f3 = NULL;
932NOTICE:  row 1 changed
933NOTICE:  row 2 changed
934UPDATE trigger_test SET f3 = NULL;
935NOTICE:  row 1 not changed
936NOTICE:  row 2 not changed
937DROP TABLE trigger_test;
938DROP FUNCTION mytrigger();
939-- Test snapshot management in serializable transactions involving triggers
940-- per bug report in 6bc73d4c0910042358k3d1adff3qa36f8df75198ecea@mail.gmail.com
941CREATE FUNCTION serializable_update_trig() RETURNS trigger LANGUAGE plpgsql AS
942$$
943declare
944	rec record;
945begin
946	new.description = 'updated in trigger';
947	return new;
948end;
949$$;
950CREATE TABLE serializable_update_tab (
951	id int,
952	filler  text,
953	description text
954);
955CREATE TRIGGER serializable_update_trig BEFORE UPDATE ON serializable_update_tab
956	FOR EACH ROW EXECUTE PROCEDURE serializable_update_trig();
957INSERT INTO serializable_update_tab SELECT a, repeat('xyzxz', 100), 'new'
958	FROM generate_series(1, 50) a;
959BEGIN;
960SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
961UPDATE serializable_update_tab SET description = 'no no', id = 1 WHERE id = 1;
962COMMIT;
963SELECT description FROM serializable_update_tab WHERE id = 1;
964    description
965--------------------
966 updated in trigger
967(1 row)
968
969DROP TABLE serializable_update_tab;
970-- minimal update trigger
971CREATE TABLE min_updates_test (
972	f1	text,
973	f2 int,
974	f3 int);
975INSERT INTO min_updates_test VALUES ('a',1,2),('b','2',null);
976CREATE TRIGGER z_min_update
977BEFORE UPDATE ON min_updates_test
978FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
979\set QUIET false
980UPDATE min_updates_test SET f1 = f1;
981UPDATE 0
982UPDATE min_updates_test SET f2 = f2 + 1;
983UPDATE 2
984UPDATE min_updates_test SET f3 = 2 WHERE f3 is null;
985UPDATE 1
986\set QUIET true
987SELECT * FROM min_updates_test;
988 f1 | f2 | f3
989----+----+----
990 a  |  2 |  2
991 b  |  3 |  2
992(2 rows)
993
994DROP TABLE min_updates_test;
995--
996-- Test triggers on views
997--
998CREATE VIEW main_view AS SELECT a, b FROM main_table;
999-- VIEW trigger function
1000CREATE OR REPLACE FUNCTION view_trigger() RETURNS trigger
1001LANGUAGE plpgsql AS $$
1002declare
1003    argstr text := '';
1004begin
1005    for i in 0 .. TG_nargs - 1 loop
1006        if i > 0 then
1007            argstr := argstr || ', ';
1008        end if;
1009        argstr := argstr || TG_argv[i];
1010    end loop;
1011
1012    raise notice '% % % % (%)', TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL, argstr;
1013
1014    if TG_LEVEL = 'ROW' then
1015        if TG_OP = 'INSERT' then
1016            raise NOTICE 'NEW: %', NEW;
1017            INSERT INTO main_table VALUES (NEW.a, NEW.b);
1018            RETURN NEW;
1019        end if;
1020
1021        if TG_OP = 'UPDATE' then
1022            raise NOTICE 'OLD: %, NEW: %', OLD, NEW;
1023            UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b;
1024            if NOT FOUND then RETURN NULL; end if;
1025            RETURN NEW;
1026        end if;
1027
1028        if TG_OP = 'DELETE' then
1029            raise NOTICE 'OLD: %', OLD;
1030            DELETE FROM main_table WHERE a = OLD.a AND b = OLD.b;
1031            if NOT FOUND then RETURN NULL; end if;
1032            RETURN OLD;
1033        end if;
1034    end if;
1035
1036    RETURN NULL;
1037end;
1038$$;
1039-- Before row triggers aren't allowed on views
1040CREATE TRIGGER invalid_trig BEFORE INSERT ON main_view
1041FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row');
1042ERROR:  "main_view" is a view
1043DETAIL:  Views cannot have row-level BEFORE or AFTER triggers.
1044CREATE TRIGGER invalid_trig BEFORE UPDATE ON main_view
1045FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row');
1046ERROR:  "main_view" is a view
1047DETAIL:  Views cannot have row-level BEFORE or AFTER triggers.
1048CREATE TRIGGER invalid_trig BEFORE DELETE ON main_view
1049FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row');
1050ERROR:  "main_view" is a view
1051DETAIL:  Views cannot have row-level BEFORE or AFTER triggers.
1052-- After row triggers aren't allowed on views
1053CREATE TRIGGER invalid_trig AFTER INSERT ON main_view
1054FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row');
1055ERROR:  "main_view" is a view
1056DETAIL:  Views cannot have row-level BEFORE or AFTER triggers.
1057CREATE TRIGGER invalid_trig AFTER UPDATE ON main_view
1058FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row');
1059ERROR:  "main_view" is a view
1060DETAIL:  Views cannot have row-level BEFORE or AFTER triggers.
1061CREATE TRIGGER invalid_trig AFTER DELETE ON main_view
1062FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row');
1063ERROR:  "main_view" is a view
1064DETAIL:  Views cannot have row-level BEFORE or AFTER triggers.
1065-- Truncate triggers aren't allowed on views
1066CREATE TRIGGER invalid_trig BEFORE TRUNCATE ON main_view
1067EXECUTE PROCEDURE trigger_func('before_tru_row');
1068ERROR:  "main_view" is a view
1069DETAIL:  Views cannot have TRUNCATE triggers.
1070CREATE TRIGGER invalid_trig AFTER TRUNCATE ON main_view
1071EXECUTE PROCEDURE trigger_func('before_tru_row');
1072ERROR:  "main_view" is a view
1073DETAIL:  Views cannot have TRUNCATE triggers.
1074-- INSTEAD OF triggers aren't allowed on tables
1075CREATE TRIGGER invalid_trig INSTEAD OF INSERT ON main_table
1076FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins');
1077ERROR:  "main_table" is a table
1078DETAIL:  Tables cannot have INSTEAD OF triggers.
1079CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_table
1080FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
1081ERROR:  "main_table" is a table
1082DETAIL:  Tables cannot have INSTEAD OF triggers.
1083CREATE TRIGGER invalid_trig INSTEAD OF DELETE ON main_table
1084FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
1085ERROR:  "main_table" is a table
1086DETAIL:  Tables cannot have INSTEAD OF triggers.
1087-- Don't support WHEN clauses with INSTEAD OF triggers
1088CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view
1089FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE view_trigger('instead_of_upd');
1090ERROR:  INSTEAD OF triggers cannot have WHEN conditions
1091-- Don't support column-level INSTEAD OF triggers
1092CREATE TRIGGER invalid_trig INSTEAD OF UPDATE OF a ON main_view
1093FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
1094ERROR:  INSTEAD OF triggers cannot have column lists
1095-- Don't support statement-level INSTEAD OF triggers
1096CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view
1097EXECUTE PROCEDURE view_trigger('instead_of_upd');
1098ERROR:  INSTEAD OF triggers must be FOR EACH ROW
1099-- Valid INSTEAD OF triggers
1100CREATE TRIGGER instead_of_insert_trig INSTEAD OF INSERT ON main_view
1101FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins');
1102CREATE TRIGGER instead_of_update_trig INSTEAD OF UPDATE ON main_view
1103FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
1104CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view
1105FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
1106-- Valid BEFORE statement VIEW triggers
1107CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view
1108FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt');
1109CREATE TRIGGER before_upd_stmt_trig BEFORE UPDATE ON main_view
1110FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt');
1111CREATE TRIGGER before_del_stmt_trig BEFORE DELETE ON main_view
1112FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt');
1113-- Valid AFTER statement VIEW triggers
1114CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_view
1115FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt');
1116CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_view
1117FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt');
1118CREATE TRIGGER after_del_stmt_trig AFTER DELETE ON main_view
1119FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt');
1120\set QUIET false
1121-- Insert into view using trigger
1122INSERT INTO main_view VALUES (20, 30);
1123NOTICE:  main_view BEFORE INSERT STATEMENT (before_view_ins_stmt)
1124NOTICE:  main_view INSTEAD OF INSERT ROW (instead_of_ins)
1125NOTICE:  NEW: (20,30)
1126NOTICE:  trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
1127NOTICE:  trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
1128NOTICE:  main_view AFTER INSERT STATEMENT (after_view_ins_stmt)
1129INSERT 0 1
1130INSERT INTO main_view VALUES (21, 31) RETURNING a, b;
1131NOTICE:  main_view BEFORE INSERT STATEMENT (before_view_ins_stmt)
1132NOTICE:  main_view INSTEAD OF INSERT ROW (instead_of_ins)
1133NOTICE:  NEW: (21,31)
1134NOTICE:  trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
1135NOTICE:  trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
1136NOTICE:  main_view AFTER INSERT STATEMENT (after_view_ins_stmt)
1137 a  | b
1138----+----
1139 21 | 31
1140(1 row)
1141
1142INSERT 0 1
1143-- Table trigger will prevent updates
1144UPDATE main_view SET b = 31 WHERE a = 20;
1145NOTICE:  main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt)
1146NOTICE:  main_view INSTEAD OF UPDATE ROW (instead_of_upd)
1147NOTICE:  OLD: (20,30), NEW: (20,31)
1148NOTICE:  trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
1149NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
1150NOTICE:  trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
1151NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
1152NOTICE:  main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
1153UPDATE 0
1154UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b;
1155NOTICE:  main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt)
1156NOTICE:  main_view INSTEAD OF UPDATE ROW (instead_of_upd)
1157NOTICE:  OLD: (21,31), NEW: (21,32)
1158NOTICE:  trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
1159NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
1160NOTICE:  trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
1161NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
1162NOTICE:  main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
1163 a | b
1164---+---
1165(0 rows)
1166
1167UPDATE 0
1168-- Remove table trigger to allow updates
1169DROP TRIGGER before_upd_a_row_trig ON main_table;
1170DROP TRIGGER
1171UPDATE main_view SET b = 31 WHERE a = 20;
1172NOTICE:  main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt)
1173NOTICE:  main_view INSTEAD OF UPDATE ROW (instead_of_upd)
1174NOTICE:  OLD: (20,30), NEW: (20,31)
1175NOTICE:  trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
1176NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
1177NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
1178NOTICE:  trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
1179NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
1180NOTICE:  main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
1181UPDATE 1
1182UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b;
1183NOTICE:  main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt)
1184NOTICE:  main_view INSTEAD OF UPDATE ROW (instead_of_upd)
1185NOTICE:  OLD: (21,31), NEW: (21,32)
1186NOTICE:  trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
1187NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
1188NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
1189NOTICE:  trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
1190NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
1191NOTICE:  main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
1192 a  | b
1193----+----
1194 21 | 32
1195(1 row)
1196
1197UPDATE 1
1198-- Before and after stmt triggers should fire even when no rows are affected
1199UPDATE main_view SET b = 0 WHERE false;
1200NOTICE:  main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt)
1201NOTICE:  main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
1202UPDATE 0
1203-- Delete from view using trigger
1204DELETE FROM main_view WHERE a IN (20,21);
1205NOTICE:  main_view BEFORE DELETE STATEMENT (before_view_del_stmt)
1206NOTICE:  main_view INSTEAD OF DELETE ROW (instead_of_del)
1207NOTICE:  OLD: (21,10)
1208NOTICE:  main_view INSTEAD OF DELETE ROW (instead_of_del)
1209NOTICE:  OLD: (20,31)
1210NOTICE:  main_view INSTEAD OF DELETE ROW (instead_of_del)
1211NOTICE:  OLD: (21,32)
1212NOTICE:  main_view AFTER DELETE STATEMENT (after_view_del_stmt)
1213DELETE 3
1214DELETE FROM main_view WHERE a = 31 RETURNING a, b;
1215NOTICE:  main_view BEFORE DELETE STATEMENT (before_view_del_stmt)
1216NOTICE:  main_view INSTEAD OF DELETE ROW (instead_of_del)
1217NOTICE:  OLD: (31,10)
1218NOTICE:  main_view AFTER DELETE STATEMENT (after_view_del_stmt)
1219 a  | b
1220----+----
1221 31 | 10
1222(1 row)
1223
1224DELETE 1
1225\set QUIET true
1226-- Describe view should list triggers
1227\d main_view
1228              View "public.main_view"
1229 Column |  Type   | Collation | Nullable | Default
1230--------+---------+-----------+----------+---------
1231 a      | integer |           |          |
1232 b      | integer |           |          |
1233Triggers:
1234    after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('after_view_del_stmt')
1235    after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('after_view_ins_stmt')
1236    after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('after_view_upd_stmt')
1237    before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_del_stmt')
1238    before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_ins_stmt')
1239    before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_upd_stmt')
1240    instead_of_delete_trig INSTEAD OF DELETE ON main_view FOR EACH ROW EXECUTE FUNCTION view_trigger('instead_of_del')
1241    instead_of_insert_trig INSTEAD OF INSERT ON main_view FOR EACH ROW EXECUTE FUNCTION view_trigger('instead_of_ins')
1242    instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE FUNCTION view_trigger('instead_of_upd')
1243
1244-- Test dropping view triggers
1245DROP TRIGGER instead_of_insert_trig ON main_view;
1246DROP TRIGGER instead_of_delete_trig ON main_view;
1247\d+ main_view
1248                          View "public.main_view"
1249 Column |  Type   | Collation | Nullable | Default | Storage | Description
1250--------+---------+-----------+----------+---------+---------+-------------
1251 a      | integer |           |          |         | plain   |
1252 b      | integer |           |          |         | plain   |
1253View definition:
1254 SELECT main_table.a,
1255    main_table.b
1256   FROM main_table;
1257Triggers:
1258    after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('after_view_del_stmt')
1259    after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('after_view_ins_stmt')
1260    after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('after_view_upd_stmt')
1261    before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_del_stmt')
1262    before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_ins_stmt')
1263    before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_upd_stmt')
1264    instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE FUNCTION view_trigger('instead_of_upd')
1265
1266DROP VIEW main_view;
1267--
1268-- Test triggers on a join view
1269--
1270CREATE TABLE country_table (
1271    country_id        serial primary key,
1272    country_name    text unique not null,
1273    continent        text not null
1274);
1275INSERT INTO country_table (country_name, continent)
1276    VALUES ('Japan', 'Asia'),
1277           ('UK', 'Europe'),
1278           ('USA', 'North America')
1279    RETURNING *;
1280 country_id | country_name |   continent
1281------------+--------------+---------------
1282          1 | Japan        | Asia
1283          2 | UK           | Europe
1284          3 | USA          | North America
1285(3 rows)
1286
1287CREATE TABLE city_table (
1288    city_id        serial primary key,
1289    city_name    text not null,
1290    population    bigint,
1291    country_id    int references country_table
1292);
1293CREATE VIEW city_view AS
1294    SELECT city_id, city_name, population, country_name, continent
1295    FROM city_table ci
1296    LEFT JOIN country_table co ON co.country_id = ci.country_id;
1297CREATE FUNCTION city_insert() RETURNS trigger LANGUAGE plpgsql AS $$
1298declare
1299    ctry_id int;
1300begin
1301    if NEW.country_name IS NOT NULL then
1302        SELECT country_id, continent INTO ctry_id, NEW.continent
1303            FROM country_table WHERE country_name = NEW.country_name;
1304        if NOT FOUND then
1305            raise exception 'No such country: "%"', NEW.country_name;
1306        end if;
1307    else
1308        NEW.continent := NULL;
1309    end if;
1310
1311    if NEW.city_id IS NOT NULL then
1312        INSERT INTO city_table
1313            VALUES(NEW.city_id, NEW.city_name, NEW.population, ctry_id);
1314    else
1315        INSERT INTO city_table(city_name, population, country_id)
1316            VALUES(NEW.city_name, NEW.population, ctry_id)
1317            RETURNING city_id INTO NEW.city_id;
1318    end if;
1319
1320    RETURN NEW;
1321end;
1322$$;
1323CREATE TRIGGER city_insert_trig INSTEAD OF INSERT ON city_view
1324FOR EACH ROW EXECUTE PROCEDURE city_insert();
1325CREATE FUNCTION city_delete() RETURNS trigger LANGUAGE plpgsql AS $$
1326begin
1327    DELETE FROM city_table WHERE city_id = OLD.city_id;
1328    if NOT FOUND then RETURN NULL; end if;
1329    RETURN OLD;
1330end;
1331$$;
1332CREATE TRIGGER city_delete_trig INSTEAD OF DELETE ON city_view
1333FOR EACH ROW EXECUTE PROCEDURE city_delete();
1334CREATE FUNCTION city_update() RETURNS trigger LANGUAGE plpgsql AS $$
1335declare
1336    ctry_id int;
1337begin
1338    if NEW.country_name IS DISTINCT FROM OLD.country_name then
1339        SELECT country_id, continent INTO ctry_id, NEW.continent
1340            FROM country_table WHERE country_name = NEW.country_name;
1341        if NOT FOUND then
1342            raise exception 'No such country: "%"', NEW.country_name;
1343        end if;
1344
1345        UPDATE city_table SET city_name = NEW.city_name,
1346                              population = NEW.population,
1347                              country_id = ctry_id
1348            WHERE city_id = OLD.city_id;
1349    else
1350        UPDATE city_table SET city_name = NEW.city_name,
1351                              population = NEW.population
1352            WHERE city_id = OLD.city_id;
1353        NEW.continent := OLD.continent;
1354    end if;
1355
1356    if NOT FOUND then RETURN NULL; end if;
1357    RETURN NEW;
1358end;
1359$$;
1360CREATE TRIGGER city_update_trig INSTEAD OF UPDATE ON city_view
1361FOR EACH ROW EXECUTE PROCEDURE city_update();
1362\set QUIET false
1363-- INSERT .. RETURNING
1364INSERT INTO city_view(city_name) VALUES('Tokyo') RETURNING *;
1365 city_id | city_name | population | country_name | continent
1366---------+-----------+------------+--------------+-----------
1367       1 | Tokyo     |            |              |
1368(1 row)
1369
1370INSERT 0 1
1371INSERT INTO city_view(city_name, population) VALUES('London', 7556900) RETURNING *;
1372 city_id | city_name | population | country_name | continent
1373---------+-----------+------------+--------------+-----------
1374       2 | London    |    7556900 |              |
1375(1 row)
1376
1377INSERT 0 1
1378INSERT INTO city_view(city_name, country_name) VALUES('Washington DC', 'USA') RETURNING *;
1379 city_id |   city_name   | population | country_name |   continent
1380---------+---------------+------------+--------------+---------------
1381       3 | Washington DC |            | USA          | North America
1382(1 row)
1383
1384INSERT 0 1
1385INSERT INTO city_view(city_id, city_name) VALUES(123456, 'New York') RETURNING *;
1386 city_id | city_name | population | country_name | continent
1387---------+-----------+------------+--------------+-----------
1388  123456 | New York  |            |              |
1389(1 row)
1390
1391INSERT 0 1
1392INSERT INTO city_view VALUES(234567, 'Birmingham', 1016800, 'UK', 'EU') RETURNING *;
1393 city_id | city_name  | population | country_name | continent
1394---------+------------+------------+--------------+-----------
1395  234567 | Birmingham |    1016800 | UK           | Europe
1396(1 row)
1397
1398INSERT 0 1
1399-- UPDATE .. RETURNING
1400UPDATE city_view SET country_name = 'Japon' WHERE city_name = 'Tokyo'; -- error
1401ERROR:  No such country: "Japon"
1402CONTEXT:  PL/pgSQL function city_update() line 9 at RAISE
1403UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Takyo'; -- no match
1404UPDATE 0
1405UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Tokyo' RETURNING *; -- OK
1406 city_id | city_name | population | country_name | continent
1407---------+-----------+------------+--------------+-----------
1408       1 | Tokyo     |            | Japan        | Asia
1409(1 row)
1410
1411UPDATE 1
1412UPDATE city_view SET population = 13010279 WHERE city_name = 'Tokyo' RETURNING *;
1413 city_id | city_name | population | country_name | continent
1414---------+-----------+------------+--------------+-----------
1415       1 | Tokyo     |   13010279 | Japan        | Asia
1416(1 row)
1417
1418UPDATE 1
1419UPDATE city_view SET country_name = 'UK' WHERE city_name = 'New York' RETURNING *;
1420 city_id | city_name | population | country_name | continent
1421---------+-----------+------------+--------------+-----------
1422  123456 | New York  |            | UK           | Europe
1423(1 row)
1424
1425UPDATE 1
1426UPDATE city_view SET country_name = 'USA', population = 8391881 WHERE city_name = 'New York' RETURNING *;
1427 city_id | city_name | population | country_name |   continent
1428---------+-----------+------------+--------------+---------------
1429  123456 | New York  |    8391881 | USA          | North America
1430(1 row)
1431
1432UPDATE 1
1433UPDATE city_view SET continent = 'EU' WHERE continent = 'Europe' RETURNING *;
1434 city_id | city_name  | population | country_name | continent
1435---------+------------+------------+--------------+-----------
1436  234567 | Birmingham |    1016800 | UK           | Europe
1437(1 row)
1438
1439UPDATE 1
1440UPDATE city_view v1 SET country_name = v2.country_name FROM city_view v2
1441    WHERE v2.city_name = 'Birmingham' AND v1.city_name = 'London' RETURNING *;
1442 city_id | city_name | population | country_name | continent | city_id | city_name  | population | country_name | continent
1443---------+-----------+------------+--------------+-----------+---------+------------+------------+--------------+-----------
1444       2 | London    |    7556900 | UK           | Europe    |  234567 | Birmingham |    1016800 | UK           | Europe
1445(1 row)
1446
1447UPDATE 1
1448-- DELETE .. RETURNING
1449DELETE FROM city_view WHERE city_name = 'Birmingham' RETURNING *;
1450 city_id | city_name  | population | country_name | continent
1451---------+------------+------------+--------------+-----------
1452  234567 | Birmingham |    1016800 | UK           | Europe
1453(1 row)
1454
1455DELETE 1
1456\set QUIET true
1457-- read-only view with WHERE clause
1458CREATE VIEW european_city_view AS
1459    SELECT * FROM city_view WHERE continent = 'Europe';
1460SELECT count(*) FROM european_city_view;
1461 count
1462-------
1463     1
1464(1 row)
1465
1466CREATE FUNCTION no_op_trig_fn() RETURNS trigger LANGUAGE plpgsql
1467AS 'begin RETURN NULL; end';
1468CREATE TRIGGER no_op_trig INSTEAD OF INSERT OR UPDATE OR DELETE
1469ON european_city_view FOR EACH ROW EXECUTE PROCEDURE no_op_trig_fn();
1470\set QUIET false
1471INSERT INTO european_city_view VALUES (0, 'x', 10000, 'y', 'z');
1472INSERT 0 0
1473UPDATE european_city_view SET population = 10000;
1474UPDATE 0
1475DELETE FROM european_city_view;
1476DELETE 0
1477\set QUIET true
1478-- rules bypassing no-op triggers
1479CREATE RULE european_city_insert_rule AS ON INSERT TO european_city_view
1480DO INSTEAD INSERT INTO city_view
1481VALUES (NEW.city_id, NEW.city_name, NEW.population, NEW.country_name, NEW.continent)
1482RETURNING *;
1483CREATE RULE european_city_update_rule AS ON UPDATE TO european_city_view
1484DO INSTEAD UPDATE city_view SET
1485    city_name = NEW.city_name,
1486    population = NEW.population,
1487    country_name = NEW.country_name
1488WHERE city_id = OLD.city_id
1489RETURNING NEW.*;
1490CREATE RULE european_city_delete_rule AS ON DELETE TO european_city_view
1491DO INSTEAD DELETE FROM city_view WHERE city_id = OLD.city_id RETURNING *;
1492\set QUIET false
1493-- INSERT not limited by view's WHERE clause, but UPDATE AND DELETE are
1494INSERT INTO european_city_view(city_name, country_name)
1495    VALUES ('Cambridge', 'USA') RETURNING *;
1496 city_id | city_name | population | country_name |   continent
1497---------+-----------+------------+--------------+---------------
1498       4 | Cambridge |            | USA          | North America
1499(1 row)
1500
1501INSERT 0 1
1502UPDATE european_city_view SET country_name = 'UK'
1503    WHERE city_name = 'Cambridge';
1504UPDATE 0
1505DELETE FROM european_city_view WHERE city_name = 'Cambridge';
1506DELETE 0
1507-- UPDATE and DELETE via rule and trigger
1508UPDATE city_view SET country_name = 'UK'
1509    WHERE city_name = 'Cambridge' RETURNING *;
1510 city_id | city_name | population | country_name | continent
1511---------+-----------+------------+--------------+-----------
1512       4 | Cambridge |            | UK           | Europe
1513(1 row)
1514
1515UPDATE 1
1516UPDATE european_city_view SET population = 122800
1517    WHERE city_name = 'Cambridge' RETURNING *;
1518 city_id | city_name | population | country_name | continent
1519---------+-----------+------------+--------------+-----------
1520       4 | Cambridge |     122800 | UK           | Europe
1521(1 row)
1522
1523UPDATE 1
1524DELETE FROM european_city_view WHERE city_name = 'Cambridge' RETURNING *;
1525 city_id | city_name | population | country_name | continent
1526---------+-----------+------------+--------------+-----------
1527       4 | Cambridge |     122800 | UK           | Europe
1528(1 row)
1529
1530DELETE 1
1531-- join UPDATE test
1532UPDATE city_view v SET population = 599657
1533    FROM city_table ci, country_table co
1534    WHERE ci.city_name = 'Washington DC' and co.country_name = 'USA'
1535    AND v.city_id = ci.city_id AND v.country_name = co.country_name
1536    RETURNING co.country_id, v.country_name,
1537              v.city_id, v.city_name, v.population;
1538 country_id | country_name | city_id |   city_name   | population
1539------------+--------------+---------+---------------+------------
1540          3 | USA          |       3 | Washington DC |     599657
1541(1 row)
1542
1543UPDATE 1
1544\set QUIET true
1545SELECT * FROM city_view;
1546 city_id |   city_name   | population | country_name |   continent
1547---------+---------------+------------+--------------+---------------
1548       1 | Tokyo         |   13010279 | Japan        | Asia
1549  123456 | New York      |    8391881 | USA          | North America
1550       2 | London        |    7556900 | UK           | Europe
1551       3 | Washington DC |     599657 | USA          | North America
1552(4 rows)
1553
1554DROP TABLE city_table CASCADE;
1555NOTICE:  drop cascades to 2 other objects
1556DETAIL:  drop cascades to view city_view
1557drop cascades to view european_city_view
1558DROP TABLE country_table;
1559-- Test pg_trigger_depth()
1560create table depth_a (id int not null primary key);
1561create table depth_b (id int not null primary key);
1562create table depth_c (id int not null primary key);
1563create function depth_a_tf() returns trigger
1564  language plpgsql as $$
1565begin
1566  raise notice '%: depth = %', tg_name, pg_trigger_depth();
1567  insert into depth_b values (new.id);
1568  raise notice '%: depth = %', tg_name, pg_trigger_depth();
1569  return new;
1570end;
1571$$;
1572create trigger depth_a_tr before insert on depth_a
1573  for each row execute procedure depth_a_tf();
1574create function depth_b_tf() returns trigger
1575  language plpgsql as $$
1576begin
1577  raise notice '%: depth = %', tg_name, pg_trigger_depth();
1578  begin
1579    execute 'insert into depth_c values (' || new.id::text || ')';
1580  exception
1581    when sqlstate 'U9999' then
1582      raise notice 'SQLSTATE = U9999: depth = %', pg_trigger_depth();
1583  end;
1584  raise notice '%: depth = %', tg_name, pg_trigger_depth();
1585  if new.id = 1 then
1586    execute 'insert into depth_c values (' || new.id::text || ')';
1587  end if;
1588  return new;
1589end;
1590$$;
1591create trigger depth_b_tr before insert on depth_b
1592  for each row execute procedure depth_b_tf();
1593create function depth_c_tf() returns trigger
1594  language plpgsql as $$
1595begin
1596  raise notice '%: depth = %', tg_name, pg_trigger_depth();
1597  if new.id = 1 then
1598    raise exception sqlstate 'U9999';
1599  end if;
1600  raise notice '%: depth = %', tg_name, pg_trigger_depth();
1601  return new;
1602end;
1603$$;
1604create trigger depth_c_tr before insert on depth_c
1605  for each row execute procedure depth_c_tf();
1606select pg_trigger_depth();
1607 pg_trigger_depth
1608------------------
1609                0
1610(1 row)
1611
1612insert into depth_a values (1);
1613NOTICE:  depth_a_tr: depth = 1
1614NOTICE:  depth_b_tr: depth = 2
1615NOTICE:  depth_c_tr: depth = 3
1616NOTICE:  SQLSTATE = U9999: depth = 2
1617NOTICE:  depth_b_tr: depth = 2
1618NOTICE:  depth_c_tr: depth = 3
1619ERROR:  U9999
1620CONTEXT:  PL/pgSQL function depth_c_tf() line 5 at RAISE
1621SQL statement "insert into depth_c values (1)"
1622PL/pgSQL function depth_b_tf() line 12 at EXECUTE
1623SQL statement "insert into depth_b values (new.id)"
1624PL/pgSQL function depth_a_tf() line 4 at SQL statement
1625select pg_trigger_depth();
1626 pg_trigger_depth
1627------------------
1628                0
1629(1 row)
1630
1631insert into depth_a values (2);
1632NOTICE:  depth_a_tr: depth = 1
1633NOTICE:  depth_b_tr: depth = 2
1634NOTICE:  depth_c_tr: depth = 3
1635NOTICE:  depth_c_tr: depth = 3
1636NOTICE:  depth_b_tr: depth = 2
1637NOTICE:  depth_a_tr: depth = 1
1638select pg_trigger_depth();
1639 pg_trigger_depth
1640------------------
1641                0
1642(1 row)
1643
1644drop table depth_a, depth_b, depth_c;
1645drop function depth_a_tf();
1646drop function depth_b_tf();
1647drop function depth_c_tf();
1648--
1649-- Test updates to rows during firing of BEFORE ROW triggers.
1650-- As of 9.2, such cases should be rejected (see bug #6123).
1651--
1652create temp table parent (
1653    aid int not null primary key,
1654    val1 text,
1655    val2 text,
1656    val3 text,
1657    val4 text,
1658    bcnt int not null default 0);
1659create temp table child (
1660    bid int not null primary key,
1661    aid int not null,
1662    val1 text);
1663create function parent_upd_func()
1664  returns trigger language plpgsql as
1665$$
1666begin
1667  if old.val1 <> new.val1 then
1668    new.val2 = new.val1;
1669    delete from child where child.aid = new.aid and child.val1 = new.val1;
1670  end if;
1671  return new;
1672end;
1673$$;
1674create trigger parent_upd_trig before update on parent
1675  for each row execute procedure parent_upd_func();
1676create function parent_del_func()
1677  returns trigger language plpgsql as
1678$$
1679begin
1680  delete from child where aid = old.aid;
1681  return old;
1682end;
1683$$;
1684create trigger parent_del_trig before delete on parent
1685  for each row execute procedure parent_del_func();
1686create function child_ins_func()
1687  returns trigger language plpgsql as
1688$$
1689begin
1690  update parent set bcnt = bcnt + 1 where aid = new.aid;
1691  return new;
1692end;
1693$$;
1694create trigger child_ins_trig after insert on child
1695  for each row execute procedure child_ins_func();
1696create function child_del_func()
1697  returns trigger language plpgsql as
1698$$
1699begin
1700  update parent set bcnt = bcnt - 1 where aid = old.aid;
1701  return old;
1702end;
1703$$;
1704create trigger child_del_trig after delete on child
1705  for each row execute procedure child_del_func();
1706insert into parent values (1, 'a', 'a', 'a', 'a', 0);
1707insert into child values (10, 1, 'b');
1708select * from parent; select * from child;
1709 aid | val1 | val2 | val3 | val4 | bcnt
1710-----+------+------+------+------+------
1711   1 | a    | a    | a    | a    |    1
1712(1 row)
1713
1714 bid | aid | val1
1715-----+-----+------
1716  10 |   1 | b
1717(1 row)
1718
1719update parent set val1 = 'b' where aid = 1; -- should fail
1720ERROR:  tuple to be updated was already modified by an operation triggered by the current command
1721HINT:  Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows.
1722select * from parent; select * from child;
1723 aid | val1 | val2 | val3 | val4 | bcnt
1724-----+------+------+------+------+------
1725   1 | a    | a    | a    | a    |    1
1726(1 row)
1727
1728 bid | aid | val1
1729-----+-----+------
1730  10 |   1 | b
1731(1 row)
1732
1733delete from parent where aid = 1; -- should fail
1734ERROR:  tuple to be deleted was already modified by an operation triggered by the current command
1735HINT:  Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows.
1736select * from parent; select * from child;
1737 aid | val1 | val2 | val3 | val4 | bcnt
1738-----+------+------+------+------+------
1739   1 | a    | a    | a    | a    |    1
1740(1 row)
1741
1742 bid | aid | val1
1743-----+-----+------
1744  10 |   1 | b
1745(1 row)
1746
1747-- replace the trigger function with one that restarts the deletion after
1748-- having modified a child
1749create or replace function parent_del_func()
1750  returns trigger language plpgsql as
1751$$
1752begin
1753  delete from child where aid = old.aid;
1754  if found then
1755    delete from parent where aid = old.aid;
1756    return null; -- cancel outer deletion
1757  end if;
1758  return old;
1759end;
1760$$;
1761delete from parent where aid = 1;
1762select * from parent; select * from child;
1763 aid | val1 | val2 | val3 | val4 | bcnt
1764-----+------+------+------+------+------
1765(0 rows)
1766
1767 bid | aid | val1
1768-----+-----+------
1769(0 rows)
1770
1771drop table parent, child;
1772drop function parent_upd_func();
1773drop function parent_del_func();
1774drop function child_ins_func();
1775drop function child_del_func();
1776-- similar case, but with a self-referencing FK so that parent and child
1777-- rows can be affected by a single operation
1778create temp table self_ref_trigger (
1779    id int primary key,
1780    parent int references self_ref_trigger,
1781    data text,
1782    nchildren int not null default 0
1783);
1784create function self_ref_trigger_ins_func()
1785  returns trigger language plpgsql as
1786$$
1787begin
1788  if new.parent is not null then
1789    update self_ref_trigger set nchildren = nchildren + 1
1790      where id = new.parent;
1791  end if;
1792  return new;
1793end;
1794$$;
1795create trigger self_ref_trigger_ins_trig before insert on self_ref_trigger
1796  for each row execute procedure self_ref_trigger_ins_func();
1797create function self_ref_trigger_del_func()
1798  returns trigger language plpgsql as
1799$$
1800begin
1801  if old.parent is not null then
1802    update self_ref_trigger set nchildren = nchildren - 1
1803      where id = old.parent;
1804  end if;
1805  return old;
1806end;
1807$$;
1808create trigger self_ref_trigger_del_trig before delete on self_ref_trigger
1809  for each row execute procedure self_ref_trigger_del_func();
1810insert into self_ref_trigger values (1, null, 'root');
1811insert into self_ref_trigger values (2, 1, 'root child A');
1812insert into self_ref_trigger values (3, 1, 'root child B');
1813insert into self_ref_trigger values (4, 2, 'grandchild 1');
1814insert into self_ref_trigger values (5, 3, 'grandchild 2');
1815update self_ref_trigger set data = 'root!' where id = 1;
1816select * from self_ref_trigger;
1817 id | parent |     data     | nchildren
1818----+--------+--------------+-----------
1819  2 |      1 | root child A |         1
1820  4 |      2 | grandchild 1 |         0
1821  3 |      1 | root child B |         1
1822  5 |      3 | grandchild 2 |         0
1823  1 |        | root!        |         2
1824(5 rows)
1825
1826delete from self_ref_trigger;
1827ERROR:  tuple to be updated was already modified by an operation triggered by the current command
1828HINT:  Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows.
1829select * from self_ref_trigger;
1830 id | parent |     data     | nchildren
1831----+--------+--------------+-----------
1832  2 |      1 | root child A |         1
1833  4 |      2 | grandchild 1 |         0
1834  3 |      1 | root child B |         1
1835  5 |      3 | grandchild 2 |         0
1836  1 |        | root!        |         2
1837(5 rows)
1838
1839drop table self_ref_trigger;
1840drop function self_ref_trigger_ins_func();
1841drop function self_ref_trigger_del_func();
1842--
1843-- Check that statement triggers work correctly even with all children excluded
1844--
1845create table stmt_trig_on_empty_upd (a int);
1846create table stmt_trig_on_empty_upd1 () inherits (stmt_trig_on_empty_upd);
1847create function update_stmt_notice() returns trigger as $$
1848begin
1849	raise notice 'updating %', TG_TABLE_NAME;
1850	return null;
1851end;
1852$$ language plpgsql;
1853create trigger before_stmt_trigger
1854	before update on stmt_trig_on_empty_upd
1855	execute procedure update_stmt_notice();
1856create trigger before_stmt_trigger
1857	before update on stmt_trig_on_empty_upd1
1858	execute procedure update_stmt_notice();
1859-- inherited no-op update
1860update stmt_trig_on_empty_upd set a = a where false returning a+1 as aa;
1861NOTICE:  updating stmt_trig_on_empty_upd
1862 aa
1863----
1864(0 rows)
1865
1866-- simple no-op update
1867update stmt_trig_on_empty_upd1 set a = a where false returning a+1 as aa;
1868NOTICE:  updating stmt_trig_on_empty_upd1
1869 aa
1870----
1871(0 rows)
1872
1873drop table stmt_trig_on_empty_upd cascade;
1874NOTICE:  drop cascades to table stmt_trig_on_empty_upd1
1875drop function update_stmt_notice();
1876--
1877-- Check that index creation (or DDL in general) is prohibited in a trigger
1878--
1879create table trigger_ddl_table (
1880   col1 integer,
1881   col2 integer
1882);
1883create function trigger_ddl_func() returns trigger as $$
1884begin
1885  alter table trigger_ddl_table add primary key (col1);
1886  return new;
1887end$$ language plpgsql;
1888create trigger trigger_ddl_func before insert on trigger_ddl_table for each row
1889  execute procedure trigger_ddl_func();
1890insert into trigger_ddl_table values (1, 42);  -- fail
1891ERROR:  cannot ALTER TABLE "trigger_ddl_table" because it is being used by active queries in this session
1892CONTEXT:  SQL statement "alter table trigger_ddl_table add primary key (col1)"
1893PL/pgSQL function trigger_ddl_func() line 3 at SQL statement
1894create or replace function trigger_ddl_func() returns trigger as $$
1895begin
1896  create index on trigger_ddl_table (col2);
1897  return new;
1898end$$ language plpgsql;
1899insert into trigger_ddl_table values (1, 42);  -- fail
1900ERROR:  cannot CREATE INDEX "trigger_ddl_table" because it is being used by active queries in this session
1901CONTEXT:  SQL statement "create index on trigger_ddl_table (col2)"
1902PL/pgSQL function trigger_ddl_func() line 3 at SQL statement
1903drop table trigger_ddl_table;
1904drop function trigger_ddl_func();
1905--
1906-- Verify behavior of before and after triggers with INSERT...ON CONFLICT
1907-- DO UPDATE
1908--
1909create table upsert (key int4 primary key, color text);
1910create function upsert_before_func()
1911  returns trigger language plpgsql as
1912$$
1913begin
1914  if (TG_OP = 'UPDATE') then
1915    raise warning 'before update (old): %', old.*::text;
1916    raise warning 'before update (new): %', new.*::text;
1917  elsif (TG_OP = 'INSERT') then
1918    raise warning 'before insert (new): %', new.*::text;
1919    if new.key % 2 = 0 then
1920      new.key := new.key + 1;
1921      new.color := new.color || ' trig modified';
1922      raise warning 'before insert (new, modified): %', new.*::text;
1923    end if;
1924  end if;
1925  return new;
1926end;
1927$$;
1928create trigger upsert_before_trig before insert or update on upsert
1929  for each row execute procedure upsert_before_func();
1930create function upsert_after_func()
1931  returns trigger language plpgsql as
1932$$
1933begin
1934  if (TG_OP = 'UPDATE') then
1935    raise warning 'after update (old): %', old.*::text;
1936    raise warning 'after update (new): %', new.*::text;
1937  elsif (TG_OP = 'INSERT') then
1938    raise warning 'after insert (new): %', new.*::text;
1939  end if;
1940  return null;
1941end;
1942$$;
1943create trigger upsert_after_trig after insert or update on upsert
1944  for each row execute procedure upsert_after_func();
1945insert into upsert values(1, 'black') on conflict (key) do update set color = 'updated ' || upsert.color;
1946WARNING:  before insert (new): (1,black)
1947WARNING:  after insert (new): (1,black)
1948insert into upsert values(2, 'red') on conflict (key) do update set color = 'updated ' || upsert.color;
1949WARNING:  before insert (new): (2,red)
1950WARNING:  before insert (new, modified): (3,"red trig modified")
1951WARNING:  after insert (new): (3,"red trig modified")
1952insert into upsert values(3, 'orange') on conflict (key) do update set color = 'updated ' || upsert.color;
1953WARNING:  before insert (new): (3,orange)
1954WARNING:  before update (old): (3,"red trig modified")
1955WARNING:  before update (new): (3,"updated red trig modified")
1956WARNING:  after update (old): (3,"red trig modified")
1957WARNING:  after update (new): (3,"updated red trig modified")
1958insert into upsert values(4, 'green') on conflict (key) do update set color = 'updated ' || upsert.color;
1959WARNING:  before insert (new): (4,green)
1960WARNING:  before insert (new, modified): (5,"green trig modified")
1961WARNING:  after insert (new): (5,"green trig modified")
1962insert into upsert values(5, 'purple') on conflict (key) do update set color = 'updated ' || upsert.color;
1963WARNING:  before insert (new): (5,purple)
1964WARNING:  before update (old): (5,"green trig modified")
1965WARNING:  before update (new): (5,"updated green trig modified")
1966WARNING:  after update (old): (5,"green trig modified")
1967WARNING:  after update (new): (5,"updated green trig modified")
1968insert into upsert values(6, 'white') on conflict (key) do update set color = 'updated ' || upsert.color;
1969WARNING:  before insert (new): (6,white)
1970WARNING:  before insert (new, modified): (7,"white trig modified")
1971WARNING:  after insert (new): (7,"white trig modified")
1972insert into upsert values(7, 'pink') on conflict (key) do update set color = 'updated ' || upsert.color;
1973WARNING:  before insert (new): (7,pink)
1974WARNING:  before update (old): (7,"white trig modified")
1975WARNING:  before update (new): (7,"updated white trig modified")
1976WARNING:  after update (old): (7,"white trig modified")
1977WARNING:  after update (new): (7,"updated white trig modified")
1978insert into upsert values(8, 'yellow') on conflict (key) do update set color = 'updated ' || upsert.color;
1979WARNING:  before insert (new): (8,yellow)
1980WARNING:  before insert (new, modified): (9,"yellow trig modified")
1981WARNING:  after insert (new): (9,"yellow trig modified")
1982select * from upsert;
1983 key |            color
1984-----+-----------------------------
1985   1 | black
1986   3 | updated red trig modified
1987   5 | updated green trig modified
1988   7 | updated white trig modified
1989   9 | yellow trig modified
1990(5 rows)
1991
1992drop table upsert;
1993drop function upsert_before_func();
1994drop function upsert_after_func();
1995--
1996-- Verify that triggers with transition tables are not allowed on
1997-- views
1998--
1999create table my_table (i int);
2000create view my_view as select * from my_table;
2001create function my_trigger_function() returns trigger as $$ begin end; $$ language plpgsql;
2002create trigger my_trigger after update on my_view referencing old table as old_table
2003   for each statement execute procedure my_trigger_function();
2004ERROR:  "my_view" is a view
2005DETAIL:  Triggers on views cannot have transition tables.
2006drop function my_trigger_function();
2007drop view my_view;
2008drop table my_table;
2009--
2010-- Verify cases that are unsupported with partitioned tables
2011--
2012create table parted_trig (a int) partition by list (a);
2013create function trigger_nothing() returns trigger
2014  language plpgsql as $$ begin end; $$;
2015create trigger failed instead of update on parted_trig
2016  for each row execute procedure trigger_nothing();
2017ERROR:  "parted_trig" is a table
2018DETAIL:  Tables cannot have INSTEAD OF triggers.
2019create trigger failed after update on parted_trig
2020  referencing old table as old_table
2021  for each row execute procedure trigger_nothing();
2022ERROR:  "parted_trig" is a partitioned table
2023DETAIL:  Triggers on partitioned tables cannot have transition tables.
2024drop table parted_trig;
2025--
2026-- Verify trigger creation for partitioned tables, and drop behavior
2027--
2028create table trigpart (a int, b int) partition by range (a);
2029create table trigpart1 partition of trigpart for values from (0) to (1000);
2030create trigger trg1 after insert on trigpart for each row execute procedure trigger_nothing();
2031create table trigpart2 partition of trigpart for values from (1000) to (2000);
2032create table trigpart3 (like trigpart);
2033alter table trigpart attach partition trigpart3 for values from (2000) to (3000);
2034create table trigpart4 partition of trigpart for values from (3000) to (4000) partition by range (a);
2035create table trigpart41 partition of trigpart4 for values from (3000) to (3500);
2036create table trigpart42 (like trigpart);
2037alter table trigpart4 attach partition trigpart42 for values from (3500) to (4000);
2038select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger
2039  where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;
2040  tgrelid   | tgname |     tgfoid
2041------------+--------+-----------------
2042 trigpart   | trg1   | trigger_nothing
2043 trigpart1  | trg1   | trigger_nothing
2044 trigpart2  | trg1   | trigger_nothing
2045 trigpart3  | trg1   | trigger_nothing
2046 trigpart4  | trg1   | trigger_nothing
2047 trigpart41 | trg1   | trigger_nothing
2048 trigpart42 | trg1   | trigger_nothing
2049(7 rows)
2050
2051drop trigger trg1 on trigpart1;	-- fail
2052ERROR:  cannot drop trigger trg1 on table trigpart1 because trigger trg1 on table trigpart requires it
2053HINT:  You can drop trigger trg1 on table trigpart instead.
2054drop trigger trg1 on trigpart2;	-- fail
2055ERROR:  cannot drop trigger trg1 on table trigpart2 because trigger trg1 on table trigpart requires it
2056HINT:  You can drop trigger trg1 on table trigpart instead.
2057drop trigger trg1 on trigpart3;	-- fail
2058ERROR:  cannot drop trigger trg1 on table trigpart3 because trigger trg1 on table trigpart requires it
2059HINT:  You can drop trigger trg1 on table trigpart instead.
2060drop table trigpart2;			-- ok, trigger should be gone in that partition
2061select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger
2062  where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;
2063  tgrelid   | tgname |     tgfoid
2064------------+--------+-----------------
2065 trigpart   | trg1   | trigger_nothing
2066 trigpart1  | trg1   | trigger_nothing
2067 trigpart3  | trg1   | trigger_nothing
2068 trigpart4  | trg1   | trigger_nothing
2069 trigpart41 | trg1   | trigger_nothing
2070 trigpart42 | trg1   | trigger_nothing
2071(6 rows)
2072
2073drop trigger trg1 on trigpart;		-- ok, all gone
2074select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger
2075  where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;
2076 tgrelid | tgname | tgfoid
2077---------+--------+--------
2078(0 rows)
2079
2080-- check detach behavior
2081create trigger trg1 after insert on trigpart for each row execute procedure trigger_nothing();
2082\d trigpart3
2083             Table "public.trigpart3"
2084 Column |  Type   | Collation | Nullable | Default
2085--------+---------+-----------+----------+---------
2086 a      | integer |           |          |
2087 b      | integer |           |          |
2088Partition of: trigpart FOR VALUES FROM (2000) TO (3000)
2089Triggers:
2090    trg1 AFTER INSERT ON trigpart3 FOR EACH ROW EXECUTE FUNCTION trigger_nothing(), ON TABLE trigpart
2091
2092alter table trigpart detach partition trigpart3;
2093drop trigger trg1 on trigpart3; -- fail due to "does not exist"
2094ERROR:  trigger "trg1" for table "trigpart3" does not exist
2095alter table trigpart detach partition trigpart4;
2096drop trigger trg1 on trigpart41; -- fail due to "does not exist"
2097ERROR:  trigger "trg1" for table "trigpart41" does not exist
2098drop table trigpart4;
2099alter table trigpart attach partition trigpart3 for values from (2000) to (3000);
2100alter table trigpart detach partition trigpart3;
2101alter table trigpart attach partition trigpart3 for values from (2000) to (3000);
2102drop table trigpart3;
2103select tgrelid::regclass::text, tgname, tgfoid::regproc, tgenabled, tgisinternal from pg_trigger
2104  where tgname ~ '^trg1' order by 1;
2105  tgrelid  | tgname |     tgfoid      | tgenabled | tgisinternal
2106-----------+--------+-----------------+-----------+--------------
2107 trigpart  | trg1   | trigger_nothing | O         | f
2108 trigpart1 | trg1   | trigger_nothing | O         | t
2109(2 rows)
2110
2111create table trigpart3 (like trigpart);
2112create trigger trg1 after insert on trigpart3 for each row execute procedure trigger_nothing();
2113\d trigpart3
2114             Table "public.trigpart3"
2115 Column |  Type   | Collation | Nullable | Default
2116--------+---------+-----------+----------+---------
2117 a      | integer |           |          |
2118 b      | integer |           |          |
2119Triggers:
2120    trg1 AFTER INSERT ON trigpart3 FOR EACH ROW EXECUTE FUNCTION trigger_nothing()
2121
2122alter table trigpart attach partition trigpart3 FOR VALUES FROM (2000) to (3000); -- fail
2123ERROR:  trigger "trg1" for relation "trigpart3" already exists
2124drop table trigpart3;
2125drop table trigpart;
2126drop function trigger_nothing();
2127--
2128-- Verify that triggers are fired for partitioned tables
2129--
2130create table parted_stmt_trig (a int) partition by list (a);
2131create table parted_stmt_trig1 partition of parted_stmt_trig for values in (1);
2132create table parted_stmt_trig2 partition of parted_stmt_trig for values in (2);
2133create table parted2_stmt_trig (a int) partition by list (a);
2134create table parted2_stmt_trig1 partition of parted2_stmt_trig for values in (1);
2135create table parted2_stmt_trig2 partition of parted2_stmt_trig for values in (2);
2136create or replace function trigger_notice() returns trigger as $$
2137  begin
2138    raise notice 'trigger % on % % % for %', TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL;
2139    if TG_LEVEL = 'ROW' then
2140       return NEW;
2141    end if;
2142    return null;
2143  end;
2144  $$ language plpgsql;
2145-- insert/update/delete statement-level triggers on the parent
2146create trigger trig_ins_before before insert on parted_stmt_trig
2147  for each statement execute procedure trigger_notice();
2148create trigger trig_ins_after after insert on parted_stmt_trig
2149  for each statement execute procedure trigger_notice();
2150create trigger trig_upd_before before update on parted_stmt_trig
2151  for each statement execute procedure trigger_notice();
2152create trigger trig_upd_after after update on parted_stmt_trig
2153  for each statement execute procedure trigger_notice();
2154create trigger trig_del_before before delete on parted_stmt_trig
2155  for each statement execute procedure trigger_notice();
2156create trigger trig_del_after after delete on parted_stmt_trig
2157  for each statement execute procedure trigger_notice();
2158-- insert/update/delete row-level triggers on the parent
2159create trigger trig_ins_after_parent after insert on parted_stmt_trig
2160  for each row execute procedure trigger_notice();
2161create trigger trig_upd_after_parent after update on parted_stmt_trig
2162  for each row execute procedure trigger_notice();
2163create trigger trig_del_after_parent after delete on parted_stmt_trig
2164  for each row execute procedure trigger_notice();
2165-- insert/update/delete row-level triggers on the first partition
2166create trigger trig_ins_before_child before insert on parted_stmt_trig1
2167  for each row execute procedure trigger_notice();
2168create trigger trig_ins_after_child after insert on parted_stmt_trig1
2169  for each row execute procedure trigger_notice();
2170create trigger trig_upd_before_child before update on parted_stmt_trig1
2171  for each row execute procedure trigger_notice();
2172create trigger trig_upd_after_child after update on parted_stmt_trig1
2173  for each row execute procedure trigger_notice();
2174create trigger trig_del_before_child before delete on parted_stmt_trig1
2175  for each row execute procedure trigger_notice();
2176create trigger trig_del_after_child after delete on parted_stmt_trig1
2177  for each row execute procedure trigger_notice();
2178-- insert/update/delete statement-level triggers on the parent
2179create trigger trig_ins_before_3 before insert on parted2_stmt_trig
2180  for each statement execute procedure trigger_notice();
2181create trigger trig_ins_after_3 after insert on parted2_stmt_trig
2182  for each statement execute procedure trigger_notice();
2183create trigger trig_upd_before_3 before update on parted2_stmt_trig
2184  for each statement execute procedure trigger_notice();
2185create trigger trig_upd_after_3 after update on parted2_stmt_trig
2186  for each statement execute procedure trigger_notice();
2187create trigger trig_del_before_3 before delete on parted2_stmt_trig
2188  for each statement execute procedure trigger_notice();
2189create trigger trig_del_after_3 after delete on parted2_stmt_trig
2190  for each statement execute procedure trigger_notice();
2191with ins (a) as (
2192  insert into parted2_stmt_trig values (1), (2) returning a
2193) insert into parted_stmt_trig select a from ins returning tableoid::regclass, a;
2194NOTICE:  trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT
2195NOTICE:  trigger trig_ins_before_3 on parted2_stmt_trig BEFORE INSERT for STATEMENT
2196NOTICE:  trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW
2197NOTICE:  trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW
2198NOTICE:  trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW
2199NOTICE:  trigger trig_ins_after_parent on parted_stmt_trig2 AFTER INSERT for ROW
2200NOTICE:  trigger trig_ins_after_3 on parted2_stmt_trig AFTER INSERT for STATEMENT
2201NOTICE:  trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT
2202     tableoid      | a
2203-------------------+---
2204 parted_stmt_trig1 | 1
2205 parted_stmt_trig2 | 2
2206(2 rows)
2207
2208with upd as (
2209  update parted2_stmt_trig set a = a
2210) update parted_stmt_trig  set a = a;
2211NOTICE:  trigger trig_upd_before on parted_stmt_trig BEFORE UPDATE for STATEMENT
2212NOTICE:  trigger trig_upd_before_child on parted_stmt_trig1 BEFORE UPDATE for ROW
2213NOTICE:  trigger trig_upd_before_3 on parted2_stmt_trig BEFORE UPDATE for STATEMENT
2214NOTICE:  trigger trig_upd_after_child on parted_stmt_trig1 AFTER UPDATE for ROW
2215NOTICE:  trigger trig_upd_after_parent on parted_stmt_trig1 AFTER UPDATE for ROW
2216NOTICE:  trigger trig_upd_after_parent on parted_stmt_trig2 AFTER UPDATE for ROW
2217NOTICE:  trigger trig_upd_after on parted_stmt_trig AFTER UPDATE for STATEMENT
2218NOTICE:  trigger trig_upd_after_3 on parted2_stmt_trig AFTER UPDATE for STATEMENT
2219delete from parted_stmt_trig;
2220NOTICE:  trigger trig_del_before on parted_stmt_trig BEFORE DELETE for STATEMENT
2221NOTICE:  trigger trig_del_before_child on parted_stmt_trig1 BEFORE DELETE for ROW
2222NOTICE:  trigger trig_del_after_parent on parted_stmt_trig2 AFTER DELETE for ROW
2223NOTICE:  trigger trig_del_after on parted_stmt_trig AFTER DELETE for STATEMENT
2224-- insert via copy on the parent
2225copy parted_stmt_trig(a) from stdin;
2226NOTICE:  trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT
2227NOTICE:  trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW
2228NOTICE:  trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW
2229NOTICE:  trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW
2230NOTICE:  trigger trig_ins_after_parent on parted_stmt_trig2 AFTER INSERT for ROW
2231NOTICE:  trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT
2232-- insert via copy on the first partition
2233copy parted_stmt_trig1(a) from stdin;
2234NOTICE:  trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW
2235NOTICE:  trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW
2236NOTICE:  trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW
2237-- Disabling a trigger in the parent table should disable children triggers too
2238alter table parted_stmt_trig disable trigger trig_ins_after_parent;
2239insert into parted_stmt_trig values (1);
2240NOTICE:  trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT
2241NOTICE:  trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW
2242NOTICE:  trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW
2243NOTICE:  trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT
2244alter table parted_stmt_trig enable trigger trig_ins_after_parent;
2245insert into parted_stmt_trig values (1);
2246NOTICE:  trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT
2247NOTICE:  trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW
2248NOTICE:  trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW
2249NOTICE:  trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW
2250NOTICE:  trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT
2251drop table parted_stmt_trig, parted2_stmt_trig;
2252-- Verify that triggers fire in alphabetical order
2253create table parted_trig (a int) partition by range (a);
2254create table parted_trig_1 partition of parted_trig for values from (0) to (1000)
2255   partition by range (a);
2256create table parted_trig_1_1 partition of parted_trig_1 for values from (0) to (100);
2257create table parted_trig_2 partition of parted_trig for values from (1000) to (2000);
2258create trigger zzz after insert on parted_trig for each row execute procedure trigger_notice();
2259create trigger mmm after insert on parted_trig_1_1 for each row execute procedure trigger_notice();
2260create trigger aaa after insert on parted_trig_1 for each row execute procedure trigger_notice();
2261create trigger bbb after insert on parted_trig for each row execute procedure trigger_notice();
2262create trigger qqq after insert on parted_trig_1_1 for each row execute procedure trigger_notice();
2263insert into parted_trig values (50), (1500);
2264NOTICE:  trigger aaa on parted_trig_1_1 AFTER INSERT for ROW
2265NOTICE:  trigger bbb on parted_trig_1_1 AFTER INSERT for ROW
2266NOTICE:  trigger mmm on parted_trig_1_1 AFTER INSERT for ROW
2267NOTICE:  trigger qqq on parted_trig_1_1 AFTER INSERT for ROW
2268NOTICE:  trigger zzz on parted_trig_1_1 AFTER INSERT for ROW
2269NOTICE:  trigger bbb on parted_trig_2 AFTER INSERT for ROW
2270NOTICE:  trigger zzz on parted_trig_2 AFTER INSERT for ROW
2271drop table parted_trig;
2272-- Verify propagation of trigger arguments to partitions
2273create table parted_trig (a int) partition by list (a);
2274create table parted_trig1 partition of parted_trig for values in (1);
2275create or replace function trigger_notice() returns trigger as $$
2276  declare
2277    arg1 text = TG_ARGV[0];
2278    arg2 integer = TG_ARGV[1];
2279  begin
2280    raise notice 'trigger % on % % % for % args % %',
2281		TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL, arg1, arg2;
2282    return null;
2283  end;
2284  $$ language plpgsql;
2285create trigger aaa after insert on parted_trig
2286   for each row execute procedure trigger_notice('quirky', 1);
2287-- Verify propagation of trigger arguments to partitions attached after creating trigger
2288create table parted_trig2 partition of parted_trig for values in (2);
2289create table parted_trig3 (like parted_trig);
2290alter table parted_trig attach partition parted_trig3 for values in (3);
2291insert into parted_trig values (1), (2), (3);
2292NOTICE:  trigger aaa on parted_trig1 AFTER INSERT for ROW args quirky 1
2293NOTICE:  trigger aaa on parted_trig2 AFTER INSERT for ROW args quirky 1
2294NOTICE:  trigger aaa on parted_trig3 AFTER INSERT for ROW args quirky 1
2295drop table parted_trig;
2296-- test irregular partitions (i.e., different column definitions),
2297-- including that the WHEN clause works
2298create function bark(text) returns bool language plpgsql immutable
2299  as $$ begin raise notice '% <- woof!', $1; return true; end; $$;
2300create or replace function trigger_notice_ab() returns trigger as $$
2301  begin
2302    raise notice 'trigger % on % % % for %: (a,b)=(%,%)',
2303		TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL,
2304		NEW.a, NEW.b;
2305    if TG_LEVEL = 'ROW' then
2306       return NEW;
2307    end if;
2308    return null;
2309  end;
2310  $$ language plpgsql;
2311create table parted_irreg_ancestor (fd text, b text, fd2 int, fd3 int, a int)
2312  partition by range (b);
2313alter table parted_irreg_ancestor drop column fd,
2314  drop column fd2, drop column fd3;
2315create table parted_irreg (fd int, a int, fd2 int, b text)
2316  partition by range (b);
2317alter table parted_irreg drop column fd, drop column fd2;
2318alter table parted_irreg_ancestor attach partition parted_irreg
2319  for values from ('aaaa') to ('zzzz');
2320create table parted1_irreg (b text, fd int, a int);
2321alter table parted1_irreg drop column fd;
2322alter table parted_irreg attach partition parted1_irreg
2323  for values from ('aaaa') to ('bbbb');
2324create trigger parted_trig after insert on parted_irreg
2325  for each row execute procedure trigger_notice_ab();
2326create trigger parted_trig_odd after insert on parted_irreg for each row
2327  when (bark(new.b) AND new.a % 2 = 1) execute procedure trigger_notice_ab();
2328-- we should hear barking for every insert, but parted_trig_odd only emits
2329-- noise for odd values of a. parted_trig does it for all inserts.
2330insert into parted_irreg values (1, 'aardvark'), (2, 'aanimals');
2331NOTICE:  aardvark <- woof!
2332NOTICE:  aanimals <- woof!
2333NOTICE:  trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(1,aardvark)
2334NOTICE:  trigger parted_trig_odd on parted1_irreg AFTER INSERT for ROW: (a,b)=(1,aardvark)
2335NOTICE:  trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(2,aanimals)
2336insert into parted1_irreg values ('aardwolf', 2);
2337NOTICE:  aardwolf <- woof!
2338NOTICE:  trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(2,aardwolf)
2339insert into parted_irreg_ancestor values ('aasvogel', 3);
2340NOTICE:  aasvogel <- woof!
2341NOTICE:  trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(3,aasvogel)
2342NOTICE:  trigger parted_trig_odd on parted1_irreg AFTER INSERT for ROW: (a,b)=(3,aasvogel)
2343drop table parted_irreg_ancestor;
2344-- Before triggers and partitions
2345create table parted (a int, b int, c text) partition by list (a);
2346create table parted_1 partition of parted for values in (1)
2347  partition by list (b);
2348create table parted_1_1 partition of parted_1 for values in (1);
2349create function parted_trigfunc() returns trigger language plpgsql as $$
2350begin
2351  new.a = new.a + 1;
2352  return new;
2353end;
2354$$;
2355insert into parted values (1, 1, 'uno uno v1');    -- works
2356create trigger t before insert or update or delete on parted
2357  for each row execute function parted_trigfunc();
2358insert into parted values (1, 1, 'uno uno v2');    -- fail
2359ERROR:  moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported
2360DETAIL:  Before executing trigger "t", the row was to be in partition "public.parted_1_1".
2361update parted set c = c || 'v3';                   -- fail
2362ERROR:  no partition of relation "parted" found for row
2363DETAIL:  Partition key of the failing row contains (a) = (2).
2364create or replace function parted_trigfunc() returns trigger language plpgsql as $$
2365begin
2366  new.b = new.b + 1;
2367  return new;
2368end;
2369$$;
2370insert into parted values (1, 1, 'uno uno v4');    -- fail
2371ERROR:  moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported
2372DETAIL:  Before executing trigger "t", the row was to be in partition "public.parted_1_1".
2373update parted set c = c || 'v5';                   -- fail
2374ERROR:  no partition of relation "parted_1" found for row
2375DETAIL:  Partition key of the failing row contains (b) = (2).
2376create or replace function parted_trigfunc() returns trigger language plpgsql as $$
2377begin
2378  new.c = new.c || ' did '|| TG_OP;
2379  return new;
2380end;
2381$$;
2382insert into parted values (1, 1, 'uno uno');       -- works
2383update parted set c = c || ' v6';                   -- works
2384select tableoid::regclass, * from parted;
2385  tableoid  | a | b |                c
2386------------+---+---+----------------------------------
2387 parted_1_1 | 1 | 1 | uno uno v1 v6 did UPDATE
2388 parted_1_1 | 1 | 1 | uno uno did INSERT v6 did UPDATE
2389(2 rows)
2390
2391-- update itself moves tuple to new partition; trigger still works
2392truncate table parted;
2393create table parted_2 partition of parted for values in (2);
2394insert into parted values (1, 1, 'uno uno v5');
2395update parted set a = 2;
2396select tableoid::regclass, * from parted;
2397 tableoid | a | b |                      c
2398----------+---+---+---------------------------------------------
2399 parted_2 | 2 | 1 | uno uno v5 did INSERT did UPDATE did INSERT
2400(1 row)
2401
2402-- both trigger and update change the partition
2403create or replace function parted_trigfunc2() returns trigger language plpgsql as $$
2404begin
2405  new.a = new.a + 1;
2406  return new;
2407end;
2408$$;
2409create trigger t2 before update on parted
2410  for each row execute function parted_trigfunc2();
2411truncate table parted;
2412insert into parted values (1, 1, 'uno uno v6');
2413create table parted_3 partition of parted for values in (3);
2414update parted set a = a + 1;
2415select tableoid::regclass, * from parted;
2416 tableoid | a | b |                      c
2417----------+---+---+---------------------------------------------
2418 parted_3 | 3 | 1 | uno uno v6 did INSERT did UPDATE did INSERT
2419(1 row)
2420
2421-- there's no partition for a=0, but this update works anyway because
2422-- the trigger causes the tuple to be routed to another partition
2423update parted set a = 0;
2424select tableoid::regclass, * from parted;
2425  tableoid  | a | b |                                 c
2426------------+---+---+-------------------------------------------------------------------
2427 parted_1_1 | 1 | 1 | uno uno v6 did INSERT did UPDATE did INSERT did UPDATE did INSERT
2428(1 row)
2429
2430drop table parted;
2431create table parted (a int, b int, c text) partition by list ((a + b));
2432create or replace function parted_trigfunc() returns trigger language plpgsql as $$
2433begin
2434  new.a = new.a + new.b;
2435  return new;
2436end;
2437$$;
2438create table parted_1 partition of parted for values in (1, 2);
2439create table parted_2 partition of parted for values in (3, 4);
2440create trigger t before insert or update on parted
2441  for each row execute function parted_trigfunc();
2442insert into parted values (0, 1, 'zero win');
2443insert into parted values (1, 1, 'one fail');
2444ERROR:  moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported
2445DETAIL:  Before executing trigger "t", the row was to be in partition "public.parted_1".
2446insert into parted values (1, 2, 'two fail');
2447ERROR:  moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported
2448DETAIL:  Before executing trigger "t", the row was to be in partition "public.parted_2".
2449select * from parted;
2450 a | b |    c
2451---+---+----------
2452 1 | 1 | zero win
2453(1 row)
2454
2455drop table parted;
2456drop function parted_trigfunc();
2457--
2458-- Constraint triggers and partitioned tables
2459create table parted_constr_ancestor (a int, b text)
2460  partition by range (b);
2461create table parted_constr (a int, b text)
2462  partition by range (b);
2463alter table parted_constr_ancestor attach partition parted_constr
2464  for values from ('aaaa') to ('zzzz');
2465create table parted1_constr (a int, b text);
2466alter table parted_constr attach partition parted1_constr
2467  for values from ('aaaa') to ('bbbb');
2468create constraint trigger parted_trig after insert on parted_constr_ancestor
2469  deferrable
2470  for each row execute procedure trigger_notice_ab();
2471create constraint trigger parted_trig_two after insert on parted_constr
2472  deferrable initially deferred
2473  for each row when (bark(new.b) AND new.a % 2 = 1)
2474  execute procedure trigger_notice_ab();
2475-- The immediate constraint is fired immediately; the WHEN clause of the
2476-- deferred constraint is also called immediately.  The deferred constraint
2477-- is fired at commit time.
2478begin;
2479insert into parted_constr values (1, 'aardvark');
2480NOTICE:  aardvark <- woof!
2481NOTICE:  trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark)
2482insert into parted1_constr values (2, 'aardwolf');
2483NOTICE:  aardwolf <- woof!
2484NOTICE:  trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(2,aardwolf)
2485insert into parted_constr_ancestor values (3, 'aasvogel');
2486NOTICE:  aasvogel <- woof!
2487NOTICE:  trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel)
2488commit;
2489NOTICE:  trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark)
2490NOTICE:  trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel)
2491-- The WHEN clause is immediate, and both constraint triggers are fired at
2492-- commit time.
2493begin;
2494set constraints parted_trig deferred;
2495insert into parted_constr values (1, 'aardvark');
2496NOTICE:  aardvark <- woof!
2497insert into parted1_constr values (2, 'aardwolf'), (3, 'aasvogel');
2498NOTICE:  aardwolf <- woof!
2499NOTICE:  aasvogel <- woof!
2500commit;
2501NOTICE:  trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark)
2502NOTICE:  trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark)
2503NOTICE:  trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(2,aardwolf)
2504NOTICE:  trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel)
2505NOTICE:  trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel)
2506drop table parted_constr_ancestor;
2507drop function bark(text);
2508-- Test that the WHEN clause is set properly to partitions
2509create table parted_trigger (a int, b text) partition by range (a);
2510create table parted_trigger_1 partition of parted_trigger for values from (0) to (1000);
2511create table parted_trigger_2 (drp int, a int, b text);
2512alter table parted_trigger_2 drop column drp;
2513alter table parted_trigger attach partition parted_trigger_2 for values from (1000) to (2000);
2514create trigger parted_trigger after update on parted_trigger
2515  for each row when (new.a % 2 = 1 and length(old.b) >= 2) execute procedure trigger_notice_ab();
2516create table parted_trigger_3 (b text, a int) partition by range (length(b));
2517create table parted_trigger_3_1 partition of parted_trigger_3 for values from (1) to (3);
2518create table parted_trigger_3_2 partition of parted_trigger_3 for values from (3) to (5);
2519alter table parted_trigger attach partition parted_trigger_3 for values from (2000) to (3000);
2520insert into parted_trigger values
2521    (0, 'a'), (1, 'bbb'), (2, 'bcd'), (3, 'c'),
2522	(1000, 'c'), (1001, 'ddd'), (1002, 'efg'), (1003, 'f'),
2523	(2000, 'e'), (2001, 'fff'), (2002, 'ghi'), (2003, 'h');
2524update parted_trigger set a = a + 2; -- notice for odd 'a' values, long 'b' values
2525NOTICE:  trigger parted_trigger on parted_trigger_1 AFTER UPDATE for ROW: (a,b)=(3,bbb)
2526NOTICE:  trigger parted_trigger on parted_trigger_2 AFTER UPDATE for ROW: (a,b)=(1003,ddd)
2527NOTICE:  trigger parted_trigger on parted_trigger_3_2 AFTER UPDATE for ROW: (a,b)=(2003,fff)
2528drop table parted_trigger;
2529-- try a constraint trigger, also
2530create table parted_referenced (a int);
2531create table unparted_trigger (a int, b text);	-- for comparison purposes
2532create table parted_trigger (a int, b text) partition by range (a);
2533create table parted_trigger_1 partition of parted_trigger for values from (0) to (1000);
2534create table parted_trigger_2 (drp int, a int, b text);
2535alter table parted_trigger_2 drop column drp;
2536alter table parted_trigger attach partition parted_trigger_2 for values from (1000) to (2000);
2537create constraint trigger parted_trigger after update on parted_trigger
2538  from parted_referenced
2539  for each row execute procedure trigger_notice_ab();
2540create constraint trigger parted_trigger after update on unparted_trigger
2541  from parted_referenced
2542  for each row execute procedure trigger_notice_ab();
2543create table parted_trigger_3 (b text, a int) partition by range (length(b));
2544create table parted_trigger_3_1 partition of parted_trigger_3 for values from (1) to (3);
2545create table parted_trigger_3_2 partition of parted_trigger_3 for values from (3) to (5);
2546alter table parted_trigger attach partition parted_trigger_3 for values from (2000) to (3000);
2547select tgname, conname, t.tgrelid::regclass, t.tgconstrrelid::regclass,
2548  c.conrelid::regclass, c.confrelid::regclass
2549  from pg_trigger t join pg_constraint c on (t.tgconstraint = c.oid)
2550  where tgname = 'parted_trigger'
2551  order by t.tgrelid::regclass::text;
2552     tgname     |    conname     |      tgrelid       |   tgconstrrelid   |      conrelid      | confrelid
2553----------------+----------------+--------------------+-------------------+--------------------+-----------
2554 parted_trigger | parted_trigger | parted_trigger     | parted_referenced | parted_trigger     | -
2555 parted_trigger | parted_trigger | parted_trigger_1   | parted_referenced | parted_trigger_1   | -
2556 parted_trigger | parted_trigger | parted_trigger_2   | parted_referenced | parted_trigger_2   | -
2557 parted_trigger | parted_trigger | parted_trigger_3   | parted_referenced | parted_trigger_3   | -
2558 parted_trigger | parted_trigger | parted_trigger_3_1 | parted_referenced | parted_trigger_3_1 | -
2559 parted_trigger | parted_trigger | parted_trigger_3_2 | parted_referenced | parted_trigger_3_2 | -
2560 parted_trigger | parted_trigger | unparted_trigger   | parted_referenced | unparted_trigger   | -
2561(7 rows)
2562
2563drop table parted_referenced, parted_trigger, unparted_trigger;
2564-- verify that the "AFTER UPDATE OF columns" event is propagated correctly
2565create table parted_trigger (a int, b text) partition by range (a);
2566create table parted_trigger_1 partition of parted_trigger for values from (0) to (1000);
2567create table parted_trigger_2 (drp int, a int, b text);
2568alter table parted_trigger_2 drop column drp;
2569alter table parted_trigger attach partition parted_trigger_2 for values from (1000) to (2000);
2570create trigger parted_trigger after update of b on parted_trigger
2571  for each row execute procedure trigger_notice_ab();
2572create table parted_trigger_3 (b text, a int) partition by range (length(b));
2573create table parted_trigger_3_1 partition of parted_trigger_3 for values from (1) to (4);
2574create table parted_trigger_3_2 partition of parted_trigger_3 for values from (4) to (8);
2575alter table parted_trigger attach partition parted_trigger_3 for values from (2000) to (3000);
2576insert into parted_trigger values (0, 'a'), (1000, 'c'), (2000, 'e'), (2001, 'eeee');
2577update parted_trigger set a = a + 2;	-- no notices here
2578update parted_trigger set b = b || 'b';	-- all triggers should fire
2579NOTICE:  trigger parted_trigger on parted_trigger_1 AFTER UPDATE for ROW: (a,b)=(2,ab)
2580NOTICE:  trigger parted_trigger on parted_trigger_2 AFTER UPDATE for ROW: (a,b)=(1002,cb)
2581NOTICE:  trigger parted_trigger on parted_trigger_3_1 AFTER UPDATE for ROW: (a,b)=(2002,eb)
2582NOTICE:  trigger parted_trigger on parted_trigger_3_2 AFTER UPDATE for ROW: (a,b)=(2003,eeeeb)
2583drop table parted_trigger;
2584drop function trigger_notice_ab();
2585-- Make sure we don't end up with unnecessary copies of triggers, when
2586-- cloning them.
2587create table trg_clone (a int) partition by range (a);
2588create table trg_clone1 partition of trg_clone for values from (0) to (1000);
2589alter table trg_clone add constraint uniq unique (a) deferrable;
2590create table trg_clone2 partition of trg_clone for values from (1000) to (2000);
2591create table trg_clone3 partition of trg_clone for values from (2000) to (3000)
2592  partition by range (a);
2593create table trg_clone_3_3 partition of trg_clone3 for values from (2000) to (2100);
2594select tgrelid::regclass, count(*) from pg_trigger
2595  where tgrelid::regclass in ('trg_clone', 'trg_clone1', 'trg_clone2',
2596	'trg_clone3', 'trg_clone_3_3')
2597  group by tgrelid::regclass order by tgrelid::regclass;
2598    tgrelid    | count
2599---------------+-------
2600 trg_clone     |     1
2601 trg_clone1    |     1
2602 trg_clone2    |     1
2603 trg_clone3    |     1
2604 trg_clone_3_3 |     1
2605(5 rows)
2606
2607drop table trg_clone;
2608-- Test the interaction between ALTER TABLE .. DISABLE TRIGGER and
2609-- both kinds of inheritance.  Historically, legacy inheritance has
2610-- not recursed to children, so that behavior is preserved.
2611create table parent (a int);
2612create table child1 () inherits (parent);
2613create function trig_nothing() returns trigger language plpgsql
2614  as $$ begin return null; end $$;
2615create trigger tg after insert on parent
2616  for each row execute function trig_nothing();
2617create trigger tg after insert on child1
2618  for each row execute function trig_nothing();
2619alter table parent disable trigger tg;
2620select tgrelid::regclass, tgname, tgenabled from pg_trigger
2621  where tgrelid in ('parent'::regclass, 'child1'::regclass)
2622  order by tgrelid::regclass::text;
2623 tgrelid | tgname | tgenabled
2624---------+--------+-----------
2625 child1  | tg     | O
2626 parent  | tg     | D
2627(2 rows)
2628
2629alter table only parent enable always trigger tg;
2630select tgrelid::regclass, tgname, tgenabled from pg_trigger
2631  where tgrelid in ('parent'::regclass, 'child1'::regclass)
2632  order by tgrelid::regclass::text;
2633 tgrelid | tgname | tgenabled
2634---------+--------+-----------
2635 child1  | tg     | O
2636 parent  | tg     | A
2637(2 rows)
2638
2639drop table parent, child1;
2640create table parent (a int) partition by list (a);
2641create table child1 partition of parent for values in (1);
2642create trigger tg after insert on parent
2643  for each row execute procedure trig_nothing();
2644select tgrelid::regclass, tgname, tgenabled from pg_trigger
2645  where tgrelid in ('parent'::regclass, 'child1'::regclass)
2646  order by tgrelid::regclass::text;
2647 tgrelid | tgname | tgenabled
2648---------+--------+-----------
2649 child1  | tg     | O
2650 parent  | tg     | O
2651(2 rows)
2652
2653alter table only parent enable always trigger tg;
2654select tgrelid::regclass, tgname, tgenabled from pg_trigger
2655  where tgrelid in ('parent'::regclass, 'child1'::regclass)
2656  order by tgrelid::regclass::text;
2657 tgrelid | tgname | tgenabled
2658---------+--------+-----------
2659 child1  | tg     | O
2660 parent  | tg     | A
2661(2 rows)
2662
2663drop table parent, child1;
2664-- Verify that firing state propagates correctly on creation, too
2665CREATE TABLE trgfire (i int) PARTITION BY RANGE (i);
2666CREATE TABLE trgfire1 PARTITION OF trgfire FOR VALUES FROM (1) TO (10);
2667CREATE OR REPLACE FUNCTION tgf() RETURNS trigger LANGUAGE plpgsql
2668  AS $$ begin raise exception 'except'; end $$;
2669CREATE TRIGGER tg AFTER INSERT ON trgfire FOR EACH ROW EXECUTE FUNCTION tgf();
2670INSERT INTO trgfire VALUES (1);
2671ERROR:  except
2672CONTEXT:  PL/pgSQL function tgf() line 1 at RAISE
2673ALTER TABLE trgfire DISABLE TRIGGER tg;
2674INSERT INTO trgfire VALUES (1);
2675CREATE TABLE trgfire2 PARTITION OF trgfire FOR VALUES FROM (10) TO (20);
2676INSERT INTO trgfire VALUES (11);
2677CREATE TABLE trgfire3 (LIKE trgfire);
2678ALTER TABLE trgfire ATTACH PARTITION trgfire3 FOR VALUES FROM (20) TO (30);
2679INSERT INTO trgfire VALUES (21);
2680CREATE TABLE trgfire4 PARTITION OF trgfire FOR VALUES FROM (30) TO (40) PARTITION BY LIST (i);
2681CREATE TABLE trgfire4_30 PARTITION OF trgfire4 FOR VALUES IN (30);
2682INSERT INTO trgfire VALUES (30);
2683CREATE TABLE trgfire5 (LIKE trgfire) PARTITION BY LIST (i);
2684CREATE TABLE trgfire5_40 PARTITION OF trgfire5 FOR VALUES IN (40);
2685ALTER TABLE trgfire ATTACH PARTITION trgfire5 FOR VALUES FROM (40) TO (50);
2686INSERT INTO trgfire VALUES (40);
2687SELECT tgrelid::regclass, tgenabled FROM pg_trigger
2688  WHERE tgrelid::regclass IN (SELECT oid from pg_class where relname LIKE 'trgfire%')
2689  ORDER BY tgrelid::regclass::text;
2690   tgrelid   | tgenabled
2691-------------+-----------
2692 trgfire     | D
2693 trgfire1    | D
2694 trgfire2    | D
2695 trgfire3    | D
2696 trgfire4    | D
2697 trgfire4_30 | D
2698 trgfire5    | D
2699 trgfire5_40 | D
2700(8 rows)
2701
2702ALTER TABLE trgfire ENABLE TRIGGER tg;
2703INSERT INTO trgfire VALUES (1);
2704ERROR:  except
2705CONTEXT:  PL/pgSQL function tgf() line 1 at RAISE
2706INSERT INTO trgfire VALUES (11);
2707ERROR:  except
2708CONTEXT:  PL/pgSQL function tgf() line 1 at RAISE
2709INSERT INTO trgfire VALUES (21);
2710ERROR:  except
2711CONTEXT:  PL/pgSQL function tgf() line 1 at RAISE
2712INSERT INTO trgfire VALUES (30);
2713ERROR:  except
2714CONTEXT:  PL/pgSQL function tgf() line 1 at RAISE
2715INSERT INTO trgfire VALUES (40);
2716ERROR:  except
2717CONTEXT:  PL/pgSQL function tgf() line 1 at RAISE
2718DROP TABLE trgfire;
2719DROP FUNCTION tgf();
2720--
2721-- Test the interaction between transition tables and both kinds of
2722-- inheritance.  We'll dump the contents of the transition tables in a
2723-- format that shows the attribute order, so that we can distinguish
2724-- tuple formats (though not dropped attributes).
2725--
2726create or replace function dump_insert() returns trigger language plpgsql as
2727$$
2728  begin
2729    raise notice 'trigger = %, new table = %',
2730                 TG_NAME,
2731                 (select string_agg(new_table::text, ', ' order by a) from new_table);
2732    return null;
2733  end;
2734$$;
2735create or replace function dump_update() returns trigger language plpgsql as
2736$$
2737  begin
2738    raise notice 'trigger = %, old table = %, new table = %',
2739                 TG_NAME,
2740                 (select string_agg(old_table::text, ', ' order by a) from old_table),
2741                 (select string_agg(new_table::text, ', ' order by a) from new_table);
2742    return null;
2743  end;
2744$$;
2745create or replace function dump_delete() returns trigger language plpgsql as
2746$$
2747  begin
2748    raise notice 'trigger = %, old table = %',
2749                 TG_NAME,
2750                 (select string_agg(old_table::text, ', ' order by a) from old_table);
2751    return null;
2752  end;
2753$$;
2754--
2755-- Verify behavior of statement triggers on partition hierarchy with
2756-- transition tables.  Tuples should appear to each trigger in the
2757-- format of the relation the trigger is attached to.
2758--
2759-- set up a partition hierarchy with some different TupleDescriptors
2760create table parent (a text, b int) partition by list (a);
2761-- a child matching parent
2762create table child1 partition of parent for values in ('AAA');
2763-- a child with a dropped column
2764create table child2 (x int, a text, b int);
2765alter table child2 drop column x;
2766alter table parent attach partition child2 for values in ('BBB');
2767-- a child with a different column order
2768create table child3 (b int, a text);
2769alter table parent attach partition child3 for values in ('CCC');
2770create trigger parent_insert_trig
2771  after insert on parent referencing new table as new_table
2772  for each statement execute procedure dump_insert();
2773create trigger parent_update_trig
2774  after update on parent referencing old table as old_table new table as new_table
2775  for each statement execute procedure dump_update();
2776create trigger parent_delete_trig
2777  after delete on parent referencing old table as old_table
2778  for each statement execute procedure dump_delete();
2779create trigger child1_insert_trig
2780  after insert on child1 referencing new table as new_table
2781  for each statement execute procedure dump_insert();
2782create trigger child1_update_trig
2783  after update on child1 referencing old table as old_table new table as new_table
2784  for each statement execute procedure dump_update();
2785create trigger child1_delete_trig
2786  after delete on child1 referencing old table as old_table
2787  for each statement execute procedure dump_delete();
2788create trigger child2_insert_trig
2789  after insert on child2 referencing new table as new_table
2790  for each statement execute procedure dump_insert();
2791create trigger child2_update_trig
2792  after update on child2 referencing old table as old_table new table as new_table
2793  for each statement execute procedure dump_update();
2794create trigger child2_delete_trig
2795  after delete on child2 referencing old table as old_table
2796  for each statement execute procedure dump_delete();
2797create trigger child3_insert_trig
2798  after insert on child3 referencing new table as new_table
2799  for each statement execute procedure dump_insert();
2800create trigger child3_update_trig
2801  after update on child3 referencing old table as old_table new table as new_table
2802  for each statement execute procedure dump_update();
2803create trigger child3_delete_trig
2804  after delete on child3 referencing old table as old_table
2805  for each statement execute procedure dump_delete();
2806SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
2807       action_order, action_condition, action_orientation, action_timing,
2808       action_reference_old_table, action_reference_new_table
2809  FROM information_schema.triggers
2810  WHERE event_object_table IN ('parent', 'child1', 'child2', 'child3')
2811  ORDER BY trigger_name COLLATE "C", 2;
2812    trigger_name    | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table
2813--------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+----------------------------
2814 child1_delete_trig | DELETE             | public              | child1             |            1 |                  | STATEMENT          | AFTER         | old_table                  |
2815 child1_insert_trig | INSERT             | public              | child1             |            1 |                  | STATEMENT          | AFTER         |                            | new_table
2816 child1_update_trig | UPDATE             | public              | child1             |            1 |                  | STATEMENT          | AFTER         | old_table                  | new_table
2817 child2_delete_trig | DELETE             | public              | child2             |            1 |                  | STATEMENT          | AFTER         | old_table                  |
2818 child2_insert_trig | INSERT             | public              | child2             |            1 |                  | STATEMENT          | AFTER         |                            | new_table
2819 child2_update_trig | UPDATE             | public              | child2             |            1 |                  | STATEMENT          | AFTER         | old_table                  | new_table
2820 child3_delete_trig | DELETE             | public              | child3             |            1 |                  | STATEMENT          | AFTER         | old_table                  |
2821 child3_insert_trig | INSERT             | public              | child3             |            1 |                  | STATEMENT          | AFTER         |                            | new_table
2822 child3_update_trig | UPDATE             | public              | child3             |            1 |                  | STATEMENT          | AFTER         | old_table                  | new_table
2823 parent_delete_trig | DELETE             | public              | parent             |            1 |                  | STATEMENT          | AFTER         | old_table                  |
2824 parent_insert_trig | INSERT             | public              | parent             |            1 |                  | STATEMENT          | AFTER         |                            | new_table
2825 parent_update_trig | UPDATE             | public              | parent             |            1 |                  | STATEMENT          | AFTER         | old_table                  | new_table
2826(12 rows)
2827
2828-- insert directly into children sees respective child-format tuples
2829insert into child1 values ('AAA', 42);
2830NOTICE:  trigger = child1_insert_trig, new table = (AAA,42)
2831insert into child2 values ('BBB', 42);
2832NOTICE:  trigger = child2_insert_trig, new table = (BBB,42)
2833insert into child3 values (42, 'CCC');
2834NOTICE:  trigger = child3_insert_trig, new table = (42,CCC)
2835-- update via parent sees parent-format tuples
2836update parent set b = b + 1;
2837NOTICE:  trigger = parent_update_trig, old table = (AAA,42), (BBB,42), (CCC,42), new table = (AAA,43), (BBB,43), (CCC,43)
2838-- delete via parent sees parent-format tuples
2839delete from parent;
2840NOTICE:  trigger = parent_delete_trig, old table = (AAA,43), (BBB,43), (CCC,43)
2841-- insert into parent sees parent-format tuples
2842insert into parent values ('AAA', 42);
2843NOTICE:  trigger = parent_insert_trig, new table = (AAA,42)
2844insert into parent values ('BBB', 42);
2845NOTICE:  trigger = parent_insert_trig, new table = (BBB,42)
2846insert into parent values ('CCC', 42);
2847NOTICE:  trigger = parent_insert_trig, new table = (CCC,42)
2848-- delete from children sees respective child-format tuples
2849delete from child1;
2850NOTICE:  trigger = child1_delete_trig, old table = (AAA,42)
2851delete from child2;
2852NOTICE:  trigger = child2_delete_trig, old table = (BBB,42)
2853delete from child3;
2854NOTICE:  trigger = child3_delete_trig, old table = (42,CCC)
2855-- copy into parent sees parent-format tuples
2856copy parent (a, b) from stdin;
2857NOTICE:  trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,42)
2858-- DML affecting parent sees tuples collected from children even if
2859-- there is no transition table trigger on the children
2860drop trigger child1_insert_trig on child1;
2861drop trigger child1_update_trig on child1;
2862drop trigger child1_delete_trig on child1;
2863drop trigger child2_insert_trig on child2;
2864drop trigger child2_update_trig on child2;
2865drop trigger child2_delete_trig on child2;
2866drop trigger child3_insert_trig on child3;
2867drop trigger child3_update_trig on child3;
2868drop trigger child3_delete_trig on child3;
2869delete from parent;
2870NOTICE:  trigger = parent_delete_trig, old table = (AAA,42), (BBB,42), (CCC,42)
2871-- copy into parent sees tuples collected from children even if there
2872-- is no transition-table trigger on the children
2873copy parent (a, b) from stdin;
2874NOTICE:  trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,42)
2875-- insert into parent with a before trigger on a child tuple before
2876-- insertion, and we capture the newly modified row in parent format
2877create or replace function intercept_insert() returns trigger language plpgsql as
2878$$
2879  begin
2880    new.b = new.b + 1000;
2881    return new;
2882  end;
2883$$;
2884create trigger intercept_insert_child3
2885  before insert on child3
2886  for each row execute procedure intercept_insert();
2887-- insert, parent trigger sees post-modification parent-format tuple
2888insert into parent values ('AAA', 42), ('BBB', 42), ('CCC', 66);
2889NOTICE:  trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,1066)
2890-- copy, parent trigger sees post-modification parent-format tuple
2891copy parent (a, b) from stdin;
2892NOTICE:  trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,1234)
2893drop table child1, child2, child3, parent;
2894drop function intercept_insert();
2895--
2896-- Verify prohibition of row triggers with transition triggers on
2897-- partitions
2898--
2899create table parent (a text, b int) partition by list (a);
2900create table child partition of parent for values in ('AAA');
2901-- adding row trigger with transition table fails
2902create trigger child_row_trig
2903  after insert on child referencing new table as new_table
2904  for each row execute procedure dump_insert();
2905ERROR:  ROW triggers with transition tables are not supported on partitions
2906-- detaching it first works
2907alter table parent detach partition child;
2908create trigger child_row_trig
2909  after insert on child referencing new table as new_table
2910  for each row execute procedure dump_insert();
2911-- but now we're not allowed to reattach it
2912alter table parent attach partition child for values in ('AAA');
2913ERROR:  trigger "child_row_trig" prevents table "child" from becoming a partition
2914DETAIL:  ROW triggers with transition tables are not supported on partitions
2915-- drop the trigger, and now we're allowed to attach it again
2916drop trigger child_row_trig on child;
2917alter table parent attach partition child for values in ('AAA');
2918drop table child, parent;
2919--
2920-- Verify behavior of statement triggers on (non-partition)
2921-- inheritance hierarchy with transition tables; similar to the
2922-- partition case, except there is no rerouting on insertion and child
2923-- tables can have extra columns
2924--
2925-- set up inheritance hierarchy with different TupleDescriptors
2926create table parent (a text, b int);
2927-- a child matching parent
2928create table child1 () inherits (parent);
2929-- a child with a different column order
2930create table child2 (b int, a text);
2931alter table child2 inherit parent;
2932-- a child with an extra column
2933create table child3 (c text) inherits (parent);
2934create trigger parent_insert_trig
2935  after insert on parent referencing new table as new_table
2936  for each statement execute procedure dump_insert();
2937create trigger parent_update_trig
2938  after update on parent referencing old table as old_table new table as new_table
2939  for each statement execute procedure dump_update();
2940create trigger parent_delete_trig
2941  after delete on parent referencing old table as old_table
2942  for each statement execute procedure dump_delete();
2943create trigger child1_insert_trig
2944  after insert on child1 referencing new table as new_table
2945  for each statement execute procedure dump_insert();
2946create trigger child1_update_trig
2947  after update on child1 referencing old table as old_table new table as new_table
2948  for each statement execute procedure dump_update();
2949create trigger child1_delete_trig
2950  after delete on child1 referencing old table as old_table
2951  for each statement execute procedure dump_delete();
2952create trigger child2_insert_trig
2953  after insert on child2 referencing new table as new_table
2954  for each statement execute procedure dump_insert();
2955create trigger child2_update_trig
2956  after update on child2 referencing old table as old_table new table as new_table
2957  for each statement execute procedure dump_update();
2958create trigger child2_delete_trig
2959  after delete on child2 referencing old table as old_table
2960  for each statement execute procedure dump_delete();
2961create trigger child3_insert_trig
2962  after insert on child3 referencing new table as new_table
2963  for each statement execute procedure dump_insert();
2964create trigger child3_update_trig
2965  after update on child3 referencing old table as old_table new table as new_table
2966  for each statement execute procedure dump_update();
2967create trigger child3_delete_trig
2968  after delete on child3 referencing old table as old_table
2969  for each statement execute procedure dump_delete();
2970-- insert directly into children sees respective child-format tuples
2971insert into child1 values ('AAA', 42);
2972NOTICE:  trigger = child1_insert_trig, new table = (AAA,42)
2973insert into child2 values (42, 'BBB');
2974NOTICE:  trigger = child2_insert_trig, new table = (42,BBB)
2975insert into child3 values ('CCC', 42, 'foo');
2976NOTICE:  trigger = child3_insert_trig, new table = (CCC,42,foo)
2977-- update via parent sees parent-format tuples
2978update parent set b = b + 1;
2979NOTICE:  trigger = parent_update_trig, old table = (AAA,42), (BBB,42), (CCC,42), new table = (AAA,43), (BBB,43), (CCC,43)
2980-- delete via parent sees parent-format tuples
2981delete from parent;
2982NOTICE:  trigger = parent_delete_trig, old table = (AAA,43), (BBB,43), (CCC,43)
2983-- reinsert values into children for next test...
2984insert into child1 values ('AAA', 42);
2985NOTICE:  trigger = child1_insert_trig, new table = (AAA,42)
2986insert into child2 values (42, 'BBB');
2987NOTICE:  trigger = child2_insert_trig, new table = (42,BBB)
2988insert into child3 values ('CCC', 42, 'foo');
2989NOTICE:  trigger = child3_insert_trig, new table = (CCC,42,foo)
2990-- delete from children sees respective child-format tuples
2991delete from child1;
2992NOTICE:  trigger = child1_delete_trig, old table = (AAA,42)
2993delete from child2;
2994NOTICE:  trigger = child2_delete_trig, old table = (42,BBB)
2995delete from child3;
2996NOTICE:  trigger = child3_delete_trig, old table = (CCC,42,foo)
2997-- copy into parent sees parent-format tuples (no rerouting, so these
2998-- are really inserted into the parent)
2999copy parent (a, b) from stdin;
3000NOTICE:  trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,42)
3001-- same behavior for copy if there is an index (interesting because rows are
3002-- captured by a different code path in copyfrom.c if there are indexes)
3003create index on parent(b);
3004copy parent (a, b) from stdin;
3005NOTICE:  trigger = parent_insert_trig, new table = (DDD,42)
3006-- DML affecting parent sees tuples collected from children even if
3007-- there is no transition table trigger on the children
3008drop trigger child1_insert_trig on child1;
3009drop trigger child1_update_trig on child1;
3010drop trigger child1_delete_trig on child1;
3011drop trigger child2_insert_trig on child2;
3012drop trigger child2_update_trig on child2;
3013drop trigger child2_delete_trig on child2;
3014drop trigger child3_insert_trig on child3;
3015drop trigger child3_update_trig on child3;
3016drop trigger child3_delete_trig on child3;
3017delete from parent;
3018NOTICE:  trigger = parent_delete_trig, old table = (AAA,42), (BBB,42), (CCC,42), (DDD,42)
3019drop table child1, child2, child3, parent;
3020--
3021-- Verify prohibition of row triggers with transition triggers on
3022-- inheritance children
3023--
3024create table parent (a text, b int);
3025create table child () inherits (parent);
3026-- adding row trigger with transition table fails
3027create trigger child_row_trig
3028  after insert on child referencing new table as new_table
3029  for each row execute procedure dump_insert();
3030ERROR:  ROW triggers with transition tables are not supported on inheritance children
3031-- disinheriting it first works
3032alter table child no inherit parent;
3033create trigger child_row_trig
3034  after insert on child referencing new table as new_table
3035  for each row execute procedure dump_insert();
3036-- but now we're not allowed to make it inherit anymore
3037alter table child inherit parent;
3038ERROR:  trigger "child_row_trig" prevents table "child" from becoming an inheritance child
3039DETAIL:  ROW triggers with transition tables are not supported in inheritance hierarchies.
3040-- drop the trigger, and now we're allowed to make it inherit again
3041drop trigger child_row_trig on child;
3042alter table child inherit parent;
3043drop table child, parent;
3044--
3045-- Verify behavior of queries with wCTEs, where multiple transition
3046-- tuplestores can be active at the same time because there are
3047-- multiple DML statements that might fire triggers with transition
3048-- tables
3049--
3050create table table1 (a int);
3051create table table2 (a text);
3052create trigger table1_trig
3053  after insert on table1 referencing new table as new_table
3054  for each statement execute procedure dump_insert();
3055create trigger table2_trig
3056  after insert on table2 referencing new table as new_table
3057  for each statement execute procedure dump_insert();
3058with wcte as (insert into table1 values (42))
3059  insert into table2 values ('hello world');
3060NOTICE:  trigger = table2_trig, new table = ("hello world")
3061NOTICE:  trigger = table1_trig, new table = (42)
3062with wcte as (insert into table1 values (43))
3063  insert into table1 values (44);
3064NOTICE:  trigger = table1_trig, new table = (43), (44)
3065select * from table1;
3066 a
3067----
3068 42
3069 44
3070 43
3071(3 rows)
3072
3073select * from table2;
3074      a
3075-------------
3076 hello world
3077(1 row)
3078
3079drop table table1;
3080drop table table2;
3081--
3082-- Verify behavior of INSERT ... ON CONFLICT DO UPDATE ... with
3083-- transition tables.
3084--
3085create table my_table (a int primary key, b text);
3086create trigger my_table_insert_trig
3087  after insert on my_table referencing new table as new_table
3088  for each statement execute procedure dump_insert();
3089create trigger my_table_update_trig
3090  after update on my_table referencing old table as old_table new table as new_table
3091  for each statement execute procedure dump_update();
3092-- inserts only
3093insert into my_table values (1, 'AAA'), (2, 'BBB')
3094  on conflict (a) do
3095  update set b = my_table.b || ':' || excluded.b;
3096NOTICE:  trigger = my_table_update_trig, old table = <NULL>, new table = <NULL>
3097NOTICE:  trigger = my_table_insert_trig, new table = (1,AAA), (2,BBB)
3098-- mixture of inserts and updates
3099insert into my_table values (1, 'AAA'), (2, 'BBB'), (3, 'CCC'), (4, 'DDD')
3100  on conflict (a) do
3101  update set b = my_table.b || ':' || excluded.b;
3102NOTICE:  trigger = my_table_update_trig, old table = (1,AAA), (2,BBB), new table = (1,AAA:AAA), (2,BBB:BBB)
3103NOTICE:  trigger = my_table_insert_trig, new table = (3,CCC), (4,DDD)
3104-- updates only
3105insert into my_table values (3, 'CCC'), (4, 'DDD')
3106  on conflict (a) do
3107  update set b = my_table.b || ':' || excluded.b;
3108NOTICE:  trigger = my_table_update_trig, old table = (3,CCC), (4,DDD), new table = (3,CCC:CCC), (4,DDD:DDD)
3109NOTICE:  trigger = my_table_insert_trig, new table = <NULL>
3110--
3111-- now using a partitioned table
3112--
3113create table iocdu_tt_parted (a int primary key, b text) partition by list (a);
3114create table iocdu_tt_parted1 partition of iocdu_tt_parted for values in (1);
3115create table iocdu_tt_parted2 partition of iocdu_tt_parted for values in (2);
3116create table iocdu_tt_parted3 partition of iocdu_tt_parted for values in (3);
3117create table iocdu_tt_parted4 partition of iocdu_tt_parted for values in (4);
3118create trigger iocdu_tt_parted_insert_trig
3119  after insert on iocdu_tt_parted referencing new table as new_table
3120  for each statement execute procedure dump_insert();
3121create trigger iocdu_tt_parted_update_trig
3122  after update on iocdu_tt_parted referencing old table as old_table new table as new_table
3123  for each statement execute procedure dump_update();
3124-- inserts only
3125insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB')
3126  on conflict (a) do
3127  update set b = iocdu_tt_parted.b || ':' || excluded.b;
3128NOTICE:  trigger = iocdu_tt_parted_update_trig, old table = <NULL>, new table = <NULL>
3129NOTICE:  trigger = iocdu_tt_parted_insert_trig, new table = (1,AAA), (2,BBB)
3130-- mixture of inserts and updates
3131insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB'), (3, 'CCC'), (4, 'DDD')
3132  on conflict (a) do
3133  update set b = iocdu_tt_parted.b || ':' || excluded.b;
3134NOTICE:  trigger = iocdu_tt_parted_update_trig, old table = (1,AAA), (2,BBB), new table = (1,AAA:AAA), (2,BBB:BBB)
3135NOTICE:  trigger = iocdu_tt_parted_insert_trig, new table = (3,CCC), (4,DDD)
3136-- updates only
3137insert into iocdu_tt_parted values (3, 'CCC'), (4, 'DDD')
3138  on conflict (a) do
3139  update set b = iocdu_tt_parted.b || ':' || excluded.b;
3140NOTICE:  trigger = iocdu_tt_parted_update_trig, old table = (3,CCC), (4,DDD), new table = (3,CCC:CCC), (4,DDD:DDD)
3141NOTICE:  trigger = iocdu_tt_parted_insert_trig, new table = <NULL>
3142drop table iocdu_tt_parted;
3143--
3144-- Verify that you can't create a trigger with transition tables for
3145-- more than one event.
3146--
3147create trigger my_table_multievent_trig
3148  after insert or update on my_table referencing new table as new_table
3149  for each statement execute procedure dump_insert();
3150ERROR:  transition tables cannot be specified for triggers with more than one event
3151--
3152-- Verify that you can't create a trigger with transition tables with
3153-- a column list.
3154--
3155create trigger my_table_col_update_trig
3156  after update of b on my_table referencing new table as new_table
3157  for each statement execute procedure dump_insert();
3158ERROR:  transition tables cannot be specified for triggers with column lists
3159drop table my_table;
3160--
3161-- Test firing of triggers with transition tables by foreign key cascades
3162--
3163create table refd_table (a int primary key, b text);
3164create table trig_table (a int, b text,
3165  foreign key (a) references refd_table on update cascade on delete cascade
3166);
3167create trigger trig_table_before_trig
3168  before insert or update or delete on trig_table
3169  for each statement execute procedure trigger_func('trig_table');
3170create trigger trig_table_insert_trig
3171  after insert on trig_table referencing new table as new_table
3172  for each statement execute procedure dump_insert();
3173create trigger trig_table_update_trig
3174  after update on trig_table referencing old table as old_table new table as new_table
3175  for each statement execute procedure dump_update();
3176create trigger trig_table_delete_trig
3177  after delete on trig_table referencing old table as old_table
3178  for each statement execute procedure dump_delete();
3179insert into refd_table values
3180  (1, 'one'),
3181  (2, 'two'),
3182  (3, 'three');
3183insert into trig_table values
3184  (1, 'one a'),
3185  (1, 'one b'),
3186  (2, 'two a'),
3187  (2, 'two b'),
3188  (3, 'three a'),
3189  (3, 'three b');
3190NOTICE:  trigger_func(trig_table) called: action = INSERT, when = BEFORE, level = STATEMENT
3191NOTICE:  trigger = trig_table_insert_trig, new table = (1,"one a"), (1,"one b"), (2,"two a"), (2,"two b"), (3,"three a"), (3,"three b")
3192update refd_table set a = 11 where b = 'one';
3193NOTICE:  trigger_func(trig_table) called: action = UPDATE, when = BEFORE, level = STATEMENT
3194NOTICE:  trigger = trig_table_update_trig, old table = (1,"one a"), (1,"one b"), new table = (11,"one a"), (11,"one b")
3195select * from trig_table;
3196 a  |    b
3197----+---------
3198  2 | two a
3199  2 | two b
3200  3 | three a
3201  3 | three b
3202 11 | one a
3203 11 | one b
3204(6 rows)
3205
3206delete from refd_table where length(b) = 3;
3207NOTICE:  trigger_func(trig_table) called: action = DELETE, when = BEFORE, level = STATEMENT
3208NOTICE:  trigger = trig_table_delete_trig, old table = (2,"two a"), (2,"two b"), (11,"one a"), (11,"one b")
3209select * from trig_table;
3210 a |    b
3211---+---------
3212 3 | three a
3213 3 | three b
3214(2 rows)
3215
3216drop table refd_table, trig_table;
3217--
3218-- self-referential FKs are even more fun
3219--
3220create table self_ref (a int primary key,
3221                       b int references self_ref(a) on delete cascade);
3222create trigger self_ref_before_trig
3223  before delete on self_ref
3224  for each statement execute procedure trigger_func('self_ref');
3225create trigger self_ref_r_trig
3226  after delete on self_ref referencing old table as old_table
3227  for each row execute procedure dump_delete();
3228create trigger self_ref_s_trig
3229  after delete on self_ref referencing old table as old_table
3230  for each statement execute procedure dump_delete();
3231insert into self_ref values (1, null), (2, 1), (3, 2);
3232delete from self_ref where a = 1;
3233NOTICE:  trigger_func(self_ref) called: action = DELETE, when = BEFORE, level = STATEMENT
3234NOTICE:  trigger = self_ref_r_trig, old table = (1,), (2,1)
3235NOTICE:  trigger_func(self_ref) called: action = DELETE, when = BEFORE, level = STATEMENT
3236NOTICE:  trigger = self_ref_r_trig, old table = (1,), (2,1)
3237NOTICE:  trigger = self_ref_s_trig, old table = (1,), (2,1)
3238NOTICE:  trigger = self_ref_r_trig, old table = (3,2)
3239NOTICE:  trigger = self_ref_s_trig, old table = (3,2)
3240-- without AR trigger, cascaded deletes all end up in one transition table
3241drop trigger self_ref_r_trig on self_ref;
3242insert into self_ref values (1, null), (2, 1), (3, 2), (4, 3);
3243delete from self_ref where a = 1;
3244NOTICE:  trigger_func(self_ref) called: action = DELETE, when = BEFORE, level = STATEMENT
3245NOTICE:  trigger = self_ref_s_trig, old table = (1,), (2,1), (3,2), (4,3)
3246drop table self_ref;
3247-- cleanup
3248drop function dump_insert();
3249drop function dump_update();
3250drop function dump_delete();
3251--
3252-- Tests for CREATE OR REPLACE TRIGGER
3253--
3254create table my_table (id integer);
3255create function funcA() returns trigger as $$
3256begin
3257  raise notice 'hello from funcA';
3258  return null;
3259end; $$ language plpgsql;
3260create function funcB() returns trigger as $$
3261begin
3262  raise notice 'hello from funcB';
3263  return null;
3264end; $$ language plpgsql;
3265create trigger my_trig
3266  after insert on my_table
3267  for each row execute procedure funcA();
3268create trigger my_trig
3269  before insert on my_table
3270  for each row execute procedure funcB();  -- should fail
3271ERROR:  trigger "my_trig" for relation "my_table" already exists
3272insert into my_table values (1);
3273NOTICE:  hello from funcA
3274create or replace trigger my_trig
3275  before insert on my_table
3276  for each row execute procedure funcB();  -- OK
3277insert into my_table values (2);  -- this insert should become a no-op
3278NOTICE:  hello from funcB
3279table my_table;
3280 id
3281----
3282  1
3283(1 row)
3284
3285drop table my_table;
3286-- test CREATE OR REPLACE TRIGGER on partition table
3287create table parted_trig (a int) partition by range (a);
3288create table parted_trig_1 partition of parted_trig
3289       for values from (0) to (1000) partition by range (a);
3290create table parted_trig_1_1 partition of parted_trig_1 for values from (0) to (100);
3291create table parted_trig_2 partition of parted_trig for values from (1000) to (2000);
3292create table default_parted_trig partition of parted_trig default;
3293-- test that trigger can be replaced by another one
3294-- at the same level of partition table
3295create or replace trigger my_trig
3296  after insert on parted_trig
3297  for each row execute procedure funcA();
3298insert into parted_trig (a) values (50);
3299NOTICE:  hello from funcA
3300create or replace trigger my_trig
3301  after insert on parted_trig
3302  for each row execute procedure funcB();
3303insert into parted_trig (a) values (50);
3304NOTICE:  hello from funcB
3305-- test that child trigger cannot be replaced directly
3306create or replace trigger my_trig
3307  after insert on parted_trig
3308  for each row execute procedure funcA();
3309insert into parted_trig (a) values (50);
3310NOTICE:  hello from funcA
3311create or replace trigger my_trig
3312  after insert on parted_trig_1
3313  for each row execute procedure funcB();  -- should fail
3314ERROR:  trigger "my_trig" for relation "parted_trig_1" is an internal trigger
3315insert into parted_trig (a) values (50);
3316NOTICE:  hello from funcA
3317drop trigger my_trig on parted_trig;
3318insert into parted_trig (a) values (50);
3319-- test that user trigger can be overwritten by one defined at upper level
3320create trigger my_trig
3321  after insert on parted_trig_1
3322  for each row execute procedure funcA();
3323insert into parted_trig (a) values (50);
3324NOTICE:  hello from funcA
3325create trigger my_trig
3326  after insert on parted_trig
3327  for each row execute procedure funcB();  -- should fail
3328ERROR:  trigger "my_trig" for relation "parted_trig_1" already exists
3329insert into parted_trig (a) values (50);
3330NOTICE:  hello from funcA
3331create or replace trigger my_trig
3332  after insert on parted_trig
3333  for each row execute procedure funcB();
3334insert into parted_trig (a) values (50);
3335NOTICE:  hello from funcB
3336-- cleanup
3337drop table parted_trig;
3338drop function funcA();
3339drop function funcB();
3340-- Leave around some objects for other tests
3341create table trigger_parted (a int primary key) partition by list (a);
3342create function trigger_parted_trigfunc() returns trigger language plpgsql as
3343  $$ begin end; $$;
3344create trigger aft_row after insert or update on trigger_parted
3345  for each row execute function trigger_parted_trigfunc();
3346create table trigger_parted_p1 partition of trigger_parted for values in (1)
3347  partition by list (a);
3348create table trigger_parted_p1_1 partition of trigger_parted_p1 for values in (1);
3349create table trigger_parted_p2 partition of trigger_parted for values in (2)
3350  partition by list (a);
3351create table trigger_parted_p2_2 partition of trigger_parted_p2 for values in (2);
3352alter table only trigger_parted_p2 disable trigger aft_row;
3353alter table trigger_parted_p2_2 enable always trigger aft_row;
3354-- verify transition table conversion slot's lifetime
3355-- https://postgr.es/m/39a71864-b120-5a5c-8cc5-c632b6f16761@amazon.com
3356create table convslot_test_parent (col1 text primary key);
3357create table convslot_test_child (col1 text primary key,
3358	foreign key (col1) references convslot_test_parent(col1) on delete cascade on update cascade
3359);
3360alter table convslot_test_child add column col2 text not null default 'tutu';
3361insert into convslot_test_parent(col1) values ('1');
3362insert into convslot_test_child(col1) values ('1');
3363insert into convslot_test_parent(col1) values ('3');
3364insert into convslot_test_child(col1) values ('3');
3365create or replace function trigger_function1()
3366returns trigger
3367language plpgsql
3368AS $$
3369begin
3370raise notice 'trigger = %, old_table = %',
3371          TG_NAME,
3372          (select string_agg(old_table::text, ', ' order by col1) from old_table);
3373return null;
3374end; $$;
3375create or replace function trigger_function2()
3376returns trigger
3377language plpgsql
3378AS $$
3379begin
3380raise notice 'trigger = %, new table = %',
3381          TG_NAME,
3382          (select string_agg(new_table::text, ', ' order by col1) from new_table);
3383return null;
3384end; $$;
3385create trigger but_trigger after update on convslot_test_child
3386referencing new table as new_table
3387for each statement execute function trigger_function2();
3388update convslot_test_parent set col1 = col1 || '1';
3389NOTICE:  trigger = but_trigger, new table = (11,tutu), (31,tutu)
3390create or replace function trigger_function3()
3391returns trigger
3392language plpgsql
3393AS $$
3394begin
3395raise notice 'trigger = %, old_table = %, new table = %',
3396          TG_NAME,
3397          (select string_agg(old_table::text, ', ' order by col1) from old_table),
3398          (select string_agg(new_table::text, ', ' order by col1) from new_table);
3399return null;
3400end; $$;
3401create trigger but_trigger2 after update on convslot_test_child
3402referencing old table as old_table new table as new_table
3403for each statement execute function trigger_function3();
3404update convslot_test_parent set col1 = col1 || '1';
3405NOTICE:  trigger = but_trigger, new table = (111,tutu), (311,tutu)
3406NOTICE:  trigger = but_trigger2, old_table = (11,tutu), (31,tutu), new table = (111,tutu), (311,tutu)
3407create trigger bdt_trigger after delete on convslot_test_child
3408referencing old table as old_table
3409for each statement execute function trigger_function1();
3410delete from convslot_test_parent;
3411NOTICE:  trigger = bdt_trigger, old_table = (111,tutu), (311,tutu)
3412drop table convslot_test_child, convslot_test_parent;
3413