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