src/test/regress/sql/plpgsql.sql

   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
  25 create table Room (
  26     roomno      char(8),
  27     comment     text
  28 );
  29
  30 create unique index Room_rno on Room using btree (roomno bpchar_ops);
  31
  32
  33 create table WSlot (
  34     slotname    char(20),
  35     roomno      char(8),
  36     slotlink    char(20),
  37     backlink    char(20)
  38 );
  39
  40 create unique index WSlot_name on WSlot using btree (slotname bpchar_ops);
  41
  42
  43 create table PField (
  44     name        text,
  45     comment     text
  46 );
  47
  48 create unique index PField_name on PField using btree (name text_ops);
  49
  50
  51 create table PSlot (
  52     slotname    char(20),
  53     pfname      text,
  54     slotlink    char(20),
  55     backlink    char(20)
  56 );
  57
  58 create unique index PSlot_name on PSlot using btree (slotname bpchar_ops);
  59
  60
  61 create table PLine (
  62     slotname    char(20),
  63     phonenumber char(20),
  64     comment     text,
  65     backlink    char(20)
  66 );
  67
  68 create unique index PLine_name on PLine using btree (slotname bpchar_ops);
  69
  70
  71 create table Hub (
  72     name        char(14),
  73     comment     text,
  74     nslots      integer
  75 );
  76
  77 create unique index Hub_name on Hub using btree (name bpchar_ops);
  78
  79
  80 create table HSlot (
  81     slotname    char(20),
  82     hubname     char(14),
  83     slotno      integer,
  84     slotlink    char(20)
  85 );
  86
  87 create unique index HSlot_name on HSlot using btree (slotname bpchar_ops);
  88 create index HSlot_hubname on HSlot using btree (hubname bpchar_ops);
  89
  90
  91 create table System (
  92     name        text,
  93     comment     text
  94 );
  95
  96 create unique index System_name on System using btree (name text_ops);
  97
  98
  99 create table IFace (
 100     slotname    char(20),
 101     sysname     text,
 102     ifname      text,
 103     slotlink    char(20)
 104 );
 105
 106 create unique index IFace_name on IFace using btree (slotname bpchar_ops);
 107
 108
 109 create table PHone (
 110     slotname    char(20),
 111     comment     text,
 112     slotlink    char(20)
 113 );
 114
 115 create 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 -- ************************************************************
 130 create function tg_room_au() returns trigger as '
 131 begin
 132     if new.roomno != old.roomno then
 133         update WSlot set roomno = new.roomno where roomno = old.roomno;
 134     end if;
 135     return new;
 136 end;
 137 ' language plpgsql;
 138
 139 create 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 -- ************************************************************
 147 create function tg_room_ad() returns trigger as '
 148 begin
 149     delete from WSlot where roomno = old.roomno;
 150     return old;
 151 end;
 152 ' language plpgsql;
 153
 154 create 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 -- ************************************************************
 162 create function tg_wslot_biu() returns trigger as $$
 163 begin
 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;
 168 end;
 169 $$ language plpgsql;
 170
 171 create 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 -- ************************************************************
 179 create function tg_pfield_au() returns trigger as '
 180 begin
 181     if new.name != old.name then
 182         update PSlot set pfname = new.name where pfname = old.name;
 183     end if;
 184     return new;
 185 end;
 186 ' language plpgsql;
 187
 188 create 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 -- ************************************************************
 196 create function tg_pfield_ad() returns trigger as '
 197 begin
 198     delete from PSlot where pfname = old.name;
 199     return old;
 200 end;
 201 ' language plpgsql;
 202
 203 create 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 -- ************************************************************
 211 create function tg_pslot_biu() returns trigger as $proc$
 212 declare
 213     pfrec       record;
 214     ps          alias for new;
 215 begin
 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;
 221 end;
 222 $proc$ language plpgsql;
 223
 224 create 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 -- ************************************************************
 232 create function tg_system_au() returns trigger as '
 233 begin
 234     if new.name != old.name then
 235         update IFace set sysname = new.name where sysname = old.name;
 236     end if;
 237     return new;
 238 end;
 239 ' language plpgsql;
 240
 241 create 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 -- ************************************************************
 249 create function tg_iface_biu() returns trigger as $$
 250 declare
 251     sname       text;
 252     sysrec      record;
 253 begin
 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;
 266 end;
 267 $$ language plpgsql;
 268
 269 create 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 -- ************************************************************
 277 create function tg_hub_a() returns trigger as '
 278 declare
 279     hname       text;
 280     dummy       integer;
 281 begin
 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;
 297 end;
 298 ' language plpgsql;
 299
 300 create 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 -- ************************************************************
 307 create function tg_hub_adjustslots(hname bpchar,
 308                                    oldnslots integer,
 309                                    newnslots integer)
 310 returns integer as '
 311 begin
 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;
 324 end
 325 ' language plpgsql;
 326
 327 -- Test comments
 328 COMMENT ON FUNCTION tg_hub_adjustslots_wrong(bpchar, integer, integer) IS 'function with args';
 329 COMMENT ON FUNCTION tg_hub_adjustslots(bpchar, integer, integer) IS 'function with args';
 330 COMMENT 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 -- ************************************************************
 337 create function tg_hslot_biu() returns trigger as '
 338 declare
 339     sname       text;
 340     xname       HSlot.slotname%TYPE;
 341     hubrec      record;
 342 begin
 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;
 363 end;
 364 ' language plpgsql;
 365
 366 create 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 -- ************************************************************
 374 create function tg_hslot_bd() returns trigger as '
 375 declare
 376     hubrec      record;
 377 begin
 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'';
 386 end;
 387 ' language plpgsql;
 388
 389 create 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 -- ************************************************************
 397 create function tg_chkslotname() returns trigger as '
 398 begin
 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;
 403 end;
 404 ' language plpgsql;
 405
 406 create trigger tg_chkslotname before insert
 407     on PSlot for each row execute procedure tg_chkslotname('PS');
 408
 409 create trigger tg_chkslotname before insert
 410     on WSlot for each row execute procedure tg_chkslotname('WS');
 411
 412 create trigger tg_chkslotname before insert
 413     on PLine for each row execute procedure tg_chkslotname('PL');
 414
 415 create trigger tg_chkslotname before insert
 416     on IFace for each row execute procedure tg_chkslotname('IF');
 417
 418 create 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 -- ************************************************************
 426 create function tg_chkslotlink() returns trigger as '
 427 begin
 428     if new.slotlink isnull then
 429         new.slotlink := '''';
 430     end if;
 431     return new;
 432 end;
 433 ' language plpgsql;
 434
 435 create trigger tg_chkslotlink before insert or update
 436     on PSlot for each row execute procedure tg_chkslotlink();
 437
 438 create trigger tg_chkslotlink before insert or update
 439     on WSlot for each row execute procedure tg_chkslotlink();
 440
 441 create trigger tg_chkslotlink before insert or update
 442     on IFace for each row execute procedure tg_chkslotlink();
 443
 444 create trigger tg_chkslotlink before insert or update
 445     on HSlot for each row execute procedure tg_chkslotlink();
 446
 447 create 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 -- ************************************************************
 455 create function tg_chkbacklink() returns trigger as '
 456 begin
 457     if new.backlink isnull then
 458         new.backlink := '''';
 459     end if;
 460     return new;
 461 end;
 462 ' language plpgsql;
 463
 464 create trigger tg_chkbacklink before insert or update
 465     on PSlot for each row execute procedure tg_chkbacklink();
 466
 467 create trigger tg_chkbacklink before insert or update
 468     on WSlot for each row execute procedure tg_chkbacklink();
 469
 470 create 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 -- ************************************************************
 478 create function tg_pslot_bu() returns trigger as '
 479 begin
 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;
 496 end;
 497 ' language plpgsql;
 498
 499 create 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 -- ************************************************************
 507 create function tg_wslot_bu() returns trigger as '
 508 begin
 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;
 525 end;
 526 ' language plpgsql;
 527
 528 create 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 -- ************************************************************
 536 create function tg_pline_bu() returns trigger as '
 537 begin
 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;
 554 end;
 555 ' language plpgsql;
 556
 557 create 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 -- ************************************************************
 565 create function tg_iface_bu() returns trigger as '
 566 begin
 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;
 583 end;
 584 ' language plpgsql;
 585
 586 create 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 -- ************************************************************
 594 create function tg_hslot_bu() returns trigger as '
 595 begin
 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;
 612 end;
 613 ' language plpgsql;
 614
 615 create 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 -- ************************************************************
 623 create function tg_phone_bu() returns trigger as '
 624 begin
 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;
 639 end;
 640 ' language plpgsql;
 641
 642 create 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 -- ************************************************************
 650 create function tg_backlink_a() returns trigger as '
 651 declare
 652     dummy       integer;
 653 begin
 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;
 681 end;
 682 ' language plpgsql;
 683
 684
 685 create trigger tg_backlink_a after insert or update or delete
 686     on PSlot for each row execute procedure tg_backlink_a('PS');
 687
 688 create trigger tg_backlink_a after insert or update or delete
 689     on WSlot for each row execute procedure tg_backlink_a('WS');
 690
 691 create 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 -- ************************************************************
 699 create function tg_backlink_set(myname bpchar, blname bpchar)
 700 returns integer as '
 701 declare
 702     mytype      char(2);
 703     link        char(4);
 704     rec         record;
 705 begin
 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;
 747 end;
 748 ' language plpgsql;
 749
 750
 751 -- ************************************************************
 752 -- * Support function to clear out the backlink field if
 753 -- * it still points to specific slot
 754 -- ************************************************************
 755 create function tg_backlink_unset(bpchar, bpchar)
 756 returns integer as '
 757 declare
 758     myname      alias for $1;
 759     blname      alias for $2;
 760     mytype      char(2);
 761     rec         record;
 762 begin
 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;
 794 end
 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 -- ************************************************************
 802 create function tg_slotlink_a() returns trigger as '
 803 declare
 804     dummy       integer;
 805 begin
 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;
 833 end;
 834 ' language plpgsql;
 835
 836
 837 create trigger tg_slotlink_a after insert or update or delete
 838     on PSlot for each row execute procedure tg_slotlink_a('PS');
 839
 840 create trigger tg_slotlink_a after insert or update or delete
 841     on WSlot for each row execute procedure tg_slotlink_a('WS');
 842
 843 create trigger tg_slotlink_a after insert or update or delete
 844     on IFace for each row execute procedure tg_slotlink_a('IF');
 845
 846 create trigger tg_slotlink_a after insert or update or delete
 847     on HSlot for each row execute procedure tg_slotlink_a('HS');
 848
 849 create 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 -- ************************************************************
 857 create function tg_slotlink_set(bpchar, bpchar)
 858 returns integer as '
 859 declare
 860     myname      alias for $1;
 861     blname      alias for $2;
 862     mytype      char(2);
 863     link        char(4);
 864     rec         record;
 865 begin
 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;
 935 end;
 936 ' language plpgsql;
 937
 938
 939 -- ************************************************************
 940 -- * Support function to clear out the slotlink field if
 941 -- * it still points to specific slot
 942 -- ************************************************************
 943 create function tg_slotlink_unset(bpchar, bpchar)
 944 returns integer as '
 945 declare
 946     myname      alias for $1;
 947     blname      alias for $2;
 948     mytype      char(2);
 949     rec         record;
 950 begin
 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;
1002 end;
1003 ' language plpgsql;
1004
1005
1006 -- ************************************************************
1007 -- * Describe the backside of a patchfield slot
1008 -- ************************************************************
1009 create function pslot_backlink_view(bpchar)
1010 returns text as '
1011 <<outer>>
1012 declare
1013     rec         record;
1014     bltype      char(2);
1015     retval      text;
1016 begin
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;
1047 end;
1048 ' language plpgsql;
1049
1050
1051 -- ************************************************************
1052 -- * Describe the front of a patchfield slot
1053 -- ************************************************************
1054 create function pslot_slotlink_view(bpchar)
1055 returns text as '
1056 declare
1057     psrec       record;
1058     sltype      char(2);
1059     retval      text;
1060 begin
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;
1083 end;
1084 ' language plpgsql;
1085
1086
1087 -- ************************************************************
1088 -- * Describe the front of a wall connector slot
1089 -- ************************************************************
1090 create function wslot_slotlink_view(bpchar)
1091 returns text as '
1092 declare
1093     rec         record;
1094     sltype      char(2);
1095     retval      text;
1096 begin
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;
1133 end;
1134 ' language plpgsql;
1135
1136
1137
1138 -- ************************************************************
1139 -- * View of a patchfield describing backside and patches
1140 -- ************************************************************
1141 create 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 --
1150 insert into Room values ('001', 'Entrance');
1151 insert into Room values ('002', 'Office');
1152 insert into Room values ('003', 'Office');
1153 insert into Room values ('004', 'Technical');
1154 insert into Room values ('101', 'Office');
1155 insert into Room values ('102', 'Conference');
1156 insert into Room values ('103', 'Restroom');
1157 insert into Room values ('104', 'Technical');
1158 insert into Room values ('105', 'Office');
1159 insert into Room values ('106', 'Office');
1160
1161 --
1162 -- Second we install the wall connectors
1163 --
1164 insert into WSlot values ('WS.001.1a', '001', '', '');
1165 insert into WSlot values ('WS.001.1b', '001', '', '');
1166 insert into WSlot values ('WS.001.2a', '001', '', '');
1167 insert into WSlot values ('WS.001.2b', '001', '', '');
1168 insert into WSlot values ('WS.001.3a', '001', '', '');
1169 insert into WSlot values ('WS.001.3b', '001', '', '');
1170
1171 insert into WSlot values ('WS.002.1a', '002', '', '');
1172 insert into WSlot values ('WS.002.1b', '002', '', '');
1173 insert into WSlot values ('WS.002.2a', '002', '', '');
1174 insert into WSlot values ('WS.002.2b', '002', '', '');
1175 insert into WSlot values ('WS.002.3a', '002', '', '');
1176 insert into WSlot values ('WS.002.3b', '002', '', '');
1177
1178 insert into WSlot values ('WS.003.1a', '003', '', '');
1179 insert into WSlot values ('WS.003.1b', '003', '', '');
1180 insert into WSlot values ('WS.003.2a', '003', '', '');
1181 insert into WSlot values ('WS.003.2b', '003', '', '');
1182 insert into WSlot values ('WS.003.3a', '003', '', '');
1183 insert into WSlot values ('WS.003.3b', '003', '', '');
1184
1185 insert into WSlot values ('WS.101.1a', '101', '', '');
1186 insert into WSlot values ('WS.101.1b', '101', '', '');
1187 insert into WSlot values ('WS.101.2a', '101', '', '');
1188 insert into WSlot values ('WS.101.2b', '101', '', '');
1189 insert into WSlot values ('WS.101.3a', '101', '', '');
1190 insert into WSlot values ('WS.101.3b', '101', '', '');
1191
1192 insert into WSlot values ('WS.102.1a', '102', '', '');
1193 insert into WSlot values ('WS.102.1b', '102', '', '');
1194 insert into WSlot values ('WS.102.2a', '102', '', '');
1195 insert into WSlot values ('WS.102.2b', '102', '', '');
1196 insert into WSlot values ('WS.102.3a', '102', '', '');
1197 insert into WSlot values ('WS.102.3b', '102', '', '');
1198
1199 insert into WSlot values ('WS.105.1a', '105', '', '');
1200 insert into WSlot values ('WS.105.1b', '105', '', '');
1201 insert into WSlot values ('WS.105.2a', '105', '', '');
1202 insert into WSlot values ('WS.105.2b', '105', '', '');
1203 insert into WSlot values ('WS.105.3a', '105', '', '');
1204 insert into WSlot values ('WS.105.3b', '105', '', '');
1205
1206 insert into WSlot values ('WS.106.1a', '106', '', '');
1207 insert into WSlot values ('WS.106.1b', '106', '', '');
1208 insert into WSlot values ('WS.106.2a', '106', '', '');
1209 insert into WSlot values ('WS.106.2b', '106', '', '');
1210 insert into WSlot values ('WS.106.3a', '106', '', '');
1211 insert into WSlot values ('WS.106.3b', '106', '', '');
1212
1213 --
1214 -- Now create the patch fields and their slots
1215 --
1216 insert 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 --
1221 insert into PSlot values ('PS.base.a1', 'PF0_1', '', '');
1222 insert into PSlot values ('PS.base.a2', 'PF0_1', '', '');
1223 insert into PSlot values ('PS.base.a3', 'PF0_1', '', '');
1224 insert into PSlot values ('PS.base.a4', 'PF0_1', '', '');
1225 insert into PSlot values ('PS.base.a5', 'PF0_1', '', '');
1226 insert into PSlot values ('PS.base.a6', 'PF0_1', '', '');
1227
1228 --
1229 -- These are already wired to the wall connectors
1230 --
1231 insert into PSlot values ('PS.base.b1', 'PF0_1', '', 'WS.002.1a');
1232 insert into PSlot values ('PS.base.b2', 'PF0_1', '', 'WS.002.1b');
1233 insert into PSlot values ('PS.base.b3', 'PF0_1', '', 'WS.002.2a');
1234 insert into PSlot values ('PS.base.b4', 'PF0_1', '', 'WS.002.2b');
1235 insert into PSlot values ('PS.base.b5', 'PF0_1', '', 'WS.002.3a');
1236 insert into PSlot values ('PS.base.b6', 'PF0_1', '', 'WS.002.3b');
1237
1238 insert into PSlot values ('PS.base.c1', 'PF0_1', '', 'WS.003.1a');
1239 insert into PSlot values ('PS.base.c2', 'PF0_1', '', 'WS.003.1b');
1240 insert into PSlot values ('PS.base.c3', 'PF0_1', '', 'WS.003.2a');
1241 insert into PSlot values ('PS.base.c4', 'PF0_1', '', 'WS.003.2b');
1242 insert into PSlot values ('PS.base.c5', 'PF0_1', '', 'WS.003.3a');
1243 insert 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 --
1249 insert into PField values ('PF0_X', 'Phonelines basement');
1250
1251 insert into PSlot values ('PS.base.ta1', 'PF0_X', '', '');
1252 insert into PSlot values ('PS.base.ta2', 'PF0_X', '', '');
1253 insert into PSlot values ('PS.base.ta3', 'PF0_X', '', '');
1254 insert into PSlot values ('PS.base.ta4', 'PF0_X', '', '');
1255 insert into PSlot values ('PS.base.ta5', 'PF0_X', '', '');
1256 insert into PSlot values ('PS.base.ta6', 'PF0_X', '', '');
1257
1258 insert into PSlot values ('PS.base.tb1', 'PF0_X', '', '');
1259 insert into PSlot values ('PS.base.tb2', 'PF0_X', '', '');
1260 insert into PSlot values ('PS.base.tb3', 'PF0_X', '', '');
1261 insert into PSlot values ('PS.base.tb4', 'PF0_X', '', '');
1262 insert into PSlot values ('PS.base.tb5', 'PF0_X', '', '');
1263 insert into PSlot values ('PS.base.tb6', 'PF0_X', '', '');
1264
1265 insert into PField values ('PF1_1', 'Wallslots first floor');
1266
1267 insert into PSlot values ('PS.first.a1', 'PF1_1', '', 'WS.101.1a');
1268 insert into PSlot values ('PS.first.a2', 'PF1_1', '', 'WS.101.1b');
1269 insert into PSlot values ('PS.first.a3', 'PF1_1', '', 'WS.101.2a');
1270 insert into PSlot values ('PS.first.a4', 'PF1_1', '', 'WS.101.2b');
1271 insert into PSlot values ('PS.first.a5', 'PF1_1', '', 'WS.101.3a');
1272 insert into PSlot values ('PS.first.a6', 'PF1_1', '', 'WS.101.3b');
1273
1274 insert into PSlot values ('PS.first.b1', 'PF1_1', '', 'WS.102.1a');
1275 insert into PSlot values ('PS.first.b2', 'PF1_1', '', 'WS.102.1b');
1276 insert into PSlot values ('PS.first.b3', 'PF1_1', '', 'WS.102.2a');
1277 insert into PSlot values ('PS.first.b4', 'PF1_1', '', 'WS.102.2b');
1278 insert into PSlot values ('PS.first.b5', 'PF1_1', '', 'WS.102.3a');
1279 insert into PSlot values ('PS.first.b6', 'PF1_1', '', 'WS.102.3b');
1280
1281 insert into PSlot values ('PS.first.c1', 'PF1_1', '', 'WS.105.1a');
1282 insert into PSlot values ('PS.first.c2', 'PF1_1', '', 'WS.105.1b');
1283 insert into PSlot values ('PS.first.c3', 'PF1_1', '', 'WS.105.2a');
1284 insert into PSlot values ('PS.first.c4', 'PF1_1', '', 'WS.105.2b');
1285 insert into PSlot values ('PS.first.c5', 'PF1_1', '', 'WS.105.3a');
1286 insert into PSlot values ('PS.first.c6', 'PF1_1', '', 'WS.105.3b');
1287
1288 insert into PSlot values ('PS.first.d1', 'PF1_1', '', 'WS.106.1a');
1289 insert into PSlot values ('PS.first.d2', 'PF1_1', '', 'WS.106.1b');
1290 insert into PSlot values ('PS.first.d3', 'PF1_1', '', 'WS.106.2a');
1291 insert into PSlot values ('PS.first.d4', 'PF1_1', '', 'WS.106.2b');
1292 insert into PSlot values ('PS.first.d5', 'PF1_1', '', 'WS.106.3a');
1293 insert 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 --
1300 update PSlot set backlink = 'WS.001.1a' where slotname = 'PS.base.a1';
1301 update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a3';
1302 select * from WSlot where roomno = '001' order by slotname;
1303 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1304 update PSlot set backlink = 'WS.001.2a' where slotname = 'PS.base.a3';
1305 select * from WSlot where roomno = '001' order by slotname;
1306 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1307 update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a2';
1308 select * from WSlot where roomno = '001' order by slotname;
1309 select * 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 --
1316 update WSlot set backlink = 'PS.base.a4' where slotname = 'WS.001.2b';
1317 update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3a';
1318 select * from WSlot where roomno = '001' order by slotname;
1319 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1320 update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3b';
1321 select * from WSlot where roomno = '001' order by slotname;
1322 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1323 update WSlot set backlink = 'PS.base.a5' where slotname = 'WS.001.3a';
1324 select * from WSlot where roomno = '001' order by slotname;
1325 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1326
1327 insert into PField values ('PF1_2', 'Phonelines first floor');
1328
1329 insert into PSlot values ('PS.first.ta1', 'PF1_2', '', '');
1330 insert into PSlot values ('PS.first.ta2', 'PF1_2', '', '');
1331 insert into PSlot values ('PS.first.ta3', 'PF1_2', '', '');
1332 insert into PSlot values ('PS.first.ta4', 'PF1_2', '', '');
1333 insert into PSlot values ('PS.first.ta5', 'PF1_2', '', '');
1334 insert into PSlot values ('PS.first.ta6', 'PF1_2', '', '');
1335
1336 insert into PSlot values ('PS.first.tb1', 'PF1_2', '', '');
1337 insert into PSlot values ('PS.first.tb2', 'PF1_2', '', '');
1338 insert into PSlot values ('PS.first.tb3', 'PF1_2', '', '');
1339 insert into PSlot values ('PS.first.tb4', 'PF1_2', '', '');
1340 insert into PSlot values ('PS.first.tb5', 'PF1_2', '', '');
1341 insert into PSlot values ('PS.first.tb6', 'PF1_2', '', '');
1342
1343 --
1344 -- Fix the wrong name for patchfield PF0_2
1345 --
1346 update PField set name = 'PF0_2' where name = 'PF0_X';
1347
1348 select * from PSlot order by slotname;
1349 select * from WSlot order by slotname;
1350
1351 --
1352 -- Install the central phone system and create the phone numbers.
1353 -- They are weired on insert to the patchfields. Again the
1354 -- triggers automatically tell the PSlots to update their
1355 -- backlink field.
1356 --
1357 insert into PLine values ('PL.001', '-0', 'Central call', 'PS.base.ta1');
1358 insert into PLine values ('PL.002', '-101', '', 'PS.base.ta2');
1359 insert into PLine values ('PL.003', '-102', '', 'PS.base.ta3');
1360 insert into PLine values ('PL.004', '-103', '', 'PS.base.ta5');
1361 insert into PLine values ('PL.005', '-104', '', 'PS.base.ta6');
1362 insert into PLine values ('PL.006', '-106', '', 'PS.base.tb2');
1363 insert into PLine values ('PL.007', '-108', '', 'PS.base.tb3');
1364 insert into PLine values ('PL.008', '-109', '', 'PS.base.tb4');
1365 insert into PLine values ('PL.009', '-121', '', 'PS.base.tb5');
1366 insert into PLine values ('PL.010', '-122', '', 'PS.base.tb6');
1367 insert into PLine values ('PL.015', '-134', '', 'PS.first.ta1');
1368 insert into PLine values ('PL.016', '-137', '', 'PS.first.ta3');
1369 insert into PLine values ('PL.017', '-139', '', 'PS.first.ta4');
1370 insert into PLine values ('PL.018', '-362', '', 'PS.first.tb1');
1371 insert into PLine values ('PL.019', '-363', '', 'PS.first.tb2');
1372 insert into PLine values ('PL.020', '-364', '', 'PS.first.tb3');
1373 insert into PLine values ('PL.021', '-365', '', 'PS.first.tb5');
1374 insert into PLine values ('PL.022', '-367', '', 'PS.first.tb6');
1375 insert into PLine values ('PL.028', '-501', 'Fax entrance', 'PS.base.ta2');
1376 insert 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 --
1382 insert into PHone values ('PH.hc001', 'Hicom standard', 'WS.001.1a');
1383 update PSlot set slotlink = 'PS.base.ta1' where slotname = 'PS.base.a1';
1384 insert into PHone values ('PH.hc002', 'Hicom standard', 'WS.002.1a');
1385 update PSlot set slotlink = 'PS.base.ta5' where slotname = 'PS.base.b1';
1386 insert into PHone values ('PH.hc003', 'Hicom standard', 'WS.002.2a');
1387 update PSlot set slotlink = 'PS.base.tb2' where slotname = 'PS.base.b3';
1388 insert into PHone values ('PH.fax001', 'Canon fax', 'WS.001.2a');
1389 update 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 --
1395 insert into Hub values ('base.hub1', 'Patchfield PF0_1 hub', 16);
1396 insert into System values ('orion', 'PC');
1397 insert into IFace values ('IF', 'orion', 'eth0', 'WS.002.1b');
1398 update PSlot set slotlink = 'HS.base.hub1.1' where slotname = 'PS.base.b2';
1399
1400 --
1401 -- Now we take a look at the patchfield
1402 --
1403 select * from PField_v1 where pfname = 'PF0_1' order by slotname;
1404 select * from PField_v1 where pfname = 'PF0_2' order by slotname;
1405
1406 --
1407 -- Finally we want errors
1408 --
1409 insert into PField values ('PF1_1', 'should fail due to unique index');
1410 update PSlot set backlink = 'WS.not.there' where slotname = 'PS.base.a1';
1411 update PSlot set backlink = 'XX.illegal' where slotname = 'PS.base.a1';
1412 update PSlot set slotlink = 'PS.not.there' where slotname = 'PS.base.a1';
1413 update PSlot set slotlink = 'XX.illegal' where slotname = 'PS.base.a1';
1414 insert into HSlot values ('HS', 'base.hub1', 1, '');
1415 insert into HSlot values ('HS', 'base.hub1', 20, '');
1416 delete from HSlot;
1417 insert into IFace values ('IF', 'notthere', 'eth0', '');
1418 insert 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 --
1429 CREATE FUNCTION recursion_test(int,int) RETURNS text AS '
1430 DECLARE rslt text;
1431 BEGIN
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;
1438 END;' LANGUAGE plpgsql;
1439
1440 SELECT recursion_test(4,3);
1441
1442 --
1443 -- Test the FOUND magic variable
1444 --
1445 CREATE TABLE found_test_tbl (a int);
1446
1447 create 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
1483 select test_found();
1484 select * from found_test_tbl;
1485
1486 --
1487 -- Test set-returning functions for PL/pgSQL
1488 --
1489
1490 create function test_table_func_rec() returns setof found_test_tbl as '
1491 DECLARE
1492         rec RECORD;
1493 BEGIN
1494         FOR rec IN select * from found_test_tbl LOOP
1495                 RETURN NEXT rec;
1496         END LOOP;
1497         RETURN;
1498 END;' language plpgsql;
1499
1500 select * from test_table_func_rec();
1501
1502 create function test_table_func_row() returns setof found_test_tbl as '
1503 DECLARE
1504         row found_test_tbl%ROWTYPE;
1505 BEGIN
1506         FOR row IN select * from found_test_tbl LOOP
1507                 RETURN NEXT row;
1508         END LOOP;
1509         RETURN;
1510 END;' language plpgsql;
1511
1512 select * from test_table_func_row();
1513
1514 create function test_ret_set_scalar(int,int) returns setof int as '
1515 DECLARE
1516         i int;
1517 BEGIN
1518         FOR i IN $1 .. $2 LOOP
1519                 RETURN NEXT i + 1;
1520         END LOOP;
1521         RETURN;
1522 END;' language plpgsql;
1523
1524 select * from test_ret_set_scalar(1,10);
1525
1526 create function test_ret_set_rec_dyn(int) returns setof record as '
1527 DECLARE
1528         retval RECORD;
1529 BEGIN
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;
1540 END;' language plpgsql;
1541
1542 SELECT * FROM test_ret_set_rec_dyn(1500) AS (a int, b int, c int);
1543 SELECT * FROM test_ret_set_rec_dyn(5) AS (a int, b numeric, c text);
1544
1545 create function test_ret_rec_dyn(int) returns record as '
1546 DECLARE
1547         retval RECORD;
1548 BEGIN
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;
1556 END;' language plpgsql;
1557
1558 SELECT * FROM test_ret_rec_dyn(1500) AS (a int, b int, c int);
1559 SELECT * 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:
1567 create function f1(in i int, out j int) returns int as $$
1568 begin
1569   return i+1;
1570 end$$ language plpgsql;
1571
1572 create function f1(in i int, out j int) as $$
1573 begin
1574   j := i+1;
1575   return;
1576 end$$ language plpgsql;
1577
1578 select f1(42);
1579 select * from f1(42);
1580
1581 create or replace function f1(inout i int) as $$
1582 begin
1583   i := i+1;
1584 end$$ language plpgsql;
1585
1586 select f1(42);
1587 select * from f1(42);
1588
1589 drop function f1(int);
1590
1591 create function f1(in i int, out j int) returns setof int as $$
1592 begin
1593   j := i+1;
1594   return next;
1595   j := i+2;
1596   return next;
1597   return;
1598 end$$ language plpgsql;
1599
1600 select * from f1(42);
1601
1602 drop function f1(int);
1603
1604 create function f1(in i int, out j int, out k text) as $$
1605 begin
1606   j := i;
1607   j := j+1;
1608   k := 'foo';
1609 end$$ language plpgsql;
1610
1611 select f1(42);
1612 select * from f1(42);
1613
1614 drop function f1(int);
1615
1616 create function f1(in i int, out j int, out k text) returns setof record as $$
1617 begin
1618   j := i+1;
1619   k := 'foo';
1620   return next;
1621   j := j+1;
1622   k := 'foot';
1623   return next;
1624 end$$ language plpgsql;
1625
1626 select * from f1(42);
1627
1628 drop function f1(int);
1629
1630 create function duplic(in i anyelement, out j anyelement, out k anyarray) as $$
1631 begin
1632   j := i;
1633   k := array[j,j];
1634   return;
1635 end$$ language plpgsql;
1636
1637 select * from duplic(42);
1638 select * from duplic('foo'::text);
1639
1640 drop function duplic(anyelement);
1641
1642 --
1643 -- test PERFORM
1644 --
1645
1646 create table perform_test (
1647         a       INT,
1648         b       INT
1649 );
1650
1651 create function simple_func(int) returns boolean as '
1652 BEGIN
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;
1659 END;' language plpgsql;
1660
1661 create function perform_test_func() returns void as '
1662 BEGIN
1663         IF FOUND then
1664                 INSERT INTO perform_test VALUES (100, 100);
1665         END IF;
1666
1667         PERFORM simple_func(5);
1668
1669         IF FOUND then
1670                 INSERT INTO perform_test VALUES (100, 100);
1671         END IF;
1672
1673         PERFORM simple_func(50);
1674
1675         IF FOUND then
1676                 INSERT INTO perform_test VALUES (100, 100);
1677         END IF;
1678
1679         RETURN;
1680 END;' language plpgsql;
1681
1682 SELECT perform_test_func();
1683 SELECT * FROM perform_test;
1684
1685 drop table perform_test;
1686
1687 --
1688 -- Test error trapping
1689 --
1690
1691 create function trap_zero_divide(int) returns int as $$
1692 declare x int;
1693         sx smallint;
1694 begin
1695         begin   -- start a subtransaction
1696                 raise notice 'should see this';
1697                 x := 100 / $1;
1698                 raise notice 'should see this only if % <> 0', $1;
1699                 sx := $1;
1700                 raise notice 'should see this only if % fits in smallint', $1;
1701                 if $1 < 0 then
1702                         raise exception '% is less than zero', $1;
1703                 end if;
1704         exception
1705                 when division_by_zero then
1706                         raise notice 'caught division_by_zero';
1707                         x := -1;
1708                 when NUMERIC_VALUE_OUT_OF_RANGE then
1709                         raise notice 'caught numeric_value_out_of_range';
1710                         x := -2;
1711         end;
1712         return x;
1713 end$$ language plpgsql;
1714
1715 select trap_zero_divide(50);
1716 select trap_zero_divide(0);
1717 select trap_zero_divide(100000);
1718 select trap_zero_divide(-100);
1719
1720 create function trap_matching_test(int) returns int as $$
1721 declare x int;
1722         sx smallint;
1723         y int;
1724 begin
1725         begin   -- start a subtransaction
1726                 x := 100 / $1;
1727                 sx := $1;
1728                 select into y unique1 from tenk1 where unique2 =
1729                         (select unique2 from tenk1 b where ten = $1);
1730         exception
1731                 when data_exception then  -- category match
1732                         raise notice 'caught data_exception';
1733                         x := -1;
1734                 when NUMERIC_VALUE_OUT_OF_RANGE OR CARDINALITY_VIOLATION then
1735                         raise notice 'caught numeric_value_out_of_range or cardinality_violation';
1736                         x := -2;
1737         end;
1738         return x;
1739 end$$ language plpgsql;
1740
1741 select trap_matching_test(50);
1742 select trap_matching_test(0);
1743 select trap_matching_test(100000);
1744 select trap_matching_test(1);
1745
1746 create temp table foo (f1 int);
1747
1748 create function blockme() returns int as $$
1749 declare x int;
1750 begin
1751   x := 1;
1752   insert into foo values(x);
1753   begin
1754     x := x + 1;
1755     insert into foo values(x);
1756     -- we assume this will take longer than 2 seconds:
1757     select count(*) into x from tenk1 a, tenk1 b, tenk1 c;
1758   exception
1759     when others then
1760       raise notice 'caught others?';
1761       return -1;
1762     when query_canceled then
1763       raise notice 'nyeah nyeah, can''t stop me';
1764       x := x * 10;
1765   end;
1766   insert into foo values(x);
1767   return x;
1768 end$$ language plpgsql;
1769
1770 set statement_timeout to 2000;
1771
1772 select blockme();
1773
1774 reset statement_timeout;
1775
1776 select * from foo;
1777
1778 drop table foo;
1779
1780 -- Test for pass-by-ref values being stored in proper context
1781 create function test_variable_storage() returns text as $$
1782 declare x text;
1783 begin
1784   x := '1234';
1785   begin
1786     x := x || '5678';
1787     -- force error inside subtransaction SPI context
1788     perform trap_zero_divide(-100);
1789   exception
1790     when others then
1791       x := x || '9012';
1792   end;
1793   return x;
1794 end$$ language plpgsql;
1795
1796 select test_variable_storage();
1797
1798 --
1799 -- test foreign key error trapping
1800 --
1801
1802 create temp table master(f1 int primary key);
1803
1804 create temp table slave(f1 int references master deferrable);
1805
1806 insert into master values(1);
1807 insert into slave values(1);
1808 insert into slave values(2);    -- fails
1809
1810 create function trap_foreign_key(int) returns int as $$
1811 begin
1812         begin   -- start a subtransaction
1813                 insert into slave values($1);
1814         exception
1815                 when foreign_key_violation then
1816                         raise notice 'caught foreign_key_violation';
1817                         return 0;
1818         end;
1819         return 1;
1820 end$$ language plpgsql;
1821
1822 create function trap_foreign_key_2() returns int as $$
1823 begin
1824         begin   -- start a subtransaction
1825                 set constraints all immediate;
1826         exception
1827                 when foreign_key_violation then
1828                         raise notice 'caught foreign_key_violation';
1829                         return 0;
1830         end;
1831         return 1;
1832 end$$ language plpgsql;
1833
1834 select trap_foreign_key(1);
1835 select trap_foreign_key(2);     -- detects FK violation
1836
1837 begin;
1838   set constraints all deferred;
1839   select trap_foreign_key(2);   -- should not detect FK violation
1840   savepoint x;
1841     set constraints all immediate; -- fails
1842   rollback to x;
1843   select trap_foreign_key_2();  -- detects FK violation
1844 commit;                         -- still fails
1845
1846 drop function trap_foreign_key(int);
1847 drop function trap_foreign_key_2();
1848
1849 --
1850 -- Test proper snapshot handling in simple expressions
1851 --
1852
1853 create temp table users(login text, id serial);
1854
1855 create function sp_id_user(a_login text) returns int as $$
1856 declare x int;
1857 begin
1858   select into x id from users where login = a_login;
1859   if found then return x; end if;
1860   return 0;
1861 end$$ language plpgsql stable;
1862
1863 insert into users values('user1');
1864
1865 select sp_id_user('user1');
1866 select sp_id_user('userx');
1867
1868 create function sp_add_user(a_login text) returns int as $$
1869 declare my_id_user int;
1870 begin
1871   my_id_user = sp_id_user( a_login );
1872   IF  my_id_user > 0 THEN
1873     RETURN -1;  -- error code for existing user
1874   END IF;
1875   INSERT INTO users ( login ) VALUES ( a_login );
1876   my_id_user = sp_id_user( a_login );
1877   IF  my_id_user = 0 THEN
1878     RETURN -2;  -- error code for insertion failure
1879   END IF;
1880   RETURN my_id_user;
1881 end$$ language plpgsql;
1882
1883 select sp_add_user('user1');
1884 select sp_add_user('user2');
1885 select sp_add_user('user2');
1886 select sp_add_user('user3');
1887 select sp_add_user('user3');
1888
1889 drop function sp_add_user(text);
1890 drop function sp_id_user(text);
1891
1892 --
1893 -- tests for refcursors
1894 --
1895 create table rc_test (a int, b int);
1896 copy rc_test from stdin;
1897 5       10
1898 50      100
1899 500     1000
1900 \.
1901
1902 create function return_refcursor(rc refcursor) returns refcursor as $$
1903 begin
1904     open rc for select a from rc_test;
1905     return rc;
1906 end
1907 $$ language plpgsql;
1908
1909 create function refcursor_test1(refcursor) returns refcursor as $$
1910 begin
1911     perform return_refcursor($1);
1912     return $1;
1913 end
1914 $$ language plpgsql;
1915
1916 begin;
1917
1918 select refcursor_test1('test1');
1919 fetch next in test1;
1920
1921 select refcursor_test1('test2');
1922 fetch all from test2;
1923
1924 commit;
1925
1926 -- should fail
1927 fetch next from test1;
1928
1929 create function refcursor_test2(int, int) returns boolean as $$
1930 declare
1931     c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
1932     nonsense record;
1933 begin
1934     open c1($1, $2);
1935     fetch c1 into nonsense;
1936     close c1;
1937     if found then
1938         return true;
1939     else
1940         return false;
1941     end if;
1942 end
1943 $$ language plpgsql;
1944
1945 select refcursor_test2(20000, 20000) as "Should be false",
1946        refcursor_test2(20, 20) as "Should be true";
1947
1948 --
1949 -- tests for "raise" processing
1950 --
1951 create function raise_test1(int) returns int as $$
1952 begin
1953     raise notice 'This message has too many parameters!', $1;
1954     return $1;
1955 end;
1956 $$ language plpgsql;
1957
1958 select raise_test1(5);
1959
1960 create function raise_test2(int) returns int as $$
1961 begin
1962     raise notice 'This message has too few parameters: %, %, %', $1, $1;
1963     return $1;
1964 end;
1965 $$ language plpgsql;
1966
1967 select raise_test2(10);
1968
1969 --
1970 -- reject function definitions that contain malformed SQL queries at
1971 -- compile-time, where possible
1972 --
1973 create function bad_sql1() returns int as $$
1974 declare a int;
1975 begin
1976     a := 5;
1977     Johnny Yuma;
1978     a := 10;
1979     return a;
1980 end$$ language plpgsql;
1981
1982 create function bad_sql2() returns int as $$
1983 declare r record;
1984 begin
1985     for r in select I fought the law, the law won LOOP
1986         raise notice 'in loop';
1987     end loop;
1988     return 5;
1989 end;$$ language plpgsql;
1990
1991 -- a RETURN expression is mandatory, except for void-returning
1992 -- functions, where it is not allowed
1993 create function missing_return_expr() returns int as $$
1994 begin
1995     return ;
1996 end;$$ language plpgsql;
1997
1998 create function void_return_expr() returns void as $$
1999 begin
2000     return 5;
2001 end;$$ language plpgsql;
2002
2003 -- VOID functions are allowed to omit RETURN
2004 create function void_return_expr() returns void as $$
2005 begin
2006     perform 2+2;
2007 end;$$ language plpgsql;
2008
2009 select void_return_expr();
2010
2011 -- but ordinary functions are not
2012 create function missing_return_expr() returns int as $$
2013 begin
2014     perform 2+2;
2015 end;$$ language plpgsql;
2016
2017 select missing_return_expr();
2018
2019 drop function void_return_expr();
2020 drop function missing_return_expr();
2021
2022 --
2023 -- EXECUTE ... INTO test
2024 --
2025
2026 create table eifoo (i integer, y integer);
2027 create type eitype as (i integer, y integer);
2028
2029 create or replace function execute_into_test(varchar) returns record as $$
2030 declare
2031     _r record;
2032     _rt eifoo%rowtype;
2033     _v eitype;
2034     i int;
2035     j int;
2036     k int;
2037 begin
2038     execute 'insert into '||$1||' values(10,15)';
2039     execute 'select (row).* from (select row(10,1)::eifoo) s' into _r;
2040     raise notice '% %', _r.i, _r.y;
2041     execute 'select * from '||$1||' limit 1' into _rt;
2042     raise notice '% %', _rt.i, _rt.y;
2043     execute 'select *, 20 from '||$1||' limit 1' into i, j, k;
2044     raise notice '% % %', i, j, k;
2045     execute 'select 1,2' into _v;
2046     return _v;
2047 end; $$ language plpgsql;
2048
2049 select execute_into_test('eifoo');
2050
2051 drop table eifoo cascade;
2052 drop type eitype cascade;
2053
2054 --
2055 -- SQLSTATE and SQLERRM test
2056 --
2057
2058 create function excpt_test1() returns void as $$
2059 begin
2060     raise notice '% %', sqlstate, sqlerrm;
2061 end; $$ language plpgsql;
2062 -- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
2063 -- blocks
2064 select excpt_test1();
2065
2066 create function excpt_test2() returns void as $$
2067 begin
2068     begin
2069         begin
2070             raise notice '% %', sqlstate, sqlerrm;
2071         end;
2072     end;
2073 end; $$ language plpgsql;
2074 -- should fail
2075 select excpt_test2();
2076
2077 create function excpt_test3() returns void as $$
2078 begin
2079     begin
2080         raise exception 'user exception';
2081     exception when others then
2082             raise notice 'caught exception % %', sqlstate, sqlerrm;
2083             begin
2084                 raise notice '% %', sqlstate, sqlerrm;
2085                 perform 10/0;
2086         exception
2087             when substring_error then
2088                 -- this exception handler shouldn't be invoked
2089                 raise notice 'unexpected exception: % %', sqlstate, sqlerrm;
2090                 when division_by_zero then
2091                     raise notice 'caught exception % %', sqlstate, sqlerrm;
2092             end;
2093             raise notice '% %', sqlstate, sqlerrm;
2094     end;
2095 end; $$ language plpgsql;
2096
2097 select excpt_test3();
2098 drop function excpt_test1();
2099 drop function excpt_test2();
2100 drop function excpt_test3();
2101
2102 -- parameters of raise stmt can be expressions
2103 create function raise_exprs() returns void as $$
2104 declare
2105     a integer[] = '{10,20,30}';
2106     c varchar = 'xyz';
2107     i integer;
2108 begin
2109     i := 2;
2110     raise notice '%; %; %; %; %; %', a, a[i], c, (select c || 'abc'), row(10,'aaa',NULL,30), NULL;
2111 end;$$ language plpgsql;
2112
2113 select raise_exprs();
2114 drop function raise_exprs();
2115
2116 -- continue statement
2117 create table conttesttbl(idx serial, v integer);
2118 insert into conttesttbl(v) values(10);
2119 insert into conttesttbl(v) values(20);
2120 insert into conttesttbl(v) values(30);
2121 insert into conttesttbl(v) values(40);
2122
2123 create function continue_test1() returns void as $$
2124 declare _i integer = 0; _r record;
2125 begin
2126   raise notice '---1---';
2127   loop
2128     _i := _i + 1;
2129     raise notice '%', _i;
2130     continue when _i < 10;
2131     exit;
2132   end loop;
2133
2134   raise notice '---2---';
2135   <<lbl>>
2136   loop
2137     _i := _i - 1;
2138     loop
2139       raise notice '%', _i;
2140       continue lbl when _i > 0;
2141       exit lbl;
2142     end loop;
2143   end loop;
2144
2145   raise notice '---3---';
2146   <<the_loop>>
2147   while _i < 10 loop
2148     _i := _i + 1;
2149     continue the_loop when _i % 2 = 0;
2150     raise notice '%', _i;
2151   end loop;
2152
2153   raise notice '---4---';
2154   for _i in 1..10 loop
2155     begin
2156       -- applies to outer loop, not the nested begin block
2157       continue when _i < 5;
2158       raise notice '%', _i;
2159     end;
2160   end loop;
2161
2162   raise notice '---5---';
2163   for _r in select * from conttesttbl loop
2164     continue when _r.v <= 20;
2165     raise notice '%', _r.v;
2166   end loop;
2167
2168   raise notice '---6---';
2169   for _r in execute 'select * from conttesttbl' loop
2170     continue when _r.v <= 20;
2171     raise notice '%', _r.v;
2172   end loop;
2173
2174   raise notice '---7---';
2175   for _i in 1..3 loop
2176     raise notice '%', _i;
2177     continue when _i = 3;
2178   end loop;
2179
2180   raise notice '---8---';
2181   _i := 1;
2182   while _i <= 3 loop
2183     raise notice '%', _i;
2184     _i := _i + 1;
2185     continue when _i = 3;
2186   end loop;
2187
2188   raise notice '---9---';
2189   for _r in select * from conttesttbl order by v limit 1 loop
2190     raise notice '%', _r.v;
2191     continue;
2192   end loop;
2193
2194   raise notice '---10---';
2195   for _r in execute 'select * from conttesttbl order by v limit 1' loop
2196     raise notice '%', _r.v;
2197     continue;
2198   end loop;
2199 end; $$ language plpgsql;
2200
2201 select continue_test1();
2202
2203 -- CONTINUE is only legal inside a loop
2204 create function continue_test2() returns void as $$
2205 begin
2206     begin
2207         continue;
2208     end;
2209     return;
2210 end;
2211 $$ language plpgsql;
2212
2213 -- should fail
2214 select continue_test2();
2215
2216 -- CONTINUE can't reference the label of a named block
2217 create function continue_test3() returns void as $$
2218 begin
2219     <<begin_block1>>
2220     begin
2221         loop
2222             continue begin_block1;
2223         end loop;
2224     end;
2225 end;
2226 $$ language plpgsql;
2227
2228 -- should fail
2229 select continue_test3();
2230
2231 drop function continue_test1();
2232 drop function continue_test2();
2233 drop function continue_test3();
2234 drop table conttesttbl;
2235
2236 -- verbose end block and end loop
2237 create function end_label1() returns void as $$
2238 <<blbl>>
2239 begin
2240   <<flbl1>>
2241   for _i in 1 .. 10 loop
2242     exit flbl1;
2243   end loop flbl1;
2244   <<flbl2>>
2245   for _i in 1 .. 10 loop
2246     exit flbl2;
2247   end loop;
2248 end blbl;
2249 $$ language plpgsql;
2250
2251 select end_label1();
2252 drop function end_label1();
2253
2254 -- should fail: undefined end label
2255 create function end_label2() returns void as $$
2256 begin
2257   for _i in 1 .. 10 loop
2258     exit;
2259   end loop flbl1;
2260 end;
2261 $$ language plpgsql;
2262
2263 -- should fail: end label does not match start label
2264 create function end_label3() returns void as $$
2265 <<outer_label>>
2266 begin
2267   <<inner_label>>
2268   for _i in 1 .. 10 loop
2269     exit;
2270   end loop outer_label;
2271 end;
2272 $$ language plpgsql;
2273
2274 -- should fail: end label on a block without a start label
2275 create function end_label4() returns void as $$
2276 <<outer_label>>
2277 begin
2278   for _i in 1 .. 10 loop
2279     exit;
2280   end loop outer_label;
2281 end;
2282 $$ language plpgsql;
2283
2284 -- using list of scalars in fori and fore stmts
2285 create function for_vect() returns void as $proc$
2286 <<lbl>>declare a integer; b varchar; c varchar; r record;
2287 begin
2288   -- fori
2289   for i in 1 .. 3 loop
2290     raise notice '%', i;
2291   end loop;
2292   -- fore with record var
2293   for r in select gs as aa, 'BB' as bb, 'CC' as cc from generate_series(1,4) gs loop
2294     raise notice '% % %', r.aa, r.bb, r.cc;
2295   end loop;
2296   -- fore with single scalar
2297   for a in select gs from generate_series(1,4) gs loop
2298     raise notice '%', a;
2299   end loop;
2300   -- fore with multiple scalars
2301   for a,b,c in select gs, 'BB','CC' from generate_series(1,4) gs loop
2302     raise notice '% % %', a, b, c;
2303   end loop;
2304   -- using qualified names in fors, fore is enabled, disabled only for fori
2305   for lbl.a, lbl.b, lbl.c in execute $$select gs, 'bb','cc' from generate_series(1,4) gs$$ loop
2306     raise notice '% % %', a, b, c;
2307   end loop;
2308 end;
2309 $proc$ language plpgsql;
2310
2311 select for_vect();
2312
2313 -- regression test: verify that multiple uses of same plpgsql datum within
2314 -- a SQL command all get mapped to the same $n parameter.  The return value
2315 -- of the SELECT is not important, we only care that it doesn't fail with
2316 -- a complaint about an ungrouped column reference.
2317 create function multi_datum_use(p1 int) returns bool as $$
2318 declare
2319   x int;
2320   y int;
2321 begin
2322   select into x,y unique1/p1, unique1/$1 from tenk1 group by unique1/p1;
2323   return x = y;
2324 end$$ language plpgsql;
2325
2326 select multi_datum_use(42);
2327
2328 --
2329 -- Test STRICT limiter in both planned and EXECUTE invocations.
2330 -- Note that a data-modifying query is quasi strict (disallow multi rows)
2331 -- by default in the planned case, but not in EXECUTE.
2332 --
2333
2334 create temp table foo (f1 int, f2 int);
2335
2336 insert into foo values (1,2), (3,4);
2337
2338 create or replace function footest() returns void as $$
2339 declare x record;
2340 begin
2341   -- should work
2342   insert into foo values(5,6) returning * into x;
2343   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2344 end$$ language plpgsql;
2345
2346 select footest();
2347
2348 create or replace function footest() returns void as $$
2349 declare x record;
2350 begin
2351   -- should fail due to implicit strict
2352   insert into foo values(7,8),(9,10) returning * into x;
2353   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2354 end$$ language plpgsql;
2355
2356 select footest();
2357
2358 create or replace function footest() returns void as $$
2359 declare x record;
2360 begin
2361   -- should work
2362   execute 'insert into foo values(5,6) returning *' into x;
2363   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2364 end$$ language plpgsql;
2365
2366 select footest();
2367
2368 create or replace function footest() returns void as $$
2369 declare x record;
2370 begin
2371   -- this should work since EXECUTE isn't as picky
2372   execute 'insert into foo values(7,8),(9,10) returning *' into x;
2373   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2374 end$$ language plpgsql;
2375
2376 select footest();
2377
2378 select * from foo;
2379
2380 create or replace function footest() returns void as $$
2381 declare x record;
2382 begin
2383   -- should work
2384   select * from foo where f1 = 3 into strict x;
2385   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2386 end$$ language plpgsql;
2387
2388 select footest();
2389
2390 create or replace function footest() returns void as $$
2391 declare x record;
2392 begin
2393   -- should fail, no rows
2394   select * from foo where f1 = 0 into strict x;
2395   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2396 end$$ language plpgsql;
2397
2398 select footest();
2399
2400 create or replace function footest() returns void as $$
2401 declare x record;
2402 begin
2403   -- should fail, too many rows
2404   select * from foo where f1 > 3 into strict x;
2405   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2406 end$$ language plpgsql;
2407
2408 select footest();
2409
2410 create or replace function footest() returns void as $$
2411 declare x record;
2412 begin
2413   -- should work
2414   execute 'select * from foo where f1 = 3' into strict x;
2415   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2416 end$$ language plpgsql;
2417
2418 select footest();
2419
2420 create or replace function footest() returns void as $$
2421 declare x record;
2422 begin
2423   -- should fail, no rows
2424   execute 'select * from foo where f1 = 0' into strict x;
2425   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2426 end$$ language plpgsql;
2427
2428 select footest();
2429
2430 create or replace function footest() returns void as $$
2431 declare x record;
2432 begin
2433   -- should fail, too many rows
2434   execute 'select * from foo where f1 > 3' into strict x;
2435   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2436 end$$ language plpgsql;
2437
2438 select footest();
2439
2440 drop function footest();
2441
2442 -- test scrollable cursor support
2443
2444 create function sc_test() returns setof integer as $$
2445 declare
2446   c scroll cursor for select f1 from int4_tbl;
2447   x integer;
2448 begin
2449   open c;
2450   fetch last from c into x;
2451   while found loop
2452     return next x;
2453     fetch prior from c into x;
2454   end loop;
2455   close c;
2456 end;
2457 $$ language plpgsql;
2458
2459 select * from sc_test();
2460
2461 create or replace function sc_test() returns setof integer as $$
2462 declare
2463   c no scroll cursor for select f1 from int4_tbl;
2464   x integer;
2465 begin
2466   open c;
2467   fetch last from c into x;
2468   while found loop
2469     return next x;
2470     fetch prior from c into x;
2471   end loop;
2472   close c;
2473 end;
2474 $$ language plpgsql;
2475
2476 select * from sc_test();  -- fails because of NO SCROLL specification
2477
2478 create or replace function sc_test() returns setof integer as $$
2479 declare
2480   c refcursor;
2481   x integer;
2482 begin
2483   open c scroll for select f1 from int4_tbl;
2484   fetch last from c into x;
2485   while found loop
2486     return next x;
2487     fetch prior from c into x;
2488   end loop;
2489   close c;
2490 end;
2491 $$ language plpgsql;
2492
2493 select * from sc_test();
2494
2495 create or replace function sc_test() returns setof integer as $$
2496 declare
2497   c refcursor;
2498   x integer;
2499 begin
2500   open c scroll for execute 'select f1 from int4_tbl';
2501   fetch last from c into x;
2502   while found loop
2503     return next x;
2504     fetch relative -2 from c into x;
2505   end loop;
2506   close c;
2507 end;
2508 $$ language plpgsql;
2509
2510 select * from sc_test();
2511
2512 create or replace function sc_test() returns setof integer as $$
2513 declare
2514   c refcursor;
2515   x integer;
2516 begin
2517   open c scroll for execute 'select f1 from int4_tbl';
2518   fetch last from c into x;
2519   while found loop
2520     return next x;
2521     move backward 2 from c;
2522     fetch relative -1 from c into x;
2523   end loop;
2524   close c;
2525 end;
2526 $$ language plpgsql;
2527
2528 select * from sc_test();
2529
2530 create or replace function sc_test() returns setof integer as $$
2531 declare
2532   c cursor for select * from generate_series(1, 10);
2533   x integer;
2534 begin
2535   open c;
2536   loop
2537       move relative 2 in c;
2538       if not found then
2539           exit;
2540       end if;
2541       fetch next from c into x;
2542       if found then
2543           return next x;
2544       end if;
2545   end loop;
2546   close c;
2547 end;
2548 $$ language plpgsql;
2549
2550 select * from sc_test();
2551
2552 create or replace function sc_test() returns setof integer as $$
2553 declare
2554   c cursor for select * from generate_series(1, 10);
2555   x integer;
2556 begin
2557   open c;
2558   move forward all in c;
2559   fetch backward from c into x;
2560   if found then
2561     return next x;
2562   end if;
2563   close c;
2564 end;
2565 $$ language plpgsql;
2566
2567 select * from sc_test();
2568
2569 drop function sc_test();
2570
2571 -- test qualified variable names
2572
2573 create function pl_qual_names (param1 int) returns void as $$
2574 <<outerblock>>
2575 declare
2576   param1 int := 1;
2577 begin
2578   <<innerblock>>
2579   declare
2580     param1 int := 2;
2581   begin
2582     raise notice 'param1 = %', param1;
2583     raise notice 'pl_qual_names.param1 = %', pl_qual_names.param1;
2584     raise notice 'outerblock.param1 = %', outerblock.param1;
2585     raise notice 'innerblock.param1 = %', innerblock.param1;
2586   end;
2587 end;
2588 $$ language plpgsql;
2589
2590 select pl_qual_names(42);
2591
2592 drop function pl_qual_names(int);
2593
2594 -- tests for RETURN QUERY
2595 create function ret_query1(out int, out int) returns setof record as $$
2596 begin
2597     $1 := -1;
2598     $2 := -2;
2599     return next;
2600     return query select x + 1, x * 10 from generate_series(0, 10) s (x);
2601     return next;
2602 end;
2603 $$ language plpgsql;
2604
2605 select * from ret_query1();
2606
2607 create type record_type as (x text, y int, z boolean);
2608
2609 create or replace function ret_query2(lim int) returns setof record_type as $$
2610 begin
2611     return query select md5(s.x::text), s.x, s.x > 0
2612                  from generate_series(-8, lim) s (x) where s.x % 2 = 0;
2613 end;
2614 $$ language plpgsql;
2615
2616 select * from ret_query2(8);
2617
2618 -- test EXECUTE USING
2619 create function exc_using(int, text) returns int as $$
2620 declare i int;
2621 begin
2622   for i in execute 'select * from generate_series(1,$1)' using $1+1 loop
2623     raise notice '%', i;
2624   end loop;
2625   execute 'select $2 + $2*3 + length($1)' into i using $2,$1;
2626   return i;
2627 end
2628 $$ language plpgsql;
2629
2630 select exc_using(5, 'foobar');
2631
2632 drop function exc_using(int, text);
2633
2634 create or replace function exc_using(int) returns void as $$
2635 declare
2636   c refcursor;
2637   i int;
2638 begin
2639   open c for execute 'select * from generate_series(1,$1)' using $1+1;
2640   loop
2641     fetch c into i;
2642     exit when not found;
2643     raise notice '%', i;
2644   end loop;
2645   close c;
2646   return;
2647 end;
2648 $$ language plpgsql;
2649
2650 select exc_using(5);
2651
2652 drop function exc_using(int);
2653
2654 -- test FOR-over-cursor
2655
2656 create or replace function forc01() returns void as $$
2657 declare
2658   c cursor(r1 integer, r2 integer)
2659        for select * from generate_series(r1,r2) i;
2660   c2 cursor
2661        for select * from generate_series(41,43) i;
2662 begin
2663   for r in c(5,7) loop
2664     raise notice '% from %', r.i, c;
2665   end loop;
2666   -- again, to test if cursor was closed properly
2667   for r in c(9,10) loop
2668     raise notice '% from %', r.i, c;
2669   end loop;
2670   -- and test a parameterless cursor
2671   for r in c2 loop
2672     raise notice '% from %', r.i, c2;
2673   end loop;
2674   -- and try it with a hand-assigned name
2675   raise notice 'after loop, c2 = %', c2;
2676   c2 := 'special_name';
2677   for r in c2 loop
2678     raise notice '% from %', r.i, c2;
2679   end loop;
2680   raise notice 'after loop, c2 = %', c2;
2681   -- and try it with a generated name
2682   -- (which we can't show in the output because it's variable)
2683   c2 := null;
2684   for r in c2 loop
2685     raise notice '%', r.i;
2686   end loop;
2687   raise notice 'after loop, c2 = %', c2;
2688   return;
2689 end;
2690 $$ language plpgsql;
2691
2692 select forc01();
2693
2694 -- try updating the cursor's current row
2695
2696 create temp table forc_test as
2697   select n as i, n as j from generate_series(1,10) n;
2698
2699 create or replace function forc01() returns void as $$
2700 declare
2701   c cursor for select * from forc_test;
2702 begin
2703   for r in c loop
2704     raise notice '%, %', r.i, r.j;
2705     update forc_test set i = i * 100, j = r.j * 2 where current of c;
2706   end loop;
2707 end;
2708 $$ language plpgsql;
2709
2710 select forc01();
2711
2712 select * from forc_test;
2713
2714 -- same, with a cursor whose portal name doesn't match variable name
2715 create or replace function forc01() returns void as $$
2716 declare
2717   c refcursor := 'fooled_ya';
2718   r record;
2719 begin
2720   open c for select * from forc_test;
2721   loop
2722     fetch c into r;
2723     exit when not found;
2724     raise notice '%, %', r.i, r.j;
2725     update forc_test set i = i * 100, j = r.j * 2 where current of c;
2726   end loop;
2727 end;
2728 $$ language plpgsql;
2729
2730 select forc01();
2731
2732 select * from forc_test;
2733
2734 drop function forc01();
2735
2736 -- fail because cursor has no query bound to it
2737
2738 create or replace function forc_bad() returns void as $$
2739 declare
2740   c refcursor;
2741 begin
2742   for r in c loop
2743     raise notice '%', r.i;
2744   end loop;
2745 end;
2746 $$ language plpgsql;
2747
2748 -- test RETURN QUERY EXECUTE
2749
2750 create or replace function return_dquery()
2751 returns setof int as $$
2752 begin
2753   return query execute 'select * from (values(10),(20)) f';
2754   return query execute 'select * from (values($1),($2)) f' using 40,50;
2755 end;
2756 $$ language plpgsql;
2757
2758 select * from return_dquery();
2759
2760 drop function return_dquery();
2761
2762 -- test RETURN QUERY with dropped columns
2763
2764 create table tabwithcols(a int, b int, c int, d int);
2765 insert into tabwithcols values(10,20,30,40),(50,60,70,80);
2766
2767 create or replace function returnqueryf()
2768 returns setof tabwithcols as $$
2769 begin
2770   return query select * from tabwithcols;
2771   return query execute 'select * from tabwithcols';
2772 end;
2773 $$ language plpgsql;
2774
2775 select * from returnqueryf();
2776
2777 alter table tabwithcols drop column b;
2778
2779 select * from returnqueryf();
2780
2781 alter table tabwithcols drop column d;
2782
2783 select * from returnqueryf();
2784
2785 alter table tabwithcols add column d int;
2786
2787 select * from returnqueryf();
2788
2789 drop function returnqueryf();
2790 drop table tabwithcols;
2791
2792 -- Tests for 8.4's new RAISE features
2793
2794 create or replace function raise_test() returns void as $$
2795 begin
2796   raise notice '% % %', 1, 2, 3
2797      using errcode = '55001', detail = 'some detail info', hint = 'some hint';
2798   raise '% % %', 1, 2, 3
2799      using errcode = 'division_by_zero', detail = 'some detail info';
2800 end;
2801 $$ language plpgsql;
2802
2803 select raise_test();
2804
2805 -- Since we can't actually see the thrown SQLSTATE in default psql output,
2806 -- test it like this; this also tests re-RAISE
2807
2808 create or replace function raise_test() returns void as $$
2809 begin
2810   raise 'check me'
2811      using errcode = 'division_by_zero', detail = 'some detail info';
2812   exception
2813     when others then
2814       raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
2815       raise;
2816 end;
2817 $$ language plpgsql;
2818
2819 select raise_test();
2820
2821 create or replace function raise_test() returns void as $$
2822 begin
2823   raise 'check me'
2824      using errcode = '1234F', detail = 'some detail info';
2825   exception
2826     when others then
2827       raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
2828       raise;
2829 end;
2830 $$ language plpgsql;
2831
2832 select raise_test();
2833
2834 -- SQLSTATE specification in WHEN
2835 create or replace function raise_test() returns void as $$
2836 begin
2837   raise 'check me'
2838      using errcode = '1234F', detail = 'some detail info';
2839   exception
2840     when sqlstate '1234F' then
2841       raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
2842       raise;
2843 end;
2844 $$ language plpgsql;
2845
2846 select raise_test();
2847
2848 create or replace function raise_test() returns void as $$
2849 begin
2850   raise division_by_zero using detail = 'some detail info';
2851   exception
2852     when others then
2853       raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
2854       raise;
2855 end;
2856 $$ language plpgsql;
2857
2858 select raise_test();
2859
2860 create or replace function raise_test() returns void as $$
2861 begin
2862   raise division_by_zero;
2863 end;
2864 $$ language plpgsql;
2865
2866 select raise_test();
2867
2868 create or replace function raise_test() returns void as $$
2869 begin
2870   raise sqlstate '1234F';
2871 end;
2872 $$ language plpgsql;
2873
2874 select raise_test();
2875
2876 create or replace function raise_test() returns void as $$
2877 begin
2878   raise division_by_zero using message = 'custom' || ' message';
2879 end;
2880 $$ language plpgsql;
2881
2882 select raise_test();
2883
2884 create or replace function raise_test() returns void as $$
2885 begin
2886   raise using message = 'custom' || ' message', errcode = '22012';
2887 end;
2888 $$ language plpgsql;
2889
2890 select raise_test();
2891
2892 -- conflict on message
2893 create or replace function raise_test() returns void as $$
2894 begin
2895   raise notice 'some message' using message = 'custom' || ' message', errcode = '22012';
2896 end;
2897 $$ language plpgsql;
2898
2899 select raise_test();
2900
2901 -- conflict on errcode
2902 create or replace function raise_test() returns void as $$
2903 begin
2904   raise division_by_zero using message = 'custom' || ' message', errcode = '22012';
2905 end;
2906 $$ language plpgsql;
2907
2908 select raise_test();
2909
2910 -- nothing to re-RAISE
2911 create or replace function raise_test() returns void as $$
2912 begin
2913   raise;
2914 end;
2915 $$ language plpgsql;
2916
2917 select raise_test();
2918
2919 -- check cases where implicit SQLSTATE variable could be confused with
2920 -- SQLSTATE as a keyword, cf bug #5524
2921 create or replace function raise_test() returns void as $$
2922 begin
2923   perform 1/0;
2924 exception
2925   when sqlstate '22012' then
2926     raise notice using message = sqlstate;
2927     raise sqlstate '22012' using message = 'substitute message';
2928 end;
2929 $$ language plpgsql;
2930
2931 select raise_test();
2932
2933 drop function raise_test();
2934
2935 -- test CASE statement
2936
2937 create or replace function case_test(bigint) returns text as $$
2938 declare a int = 10;
2939         b int = 1;
2940 begin
2941   case $1
2942     when 1 then
2943       return 'one';
2944     when 2 then
2945       return 'two';
2946     when 3,4,3+5 then
2947       return 'three, four or eight';
2948     when a then
2949       return 'ten';
2950     when a+b, a+b+1 then
2951       return 'eleven, twelve';
2952   end case;
2953 end;
2954 $$ language plpgsql immutable;
2955
2956 select case_test(1);
2957 select case_test(2);
2958 select case_test(3);
2959 select case_test(4);
2960 select case_test(5); -- fails
2961 select case_test(8);
2962 select case_test(10);
2963 select case_test(11);
2964 select case_test(12);
2965 select case_test(13); -- fails
2966
2967 create or replace function catch() returns void as $$
2968 begin
2969   raise notice '%', case_test(6);
2970 exception
2971   when case_not_found then
2972     raise notice 'caught case_not_found % %', SQLSTATE, SQLERRM;
2973 end
2974 $$ language plpgsql;
2975
2976 select catch();
2977
2978 -- test the searched variant too, as well as ELSE
2979 create or replace function case_test(bigint) returns text as $$
2980 declare a int = 10;
2981 begin
2982   case
2983     when $1 = 1 then
2984       return 'one';
2985     when $1 = a + 2 then
2986       return 'twelve';
2987     else
2988       return 'other';
2989   end case;
2990 end;
2991 $$ language plpgsql immutable;
2992
2993 select case_test(1);
2994 select case_test(2);
2995 select case_test(12);
2996 select case_test(13);
2997
2998 drop function catch();
2999 drop function case_test(bigint);
3000
3001 -- test variadic functions
3002
3003 create or replace function vari(variadic int[])
3004 returns void as $$
3005 begin
3006   for i in array_lower($1,1)..array_upper($1,1) loop
3007     raise notice '%', $1[i];
3008   end loop; end;
3009 $$ language plpgsql;
3010
3011 select vari(1,2,3,4,5);
3012 select vari(3,4,5);
3013 select vari(variadic array[5,6,7]);
3014
3015 drop function vari(int[]);
3016
3017 -- coercion test
3018 create or replace function pleast(variadic numeric[])
3019 returns numeric as $$
3020 declare aux numeric = $1[array_lower($1,1)];
3021 begin
3022   for i in array_lower($1,1)+1..array_upper($1,1) loop
3023     if $1[i] < aux then aux := $1[i]; end if;
3024   end loop;
3025   return aux;
3026 end;
3027 $$ language plpgsql immutable strict;
3028
3029 select pleast(10,1,2,3,-16);
3030 select pleast(10.2,2.2,-1.1);
3031 select pleast(10.2,10, -20);
3032 select pleast(10,20, -1.0);
3033
3034 -- in case of conflict, non-variadic version is preferred
3035 create or replace function pleast(numeric)
3036 returns numeric as $$
3037 begin
3038   raise notice 'non-variadic function called';
3039   return $1;
3040 end;
3041 $$ language plpgsql immutable strict;
3042
3043 select pleast(10);
3044
3045 drop function pleast(numeric[]);
3046 drop function pleast(numeric);
3047
3048 -- test table functions
3049
3050 create function tftest(int) returns table(a int, b int) as $$
3051 begin
3052   return query select $1, $1+i from generate_series(1,5) g(i);
3053 end;
3054 $$ language plpgsql immutable strict;
3055
3056 select * from tftest(10);
3057
3058 create or replace function tftest(a1 int) returns table(a int, b int) as $$
3059 begin
3060   a := a1; b := a1 + 1;
3061   return next;
3062   a := a1 * 10; b := a1 * 10 + 1;
3063   return next;
3064 end;
3065 $$ language plpgsql immutable strict;
3066
3067 select * from tftest(10);
3068
3069 drop function tftest(int);
3070
3071 create or replace function rttest()
3072 returns setof int as $$
3073 declare rc int;
3074 begin
3075   return query values(10),(20);
3076   get diagnostics rc = row_count;
3077   raise notice '% %', found, rc;
3078   return query select * from (values(10),(20)) f(a) where false;
3079   get diagnostics rc = row_count;
3080   raise notice '% %', found, rc;
3081   return query execute 'values(10),(20)';
3082   get diagnostics rc = row_count;
3083   raise notice '% %', found, rc;
3084   return query execute 'select * from (values(10),(20)) f(a) where false';
3085   get diagnostics rc = row_count;
3086   raise notice '% %', found, rc;
3087 end;
3088 $$ language plpgsql;
3089
3090 select * from rttest();
3091
3092 drop function rttest();
3093
3094 -- Test for proper cleanup at subtransaction exit.  This example
3095 -- exposed a bug in PG 8.2.
3096
3097 CREATE FUNCTION leaker_1(fail BOOL) RETURNS INTEGER AS $$
3098 DECLARE
3099   v_var INTEGER;
3100 BEGIN
3101   BEGIN
3102     v_var := (leaker_2(fail)).error_code;
3103   EXCEPTION
3104     WHEN others THEN RETURN 0;
3105   END;
3106   RETURN 1;
3107 END;
3108 $$ LANGUAGE plpgsql;
3109
3110 CREATE FUNCTION leaker_2(fail BOOL, OUT error_code INTEGER, OUT new_id INTEGER)
3111   RETURNS RECORD AS $$
3112 BEGIN
3113   IF fail THEN
3114     RAISE EXCEPTION 'fail ...';
3115   END IF;
3116   error_code := 1;
3117   new_id := 1;
3118   RETURN;
3119 END;
3120 $$ LANGUAGE plpgsql;
3121
3122 SELECT * FROM leaker_1(false);
3123 SELECT * FROM leaker_1(true);
3124
3125 DROP FUNCTION leaker_1(bool);
3126 DROP FUNCTION leaker_2(bool);
3127
3128 -- Test for appropriate cleanup of non-simple expression evaluations
3129 -- (bug in all versions prior to August 2010)
3130
3131 CREATE FUNCTION nonsimple_expr_test() RETURNS text[] AS $$
3132 DECLARE
3133   arr text[];
3134   lr text;
3135   i integer;
3136 BEGIN
3137   arr := array[array['foo','bar'], array['baz', 'quux']];
3138   lr := 'fool';
3139   i := 1;
3140   -- use sub-SELECTs to make expressions non-simple
3141   arr[(SELECT i)][(SELECT i+1)] := (SELECT lr);
3142   RETURN arr;
3143 END;
3144 $$ LANGUAGE plpgsql;
3145
3146 SELECT nonsimple_expr_test();
3147
3148 DROP FUNCTION nonsimple_expr_test();
3149
3150 CREATE FUNCTION nonsimple_expr_test() RETURNS integer AS $$
3151 declare
3152    i integer NOT NULL := 0;
3153 begin
3154   begin
3155     i := (SELECT NULL::integer);  -- should throw error
3156   exception
3157     WHEN OTHERS THEN
3158       i := (SELECT 1::integer);
3159   end;
3160   return i;
3161 end;
3162 $$ LANGUAGE plpgsql;
3163
3164 SELECT nonsimple_expr_test();
3165
3166 DROP FUNCTION nonsimple_expr_test();
3167
3168 -- Test handling of string literals.
3169
3170 set standard_conforming_strings = off;
3171
3172 create or replace function strtest() returns text as $$
3173 begin
3174   raise notice 'foo\\bar\041baz';
3175   return 'foo\\bar\041baz';
3176 end
3177 $$ language plpgsql;
3178
3179 select strtest();
3180
3181 create or replace function strtest() returns text as $$
3182 begin
3183   raise notice E'foo\\bar\041baz';
3184   return E'foo\\bar\041baz';
3185 end
3186 $$ language plpgsql;
3187
3188 select strtest();
3189
3190 set standard_conforming_strings = on;
3191
3192 create or replace function strtest() returns text as $$
3193 begin
3194   raise notice 'foo\\bar\041baz\';
3195   return 'foo\\bar\041baz\';
3196 end
3197 $$ language plpgsql;
3198
3199 select strtest();
3200
3201 create or replace function strtest() returns text as $$
3202 begin
3203   raise notice E'foo\\bar\041baz';
3204   return E'foo\\bar\041baz';
3205 end
3206 $$ language plpgsql;
3207
3208 select strtest();
3209
3210 drop function strtest();
3211
3212 -- Test anonymous code blocks.
3213
3214 DO $$
3215 DECLARE r record;
3216 BEGIN
3217     FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno
3218     LOOP
3219         RAISE NOTICE '%, %', r.roomno, r.comment;
3220     END LOOP;
3221 END$$;
3222
3223 -- these are to check syntax error reporting
3224 DO LANGUAGE plpgsql $$begin return 1; end$$;
3225
3226 DO $$
3227 DECLARE r record;
3228 BEGIN
3229     FOR r IN SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno
3230     LOOP
3231         RAISE NOTICE '%, %', r.roomno, r.comment;
3232     END LOOP;
3233 END$$;
3234
3235 -- Check variable scoping -- a var is not available in its own or prior
3236 -- default expressions.
3237
3238 create function scope_test() returns int as $$
3239 declare x int := 42;
3240 begin
3241   declare y int := x + 1;
3242           x int := x + 2;
3243   begin
3244     return x * 100 + y;
3245   end;
3246 end;
3247 $$ language plpgsql;
3248
3249 select scope_test();
3250
3251 drop function scope_test();
3252
3253 -- Check handling of conflicts between plpgsql vars and table columns.
3254
3255 set plpgsql.variable_conflict = error;
3256
3257 create function conflict_test() returns setof int8_tbl as $$
3258 declare r record;
3259   q1 bigint := 42;
3260 begin
3261   for r in select q1,q2 from int8_tbl loop
3262     return next r;
3263   end loop;
3264 end;
3265 $$ language plpgsql;
3266
3267 select * from conflict_test();
3268
3269 create or replace function conflict_test() returns setof int8_tbl as $$
3270 #variable_conflict use_variable
3271 declare r record;
3272   q1 bigint := 42;
3273 begin
3274   for r in select q1,q2 from int8_tbl loop
3275     return next r;
3276   end loop;
3277 end;
3278 $$ language plpgsql;
3279
3280 select * from conflict_test();
3281
3282 create or replace function conflict_test() returns setof int8_tbl as $$
3283 #variable_conflict use_column
3284 declare r record;
3285   q1 bigint := 42;
3286 begin
3287   for r in select q1,q2 from int8_tbl loop
3288     return next r;
3289   end loop;
3290 end;
3291 $$ language plpgsql;
3292
3293 select * from conflict_test();
3294
3295 drop function conflict_test();
3296
3297 -- Check that an unreserved keyword can be used as a variable name
3298
3299 create function unreserved_test() returns int as $$
3300 declare
3301   forward int := 21;
3302 begin
3303   forward := forward * 2;
3304   return forward;
3305 end
3306 $$ language plpgsql;
3307
3308 select unreserved_test();
3309
3310 drop function unreserved_test();