1-- 2-- TRIGGERS 3-- 4 5create table pkeys (pkey1 int4 not null, pkey2 text not null); 6create table fkeys (fkey1 int4, fkey2 text, fkey3 int); 7create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null); 8 9create index fkeys_i on fkeys (fkey1, fkey2); 10create index fkeys2_i on fkeys2 (fkey21, fkey22); 11create index fkeys2p_i on fkeys2 (pkey23); 12 13insert into pkeys values (10, '1'); 14insert into pkeys values (20, '2'); 15insert into pkeys values (30, '3'); 16insert into pkeys values (40, '4'); 17insert into pkeys values (50, '5'); 18insert into pkeys values (60, '6'); 19create unique index pkeys_i on pkeys (pkey1, pkey2); 20 21-- 22-- For fkeys: 23-- (fkey1, fkey2) --> pkeys (pkey1, pkey2) 24-- (fkey3) --> fkeys2 (pkey23) 25-- 26create trigger check_fkeys_pkey_exist 27 before insert or update on fkeys 28 for each row 29 execute procedure 30 check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2'); 31 32create trigger check_fkeys_pkey2_exist 33 before insert or update on fkeys 34 for each row 35 execute procedure check_primary_key ('fkey3', 'fkeys2', 'pkey23'); 36 37-- 38-- For fkeys2: 39-- (fkey21, fkey22) --> pkeys (pkey1, pkey2) 40-- 41create trigger check_fkeys2_pkey_exist 42 before insert or update on fkeys2 43 for each row 44 execute procedure 45 check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2'); 46 47-- Test comments 48COMMENT ON TRIGGER check_fkeys2_pkey_bad ON fkeys2 IS 'wrong'; 49COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS 'right'; 50COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS NULL; 51 52-- 53-- For pkeys: 54-- ON DELETE/UPDATE (pkey1, pkey2) CASCADE: 55-- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22) 56-- 57create trigger check_pkeys_fkey_cascade 58 before delete or update on pkeys 59 for each row 60 execute procedure 61 check_foreign_key (2, 'cascade', 'pkey1', 'pkey2', 62 'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22'); 63 64-- 65-- For fkeys2: 66-- ON DELETE/UPDATE (pkey23) RESTRICT: 67-- fkeys (fkey3) 68-- 69create trigger check_fkeys2_fkey_restrict 70 before delete or update on fkeys2 71 for each row 72 execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3'); 73 74insert into fkeys2 values (10, '1', 1); 75insert into fkeys2 values (30, '3', 2); 76insert into fkeys2 values (40, '4', 5); 77insert into fkeys2 values (50, '5', 3); 78-- no key in pkeys 79insert into fkeys2 values (70, '5', 3); 80 81insert into fkeys values (10, '1', 2); 82insert into fkeys values (30, '3', 3); 83insert into fkeys values (40, '4', 2); 84insert into fkeys values (50, '5', 2); 85-- no key in pkeys 86insert into fkeys values (70, '5', 1); 87-- no key in fkeys2 88insert into fkeys values (60, '6', 4); 89 90delete from pkeys where pkey1 = 30 and pkey2 = '3'; 91delete from pkeys where pkey1 = 40 and pkey2 = '4'; 92update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5'; 93update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1'; 94 95DROP TABLE pkeys; 96DROP TABLE fkeys; 97DROP TABLE fkeys2; 98 99-- -- I've disabled the funny_dup17 test because the new semantics 100-- -- of AFTER ROW triggers, which get now fired at the end of a 101-- -- query always, cause funny_dup17 to enter an endless loop. 102-- -- 103-- -- Jan 104-- 105-- create table dup17 (x int4); 106-- 107-- create trigger dup17_before 108-- before insert on dup17 109-- for each row 110-- execute procedure 111-- funny_dup17 () 112-- ; 113-- 114-- insert into dup17 values (17); 115-- select count(*) from dup17; 116-- insert into dup17 values (17); 117-- select count(*) from dup17; 118-- 119-- drop trigger dup17_before on dup17; 120-- 121-- create trigger dup17_after 122-- after insert on dup17 123-- for each row 124-- execute procedure 125-- funny_dup17 () 126-- ; 127-- insert into dup17 values (13); 128-- select count(*) from dup17 where x = 13; 129-- insert into dup17 values (13); 130-- select count(*) from dup17 where x = 13; 131-- 132-- DROP TABLE dup17; 133 134-- Check behavior when trigger returns unmodified trigtuple 135create table trigtest (f1 int, f2 text); 136 137create trigger trigger_return_old 138 before insert or delete or update on trigtest 139 for each row execute procedure trigger_return_old(); 140 141insert into trigtest values(1, 'foo'); 142select * from trigtest; 143update trigtest set f2 = f2 || 'bar'; 144select * from trigtest; 145delete from trigtest; 146select * from trigtest; 147 148-- Also check what happens when such a trigger runs before or after others 149create function f1_times_10() returns trigger as 150$$ begin new.f1 := new.f1 * 10; return new; end $$ language plpgsql; 151 152create trigger trigger_alpha 153 before insert or update on trigtest 154 for each row execute procedure f1_times_10(); 155 156insert into trigtest values(1, 'foo'); 157select * from trigtest; 158update trigtest set f2 = f2 || 'bar'; 159select * from trigtest; 160delete from trigtest; 161select * from trigtest; 162 163create trigger trigger_zed 164 before insert or update on trigtest 165 for each row execute procedure f1_times_10(); 166 167insert into trigtest values(1, 'foo'); 168select * from trigtest; 169update trigtest set f2 = f2 || 'bar'; 170select * from trigtest; 171delete from trigtest; 172select * from trigtest; 173 174drop trigger trigger_alpha on trigtest; 175 176insert into trigtest values(1, 'foo'); 177select * from trigtest; 178update trigtest set f2 = f2 || 'bar'; 179select * from trigtest; 180delete from trigtest; 181select * from trigtest; 182 183drop table trigtest; 184 185create sequence ttdummy_seq increment 10 start 0 minvalue 0; 186 187create table tttest ( 188 price_id int4, 189 price_val int4, 190 price_on int4, 191 price_off int4 default 999999 192); 193 194create trigger ttdummy 195 before delete or update on tttest 196 for each row 197 execute procedure 198 ttdummy (price_on, price_off); 199 200create trigger ttserial 201 before insert or update on tttest 202 for each row 203 execute procedure 204 autoinc (price_on, ttdummy_seq); 205 206insert into tttest values (1, 1, null); 207insert into tttest values (2, 2, null); 208insert into tttest values (3, 3, 0); 209 210select * from tttest; 211delete from tttest where price_id = 2; 212select * from tttest; 213-- what do we see ? 214 215-- get current prices 216select * from tttest where price_off = 999999; 217 218-- change price for price_id == 3 219update tttest set price_val = 30 where price_id = 3; 220select * from tttest; 221 222-- now we want to change pric_id in ALL tuples 223-- this gets us not what we need 224update tttest set price_id = 5 where price_id = 3; 225select * from tttest; 226 227-- restore data as before last update: 228select set_ttdummy(0); 229delete from tttest where price_id = 5; 230update tttest set price_off = 999999 where price_val = 30; 231select * from tttest; 232 233-- and try change price_id now! 234update tttest set price_id = 5 where price_id = 3; 235select * from tttest; 236-- isn't it what we need ? 237 238select set_ttdummy(1); 239 240-- we want to correct some "date" 241update tttest set price_on = -1 where price_id = 1; 242-- but this doesn't work 243 244-- try in this way 245select set_ttdummy(0); 246update tttest set price_on = -1 where price_id = 1; 247select * from tttest; 248-- isn't it what we need ? 249 250-- get price for price_id == 5 as it was @ "date" 35 251select * from tttest where price_on <= 35 and price_off > 35 and price_id = 5; 252 253drop table tttest; 254drop sequence ttdummy_seq; 255 256-- 257-- tests for per-statement triggers 258-- 259 260CREATE TABLE log_table (tstamp timestamp default timeofday()::timestamp); 261 262CREATE TABLE main_table (a int unique, b int); 263 264COPY main_table (a,b) FROM stdin; 2655 10 26620 20 26730 10 26850 35 26980 15 270\. 271 272CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS ' 273BEGIN 274 RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL; 275 RETURN NULL; 276END;'; 277 278CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table 279FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt'); 280 281CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table 282FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt'); 283 284-- 285-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified, 286-- CREATE TRIGGER should default to 'FOR EACH STATEMENT' 287-- 288CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_table 289EXECUTE PROCEDURE trigger_func('after_upd_stmt'); 290 291-- Both insert and update statement level triggers (before and after) should 292-- fire. Doesn't fire UPDATE before trigger, but only because one isn't 293-- defined. 294INSERT INTO main_table (a, b) VALUES (5, 10) ON CONFLICT (a) 295 DO UPDATE SET b = EXCLUDED.b; 296 297CREATE TRIGGER after_upd_row_trig AFTER UPDATE ON main_table 298FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_row'); 299 300INSERT INTO main_table DEFAULT VALUES; 301 302UPDATE main_table SET a = a + 1 WHERE b < 30; 303-- UPDATE that effects zero rows should still call per-statement trigger 304UPDATE main_table SET a = a + 2 WHERE b > 100; 305 306-- constraint now unneeded 307ALTER TABLE main_table DROP CONSTRAINT main_table_a_key; 308 309-- COPY should fire per-row and per-statement INSERT triggers 310COPY main_table (a, b) FROM stdin; 31130 40 31250 60 313\. 314 315SELECT * FROM main_table ORDER BY a, b; 316 317-- 318-- test triggers with WHEN clause 319-- 320 321CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table 322FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('modified_a'); 323CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table 324FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE PROCEDURE trigger_func('modified_any'); 325CREATE TRIGGER insert_a AFTER INSERT ON main_table 326FOR EACH ROW WHEN (NEW.a = 123) EXECUTE PROCEDURE trigger_func('insert_a'); 327CREATE TRIGGER delete_a AFTER DELETE ON main_table 328FOR EACH ROW WHEN (OLD.a = 123) EXECUTE PROCEDURE trigger_func('delete_a'); 329CREATE TRIGGER insert_when BEFORE INSERT ON main_table 330FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('insert_when'); 331CREATE TRIGGER delete_when AFTER DELETE ON main_table 332FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('delete_when'); 333INSERT INTO main_table (a) VALUES (123), (456); 334COPY main_table FROM stdin; 335123 999 336456 999 337\. 338DELETE FROM main_table WHERE a IN (123, 456); 339UPDATE main_table SET a = 50, b = 60; 340SELECT * FROM main_table ORDER BY a, b; 341SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a'; 342SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a'; 343SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any'; 344DROP TRIGGER modified_a ON main_table; 345DROP TRIGGER modified_any ON main_table; 346DROP TRIGGER insert_a ON main_table; 347DROP TRIGGER delete_a ON main_table; 348DROP TRIGGER insert_when ON main_table; 349DROP TRIGGER delete_when ON main_table; 350 351-- Test column-level triggers 352DROP TRIGGER after_upd_row_trig ON main_table; 353 354CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON main_table 355FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_a_row'); 356CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON main_table 357FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_b_row'); 358CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON main_table 359FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_a_b_row'); 360 361CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON main_table 362FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_upd_a_stmt'); 363CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON main_table 364FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_upd_b_stmt'); 365 366SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig'; 367 368UPDATE main_table SET a = 50; 369UPDATE main_table SET b = 10; 370 371-- 372-- Test case for bug with BEFORE trigger followed by AFTER trigger with WHEN 373-- 374 375CREATE TABLE some_t (some_col boolean NOT NULL); 376CREATE FUNCTION dummy_update_func() RETURNS trigger AS $$ 377BEGIN 378 RAISE NOTICE 'dummy_update_func(%) called: action = %, old = %, new = %', 379 TG_ARGV[0], TG_OP, OLD, NEW; 380 RETURN NEW; 381END; 382$$ LANGUAGE plpgsql; 383CREATE TRIGGER some_trig_before BEFORE UPDATE ON some_t FOR EACH ROW 384 EXECUTE PROCEDURE dummy_update_func('before'); 385CREATE TRIGGER some_trig_aftera AFTER UPDATE ON some_t FOR EACH ROW 386 WHEN (NOT OLD.some_col AND NEW.some_col) 387 EXECUTE PROCEDURE dummy_update_func('aftera'); 388CREATE TRIGGER some_trig_afterb AFTER UPDATE ON some_t FOR EACH ROW 389 WHEN (NOT NEW.some_col) 390 EXECUTE PROCEDURE dummy_update_func('afterb'); 391INSERT INTO some_t VALUES (TRUE); 392UPDATE some_t SET some_col = TRUE; 393UPDATE some_t SET some_col = FALSE; 394UPDATE some_t SET some_col = TRUE; 395DROP TABLE some_t; 396 397-- bogus cases 398CREATE TRIGGER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_table 399FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_and_col'); 400CREATE TRIGGER error_upd_a_a BEFORE UPDATE OF a, a ON main_table 401FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_a_a'); 402CREATE TRIGGER error_ins_a BEFORE INSERT OF a ON main_table 403FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_ins_a'); 404CREATE TRIGGER error_ins_when BEFORE INSERT OR UPDATE ON main_table 405FOR EACH ROW WHEN (OLD.a <> NEW.a) 406EXECUTE PROCEDURE trigger_func('error_ins_old'); 407CREATE TRIGGER error_del_when BEFORE DELETE OR UPDATE ON main_table 408FOR EACH ROW WHEN (OLD.a <> NEW.a) 409EXECUTE PROCEDURE trigger_func('error_del_new'); 410CREATE TRIGGER error_del_when BEFORE INSERT OR UPDATE ON main_table 411FOR EACH ROW WHEN (NEW.tableoid <> 0) 412EXECUTE PROCEDURE trigger_func('error_when_sys_column'); 413CREATE TRIGGER error_stmt_when BEFORE UPDATE OF a ON main_table 414FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*) 415EXECUTE PROCEDURE trigger_func('error_stmt_when'); 416 417-- check dependency restrictions 418ALTER TABLE main_table DROP COLUMN b; 419-- this should succeed, but we'll roll it back to keep the triggers around 420begin; 421DROP TRIGGER after_upd_a_b_row_trig ON main_table; 422DROP TRIGGER after_upd_b_row_trig ON main_table; 423DROP TRIGGER after_upd_b_stmt_trig ON main_table; 424ALTER TABLE main_table DROP COLUMN b; 425rollback; 426 427-- Test enable/disable triggers 428 429create table trigtest (i serial primary key); 430-- test that disabling RI triggers works 431create table trigtest2 (i int references trigtest(i) on delete cascade); 432 433create function trigtest() returns trigger as $$ 434begin 435 raise notice '% % % %', TG_RELNAME, TG_OP, TG_WHEN, TG_LEVEL; 436 return new; 437end;$$ language plpgsql; 438 439create trigger trigtest_b_row_tg before insert or update or delete on trigtest 440for each row execute procedure trigtest(); 441create trigger trigtest_a_row_tg after insert or update or delete on trigtest 442for each row execute procedure trigtest(); 443create trigger trigtest_b_stmt_tg before insert or update or delete on trigtest 444for each statement execute procedure trigtest(); 445create trigger trigtest_a_stmt_tg after insert or update or delete on trigtest 446for each statement execute procedure trigtest(); 447 448insert into trigtest default values; 449alter table trigtest disable trigger trigtest_b_row_tg; 450insert into trigtest default values; 451alter table trigtest disable trigger user; 452insert into trigtest default values; 453alter table trigtest enable trigger trigtest_a_stmt_tg; 454insert into trigtest default values; 455insert into trigtest2 values(1); 456insert into trigtest2 values(2); 457delete from trigtest where i=2; 458select * from trigtest2; 459alter table trigtest disable trigger all; 460delete from trigtest where i=1; 461select * from trigtest2; 462-- ensure we still insert, even when all triggers are disabled 463insert into trigtest default values; 464select * from trigtest; 465drop table trigtest2; 466drop table trigtest; 467 468 469-- dump trigger data 470CREATE TABLE trigger_test ( 471 i int, 472 v varchar 473); 474 475CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger 476LANGUAGE plpgsql AS $$ 477 478declare 479 480 argstr text; 481 relid text; 482 483begin 484 485 relid := TG_relid::regclass; 486 487 -- plpgsql can't discover its trigger data in a hash like perl and python 488 -- can, or by a sort of reflection like tcl can, 489 -- so we have to hard code the names. 490 raise NOTICE 'TG_NAME: %', TG_name; 491 raise NOTICE 'TG_WHEN: %', TG_when; 492 raise NOTICE 'TG_LEVEL: %', TG_level; 493 raise NOTICE 'TG_OP: %', TG_op; 494 raise NOTICE 'TG_RELID::regclass: %', relid; 495 raise NOTICE 'TG_RELNAME: %', TG_relname; 496 raise NOTICE 'TG_TABLE_NAME: %', TG_table_name; 497 raise NOTICE 'TG_TABLE_SCHEMA: %', TG_table_schema; 498 raise NOTICE 'TG_NARGS: %', TG_nargs; 499 500 argstr := '['; 501 for i in 0 .. TG_nargs - 1 loop 502 if i > 0 then 503 argstr := argstr || ', '; 504 end if; 505 argstr := argstr || TG_argv[i]; 506 end loop; 507 argstr := argstr || ']'; 508 raise NOTICE 'TG_ARGV: %', argstr; 509 510 if TG_OP != 'INSERT' then 511 raise NOTICE 'OLD: %', OLD; 512 end if; 513 514 if TG_OP != 'DELETE' then 515 raise NOTICE 'NEW: %', NEW; 516 end if; 517 518 if TG_OP = 'DELETE' then 519 return OLD; 520 else 521 return NEW; 522 end if; 523 524end; 525$$; 526 527CREATE TRIGGER show_trigger_data_trig 528BEFORE INSERT OR UPDATE OR DELETE ON trigger_test 529FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 530 531insert into trigger_test values(1,'insert'); 532update trigger_test set v = 'update' where i = 1; 533delete from trigger_test; 534 535DROP TRIGGER show_trigger_data_trig on trigger_test; 536 537DROP FUNCTION trigger_data(); 538 539DROP TABLE trigger_test; 540 541-- 542-- Test use of row comparisons on OLD/NEW 543-- 544 545CREATE TABLE trigger_test (f1 int, f2 text, f3 text); 546 547-- this is the obvious (and wrong...) way to compare rows 548CREATE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$ 549begin 550 if row(old.*) = row(new.*) then 551 raise notice 'row % not changed', new.f1; 552 else 553 raise notice 'row % changed', new.f1; 554 end if; 555 return new; 556end$$; 557 558CREATE TRIGGER t 559BEFORE UPDATE ON trigger_test 560FOR EACH ROW EXECUTE PROCEDURE mytrigger(); 561 562INSERT INTO trigger_test VALUES(1, 'foo', 'bar'); 563INSERT INTO trigger_test VALUES(2, 'baz', 'quux'); 564 565UPDATE trigger_test SET f3 = 'bar'; 566UPDATE trigger_test SET f3 = NULL; 567-- this demonstrates that the above isn't really working as desired: 568UPDATE trigger_test SET f3 = NULL; 569 570-- the right way when considering nulls is 571CREATE OR REPLACE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$ 572begin 573 if row(old.*) is distinct from row(new.*) then 574 raise notice 'row % changed', new.f1; 575 else 576 raise notice 'row % not changed', new.f1; 577 end if; 578 return new; 579end$$; 580 581UPDATE trigger_test SET f3 = 'bar'; 582UPDATE trigger_test SET f3 = NULL; 583UPDATE trigger_test SET f3 = NULL; 584 585DROP TABLE trigger_test; 586 587DROP FUNCTION mytrigger(); 588 589-- Test snapshot management in serializable transactions involving triggers 590-- per bug report in 6bc73d4c0910042358k3d1adff3qa36f8df75198ecea@mail.gmail.com 591CREATE FUNCTION serializable_update_trig() RETURNS trigger LANGUAGE plpgsql AS 592$$ 593declare 594 rec record; 595begin 596 new.description = 'updated in trigger'; 597 return new; 598end; 599$$; 600 601CREATE TABLE serializable_update_tab ( 602 id int, 603 filler text, 604 description text 605); 606 607CREATE TRIGGER serializable_update_trig BEFORE UPDATE ON serializable_update_tab 608 FOR EACH ROW EXECUTE PROCEDURE serializable_update_trig(); 609 610INSERT INTO serializable_update_tab SELECT a, repeat('xyzxz', 100), 'new' 611 FROM generate_series(1, 50) a; 612 613BEGIN; 614SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 615UPDATE serializable_update_tab SET description = 'no no', id = 1 WHERE id = 1; 616COMMIT; 617SELECT description FROM serializable_update_tab WHERE id = 1; 618DROP TABLE serializable_update_tab; 619 620-- minimal update trigger 621 622CREATE TABLE min_updates_test ( 623 f1 text, 624 f2 int, 625 f3 int); 626 627CREATE TABLE min_updates_test_oids ( 628 f1 text, 629 f2 int, 630 f3 int) WITH OIDS; 631 632INSERT INTO min_updates_test VALUES ('a',1,2),('b','2',null); 633 634INSERT INTO min_updates_test_oids VALUES ('a',1,2),('b','2',null); 635 636CREATE TRIGGER z_min_update 637BEFORE UPDATE ON min_updates_test 638FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); 639 640CREATE TRIGGER z_min_update 641BEFORE UPDATE ON min_updates_test_oids 642FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); 643 644\set QUIET false 645 646UPDATE min_updates_test SET f1 = f1; 647 648UPDATE min_updates_test SET f2 = f2 + 1; 649 650UPDATE min_updates_test SET f3 = 2 WHERE f3 is null; 651 652UPDATE min_updates_test_oids SET f1 = f1; 653 654UPDATE min_updates_test_oids SET f2 = f2 + 1; 655 656UPDATE min_updates_test_oids SET f3 = 2 WHERE f3 is null; 657 658\set QUIET true 659 660SELECT * FROM min_updates_test; 661 662SELECT * FROM min_updates_test_oids; 663 664DROP TABLE min_updates_test; 665 666DROP TABLE min_updates_test_oids; 667 668-- 669-- Test triggers on views 670-- 671 672CREATE VIEW main_view AS SELECT a, b FROM main_table; 673 674-- VIEW trigger function 675CREATE OR REPLACE FUNCTION view_trigger() RETURNS trigger 676LANGUAGE plpgsql AS $$ 677declare 678 argstr text := ''; 679begin 680 for i in 0 .. TG_nargs - 1 loop 681 if i > 0 then 682 argstr := argstr || ', '; 683 end if; 684 argstr := argstr || TG_argv[i]; 685 end loop; 686 687 raise notice '% % % % (%)', TG_RELNAME, TG_WHEN, TG_OP, TG_LEVEL, argstr; 688 689 if TG_LEVEL = 'ROW' then 690 if TG_OP = 'INSERT' then 691 raise NOTICE 'NEW: %', NEW; 692 INSERT INTO main_table VALUES (NEW.a, NEW.b); 693 RETURN NEW; 694 end if; 695 696 if TG_OP = 'UPDATE' then 697 raise NOTICE 'OLD: %, NEW: %', OLD, NEW; 698 UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b; 699 if NOT FOUND then RETURN NULL; end if; 700 RETURN NEW; 701 end if; 702 703 if TG_OP = 'DELETE' then 704 raise NOTICE 'OLD: %', OLD; 705 DELETE FROM main_table WHERE a = OLD.a AND b = OLD.b; 706 if NOT FOUND then RETURN NULL; end if; 707 RETURN OLD; 708 end if; 709 end if; 710 711 RETURN NULL; 712end; 713$$; 714 715-- Before row triggers aren't allowed on views 716CREATE TRIGGER invalid_trig BEFORE INSERT ON main_view 717FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row'); 718 719CREATE TRIGGER invalid_trig BEFORE UPDATE ON main_view 720FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row'); 721 722CREATE TRIGGER invalid_trig BEFORE DELETE ON main_view 723FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row'); 724 725-- After row triggers aren't allowed on views 726CREATE TRIGGER invalid_trig AFTER INSERT ON main_view 727FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row'); 728 729CREATE TRIGGER invalid_trig AFTER UPDATE ON main_view 730FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row'); 731 732CREATE TRIGGER invalid_trig AFTER DELETE ON main_view 733FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row'); 734 735-- Truncate triggers aren't allowed on views 736CREATE TRIGGER invalid_trig BEFORE TRUNCATE ON main_view 737EXECUTE PROCEDURE trigger_func('before_tru_row'); 738 739CREATE TRIGGER invalid_trig AFTER TRUNCATE ON main_view 740EXECUTE PROCEDURE trigger_func('before_tru_row'); 741 742-- INSTEAD OF triggers aren't allowed on tables 743CREATE TRIGGER invalid_trig INSTEAD OF INSERT ON main_table 744FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins'); 745 746CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_table 747FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd'); 748 749CREATE TRIGGER invalid_trig INSTEAD OF DELETE ON main_table 750FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del'); 751 752-- Don't support WHEN clauses with INSTEAD OF triggers 753CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view 754FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE view_trigger('instead_of_upd'); 755 756-- Don't support column-level INSTEAD OF triggers 757CREATE TRIGGER invalid_trig INSTEAD OF UPDATE OF a ON main_view 758FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd'); 759 760-- Don't support statement-level INSTEAD OF triggers 761CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view 762EXECUTE PROCEDURE view_trigger('instead_of_upd'); 763 764-- Valid INSTEAD OF triggers 765CREATE TRIGGER instead_of_insert_trig INSTEAD OF INSERT ON main_view 766FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins'); 767 768CREATE TRIGGER instead_of_update_trig INSTEAD OF UPDATE ON main_view 769FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd'); 770 771CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view 772FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del'); 773 774-- Valid BEFORE statement VIEW triggers 775CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view 776FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt'); 777 778CREATE TRIGGER before_upd_stmt_trig BEFORE UPDATE ON main_view 779FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt'); 780 781CREATE TRIGGER before_del_stmt_trig BEFORE DELETE ON main_view 782FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt'); 783 784-- Valid AFTER statement VIEW triggers 785CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_view 786FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt'); 787 788CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_view 789FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt'); 790 791CREATE TRIGGER after_del_stmt_trig AFTER DELETE ON main_view 792FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt'); 793 794\set QUIET false 795 796-- Insert into view using trigger 797INSERT INTO main_view VALUES (20, 30); 798INSERT INTO main_view VALUES (21, 31) RETURNING a, b; 799 800-- Table trigger will prevent updates 801UPDATE main_view SET b = 31 WHERE a = 20; 802UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b; 803 804-- Remove table trigger to allow updates 805DROP TRIGGER before_upd_a_row_trig ON main_table; 806UPDATE main_view SET b = 31 WHERE a = 20; 807UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b; 808 809-- Before and after stmt triggers should fire even when no rows are affected 810UPDATE main_view SET b = 0 WHERE false; 811 812-- Delete from view using trigger 813DELETE FROM main_view WHERE a IN (20,21); 814DELETE FROM main_view WHERE a = 31 RETURNING a, b; 815 816\set QUIET true 817 818-- Describe view should list triggers 819\d main_view 820 821-- Test dropping view triggers 822DROP TRIGGER instead_of_insert_trig ON main_view; 823DROP TRIGGER instead_of_delete_trig ON main_view; 824\d+ main_view 825DROP VIEW main_view; 826 827-- 828-- Test triggers on a join view 829-- 830CREATE TABLE country_table ( 831 country_id serial primary key, 832 country_name text unique not null, 833 continent text not null 834); 835 836INSERT INTO country_table (country_name, continent) 837 VALUES ('Japan', 'Asia'), 838 ('UK', 'Europe'), 839 ('USA', 'North America') 840 RETURNING *; 841 842CREATE TABLE city_table ( 843 city_id serial primary key, 844 city_name text not null, 845 population bigint, 846 country_id int references country_table 847); 848 849CREATE VIEW city_view AS 850 SELECT city_id, city_name, population, country_name, continent 851 FROM city_table ci 852 LEFT JOIN country_table co ON co.country_id = ci.country_id; 853 854CREATE FUNCTION city_insert() RETURNS trigger LANGUAGE plpgsql AS $$ 855declare 856 ctry_id int; 857begin 858 if NEW.country_name IS NOT NULL then 859 SELECT country_id, continent INTO ctry_id, NEW.continent 860 FROM country_table WHERE country_name = NEW.country_name; 861 if NOT FOUND then 862 raise exception 'No such country: "%"', NEW.country_name; 863 end if; 864 else 865 NEW.continent := NULL; 866 end if; 867 868 if NEW.city_id IS NOT NULL then 869 INSERT INTO city_table 870 VALUES(NEW.city_id, NEW.city_name, NEW.population, ctry_id); 871 else 872 INSERT INTO city_table(city_name, population, country_id) 873 VALUES(NEW.city_name, NEW.population, ctry_id) 874 RETURNING city_id INTO NEW.city_id; 875 end if; 876 877 RETURN NEW; 878end; 879$$; 880 881CREATE TRIGGER city_insert_trig INSTEAD OF INSERT ON city_view 882FOR EACH ROW EXECUTE PROCEDURE city_insert(); 883 884CREATE FUNCTION city_delete() RETURNS trigger LANGUAGE plpgsql AS $$ 885begin 886 DELETE FROM city_table WHERE city_id = OLD.city_id; 887 if NOT FOUND then RETURN NULL; end if; 888 RETURN OLD; 889end; 890$$; 891 892CREATE TRIGGER city_delete_trig INSTEAD OF DELETE ON city_view 893FOR EACH ROW EXECUTE PROCEDURE city_delete(); 894 895CREATE FUNCTION city_update() RETURNS trigger LANGUAGE plpgsql AS $$ 896declare 897 ctry_id int; 898begin 899 if NEW.country_name IS DISTINCT FROM OLD.country_name then 900 SELECT country_id, continent INTO ctry_id, NEW.continent 901 FROM country_table WHERE country_name = NEW.country_name; 902 if NOT FOUND then 903 raise exception 'No such country: "%"', NEW.country_name; 904 end if; 905 906 UPDATE city_table SET city_name = NEW.city_name, 907 population = NEW.population, 908 country_id = ctry_id 909 WHERE city_id = OLD.city_id; 910 else 911 UPDATE city_table SET city_name = NEW.city_name, 912 population = NEW.population 913 WHERE city_id = OLD.city_id; 914 NEW.continent := OLD.continent; 915 end if; 916 917 if NOT FOUND then RETURN NULL; end if; 918 RETURN NEW; 919end; 920$$; 921 922CREATE TRIGGER city_update_trig INSTEAD OF UPDATE ON city_view 923FOR EACH ROW EXECUTE PROCEDURE city_update(); 924 925\set QUIET false 926 927-- INSERT .. RETURNING 928INSERT INTO city_view(city_name) VALUES('Tokyo') RETURNING *; 929INSERT INTO city_view(city_name, population) VALUES('London', 7556900) RETURNING *; 930INSERT INTO city_view(city_name, country_name) VALUES('Washington DC', 'USA') RETURNING *; 931INSERT INTO city_view(city_id, city_name) VALUES(123456, 'New York') RETURNING *; 932INSERT INTO city_view VALUES(234567, 'Birmingham', 1016800, 'UK', 'EU') RETURNING *; 933 934-- UPDATE .. RETURNING 935UPDATE city_view SET country_name = 'Japon' WHERE city_name = 'Tokyo'; -- error 936UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Takyo'; -- no match 937UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Tokyo' RETURNING *; -- OK 938 939UPDATE city_view SET population = 13010279 WHERE city_name = 'Tokyo' RETURNING *; 940UPDATE city_view SET country_name = 'UK' WHERE city_name = 'New York' RETURNING *; 941UPDATE city_view SET country_name = 'USA', population = 8391881 WHERE city_name = 'New York' RETURNING *; 942UPDATE city_view SET continent = 'EU' WHERE continent = 'Europe' RETURNING *; 943UPDATE city_view v1 SET country_name = v2.country_name FROM city_view v2 944 WHERE v2.city_name = 'Birmingham' AND v1.city_name = 'London' RETURNING *; 945 946-- DELETE .. RETURNING 947DELETE FROM city_view WHERE city_name = 'Birmingham' RETURNING *; 948 949\set QUIET true 950 951-- read-only view with WHERE clause 952CREATE VIEW european_city_view AS 953 SELECT * FROM city_view WHERE continent = 'Europe'; 954SELECT count(*) FROM european_city_view; 955 956CREATE FUNCTION no_op_trig_fn() RETURNS trigger LANGUAGE plpgsql 957AS 'begin RETURN NULL; end'; 958 959CREATE TRIGGER no_op_trig INSTEAD OF INSERT OR UPDATE OR DELETE 960ON european_city_view FOR EACH ROW EXECUTE PROCEDURE no_op_trig_fn(); 961 962\set QUIET false 963 964INSERT INTO european_city_view VALUES (0, 'x', 10000, 'y', 'z'); 965UPDATE european_city_view SET population = 10000; 966DELETE FROM european_city_view; 967 968\set QUIET true 969 970-- rules bypassing no-op triggers 971CREATE RULE european_city_insert_rule AS ON INSERT TO european_city_view 972DO INSTEAD INSERT INTO city_view 973VALUES (NEW.city_id, NEW.city_name, NEW.population, NEW.country_name, NEW.continent) 974RETURNING *; 975 976CREATE RULE european_city_update_rule AS ON UPDATE TO european_city_view 977DO INSTEAD UPDATE city_view SET 978 city_name = NEW.city_name, 979 population = NEW.population, 980 country_name = NEW.country_name 981WHERE city_id = OLD.city_id 982RETURNING NEW.*; 983 984CREATE RULE european_city_delete_rule AS ON DELETE TO european_city_view 985DO INSTEAD DELETE FROM city_view WHERE city_id = OLD.city_id RETURNING *; 986 987\set QUIET false 988 989-- INSERT not limited by view's WHERE clause, but UPDATE AND DELETE are 990INSERT INTO european_city_view(city_name, country_name) 991 VALUES ('Cambridge', 'USA') RETURNING *; 992UPDATE european_city_view SET country_name = 'UK' 993 WHERE city_name = 'Cambridge'; 994DELETE FROM european_city_view WHERE city_name = 'Cambridge'; 995 996-- UPDATE and DELETE via rule and trigger 997UPDATE city_view SET country_name = 'UK' 998 WHERE city_name = 'Cambridge' RETURNING *; 999UPDATE european_city_view SET population = 122800 1000 WHERE city_name = 'Cambridge' RETURNING *; 1001DELETE FROM european_city_view WHERE city_name = 'Cambridge' RETURNING *; 1002 1003-- join UPDATE test 1004UPDATE city_view v SET population = 599657 1005 FROM city_table ci, country_table co 1006 WHERE ci.city_name = 'Washington DC' and co.country_name = 'USA' 1007 AND v.city_id = ci.city_id AND v.country_name = co.country_name 1008 RETURNING co.country_id, v.country_name, 1009 v.city_id, v.city_name, v.population; 1010 1011\set QUIET true 1012 1013SELECT * FROM city_view; 1014 1015DROP TABLE city_table CASCADE; 1016DROP TABLE country_table; 1017 1018 1019-- Test pg_trigger_depth() 1020 1021create table depth_a (id int not null primary key); 1022create table depth_b (id int not null primary key); 1023create table depth_c (id int not null primary key); 1024 1025create function depth_a_tf() returns trigger 1026 language plpgsql as $$ 1027begin 1028 raise notice '%: depth = %', tg_name, pg_trigger_depth(); 1029 insert into depth_b values (new.id); 1030 raise notice '%: depth = %', tg_name, pg_trigger_depth(); 1031 return new; 1032end; 1033$$; 1034create trigger depth_a_tr before insert on depth_a 1035 for each row execute procedure depth_a_tf(); 1036 1037create function depth_b_tf() returns trigger 1038 language plpgsql as $$ 1039begin 1040 raise notice '%: depth = %', tg_name, pg_trigger_depth(); 1041 begin 1042 execute 'insert into depth_c values (' || new.id::text || ')'; 1043 exception 1044 when sqlstate 'U9999' then 1045 raise notice 'SQLSTATE = U9999: depth = %', pg_trigger_depth(); 1046 end; 1047 raise notice '%: depth = %', tg_name, pg_trigger_depth(); 1048 if new.id = 1 then 1049 execute 'insert into depth_c values (' || new.id::text || ')'; 1050 end if; 1051 return new; 1052end; 1053$$; 1054create trigger depth_b_tr before insert on depth_b 1055 for each row execute procedure depth_b_tf(); 1056 1057create function depth_c_tf() returns trigger 1058 language plpgsql as $$ 1059begin 1060 raise notice '%: depth = %', tg_name, pg_trigger_depth(); 1061 if new.id = 1 then 1062 raise exception sqlstate 'U9999'; 1063 end if; 1064 raise notice '%: depth = %', tg_name, pg_trigger_depth(); 1065 return new; 1066end; 1067$$; 1068create trigger depth_c_tr before insert on depth_c 1069 for each row execute procedure depth_c_tf(); 1070 1071select pg_trigger_depth(); 1072insert into depth_a values (1); 1073select pg_trigger_depth(); 1074insert into depth_a values (2); 1075select pg_trigger_depth(); 1076 1077drop table depth_a, depth_b, depth_c; 1078drop function depth_a_tf(); 1079drop function depth_b_tf(); 1080drop function depth_c_tf(); 1081 1082-- 1083-- Test updates to rows during firing of BEFORE ROW triggers. 1084-- As of 9.2, such cases should be rejected (see bug #6123). 1085-- 1086 1087create temp table parent ( 1088 aid int not null primary key, 1089 val1 text, 1090 val2 text, 1091 val3 text, 1092 val4 text, 1093 bcnt int not null default 0); 1094create temp table child ( 1095 bid int not null primary key, 1096 aid int not null, 1097 val1 text); 1098 1099create function parent_upd_func() 1100 returns trigger language plpgsql as 1101$$ 1102begin 1103 if old.val1 <> new.val1 then 1104 new.val2 = new.val1; 1105 delete from child where child.aid = new.aid and child.val1 = new.val1; 1106 end if; 1107 return new; 1108end; 1109$$; 1110create trigger parent_upd_trig before update on parent 1111 for each row execute procedure parent_upd_func(); 1112 1113create function parent_del_func() 1114 returns trigger language plpgsql as 1115$$ 1116begin 1117 delete from child where aid = old.aid; 1118 return old; 1119end; 1120$$; 1121create trigger parent_del_trig before delete on parent 1122 for each row execute procedure parent_del_func(); 1123 1124create function child_ins_func() 1125 returns trigger language plpgsql as 1126$$ 1127begin 1128 update parent set bcnt = bcnt + 1 where aid = new.aid; 1129 return new; 1130end; 1131$$; 1132create trigger child_ins_trig after insert on child 1133 for each row execute procedure child_ins_func(); 1134 1135create function child_del_func() 1136 returns trigger language plpgsql as 1137$$ 1138begin 1139 update parent set bcnt = bcnt - 1 where aid = old.aid; 1140 return old; 1141end; 1142$$; 1143create trigger child_del_trig after delete on child 1144 for each row execute procedure child_del_func(); 1145 1146insert into parent values (1, 'a', 'a', 'a', 'a', 0); 1147insert into child values (10, 1, 'b'); 1148select * from parent; select * from child; 1149 1150update parent set val1 = 'b' where aid = 1; -- should fail 1151select * from parent; select * from child; 1152 1153delete from parent where aid = 1; -- should fail 1154select * from parent; select * from child; 1155 1156-- replace the trigger function with one that restarts the deletion after 1157-- having modified a child 1158create or replace function parent_del_func() 1159 returns trigger language plpgsql as 1160$$ 1161begin 1162 delete from child where aid = old.aid; 1163 if found then 1164 delete from parent where aid = old.aid; 1165 return null; -- cancel outer deletion 1166 end if; 1167 return old; 1168end; 1169$$; 1170 1171delete from parent where aid = 1; 1172select * from parent; select * from child; 1173 1174drop table parent, child; 1175 1176drop function parent_upd_func(); 1177drop function parent_del_func(); 1178drop function child_ins_func(); 1179drop function child_del_func(); 1180 1181-- similar case, but with a self-referencing FK so that parent and child 1182-- rows can be affected by a single operation 1183 1184create temp table self_ref_trigger ( 1185 id int primary key, 1186 parent int references self_ref_trigger, 1187 data text, 1188 nchildren int not null default 0 1189); 1190 1191create function self_ref_trigger_ins_func() 1192 returns trigger language plpgsql as 1193$$ 1194begin 1195 if new.parent is not null then 1196 update self_ref_trigger set nchildren = nchildren + 1 1197 where id = new.parent; 1198 end if; 1199 return new; 1200end; 1201$$; 1202create trigger self_ref_trigger_ins_trig before insert on self_ref_trigger 1203 for each row execute procedure self_ref_trigger_ins_func(); 1204 1205create function self_ref_trigger_del_func() 1206 returns trigger language plpgsql as 1207$$ 1208begin 1209 if old.parent is not null then 1210 update self_ref_trigger set nchildren = nchildren - 1 1211 where id = old.parent; 1212 end if; 1213 return old; 1214end; 1215$$; 1216create trigger self_ref_trigger_del_trig before delete on self_ref_trigger 1217 for each row execute procedure self_ref_trigger_del_func(); 1218 1219insert into self_ref_trigger values (1, null, 'root'); 1220insert into self_ref_trigger values (2, 1, 'root child A'); 1221insert into self_ref_trigger values (3, 1, 'root child B'); 1222insert into self_ref_trigger values (4, 2, 'grandchild 1'); 1223insert into self_ref_trigger values (5, 3, 'grandchild 2'); 1224 1225update self_ref_trigger set data = 'root!' where id = 1; 1226 1227select * from self_ref_trigger; 1228 1229delete from self_ref_trigger; 1230 1231select * from self_ref_trigger; 1232 1233drop table self_ref_trigger; 1234drop function self_ref_trigger_ins_func(); 1235drop function self_ref_trigger_del_func(); 1236 1237-- 1238-- Check that statement triggers work correctly even with all children excluded 1239-- 1240 1241create table stmt_trig_on_empty_upd (a int); 1242create table stmt_trig_on_empty_upd1 () inherits (stmt_trig_on_empty_upd); 1243create function update_stmt_notice() returns trigger as $$ 1244begin 1245 raise notice 'updating %', TG_TABLE_NAME; 1246 return null; 1247end; 1248$$ language plpgsql; 1249create trigger before_stmt_trigger 1250 before update on stmt_trig_on_empty_upd 1251 execute procedure update_stmt_notice(); 1252create trigger before_stmt_trigger 1253 before update on stmt_trig_on_empty_upd1 1254 execute procedure update_stmt_notice(); 1255 1256-- inherited no-op update 1257update stmt_trig_on_empty_upd set a = a where false returning a+1 as aa; 1258-- simple no-op update 1259update stmt_trig_on_empty_upd1 set a = a where false returning a+1 as aa; 1260 1261drop table stmt_trig_on_empty_upd cascade; 1262drop function update_stmt_notice(); 1263 1264-- 1265-- Check that index creation (or DDL in general) is prohibited in a trigger 1266-- 1267 1268create table trigger_ddl_table ( 1269 col1 integer, 1270 col2 integer 1271); 1272 1273create function trigger_ddl_func() returns trigger as $$ 1274begin 1275 alter table trigger_ddl_table add primary key (col1); 1276 return new; 1277end$$ language plpgsql; 1278 1279create trigger trigger_ddl_func before insert on trigger_ddl_table for each row 1280 execute procedure trigger_ddl_func(); 1281 1282insert into trigger_ddl_table values (1, 42); -- fail 1283 1284create or replace function trigger_ddl_func() returns trigger as $$ 1285begin 1286 create index on trigger_ddl_table (col2); 1287 return new; 1288end$$ language plpgsql; 1289 1290insert into trigger_ddl_table values (1, 42); -- fail 1291 1292drop table trigger_ddl_table; 1293drop function trigger_ddl_func(); 1294 1295-- 1296-- Verify behavior of before and after triggers with INSERT...ON CONFLICT 1297-- DO UPDATE 1298-- 1299create table upsert (key int4 primary key, color text); 1300 1301create function upsert_before_func() 1302 returns trigger language plpgsql as 1303$$ 1304begin 1305 if (TG_OP = 'UPDATE') then 1306 raise warning 'before update (old): %', old.*::text; 1307 raise warning 'before update (new): %', new.*::text; 1308 elsif (TG_OP = 'INSERT') then 1309 raise warning 'before insert (new): %', new.*::text; 1310 if new.key % 2 = 0 then 1311 new.key := new.key + 1; 1312 new.color := new.color || ' trig modified'; 1313 raise warning 'before insert (new, modified): %', new.*::text; 1314 end if; 1315 end if; 1316 return new; 1317end; 1318$$; 1319create trigger upsert_before_trig before insert or update on upsert 1320 for each row execute procedure upsert_before_func(); 1321 1322create function upsert_after_func() 1323 returns trigger language plpgsql as 1324$$ 1325begin 1326 if (TG_OP = 'UPDATE') then 1327 raise warning 'after update (old): %', old.*::text; 1328 raise warning 'after update (new): %', new.*::text; 1329 elsif (TG_OP = 'INSERT') then 1330 raise warning 'after insert (new): %', new.*::text; 1331 end if; 1332 return null; 1333end; 1334$$; 1335create trigger upsert_after_trig after insert or update on upsert 1336 for each row execute procedure upsert_after_func(); 1337 1338insert into upsert values(1, 'black') on conflict (key) do update set color = 'updated ' || upsert.color; 1339insert into upsert values(2, 'red') on conflict (key) do update set color = 'updated ' || upsert.color; 1340insert into upsert values(3, 'orange') on conflict (key) do update set color = 'updated ' || upsert.color; 1341insert into upsert values(4, 'green') on conflict (key) do update set color = 'updated ' || upsert.color; 1342insert into upsert values(5, 'purple') on conflict (key) do update set color = 'updated ' || upsert.color; 1343insert into upsert values(6, 'white') on conflict (key) do update set color = 'updated ' || upsert.color; 1344insert into upsert values(7, 'pink') on conflict (key) do update set color = 'updated ' || upsert.color; 1345insert into upsert values(8, 'yellow') on conflict (key) do update set color = 'updated ' || upsert.color; 1346 1347select * from upsert; 1348 1349drop table upsert; 1350drop function upsert_before_func(); 1351drop function upsert_after_func(); 1352 1353-- 1354-- Verify that triggers with transition tables are not allowed on 1355-- views 1356-- 1357 1358create table my_table (i int); 1359create view my_view as select * from my_table; 1360create function my_trigger_function() returns trigger as $$ begin end; $$ language plpgsql; 1361create trigger my_trigger after update on my_view referencing old table as old_table 1362 for each statement execute procedure my_trigger_function(); 1363drop function my_trigger_function(); 1364drop view my_view; 1365drop table my_table; 1366 1367-- 1368-- Verify that per-statement triggers are fired for partitioned tables 1369-- 1370create table parted_stmt_trig (a int) partition by list (a); 1371create table parted_stmt_trig1 partition of parted_stmt_trig for values in (1); 1372create table parted_stmt_trig2 partition of parted_stmt_trig for values in (2); 1373 1374create table parted2_stmt_trig (a int) partition by list (a); 1375create table parted2_stmt_trig1 partition of parted2_stmt_trig for values in (1); 1376create table parted2_stmt_trig2 partition of parted2_stmt_trig for values in (2); 1377 1378create or replace function trigger_notice() returns trigger as $$ 1379 begin 1380 raise notice 'trigger on % % % for %', TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL; 1381 if TG_LEVEL = 'ROW' then 1382 return NEW; 1383 end if; 1384 return null; 1385 end; 1386 $$ language plpgsql; 1387 1388-- insert/update/delete statment-level triggers on the parent 1389create trigger trig_ins_before before insert on parted_stmt_trig 1390 for each statement execute procedure trigger_notice(); 1391create trigger trig_ins_after after insert on parted_stmt_trig 1392 for each statement execute procedure trigger_notice(); 1393create trigger trig_upd_before before update on parted_stmt_trig 1394 for each statement execute procedure trigger_notice(); 1395create trigger trig_upd_after after update on parted_stmt_trig 1396 for each statement execute procedure trigger_notice(); 1397create trigger trig_del_before before delete on parted_stmt_trig 1398 for each statement execute procedure trigger_notice(); 1399create trigger trig_del_after after delete on parted_stmt_trig 1400 for each statement execute procedure trigger_notice(); 1401 1402-- insert/update/delete row-level triggers on the first partition 1403create trigger trig_ins_before before insert on parted_stmt_trig1 1404 for each row execute procedure trigger_notice(); 1405create trigger trig_ins_after after insert on parted_stmt_trig1 1406 for each row execute procedure trigger_notice(); 1407create trigger trig_upd_before before update on parted_stmt_trig1 1408 for each row execute procedure trigger_notice(); 1409create trigger trig_upd_after after update on parted_stmt_trig1 1410 for each row execute procedure trigger_notice(); 1411 1412-- insert/update/delete statement-level triggers on the parent 1413create trigger trig_ins_before before insert on parted2_stmt_trig 1414 for each statement execute procedure trigger_notice(); 1415create trigger trig_ins_after after insert on parted2_stmt_trig 1416 for each statement execute procedure trigger_notice(); 1417create trigger trig_upd_before before update on parted2_stmt_trig 1418 for each statement execute procedure trigger_notice(); 1419create trigger trig_upd_after after update on parted2_stmt_trig 1420 for each statement execute procedure trigger_notice(); 1421create trigger trig_del_before before delete on parted2_stmt_trig 1422 for each statement execute procedure trigger_notice(); 1423create trigger trig_del_after after delete on parted2_stmt_trig 1424 for each statement execute procedure trigger_notice(); 1425 1426with ins (a) as ( 1427 insert into parted2_stmt_trig values (1), (2) returning a 1428) insert into parted_stmt_trig select a from ins returning tableoid::regclass, a; 1429 1430with upd as ( 1431 update parted2_stmt_trig set a = a 1432) update parted_stmt_trig set a = a; 1433 1434delete from parted_stmt_trig; 1435 1436-- insert via copy on the parent 1437copy parted_stmt_trig(a) from stdin; 14381 14392 1440\. 1441 1442-- insert via copy on the first partition 1443copy parted_stmt_trig1(a) from stdin; 14441 1445\. 1446 1447drop table parted_stmt_trig, parted2_stmt_trig; 1448 1449-- 1450-- Test the interaction between transition tables and both kinds of 1451-- inheritance. We'll dump the contents of the transition tables in a 1452-- format that shows the attribute order, so that we can distinguish 1453-- tuple formats (though not dropped attributes). 1454-- 1455 1456create or replace function dump_insert() returns trigger language plpgsql as 1457$$ 1458 begin 1459 raise notice 'trigger = %, new table = %', 1460 TG_NAME, 1461 (select string_agg(new_table::text, ', ' order by a) from new_table); 1462 return null; 1463 end; 1464$$; 1465 1466create or replace function dump_update() returns trigger language plpgsql as 1467$$ 1468 begin 1469 raise notice 'trigger = %, old table = %, new table = %', 1470 TG_NAME, 1471 (select string_agg(old_table::text, ', ' order by a) from old_table), 1472 (select string_agg(new_table::text, ', ' order by a) from new_table); 1473 return null; 1474 end; 1475$$; 1476 1477create or replace function dump_delete() returns trigger language plpgsql as 1478$$ 1479 begin 1480 raise notice 'trigger = %, old table = %', 1481 TG_NAME, 1482 (select string_agg(old_table::text, ', ' order by a) from old_table); 1483 return null; 1484 end; 1485$$; 1486 1487-- 1488-- Verify behavior of statement triggers on partition hierarchy with 1489-- transition tables. Tuples should appear to each trigger in the 1490-- format of the the relation the trigger is attached to. 1491-- 1492 1493-- set up a partition hierarchy with some different TupleDescriptors 1494create table parent (a text, b int) partition by list (a); 1495 1496-- a child matching parent 1497create table child1 partition of parent for values in ('AAA'); 1498 1499-- a child with a dropped column 1500create table child2 (x int, a text, b int); 1501alter table child2 drop column x; 1502alter table parent attach partition child2 for values in ('BBB'); 1503 1504-- a child with a different column order 1505create table child3 (b int, a text); 1506alter table parent attach partition child3 for values in ('CCC'); 1507 1508create trigger parent_insert_trig 1509 after insert on parent referencing new table as new_table 1510 for each statement execute procedure dump_insert(); 1511create trigger parent_update_trig 1512 after update on parent referencing old table as old_table new table as new_table 1513 for each statement execute procedure dump_update(); 1514create trigger parent_delete_trig 1515 after delete on parent referencing old table as old_table 1516 for each statement execute procedure dump_delete(); 1517 1518create trigger child1_insert_trig 1519 after insert on child1 referencing new table as new_table 1520 for each statement execute procedure dump_insert(); 1521create trigger child1_update_trig 1522 after update on child1 referencing old table as old_table new table as new_table 1523 for each statement execute procedure dump_update(); 1524create trigger child1_delete_trig 1525 after delete on child1 referencing old table as old_table 1526 for each statement execute procedure dump_delete(); 1527 1528create trigger child2_insert_trig 1529 after insert on child2 referencing new table as new_table 1530 for each statement execute procedure dump_insert(); 1531create trigger child2_update_trig 1532 after update on child2 referencing old table as old_table new table as new_table 1533 for each statement execute procedure dump_update(); 1534create trigger child2_delete_trig 1535 after delete on child2 referencing old table as old_table 1536 for each statement execute procedure dump_delete(); 1537 1538create trigger child3_insert_trig 1539 after insert on child3 referencing new table as new_table 1540 for each statement execute procedure dump_insert(); 1541create trigger child3_update_trig 1542 after update on child3 referencing old table as old_table new table as new_table 1543 for each statement execute procedure dump_update(); 1544create trigger child3_delete_trig 1545 after delete on child3 referencing old table as old_table 1546 for each statement execute procedure dump_delete(); 1547 1548-- insert directly into children sees respective child-format tuples 1549insert into child1 values ('AAA', 42); 1550insert into child2 values ('BBB', 42); 1551insert into child3 values (42, 'CCC'); 1552 1553-- update via parent sees parent-format tuples 1554update parent set b = b + 1; 1555 1556-- delete via parent sees parent-format tuples 1557delete from parent; 1558 1559-- insert into parent sees parent-format tuples 1560insert into parent values ('AAA', 42); 1561insert into parent values ('BBB', 42); 1562insert into parent values ('CCC', 42); 1563 1564-- delete from children sees respective child-format tuples 1565delete from child1; 1566delete from child2; 1567delete from child3; 1568 1569-- copy into parent sees parent-format tuples 1570copy parent (a, b) from stdin; 1571AAA 42 1572BBB 42 1573CCC 42 1574\. 1575 1576-- DML affecting parent sees tuples collected from children even if 1577-- there is no transition table trigger on the children 1578drop trigger child1_insert_trig on child1; 1579drop trigger child1_update_trig on child1; 1580drop trigger child1_delete_trig on child1; 1581drop trigger child2_insert_trig on child2; 1582drop trigger child2_update_trig on child2; 1583drop trigger child2_delete_trig on child2; 1584drop trigger child3_insert_trig on child3; 1585drop trigger child3_update_trig on child3; 1586drop trigger child3_delete_trig on child3; 1587delete from parent; 1588 1589-- copy into parent sees tuples collected from children even if there 1590-- is no transition-table trigger on the children 1591copy parent (a, b) from stdin; 1592AAA 42 1593BBB 42 1594CCC 42 1595\. 1596 1597-- insert into parent with a before trigger on a child tuple before 1598-- insertion, and we capture the newly modified row in parent format 1599create or replace function intercept_insert() returns trigger language plpgsql as 1600$$ 1601 begin 1602 new.b = new.b + 1000; 1603 return new; 1604 end; 1605$$; 1606 1607create trigger intercept_insert_child3 1608 before insert on child3 1609 for each row execute procedure intercept_insert(); 1610 1611 1612-- insert, parent trigger sees post-modification parent-format tuple 1613insert into parent values ('AAA', 42), ('BBB', 42), ('CCC', 66); 1614 1615-- copy, parent trigger sees post-modification parent-format tuple 1616copy parent (a, b) from stdin; 1617AAA 42 1618BBB 42 1619CCC 234 1620\. 1621 1622drop table child1, child2, child3, parent; 1623drop function intercept_insert(); 1624 1625-- 1626-- Verify prohibition of row triggers with transition triggers on 1627-- partitions 1628-- 1629create table parent (a text, b int) partition by list (a); 1630create table child partition of parent for values in ('AAA'); 1631 1632-- adding row trigger with transition table fails 1633create trigger child_row_trig 1634 after insert on child referencing new table as new_table 1635 for each row execute procedure dump_insert(); 1636 1637-- detaching it first works 1638alter table parent detach partition child; 1639 1640create trigger child_row_trig 1641 after insert on child referencing new table as new_table 1642 for each row execute procedure dump_insert(); 1643 1644-- but now we're not allowed to reattach it 1645alter table parent attach partition child for values in ('AAA'); 1646 1647-- drop the trigger, and now we're allowed to attach it again 1648drop trigger child_row_trig on child; 1649alter table parent attach partition child for values in ('AAA'); 1650 1651drop table child, parent; 1652 1653-- 1654-- Verify behavior of statement triggers on (non-partition) 1655-- inheritance hierarchy with transition tables; similar to the 1656-- partition case, except there is no rerouting on insertion and child 1657-- tables can have extra columns 1658-- 1659 1660-- set up inheritance hierarchy with different TupleDescriptors 1661create table parent (a text, b int); 1662 1663-- a child matching parent 1664create table child1 () inherits (parent); 1665 1666-- a child with a different column order 1667create table child2 (b int, a text); 1668alter table child2 inherit parent; 1669 1670-- a child with an extra column 1671create table child3 (c text) inherits (parent); 1672 1673create trigger parent_insert_trig 1674 after insert on parent referencing new table as new_table 1675 for each statement execute procedure dump_insert(); 1676create trigger parent_update_trig 1677 after update on parent referencing old table as old_table new table as new_table 1678 for each statement execute procedure dump_update(); 1679create trigger parent_delete_trig 1680 after delete on parent referencing old table as old_table 1681 for each statement execute procedure dump_delete(); 1682 1683create trigger child1_insert_trig 1684 after insert on child1 referencing new table as new_table 1685 for each statement execute procedure dump_insert(); 1686create trigger child1_update_trig 1687 after update on child1 referencing old table as old_table new table as new_table 1688 for each statement execute procedure dump_update(); 1689create trigger child1_delete_trig 1690 after delete on child1 referencing old table as old_table 1691 for each statement execute procedure dump_delete(); 1692 1693create trigger child2_insert_trig 1694 after insert on child2 referencing new table as new_table 1695 for each statement execute procedure dump_insert(); 1696create trigger child2_update_trig 1697 after update on child2 referencing old table as old_table new table as new_table 1698 for each statement execute procedure dump_update(); 1699create trigger child2_delete_trig 1700 after delete on child2 referencing old table as old_table 1701 for each statement execute procedure dump_delete(); 1702 1703create trigger child3_insert_trig 1704 after insert on child3 referencing new table as new_table 1705 for each statement execute procedure dump_insert(); 1706create trigger child3_update_trig 1707 after update on child3 referencing old table as old_table new table as new_table 1708 for each statement execute procedure dump_update(); 1709create trigger child3_delete_trig 1710 after delete on child3 referencing old table as old_table 1711 for each statement execute procedure dump_delete(); 1712 1713-- insert directly into children sees respective child-format tuples 1714insert into child1 values ('AAA', 42); 1715insert into child2 values (42, 'BBB'); 1716insert into child3 values ('CCC', 42, 'foo'); 1717 1718-- update via parent sees parent-format tuples 1719update parent set b = b + 1; 1720 1721-- delete via parent sees parent-format tuples 1722delete from parent; 1723 1724-- reinsert values into children for next test... 1725insert into child1 values ('AAA', 42); 1726insert into child2 values (42, 'BBB'); 1727insert into child3 values ('CCC', 42, 'foo'); 1728 1729-- delete from children sees respective child-format tuples 1730delete from child1; 1731delete from child2; 1732delete from child3; 1733 1734-- copy into parent sees parent-format tuples (no rerouting, so these 1735-- are really inserted into the parent) 1736copy parent (a, b) from stdin; 1737AAA 42 1738BBB 42 1739CCC 42 1740\. 1741 1742-- same behavior for copy if there is an index (interesting because rows are 1743-- captured by a different code path in copy.c if there are indexes) 1744create index on parent(b); 1745copy parent (a, b) from stdin; 1746DDD 42 1747\. 1748 1749-- DML affecting parent sees tuples collected from children even if 1750-- there is no transition table trigger on the children 1751drop trigger child1_insert_trig on child1; 1752drop trigger child1_update_trig on child1; 1753drop trigger child1_delete_trig on child1; 1754drop trigger child2_insert_trig on child2; 1755drop trigger child2_update_trig on child2; 1756drop trigger child2_delete_trig on child2; 1757drop trigger child3_insert_trig on child3; 1758drop trigger child3_update_trig on child3; 1759drop trigger child3_delete_trig on child3; 1760delete from parent; 1761 1762drop table child1, child2, child3, parent; 1763 1764-- 1765-- Verify prohibition of row triggers with transition triggers on 1766-- inheritance children 1767-- 1768create table parent (a text, b int); 1769create table child () inherits (parent); 1770 1771-- adding row trigger with transition table fails 1772create trigger child_row_trig 1773 after insert on child referencing new table as new_table 1774 for each row execute procedure dump_insert(); 1775 1776-- disinheriting it first works 1777alter table child no inherit parent; 1778 1779create trigger child_row_trig 1780 after insert on child referencing new table as new_table 1781 for each row execute procedure dump_insert(); 1782 1783-- but now we're not allowed to make it inherit anymore 1784alter table child inherit parent; 1785 1786-- drop the trigger, and now we're allowed to make it inherit again 1787drop trigger child_row_trig on child; 1788alter table child inherit parent; 1789 1790drop table child, parent; 1791 1792-- 1793-- Verify behavior of queries with wCTEs, where multiple transition 1794-- tuplestores can be active at the same time because there are 1795-- multiple DML statements that might fire triggers with transition 1796-- tables 1797-- 1798create table table1 (a int); 1799create table table2 (a text); 1800create trigger table1_trig 1801 after insert on table1 referencing new table as new_table 1802 for each statement execute procedure dump_insert(); 1803create trigger table2_trig 1804 after insert on table2 referencing new table as new_table 1805 for each statement execute procedure dump_insert(); 1806 1807with wcte as (insert into table1 values (42)) 1808 insert into table2 values ('hello world'); 1809 1810with wcte as (insert into table1 values (43)) 1811 insert into table1 values (44); 1812 1813select * from table1; 1814select * from table2; 1815 1816drop table table1; 1817drop table table2; 1818 1819-- 1820-- Verify behavior of INSERT ... ON CONFLICT DO UPDATE ... with 1821-- transition tables. 1822-- 1823 1824create table my_table (a int primary key, b text); 1825create trigger my_table_insert_trig 1826 after insert on my_table referencing new table as new_table 1827 for each statement execute procedure dump_insert(); 1828create trigger my_table_update_trig 1829 after update on my_table referencing old table as old_table new table as new_table 1830 for each statement execute procedure dump_update(); 1831 1832-- inserts only 1833insert into my_table values (1, 'AAA'), (2, 'BBB') 1834 on conflict (a) do 1835 update set b = my_table.b || ':' || excluded.b; 1836 1837-- mixture of inserts and updates 1838insert into my_table values (1, 'AAA'), (2, 'BBB'), (3, 'CCC'), (4, 'DDD') 1839 on conflict (a) do 1840 update set b = my_table.b || ':' || excluded.b; 1841 1842-- updates only 1843insert into my_table values (3, 'CCC'), (4, 'DDD') 1844 on conflict (a) do 1845 update set b = my_table.b || ':' || excluded.b; 1846 1847-- 1848-- Verify that you can't create a trigger with transition tables for 1849-- more than one event. 1850-- 1851 1852create trigger my_table_multievent_trig 1853 after insert or update on my_table referencing new table as new_table 1854 for each statement execute procedure dump_insert(); 1855 1856-- 1857-- Verify that you can't create a trigger with transition tables with 1858-- a column list. 1859-- 1860 1861create trigger my_table_col_update_trig 1862 after update of b on my_table referencing new table as new_table 1863 for each statement execute procedure dump_insert(); 1864 1865drop table my_table; 1866 1867-- 1868-- Test firing of triggers with transition tables by foreign key cascades 1869-- 1870 1871create table refd_table (a int primary key, b text); 1872create table trig_table (a int, b text, 1873 foreign key (a) references refd_table on update cascade on delete cascade 1874); 1875 1876create trigger trig_table_before_trig 1877 before insert or update or delete on trig_table 1878 for each statement execute procedure trigger_func('trig_table'); 1879create trigger trig_table_insert_trig 1880 after insert on trig_table referencing new table as new_table 1881 for each statement execute procedure dump_insert(); 1882create trigger trig_table_update_trig 1883 after update on trig_table referencing old table as old_table new table as new_table 1884 for each statement execute procedure dump_update(); 1885create trigger trig_table_delete_trig 1886 after delete on trig_table referencing old table as old_table 1887 for each statement execute procedure dump_delete(); 1888 1889insert into refd_table values 1890 (1, 'one'), 1891 (2, 'two'), 1892 (3, 'three'); 1893insert into trig_table values 1894 (1, 'one a'), 1895 (1, 'one b'), 1896 (2, 'two a'), 1897 (2, 'two b'), 1898 (3, 'three a'), 1899 (3, 'three b'); 1900 1901update refd_table set a = 11 where b = 'one'; 1902 1903select * from trig_table; 1904 1905delete from refd_table where length(b) = 3; 1906 1907select * from trig_table; 1908 1909drop table refd_table, trig_table; 1910 1911-- 1912-- self-referential FKs are even more fun 1913-- 1914 1915create table self_ref (a int primary key, 1916 b int references self_ref(a) on delete cascade); 1917 1918create trigger self_ref_before_trig 1919 before delete on self_ref 1920 for each statement execute procedure trigger_func('self_ref'); 1921create trigger self_ref_r_trig 1922 after delete on self_ref referencing old table as old_table 1923 for each row execute procedure dump_delete(); 1924create trigger self_ref_s_trig 1925 after delete on self_ref referencing old table as old_table 1926 for each statement execute procedure dump_delete(); 1927 1928insert into self_ref values (1, null), (2, 1), (3, 2); 1929 1930delete from self_ref where a = 1; 1931 1932-- without AR trigger, cascaded deletes all end up in one transition table 1933drop trigger self_ref_r_trig on self_ref; 1934 1935insert into self_ref values (1, null), (2, 1), (3, 2), (4, 3); 1936 1937delete from self_ref where a = 1; 1938 1939drop table self_ref; 1940 1941-- cleanup 1942drop function dump_insert(); 1943drop function dump_update(); 1944drop function dump_delete(); 1945