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