1-- 2-- PLPGSQL 3-- 4-- Scenario: 5-- 6-- A building with a modern TP cable installation where any 7-- of the wall connectors can be used to plug in phones, 8-- ethernet interfaces or local office hubs. The backside 9-- of the wall connectors is wired to one of several patch- 10-- fields in the building. 11-- 12-- In the patchfields, there are hubs and all the slots 13-- representing the wall connectors. In addition there are 14-- slots that can represent a phone line from the central 15-- phone system. 16-- 17-- Triggers ensure consistency of the patching information. 18-- 19-- Functions are used to build up powerful views that let 20-- you look behind the wall when looking at a patchfield 21-- or into a room. 22-- 23 24 25create table Room ( 26 roomno char(8), 27 comment text 28); 29 30create unique index Room_rno on Room using btree (roomno bpchar_ops); 31 32 33create table WSlot ( 34 slotname char(20), 35 roomno char(8), 36 slotlink char(20), 37 backlink char(20) 38); 39 40create unique index WSlot_name on WSlot using btree (slotname bpchar_ops); 41 42 43create table PField ( 44 name text, 45 comment text 46); 47 48create unique index PField_name on PField using btree (name text_ops); 49 50 51create table PSlot ( 52 slotname char(20), 53 pfname text, 54 slotlink char(20), 55 backlink char(20) 56); 57 58create unique index PSlot_name on PSlot using btree (slotname bpchar_ops); 59 60 61create table PLine ( 62 slotname char(20), 63 phonenumber char(20), 64 comment text, 65 backlink char(20) 66); 67 68create unique index PLine_name on PLine using btree (slotname bpchar_ops); 69 70 71create table Hub ( 72 name char(14), 73 comment text, 74 nslots integer 75); 76 77create unique index Hub_name on Hub using btree (name bpchar_ops); 78 79 80create table HSlot ( 81 slotname char(20), 82 hubname char(14), 83 slotno integer, 84 slotlink char(20) 85); 86 87create unique index HSlot_name on HSlot using btree (slotname bpchar_ops); 88create index HSlot_hubname on HSlot using btree (hubname bpchar_ops); 89 90 91create table System ( 92 name text, 93 comment text 94); 95 96create unique index System_name on System using btree (name text_ops); 97 98 99create table IFace ( 100 slotname char(20), 101 sysname text, 102 ifname text, 103 slotlink char(20) 104); 105 106create unique index IFace_name on IFace using btree (slotname bpchar_ops); 107 108 109create table PHone ( 110 slotname char(20), 111 comment text, 112 slotlink char(20) 113); 114 115create unique index PHone_name on PHone using btree (slotname bpchar_ops); 116 117 118-- ************************************************************ 119-- * 120-- * Trigger procedures and functions for the patchfield 121-- * test of PL/pgSQL 122-- * 123-- ************************************************************ 124 125 126-- ************************************************************ 127-- * AFTER UPDATE on Room 128-- * - If room no changes let wall slots follow 129-- ************************************************************ 130create function tg_room_au() returns trigger as ' 131begin 132 if new.roomno != old.roomno then 133 update WSlot set roomno = new.roomno where roomno = old.roomno; 134 end if; 135 return new; 136end; 137' language plpgsql; 138 139create trigger tg_room_au after update 140 on Room for each row execute procedure tg_room_au(); 141 142 143-- ************************************************************ 144-- * AFTER DELETE on Room 145-- * - delete wall slots in this room 146-- ************************************************************ 147create function tg_room_ad() returns trigger as ' 148begin 149 delete from WSlot where roomno = old.roomno; 150 return old; 151end; 152' language plpgsql; 153 154create trigger tg_room_ad after delete 155 on Room for each row execute procedure tg_room_ad(); 156 157 158-- ************************************************************ 159-- * BEFORE INSERT or UPDATE on WSlot 160-- * - Check that room exists 161-- ************************************************************ 162create function tg_wslot_biu() returns trigger as $$ 163begin 164 if count(*) = 0 from Room where roomno = new.roomno then 165 raise exception 'Room % does not exist', new.roomno; 166 end if; 167 return new; 168end; 169$$ language plpgsql; 170 171create trigger tg_wslot_biu before insert or update 172 on WSlot for each row execute procedure tg_wslot_biu(); 173 174 175-- ************************************************************ 176-- * AFTER UPDATE on PField 177-- * - Let PSlots of this field follow 178-- ************************************************************ 179create function tg_pfield_au() returns trigger as ' 180begin 181 if new.name != old.name then 182 update PSlot set pfname = new.name where pfname = old.name; 183 end if; 184 return new; 185end; 186' language plpgsql; 187 188create trigger tg_pfield_au after update 189 on PField for each row execute procedure tg_pfield_au(); 190 191 192-- ************************************************************ 193-- * AFTER DELETE on PField 194-- * - Remove all slots of this patchfield 195-- ************************************************************ 196create function tg_pfield_ad() returns trigger as ' 197begin 198 delete from PSlot where pfname = old.name; 199 return old; 200end; 201' language plpgsql; 202 203create trigger tg_pfield_ad after delete 204 on PField for each row execute procedure tg_pfield_ad(); 205 206 207-- ************************************************************ 208-- * BEFORE INSERT or UPDATE on PSlot 209-- * - Ensure that our patchfield does exist 210-- ************************************************************ 211create function tg_pslot_biu() returns trigger as $proc$ 212declare 213 pfrec record; 214 ps alias for new; 215begin 216 select into pfrec * from PField where name = ps.pfname; 217 if not found then 218 raise exception $$Patchfield "%" does not exist$$, ps.pfname; 219 end if; 220 return ps; 221end; 222$proc$ language plpgsql; 223 224create trigger tg_pslot_biu before insert or update 225 on PSlot for each row execute procedure tg_pslot_biu(); 226 227 228-- ************************************************************ 229-- * AFTER UPDATE on System 230-- * - If system name changes let interfaces follow 231-- ************************************************************ 232create function tg_system_au() returns trigger as ' 233begin 234 if new.name != old.name then 235 update IFace set sysname = new.name where sysname = old.name; 236 end if; 237 return new; 238end; 239' language plpgsql; 240 241create trigger tg_system_au after update 242 on System for each row execute procedure tg_system_au(); 243 244 245-- ************************************************************ 246-- * BEFORE INSERT or UPDATE on IFace 247-- * - set the slotname to IF.sysname.ifname 248-- ************************************************************ 249create function tg_iface_biu() returns trigger as $$ 250declare 251 sname text; 252 sysrec record; 253begin 254 select into sysrec * from system where name = new.sysname; 255 if not found then 256 raise exception $q$system "%" does not exist$q$, new.sysname; 257 end if; 258 sname := 'IF.' || new.sysname; 259 sname := sname || '.'; 260 sname := sname || new.ifname; 261 if length(sname) > 20 then 262 raise exception 'IFace slotname "%" too long (20 char max)', sname; 263 end if; 264 new.slotname := sname; 265 return new; 266end; 267$$ language plpgsql; 268 269create trigger tg_iface_biu before insert or update 270 on IFace for each row execute procedure tg_iface_biu(); 271 272 273-- ************************************************************ 274-- * AFTER INSERT or UPDATE or DELETE on Hub 275-- * - insert/delete/rename slots as required 276-- ************************************************************ 277create function tg_hub_a() returns trigger as ' 278declare 279 hname text; 280 dummy integer; 281begin 282 if tg_op = ''INSERT'' then 283 dummy := tg_hub_adjustslots(new.name, 0, new.nslots); 284 return new; 285 end if; 286 if tg_op = ''UPDATE'' then 287 if new.name != old.name then 288 update HSlot set hubname = new.name where hubname = old.name; 289 end if; 290 dummy := tg_hub_adjustslots(new.name, old.nslots, new.nslots); 291 return new; 292 end if; 293 if tg_op = ''DELETE'' then 294 dummy := tg_hub_adjustslots(old.name, old.nslots, 0); 295 return old; 296 end if; 297end; 298' language plpgsql; 299 300create trigger tg_hub_a after insert or update or delete 301 on Hub for each row execute procedure tg_hub_a(); 302 303 304-- ************************************************************ 305-- * Support function to add/remove slots of Hub 306-- ************************************************************ 307create function tg_hub_adjustslots(hname bpchar, 308 oldnslots integer, 309 newnslots integer) 310returns integer as ' 311begin 312 if newnslots = oldnslots then 313 return 0; 314 end if; 315 if newnslots < oldnslots then 316 delete from HSlot where hubname = hname and slotno > newnslots; 317 return 0; 318 end if; 319 for i in oldnslots + 1 .. newnslots loop 320 insert into HSlot (slotname, hubname, slotno, slotlink) 321 values (''HS.dummy'', hname, i, ''''); 322 end loop; 323 return 0; 324end 325' language plpgsql; 326 327-- Test comments 328COMMENT ON FUNCTION tg_hub_adjustslots_wrong(bpchar, integer, integer) IS 'function with args'; 329COMMENT ON FUNCTION tg_hub_adjustslots(bpchar, integer, integer) IS 'function with args'; 330COMMENT ON FUNCTION tg_hub_adjustslots(bpchar, integer, integer) IS NULL; 331 332-- ************************************************************ 333-- * BEFORE INSERT or UPDATE on HSlot 334-- * - prevent from manual manipulation 335-- * - set the slotname to HS.hubname.slotno 336-- ************************************************************ 337create function tg_hslot_biu() returns trigger as ' 338declare 339 sname text; 340 xname HSlot.slotname%TYPE; 341 hubrec record; 342begin 343 select into hubrec * from Hub where name = new.hubname; 344 if not found then 345 raise exception ''no manual manipulation of HSlot''; 346 end if; 347 if new.slotno < 1 or new.slotno > hubrec.nslots then 348 raise exception ''no manual manipulation of HSlot''; 349 end if; 350 if tg_op = ''UPDATE'' and new.hubname != old.hubname then 351 if count(*) > 0 from Hub where name = old.hubname then 352 raise exception ''no manual manipulation of HSlot''; 353 end if; 354 end if; 355 sname := ''HS.'' || trim(new.hubname); 356 sname := sname || ''.''; 357 sname := sname || new.slotno::text; 358 if length(sname) > 20 then 359 raise exception ''HSlot slotname "%" too long (20 char max)'', sname; 360 end if; 361 new.slotname := sname; 362 return new; 363end; 364' language plpgsql; 365 366create trigger tg_hslot_biu before insert or update 367 on HSlot for each row execute procedure tg_hslot_biu(); 368 369 370-- ************************************************************ 371-- * BEFORE DELETE on HSlot 372-- * - prevent from manual manipulation 373-- ************************************************************ 374create function tg_hslot_bd() returns trigger as ' 375declare 376 hubrec record; 377begin 378 select into hubrec * from Hub where name = old.hubname; 379 if not found then 380 return old; 381 end if; 382 if old.slotno > hubrec.nslots then 383 return old; 384 end if; 385 raise exception ''no manual manipulation of HSlot''; 386end; 387' language plpgsql; 388 389create trigger tg_hslot_bd before delete 390 on HSlot for each row execute procedure tg_hslot_bd(); 391 392 393-- ************************************************************ 394-- * BEFORE INSERT on all slots 395-- * - Check name prefix 396-- ************************************************************ 397create function tg_chkslotname() returns trigger as ' 398begin 399 if substr(new.slotname, 1, 2) != tg_argv[0] then 400 raise exception ''slotname must begin with %'', tg_argv[0]; 401 end if; 402 return new; 403end; 404' language plpgsql; 405 406create trigger tg_chkslotname before insert 407 on PSlot for each row execute procedure tg_chkslotname('PS'); 408 409create trigger tg_chkslotname before insert 410 on WSlot for each row execute procedure tg_chkslotname('WS'); 411 412create trigger tg_chkslotname before insert 413 on PLine for each row execute procedure tg_chkslotname('PL'); 414 415create trigger tg_chkslotname before insert 416 on IFace for each row execute procedure tg_chkslotname('IF'); 417 418create trigger tg_chkslotname before insert 419 on PHone for each row execute procedure tg_chkslotname('PH'); 420 421 422-- ************************************************************ 423-- * BEFORE INSERT or UPDATE on all slots with slotlink 424-- * - Set slotlink to empty string if NULL value given 425-- ************************************************************ 426create function tg_chkslotlink() returns trigger as ' 427begin 428 if new.slotlink isnull then 429 new.slotlink := ''''; 430 end if; 431 return new; 432end; 433' language plpgsql; 434 435create trigger tg_chkslotlink before insert or update 436 on PSlot for each row execute procedure tg_chkslotlink(); 437 438create trigger tg_chkslotlink before insert or update 439 on WSlot for each row execute procedure tg_chkslotlink(); 440 441create trigger tg_chkslotlink before insert or update 442 on IFace for each row execute procedure tg_chkslotlink(); 443 444create trigger tg_chkslotlink before insert or update 445 on HSlot for each row execute procedure tg_chkslotlink(); 446 447create trigger tg_chkslotlink before insert or update 448 on PHone for each row execute procedure tg_chkslotlink(); 449 450 451-- ************************************************************ 452-- * BEFORE INSERT or UPDATE on all slots with backlink 453-- * - Set backlink to empty string if NULL value given 454-- ************************************************************ 455create function tg_chkbacklink() returns trigger as ' 456begin 457 if new.backlink isnull then 458 new.backlink := ''''; 459 end if; 460 return new; 461end; 462' language plpgsql; 463 464create trigger tg_chkbacklink before insert or update 465 on PSlot for each row execute procedure tg_chkbacklink(); 466 467create trigger tg_chkbacklink before insert or update 468 on WSlot for each row execute procedure tg_chkbacklink(); 469 470create trigger tg_chkbacklink before insert or update 471 on PLine for each row execute procedure tg_chkbacklink(); 472 473 474-- ************************************************************ 475-- * BEFORE UPDATE on PSlot 476-- * - do delete/insert instead of update if name changes 477-- ************************************************************ 478create function tg_pslot_bu() returns trigger as ' 479begin 480 if new.slotname != old.slotname then 481 delete from PSlot where slotname = old.slotname; 482 insert into PSlot ( 483 slotname, 484 pfname, 485 slotlink, 486 backlink 487 ) values ( 488 new.slotname, 489 new.pfname, 490 new.slotlink, 491 new.backlink 492 ); 493 return null; 494 end if; 495 return new; 496end; 497' language plpgsql; 498 499create trigger tg_pslot_bu before update 500 on PSlot for each row execute procedure tg_pslot_bu(); 501 502 503-- ************************************************************ 504-- * BEFORE UPDATE on WSlot 505-- * - do delete/insert instead of update if name changes 506-- ************************************************************ 507create function tg_wslot_bu() returns trigger as ' 508begin 509 if new.slotname != old.slotname then 510 delete from WSlot where slotname = old.slotname; 511 insert into WSlot ( 512 slotname, 513 roomno, 514 slotlink, 515 backlink 516 ) values ( 517 new.slotname, 518 new.roomno, 519 new.slotlink, 520 new.backlink 521 ); 522 return null; 523 end if; 524 return new; 525end; 526' language plpgsql; 527 528create trigger tg_wslot_bu before update 529 on WSlot for each row execute procedure tg_Wslot_bu(); 530 531 532-- ************************************************************ 533-- * BEFORE UPDATE on PLine 534-- * - do delete/insert instead of update if name changes 535-- ************************************************************ 536create function tg_pline_bu() returns trigger as ' 537begin 538 if new.slotname != old.slotname then 539 delete from PLine where slotname = old.slotname; 540 insert into PLine ( 541 slotname, 542 phonenumber, 543 comment, 544 backlink 545 ) values ( 546 new.slotname, 547 new.phonenumber, 548 new.comment, 549 new.backlink 550 ); 551 return null; 552 end if; 553 return new; 554end; 555' language plpgsql; 556 557create trigger tg_pline_bu before update 558 on PLine for each row execute procedure tg_pline_bu(); 559 560 561-- ************************************************************ 562-- * BEFORE UPDATE on IFace 563-- * - do delete/insert instead of update if name changes 564-- ************************************************************ 565create function tg_iface_bu() returns trigger as ' 566begin 567 if new.slotname != old.slotname then 568 delete from IFace where slotname = old.slotname; 569 insert into IFace ( 570 slotname, 571 sysname, 572 ifname, 573 slotlink 574 ) values ( 575 new.slotname, 576 new.sysname, 577 new.ifname, 578 new.slotlink 579 ); 580 return null; 581 end if; 582 return new; 583end; 584' language plpgsql; 585 586create trigger tg_iface_bu before update 587 on IFace for each row execute procedure tg_iface_bu(); 588 589 590-- ************************************************************ 591-- * BEFORE UPDATE on HSlot 592-- * - do delete/insert instead of update if name changes 593-- ************************************************************ 594create function tg_hslot_bu() returns trigger as ' 595begin 596 if new.slotname != old.slotname or new.hubname != old.hubname then 597 delete from HSlot where slotname = old.slotname; 598 insert into HSlot ( 599 slotname, 600 hubname, 601 slotno, 602 slotlink 603 ) values ( 604 new.slotname, 605 new.hubname, 606 new.slotno, 607 new.slotlink 608 ); 609 return null; 610 end if; 611 return new; 612end; 613' language plpgsql; 614 615create trigger tg_hslot_bu before update 616 on HSlot for each row execute procedure tg_hslot_bu(); 617 618 619-- ************************************************************ 620-- * BEFORE UPDATE on PHone 621-- * - do delete/insert instead of update if name changes 622-- ************************************************************ 623create function tg_phone_bu() returns trigger as ' 624begin 625 if new.slotname != old.slotname then 626 delete from PHone where slotname = old.slotname; 627 insert into PHone ( 628 slotname, 629 comment, 630 slotlink 631 ) values ( 632 new.slotname, 633 new.comment, 634 new.slotlink 635 ); 636 return null; 637 end if; 638 return new; 639end; 640' language plpgsql; 641 642create trigger tg_phone_bu before update 643 on PHone for each row execute procedure tg_phone_bu(); 644 645 646-- ************************************************************ 647-- * AFTER INSERT or UPDATE or DELETE on slot with backlink 648-- * - Ensure that the opponent correctly points back to us 649-- ************************************************************ 650create function tg_backlink_a() returns trigger as ' 651declare 652 dummy integer; 653begin 654 if tg_op = ''INSERT'' then 655 if new.backlink != '''' then 656 dummy := tg_backlink_set(new.backlink, new.slotname); 657 end if; 658 return new; 659 end if; 660 if tg_op = ''UPDATE'' then 661 if new.backlink != old.backlink then 662 if old.backlink != '''' then 663 dummy := tg_backlink_unset(old.backlink, old.slotname); 664 end if; 665 if new.backlink != '''' then 666 dummy := tg_backlink_set(new.backlink, new.slotname); 667 end if; 668 else 669 if new.slotname != old.slotname and new.backlink != '''' then 670 dummy := tg_slotlink_set(new.backlink, new.slotname); 671 end if; 672 end if; 673 return new; 674 end if; 675 if tg_op = ''DELETE'' then 676 if old.backlink != '''' then 677 dummy := tg_backlink_unset(old.backlink, old.slotname); 678 end if; 679 return old; 680 end if; 681end; 682' language plpgsql; 683 684 685create trigger tg_backlink_a after insert or update or delete 686 on PSlot for each row execute procedure tg_backlink_a('PS'); 687 688create trigger tg_backlink_a after insert or update or delete 689 on WSlot for each row execute procedure tg_backlink_a('WS'); 690 691create trigger tg_backlink_a after insert or update or delete 692 on PLine for each row execute procedure tg_backlink_a('PL'); 693 694 695-- ************************************************************ 696-- * Support function to set the opponents backlink field 697-- * if it does not already point to the requested slot 698-- ************************************************************ 699create function tg_backlink_set(myname bpchar, blname bpchar) 700returns integer as ' 701declare 702 mytype char(2); 703 link char(4); 704 rec record; 705begin 706 mytype := substr(myname, 1, 2); 707 link := mytype || substr(blname, 1, 2); 708 if link = ''PLPL'' then 709 raise exception 710 ''backlink between two phone lines does not make sense''; 711 end if; 712 if link in (''PLWS'', ''WSPL'') then 713 raise exception 714 ''direct link of phone line to wall slot not permitted''; 715 end if; 716 if mytype = ''PS'' then 717 select into rec * from PSlot where slotname = myname; 718 if not found then 719 raise exception ''% does not exist'', myname; 720 end if; 721 if rec.backlink != blname then 722 update PSlot set backlink = blname where slotname = myname; 723 end if; 724 return 0; 725 end if; 726 if mytype = ''WS'' then 727 select into rec * from WSlot where slotname = myname; 728 if not found then 729 raise exception ''% does not exist'', myname; 730 end if; 731 if rec.backlink != blname then 732 update WSlot set backlink = blname where slotname = myname; 733 end if; 734 return 0; 735 end if; 736 if mytype = ''PL'' then 737 select into rec * from PLine where slotname = myname; 738 if not found then 739 raise exception ''% does not exist'', myname; 740 end if; 741 if rec.backlink != blname then 742 update PLine set backlink = blname where slotname = myname; 743 end if; 744 return 0; 745 end if; 746 raise exception ''illegal backlink beginning with %'', mytype; 747end; 748' language plpgsql; 749 750 751-- ************************************************************ 752-- * Support function to clear out the backlink field if 753-- * it still points to specific slot 754-- ************************************************************ 755create function tg_backlink_unset(bpchar, bpchar) 756returns integer as ' 757declare 758 myname alias for $1; 759 blname alias for $2; 760 mytype char(2); 761 rec record; 762begin 763 mytype := substr(myname, 1, 2); 764 if mytype = ''PS'' then 765 select into rec * from PSlot where slotname = myname; 766 if not found then 767 return 0; 768 end if; 769 if rec.backlink = blname then 770 update PSlot set backlink = '''' where slotname = myname; 771 end if; 772 return 0; 773 end if; 774 if mytype = ''WS'' then 775 select into rec * from WSlot where slotname = myname; 776 if not found then 777 return 0; 778 end if; 779 if rec.backlink = blname then 780 update WSlot set backlink = '''' where slotname = myname; 781 end if; 782 return 0; 783 end if; 784 if mytype = ''PL'' then 785 select into rec * from PLine where slotname = myname; 786 if not found then 787 return 0; 788 end if; 789 if rec.backlink = blname then 790 update PLine set backlink = '''' where slotname = myname; 791 end if; 792 return 0; 793 end if; 794end 795' language plpgsql; 796 797 798-- ************************************************************ 799-- * AFTER INSERT or UPDATE or DELETE on slot with slotlink 800-- * - Ensure that the opponent correctly points back to us 801-- ************************************************************ 802create function tg_slotlink_a() returns trigger as ' 803declare 804 dummy integer; 805begin 806 if tg_op = ''INSERT'' then 807 if new.slotlink != '''' then 808 dummy := tg_slotlink_set(new.slotlink, new.slotname); 809 end if; 810 return new; 811 end if; 812 if tg_op = ''UPDATE'' then 813 if new.slotlink != old.slotlink then 814 if old.slotlink != '''' then 815 dummy := tg_slotlink_unset(old.slotlink, old.slotname); 816 end if; 817 if new.slotlink != '''' then 818 dummy := tg_slotlink_set(new.slotlink, new.slotname); 819 end if; 820 else 821 if new.slotname != old.slotname and new.slotlink != '''' then 822 dummy := tg_slotlink_set(new.slotlink, new.slotname); 823 end if; 824 end if; 825 return new; 826 end if; 827 if tg_op = ''DELETE'' then 828 if old.slotlink != '''' then 829 dummy := tg_slotlink_unset(old.slotlink, old.slotname); 830 end if; 831 return old; 832 end if; 833end; 834' language plpgsql; 835 836 837create trigger tg_slotlink_a after insert or update or delete 838 on PSlot for each row execute procedure tg_slotlink_a('PS'); 839 840create trigger tg_slotlink_a after insert or update or delete 841 on WSlot for each row execute procedure tg_slotlink_a('WS'); 842 843create trigger tg_slotlink_a after insert or update or delete 844 on IFace for each row execute procedure tg_slotlink_a('IF'); 845 846create trigger tg_slotlink_a after insert or update or delete 847 on HSlot for each row execute procedure tg_slotlink_a('HS'); 848 849create trigger tg_slotlink_a after insert or update or delete 850 on PHone for each row execute procedure tg_slotlink_a('PH'); 851 852 853-- ************************************************************ 854-- * Support function to set the opponents slotlink field 855-- * if it does not already point to the requested slot 856-- ************************************************************ 857create function tg_slotlink_set(bpchar, bpchar) 858returns integer as ' 859declare 860 myname alias for $1; 861 blname alias for $2; 862 mytype char(2); 863 link char(4); 864 rec record; 865begin 866 mytype := substr(myname, 1, 2); 867 link := mytype || substr(blname, 1, 2); 868 if link = ''PHPH'' then 869 raise exception 870 ''slotlink between two phones does not make sense''; 871 end if; 872 if link in (''PHHS'', ''HSPH'') then 873 raise exception 874 ''link of phone to hub does not make sense''; 875 end if; 876 if link in (''PHIF'', ''IFPH'') then 877 raise exception 878 ''link of phone to hub does not make sense''; 879 end if; 880 if link in (''PSWS'', ''WSPS'') then 881 raise exception 882 ''slotlink from patchslot to wallslot not permitted''; 883 end if; 884 if mytype = ''PS'' then 885 select into rec * from PSlot where slotname = myname; 886 if not found then 887 raise exception ''% does not exist'', myname; 888 end if; 889 if rec.slotlink != blname then 890 update PSlot set slotlink = blname where slotname = myname; 891 end if; 892 return 0; 893 end if; 894 if mytype = ''WS'' then 895 select into rec * from WSlot where slotname = myname; 896 if not found then 897 raise exception ''% does not exist'', myname; 898 end if; 899 if rec.slotlink != blname then 900 update WSlot set slotlink = blname where slotname = myname; 901 end if; 902 return 0; 903 end if; 904 if mytype = ''IF'' then 905 select into rec * from IFace where slotname = myname; 906 if not found then 907 raise exception ''% does not exist'', myname; 908 end if; 909 if rec.slotlink != blname then 910 update IFace set slotlink = blname where slotname = myname; 911 end if; 912 return 0; 913 end if; 914 if mytype = ''HS'' then 915 select into rec * from HSlot where slotname = myname; 916 if not found then 917 raise exception ''% does not exist'', myname; 918 end if; 919 if rec.slotlink != blname then 920 update HSlot set slotlink = blname where slotname = myname; 921 end if; 922 return 0; 923 end if; 924 if mytype = ''PH'' then 925 select into rec * from PHone where slotname = myname; 926 if not found then 927 raise exception ''% does not exist'', myname; 928 end if; 929 if rec.slotlink != blname then 930 update PHone set slotlink = blname where slotname = myname; 931 end if; 932 return 0; 933 end if; 934 raise exception ''illegal slotlink beginning with %'', mytype; 935end; 936' language plpgsql; 937 938 939-- ************************************************************ 940-- * Support function to clear out the slotlink field if 941-- * it still points to specific slot 942-- ************************************************************ 943create function tg_slotlink_unset(bpchar, bpchar) 944returns integer as ' 945declare 946 myname alias for $1; 947 blname alias for $2; 948 mytype char(2); 949 rec record; 950begin 951 mytype := substr(myname, 1, 2); 952 if mytype = ''PS'' then 953 select into rec * from PSlot where slotname = myname; 954 if not found then 955 return 0; 956 end if; 957 if rec.slotlink = blname then 958 update PSlot set slotlink = '''' where slotname = myname; 959 end if; 960 return 0; 961 end if; 962 if mytype = ''WS'' then 963 select into rec * from WSlot where slotname = myname; 964 if not found then 965 return 0; 966 end if; 967 if rec.slotlink = blname then 968 update WSlot set slotlink = '''' where slotname = myname; 969 end if; 970 return 0; 971 end if; 972 if mytype = ''IF'' then 973 select into rec * from IFace where slotname = myname; 974 if not found then 975 return 0; 976 end if; 977 if rec.slotlink = blname then 978 update IFace set slotlink = '''' where slotname = myname; 979 end if; 980 return 0; 981 end if; 982 if mytype = ''HS'' then 983 select into rec * from HSlot where slotname = myname; 984 if not found then 985 return 0; 986 end if; 987 if rec.slotlink = blname then 988 update HSlot set slotlink = '''' where slotname = myname; 989 end if; 990 return 0; 991 end if; 992 if mytype = ''PH'' then 993 select into rec * from PHone where slotname = myname; 994 if not found then 995 return 0; 996 end if; 997 if rec.slotlink = blname then 998 update PHone set slotlink = '''' where slotname = myname; 999 end if; 1000 return 0; 1001 end if; 1002end; 1003' language plpgsql; 1004 1005 1006-- ************************************************************ 1007-- * Describe the backside of a patchfield slot 1008-- ************************************************************ 1009create function pslot_backlink_view(bpchar) 1010returns text as ' 1011<<outer>> 1012declare 1013 rec record; 1014 bltype char(2); 1015 retval text; 1016begin 1017 select into rec * from PSlot where slotname = $1; 1018 if not found then 1019 return ''''; 1020 end if; 1021 if rec.backlink = '''' then 1022 return ''-''; 1023 end if; 1024 bltype := substr(rec.backlink, 1, 2); 1025 if bltype = ''PL'' then 1026 declare 1027 rec record; 1028 begin 1029 select into rec * from PLine where slotname = "outer".rec.backlink; 1030 retval := ''Phone line '' || trim(rec.phonenumber); 1031 if rec.comment != '''' then 1032 retval := retval || '' (''; 1033 retval := retval || rec.comment; 1034 retval := retval || '')''; 1035 end if; 1036 return retval; 1037 end; 1038 end if; 1039 if bltype = ''WS'' then 1040 select into rec * from WSlot where slotname = rec.backlink; 1041 retval := trim(rec.slotname) || '' in room ''; 1042 retval := retval || trim(rec.roomno); 1043 retval := retval || '' -> ''; 1044 return retval || wslot_slotlink_view(rec.slotname); 1045 end if; 1046 return rec.backlink; 1047end; 1048' language plpgsql; 1049 1050 1051-- ************************************************************ 1052-- * Describe the front of a patchfield slot 1053-- ************************************************************ 1054create function pslot_slotlink_view(bpchar) 1055returns text as ' 1056declare 1057 psrec record; 1058 sltype char(2); 1059 retval text; 1060begin 1061 select into psrec * from PSlot where slotname = $1; 1062 if not found then 1063 return ''''; 1064 end if; 1065 if psrec.slotlink = '''' then 1066 return ''-''; 1067 end if; 1068 sltype := substr(psrec.slotlink, 1, 2); 1069 if sltype = ''PS'' then 1070 retval := trim(psrec.slotlink) || '' -> ''; 1071 return retval || pslot_backlink_view(psrec.slotlink); 1072 end if; 1073 if sltype = ''HS'' then 1074 retval := comment from Hub H, HSlot HS 1075 where HS.slotname = psrec.slotlink 1076 and H.name = HS.hubname; 1077 retval := retval || '' slot ''; 1078 retval := retval || slotno::text from HSlot 1079 where slotname = psrec.slotlink; 1080 return retval; 1081 end if; 1082 return psrec.slotlink; 1083end; 1084' language plpgsql; 1085 1086 1087-- ************************************************************ 1088-- * Describe the front of a wall connector slot 1089-- ************************************************************ 1090create function wslot_slotlink_view(bpchar) 1091returns text as ' 1092declare 1093 rec record; 1094 sltype char(2); 1095 retval text; 1096begin 1097 select into rec * from WSlot where slotname = $1; 1098 if not found then 1099 return ''''; 1100 end if; 1101 if rec.slotlink = '''' then 1102 return ''-''; 1103 end if; 1104 sltype := substr(rec.slotlink, 1, 2); 1105 if sltype = ''PH'' then 1106 select into rec * from PHone where slotname = rec.slotlink; 1107 retval := ''Phone '' || trim(rec.slotname); 1108 if rec.comment != '''' then 1109 retval := retval || '' (''; 1110 retval := retval || rec.comment; 1111 retval := retval || '')''; 1112 end if; 1113 return retval; 1114 end if; 1115 if sltype = ''IF'' then 1116 declare 1117 syrow System%RowType; 1118 ifrow IFace%ROWTYPE; 1119 begin 1120 select into ifrow * from IFace where slotname = rec.slotlink; 1121 select into syrow * from System where name = ifrow.sysname; 1122 retval := syrow.name || '' IF ''; 1123 retval := retval || ifrow.ifname; 1124 if syrow.comment != '''' then 1125 retval := retval || '' (''; 1126 retval := retval || syrow.comment; 1127 retval := retval || '')''; 1128 end if; 1129 return retval; 1130 end; 1131 end if; 1132 return rec.slotlink; 1133end; 1134' language plpgsql; 1135 1136 1137 1138-- ************************************************************ 1139-- * View of a patchfield describing backside and patches 1140-- ************************************************************ 1141create view Pfield_v1 as select PF.pfname, PF.slotname, 1142 pslot_backlink_view(PF.slotname) as backside, 1143 pslot_slotlink_view(PF.slotname) as patch 1144 from PSlot PF; 1145 1146 1147-- 1148-- First we build the house - so we create the rooms 1149-- 1150insert into Room values ('001', 'Entrance'); 1151insert into Room values ('002', 'Office'); 1152insert into Room values ('003', 'Office'); 1153insert into Room values ('004', 'Technical'); 1154insert into Room values ('101', 'Office'); 1155insert into Room values ('102', 'Conference'); 1156insert into Room values ('103', 'Restroom'); 1157insert into Room values ('104', 'Technical'); 1158insert into Room values ('105', 'Office'); 1159insert into Room values ('106', 'Office'); 1160 1161-- 1162-- Second we install the wall connectors 1163-- 1164insert into WSlot values ('WS.001.1a', '001', '', ''); 1165insert into WSlot values ('WS.001.1b', '001', '', ''); 1166insert into WSlot values ('WS.001.2a', '001', '', ''); 1167insert into WSlot values ('WS.001.2b', '001', '', ''); 1168insert into WSlot values ('WS.001.3a', '001', '', ''); 1169insert into WSlot values ('WS.001.3b', '001', '', ''); 1170 1171insert into WSlot values ('WS.002.1a', '002', '', ''); 1172insert into WSlot values ('WS.002.1b', '002', '', ''); 1173insert into WSlot values ('WS.002.2a', '002', '', ''); 1174insert into WSlot values ('WS.002.2b', '002', '', ''); 1175insert into WSlot values ('WS.002.3a', '002', '', ''); 1176insert into WSlot values ('WS.002.3b', '002', '', ''); 1177 1178insert into WSlot values ('WS.003.1a', '003', '', ''); 1179insert into WSlot values ('WS.003.1b', '003', '', ''); 1180insert into WSlot values ('WS.003.2a', '003', '', ''); 1181insert into WSlot values ('WS.003.2b', '003', '', ''); 1182insert into WSlot values ('WS.003.3a', '003', '', ''); 1183insert into WSlot values ('WS.003.3b', '003', '', ''); 1184 1185insert into WSlot values ('WS.101.1a', '101', '', ''); 1186insert into WSlot values ('WS.101.1b', '101', '', ''); 1187insert into WSlot values ('WS.101.2a', '101', '', ''); 1188insert into WSlot values ('WS.101.2b', '101', '', ''); 1189insert into WSlot values ('WS.101.3a', '101', '', ''); 1190insert into WSlot values ('WS.101.3b', '101', '', ''); 1191 1192insert into WSlot values ('WS.102.1a', '102', '', ''); 1193insert into WSlot values ('WS.102.1b', '102', '', ''); 1194insert into WSlot values ('WS.102.2a', '102', '', ''); 1195insert into WSlot values ('WS.102.2b', '102', '', ''); 1196insert into WSlot values ('WS.102.3a', '102', '', ''); 1197insert into WSlot values ('WS.102.3b', '102', '', ''); 1198 1199insert into WSlot values ('WS.105.1a', '105', '', ''); 1200insert into WSlot values ('WS.105.1b', '105', '', ''); 1201insert into WSlot values ('WS.105.2a', '105', '', ''); 1202insert into WSlot values ('WS.105.2b', '105', '', ''); 1203insert into WSlot values ('WS.105.3a', '105', '', ''); 1204insert into WSlot values ('WS.105.3b', '105', '', ''); 1205 1206insert into WSlot values ('WS.106.1a', '106', '', ''); 1207insert into WSlot values ('WS.106.1b', '106', '', ''); 1208insert into WSlot values ('WS.106.2a', '106', '', ''); 1209insert into WSlot values ('WS.106.2b', '106', '', ''); 1210insert into WSlot values ('WS.106.3a', '106', '', ''); 1211insert into WSlot values ('WS.106.3b', '106', '', ''); 1212 1213-- 1214-- Now create the patch fields and their slots 1215-- 1216insert into PField values ('PF0_1', 'Wallslots basement'); 1217 1218-- 1219-- The cables for these will be made later, so they are unconnected for now 1220-- 1221insert into PSlot values ('PS.base.a1', 'PF0_1', '', ''); 1222insert into PSlot values ('PS.base.a2', 'PF0_1', '', ''); 1223insert into PSlot values ('PS.base.a3', 'PF0_1', '', ''); 1224insert into PSlot values ('PS.base.a4', 'PF0_1', '', ''); 1225insert into PSlot values ('PS.base.a5', 'PF0_1', '', ''); 1226insert into PSlot values ('PS.base.a6', 'PF0_1', '', ''); 1227 1228-- 1229-- These are already wired to the wall connectors 1230-- 1231insert into PSlot values ('PS.base.b1', 'PF0_1', '', 'WS.002.1a'); 1232insert into PSlot values ('PS.base.b2', 'PF0_1', '', 'WS.002.1b'); 1233insert into PSlot values ('PS.base.b3', 'PF0_1', '', 'WS.002.2a'); 1234insert into PSlot values ('PS.base.b4', 'PF0_1', '', 'WS.002.2b'); 1235insert into PSlot values ('PS.base.b5', 'PF0_1', '', 'WS.002.3a'); 1236insert into PSlot values ('PS.base.b6', 'PF0_1', '', 'WS.002.3b'); 1237 1238insert into PSlot values ('PS.base.c1', 'PF0_1', '', 'WS.003.1a'); 1239insert into PSlot values ('PS.base.c2', 'PF0_1', '', 'WS.003.1b'); 1240insert into PSlot values ('PS.base.c3', 'PF0_1', '', 'WS.003.2a'); 1241insert into PSlot values ('PS.base.c4', 'PF0_1', '', 'WS.003.2b'); 1242insert into PSlot values ('PS.base.c5', 'PF0_1', '', 'WS.003.3a'); 1243insert into PSlot values ('PS.base.c6', 'PF0_1', '', 'WS.003.3b'); 1244 1245-- 1246-- This patchfield will be renamed later into PF0_2 - so its 1247-- slots references in pfname should follow 1248-- 1249insert into PField values ('PF0_X', 'Phonelines basement'); 1250 1251insert into PSlot values ('PS.base.ta1', 'PF0_X', '', ''); 1252insert into PSlot values ('PS.base.ta2', 'PF0_X', '', ''); 1253insert into PSlot values ('PS.base.ta3', 'PF0_X', '', ''); 1254insert into PSlot values ('PS.base.ta4', 'PF0_X', '', ''); 1255insert into PSlot values ('PS.base.ta5', 'PF0_X', '', ''); 1256insert into PSlot values ('PS.base.ta6', 'PF0_X', '', ''); 1257 1258insert into PSlot values ('PS.base.tb1', 'PF0_X', '', ''); 1259insert into PSlot values ('PS.base.tb2', 'PF0_X', '', ''); 1260insert into PSlot values ('PS.base.tb3', 'PF0_X', '', ''); 1261insert into PSlot values ('PS.base.tb4', 'PF0_X', '', ''); 1262insert into PSlot values ('PS.base.tb5', 'PF0_X', '', ''); 1263insert into PSlot values ('PS.base.tb6', 'PF0_X', '', ''); 1264 1265insert into PField values ('PF1_1', 'Wallslots first floor'); 1266 1267insert into PSlot values ('PS.first.a1', 'PF1_1', '', 'WS.101.1a'); 1268insert into PSlot values ('PS.first.a2', 'PF1_1', '', 'WS.101.1b'); 1269insert into PSlot values ('PS.first.a3', 'PF1_1', '', 'WS.101.2a'); 1270insert into PSlot values ('PS.first.a4', 'PF1_1', '', 'WS.101.2b'); 1271insert into PSlot values ('PS.first.a5', 'PF1_1', '', 'WS.101.3a'); 1272insert into PSlot values ('PS.first.a6', 'PF1_1', '', 'WS.101.3b'); 1273 1274insert into PSlot values ('PS.first.b1', 'PF1_1', '', 'WS.102.1a'); 1275insert into PSlot values ('PS.first.b2', 'PF1_1', '', 'WS.102.1b'); 1276insert into PSlot values ('PS.first.b3', 'PF1_1', '', 'WS.102.2a'); 1277insert into PSlot values ('PS.first.b4', 'PF1_1', '', 'WS.102.2b'); 1278insert into PSlot values ('PS.first.b5', 'PF1_1', '', 'WS.102.3a'); 1279insert into PSlot values ('PS.first.b6', 'PF1_1', '', 'WS.102.3b'); 1280 1281insert into PSlot values ('PS.first.c1', 'PF1_1', '', 'WS.105.1a'); 1282insert into PSlot values ('PS.first.c2', 'PF1_1', '', 'WS.105.1b'); 1283insert into PSlot values ('PS.first.c3', 'PF1_1', '', 'WS.105.2a'); 1284insert into PSlot values ('PS.first.c4', 'PF1_1', '', 'WS.105.2b'); 1285insert into PSlot values ('PS.first.c5', 'PF1_1', '', 'WS.105.3a'); 1286insert into PSlot values ('PS.first.c6', 'PF1_1', '', 'WS.105.3b'); 1287 1288insert into PSlot values ('PS.first.d1', 'PF1_1', '', 'WS.106.1a'); 1289insert into PSlot values ('PS.first.d2', 'PF1_1', '', 'WS.106.1b'); 1290insert into PSlot values ('PS.first.d3', 'PF1_1', '', 'WS.106.2a'); 1291insert into PSlot values ('PS.first.d4', 'PF1_1', '', 'WS.106.2b'); 1292insert into PSlot values ('PS.first.d5', 'PF1_1', '', 'WS.106.3a'); 1293insert into PSlot values ('PS.first.d6', 'PF1_1', '', 'WS.106.3b'); 1294 1295-- 1296-- Now we wire the wall connectors 1a-2a in room 001 to the 1297-- patchfield. In the second update we make an error, and 1298-- correct it after 1299-- 1300update PSlot set backlink = 'WS.001.1a' where slotname = 'PS.base.a1'; 1301update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a3'; 1302select * from WSlot where roomno = '001' order by slotname; 1303select * from PSlot where slotname ~ 'PS.base.a' order by slotname; 1304update PSlot set backlink = 'WS.001.2a' where slotname = 'PS.base.a3'; 1305select * from WSlot where roomno = '001' order by slotname; 1306select * from PSlot where slotname ~ 'PS.base.a' order by slotname; 1307update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a2'; 1308select * from WSlot where roomno = '001' order by slotname; 1309select * from PSlot where slotname ~ 'PS.base.a' order by slotname; 1310 1311-- 1312-- Same procedure for 2b-3b but this time updating the WSlot instead 1313-- of the PSlot. Due to the triggers the result is the same: 1314-- WSlot and corresponding PSlot point to each other. 1315-- 1316update WSlot set backlink = 'PS.base.a4' where slotname = 'WS.001.2b'; 1317update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3a'; 1318select * from WSlot where roomno = '001' order by slotname; 1319select * from PSlot where slotname ~ 'PS.base.a' order by slotname; 1320update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3b'; 1321select * from WSlot where roomno = '001' order by slotname; 1322select * from PSlot where slotname ~ 'PS.base.a' order by slotname; 1323update WSlot set backlink = 'PS.base.a5' where slotname = 'WS.001.3a'; 1324select * from WSlot where roomno = '001' order by slotname; 1325select * from PSlot where slotname ~ 'PS.base.a' order by slotname; 1326 1327insert into PField values ('PF1_2', 'Phonelines first floor'); 1328 1329insert into PSlot values ('PS.first.ta1', 'PF1_2', '', ''); 1330insert into PSlot values ('PS.first.ta2', 'PF1_2', '', ''); 1331insert into PSlot values ('PS.first.ta3', 'PF1_2', '', ''); 1332insert into PSlot values ('PS.first.ta4', 'PF1_2', '', ''); 1333insert into PSlot values ('PS.first.ta5', 'PF1_2', '', ''); 1334insert into PSlot values ('PS.first.ta6', 'PF1_2', '', ''); 1335 1336insert into PSlot values ('PS.first.tb1', 'PF1_2', '', ''); 1337insert into PSlot values ('PS.first.tb2', 'PF1_2', '', ''); 1338insert into PSlot values ('PS.first.tb3', 'PF1_2', '', ''); 1339insert into PSlot values ('PS.first.tb4', 'PF1_2', '', ''); 1340insert into PSlot values ('PS.first.tb5', 'PF1_2', '', ''); 1341insert into PSlot values ('PS.first.tb6', 'PF1_2', '', ''); 1342 1343-- 1344-- Fix the wrong name for patchfield PF0_2 1345-- 1346update PField set name = 'PF0_2' where name = 'PF0_X'; 1347 1348select * from PSlot order by slotname; 1349select * from WSlot order by slotname; 1350 1351-- 1352-- Install the central phone system and create the phone numbers. 1353-- They are wired on insert to the patchfields. Again the 1354-- triggers automatically tell the PSlots to update their 1355-- backlink field. 1356-- 1357insert into PLine values ('PL.001', '-0', 'Central call', 'PS.base.ta1'); 1358insert into PLine values ('PL.002', '-101', '', 'PS.base.ta2'); 1359insert into PLine values ('PL.003', '-102', '', 'PS.base.ta3'); 1360insert into PLine values ('PL.004', '-103', '', 'PS.base.ta5'); 1361insert into PLine values ('PL.005', '-104', '', 'PS.base.ta6'); 1362insert into PLine values ('PL.006', '-106', '', 'PS.base.tb2'); 1363insert into PLine values ('PL.007', '-108', '', 'PS.base.tb3'); 1364insert into PLine values ('PL.008', '-109', '', 'PS.base.tb4'); 1365insert into PLine values ('PL.009', '-121', '', 'PS.base.tb5'); 1366insert into PLine values ('PL.010', '-122', '', 'PS.base.tb6'); 1367insert into PLine values ('PL.015', '-134', '', 'PS.first.ta1'); 1368insert into PLine values ('PL.016', '-137', '', 'PS.first.ta3'); 1369insert into PLine values ('PL.017', '-139', '', 'PS.first.ta4'); 1370insert into PLine values ('PL.018', '-362', '', 'PS.first.tb1'); 1371insert into PLine values ('PL.019', '-363', '', 'PS.first.tb2'); 1372insert into PLine values ('PL.020', '-364', '', 'PS.first.tb3'); 1373insert into PLine values ('PL.021', '-365', '', 'PS.first.tb5'); 1374insert into PLine values ('PL.022', '-367', '', 'PS.first.tb6'); 1375insert into PLine values ('PL.028', '-501', 'Fax entrance', 'PS.base.ta2'); 1376insert into PLine values ('PL.029', '-502', 'Fax first floor', 'PS.first.ta1'); 1377 1378-- 1379-- Buy some phones, plug them into the wall and patch the 1380-- phone lines to the corresponding patchfield slots. 1381-- 1382insert into PHone values ('PH.hc001', 'Hicom standard', 'WS.001.1a'); 1383update PSlot set slotlink = 'PS.base.ta1' where slotname = 'PS.base.a1'; 1384insert into PHone values ('PH.hc002', 'Hicom standard', 'WS.002.1a'); 1385update PSlot set slotlink = 'PS.base.ta5' where slotname = 'PS.base.b1'; 1386insert into PHone values ('PH.hc003', 'Hicom standard', 'WS.002.2a'); 1387update PSlot set slotlink = 'PS.base.tb2' where slotname = 'PS.base.b3'; 1388insert into PHone values ('PH.fax001', 'Canon fax', 'WS.001.2a'); 1389update PSlot set slotlink = 'PS.base.ta2' where slotname = 'PS.base.a3'; 1390 1391-- 1392-- Install a hub at one of the patchfields, plug a computers 1393-- ethernet interface into the wall and patch it to the hub. 1394-- 1395insert into Hub values ('base.hub1', 'Patchfield PF0_1 hub', 16); 1396insert into System values ('orion', 'PC'); 1397insert into IFace values ('IF', 'orion', 'eth0', 'WS.002.1b'); 1398update PSlot set slotlink = 'HS.base.hub1.1' where slotname = 'PS.base.b2'; 1399 1400-- 1401-- Now we take a look at the patchfield 1402-- 1403select * from PField_v1 where pfname = 'PF0_1' order by slotname; 1404select * from PField_v1 where pfname = 'PF0_2' order by slotname; 1405 1406-- 1407-- Finally we want errors 1408-- 1409insert into PField values ('PF1_1', 'should fail due to unique index'); 1410update PSlot set backlink = 'WS.not.there' where slotname = 'PS.base.a1'; 1411update PSlot set backlink = 'XX.illegal' where slotname = 'PS.base.a1'; 1412update PSlot set slotlink = 'PS.not.there' where slotname = 'PS.base.a1'; 1413update PSlot set slotlink = 'XX.illegal' where slotname = 'PS.base.a1'; 1414insert into HSlot values ('HS', 'base.hub1', 1, ''); 1415insert into HSlot values ('HS', 'base.hub1', 20, ''); 1416delete from HSlot; 1417insert into IFace values ('IF', 'notthere', 'eth0', ''); 1418insert into IFace values ('IF', 'orion', 'ethernet_interface_name_too_long', ''); 1419 1420 1421-- 1422-- The following tests are unrelated to the scenario outlined above; 1423-- they merely exercise specific parts of PL/pgSQL 1424-- 1425 1426-- 1427-- Test recursion, per bug report 7-Sep-01 1428-- 1429CREATE FUNCTION recursion_test(int,int) RETURNS text AS ' 1430DECLARE rslt text; 1431BEGIN 1432 IF $1 <= 0 THEN 1433 rslt = CAST($2 AS TEXT); 1434 ELSE 1435 rslt = CAST($1 AS TEXT) || '','' || recursion_test($1 - 1, $2); 1436 END IF; 1437 RETURN rslt; 1438END;' LANGUAGE plpgsql; 1439 1440SELECT recursion_test(4,3); 1441 1442-- 1443-- Test the FOUND magic variable 1444-- 1445CREATE TABLE found_test_tbl (a int); 1446 1447create function test_found() 1448 returns boolean as ' 1449 declare 1450 begin 1451 insert into found_test_tbl values (1); 1452 if FOUND then 1453 insert into found_test_tbl values (2); 1454 end if; 1455 1456 update found_test_tbl set a = 100 where a = 1; 1457 if FOUND then 1458 insert into found_test_tbl values (3); 1459 end if; 1460 1461 delete from found_test_tbl where a = 9999; -- matches no rows 1462 if not FOUND then 1463 insert into found_test_tbl values (4); 1464 end if; 1465 1466 for i in 1 .. 10 loop 1467 -- no need to do anything 1468 end loop; 1469 if FOUND then 1470 insert into found_test_tbl values (5); 1471 end if; 1472 1473 -- never executes the loop 1474 for i in 2 .. 1 loop 1475 -- no need to do anything 1476 end loop; 1477 if not FOUND then 1478 insert into found_test_tbl values (6); 1479 end if; 1480 return true; 1481 end;' language plpgsql; 1482 1483select test_found(); 1484select * from found_test_tbl; 1485 1486-- 1487-- Test set-returning functions for PL/pgSQL 1488-- 1489 1490create function test_table_func_rec() returns setof found_test_tbl as ' 1491DECLARE 1492 rec RECORD; 1493BEGIN 1494 FOR rec IN select * from found_test_tbl LOOP 1495 RETURN NEXT rec; 1496 END LOOP; 1497 RETURN; 1498END;' language plpgsql; 1499 1500select * from test_table_func_rec(); 1501 1502create function test_table_func_row() returns setof found_test_tbl as ' 1503DECLARE 1504 row found_test_tbl%ROWTYPE; 1505BEGIN 1506 FOR row IN select * from found_test_tbl LOOP 1507 RETURN NEXT row; 1508 END LOOP; 1509 RETURN; 1510END;' language plpgsql; 1511 1512select * from test_table_func_row(); 1513 1514create function test_ret_set_scalar(int,int) returns setof int as ' 1515DECLARE 1516 i int; 1517BEGIN 1518 FOR i IN $1 .. $2 LOOP 1519 RETURN NEXT i + 1; 1520 END LOOP; 1521 RETURN; 1522END;' language plpgsql; 1523 1524select * from test_ret_set_scalar(1,10); 1525 1526create function test_ret_set_rec_dyn(int) returns setof record as ' 1527DECLARE 1528 retval RECORD; 1529BEGIN 1530 IF $1 > 10 THEN 1531 SELECT INTO retval 5, 10, 15; 1532 RETURN NEXT retval; 1533 RETURN NEXT retval; 1534 ELSE 1535 SELECT INTO retval 50, 5::numeric, ''xxx''::text; 1536 RETURN NEXT retval; 1537 RETURN NEXT retval; 1538 END IF; 1539 RETURN; 1540END;' language plpgsql; 1541 1542SELECT * FROM test_ret_set_rec_dyn(1500) AS (a int, b int, c int); 1543SELECT * FROM test_ret_set_rec_dyn(5) AS (a int, b numeric, c text); 1544 1545create function test_ret_rec_dyn(int) returns record as ' 1546DECLARE 1547 retval RECORD; 1548BEGIN 1549 IF $1 > 10 THEN 1550 SELECT INTO retval 5, 10, 15; 1551 RETURN retval; 1552 ELSE 1553 SELECT INTO retval 50, 5::numeric, ''xxx''::text; 1554 RETURN retval; 1555 END IF; 1556END;' language plpgsql; 1557 1558SELECT * FROM test_ret_rec_dyn(1500) AS (a int, b int, c int); 1559SELECT * FROM test_ret_rec_dyn(5) AS (a int, b numeric, c text); 1560 1561-- 1562-- Test handling of OUT parameters, including polymorphic cases. 1563-- Note that RETURN is optional with OUT params; we try both ways. 1564-- 1565 1566-- wrong way to do it: 1567create function f1(in i int, out j int) returns int as $$ 1568begin 1569 return i+1; 1570end$$ language plpgsql; 1571 1572create function f1(in i int, out j int) as $$ 1573begin 1574 j := i+1; 1575 return; 1576end$$ language plpgsql; 1577 1578select f1(42); 1579select * from f1(42); 1580 1581create or replace function f1(inout i int) as $$ 1582begin 1583 i := i+1; 1584end$$ language plpgsql; 1585 1586select f1(42); 1587select * from f1(42); 1588 1589drop function f1(int); 1590 1591create function f1(in i int, out j int) returns setof int as $$ 1592begin 1593 j := i+1; 1594 return next; 1595 j := i+2; 1596 return next; 1597 return; 1598end$$ language plpgsql; 1599 1600select * from f1(42); 1601 1602drop function f1(int); 1603 1604create function f1(in i int, out j int, out k text) as $$ 1605begin 1606 j := i; 1607 j := j+1; 1608 k := 'foo'; 1609end$$ language plpgsql; 1610 1611select f1(42); 1612select * from f1(42); 1613 1614drop function f1(int); 1615 1616create function f1(in i int, out j int, out k text) returns setof record as $$ 1617begin 1618 j := i+1; 1619 k := 'foo'; 1620 return next; 1621 j := j+1; 1622 k := 'foot'; 1623 return next; 1624end$$ language plpgsql; 1625 1626select * from f1(42); 1627 1628drop function f1(int); 1629 1630create function duplic(in i anyelement, out j anyelement, out k anyarray) as $$ 1631begin 1632 j := i; 1633 k := array[j,j]; 1634 return; 1635end$$ language plpgsql; 1636 1637select * from duplic(42); 1638select * from duplic('foo'::text); 1639 1640drop function duplic(anyelement); 1641 1642-- 1643-- test PERFORM 1644-- 1645 1646create table perform_test ( 1647 a INT, 1648 b INT 1649); 1650 1651create function simple_func(int) returns boolean as ' 1652BEGIN 1653 IF $1 < 20 THEN 1654 INSERT INTO perform_test VALUES ($1, $1 + 10); 1655 RETURN TRUE; 1656 ELSE 1657 RETURN FALSE; 1658 END IF; 1659END;' language plpgsql; 1660 1661create function perform_test_func() returns void as ' 1662BEGIN 1663 IF FOUND then 1664 INSERT INTO perform_test VALUES (100, 100); 1665 END IF; 1666 1667 PERFORM simple_func(5); 1668 1669 IF FOUND then 1670 INSERT INTO perform_test VALUES (100, 100); 1671 END IF; 1672 1673 PERFORM simple_func(50); 1674 1675 IF FOUND then 1676 INSERT INTO perform_test VALUES (100, 100); 1677 END IF; 1678 1679 RETURN; 1680END;' language plpgsql; 1681 1682SELECT perform_test_func(); 1683SELECT * FROM perform_test; 1684 1685drop table perform_test; 1686 1687-- 1688-- Test error trapping 1689-- 1690 1691create function trap_zero_divide(int) returns int as $$ 1692declare x int; 1693 sx smallint; 1694begin 1695 begin -- start a subtransaction 1696 raise notice 'should see this'; 1697 x := 100 / $1; 1698 raise notice 'should see this only if % <> 0', $1; 1699 sx := $1; 1700 raise notice 'should see this only if % fits in smallint', $1; 1701 if $1 < 0 then 1702 raise exception '% is less than zero', $1; 1703 end if; 1704 exception 1705 when division_by_zero then 1706 raise notice 'caught division_by_zero'; 1707 x := -1; 1708 when NUMERIC_VALUE_OUT_OF_RANGE then 1709 raise notice 'caught numeric_value_out_of_range'; 1710 x := -2; 1711 end; 1712 return x; 1713end$$ language plpgsql; 1714 1715select trap_zero_divide(50); 1716select trap_zero_divide(0); 1717select trap_zero_divide(100000); 1718select trap_zero_divide(-100); 1719 1720create function trap_matching_test(int) returns int as $$ 1721declare x int; 1722 sx smallint; 1723 y int; 1724begin 1725 begin -- start a subtransaction 1726 x := 100 / $1; 1727 sx := $1; 1728 select into y unique1 from tenk1 where unique2 = 1729 (select unique2 from tenk1 b where ten = $1); 1730 exception 1731 when data_exception then -- category match 1732 raise notice 'caught data_exception'; 1733 x := -1; 1734 when NUMERIC_VALUE_OUT_OF_RANGE OR CARDINALITY_VIOLATION then 1735 raise notice 'caught numeric_value_out_of_range or cardinality_violation'; 1736 x := -2; 1737 end; 1738 return x; 1739end$$ language plpgsql; 1740 1741select trap_matching_test(50); 1742select trap_matching_test(0); 1743select trap_matching_test(100000); 1744select trap_matching_test(1); 1745 1746create temp table foo (f1 int); 1747 1748create function subxact_rollback_semantics() returns int as $$ 1749declare x int; 1750begin 1751 x := 1; 1752 insert into foo values(x); 1753 begin 1754 x := x + 1; 1755 insert into foo values(x); 1756 raise exception 'inner'; 1757 exception 1758 when others then 1759 x := x * 10; 1760 end; 1761 insert into foo values(x); 1762 return x; 1763end$$ language plpgsql; 1764 1765select subxact_rollback_semantics(); 1766select * from foo; 1767drop table foo; 1768 1769create function trap_timeout() returns void as $$ 1770begin 1771 declare x int; 1772 begin 1773 -- we assume this will take longer than 2 seconds: 1774 select count(*) into x from tenk1 a, tenk1 b, tenk1 c; 1775 exception 1776 when others then 1777 raise notice 'caught others?'; 1778 when query_canceled then 1779 raise notice 'nyeah nyeah, can''t stop me'; 1780 end; 1781 -- Abort transaction to abandon the statement_timeout setting. Otherwise, 1782 -- the next top-level statement would be vulnerable to the timeout. 1783 raise exception 'end of function'; 1784end$$ language plpgsql; 1785 1786begin; 1787set statement_timeout to 2000; 1788select trap_timeout(); 1789rollback; 1790 1791-- Test for pass-by-ref values being stored in proper context 1792create function test_variable_storage() returns text as $$ 1793declare x text; 1794begin 1795 x := '1234'; 1796 begin 1797 x := x || '5678'; 1798 -- force error inside subtransaction SPI context 1799 perform trap_zero_divide(-100); 1800 exception 1801 when others then 1802 x := x || '9012'; 1803 end; 1804 return x; 1805end$$ language plpgsql; 1806 1807select test_variable_storage(); 1808 1809-- 1810-- test foreign key error trapping 1811-- 1812 1813create temp table master(f1 int primary key); 1814 1815create temp table slave(f1 int references master deferrable); 1816 1817insert into master values(1); 1818insert into slave values(1); 1819insert into slave values(2); -- fails 1820 1821create function trap_foreign_key(int) returns int as $$ 1822begin 1823 begin -- start a subtransaction 1824 insert into slave values($1); 1825 exception 1826 when foreign_key_violation then 1827 raise notice 'caught foreign_key_violation'; 1828 return 0; 1829 end; 1830 return 1; 1831end$$ language plpgsql; 1832 1833create function trap_foreign_key_2() returns int as $$ 1834begin 1835 begin -- start a subtransaction 1836 set constraints all immediate; 1837 exception 1838 when foreign_key_violation then 1839 raise notice 'caught foreign_key_violation'; 1840 return 0; 1841 end; 1842 return 1; 1843end$$ language plpgsql; 1844 1845select trap_foreign_key(1); 1846select trap_foreign_key(2); -- detects FK violation 1847 1848begin; 1849 set constraints all deferred; 1850 select trap_foreign_key(2); -- should not detect FK violation 1851 savepoint x; 1852 set constraints all immediate; -- fails 1853 rollback to x; 1854 select trap_foreign_key_2(); -- detects FK violation 1855commit; -- still fails 1856 1857drop function trap_foreign_key(int); 1858drop function trap_foreign_key_2(); 1859 1860-- 1861-- Test proper snapshot handling in simple expressions 1862-- 1863 1864create temp table users(login text, id serial); 1865 1866create function sp_id_user(a_login text) returns int as $$ 1867declare x int; 1868begin 1869 select into x id from users where login = a_login; 1870 if found then return x; end if; 1871 return 0; 1872end$$ language plpgsql stable; 1873 1874insert into users values('user1'); 1875 1876select sp_id_user('user1'); 1877select sp_id_user('userx'); 1878 1879create function sp_add_user(a_login text) returns int as $$ 1880declare my_id_user int; 1881begin 1882 my_id_user = sp_id_user( a_login ); 1883 IF my_id_user > 0 THEN 1884 RETURN -1; -- error code for existing user 1885 END IF; 1886 INSERT INTO users ( login ) VALUES ( a_login ); 1887 my_id_user = sp_id_user( a_login ); 1888 IF my_id_user = 0 THEN 1889 RETURN -2; -- error code for insertion failure 1890 END IF; 1891 RETURN my_id_user; 1892end$$ language plpgsql; 1893 1894select sp_add_user('user1'); 1895select sp_add_user('user2'); 1896select sp_add_user('user2'); 1897select sp_add_user('user3'); 1898select sp_add_user('user3'); 1899 1900drop function sp_add_user(text); 1901drop function sp_id_user(text); 1902 1903-- 1904-- tests for refcursors 1905-- 1906create table rc_test (a int, b int); 1907copy rc_test from stdin; 19085 10 190950 100 1910500 1000 1911\. 1912 1913create function return_refcursor(rc refcursor) returns refcursor as $$ 1914begin 1915 open rc for select a from rc_test; 1916 return rc; 1917end 1918$$ language plpgsql; 1919 1920create function refcursor_test1(refcursor) returns refcursor as $$ 1921begin 1922 perform return_refcursor($1); 1923 return $1; 1924end 1925$$ language plpgsql; 1926 1927begin; 1928 1929select refcursor_test1('test1'); 1930fetch next in test1; 1931 1932select refcursor_test1('test2'); 1933fetch all from test2; 1934 1935commit; 1936 1937-- should fail 1938fetch next from test1; 1939 1940create function refcursor_test2(int, int) returns boolean as $$ 1941declare 1942 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2; 1943 nonsense record; 1944begin 1945 open c1($1, $2); 1946 fetch c1 into nonsense; 1947 close c1; 1948 if found then 1949 return true; 1950 else 1951 return false; 1952 end if; 1953end 1954$$ language plpgsql; 1955 1956select refcursor_test2(20000, 20000) as "Should be false", 1957 refcursor_test2(20, 20) as "Should be true"; 1958 1959-- 1960-- tests for cursors with named parameter arguments 1961-- 1962create function namedparmcursor_test1(int, int) returns boolean as $$ 1963declare 1964 c1 cursor (param1 int, param12 int) for select * from rc_test where a > param1 and b > param12; 1965 nonsense record; 1966begin 1967 open c1(param12 := $2, param1 := $1); 1968 fetch c1 into nonsense; 1969 close c1; 1970 if found then 1971 return true; 1972 else 1973 return false; 1974 end if; 1975end 1976$$ language plpgsql; 1977 1978select namedparmcursor_test1(20000, 20000) as "Should be false", 1979 namedparmcursor_test1(20, 20) as "Should be true"; 1980 1981-- mixing named and positional argument notations 1982create function namedparmcursor_test2(int, int) returns boolean as $$ 1983declare 1984 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2; 1985 nonsense record; 1986begin 1987 open c1(param1 := $1, $2); 1988 fetch c1 into nonsense; 1989 close c1; 1990 if found then 1991 return true; 1992 else 1993 return false; 1994 end if; 1995end 1996$$ language plpgsql; 1997select namedparmcursor_test2(20, 20); 1998 1999-- mixing named and positional: param2 is given twice, once in named notation 2000-- and second time in positional notation. Should throw an error at parse time 2001create function namedparmcursor_test3() returns void as $$ 2002declare 2003 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2; 2004begin 2005 open c1(param2 := 20, 21); 2006end 2007$$ language plpgsql; 2008 2009-- mixing named and positional: same as previous test, but param1 is duplicated 2010create function namedparmcursor_test4() returns void as $$ 2011declare 2012 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2; 2013begin 2014 open c1(20, param1 := 21); 2015end 2016$$ language plpgsql; 2017 2018-- duplicate named parameter, should throw an error at parse time 2019create function namedparmcursor_test5() returns void as $$ 2020declare 2021 c1 cursor (p1 int, p2 int) for 2022 select * from tenk1 where thousand = p1 and tenthous = p2; 2023begin 2024 open c1 (p2 := 77, p2 := 42); 2025end 2026$$ language plpgsql; 2027 2028-- not enough parameters, should throw an error at parse time 2029create function namedparmcursor_test6() returns void as $$ 2030declare 2031 c1 cursor (p1 int, p2 int) for 2032 select * from tenk1 where thousand = p1 and tenthous = p2; 2033begin 2034 open c1 (p2 := 77); 2035end 2036$$ language plpgsql; 2037 2038-- division by zero runtime error, the context given in the error message 2039-- should be sensible 2040create function namedparmcursor_test7() returns void as $$ 2041declare 2042 c1 cursor (p1 int, p2 int) for 2043 select * from tenk1 where thousand = p1 and tenthous = p2; 2044begin 2045 open c1 (p2 := 77, p1 := 42/0); 2046end $$ language plpgsql; 2047select namedparmcursor_test7(); 2048 2049-- check that line comments work correctly within the argument list (there 2050-- is some special handling of this case in the code: the newline after the 2051-- comment must be preserved when the argument-evaluating query is 2052-- constructed, otherwise the comment effectively comments out the next 2053-- argument, too) 2054create function namedparmcursor_test8() returns int4 as $$ 2055declare 2056 c1 cursor (p1 int, p2 int) for 2057 select count(*) from tenk1 where thousand = p1 and tenthous = p2; 2058 n int4; 2059begin 2060 open c1 (77 -- test 2061 , 42); 2062 fetch c1 into n; 2063 return n; 2064end $$ language plpgsql; 2065select namedparmcursor_test8(); 2066 2067-- cursor parameter name can match plpgsql variable or unreserved keyword 2068create function namedparmcursor_test9(p1 int) returns int4 as $$ 2069declare 2070 c1 cursor (p1 int, p2 int, debug int) for 2071 select count(*) from tenk1 where thousand = p1 and tenthous = p2 2072 and four = debug; 2073 p2 int4 := 1006; 2074 n int4; 2075begin 2076 open c1 (p1 := p1, p2 := p2, debug := 2); 2077 fetch c1 into n; 2078 return n; 2079end $$ language plpgsql; 2080select namedparmcursor_test9(6); 2081 2082-- 2083-- tests for "raise" processing 2084-- 2085create function raise_test1(int) returns int as $$ 2086begin 2087 raise notice 'This message has too many parameters!', $1; 2088 return $1; 2089end; 2090$$ language plpgsql; 2091 2092create function raise_test2(int) returns int as $$ 2093begin 2094 raise notice 'This message has too few parameters: %, %, %', $1, $1; 2095 return $1; 2096end; 2097$$ language plpgsql; 2098 2099create function raise_test3(int) returns int as $$ 2100begin 2101 raise notice 'This message has no parameters (despite having %% signs in it)!'; 2102 return $1; 2103end; 2104$$ language plpgsql; 2105 2106select raise_test3(1); 2107 2108-- Test re-RAISE inside a nested exception block. This case is allowed 2109-- by Oracle's PL/SQL but was handled differently by PG before 9.1. 2110 2111CREATE FUNCTION reraise_test() RETURNS void AS $$ 2112BEGIN 2113 BEGIN 2114 RAISE syntax_error; 2115 EXCEPTION 2116 WHEN syntax_error THEN 2117 BEGIN 2118 raise notice 'exception % thrown in inner block, reraising', sqlerrm; 2119 RAISE; 2120 EXCEPTION 2121 WHEN OTHERS THEN 2122 raise notice 'RIGHT - exception % caught in inner block', sqlerrm; 2123 END; 2124 END; 2125EXCEPTION 2126 WHEN OTHERS THEN 2127 raise notice 'WRONG - exception % caught in outer block', sqlerrm; 2128END; 2129$$ LANGUAGE plpgsql; 2130 2131SELECT reraise_test(); 2132 2133-- 2134-- reject function definitions that contain malformed SQL queries at 2135-- compile-time, where possible 2136-- 2137create function bad_sql1() returns int as $$ 2138declare a int; 2139begin 2140 a := 5; 2141 Johnny Yuma; 2142 a := 10; 2143 return a; 2144end$$ language plpgsql; 2145 2146create function bad_sql2() returns int as $$ 2147declare r record; 2148begin 2149 for r in select I fought the law, the law won LOOP 2150 raise notice 'in loop'; 2151 end loop; 2152 return 5; 2153end;$$ language plpgsql; 2154 2155-- a RETURN expression is mandatory, except for void-returning 2156-- functions, where it is not allowed 2157create function missing_return_expr() returns int as $$ 2158begin 2159 return ; 2160end;$$ language plpgsql; 2161 2162create function void_return_expr() returns void as $$ 2163begin 2164 return 5; 2165end;$$ language plpgsql; 2166 2167-- VOID functions are allowed to omit RETURN 2168create function void_return_expr() returns void as $$ 2169begin 2170 perform 2+2; 2171end;$$ language plpgsql; 2172 2173select void_return_expr(); 2174 2175-- but ordinary functions are not 2176create function missing_return_expr() returns int as $$ 2177begin 2178 perform 2+2; 2179end;$$ language plpgsql; 2180 2181select missing_return_expr(); 2182 2183drop function void_return_expr(); 2184drop function missing_return_expr(); 2185 2186-- 2187-- EXECUTE ... INTO test 2188-- 2189 2190create table eifoo (i integer, y integer); 2191create type eitype as (i integer, y integer); 2192 2193create or replace function execute_into_test(varchar) returns record as $$ 2194declare 2195 _r record; 2196 _rt eifoo%rowtype; 2197 _v eitype; 2198 i int; 2199 j int; 2200 k int; 2201begin 2202 execute 'insert into '||$1||' values(10,15)'; 2203 execute 'select (row).* from (select row(10,1)::eifoo) s' into _r; 2204 raise notice '% %', _r.i, _r.y; 2205 execute 'select * from '||$1||' limit 1' into _rt; 2206 raise notice '% %', _rt.i, _rt.y; 2207 execute 'select *, 20 from '||$1||' limit 1' into i, j, k; 2208 raise notice '% % %', i, j, k; 2209 execute 'select 1,2' into _v; 2210 return _v; 2211end; $$ language plpgsql; 2212 2213select execute_into_test('eifoo'); 2214 2215drop table eifoo cascade; 2216drop type eitype cascade; 2217 2218-- 2219-- SQLSTATE and SQLERRM test 2220-- 2221 2222create function excpt_test1() returns void as $$ 2223begin 2224 raise notice '% %', sqlstate, sqlerrm; 2225end; $$ language plpgsql; 2226-- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION 2227-- blocks 2228select excpt_test1(); 2229 2230create function excpt_test2() returns void as $$ 2231begin 2232 begin 2233 begin 2234 raise notice '% %', sqlstate, sqlerrm; 2235 end; 2236 end; 2237end; $$ language plpgsql; 2238-- should fail 2239select excpt_test2(); 2240 2241create function excpt_test3() returns void as $$ 2242begin 2243 begin 2244 raise exception 'user exception'; 2245 exception when others then 2246 raise notice 'caught exception % %', sqlstate, sqlerrm; 2247 begin 2248 raise notice '% %', sqlstate, sqlerrm; 2249 perform 10/0; 2250 exception 2251 when substring_error then 2252 -- this exception handler shouldn't be invoked 2253 raise notice 'unexpected exception: % %', sqlstate, sqlerrm; 2254 when division_by_zero then 2255 raise notice 'caught exception % %', sqlstate, sqlerrm; 2256 end; 2257 raise notice '% %', sqlstate, sqlerrm; 2258 end; 2259end; $$ language plpgsql; 2260select excpt_test3(); 2261 2262create function excpt_test4() returns text as $$ 2263begin 2264 begin perform 1/0; 2265 exception when others then return sqlerrm; end; 2266end; $$ language plpgsql; 2267select excpt_test4(); 2268 2269drop function excpt_test1(); 2270drop function excpt_test2(); 2271drop function excpt_test3(); 2272drop function excpt_test4(); 2273 2274-- parameters of raise stmt can be expressions 2275create function raise_exprs() returns void as $$ 2276declare 2277 a integer[] = '{10,20,30}'; 2278 c varchar = 'xyz'; 2279 i integer; 2280begin 2281 i := 2; 2282 raise notice '%; %; %; %; %; %', a, a[i], c, (select c || 'abc'), row(10,'aaa',NULL,30), NULL; 2283end;$$ language plpgsql; 2284 2285select raise_exprs(); 2286drop function raise_exprs(); 2287 2288-- continue statement 2289create table conttesttbl(idx serial, v integer); 2290insert into conttesttbl(v) values(10); 2291insert into conttesttbl(v) values(20); 2292insert into conttesttbl(v) values(30); 2293insert into conttesttbl(v) values(40); 2294 2295create function continue_test1() returns void as $$ 2296declare _i integer = 0; _r record; 2297begin 2298 raise notice '---1---'; 2299 loop 2300 _i := _i + 1; 2301 raise notice '%', _i; 2302 continue when _i < 10; 2303 exit; 2304 end loop; 2305 2306 raise notice '---2---'; 2307 <<lbl>> 2308 loop 2309 _i := _i - 1; 2310 loop 2311 raise notice '%', _i; 2312 continue lbl when _i > 0; 2313 exit lbl; 2314 end loop; 2315 end loop; 2316 2317 raise notice '---3---'; 2318 <<the_loop>> 2319 while _i < 10 loop 2320 _i := _i + 1; 2321 continue the_loop when _i % 2 = 0; 2322 raise notice '%', _i; 2323 end loop; 2324 2325 raise notice '---4---'; 2326 for _i in 1..10 loop 2327 begin 2328 -- applies to outer loop, not the nested begin block 2329 continue when _i < 5; 2330 raise notice '%', _i; 2331 end; 2332 end loop; 2333 2334 raise notice '---5---'; 2335 for _r in select * from conttesttbl loop 2336 continue when _r.v <= 20; 2337 raise notice '%', _r.v; 2338 end loop; 2339 2340 raise notice '---6---'; 2341 for _r in execute 'select * from conttesttbl' loop 2342 continue when _r.v <= 20; 2343 raise notice '%', _r.v; 2344 end loop; 2345 2346 raise notice '---7---'; 2347 for _i in 1..3 loop 2348 raise notice '%', _i; 2349 continue when _i = 3; 2350 end loop; 2351 2352 raise notice '---8---'; 2353 _i := 1; 2354 while _i <= 3 loop 2355 raise notice '%', _i; 2356 _i := _i + 1; 2357 continue when _i = 3; 2358 end loop; 2359 2360 raise notice '---9---'; 2361 for _r in select * from conttesttbl order by v limit 1 loop 2362 raise notice '%', _r.v; 2363 continue; 2364 end loop; 2365 2366 raise notice '---10---'; 2367 for _r in execute 'select * from conttesttbl order by v limit 1' loop 2368 raise notice '%', _r.v; 2369 continue; 2370 end loop; 2371end; $$ language plpgsql; 2372 2373select continue_test1(); 2374 2375drop function continue_test1(); 2376drop table conttesttbl; 2377 2378-- should fail: CONTINUE is only legal inside a loop 2379create function continue_error1() returns void as $$ 2380begin 2381 begin 2382 continue; 2383 end; 2384end; 2385$$ language plpgsql; 2386 2387-- should fail: unlabeled EXIT is only legal inside a loop 2388create function exit_error1() returns void as $$ 2389begin 2390 begin 2391 exit; 2392 end; 2393end; 2394$$ language plpgsql; 2395 2396-- should fail: no such label 2397create function continue_error2() returns void as $$ 2398begin 2399 begin 2400 loop 2401 continue no_such_label; 2402 end loop; 2403 end; 2404end; 2405$$ language plpgsql; 2406 2407-- should fail: no such label 2408create function exit_error2() returns void as $$ 2409begin 2410 begin 2411 loop 2412 exit no_such_label; 2413 end loop; 2414 end; 2415end; 2416$$ language plpgsql; 2417 2418-- should fail: CONTINUE can't reference the label of a named block 2419create function continue_error3() returns void as $$ 2420begin 2421 <<begin_block1>> 2422 begin 2423 loop 2424 continue begin_block1; 2425 end loop; 2426 end; 2427end; 2428$$ language plpgsql; 2429 2430-- On the other hand, EXIT *can* reference the label of a named block 2431create function exit_block1() returns void as $$ 2432begin 2433 <<begin_block1>> 2434 begin 2435 loop 2436 exit begin_block1; 2437 raise exception 'should not get here'; 2438 end loop; 2439 end; 2440end; 2441$$ language plpgsql; 2442 2443select exit_block1(); 2444drop function exit_block1(); 2445 2446-- verbose end block and end loop 2447create function end_label1() returns void as $$ 2448<<blbl>> 2449begin 2450 <<flbl1>> 2451 for _i in 1 .. 10 loop 2452 exit flbl1; 2453 end loop flbl1; 2454 <<flbl2>> 2455 for _i in 1 .. 10 loop 2456 exit flbl2; 2457 end loop; 2458end blbl; 2459$$ language plpgsql; 2460 2461select end_label1(); 2462drop function end_label1(); 2463 2464-- should fail: undefined end label 2465create function end_label2() returns void as $$ 2466begin 2467 for _i in 1 .. 10 loop 2468 exit; 2469 end loop flbl1; 2470end; 2471$$ language plpgsql; 2472 2473-- should fail: end label does not match start label 2474create function end_label3() returns void as $$ 2475<<outer_label>> 2476begin 2477 <<inner_label>> 2478 for _i in 1 .. 10 loop 2479 exit; 2480 end loop outer_label; 2481end; 2482$$ language plpgsql; 2483 2484-- should fail: end label on a block without a start label 2485create function end_label4() returns void as $$ 2486<<outer_label>> 2487begin 2488 for _i in 1 .. 10 loop 2489 exit; 2490 end loop outer_label; 2491end; 2492$$ language plpgsql; 2493 2494-- using list of scalars in fori and fore stmts 2495create function for_vect() returns void as $proc$ 2496<<lbl>>declare a integer; b varchar; c varchar; r record; 2497begin 2498 -- fori 2499 for i in 1 .. 3 loop 2500 raise notice '%', i; 2501 end loop; 2502 -- fore with record var 2503 for r in select gs as aa, 'BB' as bb, 'CC' as cc from generate_series(1,4) gs loop 2504 raise notice '% % %', r.aa, r.bb, r.cc; 2505 end loop; 2506 -- fore with single scalar 2507 for a in select gs from generate_series(1,4) gs loop 2508 raise notice '%', a; 2509 end loop; 2510 -- fore with multiple scalars 2511 for a,b,c in select gs, 'BB','CC' from generate_series(1,4) gs loop 2512 raise notice '% % %', a, b, c; 2513 end loop; 2514 -- using qualified names in fors, fore is enabled, disabled only for fori 2515 for lbl.a, lbl.b, lbl.c in execute $$select gs, 'bb','cc' from generate_series(1,4) gs$$ loop 2516 raise notice '% % %', a, b, c; 2517 end loop; 2518end; 2519$proc$ language plpgsql; 2520 2521select for_vect(); 2522 2523-- regression test: verify that multiple uses of same plpgsql datum within 2524-- a SQL command all get mapped to the same $n parameter. The return value 2525-- of the SELECT is not important, we only care that it doesn't fail with 2526-- a complaint about an ungrouped column reference. 2527create function multi_datum_use(p1 int) returns bool as $$ 2528declare 2529 x int; 2530 y int; 2531begin 2532 select into x,y unique1/p1, unique1/$1 from tenk1 group by unique1/p1; 2533 return x = y; 2534end$$ language plpgsql; 2535 2536select multi_datum_use(42); 2537 2538-- 2539-- Test STRICT limiter in both planned and EXECUTE invocations. 2540-- Note that a data-modifying query is quasi strict (disallow multi rows) 2541-- by default in the planned case, but not in EXECUTE. 2542-- 2543 2544create temp table foo (f1 int, f2 int); 2545 2546insert into foo values (1,2), (3,4); 2547 2548create or replace function footest() returns void as $$ 2549declare x record; 2550begin 2551 -- should work 2552 insert into foo values(5,6) returning * into x; 2553 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2554end$$ language plpgsql; 2555 2556select footest(); 2557 2558create or replace function footest() returns void as $$ 2559declare x record; 2560begin 2561 -- should fail due to implicit strict 2562 insert into foo values(7,8),(9,10) returning * into x; 2563 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2564end$$ language plpgsql; 2565 2566select footest(); 2567 2568create or replace function footest() returns void as $$ 2569declare x record; 2570begin 2571 -- should work 2572 execute 'insert into foo values(5,6) returning *' into x; 2573 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2574end$$ language plpgsql; 2575 2576select footest(); 2577 2578create or replace function footest() returns void as $$ 2579declare x record; 2580begin 2581 -- this should work since EXECUTE isn't as picky 2582 execute 'insert into foo values(7,8),(9,10) returning *' into x; 2583 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2584end$$ language plpgsql; 2585 2586select footest(); 2587 2588select * from foo; 2589 2590create or replace function footest() returns void as $$ 2591declare x record; 2592begin 2593 -- should work 2594 select * from foo where f1 = 3 into strict x; 2595 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2596end$$ language plpgsql; 2597 2598select footest(); 2599 2600create or replace function footest() returns void as $$ 2601declare x record; 2602begin 2603 -- should fail, no rows 2604 select * from foo where f1 = 0 into strict x; 2605 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2606end$$ language plpgsql; 2607 2608select footest(); 2609 2610create or replace function footest() returns void as $$ 2611declare x record; 2612begin 2613 -- should fail, too many rows 2614 select * from foo where f1 > 3 into strict x; 2615 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2616end$$ language plpgsql; 2617 2618select footest(); 2619 2620create or replace function footest() returns void as $$ 2621declare x record; 2622begin 2623 -- should work 2624 execute 'select * from foo where f1 = 3' into strict x; 2625 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2626end$$ language plpgsql; 2627 2628select footest(); 2629 2630create or replace function footest() returns void as $$ 2631declare x record; 2632begin 2633 -- should fail, no rows 2634 execute 'select * from foo where f1 = 0' into strict x; 2635 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2636end$$ language plpgsql; 2637 2638select footest(); 2639 2640create or replace function footest() returns void as $$ 2641declare x record; 2642begin 2643 -- should fail, too many rows 2644 execute 'select * from foo where f1 > 3' into strict x; 2645 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2646end$$ language plpgsql; 2647 2648select footest(); 2649 2650drop function footest(); 2651 2652-- test printing parameters after failure due to STRICT 2653 2654set plpgsql.print_strict_params to true; 2655 2656create or replace function footest() returns void as $$ 2657declare 2658x record; 2659p1 int := 2; 2660p3 text := 'foo'; 2661begin 2662 -- no rows 2663 select * from foo where f1 = p1 and f1::text = p3 into strict x; 2664 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2665end$$ language plpgsql; 2666 2667select footest(); 2668 2669create or replace function footest() returns void as $$ 2670declare 2671x record; 2672p1 int := 2; 2673p3 text := 'foo'; 2674begin 2675 -- too many rows 2676 select * from foo where f1 > p1 or f1::text = p3 into strict x; 2677 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2678end$$ language plpgsql; 2679 2680select footest(); 2681 2682create or replace function footest() returns void as $$ 2683declare x record; 2684begin 2685 -- too many rows, no params 2686 select * from foo where f1 > 3 into strict x; 2687 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2688end$$ language plpgsql; 2689 2690select footest(); 2691 2692create or replace function footest() returns void as $$ 2693declare x record; 2694begin 2695 -- no rows 2696 execute 'select * from foo where f1 = $1 or f1::text = $2' using 0, 'foo' into strict x; 2697 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2698end$$ language plpgsql; 2699 2700select footest(); 2701 2702create or replace function footest() returns void as $$ 2703declare x record; 2704begin 2705 -- too many rows 2706 execute 'select * from foo where f1 > $1' using 1 into strict x; 2707 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2708end$$ language plpgsql; 2709 2710select footest(); 2711 2712create or replace function footest() returns void as $$ 2713declare x record; 2714begin 2715 -- too many rows, no parameters 2716 execute 'select * from foo where f1 > 3' into strict x; 2717 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2718end$$ language plpgsql; 2719 2720select footest(); 2721 2722create or replace function footest() returns void as $$ 2723-- override the global 2724#print_strict_params off 2725declare 2726x record; 2727p1 int := 2; 2728p3 text := 'foo'; 2729begin 2730 -- too many rows 2731 select * from foo where f1 > p1 or f1::text = p3 into strict x; 2732 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2733end$$ language plpgsql; 2734 2735select footest(); 2736 2737reset plpgsql.print_strict_params; 2738 2739create or replace function footest() returns void as $$ 2740-- override the global 2741#print_strict_params on 2742declare 2743x record; 2744p1 int := 2; 2745p3 text := 'foo'; 2746begin 2747 -- too many rows 2748 select * from foo where f1 > p1 or f1::text = p3 into strict x; 2749 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2750end$$ language plpgsql; 2751 2752select footest(); 2753 2754-- test warnings and errors 2755set plpgsql.extra_warnings to 'all'; 2756set plpgsql.extra_warnings to 'none'; 2757set plpgsql.extra_errors to 'all'; 2758set plpgsql.extra_errors to 'none'; 2759 2760-- test warnings when shadowing a variable 2761 2762set plpgsql.extra_warnings to 'shadowed_variables'; 2763 2764-- simple shadowing of input and output parameters 2765create or replace function shadowtest(in1 int) 2766 returns table (out1 int) as $$ 2767declare 2768in1 int; 2769out1 int; 2770begin 2771end 2772$$ language plpgsql; 2773select shadowtest(1); 2774 2775set plpgsql.extra_warnings to 'shadowed_variables'; 2776select shadowtest(1); 2777create or replace function shadowtest(in1 int) 2778 returns table (out1 int) as $$ 2779declare 2780in1 int; 2781out1 int; 2782begin 2783end 2784$$ language plpgsql; 2785select shadowtest(1); 2786drop function shadowtest(int); 2787 2788-- shadowing in a second DECLARE block 2789create or replace function shadowtest() 2790 returns void as $$ 2791declare 2792f1 int; 2793begin 2794 declare 2795 f1 int; 2796 begin 2797 end; 2798end$$ language plpgsql; 2799drop function shadowtest(); 2800 2801-- several levels of shadowing 2802create or replace function shadowtest(in1 int) 2803 returns void as $$ 2804declare 2805in1 int; 2806begin 2807 declare 2808 in1 int; 2809 begin 2810 end; 2811end$$ language plpgsql; 2812drop function shadowtest(int); 2813 2814-- shadowing in cursor definitions 2815create or replace function shadowtest() 2816 returns void as $$ 2817declare 2818f1 int; 2819c1 cursor (f1 int) for select 1; 2820begin 2821end$$ language plpgsql; 2822drop function shadowtest(); 2823 2824-- test errors when shadowing a variable 2825 2826set plpgsql.extra_errors to 'shadowed_variables'; 2827 2828create or replace function shadowtest(f1 int) 2829 returns boolean as $$ 2830declare f1 int; begin return 1; end $$ language plpgsql; 2831 2832select shadowtest(1); 2833 2834reset plpgsql.extra_errors; 2835reset plpgsql.extra_warnings; 2836 2837create or replace function shadowtest(f1 int) 2838 returns boolean as $$ 2839declare f1 int; begin return 1; end $$ language plpgsql; 2840 2841select shadowtest(1); 2842 2843-- test scrollable cursor support 2844 2845create function sc_test() returns setof integer as $$ 2846declare 2847 c scroll cursor for select f1 from int4_tbl; 2848 x integer; 2849begin 2850 open c; 2851 fetch last from c into x; 2852 while found loop 2853 return next x; 2854 fetch prior from c into x; 2855 end loop; 2856 close c; 2857end; 2858$$ language plpgsql; 2859 2860select * from sc_test(); 2861 2862create or replace function sc_test() returns setof integer as $$ 2863declare 2864 c no scroll cursor for select f1 from int4_tbl; 2865 x integer; 2866begin 2867 open c; 2868 fetch last from c into x; 2869 while found loop 2870 return next x; 2871 fetch prior from c into x; 2872 end loop; 2873 close c; 2874end; 2875$$ language plpgsql; 2876 2877select * from sc_test(); -- fails because of NO SCROLL specification 2878 2879create or replace function sc_test() returns setof integer as $$ 2880declare 2881 c refcursor; 2882 x integer; 2883begin 2884 open c scroll for select f1 from int4_tbl; 2885 fetch last from c into x; 2886 while found loop 2887 return next x; 2888 fetch prior from c into x; 2889 end loop; 2890 close c; 2891end; 2892$$ language plpgsql; 2893 2894select * from sc_test(); 2895 2896create or replace function sc_test() returns setof integer as $$ 2897declare 2898 c refcursor; 2899 x integer; 2900begin 2901 open c scroll for execute 'select f1 from int4_tbl'; 2902 fetch last from c into x; 2903 while found loop 2904 return next x; 2905 fetch relative -2 from c into x; 2906 end loop; 2907 close c; 2908end; 2909$$ language plpgsql; 2910 2911select * from sc_test(); 2912 2913create or replace function sc_test() returns setof integer as $$ 2914declare 2915 c refcursor; 2916 x integer; 2917begin 2918 open c scroll for execute 'select f1 from int4_tbl'; 2919 fetch last from c into x; 2920 while found loop 2921 return next x; 2922 move backward 2 from c; 2923 fetch relative -1 from c into x; 2924 end loop; 2925 close c; 2926end; 2927$$ language plpgsql; 2928 2929select * from sc_test(); 2930 2931create or replace function sc_test() returns setof integer as $$ 2932declare 2933 c cursor for select * from generate_series(1, 10); 2934 x integer; 2935begin 2936 open c; 2937 loop 2938 move relative 2 in c; 2939 if not found then 2940 exit; 2941 end if; 2942 fetch next from c into x; 2943 if found then 2944 return next x; 2945 end if; 2946 end loop; 2947 close c; 2948end; 2949$$ language plpgsql; 2950 2951select * from sc_test(); 2952 2953create or replace function sc_test() returns setof integer as $$ 2954declare 2955 c cursor for select * from generate_series(1, 10); 2956 x integer; 2957begin 2958 open c; 2959 move forward all in c; 2960 fetch backward from c into x; 2961 if found then 2962 return next x; 2963 end if; 2964 close c; 2965end; 2966$$ language plpgsql; 2967 2968select * from sc_test(); 2969 2970drop function sc_test(); 2971 2972-- test qualified variable names 2973 2974create function pl_qual_names (param1 int) returns void as $$ 2975<<outerblock>> 2976declare 2977 param1 int := 1; 2978begin 2979 <<innerblock>> 2980 declare 2981 param1 int := 2; 2982 begin 2983 raise notice 'param1 = %', param1; 2984 raise notice 'pl_qual_names.param1 = %', pl_qual_names.param1; 2985 raise notice 'outerblock.param1 = %', outerblock.param1; 2986 raise notice 'innerblock.param1 = %', innerblock.param1; 2987 end; 2988end; 2989$$ language plpgsql; 2990 2991select pl_qual_names(42); 2992 2993drop function pl_qual_names(int); 2994 2995-- tests for RETURN QUERY 2996create function ret_query1(out int, out int) returns setof record as $$ 2997begin 2998 $1 := -1; 2999 $2 := -2; 3000 return next; 3001 return query select x + 1, x * 10 from generate_series(0, 10) s (x); 3002 return next; 3003end; 3004$$ language plpgsql; 3005 3006select * from ret_query1(); 3007 3008create type record_type as (x text, y int, z boolean); 3009 3010create or replace function ret_query2(lim int) returns setof record_type as $$ 3011begin 3012 return query select md5(s.x::text), s.x, s.x > 0 3013 from generate_series(-8, lim) s (x) where s.x % 2 = 0; 3014end; 3015$$ language plpgsql; 3016 3017select * from ret_query2(8); 3018 3019-- test EXECUTE USING 3020create function exc_using(int, text) returns int as $$ 3021declare i int; 3022begin 3023 for i in execute 'select * from generate_series(1,$1)' using $1+1 loop 3024 raise notice '%', i; 3025 end loop; 3026 execute 'select $2 + $2*3 + length($1)' into i using $2,$1; 3027 return i; 3028end 3029$$ language plpgsql; 3030 3031select exc_using(5, 'foobar'); 3032 3033drop function exc_using(int, text); 3034 3035create or replace function exc_using(int) returns void as $$ 3036declare 3037 c refcursor; 3038 i int; 3039begin 3040 open c for execute 'select * from generate_series(1,$1)' using $1+1; 3041 loop 3042 fetch c into i; 3043 exit when not found; 3044 raise notice '%', i; 3045 end loop; 3046 close c; 3047 return; 3048end; 3049$$ language plpgsql; 3050 3051select exc_using(5); 3052 3053drop function exc_using(int); 3054 3055-- test FOR-over-cursor 3056 3057create or replace function forc01() returns void as $$ 3058declare 3059 c cursor(r1 integer, r2 integer) 3060 for select * from generate_series(r1,r2) i; 3061 c2 cursor 3062 for select * from generate_series(41,43) i; 3063begin 3064 for r in c(5,7) loop 3065 raise notice '% from %', r.i, c; 3066 end loop; 3067 -- again, to test if cursor was closed properly 3068 for r in c(9,10) loop 3069 raise notice '% from %', r.i, c; 3070 end loop; 3071 -- and test a parameterless cursor 3072 for r in c2 loop 3073 raise notice '% from %', r.i, c2; 3074 end loop; 3075 -- and try it with a hand-assigned name 3076 raise notice 'after loop, c2 = %', c2; 3077 c2 := 'special_name'; 3078 for r in c2 loop 3079 raise notice '% from %', r.i, c2; 3080 end loop; 3081 raise notice 'after loop, c2 = %', c2; 3082 -- and try it with a generated name 3083 -- (which we can't show in the output because it's variable) 3084 c2 := null; 3085 for r in c2 loop 3086 raise notice '%', r.i; 3087 end loop; 3088 raise notice 'after loop, c2 = %', c2; 3089 return; 3090end; 3091$$ language plpgsql; 3092 3093select forc01(); 3094 3095-- try updating the cursor's current row 3096 3097create temp table forc_test as 3098 select n as i, n as j from generate_series(1,10) n; 3099 3100create or replace function forc01() returns void as $$ 3101declare 3102 c cursor for select * from forc_test; 3103begin 3104 for r in c loop 3105 raise notice '%, %', r.i, r.j; 3106 update forc_test set i = i * 100, j = r.j * 2 where current of c; 3107 end loop; 3108end; 3109$$ language plpgsql; 3110 3111select forc01(); 3112 3113select * from forc_test; 3114 3115-- same, with a cursor whose portal name doesn't match variable name 3116create or replace function forc01() returns void as $$ 3117declare 3118 c refcursor := 'fooled_ya'; 3119 r record; 3120begin 3121 open c for select * from forc_test; 3122 loop 3123 fetch c into r; 3124 exit when not found; 3125 raise notice '%, %', r.i, r.j; 3126 update forc_test set i = i * 100, j = r.j * 2 where current of c; 3127 end loop; 3128end; 3129$$ language plpgsql; 3130 3131select forc01(); 3132 3133select * from forc_test; 3134 3135drop function forc01(); 3136 3137-- fail because cursor has no query bound to it 3138 3139create or replace function forc_bad() returns void as $$ 3140declare 3141 c refcursor; 3142begin 3143 for r in c loop 3144 raise notice '%', r.i; 3145 end loop; 3146end; 3147$$ language plpgsql; 3148 3149-- test RETURN QUERY EXECUTE 3150 3151create or replace function return_dquery() 3152returns setof int as $$ 3153begin 3154 return query execute 'select * from (values(10),(20)) f'; 3155 return query execute 'select * from (values($1),($2)) f' using 40,50; 3156end; 3157$$ language plpgsql; 3158 3159select * from return_dquery(); 3160 3161drop function return_dquery(); 3162 3163-- test RETURN QUERY with dropped columns 3164 3165create table tabwithcols(a int, b int, c int, d int); 3166insert into tabwithcols values(10,20,30,40),(50,60,70,80); 3167 3168create or replace function returnqueryf() 3169returns setof tabwithcols as $$ 3170begin 3171 return query select * from tabwithcols; 3172 return query execute 'select * from tabwithcols'; 3173end; 3174$$ language plpgsql; 3175 3176select * from returnqueryf(); 3177 3178alter table tabwithcols drop column b; 3179 3180select * from returnqueryf(); 3181 3182alter table tabwithcols drop column d; 3183 3184select * from returnqueryf(); 3185 3186alter table tabwithcols add column d int; 3187 3188select * from returnqueryf(); 3189 3190drop function returnqueryf(); 3191drop table tabwithcols; 3192 3193-- 3194-- Tests for composite-type results 3195-- 3196 3197create type compostype as (x int, y varchar); 3198 3199-- test: use of variable of composite type in return statement 3200create or replace function compos() returns compostype as $$ 3201declare 3202 v compostype; 3203begin 3204 v := (1, 'hello'); 3205 return v; 3206end; 3207$$ language plpgsql; 3208 3209select compos(); 3210 3211-- test: use of variable of record type in return statement 3212create or replace function compos() returns compostype as $$ 3213declare 3214 v record; 3215begin 3216 v := (1, 'hello'::varchar); 3217 return v; 3218end; 3219$$ language plpgsql; 3220 3221select compos(); 3222 3223-- test: use of row expr in return statement 3224create or replace function compos() returns compostype as $$ 3225begin 3226 return (1, 'hello'::varchar); 3227end; 3228$$ language plpgsql; 3229 3230select compos(); 3231 3232-- this does not work currently (no implicit casting) 3233create or replace function compos() returns compostype as $$ 3234begin 3235 return (1, 'hello'); 3236end; 3237$$ language plpgsql; 3238 3239select compos(); 3240 3241-- ... but this does 3242create or replace function compos() returns compostype as $$ 3243begin 3244 return (1, 'hello')::compostype; 3245end; 3246$$ language plpgsql; 3247 3248select compos(); 3249 3250drop function compos(); 3251 3252-- test: return a row expr as record. 3253create or replace function composrec() returns record as $$ 3254declare 3255 v record; 3256begin 3257 v := (1, 'hello'); 3258 return v; 3259end; 3260$$ language plpgsql; 3261 3262select composrec(); 3263 3264-- test: return row expr in return statement. 3265create or replace function composrec() returns record as $$ 3266begin 3267 return (1, 'hello'); 3268end; 3269$$ language plpgsql; 3270 3271select composrec(); 3272 3273drop function composrec(); 3274 3275-- test: row expr in RETURN NEXT statement. 3276create or replace function compos() returns setof compostype as $$ 3277begin 3278 for i in 1..3 3279 loop 3280 return next (1, 'hello'::varchar); 3281 end loop; 3282 return next null::compostype; 3283 return next (2, 'goodbye')::compostype; 3284end; 3285$$ language plpgsql; 3286 3287select * from compos(); 3288 3289drop function compos(); 3290 3291-- test: use invalid expr in return statement. 3292create or replace function compos() returns compostype as $$ 3293begin 3294 return 1 + 1; 3295end; 3296$$ language plpgsql; 3297 3298select compos(); 3299 3300-- RETURN variable is a different code path ... 3301create or replace function compos() returns compostype as $$ 3302declare x int := 42; 3303begin 3304 return x; 3305end; 3306$$ language plpgsql; 3307 3308select * from compos(); 3309 3310drop function compos(); 3311 3312-- test: invalid use of composite variable in scalar-returning function 3313create or replace function compos() returns int as $$ 3314declare 3315 v compostype; 3316begin 3317 v := (1, 'hello'); 3318 return v; 3319end; 3320$$ language plpgsql; 3321 3322select compos(); 3323 3324-- test: invalid use of composite expression in scalar-returning function 3325create or replace function compos() returns int as $$ 3326begin 3327 return (1, 'hello')::compostype; 3328end; 3329$$ language plpgsql; 3330 3331select compos(); 3332 3333drop function compos(); 3334drop type compostype; 3335 3336-- 3337-- Tests for 8.4's new RAISE features 3338-- 3339 3340create or replace function raise_test() returns void as $$ 3341begin 3342 raise notice '% % %', 1, 2, 3 3343 using errcode = '55001', detail = 'some detail info', hint = 'some hint'; 3344 raise '% % %', 1, 2, 3 3345 using errcode = 'division_by_zero', detail = 'some detail info'; 3346end; 3347$$ language plpgsql; 3348 3349select raise_test(); 3350 3351-- Since we can't actually see the thrown SQLSTATE in default psql output, 3352-- test it like this; this also tests re-RAISE 3353 3354create or replace function raise_test() returns void as $$ 3355begin 3356 raise 'check me' 3357 using errcode = 'division_by_zero', detail = 'some detail info'; 3358 exception 3359 when others then 3360 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; 3361 raise; 3362end; 3363$$ language plpgsql; 3364 3365select raise_test(); 3366 3367create or replace function raise_test() returns void as $$ 3368begin 3369 raise 'check me' 3370 using errcode = '1234F', detail = 'some detail info'; 3371 exception 3372 when others then 3373 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; 3374 raise; 3375end; 3376$$ language plpgsql; 3377 3378select raise_test(); 3379 3380-- SQLSTATE specification in WHEN 3381create or replace function raise_test() returns void as $$ 3382begin 3383 raise 'check me' 3384 using errcode = '1234F', detail = 'some detail info'; 3385 exception 3386 when sqlstate '1234F' then 3387 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; 3388 raise; 3389end; 3390$$ language plpgsql; 3391 3392select raise_test(); 3393 3394create or replace function raise_test() returns void as $$ 3395begin 3396 raise division_by_zero using detail = 'some detail info'; 3397 exception 3398 when others then 3399 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; 3400 raise; 3401end; 3402$$ language plpgsql; 3403 3404select raise_test(); 3405 3406create or replace function raise_test() returns void as $$ 3407begin 3408 raise division_by_zero; 3409end; 3410$$ language plpgsql; 3411 3412select raise_test(); 3413 3414create or replace function raise_test() returns void as $$ 3415begin 3416 raise sqlstate '1234F'; 3417end; 3418$$ language plpgsql; 3419 3420select raise_test(); 3421 3422create or replace function raise_test() returns void as $$ 3423begin 3424 raise division_by_zero using message = 'custom' || ' message'; 3425end; 3426$$ language plpgsql; 3427 3428select raise_test(); 3429 3430create or replace function raise_test() returns void as $$ 3431begin 3432 raise using message = 'custom' || ' message', errcode = '22012'; 3433end; 3434$$ language plpgsql; 3435 3436select raise_test(); 3437 3438-- conflict on message 3439create or replace function raise_test() returns void as $$ 3440begin 3441 raise notice 'some message' using message = 'custom' || ' message', errcode = '22012'; 3442end; 3443$$ language plpgsql; 3444 3445select raise_test(); 3446 3447-- conflict on errcode 3448create or replace function raise_test() returns void as $$ 3449begin 3450 raise division_by_zero using message = 'custom' || ' message', errcode = '22012'; 3451end; 3452$$ language plpgsql; 3453 3454select raise_test(); 3455 3456-- nothing to re-RAISE 3457create or replace function raise_test() returns void as $$ 3458begin 3459 raise; 3460end; 3461$$ language plpgsql; 3462 3463select raise_test(); 3464 3465-- test access to exception data 3466create function zero_divide() returns int as $$ 3467declare v int := 0; 3468begin 3469 return 10 / v; 3470end; 3471$$ language plpgsql; 3472 3473create or replace function raise_test() returns void as $$ 3474begin 3475 raise exception 'custom exception' 3476 using detail = 'some detail of custom exception', 3477 hint = 'some hint related to custom exception'; 3478end; 3479$$ language plpgsql; 3480 3481create function stacked_diagnostics_test() returns void as $$ 3482declare _sqlstate text; 3483 _message text; 3484 _context text; 3485begin 3486 perform zero_divide(); 3487exception when others then 3488 get stacked diagnostics 3489 _sqlstate = returned_sqlstate, 3490 _message = message_text, 3491 _context = pg_exception_context; 3492 raise notice 'sqlstate: %, message: %, context: [%]', 3493 _sqlstate, _message, replace(_context, E'\n', ' <- '); 3494end; 3495$$ language plpgsql; 3496 3497select stacked_diagnostics_test(); 3498 3499create or replace function stacked_diagnostics_test() returns void as $$ 3500declare _detail text; 3501 _hint text; 3502 _message text; 3503begin 3504 perform raise_test(); 3505exception when others then 3506 get stacked diagnostics 3507 _message = message_text, 3508 _detail = pg_exception_detail, 3509 _hint = pg_exception_hint; 3510 raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint; 3511end; 3512$$ language plpgsql; 3513 3514select stacked_diagnostics_test(); 3515 3516-- fail, cannot use stacked diagnostics statement outside handler 3517create or replace function stacked_diagnostics_test() returns void as $$ 3518declare _detail text; 3519 _hint text; 3520 _message text; 3521begin 3522 get stacked diagnostics 3523 _message = message_text, 3524 _detail = pg_exception_detail, 3525 _hint = pg_exception_hint; 3526 raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint; 3527end; 3528$$ language plpgsql; 3529 3530select stacked_diagnostics_test(); 3531 3532drop function zero_divide(); 3533drop function stacked_diagnostics_test(); 3534 3535-- check cases where implicit SQLSTATE variable could be confused with 3536-- SQLSTATE as a keyword, cf bug #5524 3537create or replace function raise_test() returns void as $$ 3538begin 3539 perform 1/0; 3540exception 3541 when sqlstate '22012' then 3542 raise notice using message = sqlstate; 3543 raise sqlstate '22012' using message = 'substitute message'; 3544end; 3545$$ language plpgsql; 3546 3547select raise_test(); 3548 3549drop function raise_test(); 3550 3551-- test passing column_name, constraint_name, datatype_name, table_name 3552-- and schema_name error fields 3553 3554create or replace function stacked_diagnostics_test() returns void as $$ 3555declare _column_name text; 3556 _constraint_name text; 3557 _datatype_name text; 3558 _table_name text; 3559 _schema_name text; 3560begin 3561 raise exception using 3562 column = '>>some column name<<', 3563 constraint = '>>some constraint name<<', 3564 datatype = '>>some datatype name<<', 3565 table = '>>some table name<<', 3566 schema = '>>some schema name<<'; 3567exception when others then 3568 get stacked diagnostics 3569 _column_name = column_name, 3570 _constraint_name = constraint_name, 3571 _datatype_name = pg_datatype_name, 3572 _table_name = table_name, 3573 _schema_name = schema_name; 3574 raise notice 'column %, constraint %, type %, table %, schema %', 3575 _column_name, _constraint_name, _datatype_name, _table_name, _schema_name; 3576end; 3577$$ language plpgsql; 3578 3579select stacked_diagnostics_test(); 3580 3581drop function stacked_diagnostics_test(); 3582 3583-- test CASE statement 3584 3585create or replace function case_test(bigint) returns text as $$ 3586declare a int = 10; 3587 b int = 1; 3588begin 3589 case $1 3590 when 1 then 3591 return 'one'; 3592 when 2 then 3593 return 'two'; 3594 when 3,4,3+5 then 3595 return 'three, four or eight'; 3596 when a then 3597 return 'ten'; 3598 when a+b, a+b+1 then 3599 return 'eleven, twelve'; 3600 end case; 3601end; 3602$$ language plpgsql immutable; 3603 3604select case_test(1); 3605select case_test(2); 3606select case_test(3); 3607select case_test(4); 3608select case_test(5); -- fails 3609select case_test(8); 3610select case_test(10); 3611select case_test(11); 3612select case_test(12); 3613select case_test(13); -- fails 3614 3615create or replace function catch() returns void as $$ 3616begin 3617 raise notice '%', case_test(6); 3618exception 3619 when case_not_found then 3620 raise notice 'caught case_not_found % %', SQLSTATE, SQLERRM; 3621end 3622$$ language plpgsql; 3623 3624select catch(); 3625 3626-- test the searched variant too, as well as ELSE 3627create or replace function case_test(bigint) returns text as $$ 3628declare a int = 10; 3629begin 3630 case 3631 when $1 = 1 then 3632 return 'one'; 3633 when $1 = a + 2 then 3634 return 'twelve'; 3635 else 3636 return 'other'; 3637 end case; 3638end; 3639$$ language plpgsql immutable; 3640 3641select case_test(1); 3642select case_test(2); 3643select case_test(12); 3644select case_test(13); 3645 3646drop function catch(); 3647drop function case_test(bigint); 3648 3649-- test variadic functions 3650 3651create or replace function vari(variadic int[]) 3652returns void as $$ 3653begin 3654 for i in array_lower($1,1)..array_upper($1,1) loop 3655 raise notice '%', $1[i]; 3656 end loop; end; 3657$$ language plpgsql; 3658 3659select vari(1,2,3,4,5); 3660select vari(3,4,5); 3661select vari(variadic array[5,6,7]); 3662 3663drop function vari(int[]); 3664 3665-- coercion test 3666create or replace function pleast(variadic numeric[]) 3667returns numeric as $$ 3668declare aux numeric = $1[array_lower($1,1)]; 3669begin 3670 for i in array_lower($1,1)+1..array_upper($1,1) loop 3671 if $1[i] < aux then aux := $1[i]; end if; 3672 end loop; 3673 return aux; 3674end; 3675$$ language plpgsql immutable strict; 3676 3677select pleast(10,1,2,3,-16); 3678select pleast(10.2,2.2,-1.1); 3679select pleast(10.2,10, -20); 3680select pleast(10,20, -1.0); 3681 3682-- in case of conflict, non-variadic version is preferred 3683create or replace function pleast(numeric) 3684returns numeric as $$ 3685begin 3686 raise notice 'non-variadic function called'; 3687 return $1; 3688end; 3689$$ language plpgsql immutable strict; 3690 3691select pleast(10); 3692 3693drop function pleast(numeric[]); 3694drop function pleast(numeric); 3695 3696-- test table functions 3697 3698create function tftest(int) returns table(a int, b int) as $$ 3699begin 3700 return query select $1, $1+i from generate_series(1,5) g(i); 3701end; 3702$$ language plpgsql immutable strict; 3703 3704select * from tftest(10); 3705 3706create or replace function tftest(a1 int) returns table(a int, b int) as $$ 3707begin 3708 a := a1; b := a1 + 1; 3709 return next; 3710 a := a1 * 10; b := a1 * 10 + 1; 3711 return next; 3712end; 3713$$ language plpgsql immutable strict; 3714 3715select * from tftest(10); 3716 3717drop function tftest(int); 3718 3719create or replace function rttest() 3720returns setof int as $$ 3721declare rc int; 3722 rca int[]; 3723begin 3724 return query values(10),(20); 3725 get diagnostics rc = row_count; 3726 raise notice '% %', found, rc; 3727 return query select * from (values(10),(20)) f(a) where false; 3728 get diagnostics rc = row_count; 3729 raise notice '% %', found, rc; 3730 return query execute 'values(10),(20)'; 3731 -- just for fun, let's use array elements as targets 3732 get diagnostics rca[1] = row_count; 3733 raise notice '% %', found, rca[1]; 3734 return query execute 'select * from (values(10),(20)) f(a) where false'; 3735 get diagnostics rca[2] = row_count; 3736 raise notice '% %', found, rca[2]; 3737end; 3738$$ language plpgsql; 3739 3740select * from rttest(); 3741 3742drop function rttest(); 3743 3744-- Test for proper cleanup at subtransaction exit. This example 3745-- exposed a bug in PG 8.2. 3746 3747CREATE FUNCTION leaker_1(fail BOOL) RETURNS INTEGER AS $$ 3748DECLARE 3749 v_var INTEGER; 3750BEGIN 3751 BEGIN 3752 v_var := (leaker_2(fail)).error_code; 3753 EXCEPTION 3754 WHEN others THEN RETURN 0; 3755 END; 3756 RETURN 1; 3757END; 3758$$ LANGUAGE plpgsql; 3759 3760CREATE FUNCTION leaker_2(fail BOOL, OUT error_code INTEGER, OUT new_id INTEGER) 3761 RETURNS RECORD AS $$ 3762BEGIN 3763 IF fail THEN 3764 RAISE EXCEPTION 'fail ...'; 3765 END IF; 3766 error_code := 1; 3767 new_id := 1; 3768 RETURN; 3769END; 3770$$ LANGUAGE plpgsql; 3771 3772SELECT * FROM leaker_1(false); 3773SELECT * FROM leaker_1(true); 3774 3775DROP FUNCTION leaker_1(bool); 3776DROP FUNCTION leaker_2(bool); 3777 3778-- Test for appropriate cleanup of non-simple expression evaluations 3779-- (bug in all versions prior to August 2010) 3780 3781CREATE FUNCTION nonsimple_expr_test() RETURNS text[] AS $$ 3782DECLARE 3783 arr text[]; 3784 lr text; 3785 i integer; 3786BEGIN 3787 arr := array[array['foo','bar'], array['baz', 'quux']]; 3788 lr := 'fool'; 3789 i := 1; 3790 -- use sub-SELECTs to make expressions non-simple 3791 arr[(SELECT i)][(SELECT i+1)] := (SELECT lr); 3792 RETURN arr; 3793END; 3794$$ LANGUAGE plpgsql; 3795 3796SELECT nonsimple_expr_test(); 3797 3798DROP FUNCTION nonsimple_expr_test(); 3799 3800CREATE FUNCTION nonsimple_expr_test() RETURNS integer AS $$ 3801declare 3802 i integer NOT NULL := 0; 3803begin 3804 begin 3805 i := (SELECT NULL::integer); -- should throw error 3806 exception 3807 WHEN OTHERS THEN 3808 i := (SELECT 1::integer); 3809 end; 3810 return i; 3811end; 3812$$ LANGUAGE plpgsql; 3813 3814SELECT nonsimple_expr_test(); 3815 3816DROP FUNCTION nonsimple_expr_test(); 3817 3818-- 3819-- Test cases involving recursion and error recovery in simple expressions 3820-- (bugs in all versions before October 2010). The problems are most 3821-- easily exposed by mutual recursion between plpgsql and sql functions. 3822-- 3823 3824create function recurse(float8) returns float8 as 3825$$ 3826begin 3827 if ($1 > 0) then 3828 return sql_recurse($1 - 1); 3829 else 3830 return $1; 3831 end if; 3832end; 3833$$ language plpgsql; 3834 3835-- "limit" is to prevent this from being inlined 3836create function sql_recurse(float8) returns float8 as 3837$$ select recurse($1) limit 1; $$ language sql; 3838 3839select recurse(10); 3840 3841create function error1(text) returns text language sql as 3842$$ SELECT relname::text FROM pg_class c WHERE c.oid = $1::regclass $$; 3843 3844create function error2(p_name_table text) returns text language plpgsql as $$ 3845begin 3846 return error1(p_name_table); 3847end$$; 3848 3849BEGIN; 3850create table public.stuffs (stuff text); 3851SAVEPOINT a; 3852select error2('nonexistent.stuffs'); 3853ROLLBACK TO a; 3854select error2('public.stuffs'); 3855rollback; 3856 3857drop function error2(p_name_table text); 3858drop function error1(text); 3859 3860-- Test for proper handling of cast-expression caching 3861 3862create function sql_to_date(integer) returns date as $$ 3863select $1::text::date 3864$$ language sql immutable strict; 3865 3866create cast (integer as date) with function sql_to_date(integer) as assignment; 3867 3868create function cast_invoker(integer) returns date as $$ 3869begin 3870 return $1; 3871end$$ language plpgsql; 3872 3873select cast_invoker(20150717); 3874select cast_invoker(20150718); -- second call crashed in pre-release 9.5 3875 3876begin; 3877select cast_invoker(20150717); 3878select cast_invoker(20150718); 3879savepoint s1; 3880select cast_invoker(20150718); 3881select cast_invoker(-1); -- fails 3882rollback to savepoint s1; 3883select cast_invoker(20150719); 3884select cast_invoker(20150720); 3885commit; 3886 3887drop function cast_invoker(integer); 3888drop function sql_to_date(integer) cascade; 3889 3890-- Test handling of cast cache inside DO blocks 3891-- (to check the original crash case, this must be a cast not previously 3892-- used in this session) 3893 3894begin; 3895do $$ declare x text[]; begin x := '{1.23, 4.56}'::numeric[]; end $$; 3896do $$ declare x text[]; begin x := '{1.23, 4.56}'::numeric[]; end $$; 3897end; 3898 3899-- Test for consistent reporting of error context 3900 3901create function fail() returns int language plpgsql as $$ 3902begin 3903 return 1/0; 3904end 3905$$; 3906 3907select fail(); 3908select fail(); 3909 3910drop function fail(); 3911 3912-- Test handling of string literals. 3913 3914set standard_conforming_strings = off; 3915 3916create or replace function strtest() returns text as $$ 3917begin 3918 raise notice 'foo\\bar\041baz'; 3919 return 'foo\\bar\041baz'; 3920end 3921$$ language plpgsql; 3922 3923select strtest(); 3924 3925create or replace function strtest() returns text as $$ 3926begin 3927 raise notice E'foo\\bar\041baz'; 3928 return E'foo\\bar\041baz'; 3929end 3930$$ language plpgsql; 3931 3932select strtest(); 3933 3934set standard_conforming_strings = on; 3935 3936create or replace function strtest() returns text as $$ 3937begin 3938 raise notice 'foo\\bar\041baz\'; 3939 return 'foo\\bar\041baz\'; 3940end 3941$$ language plpgsql; 3942 3943select strtest(); 3944 3945create or replace function strtest() returns text as $$ 3946begin 3947 raise notice E'foo\\bar\041baz'; 3948 return E'foo\\bar\041baz'; 3949end 3950$$ language plpgsql; 3951 3952select strtest(); 3953 3954drop function strtest(); 3955 3956-- Test anonymous code blocks. 3957 3958DO $$ 3959DECLARE r record; 3960BEGIN 3961 FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno 3962 LOOP 3963 RAISE NOTICE '%, %', r.roomno, r.comment; 3964 END LOOP; 3965END$$; 3966 3967-- these are to check syntax error reporting 3968DO LANGUAGE plpgsql $$begin return 1; end$$; 3969 3970DO $$ 3971DECLARE r record; 3972BEGIN 3973 FOR r IN SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno 3974 LOOP 3975 RAISE NOTICE '%, %', r.roomno, r.comment; 3976 END LOOP; 3977END$$; 3978 3979-- Check handling of errors thrown from/into anonymous code blocks. 3980do $outer$ 3981begin 3982 for i in 1..10 loop 3983 begin 3984 execute $ex$ 3985 do $$ 3986 declare x int = 0; 3987 begin 3988 x := 1 / x; 3989 end; 3990 $$; 3991 $ex$; 3992 exception when division_by_zero then 3993 raise notice 'caught division by zero'; 3994 end; 3995 end loop; 3996end; 3997$outer$; 3998 3999-- Check variable scoping -- a var is not available in its own or prior 4000-- default expressions. 4001 4002create function scope_test() returns int as $$ 4003declare x int := 42; 4004begin 4005 declare y int := x + 1; 4006 x int := x + 2; 4007 begin 4008 return x * 100 + y; 4009 end; 4010end; 4011$$ language plpgsql; 4012 4013select scope_test(); 4014 4015drop function scope_test(); 4016 4017-- Check handling of conflicts between plpgsql vars and table columns. 4018 4019set plpgsql.variable_conflict = error; 4020 4021create function conflict_test() returns setof int8_tbl as $$ 4022declare r record; 4023 q1 bigint := 42; 4024begin 4025 for r in select q1,q2 from int8_tbl loop 4026 return next r; 4027 end loop; 4028end; 4029$$ language plpgsql; 4030 4031select * from conflict_test(); 4032 4033create or replace function conflict_test() returns setof int8_tbl as $$ 4034#variable_conflict use_variable 4035declare r record; 4036 q1 bigint := 42; 4037begin 4038 for r in select q1,q2 from int8_tbl loop 4039 return next r; 4040 end loop; 4041end; 4042$$ language plpgsql; 4043 4044select * from conflict_test(); 4045 4046create or replace function conflict_test() returns setof int8_tbl as $$ 4047#variable_conflict use_column 4048declare r record; 4049 q1 bigint := 42; 4050begin 4051 for r in select q1,q2 from int8_tbl loop 4052 return next r; 4053 end loop; 4054end; 4055$$ language plpgsql; 4056 4057select * from conflict_test(); 4058 4059drop function conflict_test(); 4060 4061-- Check that an unreserved keyword can be used as a variable name 4062 4063create function unreserved_test() returns int as $$ 4064declare 4065 forward int := 21; 4066begin 4067 forward := forward * 2; 4068 return forward; 4069end 4070$$ language plpgsql; 4071 4072select unreserved_test(); 4073 4074create or replace function unreserved_test() returns int as $$ 4075declare 4076 return int := 42; 4077begin 4078 return := return + 1; 4079 return return; 4080end 4081$$ language plpgsql; 4082 4083select unreserved_test(); 4084 4085drop function unreserved_test(); 4086 4087-- 4088-- Test FOREACH over arrays 4089-- 4090 4091create function foreach_test(anyarray) 4092returns void as $$ 4093declare x int; 4094begin 4095 foreach x in array $1 4096 loop 4097 raise notice '%', x; 4098 end loop; 4099 end; 4100$$ language plpgsql; 4101 4102select foreach_test(ARRAY[1,2,3,4]); 4103select foreach_test(ARRAY[[1,2],[3,4]]); 4104 4105create or replace function foreach_test(anyarray) 4106returns void as $$ 4107declare x int; 4108begin 4109 foreach x slice 1 in array $1 4110 loop 4111 raise notice '%', x; 4112 end loop; 4113 end; 4114$$ language plpgsql; 4115 4116-- should fail 4117select foreach_test(ARRAY[1,2,3,4]); 4118select foreach_test(ARRAY[[1,2],[3,4]]); 4119 4120create or replace function foreach_test(anyarray) 4121returns void as $$ 4122declare x int[]; 4123begin 4124 foreach x slice 1 in array $1 4125 loop 4126 raise notice '%', x; 4127 end loop; 4128 end; 4129$$ language plpgsql; 4130 4131select foreach_test(ARRAY[1,2,3,4]); 4132select foreach_test(ARRAY[[1,2],[3,4]]); 4133 4134-- higher level of slicing 4135create or replace function foreach_test(anyarray) 4136returns void as $$ 4137declare x int[]; 4138begin 4139 foreach x slice 2 in array $1 4140 loop 4141 raise notice '%', x; 4142 end loop; 4143 end; 4144$$ language plpgsql; 4145 4146-- should fail 4147select foreach_test(ARRAY[1,2,3,4]); 4148-- ok 4149select foreach_test(ARRAY[[1,2],[3,4]]); 4150select foreach_test(ARRAY[[[1,2]],[[3,4]]]); 4151 4152create type xy_tuple AS (x int, y int); 4153 4154-- iteration over array of records 4155create or replace function foreach_test(anyarray) 4156returns void as $$ 4157declare r record; 4158begin 4159 foreach r in array $1 4160 loop 4161 raise notice '%', r; 4162 end loop; 4163 end; 4164$$ language plpgsql; 4165 4166select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]); 4167select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]); 4168 4169create or replace function foreach_test(anyarray) 4170returns void as $$ 4171declare x int; y int; 4172begin 4173 foreach x, y in array $1 4174 loop 4175 raise notice 'x = %, y = %', x, y; 4176 end loop; 4177 end; 4178$$ language plpgsql; 4179 4180select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]); 4181select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]); 4182 4183-- slicing over array of composite types 4184create or replace function foreach_test(anyarray) 4185returns void as $$ 4186declare x xy_tuple[]; 4187begin 4188 foreach x slice 1 in array $1 4189 loop 4190 raise notice '%', x; 4191 end loop; 4192 end; 4193$$ language plpgsql; 4194 4195select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]); 4196select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]); 4197 4198drop function foreach_test(anyarray); 4199drop type xy_tuple; 4200 4201-- 4202-- Assorted tests for array subscript assignment 4203-- 4204 4205create temp table rtype (id int, ar text[]); 4206 4207create function arrayassign1() returns text[] language plpgsql as $$ 4208declare 4209 r record; 4210begin 4211 r := row(12, '{foo,bar,baz}')::rtype; 4212 r.ar[2] := 'replace'; 4213 return r.ar; 4214end$$; 4215 4216select arrayassign1(); 4217select arrayassign1(); -- try again to exercise internal caching 4218 4219create domain orderedarray as int[2] 4220 constraint sorted check (value[1] < value[2]); 4221 4222select '{1,2}'::orderedarray; 4223select '{2,1}'::orderedarray; -- fail 4224 4225create function testoa(x1 int, x2 int, x3 int) returns orderedarray 4226language plpgsql as $$ 4227declare res orderedarray; 4228begin 4229 res := array[x1, x2]; 4230 res[2] := x3; 4231 return res; 4232end$$; 4233 4234select testoa(1,2,3); 4235select testoa(1,2,3); -- try again to exercise internal caching 4236select testoa(2,1,3); -- fail at initial assign 4237select testoa(1,2,1); -- fail at update 4238 4239drop function arrayassign1(); 4240drop function testoa(x1 int, x2 int, x3 int); 4241 4242 4243-- 4244-- Test handling of expanded arrays 4245-- 4246 4247create function returns_rw_array(int) returns int[] 4248language plpgsql as $$ 4249 declare r int[]; 4250 begin r := array[$1, $1]; return r; end; 4251$$ stable; 4252 4253create function consumes_rw_array(int[]) returns int 4254language plpgsql as $$ 4255 begin return $1[1]; end; 4256$$ stable; 4257 4258-- bug #14174 4259explain (verbose, costs off) 4260select i, a from 4261 (select returns_rw_array(1) as a offset 0) ss, 4262 lateral consumes_rw_array(a) i; 4263 4264select i, a from 4265 (select returns_rw_array(1) as a offset 0) ss, 4266 lateral consumes_rw_array(a) i; 4267 4268explain (verbose, costs off) 4269select consumes_rw_array(a), a from returns_rw_array(1) a; 4270 4271select consumes_rw_array(a), a from returns_rw_array(1) a; 4272 4273explain (verbose, costs off) 4274select consumes_rw_array(a), a from 4275 (values (returns_rw_array(1)), (returns_rw_array(2))) v(a); 4276 4277select consumes_rw_array(a), a from 4278 (values (returns_rw_array(1)), (returns_rw_array(2))) v(a); 4279 4280 4281-- 4282-- Test access to call stack 4283-- 4284 4285create function inner_func(int) 4286returns int as $$ 4287declare _context text; 4288begin 4289 get diagnostics _context = pg_context; 4290 raise notice '***%***', _context; 4291 -- lets do it again, just for fun.. 4292 get diagnostics _context = pg_context; 4293 raise notice '***%***', _context; 4294 raise notice 'lets make sure we didnt break anything'; 4295 return 2 * $1; 4296end; 4297$$ language plpgsql; 4298 4299create or replace function outer_func(int) 4300returns int as $$ 4301declare 4302 myresult int; 4303begin 4304 raise notice 'calling down into inner_func()'; 4305 myresult := inner_func($1); 4306 raise notice 'inner_func() done'; 4307 return myresult; 4308end; 4309$$ language plpgsql; 4310 4311create or replace function outer_outer_func(int) 4312returns int as $$ 4313declare 4314 myresult int; 4315begin 4316 raise notice 'calling down into outer_func()'; 4317 myresult := outer_func($1); 4318 raise notice 'outer_func() done'; 4319 return myresult; 4320end; 4321$$ language plpgsql; 4322 4323select outer_outer_func(10); 4324-- repeated call should to work 4325select outer_outer_func(20); 4326 4327drop function outer_outer_func(int); 4328drop function outer_func(int); 4329drop function inner_func(int); 4330 4331-- access to call stack from exception 4332create function inner_func(int) 4333returns int as $$ 4334declare 4335 _context text; 4336 sx int := 5; 4337begin 4338 begin 4339 perform sx / 0; 4340 exception 4341 when division_by_zero then 4342 get diagnostics _context = pg_context; 4343 raise notice '***%***', _context; 4344 end; 4345 4346 -- lets do it again, just for fun.. 4347 get diagnostics _context = pg_context; 4348 raise notice '***%***', _context; 4349 raise notice 'lets make sure we didnt break anything'; 4350 return 2 * $1; 4351end; 4352$$ language plpgsql; 4353 4354create or replace function outer_func(int) 4355returns int as $$ 4356declare 4357 myresult int; 4358begin 4359 raise notice 'calling down into inner_func()'; 4360 myresult := inner_func($1); 4361 raise notice 'inner_func() done'; 4362 return myresult; 4363end; 4364$$ language plpgsql; 4365 4366create or replace function outer_outer_func(int) 4367returns int as $$ 4368declare 4369 myresult int; 4370begin 4371 raise notice 'calling down into outer_func()'; 4372 myresult := outer_func($1); 4373 raise notice 'outer_func() done'; 4374 return myresult; 4375end; 4376$$ language plpgsql; 4377 4378select outer_outer_func(10); 4379-- repeated call should to work 4380select outer_outer_func(20); 4381 4382drop function outer_outer_func(int); 4383drop function outer_func(int); 4384drop function inner_func(int); 4385 4386-- 4387-- Test ASSERT 4388-- 4389 4390do $$ 4391begin 4392 assert 1=1; -- should succeed 4393end; 4394$$; 4395 4396do $$ 4397begin 4398 assert 1=0; -- should fail 4399end; 4400$$; 4401 4402do $$ 4403begin 4404 assert NULL; -- should fail 4405end; 4406$$; 4407 4408-- check controlling GUC 4409set plpgsql.check_asserts = off; 4410do $$ 4411begin 4412 assert 1=0; -- won't be tested 4413end; 4414$$; 4415reset plpgsql.check_asserts; 4416 4417-- test custom message 4418do $$ 4419declare var text := 'some value'; 4420begin 4421 assert 1=0, format('assertion failed, var = "%s"', var); 4422end; 4423$$; 4424 4425-- ensure assertions are not trapped by 'others' 4426do $$ 4427begin 4428 assert 1=0, 'unhandled assertion'; 4429exception when others then 4430 null; -- do nothing 4431end; 4432$$; 4433 4434-- Test use of plpgsql in a domain check constraint (cf. bug #14414) 4435 4436create function plpgsql_domain_check(val int) returns boolean as $$ 4437begin return val > 0; end 4438$$ language plpgsql immutable; 4439 4440create domain plpgsql_domain as integer check(plpgsql_domain_check(value)); 4441 4442do $$ 4443declare v_test plpgsql_domain; 4444begin 4445 v_test := 1; 4446end; 4447$$; 4448 4449do $$ 4450declare v_test plpgsql_domain := 1; 4451begin 4452 v_test := 0; -- fail 4453end; 4454$$; 4455 4456-- Test handling of expanded array passed to a domain constraint (bug #14472) 4457 4458create function plpgsql_arr_domain_check(val int[]) returns boolean as $$ 4459begin return val[1] > 0; end 4460$$ language plpgsql immutable; 4461 4462create domain plpgsql_arr_domain as int[] check(plpgsql_arr_domain_check(value)); 4463 4464do $$ 4465declare v_test plpgsql_arr_domain; 4466begin 4467 v_test := array[1]; 4468 v_test := v_test || 2; 4469end; 4470$$; 4471 4472do $$ 4473declare v_test plpgsql_arr_domain := array[1]; 4474begin 4475 v_test := 0 || v_test; -- fail 4476end; 4477$$; 4478 4479-- 4480-- test usage of transition tables in AFTER triggers 4481-- 4482 4483CREATE TABLE transition_table_base (id int PRIMARY KEY, val text); 4484 4485CREATE FUNCTION transition_table_base_ins_func() 4486 RETURNS trigger 4487 LANGUAGE plpgsql 4488AS $$ 4489DECLARE 4490 t text; 4491 l text; 4492BEGIN 4493 t = ''; 4494 FOR l IN EXECUTE 4495 $q$ 4496 EXPLAIN (TIMING off, COSTS off, VERBOSE on) 4497 SELECT * FROM newtable 4498 $q$ LOOP 4499 t = t || l || E'\n'; 4500 END LOOP; 4501 4502 RAISE INFO '%', t; 4503 RETURN new; 4504END; 4505$$; 4506 4507CREATE TRIGGER transition_table_base_ins_trig 4508 AFTER INSERT ON transition_table_base 4509 REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable 4510 FOR EACH STATEMENT 4511 EXECUTE PROCEDURE transition_table_base_ins_func(); 4512 4513CREATE TRIGGER transition_table_base_ins_trig 4514 AFTER INSERT ON transition_table_base 4515 REFERENCING NEW TABLE AS newtable 4516 FOR EACH STATEMENT 4517 EXECUTE PROCEDURE transition_table_base_ins_func(); 4518 4519INSERT INTO transition_table_base VALUES (1, 'One'), (2, 'Two'); 4520INSERT INTO transition_table_base VALUES (3, 'Three'), (4, 'Four'); 4521 4522CREATE OR REPLACE FUNCTION transition_table_base_upd_func() 4523 RETURNS trigger 4524 LANGUAGE plpgsql 4525AS $$ 4526DECLARE 4527 t text; 4528 l text; 4529BEGIN 4530 t = ''; 4531 FOR l IN EXECUTE 4532 $q$ 4533 EXPLAIN (TIMING off, COSTS off, VERBOSE on) 4534 SELECT * FROM oldtable ot FULL JOIN newtable nt USING (id) 4535 $q$ LOOP 4536 t = t || l || E'\n'; 4537 END LOOP; 4538 4539 RAISE INFO '%', t; 4540 RETURN new; 4541END; 4542$$; 4543 4544CREATE TRIGGER transition_table_base_upd_trig 4545 AFTER UPDATE ON transition_table_base 4546 REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable 4547 FOR EACH STATEMENT 4548 EXECUTE PROCEDURE transition_table_base_upd_func(); 4549 4550UPDATE transition_table_base 4551 SET val = '*' || val || '*' 4552 WHERE id BETWEEN 2 AND 3; 4553 4554CREATE TABLE transition_table_level1 4555( 4556 level1_no serial NOT NULL , 4557 level1_node_name varchar(255), 4558 PRIMARY KEY (level1_no) 4559) WITHOUT OIDS; 4560 4561CREATE TABLE transition_table_level2 4562( 4563 level2_no serial NOT NULL , 4564 parent_no int NOT NULL, 4565 level1_node_name varchar(255), 4566 PRIMARY KEY (level2_no) 4567) WITHOUT OIDS; 4568 4569CREATE TABLE transition_table_status 4570( 4571 level int NOT NULL, 4572 node_no int NOT NULL, 4573 status int, 4574 PRIMARY KEY (level, node_no) 4575) WITHOUT OIDS; 4576 4577CREATE FUNCTION transition_table_level1_ri_parent_del_func() 4578 RETURNS TRIGGER 4579 LANGUAGE plpgsql 4580AS $$ 4581 DECLARE n bigint; 4582 BEGIN 4583 PERFORM FROM p JOIN transition_table_level2 c ON c.parent_no = p.level1_no; 4584 IF FOUND THEN 4585 RAISE EXCEPTION 'RI error'; 4586 END IF; 4587 RETURN NULL; 4588 END; 4589$$; 4590 4591CREATE TRIGGER transition_table_level1_ri_parent_del_trigger 4592 AFTER DELETE ON transition_table_level1 4593 REFERENCING OLD TABLE AS p 4594 FOR EACH STATEMENT EXECUTE PROCEDURE 4595 transition_table_level1_ri_parent_del_func(); 4596 4597CREATE FUNCTION transition_table_level1_ri_parent_upd_func() 4598 RETURNS TRIGGER 4599 LANGUAGE plpgsql 4600AS $$ 4601 DECLARE 4602 x int; 4603 BEGIN 4604 WITH p AS (SELECT level1_no, sum(delta) cnt 4605 FROM (SELECT level1_no, 1 AS delta FROM i 4606 UNION ALL 4607 SELECT level1_no, -1 AS delta FROM d) w 4608 GROUP BY level1_no 4609 HAVING sum(delta) < 0) 4610 SELECT level1_no 4611 FROM p JOIN transition_table_level2 c ON c.parent_no = p.level1_no 4612 INTO x; 4613 IF FOUND THEN 4614 RAISE EXCEPTION 'RI error'; 4615 END IF; 4616 RETURN NULL; 4617 END; 4618$$; 4619 4620CREATE TRIGGER transition_table_level1_ri_parent_upd_trigger 4621 AFTER UPDATE ON transition_table_level1 4622 REFERENCING OLD TABLE AS d NEW TABLE AS i 4623 FOR EACH STATEMENT EXECUTE PROCEDURE 4624 transition_table_level1_ri_parent_upd_func(); 4625 4626CREATE FUNCTION transition_table_level2_ri_child_insupd_func() 4627 RETURNS TRIGGER 4628 LANGUAGE plpgsql 4629AS $$ 4630 BEGIN 4631 PERFORM FROM i 4632 LEFT JOIN transition_table_level1 p 4633 ON p.level1_no IS NOT NULL AND p.level1_no = i.parent_no 4634 WHERE p.level1_no IS NULL; 4635 IF FOUND THEN 4636 RAISE EXCEPTION 'RI error'; 4637 END IF; 4638 RETURN NULL; 4639 END; 4640$$; 4641 4642CREATE TRIGGER transition_table_level2_ri_child_ins_trigger 4643 AFTER INSERT ON transition_table_level2 4644 REFERENCING NEW TABLE AS i 4645 FOR EACH STATEMENT EXECUTE PROCEDURE 4646 transition_table_level2_ri_child_insupd_func(); 4647 4648CREATE TRIGGER transition_table_level2_ri_child_upd_trigger 4649 AFTER UPDATE ON transition_table_level2 4650 REFERENCING NEW TABLE AS i 4651 FOR EACH STATEMENT EXECUTE PROCEDURE 4652 transition_table_level2_ri_child_insupd_func(); 4653 4654-- create initial test data 4655INSERT INTO transition_table_level1 (level1_no) 4656 SELECT generate_series(1,200); 4657ANALYZE transition_table_level1; 4658 4659INSERT INTO transition_table_level2 (level2_no, parent_no) 4660 SELECT level2_no, level2_no / 50 + 1 AS parent_no 4661 FROM generate_series(1,9999) level2_no; 4662ANALYZE transition_table_level2; 4663 4664INSERT INTO transition_table_status (level, node_no, status) 4665 SELECT 1, level1_no, 0 FROM transition_table_level1; 4666 4667INSERT INTO transition_table_status (level, node_no, status) 4668 SELECT 2, level2_no, 0 FROM transition_table_level2; 4669ANALYZE transition_table_status; 4670 4671INSERT INTO transition_table_level1(level1_no) 4672 SELECT generate_series(201,1000); 4673ANALYZE transition_table_level1; 4674 4675-- behave reasonably if someone tries to modify a transition table 4676CREATE FUNCTION transition_table_level2_bad_usage_func() 4677 RETURNS TRIGGER 4678 LANGUAGE plpgsql 4679AS $$ 4680 BEGIN 4681 INSERT INTO dx VALUES (1000000, 1000000, 'x'); 4682 RETURN NULL; 4683 END; 4684$$; 4685 4686CREATE TRIGGER transition_table_level2_bad_usage_trigger 4687 AFTER DELETE ON transition_table_level2 4688 REFERENCING OLD TABLE AS dx 4689 FOR EACH STATEMENT EXECUTE PROCEDURE 4690 transition_table_level2_bad_usage_func(); 4691 4692DELETE FROM transition_table_level2 4693 WHERE level2_no BETWEEN 301 AND 305; 4694 4695DROP TRIGGER transition_table_level2_bad_usage_trigger 4696 ON transition_table_level2; 4697 4698-- attempt modifications which would break RI (should all fail) 4699DELETE FROM transition_table_level1 4700 WHERE level1_no = 25; 4701 4702UPDATE transition_table_level1 SET level1_no = -1 4703 WHERE level1_no = 30; 4704 4705INSERT INTO transition_table_level2 (level2_no, parent_no) 4706 VALUES (10000, 10000); 4707 4708UPDATE transition_table_level2 SET parent_no = 2000 4709 WHERE level2_no = 40; 4710 4711 4712-- attempt modifications which would not break RI (should all succeed) 4713DELETE FROM transition_table_level1 4714 WHERE level1_no BETWEEN 201 AND 1000; 4715 4716DELETE FROM transition_table_level1 4717 WHERE level1_no BETWEEN 100000000 AND 100000010; 4718 4719SELECT count(*) FROM transition_table_level1; 4720 4721DELETE FROM transition_table_level2 4722 WHERE level2_no BETWEEN 211 AND 220; 4723 4724SELECT count(*) FROM transition_table_level2; 4725 4726CREATE TABLE alter_table_under_transition_tables 4727( 4728 id int PRIMARY KEY, 4729 name text 4730); 4731 4732CREATE FUNCTION alter_table_under_transition_tables_upd_func() 4733 RETURNS TRIGGER 4734 LANGUAGE plpgsql 4735AS $$ 4736BEGIN 4737 RAISE WARNING 'old table = %, new table = %', 4738 (SELECT string_agg(id || '=' || name, ',') FROM d), 4739 (SELECT string_agg(id || '=' || name, ',') FROM i); 4740 RAISE NOTICE 'one = %', (SELECT 1 FROM alter_table_under_transition_tables LIMIT 1); 4741 RETURN NULL; 4742END; 4743$$; 4744 4745-- should fail, TRUNCATE is not compatible with transition tables 4746CREATE TRIGGER alter_table_under_transition_tables_upd_trigger 4747 AFTER TRUNCATE OR UPDATE ON alter_table_under_transition_tables 4748 REFERENCING OLD TABLE AS d NEW TABLE AS i 4749 FOR EACH STATEMENT EXECUTE PROCEDURE 4750 alter_table_under_transition_tables_upd_func(); 4751 4752-- should work 4753CREATE TRIGGER alter_table_under_transition_tables_upd_trigger 4754 AFTER UPDATE ON alter_table_under_transition_tables 4755 REFERENCING OLD TABLE AS d NEW TABLE AS i 4756 FOR EACH STATEMENT EXECUTE PROCEDURE 4757 alter_table_under_transition_tables_upd_func(); 4758 4759INSERT INTO alter_table_under_transition_tables 4760 VALUES (1, '1'), (2, '2'), (3, '3'); 4761UPDATE alter_table_under_transition_tables 4762 SET name = name || name; 4763 4764-- now change 'name' to an integer to see what happens... 4765ALTER TABLE alter_table_under_transition_tables 4766 ALTER COLUMN name TYPE int USING name::integer; 4767UPDATE alter_table_under_transition_tables 4768 SET name = (name::text || name::text)::integer; 4769 4770-- now drop column 'name' 4771ALTER TABLE alter_table_under_transition_tables 4772 DROP column name; 4773UPDATE alter_table_under_transition_tables 4774 SET id = id; 4775 4776-- 4777-- Test multiple reference to a transition table 4778-- 4779 4780CREATE TABLE multi_test (i int); 4781INSERT INTO multi_test VALUES (1); 4782 4783CREATE OR REPLACE FUNCTION multi_test_trig() RETURNS trigger 4784LANGUAGE plpgsql AS $$ 4785BEGIN 4786 RAISE NOTICE 'count = %', (SELECT COUNT(*) FROM new_test); 4787 RAISE NOTICE 'count union = %', 4788 (SELECT COUNT(*) 4789 FROM (SELECT * FROM new_test UNION ALL SELECT * FROM new_test) ss); 4790 RETURN NULL; 4791END$$; 4792 4793CREATE TRIGGER my_trigger AFTER UPDATE ON multi_test 4794 REFERENCING NEW TABLE AS new_test OLD TABLE as old_test 4795 FOR EACH STATEMENT EXECUTE PROCEDURE multi_test_trig(); 4796 4797UPDATE multi_test SET i = i; 4798 4799DROP TABLE multi_test; 4800DROP FUNCTION multi_test_trig(); 4801 4802-- 4803-- Check type parsing and record fetching from partitioned tables 4804-- 4805 4806CREATE TABLE partitioned_table (a int, b text) PARTITION BY LIST (a); 4807CREATE TABLE pt_part1 PARTITION OF partitioned_table FOR VALUES IN (1); 4808CREATE TABLE pt_part2 PARTITION OF partitioned_table FOR VALUES IN (2); 4809 4810INSERT INTO partitioned_table VALUES (1, 'Row 1'); 4811INSERT INTO partitioned_table VALUES (2, 'Row 2'); 4812 4813CREATE OR REPLACE FUNCTION get_from_partitioned_table(partitioned_table.a%type) 4814RETURNS partitioned_table AS $$ 4815DECLARE 4816 a_val partitioned_table.a%TYPE; 4817 result partitioned_table%ROWTYPE; 4818BEGIN 4819 a_val := $1; 4820 SELECT * INTO result FROM partitioned_table WHERE a = a_val; 4821 RETURN result; 4822END; $$ LANGUAGE plpgsql; 4823 4824SELECT * FROM get_from_partitioned_table(1) AS t; 4825 4826CREATE OR REPLACE FUNCTION list_partitioned_table() 4827RETURNS SETOF partitioned_table.a%TYPE AS $$ 4828DECLARE 4829 row partitioned_table%ROWTYPE; 4830 a_val partitioned_table.a%TYPE; 4831BEGIN 4832 FOR row IN SELECT * FROM partitioned_table ORDER BY a LOOP 4833 a_val := row.a; 4834 RETURN NEXT a_val; 4835 END LOOP; 4836 RETURN; 4837END; $$ LANGUAGE plpgsql; 4838 4839SELECT * FROM list_partitioned_table() AS t; 4840