1-- suppress CONTEXT so that function OIDs aren't in output 2\set VERBOSITY terse 3 4-- 5-- Create the tables used in the test queries 6-- 7-- T_pkey1 is the primary key table for T_dta1. Entries from T_pkey1 8-- Cannot be changed or deleted if they are referenced from T_dta1. 9-- 10-- T_pkey2 is the primary key table for T_dta2. If the key values in 11-- T_pkey2 are changed, the references in T_dta2 follow. If entries 12-- are deleted, the referencing entries from T_dta2 are deleted too. 13-- The values for field key2 in T_pkey2 are silently converted to 14-- upper case on insert/update. 15-- 16create table T_pkey1 ( 17 key1 int4, 18 key2 char(20), 19 txt char(40) 20); 21 22create table T_pkey2 ( 23 key1 int4, 24 key2 char(20), 25 txt char(40) 26); 27 28create table T_dta1 ( 29 tkey char(10), 30 ref1 int4, 31 ref2 char(20) 32); 33 34create table T_dta2 ( 35 tkey char(10), 36 ref1 int4, 37 ref2 char(20) 38); 39 40 41-- 42-- Function to check key existence in T_pkey1 43-- 44create function check_pkey1_exists(int4, bpchar) returns bool as E' 45 if {![info exists GD]} { 46 set GD(plan) [spi_prepare \\ 47 "select 1 from T_pkey1 \\ 48 where key1 = \\$1 and key2 = \\$2" \\ 49 {int4 bpchar}] 50 } 51 52 set n [spi_execp -count 1 $GD(plan) [list $1 $2]] 53 54 if {$n > 0} { 55 return "t" 56 } 57 return "f" 58' language pltcl; 59 60 61-- dump trigger data 62 63CREATE TABLE trigger_test ( 64 i int, 65 v text, 66 dropme text, 67 test_skip boolean DEFAULT false, 68 test_return_null boolean DEFAULT false, 69 test_argisnull boolean DEFAULT false 70); 71-- Make certain dropped attributes are handled correctly 72ALTER TABLE trigger_test DROP dropme; 73 74CREATE TABLE trigger_test_generated ( 75 i int, 76 j int GENERATED ALWAYS AS (i * 2) STORED 77); 78 79CREATE VIEW trigger_test_view AS SELECT i, v FROM trigger_test; 80 81CREATE FUNCTION trigger_data() returns trigger language pltcl as $_$ 82 if {$TG_table_name eq "trigger_test" && $TG_level eq "ROW" && $TG_op ne "DELETE"} { 83 # Special case tests 84 if {$NEW(test_return_null) eq "t" } { 85 return_null 86 } 87 if {$NEW(test_argisnull) eq "t" } { 88 set should_error [argisnull 1] 89 } 90 if {$NEW(test_skip) eq "t" } { 91 elog NOTICE "SKIPPING OPERATION $TG_op" 92 return SKIP 93 } 94 } 95 96 if { [info exists TG_relid] } { 97 set TG_relid "bogus:12345" 98 } 99 100 set dnames [info locals {[a-zA-Z]*} ] 101 102 foreach key [lsort $dnames] { 103 104 if { [array exists $key] } { 105 set str "{" 106 foreach akey [lsort [ array names $key ] ] { 107 if {[string length $str] > 1} { set str "$str, " } 108 set cmd "($akey)" 109 set cmd "set val \$$key$cmd" 110 eval $cmd 111 set str "$str$akey: $val" 112 } 113 set str "$str}" 114 elog NOTICE "$key: $str" 115 } else { 116 set val [eval list "\$$key" ] 117 elog NOTICE "$key: $val" 118 } 119 } 120 121 122 return OK 123 124$_$; 125 126CREATE TRIGGER show_trigger_data_trig 127BEFORE INSERT OR UPDATE OR DELETE ON trigger_test 128FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 129CREATE TRIGGER statement_trigger 130BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON trigger_test 131FOR EACH STATEMENT EXECUTE PROCEDURE trigger_data(42,'statement trigger'); 132 133CREATE TRIGGER show_trigger_data_trig_before 134BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_generated 135FOR EACH ROW EXECUTE PROCEDURE trigger_data(); 136CREATE TRIGGER show_trigger_data_trig_after 137AFTER INSERT OR UPDATE OR DELETE ON trigger_test_generated 138FOR EACH ROW EXECUTE PROCEDURE trigger_data(); 139 140CREATE TRIGGER show_trigger_data_view_trig 141INSTEAD OF INSERT OR UPDATE OR DELETE ON trigger_test_view 142FOR EACH ROW EXECUTE PROCEDURE trigger_data(24,'skidoo view'); 143 144-- 145-- Trigger function on every change to T_pkey1 146-- 147create function trig_pkey1_before() returns trigger as E' 148 # 149 # Create prepared plans on the first call 150 # 151 if {![info exists GD]} { 152 # 153 # Plan to check for duplicate key in T_pkey1 154 # 155 set GD(plan_pkey1) [spi_prepare \\ 156 "select check_pkey1_exists(\\$1, \\$2) as ret" \\ 157 {int4 bpchar}] 158 # 159 # Plan to check for references from T_dta1 160 # 161 set GD(plan_dta1) [spi_prepare \\ 162 "select 1 from T_dta1 \\ 163 where ref1 = \\$1 and ref2 = \\$2" \\ 164 {int4 bpchar}] 165 } 166 167 # 168 # Initialize flags 169 # 170 set check_old_ref 0 171 set check_new_dup 0 172 173 switch $TG_op { 174 INSERT { 175 # 176 # Must check for duplicate key on INSERT 177 # 178 set check_new_dup 1 179 } 180 UPDATE { 181 # 182 # Must check for duplicate key on UPDATE only if 183 # the key changes. In that case we must check for 184 # references to OLD values too. 185 # 186 if {[string compare $NEW(key1) $OLD(key1)] != 0} { 187 set check_old_ref 1 188 set check_new_dup 1 189 } 190 if {[string compare $NEW(key2) $OLD(key2)] != 0} { 191 set check_old_ref 1 192 set check_new_dup 1 193 } 194 } 195 DELETE { 196 # 197 # Must only check for references to OLD on DELETE 198 # 199 set check_old_ref 1 200 } 201 } 202 203 if {$check_new_dup} { 204 # 205 # Check for duplicate key 206 # 207 spi_execp -count 1 $GD(plan_pkey1) [list $NEW(key1) $NEW(key2)] 208 if {$ret == "t"} { 209 elog ERROR \\ 210 "duplicate key ''$NEW(key1)'', ''$NEW(key2)'' for T_pkey1" 211 } 212 } 213 214 if {$check_old_ref} { 215 # 216 # Check for references to OLD 217 # 218 set n [spi_execp -count 1 $GD(plan_dta1) [list $OLD(key1) $OLD(key2)]] 219 if {$n > 0} { 220 elog ERROR \\ 221 "key ''$OLD(key1)'', ''$OLD(key2)'' referenced by T_dta1" 222 } 223 } 224 225 # 226 # Anything is fine - let operation pass through 227 # 228 return OK 229' language pltcl; 230 231 232create trigger pkey1_before before insert or update or delete on T_pkey1 233 for each row execute procedure 234 trig_pkey1_before(); 235 236 237-- 238-- Trigger function to check for duplicate keys in T_pkey2 239-- and to force key2 to be upper case only without leading whitespaces 240-- 241create function trig_pkey2_before() returns trigger as E' 242 # 243 # Prepare plan on first call 244 # 245 if {![info exists GD]} { 246 set GD(plan_pkey2) [spi_prepare \\ 247 "select 1 from T_pkey2 \\ 248 where key1 = \\$1 and key2 = \\$2" \\ 249 {int4 bpchar}] 250 } 251 252 # 253 # Convert key2 value 254 # 255 set NEW(key2) [string toupper [string trim $NEW(key2)]] 256 257 # 258 # Check for duplicate key 259 # 260 set n [spi_execp -count 1 $GD(plan_pkey2) [list $NEW(key1) $NEW(key2)]] 261 if {$n > 0} { 262 elog ERROR \\ 263 "duplicate key ''$NEW(key1)'', ''$NEW(key2)'' for T_pkey2" 264 } 265 266 # 267 # Return modified tuple in NEW 268 # 269 return [array get NEW] 270' language pltcl; 271 272 273create trigger pkey2_before before insert or update on T_pkey2 274 for each row execute procedure 275 trig_pkey2_before(); 276 277 278-- 279-- Trigger function to force references from T_dta2 follow changes 280-- in T_pkey2 or be deleted too. This must be done AFTER the changes 281-- in T_pkey2 are done so the trigger for primkey check on T_dta2 282-- fired on our updates will see the new key values in T_pkey2. 283-- 284create function trig_pkey2_after() returns trigger as E' 285 # 286 # Prepare plans on first call 287 # 288 if {![info exists GD]} { 289 # 290 # Plan to update references from T_dta2 291 # 292 set GD(plan_dta2_upd) [spi_prepare \\ 293 "update T_dta2 set ref1 = \\$3, ref2 = \\$4 \\ 294 where ref1 = \\$1 and ref2 = \\$2" \\ 295 {int4 bpchar int4 bpchar}] 296 # 297 # Plan to delete references from T_dta2 298 # 299 set GD(plan_dta2_del) [spi_prepare \\ 300 "delete from T_dta2 \\ 301 where ref1 = \\$1 and ref2 = \\$2" \\ 302 {int4 bpchar}] 303 } 304 305 # 306 # Initialize flags 307 # 308 set old_ref_follow 0 309 set old_ref_delete 0 310 311 switch $TG_op { 312 UPDATE { 313 # 314 # On update we must let old references follow 315 # 316 set NEW(key2) [string toupper $NEW(key2)] 317 318 if {[string compare $NEW(key1) $OLD(key1)] != 0} { 319 set old_ref_follow 1 320 } 321 if {[string compare $NEW(key2) $OLD(key2)] != 0} { 322 set old_ref_follow 1 323 } 324 } 325 DELETE { 326 # 327 # On delete we must delete references too 328 # 329 set old_ref_delete 1 330 } 331 } 332 333 if {$old_ref_follow} { 334 # 335 # Let old references follow and fire NOTICE message if 336 # there where some 337 # 338 set n [spi_execp $GD(plan_dta2_upd) \\ 339 [list $OLD(key1) $OLD(key2) $NEW(key1) $NEW(key2)]] 340 if {$n > 0} { 341 elog NOTICE \\ 342 "updated $n entries in T_dta2 for new key in T_pkey2" 343 } 344 } 345 346 if {$old_ref_delete} { 347 # 348 # delete references and fire NOTICE message if 349 # there where some 350 # 351 set n [spi_execp $GD(plan_dta2_del) \\ 352 [list $OLD(key1) $OLD(key2)]] 353 if {$n > 0} { 354 elog NOTICE \\ 355 "deleted $n entries from T_dta2" 356 } 357 } 358 359 return OK 360' language pltcl; 361 362 363create trigger pkey2_after after update or delete on T_pkey2 364 for each row execute procedure 365 trig_pkey2_after(); 366 367 368-- 369-- Generic trigger function to check references in T_dta1 and T_dta2 370-- 371create function check_primkey() returns trigger as E' 372 # 373 # For every trigger/relation pair we create 374 # a saved plan and hold them in GD 375 # 376 set plankey [list "plan" $TG_name $TG_relid] 377 set planrel [list "relname" $TG_relid] 378 379 # 380 # Extract the pkey relation name 381 # 382 set keyidx [expr [llength $args] / 2] 383 set keyrel [string tolower [lindex $args $keyidx]] 384 385 if {![info exists GD($plankey)]} { 386 # 387 # We must prepare a new plan. Build up a query string 388 # for the primary key check. 389 # 390 set keylist [lrange $args [expr $keyidx + 1] end] 391 392 set query "select 1 from $keyrel" 393 set qual " where" 394 set typlist "" 395 set idx 1 396 foreach key $keylist { 397 set key [string tolower $key] 398 # 399 # Add the qual part to the query string 400 # 401 append query "$qual $key = \\$$idx" 402 set qual " and" 403 404 # 405 # Lookup the fields type in pg_attribute 406 # 407 set n [spi_exec "select T.typname \\ 408 from pg_catalog.pg_type T, pg_catalog.pg_attribute A, pg_catalog.pg_class C \\ 409 where C.relname = ''[quote $keyrel]'' \\ 410 and C.oid = A.attrelid \\ 411 and A.attname = ''[quote $key]'' \\ 412 and A.atttypid = T.oid"] 413 if {$n != 1} { 414 elog ERROR "table $keyrel doesn''t have a field named $key" 415 } 416 417 # 418 # Append the fields type to the argument type list 419 # 420 lappend typlist $typname 421 incr idx 422 } 423 424 # 425 # Prepare the plan 426 # 427 set GD($plankey) [spi_prepare $query $typlist] 428 429 # 430 # Lookup and remember the table name for later error messages 431 # 432 spi_exec "select relname from pg_catalog.pg_class \\ 433 where oid = ''$TG_relid''::oid" 434 set GD($planrel) $relname 435 } 436 437 # 438 # Build the argument list from the NEW row 439 # 440 incr keyidx -1 441 set arglist "" 442 foreach arg [lrange $args 0 $keyidx] { 443 lappend arglist $NEW($arg) 444 } 445 446 # 447 # Check for the primary key 448 # 449 set n [spi_execp -count 1 $GD($plankey) $arglist] 450 if {$n <= 0} { 451 elog ERROR "key for $GD($planrel) not in $keyrel" 452 } 453 454 # 455 # Anything is fine 456 # 457 return OK 458' language pltcl; 459 460 461create trigger dta1_before before insert or update on T_dta1 462 for each row execute procedure 463 check_primkey('ref1', 'ref2', 'T_pkey1', 'key1', 'key2'); 464 465 466create trigger dta2_before before insert or update on T_dta2 467 for each row execute procedure 468 check_primkey('ref1', 'ref2', 'T_pkey2', 'key1', 'key2'); 469 470 471insert into T_pkey1 values (1, 'key1-1', 'test key'); 472insert into T_pkey1 values (1, 'key1-2', 'test key'); 473insert into T_pkey1 values (1, 'key1-3', 'test key'); 474insert into T_pkey1 values (2, 'key2-1', 'test key'); 475insert into T_pkey1 values (2, 'key2-2', 'test key'); 476insert into T_pkey1 values (2, 'key2-3', 'test key'); 477 478insert into T_pkey2 values (1, 'key1-1', 'test key'); 479insert into T_pkey2 values (1, 'key1-2', 'test key'); 480insert into T_pkey2 values (1, 'key1-3', 'test key'); 481insert into T_pkey2 values (2, 'key2-1', 'test key'); 482insert into T_pkey2 values (2, 'key2-2', 'test key'); 483insert into T_pkey2 values (2, 'key2-3', 'test key'); 484 485select * from T_pkey1; 486 487-- key2 in T_pkey2 should have upper case only 488select * from T_pkey2; 489 490insert into T_pkey1 values (1, 'KEY1-3', 'should work'); 491 492-- Due to the upper case translation in trigger this must fail 493insert into T_pkey2 values (1, 'KEY1-3', 'should fail'); 494 495insert into T_dta1 values ('trec 1', 1, 'key1-1'); 496insert into T_dta1 values ('trec 2', 1, 'key1-2'); 497insert into T_dta1 values ('trec 3', 1, 'key1-3'); 498 499-- Must fail due to unknown key in T_pkey1 500insert into T_dta1 values ('trec 4', 1, 'key1-4'); 501 502insert into T_dta2 values ('trec 1', 1, 'KEY1-1'); 503insert into T_dta2 values ('trec 2', 1, 'KEY1-2'); 504insert into T_dta2 values ('trec 3', 1, 'KEY1-3'); 505 506-- Must fail due to unknown key in T_pkey2 507insert into T_dta2 values ('trec 4', 1, 'KEY1-4'); 508 509select * from T_dta1; 510 511select * from T_dta2; 512 513update T_pkey1 set key2 = 'key2-9' where key1 = 2 and key2 = 'key2-1'; 514update T_pkey1 set key2 = 'key1-9' where key1 = 1 and key2 = 'key1-1'; 515delete from T_pkey1 where key1 = 2 and key2 = 'key2-2'; 516delete from T_pkey1 where key1 = 1 and key2 = 'key1-2'; 517 518update T_pkey2 set key2 = 'KEY2-9' where key1 = 2 and key2 = 'KEY2-1'; 519update T_pkey2 set key2 = 'KEY1-9' where key1 = 1 and key2 = 'KEY1-1'; 520delete from T_pkey2 where key1 = 2 and key2 = 'KEY2-2'; 521delete from T_pkey2 where key1 = 1 and key2 = 'KEY1-2'; 522 523select * from T_pkey1; 524select * from T_pkey2; 525select * from T_dta1; 526select * from T_dta2; 527 528select tcl_avg(key1) from T_pkey1; 529select tcl_sum(key1) from T_pkey1; 530select tcl_avg(key1) from T_pkey2; 531select tcl_sum(key1) from T_pkey2; 532 533-- The following should return NULL instead of 0 534select tcl_avg(key1) from T_pkey1 where key1 = 99; 535select tcl_sum(key1) from T_pkey1 where key1 = 99; 536 537select 1 @< 2; 538select 100 @< 4; 539 540select * from T_pkey1 order by key1 using @<, key2 collate "C"; 541select * from T_pkey2 order by key1 using @<, key2 collate "C"; 542 543-- show dump of trigger data 544insert into trigger_test values(1,'insert'); 545 546insert into trigger_test_generated (i) values (1); 547update trigger_test_generated set i = 11 where i = 1; 548delete from trigger_test_generated; 549 550insert into trigger_test_view values(2,'insert'); 551update trigger_test_view set v = 'update' where i=1; 552delete from trigger_test_view; 553 554update trigger_test set v = 'update', test_skip=true where i = 1; 555update trigger_test set v = 'update' where i = 1; 556delete from trigger_test; 557truncate trigger_test; 558 559DROP TRIGGER show_trigger_data_trig_before ON trigger_test_generated; 560DROP TRIGGER show_trigger_data_trig_after ON trigger_test_generated; 561 562-- should error 563insert into trigger_test(test_argisnull) values(true); 564 565-- should error 566insert into trigger_test(test_return_null) values(true); 567 568-- test transition table visibility 569create table transition_table_test (id int, name text); 570insert into transition_table_test values (1, 'a'); 571create function transition_table_test_f() returns trigger language pltcl as 572$$ 573 spi_exec -array C "SELECT id, name FROM old_table" { 574 elog INFO "old: $C(id) -> $C(name)" 575 } 576 spi_exec -array C "SELECT id, name FROM new_table" { 577 elog INFO "new: $C(id) -> $C(name)" 578 } 579 return OK 580$$; 581CREATE TRIGGER a_t AFTER UPDATE ON transition_table_test 582 REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table 583 FOR EACH STATEMENT EXECUTE PROCEDURE transition_table_test_f(); 584update transition_table_test set name = 'b'; 585drop table transition_table_test; 586drop function transition_table_test_f(); 587 588-- dealing with generated columns 589 590CREATE FUNCTION generated_test_func1() RETURNS trigger 591LANGUAGE pltcl 592AS $$ 593# not allowed 594set NEW(j) 5 595return [array get NEW] 596$$; 597 598CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_generated 599FOR EACH ROW EXECUTE PROCEDURE generated_test_func1(); 600 601TRUNCATE trigger_test_generated; 602INSERT INTO trigger_test_generated (i) VALUES (1); 603SELECT * FROM trigger_test_generated; 604