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