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 perform_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 perform_simple_func(5); 1668 1669 IF FOUND then 1670 INSERT INTO perform_test VALUES (100, 100); 1671 END IF; 1672 1673 PERFORM 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 proper snapshot handling in simple expressions 1689-- 1690 1691create temp table users(login text, id serial); 1692 1693create function sp_id_user(a_login text) returns int as $$ 1694declare x int; 1695begin 1696 select into x id from users where login = a_login; 1697 if found then return x; end if; 1698 return 0; 1699end$$ language plpgsql stable; 1700 1701insert into users values('user1'); 1702 1703select sp_id_user('user1'); 1704select sp_id_user('userx'); 1705 1706create function sp_add_user(a_login text) returns int as $$ 1707declare my_id_user int; 1708begin 1709 my_id_user = sp_id_user( a_login ); 1710 IF my_id_user > 0 THEN 1711 RETURN -1; -- error code for existing user 1712 END IF; 1713 INSERT INTO users ( login ) VALUES ( a_login ); 1714 my_id_user = sp_id_user( a_login ); 1715 IF my_id_user = 0 THEN 1716 RETURN -2; -- error code for insertion failure 1717 END IF; 1718 RETURN my_id_user; 1719end$$ language plpgsql; 1720 1721select sp_add_user('user1'); 1722select sp_add_user('user2'); 1723select sp_add_user('user2'); 1724select sp_add_user('user3'); 1725select sp_add_user('user3'); 1726 1727drop function sp_add_user(text); 1728drop function sp_id_user(text); 1729 1730-- 1731-- tests for refcursors 1732-- 1733create table rc_test (a int, b int); 1734 1735create function return_unnamed_refcursor() returns refcursor as $$ 1736declare 1737 rc refcursor; 1738begin 1739 open rc for select a from rc_test; 1740 return rc; 1741end 1742$$ language plpgsql; 1743 1744create function use_refcursor(rc refcursor) returns int as $$ 1745declare 1746 rc refcursor; 1747 x record; 1748begin 1749 rc := return_unnamed_refcursor(); 1750 fetch next from rc into x; 1751 return x.a; 1752end 1753$$ language plpgsql; 1754 1755select use_refcursor(return_unnamed_refcursor()); 1756 1757create function return_refcursor(rc refcursor) returns refcursor as $$ 1758begin 1759 open rc for select a from rc_test; 1760 return rc; 1761end 1762$$ language plpgsql; 1763 1764create function refcursor_test1(refcursor) returns refcursor as $$ 1765begin 1766 perform return_refcursor($1); 1767 return $1; 1768end 1769$$ language plpgsql; 1770 1771begin; 1772 1773select refcursor_test1('test1'); 1774fetch next in test1; 1775 1776select refcursor_test1('test2'); 1777fetch all from test2; 1778 1779commit; 1780 1781-- should fail 1782fetch next from test1; 1783 1784create function refcursor_test2(int, int) returns boolean as $$ 1785declare 1786 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2; 1787 nonsense record; 1788begin 1789 open c1($1, $2); 1790 fetch c1 into nonsense; 1791 close c1; 1792 if found then 1793 return true; 1794 else 1795 return false; 1796 end if; 1797end 1798$$ language plpgsql; 1799 1800select refcursor_test2(20000, 20000) as "Should be false", 1801 refcursor_test2(20, 20) as "Should be true"; 1802 1803-- 1804-- tests for cursors with named parameter arguments 1805-- 1806create function namedparmcursor_test1(int, int) returns boolean as $$ 1807declare 1808 c1 cursor (param1 int, param12 int) for select * from rc_test where a > param1 and b > param12; 1809 nonsense record; 1810begin 1811 open c1(param12 := $2, param1 := $1); 1812 fetch c1 into nonsense; 1813 close c1; 1814 if found then 1815 return true; 1816 else 1817 return false; 1818 end if; 1819end 1820$$ language plpgsql; 1821 1822select namedparmcursor_test1(20000, 20000) as "Should be false", 1823 namedparmcursor_test1(20, 20) as "Should be true"; 1824 1825-- mixing named and positional argument notations 1826create function namedparmcursor_test2(int, int) returns boolean as $$ 1827declare 1828 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2; 1829 nonsense record; 1830begin 1831 open c1(param1 := $1, $2); 1832 fetch c1 into nonsense; 1833 close c1; 1834 if found then 1835 return true; 1836 else 1837 return false; 1838 end if; 1839end 1840$$ language plpgsql; 1841select namedparmcursor_test2(20, 20); 1842 1843-- mixing named and positional: param2 is given twice, once in named notation 1844-- and second time in positional notation. Should throw an error at parse time 1845create function namedparmcursor_test3() returns void as $$ 1846declare 1847 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2; 1848begin 1849 open c1(param2 := 20, 21); 1850end 1851$$ language plpgsql; 1852 1853-- mixing named and positional: same as previous test, but param1 is duplicated 1854create function namedparmcursor_test4() returns void as $$ 1855declare 1856 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2; 1857begin 1858 open c1(20, param1 := 21); 1859end 1860$$ language plpgsql; 1861 1862-- duplicate named parameter, should throw an error at parse time 1863create function namedparmcursor_test5() returns void as $$ 1864declare 1865 c1 cursor (p1 int, p2 int) for 1866 select * from tenk1 where thousand = p1 and tenthous = p2; 1867begin 1868 open c1 (p2 := 77, p2 := 42); 1869end 1870$$ language plpgsql; 1871 1872-- not enough parameters, should throw an error at parse time 1873create function namedparmcursor_test6() returns void as $$ 1874declare 1875 c1 cursor (p1 int, p2 int) for 1876 select * from tenk1 where thousand = p1 and tenthous = p2; 1877begin 1878 open c1 (p2 := 77); 1879end 1880$$ language plpgsql; 1881 1882-- division by zero runtime error, the context given in the error message 1883-- should be sensible 1884create function namedparmcursor_test7() returns void as $$ 1885declare 1886 c1 cursor (p1 int, p2 int) for 1887 select * from tenk1 where thousand = p1 and tenthous = p2; 1888begin 1889 open c1 (p2 := 77, p1 := 42/0); 1890end $$ language plpgsql; 1891select namedparmcursor_test7(); 1892 1893-- check that line comments work correctly within the argument list (there 1894-- is some special handling of this case in the code: the newline after the 1895-- comment must be preserved when the argument-evaluating query is 1896-- constructed, otherwise the comment effectively comments out the next 1897-- argument, too) 1898create function namedparmcursor_test8() returns int4 as $$ 1899declare 1900 c1 cursor (p1 int, p2 int) for 1901 select count(*) from tenk1 where thousand = p1 and tenthous = p2; 1902 n int4; 1903begin 1904 open c1 (77 -- test 1905 , 42); 1906 fetch c1 into n; 1907 return n; 1908end $$ language plpgsql; 1909select namedparmcursor_test8(); 1910 1911-- cursor parameter name can match plpgsql variable or unreserved keyword 1912create function namedparmcursor_test9(p1 int) returns int4 as $$ 1913declare 1914 c1 cursor (p1 int, p2 int, debug int) for 1915 select count(*) from tenk1 where thousand = p1 and tenthous = p2 1916 and four = debug; 1917 p2 int4 := 1006; 1918 n int4; 1919begin 1920 open c1 (p1 := p1, p2 := p2, debug := 2); 1921 fetch c1 into n; 1922 return n; 1923end $$ language plpgsql; 1924select namedparmcursor_test9(6); 1925 1926-- 1927-- tests for "raise" processing 1928-- 1929create function raise_test1(int) returns int as $$ 1930begin 1931 raise notice 'This message has too many parameters!', $1; 1932 return $1; 1933end; 1934$$ language plpgsql; 1935 1936create function raise_test2(int) returns int as $$ 1937begin 1938 raise notice 'This message has too few parameters: %, %, %', $1, $1; 1939 return $1; 1940end; 1941$$ language plpgsql; 1942 1943create function raise_test3(int) returns int as $$ 1944begin 1945 raise notice 'This message has no parameters (despite having %% signs in it)!'; 1946 return $1; 1947end; 1948$$ language plpgsql; 1949 1950select raise_test3(1); 1951 1952-- Test re-RAISE inside a nested exception block. This case is allowed 1953-- by Oracle's PL/SQL but was handled differently by PG before 9.1. 1954 1955CREATE FUNCTION reraise_test() RETURNS void AS $$ 1956BEGIN 1957 BEGIN 1958 RAISE syntax_error; 1959 EXCEPTION 1960 WHEN syntax_error THEN 1961 BEGIN 1962 raise notice 'exception % thrown in inner block, reraising', sqlerrm; 1963 RAISE; 1964 EXCEPTION 1965 WHEN OTHERS THEN 1966 raise notice 'RIGHT - exception % caught in inner block', sqlerrm; 1967 END; 1968 END; 1969EXCEPTION 1970 WHEN OTHERS THEN 1971 raise notice 'WRONG - exception % caught in outer block', sqlerrm; 1972END; 1973$$ LANGUAGE plpgsql; 1974 1975SELECT reraise_test(); 1976 1977-- 1978-- reject function definitions that contain malformed SQL queries at 1979-- compile-time, where possible 1980-- 1981create function bad_sql1() returns int as $$ 1982declare a int; 1983begin 1984 a := 5; 1985 Johnny Yuma; 1986 a := 10; 1987 return a; 1988end$$ language plpgsql; 1989 1990create function bad_sql2() returns int as $$ 1991declare r record; 1992begin 1993 for r in select I fought the law, the law won LOOP 1994 raise notice 'in loop'; 1995 end loop; 1996 return 5; 1997end;$$ language plpgsql; 1998 1999-- a RETURN expression is mandatory, except for void-returning 2000-- functions, where it is not allowed 2001create function missing_return_expr() returns int as $$ 2002begin 2003 return ; 2004end;$$ language plpgsql; 2005 2006create function void_return_expr() returns void as $$ 2007begin 2008 return 5; 2009end;$$ language plpgsql; 2010 2011-- VOID functions are allowed to omit RETURN 2012create function void_return_expr() returns void as $$ 2013begin 2014 perform 2+2; 2015end;$$ language plpgsql; 2016 2017select void_return_expr(); 2018 2019-- but ordinary functions are not 2020create function missing_return_expr() returns int as $$ 2021begin 2022 perform 2+2; 2023end;$$ language plpgsql; 2024 2025select missing_return_expr(); 2026 2027drop function void_return_expr(); 2028drop function missing_return_expr(); 2029 2030-- 2031-- EXECUTE ... INTO test 2032-- 2033 2034create table eifoo (i integer, y integer); 2035create type eitype as (i integer, y integer); 2036 2037create or replace function execute_into_test(varchar) returns record as $$ 2038declare 2039 _r record; 2040 _rt eifoo%rowtype; 2041 _v eitype; 2042 i int; 2043 j int; 2044 k int; 2045begin 2046 execute 'insert into '||$1||' values(10,15)'; 2047 execute 'select (row).* from (select row(10,1)::eifoo) s' into _r; 2048 raise notice '% %', _r.i, _r.y; 2049 execute 'select * from '||$1||' limit 1' into _rt; 2050 raise notice '% %', _rt.i, _rt.y; 2051 execute 'select *, 20 from '||$1||' limit 1' into i, j, k; 2052 raise notice '% % %', i, j, k; 2053 execute 'select 1,2' into _v; 2054 return _v; 2055end; $$ language plpgsql; 2056 2057select execute_into_test('eifoo'); 2058 2059drop table eifoo cascade; 2060drop type eitype cascade; 2061 2062-- 2063-- SQLSTATE and SQLERRM test 2064-- 2065 2066create function excpt_test1() returns void as $$ 2067begin 2068 raise notice '% %', sqlstate, sqlerrm; 2069end; $$ language plpgsql; 2070-- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION 2071-- blocks 2072select excpt_test1(); 2073 2074create function excpt_test2() returns void as $$ 2075begin 2076 begin 2077 begin 2078 raise notice '% %', sqlstate, sqlerrm; 2079 end; 2080 end; 2081end; $$ language plpgsql; 2082-- should fail 2083select excpt_test2(); 2084 2085create function excpt_test3() returns void as $$ 2086begin 2087 begin 2088 raise exception 'user exception'; 2089 exception when others then 2090 raise notice 'caught exception % %', sqlstate, sqlerrm; 2091 begin 2092 raise notice '% %', sqlstate, sqlerrm; 2093 perform 10/0; 2094 exception 2095 when substring_error then 2096 -- this exception handler shouldn't be invoked 2097 raise notice 'unexpected exception: % %', sqlstate, sqlerrm; 2098 when division_by_zero then 2099 raise notice 'caught exception % %', sqlstate, sqlerrm; 2100 end; 2101 raise notice '% %', sqlstate, sqlerrm; 2102 end; 2103end; $$ language plpgsql; 2104select excpt_test3(); 2105 2106create function excpt_test4() returns text as $$ 2107begin 2108 begin perform 1/0; 2109 exception when others then return sqlerrm; end; 2110end; $$ language plpgsql; 2111select excpt_test4(); 2112 2113drop function excpt_test1(); 2114drop function excpt_test2(); 2115drop function excpt_test3(); 2116drop function excpt_test4(); 2117 2118-- parameters of raise stmt can be expressions 2119create function raise_exprs() returns void as $$ 2120declare 2121 a integer[] = '{10,20,30}'; 2122 c varchar = 'xyz'; 2123 i integer; 2124begin 2125 i := 2; 2126 raise notice '%; %; %; %; %; %', a, a[i], c, (select c || 'abc'), row(10,'aaa',NULL,30), NULL; 2127end;$$ language plpgsql; 2128 2129select raise_exprs(); 2130drop function raise_exprs(); 2131 2132-- regression test: verify that multiple uses of same plpgsql datum within 2133-- a SQL command all get mapped to the same $n parameter. The return value 2134-- of the SELECT is not important, we only care that it doesn't fail with 2135-- a complaint about an ungrouped column reference. 2136create function multi_datum_use(p1 int) returns bool as $$ 2137declare 2138 x int; 2139 y int; 2140begin 2141 select into x,y unique1/p1, unique1/$1 from tenk1 group by unique1/p1; 2142 return x = y; 2143end$$ language plpgsql; 2144 2145select multi_datum_use(42); 2146 2147-- 2148-- Test STRICT limiter in both planned and EXECUTE invocations. 2149-- Note that a data-modifying query is quasi strict (disallow multi rows) 2150-- by default in the planned case, but not in EXECUTE. 2151-- 2152 2153create temp table foo (f1 int, f2 int); 2154 2155insert into foo values (1,2), (3,4); 2156 2157create or replace function stricttest() returns void as $$ 2158declare x record; 2159begin 2160 -- should work 2161 insert into foo values(5,6) returning * into x; 2162 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2163end$$ language plpgsql; 2164 2165select stricttest(); 2166 2167create or replace function stricttest() returns void as $$ 2168declare x record; 2169begin 2170 -- should fail due to implicit strict 2171 insert into foo values(7,8),(9,10) returning * into x; 2172 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2173end$$ language plpgsql; 2174 2175select stricttest(); 2176 2177create or replace function stricttest() returns void as $$ 2178declare x record; 2179begin 2180 -- should work 2181 execute 'insert into foo values(5,6) returning *' into x; 2182 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2183end$$ language plpgsql; 2184 2185select stricttest(); 2186 2187create or replace function stricttest() returns void as $$ 2188declare x record; 2189begin 2190 -- this should work since EXECUTE isn't as picky 2191 execute 'insert into foo values(7,8),(9,10) returning *' into x; 2192 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2193end$$ language plpgsql; 2194 2195select stricttest(); 2196 2197select * from foo; 2198 2199create or replace function stricttest() returns void as $$ 2200declare x record; 2201begin 2202 -- should work 2203 select * from foo where f1 = 3 into strict x; 2204 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2205end$$ language plpgsql; 2206 2207select stricttest(); 2208 2209create or replace function stricttest() returns void as $$ 2210declare x record; 2211begin 2212 -- should fail, no rows 2213 select * from foo where f1 = 0 into strict x; 2214 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2215end$$ language plpgsql; 2216 2217select stricttest(); 2218 2219create or replace function stricttest() returns void as $$ 2220declare x record; 2221begin 2222 -- should fail, too many rows 2223 select * from foo where f1 > 3 into strict x; 2224 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2225end$$ language plpgsql; 2226 2227select stricttest(); 2228 2229create or replace function stricttest() returns void as $$ 2230declare x record; 2231begin 2232 -- should work 2233 execute 'select * from foo where f1 = 3' into strict x; 2234 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2235end$$ language plpgsql; 2236 2237select stricttest(); 2238 2239create or replace function stricttest() returns void as $$ 2240declare x record; 2241begin 2242 -- should fail, no rows 2243 execute 'select * from foo where f1 = 0' into strict x; 2244 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2245end$$ language plpgsql; 2246 2247select stricttest(); 2248 2249create or replace function stricttest() returns void as $$ 2250declare x record; 2251begin 2252 -- should fail, too many rows 2253 execute 'select * from foo where f1 > 3' into strict x; 2254 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2255end$$ language plpgsql; 2256 2257select stricttest(); 2258 2259drop function stricttest(); 2260 2261-- test printing parameters after failure due to STRICT 2262 2263set plpgsql.print_strict_params to true; 2264 2265create or replace function stricttest() returns void as $$ 2266declare 2267x record; 2268p1 int := 2; 2269p3 text := 'foo'; 2270begin 2271 -- no rows 2272 select * from foo where f1 = p1 and f1::text = p3 into strict x; 2273 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2274end$$ language plpgsql; 2275 2276select stricttest(); 2277 2278create or replace function stricttest() returns void as $$ 2279declare 2280x record; 2281p1 int := 2; 2282p3 text := 'foo'; 2283begin 2284 -- too many rows 2285 select * from foo where f1 > p1 or f1::text = p3 into strict x; 2286 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2287end$$ language plpgsql; 2288 2289select stricttest(); 2290 2291create or replace function stricttest() returns void as $$ 2292declare x record; 2293begin 2294 -- too many rows, no params 2295 select * from foo where f1 > 3 into strict x; 2296 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2297end$$ language plpgsql; 2298 2299select stricttest(); 2300 2301create or replace function stricttest() returns void as $$ 2302declare x record; 2303begin 2304 -- no rows 2305 execute 'select * from foo where f1 = $1 or f1::text = $2' using 0, 'foo' into strict x; 2306 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2307end$$ language plpgsql; 2308 2309select stricttest(); 2310 2311create or replace function stricttest() returns void as $$ 2312declare x record; 2313begin 2314 -- too many rows 2315 execute 'select * from foo where f1 > $1' using 1 into strict x; 2316 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2317end$$ language plpgsql; 2318 2319select stricttest(); 2320 2321create or replace function stricttest() returns void as $$ 2322declare x record; 2323begin 2324 -- too many rows, no parameters 2325 execute 'select * from foo where f1 > 3' into strict x; 2326 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2327end$$ language plpgsql; 2328 2329select stricttest(); 2330 2331create or replace function stricttest() returns void as $$ 2332-- override the global 2333#print_strict_params off 2334declare 2335x record; 2336p1 int := 2; 2337p3 text := 'foo'; 2338begin 2339 -- too many rows 2340 select * from foo where f1 > p1 or f1::text = p3 into strict x; 2341 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2342end$$ language plpgsql; 2343 2344select stricttest(); 2345 2346reset plpgsql.print_strict_params; 2347 2348create or replace function stricttest() returns void as $$ 2349-- override the global 2350#print_strict_params on 2351declare 2352x record; 2353p1 int := 2; 2354p3 text := 'foo'; 2355begin 2356 -- too many rows 2357 select * from foo where f1 > p1 or f1::text = p3 into strict x; 2358 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2359end$$ language plpgsql; 2360 2361select stricttest(); 2362 2363-- test warnings and errors 2364set plpgsql.extra_warnings to 'all'; 2365set plpgsql.extra_warnings to 'none'; 2366set plpgsql.extra_errors to 'all'; 2367set plpgsql.extra_errors to 'none'; 2368 2369-- test warnings when shadowing a variable 2370 2371set plpgsql.extra_warnings to 'shadowed_variables'; 2372 2373-- simple shadowing of input and output parameters 2374create or replace function shadowtest(in1 int) 2375 returns table (out1 int) as $$ 2376declare 2377in1 int; 2378out1 int; 2379begin 2380end 2381$$ language plpgsql; 2382select shadowtest(1); 2383 2384set plpgsql.extra_warnings to 'shadowed_variables'; 2385select shadowtest(1); 2386create or replace function shadowtest(in1 int) 2387 returns table (out1 int) as $$ 2388declare 2389in1 int; 2390out1 int; 2391begin 2392end 2393$$ language plpgsql; 2394select shadowtest(1); 2395drop function shadowtest(int); 2396 2397-- shadowing in a second DECLARE block 2398create or replace function shadowtest() 2399 returns void as $$ 2400declare 2401f1 int; 2402begin 2403 declare 2404 f1 int; 2405 begin 2406 end; 2407end$$ language plpgsql; 2408drop function shadowtest(); 2409 2410-- several levels of shadowing 2411create or replace function shadowtest(in1 int) 2412 returns void as $$ 2413declare 2414in1 int; 2415begin 2416 declare 2417 in1 int; 2418 begin 2419 end; 2420end$$ language plpgsql; 2421drop function shadowtest(int); 2422 2423-- shadowing in cursor definitions 2424create or replace function shadowtest() 2425 returns void as $$ 2426declare 2427f1 int; 2428c1 cursor (f1 int) for select 1; 2429begin 2430end$$ language plpgsql; 2431drop function shadowtest(); 2432 2433-- test errors when shadowing a variable 2434 2435set plpgsql.extra_errors to 'shadowed_variables'; 2436 2437create or replace function shadowtest(f1 int) 2438 returns boolean as $$ 2439declare f1 int; begin return 1; end $$ language plpgsql; 2440 2441select shadowtest(1); 2442 2443reset plpgsql.extra_errors; 2444reset plpgsql.extra_warnings; 2445 2446create or replace function shadowtest(f1 int) 2447 returns boolean as $$ 2448declare f1 int; begin return 1; end $$ language plpgsql; 2449 2450select shadowtest(1); 2451 2452-- runtime extra checks 2453set plpgsql.extra_warnings to 'too_many_rows'; 2454 2455do $$ 2456declare x int; 2457begin 2458 select v from generate_series(1,2) g(v) into x; 2459end; 2460$$; 2461 2462set plpgsql.extra_errors to 'too_many_rows'; 2463 2464do $$ 2465declare x int; 2466begin 2467 select v from generate_series(1,2) g(v) into x; 2468end; 2469$$; 2470 2471reset plpgsql.extra_errors; 2472reset plpgsql.extra_warnings; 2473 2474set plpgsql.extra_warnings to 'strict_multi_assignment'; 2475 2476do $$ 2477declare 2478 x int; 2479 y int; 2480begin 2481 select 1 into x, y; 2482 select 1,2 into x, y; 2483 select 1,2,3 into x, y; 2484end 2485$$; 2486 2487set plpgsql.extra_errors to 'strict_multi_assignment'; 2488 2489do $$ 2490declare 2491 x int; 2492 y int; 2493begin 2494 select 1 into x, y; 2495 select 1,2 into x, y; 2496 select 1,2,3 into x, y; 2497end 2498$$; 2499 2500create table test_01(a int, b int, c int); 2501 2502alter table test_01 drop column a; 2503 2504-- the check is active only when source table is not empty 2505insert into test_01 values(10,20); 2506 2507do $$ 2508declare 2509 x int; 2510 y int; 2511begin 2512 select * from test_01 into x, y; -- should be ok 2513 raise notice 'ok'; 2514 select * from test_01 into x; -- should to fail 2515end; 2516$$; 2517 2518do $$ 2519declare 2520 t test_01; 2521begin 2522 select 1, 2 into t; -- should be ok 2523 raise notice 'ok'; 2524 select 1, 2, 3 into t; -- should fail; 2525end; 2526$$; 2527 2528do $$ 2529declare 2530 t test_01; 2531begin 2532 select 1 into t; -- should fail; 2533end; 2534$$; 2535 2536drop table test_01; 2537 2538reset plpgsql.extra_errors; 2539reset plpgsql.extra_warnings; 2540 2541-- test scrollable cursor support 2542 2543create function sc_test() returns setof integer as $$ 2544declare 2545 c scroll cursor for select f1 from int4_tbl; 2546 x integer; 2547begin 2548 open c; 2549 fetch last from c into x; 2550 while found loop 2551 return next x; 2552 fetch prior from c into x; 2553 end loop; 2554 close c; 2555end; 2556$$ language plpgsql; 2557 2558select * from sc_test(); 2559 2560create or replace function sc_test() returns setof integer as $$ 2561declare 2562 c no scroll cursor for select f1 from int4_tbl; 2563 x integer; 2564begin 2565 open c; 2566 fetch last from c into x; 2567 while found loop 2568 return next x; 2569 fetch prior from c into x; 2570 end loop; 2571 close c; 2572end; 2573$$ language plpgsql; 2574 2575select * from sc_test(); -- fails because of NO SCROLL specification 2576 2577create or replace function sc_test() returns setof integer as $$ 2578declare 2579 c refcursor; 2580 x integer; 2581begin 2582 open c scroll for select f1 from int4_tbl; 2583 fetch last from c into x; 2584 while found loop 2585 return next x; 2586 fetch prior from c into x; 2587 end loop; 2588 close c; 2589end; 2590$$ language plpgsql; 2591 2592select * from sc_test(); 2593 2594create or replace function sc_test() returns setof integer as $$ 2595declare 2596 c refcursor; 2597 x integer; 2598begin 2599 open c scroll for execute 'select f1 from int4_tbl'; 2600 fetch last from c into x; 2601 while found loop 2602 return next x; 2603 fetch relative -2 from c into x; 2604 end loop; 2605 close c; 2606end; 2607$$ language plpgsql; 2608 2609select * from sc_test(); 2610 2611create or replace function sc_test() returns setof integer as $$ 2612declare 2613 c refcursor; 2614 x integer; 2615begin 2616 open c scroll for execute 'select f1 from int4_tbl'; 2617 fetch last from c into x; 2618 while found loop 2619 return next x; 2620 move backward 2 from c; 2621 fetch relative -1 from c into x; 2622 end loop; 2623 close c; 2624end; 2625$$ language plpgsql; 2626 2627select * from sc_test(); 2628 2629create or replace function sc_test() returns setof integer as $$ 2630declare 2631 c cursor for select * from generate_series(1, 10); 2632 x integer; 2633begin 2634 open c; 2635 loop 2636 move relative 2 in c; 2637 if not found then 2638 exit; 2639 end if; 2640 fetch next from c into x; 2641 if found then 2642 return next x; 2643 end if; 2644 end loop; 2645 close c; 2646end; 2647$$ language plpgsql; 2648 2649select * from sc_test(); 2650 2651create or replace function sc_test() returns setof integer as $$ 2652declare 2653 c cursor for select * from generate_series(1, 10); 2654 x integer; 2655begin 2656 open c; 2657 move forward all in c; 2658 fetch backward from c into x; 2659 if found then 2660 return next x; 2661 end if; 2662 close c; 2663end; 2664$$ language plpgsql; 2665 2666select * from sc_test(); 2667 2668drop function sc_test(); 2669 2670-- test qualified variable names 2671 2672create function pl_qual_names (param1 int) returns void as $$ 2673<<outerblock>> 2674declare 2675 param1 int := 1; 2676begin 2677 <<innerblock>> 2678 declare 2679 param1 int := 2; 2680 begin 2681 raise notice 'param1 = %', param1; 2682 raise notice 'pl_qual_names.param1 = %', pl_qual_names.param1; 2683 raise notice 'outerblock.param1 = %', outerblock.param1; 2684 raise notice 'innerblock.param1 = %', innerblock.param1; 2685 end; 2686end; 2687$$ language plpgsql; 2688 2689select pl_qual_names(42); 2690 2691drop function pl_qual_names(int); 2692 2693-- tests for RETURN QUERY 2694create function ret_query1(out int, out int) returns setof record as $$ 2695begin 2696 $1 := -1; 2697 $2 := -2; 2698 return next; 2699 return query select x + 1, x * 10 from generate_series(0, 10) s (x); 2700 return next; 2701end; 2702$$ language plpgsql; 2703 2704select * from ret_query1(); 2705 2706create type record_type as (x text, y int, z boolean); 2707 2708create or replace function ret_query2(lim int) returns setof record_type as $$ 2709begin 2710 return query select md5(s.x::text), s.x, s.x > 0 2711 from generate_series(-8, lim) s (x) where s.x % 2 = 0; 2712end; 2713$$ language plpgsql; 2714 2715select * from ret_query2(8); 2716 2717-- test EXECUTE USING 2718create function exc_using(int, text) returns int as $$ 2719declare i int; 2720begin 2721 for i in execute 'select * from generate_series(1,$1)' using $1+1 loop 2722 raise notice '%', i; 2723 end loop; 2724 execute 'select $2 + $2*3 + length($1)' into i using $2,$1; 2725 return i; 2726end 2727$$ language plpgsql; 2728 2729select exc_using(5, 'foobar'); 2730 2731drop function exc_using(int, text); 2732 2733create or replace function exc_using(int) returns void as $$ 2734declare 2735 c refcursor; 2736 i int; 2737begin 2738 open c for execute 'select * from generate_series(1,$1)' using $1+1; 2739 loop 2740 fetch c into i; 2741 exit when not found; 2742 raise notice '%', i; 2743 end loop; 2744 close c; 2745 return; 2746end; 2747$$ language plpgsql; 2748 2749select exc_using(5); 2750 2751drop function exc_using(int); 2752 2753-- test FOR-over-cursor 2754 2755create or replace function forc01() returns void as $$ 2756declare 2757 c cursor(r1 integer, r2 integer) 2758 for select * from generate_series(r1,r2) i; 2759 c2 cursor 2760 for select * from generate_series(41,43) i; 2761begin 2762 for r in c(5,7) loop 2763 raise notice '% from %', r.i, c; 2764 end loop; 2765 -- again, to test if cursor was closed properly 2766 for r in c(9,10) loop 2767 raise notice '% from %', r.i, c; 2768 end loop; 2769 -- and test a parameterless cursor 2770 for r in c2 loop 2771 raise notice '% from %', r.i, c2; 2772 end loop; 2773 -- and try it with a hand-assigned name 2774 raise notice 'after loop, c2 = %', c2; 2775 c2 := 'special_name'; 2776 for r in c2 loop 2777 raise notice '% from %', r.i, c2; 2778 end loop; 2779 raise notice 'after loop, c2 = %', c2; 2780 -- and try it with a generated name 2781 -- (which we can't show in the output because it's variable) 2782 c2 := null; 2783 for r in c2 loop 2784 raise notice '%', r.i; 2785 end loop; 2786 raise notice 'after loop, c2 = %', c2; 2787 return; 2788end; 2789$$ language plpgsql; 2790 2791select forc01(); 2792 2793-- try updating the cursor's current row 2794 2795create temp table forc_test as 2796 select n as i, n as j from generate_series(1,10) n; 2797 2798create or replace function forc01() returns void as $$ 2799declare 2800 c cursor for select * from forc_test; 2801begin 2802 for r in c loop 2803 raise notice '%, %', r.i, r.j; 2804 update forc_test set i = i * 100, j = r.j * 2 where current of c; 2805 end loop; 2806end; 2807$$ language plpgsql; 2808 2809select forc01(); 2810 2811select * from forc_test; 2812 2813-- same, with a cursor whose portal name doesn't match variable name 2814create or replace function forc01() returns void as $$ 2815declare 2816 c refcursor := 'fooled_ya'; 2817 r record; 2818begin 2819 open c for select * from forc_test; 2820 loop 2821 fetch c into r; 2822 exit when not found; 2823 raise notice '%, %', r.i, r.j; 2824 update forc_test set i = i * 100, j = r.j * 2 where current of c; 2825 end loop; 2826end; 2827$$ language plpgsql; 2828 2829select forc01(); 2830 2831select * from forc_test; 2832 2833drop function forc01(); 2834 2835-- fail because cursor has no query bound to it 2836 2837create or replace function forc_bad() returns void as $$ 2838declare 2839 c refcursor; 2840begin 2841 for r in c loop 2842 raise notice '%', r.i; 2843 end loop; 2844end; 2845$$ language plpgsql; 2846 2847-- test RETURN QUERY EXECUTE 2848 2849create or replace function return_dquery() 2850returns setof int as $$ 2851begin 2852 return query execute 'select * from (values(10),(20)) f'; 2853 return query execute 'select * from (values($1),($2)) f' using 40,50; 2854end; 2855$$ language plpgsql; 2856 2857select * from return_dquery(); 2858 2859drop function return_dquery(); 2860 2861-- test RETURN QUERY with dropped columns 2862 2863create table tabwithcols(a int, b int, c int, d int); 2864insert into tabwithcols values(10,20,30,40),(50,60,70,80); 2865 2866create or replace function returnqueryf() 2867returns setof tabwithcols as $$ 2868begin 2869 return query select * from tabwithcols; 2870 return query execute 'select * from tabwithcols'; 2871end; 2872$$ language plpgsql; 2873 2874select * from returnqueryf(); 2875 2876alter table tabwithcols drop column b; 2877 2878select * from returnqueryf(); 2879 2880alter table tabwithcols drop column d; 2881 2882select * from returnqueryf(); 2883 2884alter table tabwithcols add column d int; 2885 2886select * from returnqueryf(); 2887 2888drop function returnqueryf(); 2889drop table tabwithcols; 2890 2891-- 2892-- Tests for composite-type results 2893-- 2894 2895create type compostype as (x int, y varchar); 2896 2897-- test: use of variable of composite type in return statement 2898create or replace function compos() returns compostype as $$ 2899declare 2900 v compostype; 2901begin 2902 v := (1, 'hello'); 2903 return v; 2904end; 2905$$ language plpgsql; 2906 2907select compos(); 2908 2909-- test: use of variable of record type in return statement 2910create or replace function compos() returns compostype as $$ 2911declare 2912 v record; 2913begin 2914 v := (1, 'hello'::varchar); 2915 return v; 2916end; 2917$$ language plpgsql; 2918 2919select compos(); 2920 2921-- test: use of row expr in return statement 2922create or replace function compos() returns compostype as $$ 2923begin 2924 return (1, 'hello'::varchar); 2925end; 2926$$ language plpgsql; 2927 2928select compos(); 2929 2930-- this does not work currently (no implicit casting) 2931create or replace function compos() returns compostype as $$ 2932begin 2933 return (1, 'hello'); 2934end; 2935$$ language plpgsql; 2936 2937select compos(); 2938 2939-- ... but this does 2940create or replace function compos() returns compostype as $$ 2941begin 2942 return (1, 'hello')::compostype; 2943end; 2944$$ language plpgsql; 2945 2946select compos(); 2947 2948drop function compos(); 2949 2950-- test: return a row expr as record. 2951create or replace function composrec() returns record as $$ 2952declare 2953 v record; 2954begin 2955 v := (1, 'hello'); 2956 return v; 2957end; 2958$$ language plpgsql; 2959 2960select composrec(); 2961 2962-- test: return row expr in return statement. 2963create or replace function composrec() returns record as $$ 2964begin 2965 return (1, 'hello'); 2966end; 2967$$ language plpgsql; 2968 2969select composrec(); 2970 2971drop function composrec(); 2972 2973-- test: row expr in RETURN NEXT statement. 2974create or replace function compos() returns setof compostype as $$ 2975begin 2976 for i in 1..3 2977 loop 2978 return next (1, 'hello'::varchar); 2979 end loop; 2980 return next null::compostype; 2981 return next (2, 'goodbye')::compostype; 2982end; 2983$$ language plpgsql; 2984 2985select * from compos(); 2986 2987drop function compos(); 2988 2989-- test: use invalid expr in return statement. 2990create or replace function compos() returns compostype as $$ 2991begin 2992 return 1 + 1; 2993end; 2994$$ language plpgsql; 2995 2996select compos(); 2997 2998-- RETURN variable is a different code path ... 2999create or replace function compos() returns compostype as $$ 3000declare x int := 42; 3001begin 3002 return x; 3003end; 3004$$ language plpgsql; 3005 3006select * from compos(); 3007 3008drop function compos(); 3009 3010-- test: invalid use of composite variable in scalar-returning function 3011create or replace function compos() returns int as $$ 3012declare 3013 v compostype; 3014begin 3015 v := (1, 'hello'); 3016 return v; 3017end; 3018$$ language plpgsql; 3019 3020select compos(); 3021 3022-- test: invalid use of composite expression in scalar-returning function 3023create or replace function compos() returns int as $$ 3024begin 3025 return (1, 'hello')::compostype; 3026end; 3027$$ language plpgsql; 3028 3029select compos(); 3030 3031drop function compos(); 3032drop type compostype; 3033 3034-- 3035-- Tests for 8.4's new RAISE features 3036-- 3037 3038create or replace function raise_test() returns void as $$ 3039begin 3040 raise notice '% % %', 1, 2, 3 3041 using errcode = '55001', detail = 'some detail info', hint = 'some hint'; 3042 raise '% % %', 1, 2, 3 3043 using errcode = 'division_by_zero', detail = 'some detail info'; 3044end; 3045$$ language plpgsql; 3046 3047select raise_test(); 3048 3049-- Since we can't actually see the thrown SQLSTATE in default psql output, 3050-- test it like this; this also tests re-RAISE 3051 3052create or replace function raise_test() returns void as $$ 3053begin 3054 raise 'check me' 3055 using errcode = 'division_by_zero', detail = 'some detail info'; 3056 exception 3057 when others then 3058 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; 3059 raise; 3060end; 3061$$ language plpgsql; 3062 3063select raise_test(); 3064 3065create or replace function raise_test() returns void as $$ 3066begin 3067 raise 'check me' 3068 using errcode = '1234F', detail = 'some detail info'; 3069 exception 3070 when others then 3071 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; 3072 raise; 3073end; 3074$$ language plpgsql; 3075 3076select raise_test(); 3077 3078-- SQLSTATE specification in WHEN 3079create or replace function raise_test() returns void as $$ 3080begin 3081 raise 'check me' 3082 using errcode = '1234F', detail = 'some detail info'; 3083 exception 3084 when sqlstate '1234F' then 3085 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; 3086 raise; 3087end; 3088$$ language plpgsql; 3089 3090select raise_test(); 3091 3092create or replace function raise_test() returns void as $$ 3093begin 3094 raise division_by_zero using detail = 'some detail info'; 3095 exception 3096 when others then 3097 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; 3098 raise; 3099end; 3100$$ language plpgsql; 3101 3102select raise_test(); 3103 3104create or replace function raise_test() returns void as $$ 3105begin 3106 raise division_by_zero; 3107end; 3108$$ language plpgsql; 3109 3110select raise_test(); 3111 3112create or replace function raise_test() returns void as $$ 3113begin 3114 raise sqlstate '1234F'; 3115end; 3116$$ language plpgsql; 3117 3118select raise_test(); 3119 3120create or replace function raise_test() returns void as $$ 3121begin 3122 raise division_by_zero using message = 'custom' || ' message'; 3123end; 3124$$ language plpgsql; 3125 3126select raise_test(); 3127 3128create or replace function raise_test() returns void as $$ 3129begin 3130 raise using message = 'custom' || ' message', errcode = '22012'; 3131end; 3132$$ language plpgsql; 3133 3134select raise_test(); 3135 3136-- conflict on message 3137create or replace function raise_test() returns void as $$ 3138begin 3139 raise notice 'some message' using message = 'custom' || ' message', errcode = '22012'; 3140end; 3141$$ language plpgsql; 3142 3143select raise_test(); 3144 3145-- conflict on errcode 3146create or replace function raise_test() returns void as $$ 3147begin 3148 raise division_by_zero using message = 'custom' || ' message', errcode = '22012'; 3149end; 3150$$ language plpgsql; 3151 3152select raise_test(); 3153 3154-- nothing to re-RAISE 3155create or replace function raise_test() returns void as $$ 3156begin 3157 raise; 3158end; 3159$$ language plpgsql; 3160 3161select raise_test(); 3162 3163-- test access to exception data 3164create function zero_divide() returns int as $$ 3165declare v int := 0; 3166begin 3167 return 10 / v; 3168end; 3169$$ language plpgsql; 3170 3171create or replace function raise_test() returns void as $$ 3172begin 3173 raise exception 'custom exception' 3174 using detail = 'some detail of custom exception', 3175 hint = 'some hint related to custom exception'; 3176end; 3177$$ language plpgsql; 3178 3179create function stacked_diagnostics_test() returns void as $$ 3180declare _sqlstate text; 3181 _message text; 3182 _context text; 3183begin 3184 perform zero_divide(); 3185exception when others then 3186 get stacked diagnostics 3187 _sqlstate = returned_sqlstate, 3188 _message = message_text, 3189 _context = pg_exception_context; 3190 raise notice 'sqlstate: %, message: %, context: [%]', 3191 _sqlstate, _message, replace(_context, E'\n', ' <- '); 3192end; 3193$$ language plpgsql; 3194 3195select stacked_diagnostics_test(); 3196 3197create or replace function stacked_diagnostics_test() returns void as $$ 3198declare _detail text; 3199 _hint text; 3200 _message text; 3201begin 3202 perform raise_test(); 3203exception when others then 3204 get stacked diagnostics 3205 _message = message_text, 3206 _detail = pg_exception_detail, 3207 _hint = pg_exception_hint; 3208 raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint; 3209end; 3210$$ language plpgsql; 3211 3212select stacked_diagnostics_test(); 3213 3214-- fail, cannot use stacked diagnostics statement outside handler 3215create or replace function stacked_diagnostics_test() returns void as $$ 3216declare _detail text; 3217 _hint text; 3218 _message text; 3219begin 3220 get stacked diagnostics 3221 _message = message_text, 3222 _detail = pg_exception_detail, 3223 _hint = pg_exception_hint; 3224 raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint; 3225end; 3226$$ language plpgsql; 3227 3228select stacked_diagnostics_test(); 3229 3230drop function zero_divide(); 3231drop function stacked_diagnostics_test(); 3232 3233-- check cases where implicit SQLSTATE variable could be confused with 3234-- SQLSTATE as a keyword, cf bug #5524 3235create or replace function raise_test() returns void as $$ 3236begin 3237 perform 1/0; 3238exception 3239 when sqlstate '22012' then 3240 raise notice using message = sqlstate; 3241 raise sqlstate '22012' using message = 'substitute message'; 3242end; 3243$$ language plpgsql; 3244 3245select raise_test(); 3246 3247drop function raise_test(); 3248 3249-- test passing column_name, constraint_name, datatype_name, table_name 3250-- and schema_name error fields 3251 3252create or replace function stacked_diagnostics_test() returns void as $$ 3253declare _column_name text; 3254 _constraint_name text; 3255 _datatype_name text; 3256 _table_name text; 3257 _schema_name text; 3258begin 3259 raise exception using 3260 column = '>>some column name<<', 3261 constraint = '>>some constraint name<<', 3262 datatype = '>>some datatype name<<', 3263 table = '>>some table name<<', 3264 schema = '>>some schema name<<'; 3265exception when others then 3266 get stacked diagnostics 3267 _column_name = column_name, 3268 _constraint_name = constraint_name, 3269 _datatype_name = pg_datatype_name, 3270 _table_name = table_name, 3271 _schema_name = schema_name; 3272 raise notice 'column %, constraint %, type %, table %, schema %', 3273 _column_name, _constraint_name, _datatype_name, _table_name, _schema_name; 3274end; 3275$$ language plpgsql; 3276 3277select stacked_diagnostics_test(); 3278 3279drop function stacked_diagnostics_test(); 3280 3281-- test variadic functions 3282 3283create or replace function vari(variadic int[]) 3284returns void as $$ 3285begin 3286 for i in array_lower($1,1)..array_upper($1,1) loop 3287 raise notice '%', $1[i]; 3288 end loop; end; 3289$$ language plpgsql; 3290 3291select vari(1,2,3,4,5); 3292select vari(3,4,5); 3293select vari(variadic array[5,6,7]); 3294 3295drop function vari(int[]); 3296 3297-- coercion test 3298create or replace function pleast(variadic numeric[]) 3299returns numeric as $$ 3300declare aux numeric = $1[array_lower($1,1)]; 3301begin 3302 for i in array_lower($1,1)+1..array_upper($1,1) loop 3303 if $1[i] < aux then aux := $1[i]; end if; 3304 end loop; 3305 return aux; 3306end; 3307$$ language plpgsql immutable strict; 3308 3309select pleast(10,1,2,3,-16); 3310select pleast(10.2,2.2,-1.1); 3311select pleast(10.2,10, -20); 3312select pleast(10,20, -1.0); 3313 3314-- in case of conflict, non-variadic version is preferred 3315create or replace function pleast(numeric) 3316returns numeric as $$ 3317begin 3318 raise notice 'non-variadic function called'; 3319 return $1; 3320end; 3321$$ language plpgsql immutable strict; 3322 3323select pleast(10); 3324 3325drop function pleast(numeric[]); 3326drop function pleast(numeric); 3327 3328-- test table functions 3329 3330create function tftest(int) returns table(a int, b int) as $$ 3331begin 3332 return query select $1, $1+i from generate_series(1,5) g(i); 3333end; 3334$$ language plpgsql immutable strict; 3335 3336select * from tftest(10); 3337 3338create or replace function tftest(a1 int) returns table(a int, b int) as $$ 3339begin 3340 a := a1; b := a1 + 1; 3341 return next; 3342 a := a1 * 10; b := a1 * 10 + 1; 3343 return next; 3344end; 3345$$ language plpgsql immutable strict; 3346 3347select * from tftest(10); 3348 3349drop function tftest(int); 3350 3351create or replace function rttest() 3352returns setof int as $$ 3353declare rc int; 3354 rca int[]; 3355begin 3356 return query values(10),(20); 3357 get diagnostics rc = row_count; 3358 raise notice '% %', found, rc; 3359 return query select * from (values(10),(20)) f(a) where false; 3360 get diagnostics rc = row_count; 3361 raise notice '% %', found, rc; 3362 return query execute 'values(10),(20)'; 3363 -- just for fun, let's use array elements as targets 3364 get diagnostics rca[1] = row_count; 3365 raise notice '% %', found, rca[1]; 3366 return query execute 'select * from (values(10),(20)) f(a) where false'; 3367 get diagnostics rca[2] = row_count; 3368 raise notice '% %', found, rca[2]; 3369end; 3370$$ language plpgsql; 3371 3372select * from rttest(); 3373 3374drop function rttest(); 3375 3376-- Test for proper cleanup at subtransaction exit. This example 3377-- exposed a bug in PG 8.2. 3378 3379CREATE FUNCTION leaker_1(fail BOOL) RETURNS INTEGER AS $$ 3380DECLARE 3381 v_var INTEGER; 3382BEGIN 3383 BEGIN 3384 v_var := (leaker_2(fail)).error_code; 3385 EXCEPTION 3386 WHEN others THEN RETURN 0; 3387 END; 3388 RETURN 1; 3389END; 3390$$ LANGUAGE plpgsql; 3391 3392CREATE FUNCTION leaker_2(fail BOOL, OUT error_code INTEGER, OUT new_id INTEGER) 3393 RETURNS RECORD AS $$ 3394BEGIN 3395 IF fail THEN 3396 RAISE EXCEPTION 'fail ...'; 3397 END IF; 3398 error_code := 1; 3399 new_id := 1; 3400 RETURN; 3401END; 3402$$ LANGUAGE plpgsql; 3403 3404SELECT * FROM leaker_1(false); 3405SELECT * FROM leaker_1(true); 3406 3407DROP FUNCTION leaker_1(bool); 3408DROP FUNCTION leaker_2(bool); 3409 3410-- Test for appropriate cleanup of non-simple expression evaluations 3411-- (bug in all versions prior to August 2010) 3412 3413CREATE FUNCTION nonsimple_expr_test() RETURNS text[] AS $$ 3414DECLARE 3415 arr text[]; 3416 lr text; 3417 i integer; 3418BEGIN 3419 arr := array[array['foo','bar'], array['baz', 'quux']]; 3420 lr := 'fool'; 3421 i := 1; 3422 -- use sub-SELECTs to make expressions non-simple 3423 arr[(SELECT i)][(SELECT i+1)] := (SELECT lr); 3424 RETURN arr; 3425END; 3426$$ LANGUAGE plpgsql; 3427 3428SELECT nonsimple_expr_test(); 3429 3430DROP FUNCTION nonsimple_expr_test(); 3431 3432CREATE FUNCTION nonsimple_expr_test() RETURNS integer AS $$ 3433declare 3434 i integer NOT NULL := 0; 3435begin 3436 begin 3437 i := (SELECT NULL::integer); -- should throw error 3438 exception 3439 WHEN OTHERS THEN 3440 i := (SELECT 1::integer); 3441 end; 3442 return i; 3443end; 3444$$ LANGUAGE plpgsql; 3445 3446SELECT nonsimple_expr_test(); 3447 3448DROP FUNCTION nonsimple_expr_test(); 3449 3450-- 3451-- Test cases involving recursion and error recovery in simple expressions 3452-- (bugs in all versions before October 2010). The problems are most 3453-- easily exposed by mutual recursion between plpgsql and sql functions. 3454-- 3455 3456create function recurse(float8) returns float8 as 3457$$ 3458begin 3459 if ($1 > 0) then 3460 return sql_recurse($1 - 1); 3461 else 3462 return $1; 3463 end if; 3464end; 3465$$ language plpgsql; 3466 3467-- "limit" is to prevent this from being inlined 3468create function sql_recurse(float8) returns float8 as 3469$$ select recurse($1) limit 1; $$ language sql; 3470 3471select recurse(10); 3472 3473create function error1(text) returns text language sql as 3474$$ SELECT relname::text FROM pg_class c WHERE c.oid = $1::regclass $$; 3475 3476create function error2(p_name_table text) returns text language plpgsql as $$ 3477begin 3478 return error1(p_name_table); 3479end$$; 3480 3481BEGIN; 3482create table public.stuffs (stuff text); 3483SAVEPOINT a; 3484select error2('nonexistent.stuffs'); 3485ROLLBACK TO a; 3486select error2('public.stuffs'); 3487rollback; 3488 3489drop function error2(p_name_table text); 3490drop function error1(text); 3491 3492-- Test for proper handling of cast-expression caching 3493 3494create function sql_to_date(integer) returns date as $$ 3495select $1::text::date 3496$$ language sql immutable strict; 3497 3498create cast (integer as date) with function sql_to_date(integer) as assignment; 3499 3500create function cast_invoker(integer) returns date as $$ 3501begin 3502 return $1; 3503end$$ language plpgsql; 3504 3505select cast_invoker(20150717); 3506select cast_invoker(20150718); -- second call crashed in pre-release 9.5 3507 3508begin; 3509select cast_invoker(20150717); 3510select cast_invoker(20150718); 3511savepoint s1; 3512select cast_invoker(20150718); 3513select cast_invoker(-1); -- fails 3514rollback to savepoint s1; 3515select cast_invoker(20150719); 3516select cast_invoker(20150720); 3517commit; 3518 3519drop function cast_invoker(integer); 3520drop function sql_to_date(integer) cascade; 3521 3522-- Test handling of cast cache inside DO blocks 3523-- (to check the original crash case, this must be a cast not previously 3524-- used in this session) 3525 3526begin; 3527do $$ declare x text[]; begin x := '{1.23, 4.56}'::numeric[]; end $$; 3528do $$ declare x text[]; begin x := '{1.23, 4.56}'::numeric[]; end $$; 3529end; 3530 3531-- Test for consistent reporting of error context 3532 3533create function fail() returns int language plpgsql as $$ 3534begin 3535 return 1/0; 3536end 3537$$; 3538 3539select fail(); 3540select fail(); 3541 3542drop function fail(); 3543 3544-- Test handling of string literals. 3545 3546set standard_conforming_strings = off; 3547 3548create or replace function strtest() returns text as $$ 3549begin 3550 raise notice 'foo\\bar\041baz'; 3551 return 'foo\\bar\041baz'; 3552end 3553$$ language plpgsql; 3554 3555select strtest(); 3556 3557create or replace function strtest() returns text as $$ 3558begin 3559 raise notice E'foo\\bar\041baz'; 3560 return E'foo\\bar\041baz'; 3561end 3562$$ language plpgsql; 3563 3564select strtest(); 3565 3566set standard_conforming_strings = on; 3567 3568create or replace function strtest() returns text as $$ 3569begin 3570 raise notice 'foo\\bar\041baz\'; 3571 return 'foo\\bar\041baz\'; 3572end 3573$$ language plpgsql; 3574 3575select strtest(); 3576 3577create or replace function strtest() returns text as $$ 3578begin 3579 raise notice E'foo\\bar\041baz'; 3580 return E'foo\\bar\041baz'; 3581end 3582$$ language plpgsql; 3583 3584select strtest(); 3585 3586drop function strtest(); 3587 3588-- Test anonymous code blocks. 3589 3590DO $$ 3591DECLARE r record; 3592BEGIN 3593 FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno 3594 LOOP 3595 RAISE NOTICE '%, %', r.roomno, r.comment; 3596 END LOOP; 3597END$$; 3598 3599-- these are to check syntax error reporting 3600DO LANGUAGE plpgsql $$begin return 1; end$$; 3601 3602DO $$ 3603DECLARE r record; 3604BEGIN 3605 FOR r IN SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno 3606 LOOP 3607 RAISE NOTICE '%, %', r.roomno, r.comment; 3608 END LOOP; 3609END$$; 3610 3611-- Check handling of errors thrown from/into anonymous code blocks. 3612do $outer$ 3613begin 3614 for i in 1..10 loop 3615 begin 3616 execute $ex$ 3617 do $$ 3618 declare x int = 0; 3619 begin 3620 x := 1 / x; 3621 end; 3622 $$; 3623 $ex$; 3624 exception when division_by_zero then 3625 raise notice 'caught division by zero'; 3626 end; 3627 end loop; 3628end; 3629$outer$; 3630 3631-- Check variable scoping -- a var is not available in its own or prior 3632-- default expressions. 3633 3634create function scope_test() returns int as $$ 3635declare x int := 42; 3636begin 3637 declare y int := x + 1; 3638 x int := x + 2; 3639 begin 3640 return x * 100 + y; 3641 end; 3642end; 3643$$ language plpgsql; 3644 3645select scope_test(); 3646 3647drop function scope_test(); 3648 3649-- Check handling of conflicts between plpgsql vars and table columns. 3650 3651set plpgsql.variable_conflict = error; 3652 3653create function conflict_test() returns setof int8_tbl as $$ 3654declare r record; 3655 q1 bigint := 42; 3656begin 3657 for r in select q1,q2 from int8_tbl loop 3658 return next r; 3659 end loop; 3660end; 3661$$ language plpgsql; 3662 3663select * from conflict_test(); 3664 3665create or replace function conflict_test() returns setof int8_tbl as $$ 3666#variable_conflict use_variable 3667declare r record; 3668 q1 bigint := 42; 3669begin 3670 for r in select q1,q2 from int8_tbl loop 3671 return next r; 3672 end loop; 3673end; 3674$$ language plpgsql; 3675 3676select * from conflict_test(); 3677 3678create or replace function conflict_test() returns setof int8_tbl as $$ 3679#variable_conflict use_column 3680declare r record; 3681 q1 bigint := 42; 3682begin 3683 for r in select q1,q2 from int8_tbl loop 3684 return next r; 3685 end loop; 3686end; 3687$$ language plpgsql; 3688 3689select * from conflict_test(); 3690 3691drop function conflict_test(); 3692 3693-- Check that an unreserved keyword can be used as a variable name 3694 3695create function unreserved_test() returns int as $$ 3696declare 3697 forward int := 21; 3698begin 3699 forward := forward * 2; 3700 return forward; 3701end 3702$$ language plpgsql; 3703 3704select unreserved_test(); 3705 3706create or replace function unreserved_test() returns int as $$ 3707declare 3708 return int := 42; 3709begin 3710 return := return + 1; 3711 return return; 3712end 3713$$ language plpgsql; 3714 3715select unreserved_test(); 3716 3717create or replace function unreserved_test() returns int as $$ 3718declare 3719 comment int := 21; 3720begin 3721 comment := comment * 2; 3722 comment on function unreserved_test() is 'this is a test'; 3723 return comment; 3724end 3725$$ language plpgsql; 3726 3727select unreserved_test(); 3728 3729select obj_description('unreserved_test()'::regprocedure, 'pg_proc'); 3730 3731drop function unreserved_test(); 3732 3733-- 3734-- Test FOREACH over arrays 3735-- 3736 3737create function foreach_test(anyarray) 3738returns void as $$ 3739declare x int; 3740begin 3741 foreach x in array $1 3742 loop 3743 raise notice '%', x; 3744 end loop; 3745 end; 3746$$ language plpgsql; 3747 3748select foreach_test(ARRAY[1,2,3,4]); 3749select foreach_test(ARRAY[[1,2],[3,4]]); 3750 3751create or replace function foreach_test(anyarray) 3752returns void as $$ 3753declare x int; 3754begin 3755 foreach x slice 1 in array $1 3756 loop 3757 raise notice '%', x; 3758 end loop; 3759 end; 3760$$ language plpgsql; 3761 3762-- should fail 3763select foreach_test(ARRAY[1,2,3,4]); 3764select foreach_test(ARRAY[[1,2],[3,4]]); 3765 3766create or replace function foreach_test(anyarray) 3767returns void as $$ 3768declare x int[]; 3769begin 3770 foreach x slice 1 in array $1 3771 loop 3772 raise notice '%', x; 3773 end loop; 3774 end; 3775$$ language plpgsql; 3776 3777select foreach_test(ARRAY[1,2,3,4]); 3778select foreach_test(ARRAY[[1,2],[3,4]]); 3779 3780-- higher level of slicing 3781create or replace function foreach_test(anyarray) 3782returns void as $$ 3783declare x int[]; 3784begin 3785 foreach x slice 2 in array $1 3786 loop 3787 raise notice '%', x; 3788 end loop; 3789 end; 3790$$ language plpgsql; 3791 3792-- should fail 3793select foreach_test(ARRAY[1,2,3,4]); 3794-- ok 3795select foreach_test(ARRAY[[1,2],[3,4]]); 3796select foreach_test(ARRAY[[[1,2]],[[3,4]]]); 3797 3798create type xy_tuple AS (x int, y int); 3799 3800-- iteration over array of records 3801create or replace function foreach_test(anyarray) 3802returns void as $$ 3803declare r record; 3804begin 3805 foreach r in array $1 3806 loop 3807 raise notice '%', r; 3808 end loop; 3809 end; 3810$$ language plpgsql; 3811 3812select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]); 3813select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]); 3814 3815create or replace function foreach_test(anyarray) 3816returns void as $$ 3817declare x int; y int; 3818begin 3819 foreach x, y in array $1 3820 loop 3821 raise notice 'x = %, y = %', x, y; 3822 end loop; 3823 end; 3824$$ language plpgsql; 3825 3826select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]); 3827select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]); 3828 3829-- slicing over array of composite types 3830create or replace function foreach_test(anyarray) 3831returns void as $$ 3832declare x xy_tuple[]; 3833begin 3834 foreach x slice 1 in array $1 3835 loop 3836 raise notice '%', x; 3837 end loop; 3838 end; 3839$$ language plpgsql; 3840 3841select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]); 3842select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]); 3843 3844drop function foreach_test(anyarray); 3845drop type xy_tuple; 3846 3847-- 3848-- Assorted tests for array subscript assignment 3849-- 3850 3851create temp table rtype (id int, ar text[]); 3852 3853create function arrayassign1() returns text[] language plpgsql as $$ 3854declare 3855 r record; 3856begin 3857 r := row(12, '{foo,bar,baz}')::rtype; 3858 r.ar[2] := 'replace'; 3859 return r.ar; 3860end$$; 3861 3862select arrayassign1(); 3863select arrayassign1(); -- try again to exercise internal caching 3864 3865create domain orderedarray as int[2] 3866 constraint sorted check (value[1] < value[2]); 3867 3868select '{1,2}'::orderedarray; 3869select '{2,1}'::orderedarray; -- fail 3870 3871create function testoa(x1 int, x2 int, x3 int) returns orderedarray 3872language plpgsql as $$ 3873declare res orderedarray; 3874begin 3875 res := array[x1, x2]; 3876 res[2] := x3; 3877 return res; 3878end$$; 3879 3880select testoa(1,2,3); 3881select testoa(1,2,3); -- try again to exercise internal caching 3882select testoa(2,1,3); -- fail at initial assign 3883select testoa(1,2,1); -- fail at update 3884 3885drop function arrayassign1(); 3886drop function testoa(x1 int, x2 int, x3 int); 3887 3888 3889-- 3890-- Test handling of expanded arrays 3891-- 3892 3893create function returns_rw_array(int) returns int[] 3894language plpgsql as $$ 3895 declare r int[]; 3896 begin r := array[$1, $1]; return r; end; 3897$$ stable; 3898 3899create function consumes_rw_array(int[]) returns int 3900language plpgsql as $$ 3901 begin return $1[1]; end; 3902$$ stable; 3903 3904select consumes_rw_array(returns_rw_array(42)); 3905 3906-- bug #14174 3907explain (verbose, costs off) 3908select i, a from 3909 (select returns_rw_array(1) as a offset 0) ss, 3910 lateral consumes_rw_array(a) i; 3911 3912select i, a from 3913 (select returns_rw_array(1) as a offset 0) ss, 3914 lateral consumes_rw_array(a) i; 3915 3916explain (verbose, costs off) 3917select consumes_rw_array(a), a from returns_rw_array(1) a; 3918 3919select consumes_rw_array(a), a from returns_rw_array(1) a; 3920 3921explain (verbose, costs off) 3922select consumes_rw_array(a), a from 3923 (values (returns_rw_array(1)), (returns_rw_array(2))) v(a); 3924 3925select consumes_rw_array(a), a from 3926 (values (returns_rw_array(1)), (returns_rw_array(2))) v(a); 3927 3928do $$ 3929declare a int[] := array[1,2]; 3930begin 3931 a := a || 3; 3932 raise notice 'a = %', a; 3933end$$; 3934 3935 3936-- 3937-- Test access to call stack 3938-- 3939 3940create function inner_func(int) 3941returns int as $$ 3942declare _context text; 3943begin 3944 get diagnostics _context = pg_context; 3945 raise notice '***%***', _context; 3946 -- lets do it again, just for fun.. 3947 get diagnostics _context = pg_context; 3948 raise notice '***%***', _context; 3949 raise notice 'lets make sure we didnt break anything'; 3950 return 2 * $1; 3951end; 3952$$ language plpgsql; 3953 3954create or replace function outer_func(int) 3955returns int as $$ 3956declare 3957 myresult int; 3958begin 3959 raise notice 'calling down into inner_func()'; 3960 myresult := inner_func($1); 3961 raise notice 'inner_func() done'; 3962 return myresult; 3963end; 3964$$ language plpgsql; 3965 3966create or replace function outer_outer_func(int) 3967returns int as $$ 3968declare 3969 myresult int; 3970begin 3971 raise notice 'calling down into outer_func()'; 3972 myresult := outer_func($1); 3973 raise notice 'outer_func() done'; 3974 return myresult; 3975end; 3976$$ language plpgsql; 3977 3978select outer_outer_func(10); 3979-- repeated call should to work 3980select outer_outer_func(20); 3981 3982drop function outer_outer_func(int); 3983drop function outer_func(int); 3984drop function inner_func(int); 3985 3986-- access to call stack from exception 3987create function inner_func(int) 3988returns int as $$ 3989declare 3990 _context text; 3991 sx int := 5; 3992begin 3993 begin 3994 perform sx / 0; 3995 exception 3996 when division_by_zero then 3997 get diagnostics _context = pg_context; 3998 raise notice '***%***', _context; 3999 end; 4000 4001 -- lets do it again, just for fun.. 4002 get diagnostics _context = pg_context; 4003 raise notice '***%***', _context; 4004 raise notice 'lets make sure we didnt break anything'; 4005 return 2 * $1; 4006end; 4007$$ language plpgsql; 4008 4009create or replace function outer_func(int) 4010returns int as $$ 4011declare 4012 myresult int; 4013begin 4014 raise notice 'calling down into inner_func()'; 4015 myresult := inner_func($1); 4016 raise notice 'inner_func() done'; 4017 return myresult; 4018end; 4019$$ language plpgsql; 4020 4021create or replace function outer_outer_func(int) 4022returns int as $$ 4023declare 4024 myresult int; 4025begin 4026 raise notice 'calling down into outer_func()'; 4027 myresult := outer_func($1); 4028 raise notice 'outer_func() done'; 4029 return myresult; 4030end; 4031$$ language plpgsql; 4032 4033select outer_outer_func(10); 4034-- repeated call should to work 4035select outer_outer_func(20); 4036 4037drop function outer_outer_func(int); 4038drop function outer_func(int); 4039drop function inner_func(int); 4040 4041-- 4042-- Test ASSERT 4043-- 4044 4045do $$ 4046begin 4047 assert 1=1; -- should succeed 4048end; 4049$$; 4050 4051do $$ 4052begin 4053 assert 1=0; -- should fail 4054end; 4055$$; 4056 4057do $$ 4058begin 4059 assert NULL; -- should fail 4060end; 4061$$; 4062 4063-- check controlling GUC 4064set plpgsql.check_asserts = off; 4065do $$ 4066begin 4067 assert 1=0; -- won't be tested 4068end; 4069$$; 4070reset plpgsql.check_asserts; 4071 4072-- test custom message 4073do $$ 4074declare var text := 'some value'; 4075begin 4076 assert 1=0, format('assertion failed, var = "%s"', var); 4077end; 4078$$; 4079 4080-- ensure assertions are not trapped by 'others' 4081do $$ 4082begin 4083 assert 1=0, 'unhandled assertion'; 4084exception when others then 4085 null; -- do nothing 4086end; 4087$$; 4088 4089-- Test use of plpgsql in a domain check constraint (cf. bug #14414) 4090 4091create function plpgsql_domain_check(val int) returns boolean as $$ 4092begin return val > 0; end 4093$$ language plpgsql immutable; 4094 4095create domain plpgsql_domain as integer check(plpgsql_domain_check(value)); 4096 4097do $$ 4098declare v_test plpgsql_domain; 4099begin 4100 v_test := 1; 4101end; 4102$$; 4103 4104do $$ 4105declare v_test plpgsql_domain := 1; 4106begin 4107 v_test := 0; -- fail 4108end; 4109$$; 4110 4111-- Test handling of expanded array passed to a domain constraint (bug #14472) 4112 4113create function plpgsql_arr_domain_check(val int[]) returns boolean as $$ 4114begin return val[1] > 0; end 4115$$ language plpgsql immutable; 4116 4117create domain plpgsql_arr_domain as int[] check(plpgsql_arr_domain_check(value)); 4118 4119do $$ 4120declare v_test plpgsql_arr_domain; 4121begin 4122 v_test := array[1]; 4123 v_test := v_test || 2; 4124end; 4125$$; 4126 4127do $$ 4128declare v_test plpgsql_arr_domain := array[1]; 4129begin 4130 v_test := 0 || v_test; -- fail 4131end; 4132$$; 4133 4134-- 4135-- test usage of transition tables in AFTER triggers 4136-- 4137 4138CREATE TABLE transition_table_base (id int PRIMARY KEY, val text); 4139 4140CREATE FUNCTION transition_table_base_ins_func() 4141 RETURNS trigger 4142 LANGUAGE plpgsql 4143AS $$ 4144DECLARE 4145 t text; 4146 l text; 4147BEGIN 4148 t = ''; 4149 FOR l IN EXECUTE 4150 $q$ 4151 EXPLAIN (TIMING off, COSTS off, VERBOSE on) 4152 SELECT * FROM newtable 4153 $q$ LOOP 4154 t = t || l || E'\n'; 4155 END LOOP; 4156 4157 RAISE INFO '%', t; 4158 RETURN new; 4159END; 4160$$; 4161 4162CREATE TRIGGER transition_table_base_ins_trig 4163 AFTER INSERT ON transition_table_base 4164 REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable 4165 FOR EACH STATEMENT 4166 EXECUTE PROCEDURE transition_table_base_ins_func(); 4167 4168CREATE TRIGGER transition_table_base_ins_trig 4169 AFTER INSERT ON transition_table_base 4170 REFERENCING NEW TABLE AS newtable 4171 FOR EACH STATEMENT 4172 EXECUTE PROCEDURE transition_table_base_ins_func(); 4173 4174INSERT INTO transition_table_base VALUES (1, 'One'), (2, 'Two'); 4175INSERT INTO transition_table_base VALUES (3, 'Three'), (4, 'Four'); 4176 4177CREATE OR REPLACE FUNCTION transition_table_base_upd_func() 4178 RETURNS trigger 4179 LANGUAGE plpgsql 4180AS $$ 4181DECLARE 4182 t text; 4183 l text; 4184BEGIN 4185 t = ''; 4186 FOR l IN EXECUTE 4187 $q$ 4188 EXPLAIN (TIMING off, COSTS off, VERBOSE on) 4189 SELECT * FROM oldtable ot FULL JOIN newtable nt USING (id) 4190 $q$ LOOP 4191 t = t || l || E'\n'; 4192 END LOOP; 4193 4194 RAISE INFO '%', t; 4195 RETURN new; 4196END; 4197$$; 4198 4199CREATE TRIGGER transition_table_base_upd_trig 4200 AFTER UPDATE ON transition_table_base 4201 REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable 4202 FOR EACH STATEMENT 4203 EXECUTE PROCEDURE transition_table_base_upd_func(); 4204 4205UPDATE transition_table_base 4206 SET val = '*' || val || '*' 4207 WHERE id BETWEEN 2 AND 3; 4208 4209CREATE TABLE transition_table_level1 4210( 4211 level1_no serial NOT NULL , 4212 level1_node_name varchar(255), 4213 PRIMARY KEY (level1_no) 4214) WITHOUT OIDS; 4215 4216CREATE TABLE transition_table_level2 4217( 4218 level2_no serial NOT NULL , 4219 parent_no int NOT NULL, 4220 level1_node_name varchar(255), 4221 PRIMARY KEY (level2_no) 4222) WITHOUT OIDS; 4223 4224CREATE TABLE transition_table_status 4225( 4226 level int NOT NULL, 4227 node_no int NOT NULL, 4228 status int, 4229 PRIMARY KEY (level, node_no) 4230) WITHOUT OIDS; 4231 4232CREATE FUNCTION transition_table_level1_ri_parent_del_func() 4233 RETURNS TRIGGER 4234 LANGUAGE plpgsql 4235AS $$ 4236 DECLARE n bigint; 4237 BEGIN 4238 PERFORM FROM p JOIN transition_table_level2 c ON c.parent_no = p.level1_no; 4239 IF FOUND THEN 4240 RAISE EXCEPTION 'RI error'; 4241 END IF; 4242 RETURN NULL; 4243 END; 4244$$; 4245 4246CREATE TRIGGER transition_table_level1_ri_parent_del_trigger 4247 AFTER DELETE ON transition_table_level1 4248 REFERENCING OLD TABLE AS p 4249 FOR EACH STATEMENT EXECUTE PROCEDURE 4250 transition_table_level1_ri_parent_del_func(); 4251 4252CREATE FUNCTION transition_table_level1_ri_parent_upd_func() 4253 RETURNS TRIGGER 4254 LANGUAGE plpgsql 4255AS $$ 4256 DECLARE 4257 x int; 4258 BEGIN 4259 WITH p AS (SELECT level1_no, sum(delta) cnt 4260 FROM (SELECT level1_no, 1 AS delta FROM i 4261 UNION ALL 4262 SELECT level1_no, -1 AS delta FROM d) w 4263 GROUP BY level1_no 4264 HAVING sum(delta) < 0) 4265 SELECT level1_no 4266 FROM p JOIN transition_table_level2 c ON c.parent_no = p.level1_no 4267 INTO x; 4268 IF FOUND THEN 4269 RAISE EXCEPTION 'RI error'; 4270 END IF; 4271 RETURN NULL; 4272 END; 4273$$; 4274 4275CREATE TRIGGER transition_table_level1_ri_parent_upd_trigger 4276 AFTER UPDATE ON transition_table_level1 4277 REFERENCING OLD TABLE AS d NEW TABLE AS i 4278 FOR EACH STATEMENT EXECUTE PROCEDURE 4279 transition_table_level1_ri_parent_upd_func(); 4280 4281CREATE FUNCTION transition_table_level2_ri_child_insupd_func() 4282 RETURNS TRIGGER 4283 LANGUAGE plpgsql 4284AS $$ 4285 BEGIN 4286 PERFORM FROM i 4287 LEFT JOIN transition_table_level1 p 4288 ON p.level1_no IS NOT NULL AND p.level1_no = i.parent_no 4289 WHERE p.level1_no IS NULL; 4290 IF FOUND THEN 4291 RAISE EXCEPTION 'RI error'; 4292 END IF; 4293 RETURN NULL; 4294 END; 4295$$; 4296 4297CREATE TRIGGER transition_table_level2_ri_child_ins_trigger 4298 AFTER INSERT ON transition_table_level2 4299 REFERENCING NEW TABLE AS i 4300 FOR EACH STATEMENT EXECUTE PROCEDURE 4301 transition_table_level2_ri_child_insupd_func(); 4302 4303CREATE TRIGGER transition_table_level2_ri_child_upd_trigger 4304 AFTER UPDATE ON transition_table_level2 4305 REFERENCING NEW TABLE AS i 4306 FOR EACH STATEMENT EXECUTE PROCEDURE 4307 transition_table_level2_ri_child_insupd_func(); 4308 4309-- create initial test data 4310INSERT INTO transition_table_level1 (level1_no) 4311 SELECT generate_series(1,200); 4312ANALYZE transition_table_level1; 4313 4314INSERT INTO transition_table_level2 (level2_no, parent_no) 4315 SELECT level2_no, level2_no / 50 + 1 AS parent_no 4316 FROM generate_series(1,9999) level2_no; 4317ANALYZE transition_table_level2; 4318 4319INSERT INTO transition_table_status (level, node_no, status) 4320 SELECT 1, level1_no, 0 FROM transition_table_level1; 4321 4322INSERT INTO transition_table_status (level, node_no, status) 4323 SELECT 2, level2_no, 0 FROM transition_table_level2; 4324ANALYZE transition_table_status; 4325 4326INSERT INTO transition_table_level1(level1_no) 4327 SELECT generate_series(201,1000); 4328ANALYZE transition_table_level1; 4329 4330-- behave reasonably if someone tries to modify a transition table 4331CREATE FUNCTION transition_table_level2_bad_usage_func() 4332 RETURNS TRIGGER 4333 LANGUAGE plpgsql 4334AS $$ 4335 BEGIN 4336 INSERT INTO dx VALUES (1000000, 1000000, 'x'); 4337 RETURN NULL; 4338 END; 4339$$; 4340 4341CREATE TRIGGER transition_table_level2_bad_usage_trigger 4342 AFTER DELETE ON transition_table_level2 4343 REFERENCING OLD TABLE AS dx 4344 FOR EACH STATEMENT EXECUTE PROCEDURE 4345 transition_table_level2_bad_usage_func(); 4346 4347DELETE FROM transition_table_level2 4348 WHERE level2_no BETWEEN 301 AND 305; 4349 4350DROP TRIGGER transition_table_level2_bad_usage_trigger 4351 ON transition_table_level2; 4352 4353-- attempt modifications which would break RI (should all fail) 4354DELETE FROM transition_table_level1 4355 WHERE level1_no = 25; 4356 4357UPDATE transition_table_level1 SET level1_no = -1 4358 WHERE level1_no = 30; 4359 4360INSERT INTO transition_table_level2 (level2_no, parent_no) 4361 VALUES (10000, 10000); 4362 4363UPDATE transition_table_level2 SET parent_no = 2000 4364 WHERE level2_no = 40; 4365 4366 4367-- attempt modifications which would not break RI (should all succeed) 4368DELETE FROM transition_table_level1 4369 WHERE level1_no BETWEEN 201 AND 1000; 4370 4371DELETE FROM transition_table_level1 4372 WHERE level1_no BETWEEN 100000000 AND 100000010; 4373 4374SELECT count(*) FROM transition_table_level1; 4375 4376DELETE FROM transition_table_level2 4377 WHERE level2_no BETWEEN 211 AND 220; 4378 4379SELECT count(*) FROM transition_table_level2; 4380 4381CREATE TABLE alter_table_under_transition_tables 4382( 4383 id int PRIMARY KEY, 4384 name text 4385); 4386 4387CREATE FUNCTION alter_table_under_transition_tables_upd_func() 4388 RETURNS TRIGGER 4389 LANGUAGE plpgsql 4390AS $$ 4391BEGIN 4392 RAISE WARNING 'old table = %, new table = %', 4393 (SELECT string_agg(id || '=' || name, ',') FROM d), 4394 (SELECT string_agg(id || '=' || name, ',') FROM i); 4395 RAISE NOTICE 'one = %', (SELECT 1 FROM alter_table_under_transition_tables LIMIT 1); 4396 RETURN NULL; 4397END; 4398$$; 4399 4400-- should fail, TRUNCATE is not compatible with transition tables 4401CREATE TRIGGER alter_table_under_transition_tables_upd_trigger 4402 AFTER TRUNCATE OR UPDATE ON alter_table_under_transition_tables 4403 REFERENCING OLD TABLE AS d NEW TABLE AS i 4404 FOR EACH STATEMENT EXECUTE PROCEDURE 4405 alter_table_under_transition_tables_upd_func(); 4406 4407-- should work 4408CREATE TRIGGER alter_table_under_transition_tables_upd_trigger 4409 AFTER UPDATE ON alter_table_under_transition_tables 4410 REFERENCING OLD TABLE AS d NEW TABLE AS i 4411 FOR EACH STATEMENT EXECUTE PROCEDURE 4412 alter_table_under_transition_tables_upd_func(); 4413 4414INSERT INTO alter_table_under_transition_tables 4415 VALUES (1, '1'), (2, '2'), (3, '3'); 4416UPDATE alter_table_under_transition_tables 4417 SET name = name || name; 4418 4419-- now change 'name' to an integer to see what happens... 4420ALTER TABLE alter_table_under_transition_tables 4421 ALTER COLUMN name TYPE int USING name::integer; 4422UPDATE alter_table_under_transition_tables 4423 SET name = (name::text || name::text)::integer; 4424 4425-- now drop column 'name' 4426ALTER TABLE alter_table_under_transition_tables 4427 DROP column name; 4428UPDATE alter_table_under_transition_tables 4429 SET id = id; 4430 4431-- 4432-- Test multiple reference to a transition table 4433-- 4434 4435CREATE TABLE multi_test (i int); 4436INSERT INTO multi_test VALUES (1); 4437 4438CREATE OR REPLACE FUNCTION multi_test_trig() RETURNS trigger 4439LANGUAGE plpgsql AS $$ 4440BEGIN 4441 RAISE NOTICE 'count = %', (SELECT COUNT(*) FROM new_test); 4442 RAISE NOTICE 'count union = %', 4443 (SELECT COUNT(*) 4444 FROM (SELECT * FROM new_test UNION ALL SELECT * FROM new_test) ss); 4445 RETURN NULL; 4446END$$; 4447 4448CREATE TRIGGER my_trigger AFTER UPDATE ON multi_test 4449 REFERENCING NEW TABLE AS new_test OLD TABLE as old_test 4450 FOR EACH STATEMENT EXECUTE PROCEDURE multi_test_trig(); 4451 4452UPDATE multi_test SET i = i; 4453 4454DROP TABLE multi_test; 4455DROP FUNCTION multi_test_trig(); 4456 4457-- 4458-- Check type parsing and record fetching from partitioned tables 4459-- 4460 4461CREATE TABLE partitioned_table (a int, b text) PARTITION BY LIST (a); 4462CREATE TABLE pt_part1 PARTITION OF partitioned_table FOR VALUES IN (1); 4463CREATE TABLE pt_part2 PARTITION OF partitioned_table FOR VALUES IN (2); 4464 4465INSERT INTO partitioned_table VALUES (1, 'Row 1'); 4466INSERT INTO partitioned_table VALUES (2, 'Row 2'); 4467 4468CREATE OR REPLACE FUNCTION get_from_partitioned_table(partitioned_table.a%type) 4469RETURNS partitioned_table AS $$ 4470DECLARE 4471 a_val partitioned_table.a%TYPE; 4472 result partitioned_table%ROWTYPE; 4473BEGIN 4474 a_val := $1; 4475 SELECT * INTO result FROM partitioned_table WHERE a = a_val; 4476 RETURN result; 4477END; $$ LANGUAGE plpgsql; 4478 4479SELECT * FROM get_from_partitioned_table(1) AS t; 4480 4481CREATE OR REPLACE FUNCTION list_partitioned_table() 4482RETURNS SETOF partitioned_table.a%TYPE AS $$ 4483DECLARE 4484 row partitioned_table%ROWTYPE; 4485 a_val partitioned_table.a%TYPE; 4486BEGIN 4487 FOR row IN SELECT * FROM partitioned_table ORDER BY a LOOP 4488 a_val := row.a; 4489 RETURN NEXT a_val; 4490 END LOOP; 4491 RETURN; 4492END; $$ LANGUAGE plpgsql; 4493 4494SELECT * FROM list_partitioned_table() AS t; 4495 4496-- 4497-- Check argument name is used instead of $n in error message 4498-- 4499CREATE FUNCTION fx(x WSlot) RETURNS void AS $$ 4500BEGIN 4501 GET DIAGNOSTICS x = ROW_COUNT; 4502 RETURN; 4503END; $$ LANGUAGE plpgsql; 4504