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 procedure 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 procedure 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 99DROP TABLE pkeys; 100DROP TABLE fkeys; 101DROP TABLE fkeys2; 102-- -- I've disabled the funny_dup17 test because the new semantics 103-- -- of AFTER ROW triggers, which get now fired at the end of a 104-- -- query always, cause funny_dup17 to enter an endless loop. 105-- -- 106-- -- Jan 107-- 108-- create table dup17 (x int4); 109-- 110-- create trigger dup17_before 111-- before insert on dup17 112-- for each row 113-- execute procedure 114-- funny_dup17 () 115-- ; 116-- 117-- insert into dup17 values (17); 118-- select count(*) from dup17; 119-- insert into dup17 values (17); 120-- select count(*) from dup17; 121-- 122-- drop trigger dup17_before on dup17; 123-- 124-- create trigger dup17_after 125-- after insert on dup17 126-- for each row 127-- execute procedure 128-- funny_dup17 () 129-- ; 130-- insert into dup17 values (13); 131-- select count(*) from dup17 where x = 13; 132-- insert into dup17 values (13); 133-- select count(*) from dup17 where x = 13; 134-- 135-- DROP TABLE dup17; 136-- Check behavior when trigger returns unmodified trigtuple 137create table trigtest (f1 int, f2 text); 138create trigger trigger_return_old 139 before insert or delete or update on trigtest 140 for each row execute procedure trigger_return_old(); 141insert into trigtest values(1, 'foo'); 142select * from trigtest; 143 f1 | f2 144----+----- 145 1 | foo 146(1 row) 147 148update trigtest set f2 = f2 || 'bar'; 149select * from trigtest; 150 f1 | f2 151----+----- 152 1 | foo 153(1 row) 154 155delete from trigtest; 156select * from trigtest; 157 f1 | f2 158----+---- 159(0 rows) 160 161-- Also check what happens when such a trigger runs before or after others 162create function f1_times_10() returns trigger as 163$$ begin new.f1 := new.f1 * 10; return new; end $$ language plpgsql; 164create trigger trigger_alpha 165 before insert or update on trigtest 166 for each row execute procedure f1_times_10(); 167insert into trigtest values(1, 'foo'); 168select * from trigtest; 169 f1 | f2 170----+----- 171 10 | foo 172(1 row) 173 174update trigtest set f2 = f2 || 'bar'; 175select * from trigtest; 176 f1 | f2 177----+----- 178 10 | foo 179(1 row) 180 181delete from trigtest; 182select * from trigtest; 183 f1 | f2 184----+---- 185(0 rows) 186 187create trigger trigger_zed 188 before insert or update on trigtest 189 for each row execute procedure f1_times_10(); 190insert into trigtest values(1, 'foo'); 191select * from trigtest; 192 f1 | f2 193-----+----- 194 100 | foo 195(1 row) 196 197update trigtest set f2 = f2 || 'bar'; 198select * from trigtest; 199 f1 | f2 200------+----- 201 1000 | foo 202(1 row) 203 204delete from trigtest; 205select * from trigtest; 206 f1 | f2 207----+---- 208(0 rows) 209 210drop trigger trigger_alpha on trigtest; 211insert into trigtest values(1, 'foo'); 212select * from trigtest; 213 f1 | f2 214----+----- 215 10 | foo 216(1 row) 217 218update trigtest set f2 = f2 || 'bar'; 219select * from trigtest; 220 f1 | f2 221-----+----- 222 100 | foo 223(1 row) 224 225delete from trigtest; 226select * from trigtest; 227 f1 | f2 228----+---- 229(0 rows) 230 231drop table trigtest; 232create sequence ttdummy_seq increment 10 start 0 minvalue 0; 233create table tttest ( 234 price_id int4, 235 price_val int4, 236 price_on int4, 237 price_off int4 default 999999 238); 239create trigger ttdummy 240 before delete or update on tttest 241 for each row 242 execute procedure 243 ttdummy (price_on, price_off); 244create trigger ttserial 245 before insert or update on tttest 246 for each row 247 execute procedure 248 autoinc (price_on, ttdummy_seq); 249insert into tttest values (1, 1, null); 250insert into tttest values (2, 2, null); 251insert into tttest values (3, 3, 0); 252select * from tttest; 253 price_id | price_val | price_on | price_off 254----------+-----------+----------+----------- 255 1 | 1 | 10 | 999999 256 2 | 2 | 20 | 999999 257 3 | 3 | 30 | 999999 258(3 rows) 259 260delete from tttest where price_id = 2; 261select * from tttest; 262 price_id | price_val | price_on | price_off 263----------+-----------+----------+----------- 264 1 | 1 | 10 | 999999 265 3 | 3 | 30 | 999999 266 2 | 2 | 20 | 40 267(3 rows) 268 269-- what do we see ? 270-- get current prices 271select * from tttest where price_off = 999999; 272 price_id | price_val | price_on | price_off 273----------+-----------+----------+----------- 274 1 | 1 | 10 | 999999 275 3 | 3 | 30 | 999999 276(2 rows) 277 278-- change price for price_id == 3 279update tttest set price_val = 30 where price_id = 3; 280select * from tttest; 281 price_id | price_val | price_on | price_off 282----------+-----------+----------+----------- 283 1 | 1 | 10 | 999999 284 2 | 2 | 20 | 40 285 3 | 30 | 50 | 999999 286 3 | 3 | 30 | 50 287(4 rows) 288 289-- now we want to change pric_id in ALL tuples 290-- this gets us not what we need 291update tttest set price_id = 5 where price_id = 3; 292select * from tttest; 293 price_id | price_val | price_on | price_off 294----------+-----------+----------+----------- 295 1 | 1 | 10 | 999999 296 2 | 2 | 20 | 40 297 3 | 3 | 30 | 50 298 5 | 30 | 60 | 999999 299 3 | 30 | 50 | 60 300(5 rows) 301 302-- restore data as before last update: 303select set_ttdummy(0); 304 set_ttdummy 305------------- 306 1 307(1 row) 308 309delete from tttest where price_id = 5; 310update tttest set price_off = 999999 where price_val = 30; 311select * from tttest; 312 price_id | price_val | price_on | price_off 313----------+-----------+----------+----------- 314 1 | 1 | 10 | 999999 315 2 | 2 | 20 | 40 316 3 | 3 | 30 | 50 317 3 | 30 | 50 | 999999 318(4 rows) 319 320-- and try change price_id now! 321update tttest set price_id = 5 where price_id = 3; 322select * from tttest; 323 price_id | price_val | price_on | price_off 324----------+-----------+----------+----------- 325 1 | 1 | 10 | 999999 326 2 | 2 | 20 | 40 327 5 | 3 | 30 | 50 328 5 | 30 | 50 | 999999 329(4 rows) 330 331-- isn't it what we need ? 332select set_ttdummy(1); 333 set_ttdummy 334------------- 335 0 336(1 row) 337 338-- we want to correct some "date" 339update tttest set price_on = -1 where price_id = 1; 340ERROR: ttdummy (tttest): you cannot change price_on and/or price_off columns (use set_ttdummy) 341-- but this doesn't work 342-- try in this way 343select set_ttdummy(0); 344 set_ttdummy 345------------- 346 1 347(1 row) 348 349update tttest set price_on = -1 where price_id = 1; 350select * from tttest; 351 price_id | price_val | price_on | price_off 352----------+-----------+----------+----------- 353 2 | 2 | 20 | 40 354 5 | 3 | 30 | 50 355 5 | 30 | 50 | 999999 356 1 | 1 | -1 | 999999 357(4 rows) 358 359-- isn't it what we need ? 360-- get price for price_id == 5 as it was @ "date" 35 361select * from tttest where price_on <= 35 and price_off > 35 and price_id = 5; 362 price_id | price_val | price_on | price_off 363----------+-----------+----------+----------- 364 5 | 3 | 30 | 50 365(1 row) 366 367drop table tttest; 368drop sequence ttdummy_seq; 369-- 370-- tests for per-statement triggers 371-- 372CREATE TABLE log_table (tstamp timestamp default timeofday()::timestamp); 373CREATE TABLE main_table (a int unique, b int); 374COPY main_table (a,b) FROM stdin; 375CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS ' 376BEGIN 377 RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL; 378 RETURN NULL; 379END;'; 380CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table 381FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt'); 382CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table 383FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt'); 384-- 385-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified, 386-- CREATE TRIGGER should default to 'FOR EACH STATEMENT' 387-- 388CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_table 389EXECUTE PROCEDURE trigger_func('after_upd_stmt'); 390-- Both insert and update statement level triggers (before and after) should 391-- fire. Doesn't fire UPDATE before trigger, but only because one isn't 392-- defined. 393INSERT INTO main_table (a, b) VALUES (5, 10) ON CONFLICT (a) 394 DO UPDATE SET b = EXCLUDED.b; 395NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT 396NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT 397NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT 398CREATE TRIGGER after_upd_row_trig AFTER UPDATE ON main_table 399FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_row'); 400INSERT INTO main_table DEFAULT VALUES; 401NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT 402NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT 403UPDATE main_table SET a = a + 1 WHERE b < 30; 404NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW 405NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW 406NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW 407NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW 408NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT 409-- UPDATE that effects zero rows should still call per-statement trigger 410UPDATE main_table SET a = a + 2 WHERE b > 100; 411NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT 412-- constraint now unneeded 413ALTER TABLE main_table DROP CONSTRAINT main_table_a_key; 414-- COPY should fire per-row and per-statement INSERT triggers 415COPY main_table (a, b) FROM stdin; 416NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT 417NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT 418SELECT * FROM main_table ORDER BY a, b; 419 a | b 420----+---- 421 6 | 10 422 21 | 20 423 30 | 40 424 31 | 10 425 50 | 35 426 50 | 60 427 81 | 15 428 | 429(8 rows) 430 431-- 432-- test triggers with WHEN clause 433-- 434CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table 435FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('modified_a'); 436CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table 437FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE PROCEDURE trigger_func('modified_any'); 438CREATE TRIGGER insert_a AFTER INSERT ON main_table 439FOR EACH ROW WHEN (NEW.a = 123) EXECUTE PROCEDURE trigger_func('insert_a'); 440CREATE TRIGGER delete_a AFTER DELETE ON main_table 441FOR EACH ROW WHEN (OLD.a = 123) EXECUTE PROCEDURE trigger_func('delete_a'); 442CREATE TRIGGER insert_when BEFORE INSERT ON main_table 443FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('insert_when'); 444CREATE TRIGGER delete_when AFTER DELETE ON main_table 445FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('delete_when'); 446INSERT INTO main_table (a) VALUES (123), (456); 447NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT 448NOTICE: trigger_func(insert_when) called: action = INSERT, when = BEFORE, level = STATEMENT 449NOTICE: trigger_func(insert_a) called: action = INSERT, when = AFTER, level = ROW 450NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT 451COPY main_table FROM stdin; 452NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT 453NOTICE: trigger_func(insert_when) called: action = INSERT, when = BEFORE, level = STATEMENT 454NOTICE: trigger_func(insert_a) called: action = INSERT, when = AFTER, level = ROW 455NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT 456DELETE FROM main_table WHERE a IN (123, 456); 457NOTICE: trigger_func(delete_a) called: action = DELETE, when = AFTER, level = ROW 458NOTICE: trigger_func(delete_a) called: action = DELETE, when = AFTER, level = ROW 459NOTICE: trigger_func(delete_when) called: action = DELETE, when = AFTER, level = STATEMENT 460UPDATE main_table SET a = 50, b = 60; 461NOTICE: trigger_func(modified_any) called: action = UPDATE, when = BEFORE, level = ROW 462NOTICE: trigger_func(modified_any) called: action = UPDATE, when = BEFORE, level = ROW 463NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW 464NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW 465NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW 466NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW 467NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW 468NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW 469NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT 470SELECT * FROM main_table ORDER BY a, b; 471 a | b 472----+---- 473 6 | 10 474 21 | 20 475 30 | 40 476 31 | 10 477 50 | 35 478 50 | 60 479 81 | 15 480 | 481(8 rows) 482 483SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a'; 484 pg_get_triggerdef 485-------------------------------------------------------------------------------------------------------------------------------------------- 486 CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.a <> new.a) EXECUTE PROCEDURE trigger_func('modified_a') 487(1 row) 488 489SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a'; 490 pg_get_triggerdef 491----------------------------------------------------------------------------------------------------------------------------------------------------- 492 CREATE TRIGGER modified_a BEFORE UPDATE OF a ON public.main_table FOR EACH ROW WHEN ((old.a <> new.a)) EXECUTE PROCEDURE trigger_func('modified_a') 493(1 row) 494 495SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any'; 496 pg_get_triggerdef 497-------------------------------------------------------------------------------------------------------------------------------------------------------------- 498 CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.* IS DISTINCT FROM new.*) EXECUTE PROCEDURE trigger_func('modified_any') 499(1 row) 500 501DROP TRIGGER modified_a ON main_table; 502DROP TRIGGER modified_any ON main_table; 503DROP TRIGGER insert_a ON main_table; 504DROP TRIGGER delete_a ON main_table; 505DROP TRIGGER insert_when ON main_table; 506DROP TRIGGER delete_when ON main_table; 507-- Test column-level triggers 508DROP TRIGGER after_upd_row_trig ON main_table; 509CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON main_table 510FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_a_row'); 511CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON main_table 512FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_b_row'); 513CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON main_table 514FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_a_b_row'); 515CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON main_table 516FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_upd_a_stmt'); 517CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON main_table 518FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_upd_b_stmt'); 519SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig'; 520 pg_get_triggerdef 521-------------------------------------------------------------------------------------------------------------------------------------------------- 522 CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON public.main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_a_b_row') 523(1 row) 524 525UPDATE main_table SET a = 50; 526NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT 527NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW 528NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW 529NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW 530NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW 531NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW 532NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW 533NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW 534NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW 535NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT 536UPDATE main_table SET b = 10; 537NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW 538NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW 539NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW 540NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW 541NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW 542NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW 543NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW 544NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW 545NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW 546NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW 547NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW 548NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW 549NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW 550NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW 551NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW 552NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW 553NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT 554NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT 555-- 556-- Test case for bug with BEFORE trigger followed by AFTER trigger with WHEN 557-- 558CREATE TABLE some_t (some_col boolean NOT NULL); 559CREATE FUNCTION dummy_update_func() RETURNS trigger AS $$ 560BEGIN 561 RAISE NOTICE 'dummy_update_func(%) called: action = %, old = %, new = %', 562 TG_ARGV[0], TG_OP, OLD, NEW; 563 RETURN NEW; 564END; 565$$ LANGUAGE plpgsql; 566CREATE TRIGGER some_trig_before BEFORE UPDATE ON some_t FOR EACH ROW 567 EXECUTE PROCEDURE dummy_update_func('before'); 568CREATE TRIGGER some_trig_aftera AFTER UPDATE ON some_t FOR EACH ROW 569 WHEN (NOT OLD.some_col AND NEW.some_col) 570 EXECUTE PROCEDURE dummy_update_func('aftera'); 571CREATE TRIGGER some_trig_afterb AFTER UPDATE ON some_t FOR EACH ROW 572 WHEN (NOT NEW.some_col) 573 EXECUTE PROCEDURE dummy_update_func('afterb'); 574INSERT INTO some_t VALUES (TRUE); 575UPDATE some_t SET some_col = TRUE; 576NOTICE: dummy_update_func(before) called: action = UPDATE, old = (t), new = (t) 577UPDATE some_t SET some_col = FALSE; 578NOTICE: dummy_update_func(before) called: action = UPDATE, old = (t), new = (f) 579NOTICE: dummy_update_func(afterb) called: action = UPDATE, old = (t), new = (f) 580UPDATE some_t SET some_col = TRUE; 581NOTICE: dummy_update_func(before) called: action = UPDATE, old = (f), new = (t) 582NOTICE: dummy_update_func(aftera) called: action = UPDATE, old = (f), new = (t) 583DROP TABLE some_t; 584-- bogus cases 585CREATE TRIGGER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_table 586FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_and_col'); 587ERROR: duplicate trigger events specified at or near "ON" 588LINE 1: ...ER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_ta... 589 ^ 590CREATE TRIGGER error_upd_a_a BEFORE UPDATE OF a, a ON main_table 591FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_a_a'); 592ERROR: column "a" specified more than once 593CREATE TRIGGER error_ins_a BEFORE INSERT OF a ON main_table 594FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_ins_a'); 595ERROR: syntax error at or near "OF" 596LINE 1: CREATE TRIGGER error_ins_a BEFORE INSERT OF a ON main_table 597 ^ 598CREATE TRIGGER error_ins_when BEFORE INSERT OR UPDATE ON main_table 599FOR EACH ROW WHEN (OLD.a <> NEW.a) 600EXECUTE PROCEDURE trigger_func('error_ins_old'); 601ERROR: INSERT trigger's WHEN condition cannot reference OLD values 602LINE 2: FOR EACH ROW WHEN (OLD.a <> NEW.a) 603 ^ 604CREATE TRIGGER error_del_when BEFORE DELETE OR UPDATE ON main_table 605FOR EACH ROW WHEN (OLD.a <> NEW.a) 606EXECUTE PROCEDURE trigger_func('error_del_new'); 607ERROR: DELETE trigger's WHEN condition cannot reference NEW values 608LINE 2: FOR EACH ROW WHEN (OLD.a <> NEW.a) 609 ^ 610CREATE TRIGGER error_del_when BEFORE INSERT OR UPDATE ON main_table 611FOR EACH ROW WHEN (NEW.tableoid <> 0) 612EXECUTE PROCEDURE trigger_func('error_when_sys_column'); 613ERROR: BEFORE trigger's WHEN condition cannot reference NEW system columns 614LINE 2: FOR EACH ROW WHEN (NEW.tableoid <> 0) 615 ^ 616CREATE TRIGGER error_stmt_when BEFORE UPDATE OF a ON main_table 617FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*) 618EXECUTE PROCEDURE trigger_func('error_stmt_when'); 619ERROR: statement trigger's WHEN condition cannot reference column values 620LINE 2: FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*) 621 ^ 622-- check dependency restrictions 623ALTER TABLE main_table DROP COLUMN b; 624ERROR: cannot drop table main_table column b because other objects depend on it 625DETAIL: trigger after_upd_b_row_trig on table main_table depends on table main_table column b 626trigger after_upd_a_b_row_trig on table main_table depends on table main_table column b 627trigger after_upd_b_stmt_trig on table main_table depends on table main_table column b 628HINT: Use DROP ... CASCADE to drop the dependent objects too. 629-- this should succeed, but we'll roll it back to keep the triggers around 630begin; 631DROP TRIGGER after_upd_a_b_row_trig ON main_table; 632DROP TRIGGER after_upd_b_row_trig ON main_table; 633DROP TRIGGER after_upd_b_stmt_trig ON main_table; 634ALTER TABLE main_table DROP COLUMN b; 635rollback; 636-- Test enable/disable triggers 637create table trigtest (i serial primary key); 638-- test that disabling RI triggers works 639create table trigtest2 (i int references trigtest(i) on delete cascade); 640create function trigtest() returns trigger as $$ 641begin 642 raise notice '% % % %', TG_RELNAME, TG_OP, TG_WHEN, TG_LEVEL; 643 return new; 644end;$$ language plpgsql; 645create trigger trigtest_b_row_tg before insert or update or delete on trigtest 646for each row execute procedure trigtest(); 647create trigger trigtest_a_row_tg after insert or update or delete on trigtest 648for each row execute procedure trigtest(); 649create trigger trigtest_b_stmt_tg before insert or update or delete on trigtest 650for each statement execute procedure trigtest(); 651create trigger trigtest_a_stmt_tg after insert or update or delete on trigtest 652for each statement execute procedure trigtest(); 653insert into trigtest default values; 654NOTICE: trigtest INSERT BEFORE STATEMENT 655NOTICE: trigtest INSERT BEFORE ROW 656NOTICE: trigtest INSERT AFTER ROW 657NOTICE: trigtest INSERT AFTER STATEMENT 658alter table trigtest disable trigger trigtest_b_row_tg; 659insert into trigtest default values; 660NOTICE: trigtest INSERT BEFORE STATEMENT 661NOTICE: trigtest INSERT AFTER ROW 662NOTICE: trigtest INSERT AFTER STATEMENT 663alter table trigtest disable trigger user; 664insert into trigtest default values; 665alter table trigtest enable trigger trigtest_a_stmt_tg; 666insert into trigtest default values; 667NOTICE: trigtest INSERT AFTER STATEMENT 668insert into trigtest2 values(1); 669insert into trigtest2 values(2); 670delete from trigtest where i=2; 671NOTICE: trigtest DELETE AFTER STATEMENT 672select * from trigtest2; 673 i 674--- 675 1 676(1 row) 677 678alter table trigtest disable trigger all; 679delete from trigtest where i=1; 680select * from trigtest2; 681 i 682--- 683 1 684(1 row) 685 686-- ensure we still insert, even when all triggers are disabled 687insert into trigtest default values; 688select * from trigtest; 689 i 690--- 691 3 692 4 693 5 694(3 rows) 695 696drop table trigtest2; 697drop table trigtest; 698-- dump trigger data 699CREATE TABLE trigger_test ( 700 i int, 701 v varchar 702); 703CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger 704LANGUAGE plpgsql AS $$ 705 706declare 707 708 argstr text; 709 relid text; 710 711begin 712 713 relid := TG_relid::regclass; 714 715 -- plpgsql can't discover its trigger data in a hash like perl and python 716 -- can, or by a sort of reflection like tcl can, 717 -- so we have to hard code the names. 718 raise NOTICE 'TG_NAME: %', TG_name; 719 raise NOTICE 'TG_WHEN: %', TG_when; 720 raise NOTICE 'TG_LEVEL: %', TG_level; 721 raise NOTICE 'TG_OP: %', TG_op; 722 raise NOTICE 'TG_RELID::regclass: %', relid; 723 raise NOTICE 'TG_RELNAME: %', TG_relname; 724 raise NOTICE 'TG_TABLE_NAME: %', TG_table_name; 725 raise NOTICE 'TG_TABLE_SCHEMA: %', TG_table_schema; 726 raise NOTICE 'TG_NARGS: %', TG_nargs; 727 728 argstr := '['; 729 for i in 0 .. TG_nargs - 1 loop 730 if i > 0 then 731 argstr := argstr || ', '; 732 end if; 733 argstr := argstr || TG_argv[i]; 734 end loop; 735 argstr := argstr || ']'; 736 raise NOTICE 'TG_ARGV: %', argstr; 737 738 if TG_OP != 'INSERT' then 739 raise NOTICE 'OLD: %', OLD; 740 end if; 741 742 if TG_OP != 'DELETE' then 743 raise NOTICE 'NEW: %', NEW; 744 end if; 745 746 if TG_OP = 'DELETE' then 747 return OLD; 748 else 749 return NEW; 750 end if; 751 752end; 753$$; 754CREATE TRIGGER show_trigger_data_trig 755BEFORE INSERT OR UPDATE OR DELETE ON trigger_test 756FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 757insert into trigger_test values(1,'insert'); 758NOTICE: TG_NAME: show_trigger_data_trig 759NOTICE: TG_WHEN: BEFORE 760NOTICE: TG_LEVEL: ROW 761NOTICE: TG_OP: INSERT 762NOTICE: TG_RELID::regclass: trigger_test 763NOTICE: TG_RELNAME: trigger_test 764NOTICE: TG_TABLE_NAME: trigger_test 765NOTICE: TG_TABLE_SCHEMA: public 766NOTICE: TG_NARGS: 2 767NOTICE: TG_ARGV: [23, skidoo] 768NOTICE: NEW: (1,insert) 769update trigger_test set v = 'update' where i = 1; 770NOTICE: TG_NAME: show_trigger_data_trig 771NOTICE: TG_WHEN: BEFORE 772NOTICE: TG_LEVEL: ROW 773NOTICE: TG_OP: UPDATE 774NOTICE: TG_RELID::regclass: trigger_test 775NOTICE: TG_RELNAME: trigger_test 776NOTICE: TG_TABLE_NAME: trigger_test 777NOTICE: TG_TABLE_SCHEMA: public 778NOTICE: TG_NARGS: 2 779NOTICE: TG_ARGV: [23, skidoo] 780NOTICE: OLD: (1,insert) 781NOTICE: NEW: (1,update) 782delete from trigger_test; 783NOTICE: TG_NAME: show_trigger_data_trig 784NOTICE: TG_WHEN: BEFORE 785NOTICE: TG_LEVEL: ROW 786NOTICE: TG_OP: DELETE 787NOTICE: TG_RELID::regclass: trigger_test 788NOTICE: TG_RELNAME: trigger_test 789NOTICE: TG_TABLE_NAME: trigger_test 790NOTICE: TG_TABLE_SCHEMA: public 791NOTICE: TG_NARGS: 2 792NOTICE: TG_ARGV: [23, skidoo] 793NOTICE: OLD: (1,update) 794DROP TRIGGER show_trigger_data_trig on trigger_test; 795DROP FUNCTION trigger_data(); 796DROP TABLE trigger_test; 797-- 798-- Test use of row comparisons on OLD/NEW 799-- 800CREATE TABLE trigger_test (f1 int, f2 text, f3 text); 801-- this is the obvious (and wrong...) way to compare rows 802CREATE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$ 803begin 804 if row(old.*) = row(new.*) then 805 raise notice 'row % not changed', new.f1; 806 else 807 raise notice 'row % changed', new.f1; 808 end if; 809 return new; 810end$$; 811CREATE TRIGGER t 812BEFORE UPDATE ON trigger_test 813FOR EACH ROW EXECUTE PROCEDURE mytrigger(); 814INSERT INTO trigger_test VALUES(1, 'foo', 'bar'); 815INSERT INTO trigger_test VALUES(2, 'baz', 'quux'); 816UPDATE trigger_test SET f3 = 'bar'; 817NOTICE: row 1 not changed 818NOTICE: row 2 changed 819UPDATE trigger_test SET f3 = NULL; 820NOTICE: row 1 changed 821NOTICE: row 2 changed 822-- this demonstrates that the above isn't really working as desired: 823UPDATE trigger_test SET f3 = NULL; 824NOTICE: row 1 changed 825NOTICE: row 2 changed 826-- the right way when considering nulls is 827CREATE OR REPLACE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$ 828begin 829 if row(old.*) is distinct from row(new.*) then 830 raise notice 'row % changed', new.f1; 831 else 832 raise notice 'row % not changed', new.f1; 833 end if; 834 return new; 835end$$; 836UPDATE trigger_test SET f3 = 'bar'; 837NOTICE: row 1 changed 838NOTICE: row 2 changed 839UPDATE trigger_test SET f3 = NULL; 840NOTICE: row 1 changed 841NOTICE: row 2 changed 842UPDATE trigger_test SET f3 = NULL; 843NOTICE: row 1 not changed 844NOTICE: row 2 not changed 845DROP TABLE trigger_test; 846DROP FUNCTION mytrigger(); 847-- Test snapshot management in serializable transactions involving triggers 848-- per bug report in 6bc73d4c0910042358k3d1adff3qa36f8df75198ecea@mail.gmail.com 849CREATE FUNCTION serializable_update_trig() RETURNS trigger LANGUAGE plpgsql AS 850$$ 851declare 852 rec record; 853begin 854 new.description = 'updated in trigger'; 855 return new; 856end; 857$$; 858CREATE TABLE serializable_update_tab ( 859 id int, 860 filler text, 861 description text 862); 863CREATE TRIGGER serializable_update_trig BEFORE UPDATE ON serializable_update_tab 864 FOR EACH ROW EXECUTE PROCEDURE serializable_update_trig(); 865INSERT INTO serializable_update_tab SELECT a, repeat('xyzxz', 100), 'new' 866 FROM generate_series(1, 50) a; 867BEGIN; 868SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 869UPDATE serializable_update_tab SET description = 'no no', id = 1 WHERE id = 1; 870COMMIT; 871SELECT description FROM serializable_update_tab WHERE id = 1; 872 description 873-------------------- 874 updated in trigger 875(1 row) 876 877DROP TABLE serializable_update_tab; 878-- minimal update trigger 879CREATE TABLE min_updates_test ( 880 f1 text, 881 f2 int, 882 f3 int); 883CREATE TABLE min_updates_test_oids ( 884 f1 text, 885 f2 int, 886 f3 int) WITH OIDS; 887INSERT INTO min_updates_test VALUES ('a',1,2),('b','2',null); 888INSERT INTO min_updates_test_oids VALUES ('a',1,2),('b','2',null); 889CREATE TRIGGER z_min_update 890BEFORE UPDATE ON min_updates_test 891FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); 892CREATE TRIGGER z_min_update 893BEFORE UPDATE ON min_updates_test_oids 894FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); 895\set QUIET false 896UPDATE min_updates_test SET f1 = f1; 897UPDATE 0 898UPDATE min_updates_test SET f2 = f2 + 1; 899UPDATE 2 900UPDATE min_updates_test SET f3 = 2 WHERE f3 is null; 901UPDATE 1 902UPDATE min_updates_test_oids SET f1 = f1; 903UPDATE 0 904UPDATE min_updates_test_oids SET f2 = f2 + 1; 905UPDATE 2 906UPDATE min_updates_test_oids SET f3 = 2 WHERE f3 is null; 907UPDATE 1 908\set QUIET true 909SELECT * FROM min_updates_test; 910 f1 | f2 | f3 911----+----+---- 912 a | 2 | 2 913 b | 3 | 2 914(2 rows) 915 916SELECT * FROM min_updates_test_oids; 917 f1 | f2 | f3 918----+----+---- 919 a | 2 | 2 920 b | 3 | 2 921(2 rows) 922 923DROP TABLE min_updates_test; 924DROP TABLE min_updates_test_oids; 925-- 926-- Test triggers on views 927-- 928CREATE VIEW main_view AS SELECT a, b FROM main_table; 929-- VIEW trigger function 930CREATE OR REPLACE FUNCTION view_trigger() RETURNS trigger 931LANGUAGE plpgsql AS $$ 932declare 933 argstr text := ''; 934begin 935 for i in 0 .. TG_nargs - 1 loop 936 if i > 0 then 937 argstr := argstr || ', '; 938 end if; 939 argstr := argstr || TG_argv[i]; 940 end loop; 941 942 raise notice '% % % % (%)', TG_RELNAME, TG_WHEN, TG_OP, TG_LEVEL, argstr; 943 944 if TG_LEVEL = 'ROW' then 945 if TG_OP = 'INSERT' then 946 raise NOTICE 'NEW: %', NEW; 947 INSERT INTO main_table VALUES (NEW.a, NEW.b); 948 RETURN NEW; 949 end if; 950 951 if TG_OP = 'UPDATE' then 952 raise NOTICE 'OLD: %, NEW: %', OLD, NEW; 953 UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b; 954 if NOT FOUND then RETURN NULL; end if; 955 RETURN NEW; 956 end if; 957 958 if TG_OP = 'DELETE' then 959 raise NOTICE 'OLD: %', OLD; 960 DELETE FROM main_table WHERE a = OLD.a AND b = OLD.b; 961 if NOT FOUND then RETURN NULL; end if; 962 RETURN OLD; 963 end if; 964 end if; 965 966 RETURN NULL; 967end; 968$$; 969-- Before row triggers aren't allowed on views 970CREATE TRIGGER invalid_trig BEFORE INSERT ON main_view 971FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row'); 972ERROR: "main_view" is a view 973DETAIL: Views cannot have row-level BEFORE or AFTER triggers. 974CREATE TRIGGER invalid_trig BEFORE UPDATE ON main_view 975FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row'); 976ERROR: "main_view" is a view 977DETAIL: Views cannot have row-level BEFORE or AFTER triggers. 978CREATE TRIGGER invalid_trig BEFORE DELETE ON main_view 979FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row'); 980ERROR: "main_view" is a view 981DETAIL: Views cannot have row-level BEFORE or AFTER triggers. 982-- After row triggers aren't allowed on views 983CREATE TRIGGER invalid_trig AFTER INSERT ON main_view 984FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row'); 985ERROR: "main_view" is a view 986DETAIL: Views cannot have row-level BEFORE or AFTER triggers. 987CREATE TRIGGER invalid_trig AFTER UPDATE ON main_view 988FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row'); 989ERROR: "main_view" is a view 990DETAIL: Views cannot have row-level BEFORE or AFTER triggers. 991CREATE TRIGGER invalid_trig AFTER DELETE ON main_view 992FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row'); 993ERROR: "main_view" is a view 994DETAIL: Views cannot have row-level BEFORE or AFTER triggers. 995-- Truncate triggers aren't allowed on views 996CREATE TRIGGER invalid_trig BEFORE TRUNCATE ON main_view 997EXECUTE PROCEDURE trigger_func('before_tru_row'); 998ERROR: "main_view" is a view 999DETAIL: Views cannot have TRUNCATE triggers. 1000CREATE TRIGGER invalid_trig AFTER TRUNCATE ON main_view 1001EXECUTE PROCEDURE trigger_func('before_tru_row'); 1002ERROR: "main_view" is a view 1003DETAIL: Views cannot have TRUNCATE triggers. 1004-- INSTEAD OF triggers aren't allowed on tables 1005CREATE TRIGGER invalid_trig INSTEAD OF INSERT ON main_table 1006FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins'); 1007ERROR: "main_table" is a table 1008DETAIL: Tables cannot have INSTEAD OF triggers. 1009CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_table 1010FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd'); 1011ERROR: "main_table" is a table 1012DETAIL: Tables cannot have INSTEAD OF triggers. 1013CREATE TRIGGER invalid_trig INSTEAD OF DELETE ON main_table 1014FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del'); 1015ERROR: "main_table" is a table 1016DETAIL: Tables cannot have INSTEAD OF triggers. 1017-- Don't support WHEN clauses with INSTEAD OF triggers 1018CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view 1019FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE view_trigger('instead_of_upd'); 1020ERROR: INSTEAD OF triggers cannot have WHEN conditions 1021-- Don't support column-level INSTEAD OF triggers 1022CREATE TRIGGER invalid_trig INSTEAD OF UPDATE OF a ON main_view 1023FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd'); 1024ERROR: INSTEAD OF triggers cannot have column lists 1025-- Don't support statement-level INSTEAD OF triggers 1026CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view 1027EXECUTE PROCEDURE view_trigger('instead_of_upd'); 1028ERROR: INSTEAD OF triggers must be FOR EACH ROW 1029-- Valid INSTEAD OF triggers 1030CREATE TRIGGER instead_of_insert_trig INSTEAD OF INSERT ON main_view 1031FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins'); 1032CREATE TRIGGER instead_of_update_trig INSTEAD OF UPDATE ON main_view 1033FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd'); 1034CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view 1035FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del'); 1036-- Valid BEFORE statement VIEW triggers 1037CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view 1038FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt'); 1039CREATE TRIGGER before_upd_stmt_trig BEFORE UPDATE ON main_view 1040FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt'); 1041CREATE TRIGGER before_del_stmt_trig BEFORE DELETE ON main_view 1042FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt'); 1043-- Valid AFTER statement VIEW triggers 1044CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_view 1045FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt'); 1046CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_view 1047FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt'); 1048CREATE TRIGGER after_del_stmt_trig AFTER DELETE ON main_view 1049FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt'); 1050\set QUIET false 1051-- Insert into view using trigger 1052INSERT INTO main_view VALUES (20, 30); 1053NOTICE: main_view BEFORE INSERT STATEMENT (before_view_ins_stmt) 1054NOTICE: main_view INSTEAD OF INSERT ROW (instead_of_ins) 1055NOTICE: NEW: (20,30) 1056NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT 1057NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT 1058NOTICE: main_view AFTER INSERT STATEMENT (after_view_ins_stmt) 1059INSERT 0 1 1060INSERT INTO main_view VALUES (21, 31) RETURNING a, b; 1061NOTICE: main_view BEFORE INSERT STATEMENT (before_view_ins_stmt) 1062NOTICE: main_view INSTEAD OF INSERT ROW (instead_of_ins) 1063NOTICE: NEW: (21,31) 1064NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT 1065NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT 1066NOTICE: main_view AFTER INSERT STATEMENT (after_view_ins_stmt) 1067 a | b 1068----+---- 1069 21 | 31 1070(1 row) 1071 1072INSERT 0 1 1073-- Table trigger will prevent updates 1074UPDATE main_view SET b = 31 WHERE a = 20; 1075NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) 1076NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd) 1077NOTICE: OLD: (20,30), NEW: (20,31) 1078NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT 1079NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW 1080NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT 1081NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT 1082NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) 1083UPDATE 0 1084UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b; 1085NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) 1086NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd) 1087NOTICE: OLD: (21,31), NEW: (21,32) 1088NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT 1089NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW 1090NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT 1091NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT 1092NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) 1093 a | b 1094---+--- 1095(0 rows) 1096 1097UPDATE 0 1098-- Remove table trigger to allow updates 1099DROP TRIGGER before_upd_a_row_trig ON main_table; 1100DROP TRIGGER 1101UPDATE main_view SET b = 31 WHERE a = 20; 1102NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) 1103NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd) 1104NOTICE: OLD: (20,30), NEW: (20,31) 1105NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT 1106NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW 1107NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW 1108NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT 1109NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT 1110NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) 1111UPDATE 1 1112UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b; 1113NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) 1114NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd) 1115NOTICE: OLD: (21,31), NEW: (21,32) 1116NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT 1117NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW 1118NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW 1119NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT 1120NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT 1121NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) 1122 a | b 1123----+---- 1124 21 | 32 1125(1 row) 1126 1127UPDATE 1 1128-- Before and after stmt triggers should fire even when no rows are affected 1129UPDATE main_view SET b = 0 WHERE false; 1130NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) 1131NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) 1132UPDATE 0 1133-- Delete from view using trigger 1134DELETE FROM main_view WHERE a IN (20,21); 1135NOTICE: main_view BEFORE DELETE STATEMENT (before_view_del_stmt) 1136NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del) 1137NOTICE: OLD: (21,10) 1138NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del) 1139NOTICE: OLD: (20,31) 1140NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del) 1141NOTICE: OLD: (21,32) 1142NOTICE: main_view AFTER DELETE STATEMENT (after_view_del_stmt) 1143DELETE 3 1144DELETE FROM main_view WHERE a = 31 RETURNING a, b; 1145NOTICE: main_view BEFORE DELETE STATEMENT (before_view_del_stmt) 1146NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del) 1147NOTICE: OLD: (31,10) 1148NOTICE: main_view AFTER DELETE STATEMENT (after_view_del_stmt) 1149 a | b 1150----+---- 1151 31 | 10 1152(1 row) 1153 1154DELETE 1 1155\set QUIET true 1156-- Describe view should list triggers 1157\d main_view 1158 View "public.main_view" 1159 Column | Type | Collation | Nullable | Default 1160--------+---------+-----------+----------+--------- 1161 a | integer | | | 1162 b | integer | | | 1163Triggers: 1164 after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt') 1165 after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt') 1166 after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt') 1167 before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt') 1168 before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt') 1169 before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt') 1170 instead_of_delete_trig INSTEAD OF DELETE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del') 1171 instead_of_insert_trig INSTEAD OF INSERT ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins') 1172 instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd') 1173 1174-- Test dropping view triggers 1175DROP TRIGGER instead_of_insert_trig ON main_view; 1176DROP TRIGGER instead_of_delete_trig ON main_view; 1177\d+ main_view 1178 View "public.main_view" 1179 Column | Type | Collation | Nullable | Default | Storage | Description 1180--------+---------+-----------+----------+---------+---------+------------- 1181 a | integer | | | | plain | 1182 b | integer | | | | plain | 1183View definition: 1184 SELECT main_table.a, 1185 main_table.b 1186 FROM main_table; 1187Triggers: 1188 after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt') 1189 after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt') 1190 after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt') 1191 before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt') 1192 before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt') 1193 before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt') 1194 instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd') 1195 1196DROP VIEW main_view; 1197-- 1198-- Test triggers on a join view 1199-- 1200CREATE TABLE country_table ( 1201 country_id serial primary key, 1202 country_name text unique not null, 1203 continent text not null 1204); 1205INSERT INTO country_table (country_name, continent) 1206 VALUES ('Japan', 'Asia'), 1207 ('UK', 'Europe'), 1208 ('USA', 'North America') 1209 RETURNING *; 1210 country_id | country_name | continent 1211------------+--------------+--------------- 1212 1 | Japan | Asia 1213 2 | UK | Europe 1214 3 | USA | North America 1215(3 rows) 1216 1217CREATE TABLE city_table ( 1218 city_id serial primary key, 1219 city_name text not null, 1220 population bigint, 1221 country_id int references country_table 1222); 1223CREATE VIEW city_view AS 1224 SELECT city_id, city_name, population, country_name, continent 1225 FROM city_table ci 1226 LEFT JOIN country_table co ON co.country_id = ci.country_id; 1227CREATE FUNCTION city_insert() RETURNS trigger LANGUAGE plpgsql AS $$ 1228declare 1229 ctry_id int; 1230begin 1231 if NEW.country_name IS NOT NULL then 1232 SELECT country_id, continent INTO ctry_id, NEW.continent 1233 FROM country_table WHERE country_name = NEW.country_name; 1234 if NOT FOUND then 1235 raise exception 'No such country: "%"', NEW.country_name; 1236 end if; 1237 else 1238 NEW.continent := NULL; 1239 end if; 1240 1241 if NEW.city_id IS NOT NULL then 1242 INSERT INTO city_table 1243 VALUES(NEW.city_id, NEW.city_name, NEW.population, ctry_id); 1244 else 1245 INSERT INTO city_table(city_name, population, country_id) 1246 VALUES(NEW.city_name, NEW.population, ctry_id) 1247 RETURNING city_id INTO NEW.city_id; 1248 end if; 1249 1250 RETURN NEW; 1251end; 1252$$; 1253CREATE TRIGGER city_insert_trig INSTEAD OF INSERT ON city_view 1254FOR EACH ROW EXECUTE PROCEDURE city_insert(); 1255CREATE FUNCTION city_delete() RETURNS trigger LANGUAGE plpgsql AS $$ 1256begin 1257 DELETE FROM city_table WHERE city_id = OLD.city_id; 1258 if NOT FOUND then RETURN NULL; end if; 1259 RETURN OLD; 1260end; 1261$$; 1262CREATE TRIGGER city_delete_trig INSTEAD OF DELETE ON city_view 1263FOR EACH ROW EXECUTE PROCEDURE city_delete(); 1264CREATE FUNCTION city_update() RETURNS trigger LANGUAGE plpgsql AS $$ 1265declare 1266 ctry_id int; 1267begin 1268 if NEW.country_name IS DISTINCT FROM OLD.country_name then 1269 SELECT country_id, continent INTO ctry_id, NEW.continent 1270 FROM country_table WHERE country_name = NEW.country_name; 1271 if NOT FOUND then 1272 raise exception 'No such country: "%"', NEW.country_name; 1273 end if; 1274 1275 UPDATE city_table SET city_name = NEW.city_name, 1276 population = NEW.population, 1277 country_id = ctry_id 1278 WHERE city_id = OLD.city_id; 1279 else 1280 UPDATE city_table SET city_name = NEW.city_name, 1281 population = NEW.population 1282 WHERE city_id = OLD.city_id; 1283 NEW.continent := OLD.continent; 1284 end if; 1285 1286 if NOT FOUND then RETURN NULL; end if; 1287 RETURN NEW; 1288end; 1289$$; 1290CREATE TRIGGER city_update_trig INSTEAD OF UPDATE ON city_view 1291FOR EACH ROW EXECUTE PROCEDURE city_update(); 1292\set QUIET false 1293-- INSERT .. RETURNING 1294INSERT INTO city_view(city_name) VALUES('Tokyo') RETURNING *; 1295 city_id | city_name | population | country_name | continent 1296---------+-----------+------------+--------------+----------- 1297 1 | Tokyo | | | 1298(1 row) 1299 1300INSERT 0 1 1301INSERT INTO city_view(city_name, population) VALUES('London', 7556900) RETURNING *; 1302 city_id | city_name | population | country_name | continent 1303---------+-----------+------------+--------------+----------- 1304 2 | London | 7556900 | | 1305(1 row) 1306 1307INSERT 0 1 1308INSERT INTO city_view(city_name, country_name) VALUES('Washington DC', 'USA') RETURNING *; 1309 city_id | city_name | population | country_name | continent 1310---------+---------------+------------+--------------+--------------- 1311 3 | Washington DC | | USA | North America 1312(1 row) 1313 1314INSERT 0 1 1315INSERT INTO city_view(city_id, city_name) VALUES(123456, 'New York') RETURNING *; 1316 city_id | city_name | population | country_name | continent 1317---------+-----------+------------+--------------+----------- 1318 123456 | New York | | | 1319(1 row) 1320 1321INSERT 0 1 1322INSERT INTO city_view VALUES(234567, 'Birmingham', 1016800, 'UK', 'EU') RETURNING *; 1323 city_id | city_name | population | country_name | continent 1324---------+------------+------------+--------------+----------- 1325 234567 | Birmingham | 1016800 | UK | Europe 1326(1 row) 1327 1328INSERT 0 1 1329-- UPDATE .. RETURNING 1330UPDATE city_view SET country_name = 'Japon' WHERE city_name = 'Tokyo'; -- error 1331ERROR: No such country: "Japon" 1332CONTEXT: PL/pgSQL function city_update() line 9 at RAISE 1333UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Takyo'; -- no match 1334UPDATE 0 1335UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Tokyo' RETURNING *; -- OK 1336 city_id | city_name | population | country_name | continent 1337---------+-----------+------------+--------------+----------- 1338 1 | Tokyo | | Japan | Asia 1339(1 row) 1340 1341UPDATE 1 1342UPDATE city_view SET population = 13010279 WHERE city_name = 'Tokyo' RETURNING *; 1343 city_id | city_name | population | country_name | continent 1344---------+-----------+------------+--------------+----------- 1345 1 | Tokyo | 13010279 | Japan | Asia 1346(1 row) 1347 1348UPDATE 1 1349UPDATE city_view SET country_name = 'UK' WHERE city_name = 'New York' RETURNING *; 1350 city_id | city_name | population | country_name | continent 1351---------+-----------+------------+--------------+----------- 1352 123456 | New York | | UK | Europe 1353(1 row) 1354 1355UPDATE 1 1356UPDATE city_view SET country_name = 'USA', population = 8391881 WHERE city_name = 'New York' RETURNING *; 1357 city_id | city_name | population | country_name | continent 1358---------+-----------+------------+--------------+--------------- 1359 123456 | New York | 8391881 | USA | North America 1360(1 row) 1361 1362UPDATE 1 1363UPDATE city_view SET continent = 'EU' WHERE continent = 'Europe' RETURNING *; 1364 city_id | city_name | population | country_name | continent 1365---------+------------+------------+--------------+----------- 1366 234567 | Birmingham | 1016800 | UK | Europe 1367(1 row) 1368 1369UPDATE 1 1370UPDATE city_view v1 SET country_name = v2.country_name FROM city_view v2 1371 WHERE v2.city_name = 'Birmingham' AND v1.city_name = 'London' RETURNING *; 1372 city_id | city_name | population | country_name | continent | city_id | city_name | population | country_name | continent 1373---------+-----------+------------+--------------+-----------+---------+------------+------------+--------------+----------- 1374 2 | London | 7556900 | UK | Europe | 234567 | Birmingham | 1016800 | UK | Europe 1375(1 row) 1376 1377UPDATE 1 1378-- DELETE .. RETURNING 1379DELETE FROM city_view WHERE city_name = 'Birmingham' RETURNING *; 1380 city_id | city_name | population | country_name | continent 1381---------+------------+------------+--------------+----------- 1382 234567 | Birmingham | 1016800 | UK | Europe 1383(1 row) 1384 1385DELETE 1 1386\set QUIET true 1387-- read-only view with WHERE clause 1388CREATE VIEW european_city_view AS 1389 SELECT * FROM city_view WHERE continent = 'Europe'; 1390SELECT count(*) FROM european_city_view; 1391 count 1392------- 1393 1 1394(1 row) 1395 1396CREATE FUNCTION no_op_trig_fn() RETURNS trigger LANGUAGE plpgsql 1397AS 'begin RETURN NULL; end'; 1398CREATE TRIGGER no_op_trig INSTEAD OF INSERT OR UPDATE OR DELETE 1399ON european_city_view FOR EACH ROW EXECUTE PROCEDURE no_op_trig_fn(); 1400\set QUIET false 1401INSERT INTO european_city_view VALUES (0, 'x', 10000, 'y', 'z'); 1402INSERT 0 0 1403UPDATE european_city_view SET population = 10000; 1404UPDATE 0 1405DELETE FROM european_city_view; 1406DELETE 0 1407\set QUIET true 1408-- rules bypassing no-op triggers 1409CREATE RULE european_city_insert_rule AS ON INSERT TO european_city_view 1410DO INSTEAD INSERT INTO city_view 1411VALUES (NEW.city_id, NEW.city_name, NEW.population, NEW.country_name, NEW.continent) 1412RETURNING *; 1413CREATE RULE european_city_update_rule AS ON UPDATE TO european_city_view 1414DO INSTEAD UPDATE city_view SET 1415 city_name = NEW.city_name, 1416 population = NEW.population, 1417 country_name = NEW.country_name 1418WHERE city_id = OLD.city_id 1419RETURNING NEW.*; 1420CREATE RULE european_city_delete_rule AS ON DELETE TO european_city_view 1421DO INSTEAD DELETE FROM city_view WHERE city_id = OLD.city_id RETURNING *; 1422\set QUIET false 1423-- INSERT not limited by view's WHERE clause, but UPDATE AND DELETE are 1424INSERT INTO european_city_view(city_name, country_name) 1425 VALUES ('Cambridge', 'USA') RETURNING *; 1426 city_id | city_name | population | country_name | continent 1427---------+-----------+------------+--------------+--------------- 1428 4 | Cambridge | | USA | North America 1429(1 row) 1430 1431INSERT 0 1 1432UPDATE european_city_view SET country_name = 'UK' 1433 WHERE city_name = 'Cambridge'; 1434UPDATE 0 1435DELETE FROM european_city_view WHERE city_name = 'Cambridge'; 1436DELETE 0 1437-- UPDATE and DELETE via rule and trigger 1438UPDATE city_view SET country_name = 'UK' 1439 WHERE city_name = 'Cambridge' RETURNING *; 1440 city_id | city_name | population | country_name | continent 1441---------+-----------+------------+--------------+----------- 1442 4 | Cambridge | | UK | Europe 1443(1 row) 1444 1445UPDATE 1 1446UPDATE european_city_view SET population = 122800 1447 WHERE city_name = 'Cambridge' RETURNING *; 1448 city_id | city_name | population | country_name | continent 1449---------+-----------+------------+--------------+----------- 1450 4 | Cambridge | 122800 | UK | Europe 1451(1 row) 1452 1453UPDATE 1 1454DELETE FROM european_city_view WHERE city_name = 'Cambridge' RETURNING *; 1455 city_id | city_name | population | country_name | continent 1456---------+-----------+------------+--------------+----------- 1457 4 | Cambridge | 122800 | UK | Europe 1458(1 row) 1459 1460DELETE 1 1461-- join UPDATE test 1462UPDATE city_view v SET population = 599657 1463 FROM city_table ci, country_table co 1464 WHERE ci.city_name = 'Washington DC' and co.country_name = 'USA' 1465 AND v.city_id = ci.city_id AND v.country_name = co.country_name 1466 RETURNING co.country_id, v.country_name, 1467 v.city_id, v.city_name, v.population; 1468 country_id | country_name | city_id | city_name | population 1469------------+--------------+---------+---------------+------------ 1470 3 | USA | 3 | Washington DC | 599657 1471(1 row) 1472 1473UPDATE 1 1474\set QUIET true 1475SELECT * FROM city_view; 1476 city_id | city_name | population | country_name | continent 1477---------+---------------+------------+--------------+--------------- 1478 1 | Tokyo | 13010279 | Japan | Asia 1479 123456 | New York | 8391881 | USA | North America 1480 2 | London | 7556900 | UK | Europe 1481 3 | Washington DC | 599657 | USA | North America 1482(4 rows) 1483 1484DROP TABLE city_table CASCADE; 1485NOTICE: drop cascades to 2 other objects 1486DETAIL: drop cascades to view city_view 1487drop cascades to view european_city_view 1488DROP TABLE country_table; 1489-- Test pg_trigger_depth() 1490create table depth_a (id int not null primary key); 1491create table depth_b (id int not null primary key); 1492create table depth_c (id int not null primary key); 1493create function depth_a_tf() returns trigger 1494 language plpgsql as $$ 1495begin 1496 raise notice '%: depth = %', tg_name, pg_trigger_depth(); 1497 insert into depth_b values (new.id); 1498 raise notice '%: depth = %', tg_name, pg_trigger_depth(); 1499 return new; 1500end; 1501$$; 1502create trigger depth_a_tr before insert on depth_a 1503 for each row execute procedure depth_a_tf(); 1504create function depth_b_tf() returns trigger 1505 language plpgsql as $$ 1506begin 1507 raise notice '%: depth = %', tg_name, pg_trigger_depth(); 1508 begin 1509 execute 'insert into depth_c values (' || new.id::text || ')'; 1510 exception 1511 when sqlstate 'U9999' then 1512 raise notice 'SQLSTATE = U9999: depth = %', pg_trigger_depth(); 1513 end; 1514 raise notice '%: depth = %', tg_name, pg_trigger_depth(); 1515 if new.id = 1 then 1516 execute 'insert into depth_c values (' || new.id::text || ')'; 1517 end if; 1518 return new; 1519end; 1520$$; 1521create trigger depth_b_tr before insert on depth_b 1522 for each row execute procedure depth_b_tf(); 1523create function depth_c_tf() returns trigger 1524 language plpgsql as $$ 1525begin 1526 raise notice '%: depth = %', tg_name, pg_trigger_depth(); 1527 if new.id = 1 then 1528 raise exception sqlstate 'U9999'; 1529 end if; 1530 raise notice '%: depth = %', tg_name, pg_trigger_depth(); 1531 return new; 1532end; 1533$$; 1534create trigger depth_c_tr before insert on depth_c 1535 for each row execute procedure depth_c_tf(); 1536select pg_trigger_depth(); 1537 pg_trigger_depth 1538------------------ 1539 0 1540(1 row) 1541 1542insert into depth_a values (1); 1543NOTICE: depth_a_tr: depth = 1 1544NOTICE: depth_b_tr: depth = 2 1545NOTICE: depth_c_tr: depth = 3 1546NOTICE: SQLSTATE = U9999: depth = 2 1547NOTICE: depth_b_tr: depth = 2 1548NOTICE: depth_c_tr: depth = 3 1549ERROR: U9999 1550CONTEXT: PL/pgSQL function depth_c_tf() line 5 at RAISE 1551SQL statement "insert into depth_c values (1)" 1552PL/pgSQL function depth_b_tf() line 12 at EXECUTE 1553SQL statement "insert into depth_b values (new.id)" 1554PL/pgSQL function depth_a_tf() line 4 at SQL statement 1555select pg_trigger_depth(); 1556 pg_trigger_depth 1557------------------ 1558 0 1559(1 row) 1560 1561insert into depth_a values (2); 1562NOTICE: depth_a_tr: depth = 1 1563NOTICE: depth_b_tr: depth = 2 1564NOTICE: depth_c_tr: depth = 3 1565NOTICE: depth_c_tr: depth = 3 1566NOTICE: depth_b_tr: depth = 2 1567NOTICE: depth_a_tr: depth = 1 1568select pg_trigger_depth(); 1569 pg_trigger_depth 1570------------------ 1571 0 1572(1 row) 1573 1574drop table depth_a, depth_b, depth_c; 1575drop function depth_a_tf(); 1576drop function depth_b_tf(); 1577drop function depth_c_tf(); 1578-- 1579-- Test updates to rows during firing of BEFORE ROW triggers. 1580-- As of 9.2, such cases should be rejected (see bug #6123). 1581-- 1582create temp table parent ( 1583 aid int not null primary key, 1584 val1 text, 1585 val2 text, 1586 val3 text, 1587 val4 text, 1588 bcnt int not null default 0); 1589create temp table child ( 1590 bid int not null primary key, 1591 aid int not null, 1592 val1 text); 1593create function parent_upd_func() 1594 returns trigger language plpgsql as 1595$$ 1596begin 1597 if old.val1 <> new.val1 then 1598 new.val2 = new.val1; 1599 delete from child where child.aid = new.aid and child.val1 = new.val1; 1600 end if; 1601 return new; 1602end; 1603$$; 1604create trigger parent_upd_trig before update on parent 1605 for each row execute procedure parent_upd_func(); 1606create function parent_del_func() 1607 returns trigger language plpgsql as 1608$$ 1609begin 1610 delete from child where aid = old.aid; 1611 return old; 1612end; 1613$$; 1614create trigger parent_del_trig before delete on parent 1615 for each row execute procedure parent_del_func(); 1616create function child_ins_func() 1617 returns trigger language plpgsql as 1618$$ 1619begin 1620 update parent set bcnt = bcnt + 1 where aid = new.aid; 1621 return new; 1622end; 1623$$; 1624create trigger child_ins_trig after insert on child 1625 for each row execute procedure child_ins_func(); 1626create function child_del_func() 1627 returns trigger language plpgsql as 1628$$ 1629begin 1630 update parent set bcnt = bcnt - 1 where aid = old.aid; 1631 return old; 1632end; 1633$$; 1634create trigger child_del_trig after delete on child 1635 for each row execute procedure child_del_func(); 1636insert into parent values (1, 'a', 'a', 'a', 'a', 0); 1637insert into child values (10, 1, 'b'); 1638select * from parent; select * from child; 1639 aid | val1 | val2 | val3 | val4 | bcnt 1640-----+------+------+------+------+------ 1641 1 | a | a | a | a | 1 1642(1 row) 1643 1644 bid | aid | val1 1645-----+-----+------ 1646 10 | 1 | b 1647(1 row) 1648 1649update parent set val1 = 'b' where aid = 1; -- should fail 1650ERROR: tuple to be updated was already modified by an operation triggered by the current command 1651HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows. 1652select * from parent; select * from child; 1653 aid | val1 | val2 | val3 | val4 | bcnt 1654-----+------+------+------+------+------ 1655 1 | a | a | a | a | 1 1656(1 row) 1657 1658 bid | aid | val1 1659-----+-----+------ 1660 10 | 1 | b 1661(1 row) 1662 1663delete from parent where aid = 1; -- should fail 1664ERROR: tuple to be updated was already modified by an operation triggered by the current command 1665HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows. 1666select * from parent; select * from child; 1667 aid | val1 | val2 | val3 | val4 | bcnt 1668-----+------+------+------+------+------ 1669 1 | a | a | a | a | 1 1670(1 row) 1671 1672 bid | aid | val1 1673-----+-----+------ 1674 10 | 1 | b 1675(1 row) 1676 1677-- replace the trigger function with one that restarts the deletion after 1678-- having modified a child 1679create or replace function parent_del_func() 1680 returns trigger language plpgsql as 1681$$ 1682begin 1683 delete from child where aid = old.aid; 1684 if found then 1685 delete from parent where aid = old.aid; 1686 return null; -- cancel outer deletion 1687 end if; 1688 return old; 1689end; 1690$$; 1691delete from parent where aid = 1; 1692select * from parent; select * from child; 1693 aid | val1 | val2 | val3 | val4 | bcnt 1694-----+------+------+------+------+------ 1695(0 rows) 1696 1697 bid | aid | val1 1698-----+-----+------ 1699(0 rows) 1700 1701drop table parent, child; 1702drop function parent_upd_func(); 1703drop function parent_del_func(); 1704drop function child_ins_func(); 1705drop function child_del_func(); 1706-- similar case, but with a self-referencing FK so that parent and child 1707-- rows can be affected by a single operation 1708create temp table self_ref_trigger ( 1709 id int primary key, 1710 parent int references self_ref_trigger, 1711 data text, 1712 nchildren int not null default 0 1713); 1714create function self_ref_trigger_ins_func() 1715 returns trigger language plpgsql as 1716$$ 1717begin 1718 if new.parent is not null then 1719 update self_ref_trigger set nchildren = nchildren + 1 1720 where id = new.parent; 1721 end if; 1722 return new; 1723end; 1724$$; 1725create trigger self_ref_trigger_ins_trig before insert on self_ref_trigger 1726 for each row execute procedure self_ref_trigger_ins_func(); 1727create function self_ref_trigger_del_func() 1728 returns trigger language plpgsql as 1729$$ 1730begin 1731 if old.parent is not null then 1732 update self_ref_trigger set nchildren = nchildren - 1 1733 where id = old.parent; 1734 end if; 1735 return old; 1736end; 1737$$; 1738create trigger self_ref_trigger_del_trig before delete on self_ref_trigger 1739 for each row execute procedure self_ref_trigger_del_func(); 1740insert into self_ref_trigger values (1, null, 'root'); 1741insert into self_ref_trigger values (2, 1, 'root child A'); 1742insert into self_ref_trigger values (3, 1, 'root child B'); 1743insert into self_ref_trigger values (4, 2, 'grandchild 1'); 1744insert into self_ref_trigger values (5, 3, 'grandchild 2'); 1745update self_ref_trigger set data = 'root!' where id = 1; 1746select * from self_ref_trigger; 1747 id | parent | data | nchildren 1748----+--------+--------------+----------- 1749 2 | 1 | root child A | 1 1750 4 | 2 | grandchild 1 | 0 1751 3 | 1 | root child B | 1 1752 5 | 3 | grandchild 2 | 0 1753 1 | | root! | 2 1754(5 rows) 1755 1756delete from self_ref_trigger; 1757ERROR: tuple to be updated was already modified by an operation triggered by the current command 1758HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows. 1759select * from self_ref_trigger; 1760 id | parent | data | nchildren 1761----+--------+--------------+----------- 1762 2 | 1 | root child A | 1 1763 4 | 2 | grandchild 1 | 0 1764 3 | 1 | root child B | 1 1765 5 | 3 | grandchild 2 | 0 1766 1 | | root! | 2 1767(5 rows) 1768 1769drop table self_ref_trigger; 1770drop function self_ref_trigger_ins_func(); 1771drop function self_ref_trigger_del_func(); 1772-- 1773-- Check that statement triggers work correctly even with all children excluded 1774-- 1775create table stmt_trig_on_empty_upd (a int); 1776create table stmt_trig_on_empty_upd1 () inherits (stmt_trig_on_empty_upd); 1777create function update_stmt_notice() returns trigger as $$ 1778begin 1779 raise notice 'updating %', TG_TABLE_NAME; 1780 return null; 1781end; 1782$$ language plpgsql; 1783create trigger before_stmt_trigger 1784 before update on stmt_trig_on_empty_upd 1785 execute procedure update_stmt_notice(); 1786create trigger before_stmt_trigger 1787 before update on stmt_trig_on_empty_upd1 1788 execute procedure update_stmt_notice(); 1789-- inherited no-op update 1790update stmt_trig_on_empty_upd set a = a where false returning a+1 as aa; 1791NOTICE: updating stmt_trig_on_empty_upd 1792 aa 1793---- 1794(0 rows) 1795 1796-- simple no-op update 1797update stmt_trig_on_empty_upd1 set a = a where false returning a+1 as aa; 1798NOTICE: updating stmt_trig_on_empty_upd1 1799 aa 1800---- 1801(0 rows) 1802 1803drop table stmt_trig_on_empty_upd cascade; 1804NOTICE: drop cascades to table stmt_trig_on_empty_upd1 1805drop function update_stmt_notice(); 1806-- 1807-- Check that index creation (or DDL in general) is prohibited in a trigger 1808-- 1809create table trigger_ddl_table ( 1810 col1 integer, 1811 col2 integer 1812); 1813create function trigger_ddl_func() returns trigger as $$ 1814begin 1815 alter table trigger_ddl_table add primary key (col1); 1816 return new; 1817end$$ language plpgsql; 1818create trigger trigger_ddl_func before insert on trigger_ddl_table for each row 1819 execute procedure trigger_ddl_func(); 1820insert into trigger_ddl_table values (1, 42); -- fail 1821ERROR: cannot ALTER TABLE "trigger_ddl_table" because it is being used by active queries in this session 1822CONTEXT: SQL statement "alter table trigger_ddl_table add primary key (col1)" 1823PL/pgSQL function trigger_ddl_func() line 3 at SQL statement 1824create or replace function trigger_ddl_func() returns trigger as $$ 1825begin 1826 create index on trigger_ddl_table (col2); 1827 return new; 1828end$$ language plpgsql; 1829insert into trigger_ddl_table values (1, 42); -- fail 1830ERROR: cannot CREATE INDEX "trigger_ddl_table" because it is being used by active queries in this session 1831CONTEXT: SQL statement "create index on trigger_ddl_table (col2)" 1832PL/pgSQL function trigger_ddl_func() line 3 at SQL statement 1833drop table trigger_ddl_table; 1834drop function trigger_ddl_func(); 1835-- 1836-- Verify behavior of before and after triggers with INSERT...ON CONFLICT 1837-- DO UPDATE 1838-- 1839create table upsert (key int4 primary key, color text); 1840create function upsert_before_func() 1841 returns trigger language plpgsql as 1842$$ 1843begin 1844 if (TG_OP = 'UPDATE') then 1845 raise warning 'before update (old): %', old.*::text; 1846 raise warning 'before update (new): %', new.*::text; 1847 elsif (TG_OP = 'INSERT') then 1848 raise warning 'before insert (new): %', new.*::text; 1849 if new.key % 2 = 0 then 1850 new.key := new.key + 1; 1851 new.color := new.color || ' trig modified'; 1852 raise warning 'before insert (new, modified): %', new.*::text; 1853 end if; 1854 end if; 1855 return new; 1856end; 1857$$; 1858create trigger upsert_before_trig before insert or update on upsert 1859 for each row execute procedure upsert_before_func(); 1860create function upsert_after_func() 1861 returns trigger language plpgsql as 1862$$ 1863begin 1864 if (TG_OP = 'UPDATE') then 1865 raise warning 'after update (old): %', old.*::text; 1866 raise warning 'after update (new): %', new.*::text; 1867 elsif (TG_OP = 'INSERT') then 1868 raise warning 'after insert (new): %', new.*::text; 1869 end if; 1870 return null; 1871end; 1872$$; 1873create trigger upsert_after_trig after insert or update on upsert 1874 for each row execute procedure upsert_after_func(); 1875insert into upsert values(1, 'black') on conflict (key) do update set color = 'updated ' || upsert.color; 1876WARNING: before insert (new): (1,black) 1877WARNING: after insert (new): (1,black) 1878insert into upsert values(2, 'red') on conflict (key) do update set color = 'updated ' || upsert.color; 1879WARNING: before insert (new): (2,red) 1880WARNING: before insert (new, modified): (3,"red trig modified") 1881WARNING: after insert (new): (3,"red trig modified") 1882insert into upsert values(3, 'orange') on conflict (key) do update set color = 'updated ' || upsert.color; 1883WARNING: before insert (new): (3,orange) 1884WARNING: before update (old): (3,"red trig modified") 1885WARNING: before update (new): (3,"updated red trig modified") 1886WARNING: after update (old): (3,"red trig modified") 1887WARNING: after update (new): (3,"updated red trig modified") 1888insert into upsert values(4, 'green') on conflict (key) do update set color = 'updated ' || upsert.color; 1889WARNING: before insert (new): (4,green) 1890WARNING: before insert (new, modified): (5,"green trig modified") 1891WARNING: after insert (new): (5,"green trig modified") 1892insert into upsert values(5, 'purple') on conflict (key) do update set color = 'updated ' || upsert.color; 1893WARNING: before insert (new): (5,purple) 1894WARNING: before update (old): (5,"green trig modified") 1895WARNING: before update (new): (5,"updated green trig modified") 1896WARNING: after update (old): (5,"green trig modified") 1897WARNING: after update (new): (5,"updated green trig modified") 1898insert into upsert values(6, 'white') on conflict (key) do update set color = 'updated ' || upsert.color; 1899WARNING: before insert (new): (6,white) 1900WARNING: before insert (new, modified): (7,"white trig modified") 1901WARNING: after insert (new): (7,"white trig modified") 1902insert into upsert values(7, 'pink') on conflict (key) do update set color = 'updated ' || upsert.color; 1903WARNING: before insert (new): (7,pink) 1904WARNING: before update (old): (7,"white trig modified") 1905WARNING: before update (new): (7,"updated white trig modified") 1906WARNING: after update (old): (7,"white trig modified") 1907WARNING: after update (new): (7,"updated white trig modified") 1908insert into upsert values(8, 'yellow') on conflict (key) do update set color = 'updated ' || upsert.color; 1909WARNING: before insert (new): (8,yellow) 1910WARNING: before insert (new, modified): (9,"yellow trig modified") 1911WARNING: after insert (new): (9,"yellow trig modified") 1912select * from upsert; 1913 key | color 1914-----+----------------------------- 1915 1 | black 1916 3 | updated red trig modified 1917 5 | updated green trig modified 1918 7 | updated white trig modified 1919 9 | yellow trig modified 1920(5 rows) 1921 1922drop table upsert; 1923drop function upsert_before_func(); 1924drop function upsert_after_func(); 1925-- 1926-- Verify that triggers with transition tables are not allowed on 1927-- views 1928-- 1929create table my_table (i int); 1930create view my_view as select * from my_table; 1931create function my_trigger_function() returns trigger as $$ begin end; $$ language plpgsql; 1932create trigger my_trigger after update on my_view referencing old table as old_table 1933 for each statement execute procedure my_trigger_function(); 1934ERROR: "my_view" is a view 1935DETAIL: Triggers on views cannot have transition tables. 1936drop function my_trigger_function(); 1937drop view my_view; 1938drop table my_table; 1939-- 1940-- Verify that per-statement triggers are fired for partitioned tables 1941-- 1942create table parted_stmt_trig (a int) partition by list (a); 1943create table parted_stmt_trig1 partition of parted_stmt_trig for values in (1); 1944create table parted_stmt_trig2 partition of parted_stmt_trig for values in (2); 1945create table parted2_stmt_trig (a int) partition by list (a); 1946create table parted2_stmt_trig1 partition of parted2_stmt_trig for values in (1); 1947create table parted2_stmt_trig2 partition of parted2_stmt_trig for values in (2); 1948create or replace function trigger_notice() returns trigger as $$ 1949 begin 1950 raise notice 'trigger on % % % for %', TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL; 1951 if TG_LEVEL = 'ROW' then 1952 return NEW; 1953 end if; 1954 return null; 1955 end; 1956 $$ language plpgsql; 1957-- insert/update/delete statment-level triggers on the parent 1958create trigger trig_ins_before before insert on parted_stmt_trig 1959 for each statement execute procedure trigger_notice(); 1960create trigger trig_ins_after after insert on parted_stmt_trig 1961 for each statement execute procedure trigger_notice(); 1962create trigger trig_upd_before before update on parted_stmt_trig 1963 for each statement execute procedure trigger_notice(); 1964create trigger trig_upd_after after update on parted_stmt_trig 1965 for each statement execute procedure trigger_notice(); 1966create trigger trig_del_before before delete on parted_stmt_trig 1967 for each statement execute procedure trigger_notice(); 1968create trigger trig_del_after after delete on parted_stmt_trig 1969 for each statement execute procedure trigger_notice(); 1970-- insert/update/delete row-level triggers on the first partition 1971create trigger trig_ins_before before insert on parted_stmt_trig1 1972 for each row execute procedure trigger_notice(); 1973create trigger trig_ins_after after insert on parted_stmt_trig1 1974 for each row execute procedure trigger_notice(); 1975create trigger trig_upd_before before update on parted_stmt_trig1 1976 for each row execute procedure trigger_notice(); 1977create trigger trig_upd_after after update on parted_stmt_trig1 1978 for each row execute procedure trigger_notice(); 1979-- insert/update/delete statement-level triggers on the parent 1980create trigger trig_ins_before before insert on parted2_stmt_trig 1981 for each statement execute procedure trigger_notice(); 1982create trigger trig_ins_after after insert on parted2_stmt_trig 1983 for each statement execute procedure trigger_notice(); 1984create trigger trig_upd_before before update on parted2_stmt_trig 1985 for each statement execute procedure trigger_notice(); 1986create trigger trig_upd_after after update on parted2_stmt_trig 1987 for each statement execute procedure trigger_notice(); 1988create trigger trig_del_before before delete on parted2_stmt_trig 1989 for each statement execute procedure trigger_notice(); 1990create trigger trig_del_after after delete on parted2_stmt_trig 1991 for each statement execute procedure trigger_notice(); 1992with ins (a) as ( 1993 insert into parted2_stmt_trig values (1), (2) returning a 1994) insert into parted_stmt_trig select a from ins returning tableoid::regclass, a; 1995NOTICE: trigger on parted_stmt_trig BEFORE INSERT for STATEMENT 1996NOTICE: trigger on parted2_stmt_trig BEFORE INSERT for STATEMENT 1997NOTICE: trigger on parted_stmt_trig1 BEFORE INSERT for ROW 1998NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW 1999NOTICE: trigger on parted2_stmt_trig AFTER INSERT for STATEMENT 2000NOTICE: trigger on parted_stmt_trig AFTER INSERT for STATEMENT 2001 tableoid | a 2002-------------------+--- 2003 parted_stmt_trig1 | 1 2004 parted_stmt_trig2 | 2 2005(2 rows) 2006 2007with upd as ( 2008 update parted2_stmt_trig set a = a 2009) update parted_stmt_trig set a = a; 2010NOTICE: trigger on parted_stmt_trig BEFORE UPDATE for STATEMENT 2011NOTICE: trigger on parted_stmt_trig1 BEFORE UPDATE for ROW 2012NOTICE: trigger on parted2_stmt_trig BEFORE UPDATE for STATEMENT 2013NOTICE: trigger on parted_stmt_trig1 AFTER UPDATE for ROW 2014NOTICE: trigger on parted_stmt_trig AFTER UPDATE for STATEMENT 2015NOTICE: trigger on parted2_stmt_trig AFTER UPDATE for STATEMENT 2016delete from parted_stmt_trig; 2017NOTICE: trigger on parted_stmt_trig BEFORE DELETE for STATEMENT 2018NOTICE: trigger on parted_stmt_trig AFTER DELETE for STATEMENT 2019-- insert via copy on the parent 2020copy parted_stmt_trig(a) from stdin; 2021NOTICE: trigger on parted_stmt_trig BEFORE INSERT for STATEMENT 2022NOTICE: trigger on parted_stmt_trig1 BEFORE INSERT for ROW 2023NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW 2024NOTICE: trigger on parted_stmt_trig AFTER INSERT for STATEMENT 2025-- insert via copy on the first partition 2026copy parted_stmt_trig1(a) from stdin; 2027NOTICE: trigger on parted_stmt_trig1 BEFORE INSERT for ROW 2028NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW 2029drop table parted_stmt_trig, parted2_stmt_trig; 2030-- 2031-- Test the interaction between transition tables and both kinds of 2032-- inheritance. We'll dump the contents of the transition tables in a 2033-- format that shows the attribute order, so that we can distinguish 2034-- tuple formats (though not dropped attributes). 2035-- 2036create or replace function dump_insert() returns trigger language plpgsql as 2037$$ 2038 begin 2039 raise notice 'trigger = %, new table = %', 2040 TG_NAME, 2041 (select string_agg(new_table::text, ', ' order by a) from new_table); 2042 return null; 2043 end; 2044$$; 2045create or replace function dump_update() returns trigger language plpgsql as 2046$$ 2047 begin 2048 raise notice 'trigger = %, old table = %, new table = %', 2049 TG_NAME, 2050 (select string_agg(old_table::text, ', ' order by a) from old_table), 2051 (select string_agg(new_table::text, ', ' order by a) from new_table); 2052 return null; 2053 end; 2054$$; 2055create or replace function dump_delete() returns trigger language plpgsql as 2056$$ 2057 begin 2058 raise notice 'trigger = %, old table = %', 2059 TG_NAME, 2060 (select string_agg(old_table::text, ', ' order by a) from old_table); 2061 return null; 2062 end; 2063$$; 2064-- 2065-- Verify behavior of statement triggers on partition hierarchy with 2066-- transition tables. Tuples should appear to each trigger in the 2067-- format of the the relation the trigger is attached to. 2068-- 2069-- set up a partition hierarchy with some different TupleDescriptors 2070create table parent (a text, b int) partition by list (a); 2071-- a child matching parent 2072create table child1 partition of parent for values in ('AAA'); 2073-- a child with a dropped column 2074create table child2 (x int, a text, b int); 2075alter table child2 drop column x; 2076alter table parent attach partition child2 for values in ('BBB'); 2077-- a child with a different column order 2078create table child3 (b int, a text); 2079alter table parent attach partition child3 for values in ('CCC'); 2080create trigger parent_insert_trig 2081 after insert on parent referencing new table as new_table 2082 for each statement execute procedure dump_insert(); 2083create trigger parent_update_trig 2084 after update on parent referencing old table as old_table new table as new_table 2085 for each statement execute procedure dump_update(); 2086create trigger parent_delete_trig 2087 after delete on parent referencing old table as old_table 2088 for each statement execute procedure dump_delete(); 2089create trigger child1_insert_trig 2090 after insert on child1 referencing new table as new_table 2091 for each statement execute procedure dump_insert(); 2092create trigger child1_update_trig 2093 after update on child1 referencing old table as old_table new table as new_table 2094 for each statement execute procedure dump_update(); 2095create trigger child1_delete_trig 2096 after delete on child1 referencing old table as old_table 2097 for each statement execute procedure dump_delete(); 2098create trigger child2_insert_trig 2099 after insert on child2 referencing new table as new_table 2100 for each statement execute procedure dump_insert(); 2101create trigger child2_update_trig 2102 after update on child2 referencing old table as old_table new table as new_table 2103 for each statement execute procedure dump_update(); 2104create trigger child2_delete_trig 2105 after delete on child2 referencing old table as old_table 2106 for each statement execute procedure dump_delete(); 2107create trigger child3_insert_trig 2108 after insert on child3 referencing new table as new_table 2109 for each statement execute procedure dump_insert(); 2110create trigger child3_update_trig 2111 after update on child3 referencing old table as old_table new table as new_table 2112 for each statement execute procedure dump_update(); 2113create trigger child3_delete_trig 2114 after delete on child3 referencing old table as old_table 2115 for each statement execute procedure dump_delete(); 2116-- insert directly into children sees respective child-format tuples 2117insert into child1 values ('AAA', 42); 2118NOTICE: trigger = child1_insert_trig, new table = (AAA,42) 2119insert into child2 values ('BBB', 42); 2120NOTICE: trigger = child2_insert_trig, new table = (BBB,42) 2121insert into child3 values (42, 'CCC'); 2122NOTICE: trigger = child3_insert_trig, new table = (42,CCC) 2123-- update via parent sees parent-format tuples 2124update parent set b = b + 1; 2125NOTICE: trigger = parent_update_trig, old table = (AAA,42), (BBB,42), (CCC,42), new table = (AAA,43), (BBB,43), (CCC,43) 2126-- delete via parent sees parent-format tuples 2127delete from parent; 2128NOTICE: trigger = parent_delete_trig, old table = (AAA,43), (BBB,43), (CCC,43) 2129-- insert into parent sees parent-format tuples 2130insert into parent values ('AAA', 42); 2131NOTICE: trigger = parent_insert_trig, new table = (AAA,42) 2132insert into parent values ('BBB', 42); 2133NOTICE: trigger = parent_insert_trig, new table = (BBB,42) 2134insert into parent values ('CCC', 42); 2135NOTICE: trigger = parent_insert_trig, new table = (CCC,42) 2136-- delete from children sees respective child-format tuples 2137delete from child1; 2138NOTICE: trigger = child1_delete_trig, old table = (AAA,42) 2139delete from child2; 2140NOTICE: trigger = child2_delete_trig, old table = (BBB,42) 2141delete from child3; 2142NOTICE: trigger = child3_delete_trig, old table = (42,CCC) 2143-- copy into parent sees parent-format tuples 2144copy parent (a, b) from stdin; 2145NOTICE: trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,42) 2146-- DML affecting parent sees tuples collected from children even if 2147-- there is no transition table trigger on the children 2148drop trigger child1_insert_trig on child1; 2149drop trigger child1_update_trig on child1; 2150drop trigger child1_delete_trig on child1; 2151drop trigger child2_insert_trig on child2; 2152drop trigger child2_update_trig on child2; 2153drop trigger child2_delete_trig on child2; 2154drop trigger child3_insert_trig on child3; 2155drop trigger child3_update_trig on child3; 2156drop trigger child3_delete_trig on child3; 2157delete from parent; 2158NOTICE: trigger = parent_delete_trig, old table = (AAA,42), (BBB,42), (CCC,42) 2159-- copy into parent sees tuples collected from children even if there 2160-- is no transition-table trigger on the children 2161copy parent (a, b) from stdin; 2162NOTICE: trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,42) 2163-- insert into parent with a before trigger on a child tuple before 2164-- insertion, and we capture the newly modified row in parent format 2165create or replace function intercept_insert() returns trigger language plpgsql as 2166$$ 2167 begin 2168 new.b = new.b + 1000; 2169 return new; 2170 end; 2171$$; 2172create trigger intercept_insert_child3 2173 before insert on child3 2174 for each row execute procedure intercept_insert(); 2175-- insert, parent trigger sees post-modification parent-format tuple 2176insert into parent values ('AAA', 42), ('BBB', 42), ('CCC', 66); 2177NOTICE: trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,1066) 2178-- copy, parent trigger sees post-modification parent-format tuple 2179copy parent (a, b) from stdin; 2180NOTICE: trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,1234) 2181drop table child1, child2, child3, parent; 2182drop function intercept_insert(); 2183-- 2184-- Verify prohibition of row triggers with transition triggers on 2185-- partitions 2186-- 2187create table parent (a text, b int) partition by list (a); 2188create table child partition of parent for values in ('AAA'); 2189-- adding row trigger with transition table fails 2190create trigger child_row_trig 2191 after insert on child referencing new table as new_table 2192 for each row execute procedure dump_insert(); 2193ERROR: ROW triggers with transition tables are not supported on partitions 2194-- detaching it first works 2195alter table parent detach partition child; 2196create trigger child_row_trig 2197 after insert on child referencing new table as new_table 2198 for each row execute procedure dump_insert(); 2199-- but now we're not allowed to reattach it 2200alter table parent attach partition child for values in ('AAA'); 2201ERROR: trigger "child_row_trig" prevents table "child" from becoming a partition 2202DETAIL: ROW triggers with transition tables are not supported on partitions 2203-- drop the trigger, and now we're allowed to attach it again 2204drop trigger child_row_trig on child; 2205alter table parent attach partition child for values in ('AAA'); 2206drop table child, parent; 2207-- 2208-- Verify behavior of statement triggers on (non-partition) 2209-- inheritance hierarchy with transition tables; similar to the 2210-- partition case, except there is no rerouting on insertion and child 2211-- tables can have extra columns 2212-- 2213-- set up inheritance hierarchy with different TupleDescriptors 2214create table parent (a text, b int); 2215-- a child matching parent 2216create table child1 () inherits (parent); 2217-- a child with a different column order 2218create table child2 (b int, a text); 2219alter table child2 inherit parent; 2220-- a child with an extra column 2221create table child3 (c text) inherits (parent); 2222create trigger parent_insert_trig 2223 after insert on parent referencing new table as new_table 2224 for each statement execute procedure dump_insert(); 2225create trigger parent_update_trig 2226 after update on parent referencing old table as old_table new table as new_table 2227 for each statement execute procedure dump_update(); 2228create trigger parent_delete_trig 2229 after delete on parent referencing old table as old_table 2230 for each statement execute procedure dump_delete(); 2231create trigger child1_insert_trig 2232 after insert on child1 referencing new table as new_table 2233 for each statement execute procedure dump_insert(); 2234create trigger child1_update_trig 2235 after update on child1 referencing old table as old_table new table as new_table 2236 for each statement execute procedure dump_update(); 2237create trigger child1_delete_trig 2238 after delete on child1 referencing old table as old_table 2239 for each statement execute procedure dump_delete(); 2240create trigger child2_insert_trig 2241 after insert on child2 referencing new table as new_table 2242 for each statement execute procedure dump_insert(); 2243create trigger child2_update_trig 2244 after update on child2 referencing old table as old_table new table as new_table 2245 for each statement execute procedure dump_update(); 2246create trigger child2_delete_trig 2247 after delete on child2 referencing old table as old_table 2248 for each statement execute procedure dump_delete(); 2249create trigger child3_insert_trig 2250 after insert on child3 referencing new table as new_table 2251 for each statement execute procedure dump_insert(); 2252create trigger child3_update_trig 2253 after update on child3 referencing old table as old_table new table as new_table 2254 for each statement execute procedure dump_update(); 2255create trigger child3_delete_trig 2256 after delete on child3 referencing old table as old_table 2257 for each statement execute procedure dump_delete(); 2258-- insert directly into children sees respective child-format tuples 2259insert into child1 values ('AAA', 42); 2260NOTICE: trigger = child1_insert_trig, new table = (AAA,42) 2261insert into child2 values (42, 'BBB'); 2262NOTICE: trigger = child2_insert_trig, new table = (42,BBB) 2263insert into child3 values ('CCC', 42, 'foo'); 2264NOTICE: trigger = child3_insert_trig, new table = (CCC,42,foo) 2265-- update via parent sees parent-format tuples 2266update parent set b = b + 1; 2267NOTICE: trigger = parent_update_trig, old table = (AAA,42), (BBB,42), (CCC,42), new table = (AAA,43), (BBB,43), (CCC,43) 2268-- delete via parent sees parent-format tuples 2269delete from parent; 2270NOTICE: trigger = parent_delete_trig, old table = (AAA,43), (BBB,43), (CCC,43) 2271-- reinsert values into children for next test... 2272insert into child1 values ('AAA', 42); 2273NOTICE: trigger = child1_insert_trig, new table = (AAA,42) 2274insert into child2 values (42, 'BBB'); 2275NOTICE: trigger = child2_insert_trig, new table = (42,BBB) 2276insert into child3 values ('CCC', 42, 'foo'); 2277NOTICE: trigger = child3_insert_trig, new table = (CCC,42,foo) 2278-- delete from children sees respective child-format tuples 2279delete from child1; 2280NOTICE: trigger = child1_delete_trig, old table = (AAA,42) 2281delete from child2; 2282NOTICE: trigger = child2_delete_trig, old table = (42,BBB) 2283delete from child3; 2284NOTICE: trigger = child3_delete_trig, old table = (CCC,42,foo) 2285-- copy into parent sees parent-format tuples (no rerouting, so these 2286-- are really inserted into the parent) 2287copy parent (a, b) from stdin; 2288NOTICE: trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,42) 2289-- same behavior for copy if there is an index (interesting because rows are 2290-- captured by a different code path in copy.c if there are indexes) 2291create index on parent(b); 2292copy parent (a, b) from stdin; 2293NOTICE: trigger = parent_insert_trig, new table = (DDD,42) 2294-- DML affecting parent sees tuples collected from children even if 2295-- there is no transition table trigger on the children 2296drop trigger child1_insert_trig on child1; 2297drop trigger child1_update_trig on child1; 2298drop trigger child1_delete_trig on child1; 2299drop trigger child2_insert_trig on child2; 2300drop trigger child2_update_trig on child2; 2301drop trigger child2_delete_trig on child2; 2302drop trigger child3_insert_trig on child3; 2303drop trigger child3_update_trig on child3; 2304drop trigger child3_delete_trig on child3; 2305delete from parent; 2306NOTICE: trigger = parent_delete_trig, old table = (AAA,42), (BBB,42), (CCC,42), (DDD,42) 2307drop table child1, child2, child3, parent; 2308-- 2309-- Verify prohibition of row triggers with transition triggers on 2310-- inheritance children 2311-- 2312create table parent (a text, b int); 2313create table child () inherits (parent); 2314-- adding row trigger with transition table fails 2315create trigger child_row_trig 2316 after insert on child referencing new table as new_table 2317 for each row execute procedure dump_insert(); 2318ERROR: ROW triggers with transition tables are not supported on inheritance children 2319-- disinheriting it first works 2320alter table child no inherit parent; 2321create trigger child_row_trig 2322 after insert on child referencing new table as new_table 2323 for each row execute procedure dump_insert(); 2324-- but now we're not allowed to make it inherit anymore 2325alter table child inherit parent; 2326ERROR: trigger "child_row_trig" prevents table "child" from becoming an inheritance child 2327DETAIL: ROW triggers with transition tables are not supported in inheritance hierarchies 2328-- drop the trigger, and now we're allowed to make it inherit again 2329drop trigger child_row_trig on child; 2330alter table child inherit parent; 2331drop table child, parent; 2332-- 2333-- Verify behavior of queries with wCTEs, where multiple transition 2334-- tuplestores can be active at the same time because there are 2335-- multiple DML statements that might fire triggers with transition 2336-- tables 2337-- 2338create table table1 (a int); 2339create table table2 (a text); 2340create trigger table1_trig 2341 after insert on table1 referencing new table as new_table 2342 for each statement execute procedure dump_insert(); 2343create trigger table2_trig 2344 after insert on table2 referencing new table as new_table 2345 for each statement execute procedure dump_insert(); 2346with wcte as (insert into table1 values (42)) 2347 insert into table2 values ('hello world'); 2348NOTICE: trigger = table2_trig, new table = ("hello world") 2349NOTICE: trigger = table1_trig, new table = (42) 2350with wcte as (insert into table1 values (43)) 2351 insert into table1 values (44); 2352NOTICE: trigger = table1_trig, new table = (43), (44) 2353select * from table1; 2354 a 2355---- 2356 42 2357 44 2358 43 2359(3 rows) 2360 2361select * from table2; 2362 a 2363------------- 2364 hello world 2365(1 row) 2366 2367drop table table1; 2368drop table table2; 2369-- 2370-- Verify behavior of INSERT ... ON CONFLICT DO UPDATE ... with 2371-- transition tables. 2372-- 2373create table my_table (a int primary key, b text); 2374create trigger my_table_insert_trig 2375 after insert on my_table referencing new table as new_table 2376 for each statement execute procedure dump_insert(); 2377create trigger my_table_update_trig 2378 after update on my_table referencing old table as old_table new table as new_table 2379 for each statement execute procedure dump_update(); 2380-- inserts only 2381insert into my_table values (1, 'AAA'), (2, 'BBB') 2382 on conflict (a) do 2383 update set b = my_table.b || ':' || excluded.b; 2384NOTICE: trigger = my_table_update_trig, old table = <NULL>, new table = <NULL> 2385NOTICE: trigger = my_table_insert_trig, new table = (1,AAA), (2,BBB) 2386-- mixture of inserts and updates 2387insert into my_table values (1, 'AAA'), (2, 'BBB'), (3, 'CCC'), (4, 'DDD') 2388 on conflict (a) do 2389 update set b = my_table.b || ':' || excluded.b; 2390NOTICE: trigger = my_table_update_trig, old table = (1,AAA), (2,BBB), new table = (1,AAA:AAA), (2,BBB:BBB) 2391NOTICE: trigger = my_table_insert_trig, new table = (3,CCC), (4,DDD) 2392-- updates only 2393insert into my_table values (3, 'CCC'), (4, 'DDD') 2394 on conflict (a) do 2395 update set b = my_table.b || ':' || excluded.b; 2396NOTICE: trigger = my_table_update_trig, old table = (3,CCC), (4,DDD), new table = (3,CCC:CCC), (4,DDD:DDD) 2397NOTICE: trigger = my_table_insert_trig, new table = <NULL> 2398-- 2399-- Verify that you can't create a trigger with transition tables for 2400-- more than one event. 2401-- 2402create trigger my_table_multievent_trig 2403 after insert or update on my_table referencing new table as new_table 2404 for each statement execute procedure dump_insert(); 2405ERROR: transition tables cannot be specified for triggers with more than one event 2406-- 2407-- Verify that you can't create a trigger with transition tables with 2408-- a column list. 2409-- 2410create trigger my_table_col_update_trig 2411 after update of b on my_table referencing new table as new_table 2412 for each statement execute procedure dump_insert(); 2413ERROR: transition tables cannot be specified for triggers with column lists 2414drop table my_table; 2415-- 2416-- Test firing of triggers with transition tables by foreign key cascades 2417-- 2418create table refd_table (a int primary key, b text); 2419create table trig_table (a int, b text, 2420 foreign key (a) references refd_table on update cascade on delete cascade 2421); 2422create trigger trig_table_before_trig 2423 before insert or update or delete on trig_table 2424 for each statement execute procedure trigger_func('trig_table'); 2425create trigger trig_table_insert_trig 2426 after insert on trig_table referencing new table as new_table 2427 for each statement execute procedure dump_insert(); 2428create trigger trig_table_update_trig 2429 after update on trig_table referencing old table as old_table new table as new_table 2430 for each statement execute procedure dump_update(); 2431create trigger trig_table_delete_trig 2432 after delete on trig_table referencing old table as old_table 2433 for each statement execute procedure dump_delete(); 2434insert into refd_table values 2435 (1, 'one'), 2436 (2, 'two'), 2437 (3, 'three'); 2438insert into trig_table values 2439 (1, 'one a'), 2440 (1, 'one b'), 2441 (2, 'two a'), 2442 (2, 'two b'), 2443 (3, 'three a'), 2444 (3, 'three b'); 2445NOTICE: trigger_func(trig_table) called: action = INSERT, when = BEFORE, level = STATEMENT 2446NOTICE: 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") 2447update refd_table set a = 11 where b = 'one'; 2448NOTICE: trigger_func(trig_table) called: action = UPDATE, when = BEFORE, level = STATEMENT 2449NOTICE: trigger = trig_table_update_trig, old table = (1,"one a"), (1,"one b"), new table = (11,"one a"), (11,"one b") 2450select * from trig_table; 2451 a | b 2452----+--------- 2453 2 | two a 2454 2 | two b 2455 3 | three a 2456 3 | three b 2457 11 | one a 2458 11 | one b 2459(6 rows) 2460 2461delete from refd_table where length(b) = 3; 2462NOTICE: trigger_func(trig_table) called: action = DELETE, when = BEFORE, level = STATEMENT 2463NOTICE: trigger = trig_table_delete_trig, old table = (2,"two a"), (2,"two b"), (11,"one a"), (11,"one b") 2464select * from trig_table; 2465 a | b 2466---+--------- 2467 3 | three a 2468 3 | three b 2469(2 rows) 2470 2471drop table refd_table, trig_table; 2472-- 2473-- self-referential FKs are even more fun 2474-- 2475create table self_ref (a int primary key, 2476 b int references self_ref(a) on delete cascade); 2477create trigger self_ref_before_trig 2478 before delete on self_ref 2479 for each statement execute procedure trigger_func('self_ref'); 2480create trigger self_ref_r_trig 2481 after delete on self_ref referencing old table as old_table 2482 for each row execute procedure dump_delete(); 2483create trigger self_ref_s_trig 2484 after delete on self_ref referencing old table as old_table 2485 for each statement execute procedure dump_delete(); 2486insert into self_ref values (1, null), (2, 1), (3, 2); 2487delete from self_ref where a = 1; 2488NOTICE: trigger_func(self_ref) called: action = DELETE, when = BEFORE, level = STATEMENT 2489NOTICE: trigger = self_ref_r_trig, old table = (1,), (2,1) 2490NOTICE: trigger_func(self_ref) called: action = DELETE, when = BEFORE, level = STATEMENT 2491NOTICE: trigger = self_ref_r_trig, old table = (1,), (2,1) 2492NOTICE: trigger = self_ref_s_trig, old table = (1,), (2,1) 2493NOTICE: trigger = self_ref_r_trig, old table = (3,2) 2494NOTICE: trigger = self_ref_s_trig, old table = (3,2) 2495-- without AR trigger, cascaded deletes all end up in one transition table 2496drop trigger self_ref_r_trig on self_ref; 2497insert into self_ref values (1, null), (2, 1), (3, 2), (4, 3); 2498delete from self_ref where a = 1; 2499NOTICE: trigger_func(self_ref) called: action = DELETE, when = BEFORE, level = STATEMENT 2500NOTICE: trigger = self_ref_s_trig, old table = (1,), (2,1), (3,2), (4,3) 2501drop table self_ref; 2502-- cleanup 2503drop function dump_insert(); 2504drop function dump_update(); 2505drop function dump_delete(); 2506