granicus.if.org Git - postgresql/blob - src/test/regress/expected/plpgsql.out

   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 create table Room (
  24     roomno      char(8),
  25     comment     text
  26 );
  27 create unique index Room_rno on Room using btree (roomno bpchar_ops);
  28 create table WSlot (
  29     slotname    char(20),
  30     roomno      char(8),
  31     slotlink    char(20),
  32     backlink    char(20)
  33 );
  34 create unique index WSlot_name on WSlot using btree (slotname bpchar_ops);
  35 create table PField (
  36     name        text,
  37     comment     text
  38 );
  39 create unique index PField_name on PField using btree (name text_ops);
  40 create table PSlot (
  41     slotname    char(20),
  42     pfname      text,
  43     slotlink    char(20),
  44     backlink    char(20)
  45 );
  46 create unique index PSlot_name on PSlot using btree (slotname bpchar_ops);
  47 create table PLine (
  48     slotname    char(20),
  49     phonenumber char(20),
  50     comment     text,
  51     backlink    char(20)
  52 );
  53 create unique index PLine_name on PLine using btree (slotname bpchar_ops);
  54 create table Hub (
  55     name        char(14),
  56     comment     text,
  57     nslots      integer
  58 );
  59 create unique index Hub_name on Hub using btree (name bpchar_ops);
  60 create table HSlot (
  61     slotname    char(20),
  62     hubname     char(14),
  63     slotno      integer,
  64     slotlink    char(20)
  65 );
  66 create unique index HSlot_name on HSlot using btree (slotname bpchar_ops);
  67 create index HSlot_hubname on HSlot using btree (hubname bpchar_ops);
  68 create table System (
  69     name        text,
  70     comment     text
  71 );
  72 create unique index System_name on System using btree (name text_ops);
  73 create table IFace (
  74     slotname    char(20),
  75     sysname     text,
  76     ifname      text,
  77     slotlink    char(20)
  78 );
  79 create unique index IFace_name on IFace using btree (slotname bpchar_ops);
  80 create table PHone (
  81     slotname    char(20),
  82     comment     text,
  83     slotlink    char(20)
  84 );
  85 create unique index PHone_name on PHone using btree (slotname bpchar_ops);
  86 -- ************************************************************
  87 -- *
  88 -- * Trigger procedures and functions for the patchfield
  89 -- * test of PL/pgSQL
  90 -- *
  91 -- ************************************************************
  92 -- ************************************************************
  93 -- * AFTER UPDATE on Room
  94 -- *    - If room no changes let wall slots follow
  95 -- ************************************************************
  96 create function tg_room_au() returns trigger as '
  97 begin
  98     if new.roomno != old.roomno then
  99         update WSlot set roomno = new.roomno where roomno = old.roomno;
 100     end if;
 101     return new;
 102 end;
 103 ' language 'plpgsql';
 104 create trigger tg_room_au after update
 105     on Room for each row execute procedure tg_room_au();
 106 -- ************************************************************
 107 -- * AFTER DELETE on Room
 108 -- *    - delete wall slots in this room
 109 -- ************************************************************
 110 create function tg_room_ad() returns trigger as '
 111 begin
 112     delete from WSlot where roomno = old.roomno;
 113     return old;
 114 end;
 115 ' language 'plpgsql';
 116 create trigger tg_room_ad after delete
 117     on Room for each row execute procedure tg_room_ad();
 118 -- ************************************************************
 119 -- * BEFORE INSERT or UPDATE on WSlot
 120 -- *    - Check that room exists
 121 -- ************************************************************
 122 create function tg_wslot_biu() returns trigger as $$
 123 begin
 124     if count(*) = 0 from Room where roomno = new.roomno then
 125         raise exception 'Room % does not exist', new.roomno;
 126     end if;
 127     return new;
 128 end;
 129 $$ language plpgsql;
 130 create trigger tg_wslot_biu before insert or update
 131     on WSlot for each row execute procedure tg_wslot_biu();
 132 -- ************************************************************
 133 -- * AFTER UPDATE on PField
 134 -- *    - Let PSlots of this field follow
 135 -- ************************************************************
 136 create function tg_pfield_au() returns trigger as '
 137 begin
 138     if new.name != old.name then
 139         update PSlot set pfname = new.name where pfname = old.name;
 140     end if;
 141     return new;
 142 end;
 143 ' language 'plpgsql';
 144 create trigger tg_pfield_au after update
 145     on PField for each row execute procedure tg_pfield_au();
 146 -- ************************************************************
 147 -- * AFTER DELETE on PField
 148 -- *    - Remove all slots of this patchfield
 149 -- ************************************************************
 150 create function tg_pfield_ad() returns trigger as '
 151 begin
 152     delete from PSlot where pfname = old.name;
 153     return old;
 154 end;
 155 ' language 'plpgsql';
 156 create trigger tg_pfield_ad after delete
 157     on PField for each row execute procedure tg_pfield_ad();
 158 -- ************************************************************
 159 -- * BEFORE INSERT or UPDATE on PSlot
 160 -- *    - Ensure that our patchfield does exist
 161 -- ************************************************************
 162 create function tg_pslot_biu() returns trigger as $proc$
 163 declare
 164     pfrec       record;
 165     rename new to ps;
 166 begin
 167     select into pfrec * from PField where name = ps.pfname;
 168     if not found then
 169         raise exception $$Patchfield "%" does not exist$$, ps.pfname;
 170     end if;
 171     return ps;
 172 end;
 173 $proc$ language plpgsql;
 174 create trigger tg_pslot_biu before insert or update
 175     on PSlot for each row execute procedure tg_pslot_biu();
 176 -- ************************************************************
 177 -- * AFTER UPDATE on System
 178 -- *    - If system name changes let interfaces follow
 179 -- ************************************************************
 180 create function tg_system_au() returns trigger as '
 181 begin
 182     if new.name != old.name then
 183         update IFace set sysname = new.name where sysname = old.name;
 184     end if;
 185     return new;
 186 end;
 187 ' language 'plpgsql';
 188 create trigger tg_system_au after update
 189     on System for each row execute procedure tg_system_au();
 190 -- ************************************************************
 191 -- * BEFORE INSERT or UPDATE on IFace
 192 -- *    - set the slotname to IF.sysname.ifname
 193 -- ************************************************************
 194 create function tg_iface_biu() returns trigger as $$
 195 declare
 196     sname       text;
 197     sysrec      record;
 198 begin
 199     select into sysrec * from system where name = new.sysname;
 200     if not found then
 201         raise exception $q$system "%" does not exist$q$, new.sysname;
 202     end if;
 203     sname := 'IF.' || new.sysname;
 204     sname := sname || '.';
 205     sname := sname || new.ifname;
 206     if length(sname) > 20 then
 207         raise exception 'IFace slotname "%" too long (20 char max)', sname;
 208     end if;
 209     new.slotname := sname;
 210     return new;
 211 end;
 212 $$ language plpgsql;
 213 create trigger tg_iface_biu before insert or update
 214     on IFace for each row execute procedure tg_iface_biu();
 215 -- ************************************************************
 216 -- * AFTER INSERT or UPDATE or DELETE on Hub
 217 -- *    - insert/delete/rename slots as required
 218 -- ************************************************************
 219 create function tg_hub_a() returns trigger as '
 220 declare
 221     hname       text;
 222     dummy       integer;
 223 begin
 224     if tg_op = ''INSERT'' then
 225         dummy := tg_hub_adjustslots(new.name, 0, new.nslots);
 226         return new;
 227     end if;
 228     if tg_op = ''UPDATE'' then
 229         if new.name != old.name then
 230             update HSlot set hubname = new.name where hubname = old.name;
 231         end if;
 232         dummy := tg_hub_adjustslots(new.name, old.nslots, new.nslots);
 233         return new;
 234     end if;
 235     if tg_op = ''DELETE'' then
 236         dummy := tg_hub_adjustslots(old.name, old.nslots, 0);
 237         return old;
 238     end if;
 239 end;
 240 ' language 'plpgsql';
 241 create trigger tg_hub_a after insert or update or delete
 242     on Hub for each row execute procedure tg_hub_a();
 243 -- ************************************************************
 244 -- * Support function to add/remove slots of Hub
 245 -- ************************************************************
 246 create function tg_hub_adjustslots(hname bpchar,
 247                                    oldnslots integer,
 248                                    newnslots integer)
 249 returns integer as '
 250 begin
 251     if newnslots = oldnslots then
 252         return 0;
 253     end if;
 254     if newnslots < oldnslots then
 255         delete from HSlot where hubname = hname and slotno > newnslots;
 256         return 0;
 257     end if;
 258     for i in oldnslots + 1 .. newnslots loop
 259         insert into HSlot (slotname, hubname, slotno, slotlink)
 260                 values (''HS.dummy'', hname, i, '''');
 261     end loop;
 262     return 0;
 263 end
 264 ' language 'plpgsql';
 265 -- Test comments
 266 COMMENT ON FUNCTION tg_hub_adjustslots_wrong(bpchar, integer, integer) IS 'function with args';
 267 ERROR:  function tg_hub_adjustslots_wrong(character, integer, integer) does not exist
 268 COMMENT ON FUNCTION tg_hub_adjustslots(bpchar, integer, integer) IS 'function with args';
 269 COMMENT ON FUNCTION tg_hub_adjustslots(bpchar, integer, integer) IS NULL;
 270 -- ************************************************************
 271 -- * BEFORE INSERT or UPDATE on HSlot
 272 -- *    - prevent from manual manipulation
 273 -- *    - set the slotname to HS.hubname.slotno
 274 -- ************************************************************
 275 create function tg_hslot_biu() returns trigger as '
 276 declare
 277     sname       text;
 278     xname       HSlot.slotname%TYPE;
 279     hubrec      record;
 280 begin
 281     select into hubrec * from Hub where name = new.hubname;
 282     if not found then
 283         raise exception ''no manual manipulation of HSlot'';
 284     end if;
 285     if new.slotno < 1 or new.slotno > hubrec.nslots then
 286         raise exception ''no manual manipulation of HSlot'';
 287     end if;
 288     if tg_op = ''UPDATE'' then
 289         if new.hubname != old.hubname then
 290             if count(*) > 0 from Hub where name = old.hubname then
 291                 raise exception ''no manual manipulation of HSlot'';
 292             end if;
 293         end if;
 294     end if;
 295     sname := ''HS.'' || trim(new.hubname);
 296     sname := sname || ''.'';
 297     sname := sname || new.slotno::text;
 298     if length(sname) > 20 then
 299         raise exception ''HSlot slotname "%" too long (20 char max)'', sname;
 300     end if;
 301     new.slotname := sname;
 302     return new;
 303 end;
 304 ' language 'plpgsql';
 305 create trigger tg_hslot_biu before insert or update
 306     on HSlot for each row execute procedure tg_hslot_biu();
 307 -- ************************************************************
 308 -- * BEFORE DELETE on HSlot
 309 -- *    - prevent from manual manipulation
 310 -- ************************************************************
 311 create function tg_hslot_bd() returns trigger as '
 312 declare
 313     hubrec      record;
 314 begin
 315     select into hubrec * from Hub where name = old.hubname;
 316     if not found then
 317         return old;
 318     end if;
 319     if old.slotno > hubrec.nslots then
 320         return old;
 321     end if;
 322     raise exception ''no manual manipulation of HSlot'';
 323 end;
 324 ' language 'plpgsql';
 325 create trigger tg_hslot_bd before delete
 326     on HSlot for each row execute procedure tg_hslot_bd();
 327 -- ************************************************************
 328 -- * BEFORE INSERT on all slots
 329 -- *    - Check name prefix
 330 -- ************************************************************
 331 create function tg_chkslotname() returns trigger as '
 332 begin
 333     if substr(new.slotname, 1, 2) != tg_argv[0] then
 334         raise exception ''slotname must begin with %'', tg_argv[0];
 335     end if;
 336     return new;
 337 end;
 338 ' language 'plpgsql';
 339 create trigger tg_chkslotname before insert
 340     on PSlot for each row execute procedure tg_chkslotname('PS');
 341 create trigger tg_chkslotname before insert
 342     on WSlot for each row execute procedure tg_chkslotname('WS');
 343 create trigger tg_chkslotname before insert
 344     on PLine for each row execute procedure tg_chkslotname('PL');
 345 create trigger tg_chkslotname before insert
 346     on IFace for each row execute procedure tg_chkslotname('IF');
 347 create trigger tg_chkslotname before insert
 348     on PHone for each row execute procedure tg_chkslotname('PH');
 349 -- ************************************************************
 350 -- * BEFORE INSERT or UPDATE on all slots with slotlink
 351 -- *    - Set slotlink to empty string if NULL value given
 352 -- ************************************************************
 353 create function tg_chkslotlink() returns trigger as '
 354 begin
 355     if new.slotlink isnull then
 356         new.slotlink := '''';
 357     end if;
 358     return new;
 359 end;
 360 ' language 'plpgsql';
 361 create trigger tg_chkslotlink before insert or update
 362     on PSlot for each row execute procedure tg_chkslotlink();
 363 create trigger tg_chkslotlink before insert or update
 364     on WSlot for each row execute procedure tg_chkslotlink();
 365 create trigger tg_chkslotlink before insert or update
 366     on IFace for each row execute procedure tg_chkslotlink();
 367 create trigger tg_chkslotlink before insert or update
 368     on HSlot for each row execute procedure tg_chkslotlink();
 369 create trigger tg_chkslotlink before insert or update
 370     on PHone for each row execute procedure tg_chkslotlink();
 371 -- ************************************************************
 372 -- * BEFORE INSERT or UPDATE on all slots with backlink
 373 -- *    - Set backlink to empty string if NULL value given
 374 -- ************************************************************
 375 create function tg_chkbacklink() returns trigger as '
 376 begin
 377     if new.backlink isnull then
 378         new.backlink := '''';
 379     end if;
 380     return new;
 381 end;
 382 ' language 'plpgsql';
 383 create trigger tg_chkbacklink before insert or update
 384     on PSlot for each row execute procedure tg_chkbacklink();
 385 create trigger tg_chkbacklink before insert or update
 386     on WSlot for each row execute procedure tg_chkbacklink();
 387 create trigger tg_chkbacklink before insert or update
 388     on PLine for each row execute procedure tg_chkbacklink();
 389 -- ************************************************************
 390 -- * BEFORE UPDATE on PSlot
 391 -- *    - do delete/insert instead of update if name changes
 392 -- ************************************************************
 393 create function tg_pslot_bu() returns trigger as '
 394 begin
 395     if new.slotname != old.slotname then
 396         delete from PSlot where slotname = old.slotname;
 397         insert into PSlot (
 398                     slotname,
 399                     pfname,
 400                     slotlink,
 401                     backlink
 402                 ) values (
 403                     new.slotname,
 404                     new.pfname,
 405                     new.slotlink,
 406                     new.backlink
 407                 );
 408         return null;
 409     end if;
 410     return new;
 411 end;
 412 ' language 'plpgsql';
 413 create trigger tg_pslot_bu before update
 414     on PSlot for each row execute procedure tg_pslot_bu();
 415 -- ************************************************************
 416 -- * BEFORE UPDATE on WSlot
 417 -- *    - do delete/insert instead of update if name changes
 418 -- ************************************************************
 419 create function tg_wslot_bu() returns trigger as '
 420 begin
 421     if new.slotname != old.slotname then
 422         delete from WSlot where slotname = old.slotname;
 423         insert into WSlot (
 424                     slotname,
 425                     roomno,
 426                     slotlink,
 427                     backlink
 428                 ) values (
 429                     new.slotname,
 430                     new.roomno,
 431                     new.slotlink,
 432                     new.backlink
 433                 );
 434         return null;
 435     end if;
 436     return new;
 437 end;
 438 ' language 'plpgsql';
 439 create trigger tg_wslot_bu before update
 440     on WSlot for each row execute procedure tg_Wslot_bu();
 441 -- ************************************************************
 442 -- * BEFORE UPDATE on PLine
 443 -- *    - do delete/insert instead of update if name changes
 444 -- ************************************************************
 445 create function tg_pline_bu() returns trigger as '
 446 begin
 447     if new.slotname != old.slotname then
 448         delete from PLine where slotname = old.slotname;
 449         insert into PLine (
 450                     slotname,
 451                     phonenumber,
 452                     comment,
 453                     backlink
 454                 ) values (
 455                     new.slotname,
 456                     new.phonenumber,
 457                     new.comment,
 458                     new.backlink
 459                 );
 460         return null;
 461     end if;
 462     return new;
 463 end;
 464 ' language 'plpgsql';
 465 create trigger tg_pline_bu before update
 466     on PLine for each row execute procedure tg_pline_bu();
 467 -- ************************************************************
 468 -- * BEFORE UPDATE on IFace
 469 -- *    - do delete/insert instead of update if name changes
 470 -- ************************************************************
 471 create function tg_iface_bu() returns trigger as '
 472 begin
 473     if new.slotname != old.slotname then
 474         delete from IFace where slotname = old.slotname;
 475         insert into IFace (
 476                     slotname,
 477                     sysname,
 478                     ifname,
 479                     slotlink
 480                 ) values (
 481                     new.slotname,
 482                     new.sysname,
 483                     new.ifname,
 484                     new.slotlink
 485                 );
 486         return null;
 487     end if;
 488     return new;
 489 end;
 490 ' language 'plpgsql';
 491 create trigger tg_iface_bu before update
 492     on IFace for each row execute procedure tg_iface_bu();
 493 -- ************************************************************
 494 -- * BEFORE UPDATE on HSlot
 495 -- *    - do delete/insert instead of update if name changes
 496 -- ************************************************************
 497 create function tg_hslot_bu() returns trigger as '
 498 begin
 499     if new.slotname != old.slotname or new.hubname != old.hubname then
 500         delete from HSlot where slotname = old.slotname;
 501         insert into HSlot (
 502                     slotname,
 503                     hubname,
 504                     slotno,
 505                     slotlink
 506                 ) values (
 507                     new.slotname,
 508                     new.hubname,
 509                     new.slotno,
 510                     new.slotlink
 511                 );
 512         return null;
 513     end if;
 514     return new;
 515 end;
 516 ' language 'plpgsql';
 517 create trigger tg_hslot_bu before update
 518     on HSlot for each row execute procedure tg_hslot_bu();
 519 -- ************************************************************
 520 -- * BEFORE UPDATE on PHone
 521 -- *    - do delete/insert instead of update if name changes
 522 -- ************************************************************
 523 create function tg_phone_bu() returns trigger as '
 524 begin
 525     if new.slotname != old.slotname then
 526         delete from PHone where slotname = old.slotname;
 527         insert into PHone (
 528                     slotname,
 529                     comment,
 530                     slotlink
 531                 ) values (
 532                     new.slotname,
 533                     new.comment,
 534                     new.slotlink
 535                 );
 536         return null;
 537     end if;
 538     return new;
 539 end;
 540 ' language 'plpgsql';
 541 create trigger tg_phone_bu before update
 542     on PHone for each row execute procedure tg_phone_bu();
 543 -- ************************************************************
 544 -- * AFTER INSERT or UPDATE or DELETE on slot with backlink
 545 -- *    - Ensure that the opponent correctly points back to us
 546 -- ************************************************************
 547 create function tg_backlink_a() returns trigger as '
 548 declare
 549     dummy       integer;
 550 begin
 551     if tg_op = ''INSERT'' then
 552         if new.backlink != '''' then
 553             dummy := tg_backlink_set(new.backlink, new.slotname);
 554         end if;
 555         return new;
 556     end if;
 557     if tg_op = ''UPDATE'' then
 558         if new.backlink != old.backlink then
 559             if old.backlink != '''' then
 560                 dummy := tg_backlink_unset(old.backlink, old.slotname);
 561             end if;
 562             if new.backlink != '''' then
 563                 dummy := tg_backlink_set(new.backlink, new.slotname);
 564             end if;
 565         else
 566             if new.slotname != old.slotname and new.backlink != '''' then
 567                 dummy := tg_slotlink_set(new.backlink, new.slotname);
 568             end if;
 569         end if;
 570         return new;
 571     end if;
 572     if tg_op = ''DELETE'' then
 573         if old.backlink != '''' then
 574             dummy := tg_backlink_unset(old.backlink, old.slotname);
 575         end if;
 576         return old;
 577     end if;
 578 end;
 579 ' language 'plpgsql';
 580 create trigger tg_backlink_a after insert or update or delete
 581     on PSlot for each row execute procedure tg_backlink_a('PS');
 582 create trigger tg_backlink_a after insert or update or delete
 583     on WSlot for each row execute procedure tg_backlink_a('WS');
 584 create trigger tg_backlink_a after insert or update or delete
 585     on PLine for each row execute procedure tg_backlink_a('PL');
 586 -- ************************************************************
 587 -- * Support function to set the opponents backlink field
 588 -- * if it does not already point to the requested slot
 589 -- ************************************************************
 590 create function tg_backlink_set(myname bpchar, blname bpchar)
 591 returns integer as '
 592 declare
 593     mytype      char(2);
 594     link        char(4);
 595     rec         record;
 596 begin
 597     mytype := substr(myname, 1, 2);
 598     link := mytype || substr(blname, 1, 2);
 599     if link = ''PLPL'' then
 600         raise exception
 601                 ''backlink between two phone lines does not make sense'';
 602     end if;
 603     if link in (''PLWS'', ''WSPL'') then
 604         raise exception
 605                 ''direct link of phone line to wall slot not permitted'';
 606     end if;
 607     if mytype = ''PS'' then
 608         select into rec * from PSlot where slotname = myname;
 609         if not found then
 610             raise exception ''% does not exist'', myname;
 611         end if;
 612         if rec.backlink != blname then
 613             update PSlot set backlink = blname where slotname = myname;
 614         end if;
 615         return 0;
 616     end if;
 617     if mytype = ''WS'' then
 618         select into rec * from WSlot where slotname = myname;
 619         if not found then
 620             raise exception ''% does not exist'', myname;
 621         end if;
 622         if rec.backlink != blname then
 623             update WSlot set backlink = blname where slotname = myname;
 624         end if;
 625         return 0;
 626     end if;
 627     if mytype = ''PL'' then
 628         select into rec * from PLine where slotname = myname;
 629         if not found then
 630             raise exception ''% does not exist'', myname;
 631         end if;
 632         if rec.backlink != blname then
 633             update PLine set backlink = blname where slotname = myname;
 634         end if;
 635         return 0;
 636     end if;
 637     raise exception ''illegal backlink beginning with %'', mytype;
 638 end;
 639 ' language 'plpgsql';
 640 -- ************************************************************
 641 -- * Support function to clear out the backlink field if
 642 -- * it still points to specific slot
 643 -- ************************************************************
 644 create function tg_backlink_unset(bpchar, bpchar)
 645 returns integer as '
 646 declare
 647     myname      alias for $1;
 648     blname      alias for $2;
 649     mytype      char(2);
 650     rec         record;
 651 begin
 652     mytype := substr(myname, 1, 2);
 653     if mytype = ''PS'' then
 654         select into rec * from PSlot where slotname = myname;
 655         if not found then
 656             return 0;
 657         end if;
 658         if rec.backlink = blname then
 659             update PSlot set backlink = '''' where slotname = myname;
 660         end if;
 661         return 0;
 662     end if;
 663     if mytype = ''WS'' then
 664         select into rec * from WSlot where slotname = myname;
 665         if not found then
 666             return 0;
 667         end if;
 668         if rec.backlink = blname then
 669             update WSlot set backlink = '''' where slotname = myname;
 670         end if;
 671         return 0;
 672     end if;
 673     if mytype = ''PL'' then
 674         select into rec * from PLine where slotname = myname;
 675         if not found then
 676             return 0;
 677         end if;
 678         if rec.backlink = blname then
 679             update PLine set backlink = '''' where slotname = myname;
 680         end if;
 681         return 0;
 682     end if;
 683 end
 684 ' language plpgsql;
 685 -- ************************************************************
 686 -- * AFTER INSERT or UPDATE or DELETE on slot with slotlink
 687 -- *    - Ensure that the opponent correctly points back to us
 688 -- ************************************************************
 689 create function tg_slotlink_a() returns trigger as '
 690 declare
 691     dummy       integer;
 692 begin
 693     if tg_op = ''INSERT'' then
 694         if new.slotlink != '''' then
 695             dummy := tg_slotlink_set(new.slotlink, new.slotname);
 696         end if;
 697         return new;
 698     end if;
 699     if tg_op = ''UPDATE'' then
 700         if new.slotlink != old.slotlink then
 701             if old.slotlink != '''' then
 702                 dummy := tg_slotlink_unset(old.slotlink, old.slotname);
 703             end if;
 704             if new.slotlink != '''' then
 705                 dummy := tg_slotlink_set(new.slotlink, new.slotname);
 706             end if;
 707         else
 708             if new.slotname != old.slotname and new.slotlink != '''' then
 709                 dummy := tg_slotlink_set(new.slotlink, new.slotname);
 710             end if;
 711         end if;
 712         return new;
 713     end if;
 714     if tg_op = ''DELETE'' then
 715         if old.slotlink != '''' then
 716             dummy := tg_slotlink_unset(old.slotlink, old.slotname);
 717         end if;
 718         return old;
 719     end if;
 720 end;
 721 ' language 'plpgsql';
 722 create trigger tg_slotlink_a after insert or update or delete
 723     on PSlot for each row execute procedure tg_slotlink_a('PS');
 724 create trigger tg_slotlink_a after insert or update or delete
 725     on WSlot for each row execute procedure tg_slotlink_a('WS');
 726 create trigger tg_slotlink_a after insert or update or delete
 727     on IFace for each row execute procedure tg_slotlink_a('IF');
 728 create trigger tg_slotlink_a after insert or update or delete
 729     on HSlot for each row execute procedure tg_slotlink_a('HS');
 730 create trigger tg_slotlink_a after insert or update or delete
 731     on PHone for each row execute procedure tg_slotlink_a('PH');
 732 -- ************************************************************
 733 -- * Support function to set the opponents slotlink field
 734 -- * if it does not already point to the requested slot
 735 -- ************************************************************
 736 create function tg_slotlink_set(bpchar, bpchar)
 737 returns integer as '
 738 declare
 739     myname      alias for $1;
 740     blname      alias for $2;
 741     mytype      char(2);
 742     link        char(4);
 743     rec         record;
 744 begin
 745     mytype := substr(myname, 1, 2);
 746     link := mytype || substr(blname, 1, 2);
 747     if link = ''PHPH'' then
 748         raise exception
 749                 ''slotlink between two phones does not make sense'';
 750     end if;
 751     if link in (''PHHS'', ''HSPH'') then
 752         raise exception
 753                 ''link of phone to hub does not make sense'';
 754     end if;
 755     if link in (''PHIF'', ''IFPH'') then
 756         raise exception
 757                 ''link of phone to hub does not make sense'';
 758     end if;
 759     if link in (''PSWS'', ''WSPS'') then
 760         raise exception
 761                 ''slotlink from patchslot to wallslot not permitted'';
 762     end if;
 763     if mytype = ''PS'' then
 764         select into rec * from PSlot where slotname = myname;
 765         if not found then
 766             raise exception ''% does not exist'', myname;
 767         end if;
 768         if rec.slotlink != blname then
 769             update PSlot set slotlink = blname where slotname = myname;
 770         end if;
 771         return 0;
 772     end if;
 773     if mytype = ''WS'' then
 774         select into rec * from WSlot where slotname = myname;
 775         if not found then
 776             raise exception ''% does not exist'', myname;
 777         end if;
 778         if rec.slotlink != blname then
 779             update WSlot set slotlink = blname where slotname = myname;
 780         end if;
 781         return 0;
 782     end if;
 783     if mytype = ''IF'' then
 784         select into rec * from IFace where slotname = myname;
 785         if not found then
 786             raise exception ''% does not exist'', myname;
 787         end if;
 788         if rec.slotlink != blname then
 789             update IFace set slotlink = blname where slotname = myname;
 790         end if;
 791         return 0;
 792     end if;
 793     if mytype = ''HS'' then
 794         select into rec * from HSlot where slotname = myname;
 795         if not found then
 796             raise exception ''% does not exist'', myname;
 797         end if;
 798         if rec.slotlink != blname then
 799             update HSlot set slotlink = blname where slotname = myname;
 800         end if;
 801         return 0;
 802     end if;
 803     if mytype = ''PH'' then
 804         select into rec * from PHone where slotname = myname;
 805         if not found then
 806             raise exception ''% does not exist'', myname;
 807         end if;
 808         if rec.slotlink != blname then
 809             update PHone set slotlink = blname where slotname = myname;
 810         end if;
 811         return 0;
 812     end if;
 813     raise exception ''illegal slotlink beginning with %'', mytype;
 814 end;
 815 ' language 'plpgsql';
 816 -- ************************************************************
 817 -- * Support function to clear out the slotlink field if
 818 -- * it still points to specific slot
 819 -- ************************************************************
 820 create function tg_slotlink_unset(bpchar, bpchar)
 821 returns integer as '
 822 declare
 823     myname      alias for $1;
 824     blname      alias for $2;
 825     mytype      char(2);
 826     rec         record;
 827 begin
 828     mytype := substr(myname, 1, 2);
 829     if mytype = ''PS'' then
 830         select into rec * from PSlot where slotname = myname;
 831         if not found then
 832             return 0;
 833         end if;
 834         if rec.slotlink = blname then
 835             update PSlot set slotlink = '''' where slotname = myname;
 836         end if;
 837         return 0;
 838     end if;
 839     if mytype = ''WS'' then
 840         select into rec * from WSlot where slotname = myname;
 841         if not found then
 842             return 0;
 843         end if;
 844         if rec.slotlink = blname then
 845             update WSlot set slotlink = '''' where slotname = myname;
 846         end if;
 847         return 0;
 848     end if;
 849     if mytype = ''IF'' then
 850         select into rec * from IFace where slotname = myname;
 851         if not found then
 852             return 0;
 853         end if;
 854         if rec.slotlink = blname then
 855             update IFace set slotlink = '''' where slotname = myname;
 856         end if;
 857         return 0;
 858     end if;
 859     if mytype = ''HS'' then
 860         select into rec * from HSlot where slotname = myname;
 861         if not found then
 862             return 0;
 863         end if;
 864         if rec.slotlink = blname then
 865             update HSlot set slotlink = '''' where slotname = myname;
 866         end if;
 867         return 0;
 868     end if;
 869     if mytype = ''PH'' then
 870         select into rec * from PHone where slotname = myname;
 871         if not found then
 872             return 0;
 873         end if;
 874         if rec.slotlink = blname then
 875             update PHone set slotlink = '''' where slotname = myname;
 876         end if;
 877         return 0;
 878     end if;
 879 end;
 880 ' language 'plpgsql';
 881 -- ************************************************************
 882 -- * Describe the backside of a patchfield slot
 883 -- ************************************************************
 884 create function pslot_backlink_view(bpchar)
 885 returns text as '
 886 <<outer>>
 887 declare
 888     rec         record;
 889     bltype      char(2);
 890     retval      text;
 891 begin
 892     select into rec * from PSlot where slotname = $1;
 893     if not found then
 894         return '''';
 895     end if;
 896     if rec.backlink = '''' then
 897         return ''-'';
 898     end if;
 899     bltype := substr(rec.backlink, 1, 2);
 900     if bltype = ''PL'' then
 901         declare
 902             rec         record;
 903         begin
 904             select into rec * from PLine where slotname = outer.rec.backlink;
 905             retval := ''Phone line '' || trim(rec.phonenumber);
 906             if rec.comment != '''' then
 907                 retval := retval || '' ('';
 908                 retval := retval || rec.comment;
 909                 retval := retval || '')'';
 910             end if;
 911             return retval;
 912         end;
 913     end if;
 914     if bltype = ''WS'' then
 915         select into rec * from WSlot where slotname = rec.backlink;
 916         retval := trim(rec.slotname) || '' in room '';
 917         retval := retval || trim(rec.roomno);
 918         retval := retval || '' -> '';
 919         return retval || wslot_slotlink_view(rec.slotname);
 920     end if;
 921     return rec.backlink;
 922 end;
 923 ' language 'plpgsql';
 924 -- ************************************************************
 925 -- * Describe the front of a patchfield slot
 926 -- ************************************************************
 927 create function pslot_slotlink_view(bpchar)
 928 returns text as '
 929 declare
 930     psrec       record;
 931     sltype      char(2);
 932     retval      text;
 933 begin
 934     select into psrec * from PSlot where slotname = $1;
 935     if not found then
 936         return '''';
 937     end if;
 938     if psrec.slotlink = '''' then
 939         return ''-'';
 940     end if;
 941     sltype := substr(psrec.slotlink, 1, 2);
 942     if sltype = ''PS'' then
 943         retval := trim(psrec.slotlink) || '' -> '';
 944         return retval || pslot_backlink_view(psrec.slotlink);
 945     end if;
 946     if sltype = ''HS'' then
 947         retval := comment from Hub H, HSlot HS
 948                         where HS.slotname = psrec.slotlink
 949                           and H.name = HS.hubname;
 950         retval := retval || '' slot '';
 951         retval := retval || slotno::text from HSlot
 952                         where slotname = psrec.slotlink;
 953         return retval;
 954     end if;
 955     return psrec.slotlink;
 956 end;
 957 ' language 'plpgsql';
 958 -- ************************************************************
 959 -- * Describe the front of a wall connector slot
 960 -- ************************************************************
 961 create function wslot_slotlink_view(bpchar)
 962 returns text as '
 963 declare
 964     rec         record;
 965     sltype      char(2);
 966     retval      text;
 967 begin
 968     select into rec * from WSlot where slotname = $1;
 969     if not found then
 970         return '''';
 971     end if;
 972     if rec.slotlink = '''' then
 973         return ''-'';
 974     end if;
 975     sltype := substr(rec.slotlink, 1, 2);
 976     if sltype = ''PH'' then
 977         select into rec * from PHone where slotname = rec.slotlink;
 978         retval := ''Phone '' || trim(rec.slotname);
 979         if rec.comment != '''' then
 980             retval := retval || '' ('';
 981             retval := retval || rec.comment;
 982             retval := retval || '')'';
 983         end if;
 984         return retval;
 985     end if;
 986     if sltype = ''IF'' then
 987         declare
 988             syrow       System%RowType;
 989             ifrow       IFace%ROWTYPE;
 990         begin
 991             select into ifrow * from IFace where slotname = rec.slotlink;
 992             select into syrow * from System where name = ifrow.sysname;
 993             retval := syrow.name || '' IF '';
 994             retval := retval || ifrow.ifname;
 995             if syrow.comment != '''' then
 996                 retval := retval || '' ('';
 997                 retval := retval || syrow.comment;
 998                 retval := retval || '')'';
 999             end if;
1000             return retval;
1001         end;
1002     end if;
1003     return rec.slotlink;
1004 end;
1005 ' language 'plpgsql';
1006 -- ************************************************************
1007 -- * View of a patchfield describing backside and patches
1008 -- ************************************************************
1009 create view Pfield_v1 as select PF.pfname, PF.slotname,
1010         pslot_backlink_view(PF.slotname) as backside,
1011         pslot_slotlink_view(PF.slotname) as patch
1012     from PSlot PF;
1013 --
1014 -- First we build the house - so we create the rooms
1015 --
1016 insert into Room values ('001', 'Entrance');
1017 insert into Room values ('002', 'Office');
1018 insert into Room values ('003', 'Office');
1019 insert into Room values ('004', 'Technical');
1020 insert into Room values ('101', 'Office');
1021 insert into Room values ('102', 'Conference');
1022 insert into Room values ('103', 'Restroom');
1023 insert into Room values ('104', 'Technical');
1024 insert into Room values ('105', 'Office');
1025 insert into Room values ('106', 'Office');
1026 --
1027 -- Second we install the wall connectors
1028 --
1029 insert into WSlot values ('WS.001.1a', '001', '', '');
1030 insert into WSlot values ('WS.001.1b', '001', '', '');
1031 insert into WSlot values ('WS.001.2a', '001', '', '');
1032 insert into WSlot values ('WS.001.2b', '001', '', '');
1033 insert into WSlot values ('WS.001.3a', '001', '', '');
1034 insert into WSlot values ('WS.001.3b', '001', '', '');
1035 insert into WSlot values ('WS.002.1a', '002', '', '');
1036 insert into WSlot values ('WS.002.1b', '002', '', '');
1037 insert into WSlot values ('WS.002.2a', '002', '', '');
1038 insert into WSlot values ('WS.002.2b', '002', '', '');
1039 insert into WSlot values ('WS.002.3a', '002', '', '');
1040 insert into WSlot values ('WS.002.3b', '002', '', '');
1041 insert into WSlot values ('WS.003.1a', '003', '', '');
1042 insert into WSlot values ('WS.003.1b', '003', '', '');
1043 insert into WSlot values ('WS.003.2a', '003', '', '');
1044 insert into WSlot values ('WS.003.2b', '003', '', '');
1045 insert into WSlot values ('WS.003.3a', '003', '', '');
1046 insert into WSlot values ('WS.003.3b', '003', '', '');
1047 insert into WSlot values ('WS.101.1a', '101', '', '');
1048 insert into WSlot values ('WS.101.1b', '101', '', '');
1049 insert into WSlot values ('WS.101.2a', '101', '', '');
1050 insert into WSlot values ('WS.101.2b', '101', '', '');
1051 insert into WSlot values ('WS.101.3a', '101', '', '');
1052 insert into WSlot values ('WS.101.3b', '101', '', '');
1053 insert into WSlot values ('WS.102.1a', '102', '', '');
1054 insert into WSlot values ('WS.102.1b', '102', '', '');
1055 insert into WSlot values ('WS.102.2a', '102', '', '');
1056 insert into WSlot values ('WS.102.2b', '102', '', '');
1057 insert into WSlot values ('WS.102.3a', '102', '', '');
1058 insert into WSlot values ('WS.102.3b', '102', '', '');
1059 insert into WSlot values ('WS.105.1a', '105', '', '');
1060 insert into WSlot values ('WS.105.1b', '105', '', '');
1061 insert into WSlot values ('WS.105.2a', '105', '', '');
1062 insert into WSlot values ('WS.105.2b', '105', '', '');
1063 insert into WSlot values ('WS.105.3a', '105', '', '');
1064 insert into WSlot values ('WS.105.3b', '105', '', '');
1065 insert into WSlot values ('WS.106.1a', '106', '', '');
1066 insert into WSlot values ('WS.106.1b', '106', '', '');
1067 insert into WSlot values ('WS.106.2a', '106', '', '');
1068 insert into WSlot values ('WS.106.2b', '106', '', '');
1069 insert into WSlot values ('WS.106.3a', '106', '', '');
1070 insert into WSlot values ('WS.106.3b', '106', '', '');
1071 --
1072 -- Now create the patch fields and their slots
1073 --
1074 insert into PField values ('PF0_1', 'Wallslots basement');
1075 --
1076 -- The cables for these will be made later, so they are unconnected for now
1077 --
1078 insert into PSlot values ('PS.base.a1', 'PF0_1', '', '');
1079 insert into PSlot values ('PS.base.a2', 'PF0_1', '', '');
1080 insert into PSlot values ('PS.base.a3', 'PF0_1', '', '');
1081 insert into PSlot values ('PS.base.a4', 'PF0_1', '', '');
1082 insert into PSlot values ('PS.base.a5', 'PF0_1', '', '');
1083 insert into PSlot values ('PS.base.a6', 'PF0_1', '', '');
1084 --
1085 -- These are already wired to the wall connectors
1086 --
1087 insert into PSlot values ('PS.base.b1', 'PF0_1', '', 'WS.002.1a');
1088 insert into PSlot values ('PS.base.b2', 'PF0_1', '', 'WS.002.1b');
1089 insert into PSlot values ('PS.base.b3', 'PF0_1', '', 'WS.002.2a');
1090 insert into PSlot values ('PS.base.b4', 'PF0_1', '', 'WS.002.2b');
1091 insert into PSlot values ('PS.base.b5', 'PF0_1', '', 'WS.002.3a');
1092 insert into PSlot values ('PS.base.b6', 'PF0_1', '', 'WS.002.3b');
1093 insert into PSlot values ('PS.base.c1', 'PF0_1', '', 'WS.003.1a');
1094 insert into PSlot values ('PS.base.c2', 'PF0_1', '', 'WS.003.1b');
1095 insert into PSlot values ('PS.base.c3', 'PF0_1', '', 'WS.003.2a');
1096 insert into PSlot values ('PS.base.c4', 'PF0_1', '', 'WS.003.2b');
1097 insert into PSlot values ('PS.base.c5', 'PF0_1', '', 'WS.003.3a');
1098 insert into PSlot values ('PS.base.c6', 'PF0_1', '', 'WS.003.3b');
1099 --
1100 -- This patchfield will be renamed later into PF0_2 - so its
1101 -- slots references in pfname should follow
1102 --
1103 insert into PField values ('PF0_X', 'Phonelines basement');
1104 insert into PSlot values ('PS.base.ta1', 'PF0_X', '', '');
1105 insert into PSlot values ('PS.base.ta2', 'PF0_X', '', '');
1106 insert into PSlot values ('PS.base.ta3', 'PF0_X', '', '');
1107 insert into PSlot values ('PS.base.ta4', 'PF0_X', '', '');
1108 insert into PSlot values ('PS.base.ta5', 'PF0_X', '', '');
1109 insert into PSlot values ('PS.base.ta6', 'PF0_X', '', '');
1110 insert into PSlot values ('PS.base.tb1', 'PF0_X', '', '');
1111 insert into PSlot values ('PS.base.tb2', 'PF0_X', '', '');
1112 insert into PSlot values ('PS.base.tb3', 'PF0_X', '', '');
1113 insert into PSlot values ('PS.base.tb4', 'PF0_X', '', '');
1114 insert into PSlot values ('PS.base.tb5', 'PF0_X', '', '');
1115 insert into PSlot values ('PS.base.tb6', 'PF0_X', '', '');
1116 insert into PField values ('PF1_1', 'Wallslots 1st floor');
1117 insert into PSlot values ('PS.1st.a1', 'PF1_1', '', 'WS.101.1a');
1118 insert into PSlot values ('PS.1st.a2', 'PF1_1', '', 'WS.101.1b');
1119 insert into PSlot values ('PS.1st.a3', 'PF1_1', '', 'WS.101.2a');
1120 insert into PSlot values ('PS.1st.a4', 'PF1_1', '', 'WS.101.2b');
1121 insert into PSlot values ('PS.1st.a5', 'PF1_1', '', 'WS.101.3a');
1122 insert into PSlot values ('PS.1st.a6', 'PF1_1', '', 'WS.101.3b');
1123 insert into PSlot values ('PS.1st.b1', 'PF1_1', '', 'WS.102.1a');
1124 insert into PSlot values ('PS.1st.b2', 'PF1_1', '', 'WS.102.1b');
1125 insert into PSlot values ('PS.1st.b3', 'PF1_1', '', 'WS.102.2a');
1126 insert into PSlot values ('PS.1st.b4', 'PF1_1', '', 'WS.102.2b');
1127 insert into PSlot values ('PS.1st.b5', 'PF1_1', '', 'WS.102.3a');
1128 insert into PSlot values ('PS.1st.b6', 'PF1_1', '', 'WS.102.3b');
1129 insert into PSlot values ('PS.1st.c1', 'PF1_1', '', 'WS.105.1a');
1130 insert into PSlot values ('PS.1st.c2', 'PF1_1', '', 'WS.105.1b');
1131 insert into PSlot values ('PS.1st.c3', 'PF1_1', '', 'WS.105.2a');
1132 insert into PSlot values ('PS.1st.c4', 'PF1_1', '', 'WS.105.2b');
1133 insert into PSlot values ('PS.1st.c5', 'PF1_1', '', 'WS.105.3a');
1134 insert into PSlot values ('PS.1st.c6', 'PF1_1', '', 'WS.105.3b');
1135 insert into PSlot values ('PS.1st.d1', 'PF1_1', '', 'WS.106.1a');
1136 insert into PSlot values ('PS.1st.d2', 'PF1_1', '', 'WS.106.1b');
1137 insert into PSlot values ('PS.1st.d3', 'PF1_1', '', 'WS.106.2a');
1138 insert into PSlot values ('PS.1st.d4', 'PF1_1', '', 'WS.106.2b');
1139 insert into PSlot values ('PS.1st.d5', 'PF1_1', '', 'WS.106.3a');
1140 insert into PSlot values ('PS.1st.d6', 'PF1_1', '', 'WS.106.3b');
1141 --
1142 -- Now we wire the wall connectors 1a-2a in room 001 to the
1143 -- patchfield. In the second update we make an error, and
1144 -- correct it after
1145 --
1146 update PSlot set backlink = 'WS.001.1a' where slotname = 'PS.base.a1';
1147 update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a3';
1148 select * from WSlot where roomno = '001' order by slotname;
1149        slotname       |  roomno  |       slotlink       |       backlink
1150 ----------------------+----------+----------------------+----------------------
1151  WS.001.1a            | 001      |                      | PS.base.a1
1152  WS.001.1b            | 001      |                      | PS.base.a3
1153  WS.001.2a            | 001      |                      |
1154  WS.001.2b            | 001      |                      |
1155  WS.001.3a            | 001      |                      |
1156  WS.001.3b            | 001      |                      |
1157 (6 rows)
1158
1159 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1160        slotname       | pfname |       slotlink       |       backlink
1161 ----------------------+--------+----------------------+----------------------
1162  PS.base.a1           | PF0_1  |                      | WS.001.1a
1163  PS.base.a2           | PF0_1  |                      |
1164  PS.base.a3           | PF0_1  |                      | WS.001.1b
1165  PS.base.a4           | PF0_1  |                      |
1166  PS.base.a5           | PF0_1  |                      |
1167  PS.base.a6           | PF0_1  |                      |
1168 (6 rows)
1169
1170 update PSlot set backlink = 'WS.001.2a' where slotname = 'PS.base.a3';
1171 select * from WSlot where roomno = '001' order by slotname;
1172        slotname       |  roomno  |       slotlink       |       backlink
1173 ----------------------+----------+----------------------+----------------------
1174  WS.001.1a            | 001      |                      | PS.base.a1
1175  WS.001.1b            | 001      |                      |
1176  WS.001.2a            | 001      |                      | PS.base.a3
1177  WS.001.2b            | 001      |                      |
1178  WS.001.3a            | 001      |                      |
1179  WS.001.3b            | 001      |                      |
1180 (6 rows)
1181
1182 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1183        slotname       | pfname |       slotlink       |       backlink
1184 ----------------------+--------+----------------------+----------------------
1185  PS.base.a1           | PF0_1  |                      | WS.001.1a
1186  PS.base.a2           | PF0_1  |                      |
1187  PS.base.a3           | PF0_1  |                      | WS.001.2a
1188  PS.base.a4           | PF0_1  |                      |
1189  PS.base.a5           | PF0_1  |                      |
1190  PS.base.a6           | PF0_1  |                      |
1191 (6 rows)
1192
1193 update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a2';
1194 select * from WSlot where roomno = '001' order by slotname;
1195        slotname       |  roomno  |       slotlink       |       backlink
1196 ----------------------+----------+----------------------+----------------------
1197  WS.001.1a            | 001      |                      | PS.base.a1
1198  WS.001.1b            | 001      |                      | PS.base.a2
1199  WS.001.2a            | 001      |                      | PS.base.a3
1200  WS.001.2b            | 001      |                      |
1201  WS.001.3a            | 001      |                      |
1202  WS.001.3b            | 001      |                      |
1203 (6 rows)
1204
1205 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1206        slotname       | pfname |       slotlink       |       backlink
1207 ----------------------+--------+----------------------+----------------------
1208  PS.base.a1           | PF0_1  |                      | WS.001.1a
1209  PS.base.a2           | PF0_1  |                      | WS.001.1b
1210  PS.base.a3           | PF0_1  |                      | WS.001.2a
1211  PS.base.a4           | PF0_1  |                      |
1212  PS.base.a5           | PF0_1  |                      |
1213  PS.base.a6           | PF0_1  |                      |
1214 (6 rows)
1215
1216 --
1217 -- Same procedure for 2b-3b but this time updating the WSlot instead
1218 -- of the PSlot. Due to the triggers the result is the same:
1219 -- WSlot and corresponding PSlot point to each other.
1220 --
1221 update WSlot set backlink = 'PS.base.a4' where slotname = 'WS.001.2b';
1222 update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3a';
1223 select * from WSlot where roomno = '001' order by slotname;
1224        slotname       |  roomno  |       slotlink       |       backlink
1225 ----------------------+----------+----------------------+----------------------
1226  WS.001.1a            | 001      |                      | PS.base.a1
1227  WS.001.1b            | 001      |                      | PS.base.a2
1228  WS.001.2a            | 001      |                      | PS.base.a3
1229  WS.001.2b            | 001      |                      | PS.base.a4
1230  WS.001.3a            | 001      |                      | PS.base.a6
1231  WS.001.3b            | 001      |                      |
1232 (6 rows)
1233
1234 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1235        slotname       | pfname |       slotlink       |       backlink
1236 ----------------------+--------+----------------------+----------------------
1237  PS.base.a1           | PF0_1  |                      | WS.001.1a
1238  PS.base.a2           | PF0_1  |                      | WS.001.1b
1239  PS.base.a3           | PF0_1  |                      | WS.001.2a
1240  PS.base.a4           | PF0_1  |                      | WS.001.2b
1241  PS.base.a5           | PF0_1  |                      |
1242  PS.base.a6           | PF0_1  |                      | WS.001.3a
1243 (6 rows)
1244
1245 update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3b';
1246 select * from WSlot where roomno = '001' order by slotname;
1247        slotname       |  roomno  |       slotlink       |       backlink
1248 ----------------------+----------+----------------------+----------------------
1249  WS.001.1a            | 001      |                      | PS.base.a1
1250  WS.001.1b            | 001      |                      | PS.base.a2
1251  WS.001.2a            | 001      |                      | PS.base.a3
1252  WS.001.2b            | 001      |                      | PS.base.a4
1253  WS.001.3a            | 001      |                      |
1254  WS.001.3b            | 001      |                      | PS.base.a6
1255 (6 rows)
1256
1257 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1258        slotname       | pfname |       slotlink       |       backlink
1259 ----------------------+--------+----------------------+----------------------
1260  PS.base.a1           | PF0_1  |                      | WS.001.1a
1261  PS.base.a2           | PF0_1  |                      | WS.001.1b
1262  PS.base.a3           | PF0_1  |                      | WS.001.2a
1263  PS.base.a4           | PF0_1  |                      | WS.001.2b
1264  PS.base.a5           | PF0_1  |                      |
1265  PS.base.a6           | PF0_1  |                      | WS.001.3b
1266 (6 rows)
1267
1268 update WSlot set backlink = 'PS.base.a5' where slotname = 'WS.001.3a';
1269 select * from WSlot where roomno = '001' order by slotname;
1270        slotname       |  roomno  |       slotlink       |       backlink
1271 ----------------------+----------+----------------------+----------------------
1272  WS.001.1a            | 001      |                      | PS.base.a1
1273  WS.001.1b            | 001      |                      | PS.base.a2
1274  WS.001.2a            | 001      |                      | PS.base.a3
1275  WS.001.2b            | 001      |                      | PS.base.a4
1276  WS.001.3a            | 001      |                      | PS.base.a5
1277  WS.001.3b            | 001      |                      | PS.base.a6
1278 (6 rows)
1279
1280 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1281        slotname       | pfname |       slotlink       |       backlink
1282 ----------------------+--------+----------------------+----------------------
1283  PS.base.a1           | PF0_1  |                      | WS.001.1a
1284  PS.base.a2           | PF0_1  |                      | WS.001.1b
1285  PS.base.a3           | PF0_1  |                      | WS.001.2a
1286  PS.base.a4           | PF0_1  |                      | WS.001.2b
1287  PS.base.a5           | PF0_1  |                      | WS.001.3a
1288  PS.base.a6           | PF0_1  |                      | WS.001.3b
1289 (6 rows)
1290
1291 insert into PField values ('PF1_2', 'Phonelines 1st floor');
1292 insert into PSlot values ('PS.1st.ta1', 'PF1_2', '', '');
1293 insert into PSlot values ('PS.1st.ta2', 'PF1_2', '', '');
1294 insert into PSlot values ('PS.1st.ta3', 'PF1_2', '', '');
1295 insert into PSlot values ('PS.1st.ta4', 'PF1_2', '', '');
1296 insert into PSlot values ('PS.1st.ta5', 'PF1_2', '', '');
1297 insert into PSlot values ('PS.1st.ta6', 'PF1_2', '', '');
1298 insert into PSlot values ('PS.1st.tb1', 'PF1_2', '', '');
1299 insert into PSlot values ('PS.1st.tb2', 'PF1_2', '', '');
1300 insert into PSlot values ('PS.1st.tb3', 'PF1_2', '', '');
1301 insert into PSlot values ('PS.1st.tb4', 'PF1_2', '', '');
1302 insert into PSlot values ('PS.1st.tb5', 'PF1_2', '', '');
1303 insert into PSlot values ('PS.1st.tb6', 'PF1_2', '', '');
1304 --
1305 -- Fix the wrong name for patchfield PF0_2
1306 --
1307 update PField set name = 'PF0_2' where name = 'PF0_X';
1308 select * from PSlot order by slotname;
1309        slotname       | pfname |       slotlink       |       backlink
1310 ----------------------+--------+----------------------+----------------------
1311  PS.1st.a1            | PF1_1  |                      | WS.101.1a
1312  PS.1st.a2            | PF1_1  |                      | WS.101.1b
1313  PS.1st.a3            | PF1_1  |                      | WS.101.2a
1314  PS.1st.a4            | PF1_1  |                      | WS.101.2b
1315  PS.1st.a5            | PF1_1  |                      | WS.101.3a
1316  PS.1st.a6            | PF1_1  |                      | WS.101.3b
1317  PS.1st.b1            | PF1_1  |                      | WS.102.1a
1318  PS.1st.b2            | PF1_1  |                      | WS.102.1b
1319  PS.1st.b3            | PF1_1  |                      | WS.102.2a
1320  PS.1st.b4            | PF1_1  |                      | WS.102.2b
1321  PS.1st.b5            | PF1_1  |                      | WS.102.3a
1322  PS.1st.b6            | PF1_1  |                      | WS.102.3b
1323  PS.1st.c1            | PF1_1  |                      | WS.105.1a
1324  PS.1st.c2            | PF1_1  |                      | WS.105.1b
1325  PS.1st.c3            | PF1_1  |                      | WS.105.2a
1326  PS.1st.c4            | PF1_1  |                      | WS.105.2b
1327  PS.1st.c5            | PF1_1  |                      | WS.105.3a
1328  PS.1st.c6            | PF1_1  |                      | WS.105.3b
1329  PS.1st.d1            | PF1_1  |                      | WS.106.1a
1330  PS.1st.d2            | PF1_1  |                      | WS.106.1b
1331  PS.1st.d3            | PF1_1  |                      | WS.106.2a
1332  PS.1st.d4            | PF1_1  |                      | WS.106.2b
1333  PS.1st.d5            | PF1_1  |                      | WS.106.3a
1334  PS.1st.d6            | PF1_1  |                      | WS.106.3b
1335  PS.1st.ta1           | PF1_2  |                      |
1336  PS.1st.ta2           | PF1_2  |                      |
1337  PS.1st.ta3           | PF1_2  |                      |
1338  PS.1st.ta4           | PF1_2  |                      |
1339  PS.1st.ta5           | PF1_2  |                      |
1340  PS.1st.ta6           | PF1_2  |                      |
1341  PS.1st.tb1           | PF1_2  |                      |
1342  PS.1st.tb2           | PF1_2  |                      |
1343  PS.1st.tb3           | PF1_2  |                      |
1344  PS.1st.tb4           | PF1_2  |                      |
1345  PS.1st.tb5           | PF1_2  |                      |
1346  PS.1st.tb6           | PF1_2  |                      |
1347  PS.base.a1           | PF0_1  |                      | WS.001.1a
1348  PS.base.a2           | PF0_1  |                      | WS.001.1b
1349  PS.base.a3           | PF0_1  |                      | WS.001.2a
1350  PS.base.a4           | PF0_1  |                      | WS.001.2b
1351  PS.base.a5           | PF0_1  |                      | WS.001.3a
1352  PS.base.a6           | PF0_1  |                      | WS.001.3b
1353  PS.base.b1           | PF0_1  |                      | WS.002.1a
1354  PS.base.b2           | PF0_1  |                      | WS.002.1b
1355  PS.base.b3           | PF0_1  |                      | WS.002.2a
1356  PS.base.b4           | PF0_1  |                      | WS.002.2b
1357  PS.base.b5           | PF0_1  |                      | WS.002.3a
1358  PS.base.b6           | PF0_1  |                      | WS.002.3b
1359  PS.base.c1           | PF0_1  |                      | WS.003.1a
1360  PS.base.c2           | PF0_1  |                      | WS.003.1b
1361  PS.base.c3           | PF0_1  |                      | WS.003.2a
1362  PS.base.c4           | PF0_1  |                      | WS.003.2b
1363  PS.base.c5           | PF0_1  |                      | WS.003.3a
1364  PS.base.c6           | PF0_1  |                      | WS.003.3b
1365  PS.base.ta1          | PF0_2  |                      |
1366  PS.base.ta2          | PF0_2  |                      |
1367  PS.base.ta3          | PF0_2  |                      |
1368  PS.base.ta4          | PF0_2  |                      |
1369  PS.base.ta5          | PF0_2  |                      |
1370  PS.base.ta6          | PF0_2  |                      |
1371  PS.base.tb1          | PF0_2  |                      |
1372  PS.base.tb2          | PF0_2  |                      |
1373  PS.base.tb3          | PF0_2  |                      |
1374  PS.base.tb4          | PF0_2  |                      |
1375  PS.base.tb5          | PF0_2  |                      |
1376  PS.base.tb6          | PF0_2  |                      |
1377 (66 rows)
1378
1379 select * from WSlot order by slotname;
1380        slotname       |  roomno  |       slotlink       |       backlink
1381 ----------------------+----------+----------------------+----------------------
1382  WS.001.1a            | 001      |                      | PS.base.a1
1383  WS.001.1b            | 001      |                      | PS.base.a2
1384  WS.001.2a            | 001      |                      | PS.base.a3
1385  WS.001.2b            | 001      |                      | PS.base.a4
1386  WS.001.3a            | 001      |                      | PS.base.a5
1387  WS.001.3b            | 001      |                      | PS.base.a6
1388  WS.002.1a            | 002      |                      | PS.base.b1
1389  WS.002.1b            | 002      |                      | PS.base.b2
1390  WS.002.2a            | 002      |                      | PS.base.b3
1391  WS.002.2b            | 002      |                      | PS.base.b4
1392  WS.002.3a            | 002      |                      | PS.base.b5
1393  WS.002.3b            | 002      |                      | PS.base.b6
1394  WS.003.1a            | 003      |                      | PS.base.c1
1395  WS.003.1b            | 003      |                      | PS.base.c2
1396  WS.003.2a            | 003      |                      | PS.base.c3
1397  WS.003.2b            | 003      |                      | PS.base.c4
1398  WS.003.3a            | 003      |                      | PS.base.c5
1399  WS.003.3b            | 003      |                      | PS.base.c6
1400  WS.101.1a            | 101      |                      | PS.1st.a1
1401  WS.101.1b            | 101      |                      | PS.1st.a2
1402  WS.101.2a            | 101      |                      | PS.1st.a3
1403  WS.101.2b            | 101      |                      | PS.1st.a4
1404  WS.101.3a            | 101      |                      | PS.1st.a5
1405  WS.101.3b            | 101      |                      | PS.1st.a6
1406  WS.102.1a            | 102      |                      | PS.1st.b1
1407  WS.102.1b            | 102      |                      | PS.1st.b2
1408  WS.102.2a            | 102      |                      | PS.1st.b3
1409  WS.102.2b            | 102      |                      | PS.1st.b4
1410  WS.102.3a            | 102      |                      | PS.1st.b5
1411  WS.102.3b            | 102      |                      | PS.1st.b6
1412  WS.105.1a            | 105      |                      | PS.1st.c1
1413  WS.105.1b            | 105      |                      | PS.1st.c2
1414  WS.105.2a            | 105      |                      | PS.1st.c3
1415  WS.105.2b            | 105      |                      | PS.1st.c4
1416  WS.105.3a            | 105      |                      | PS.1st.c5
1417  WS.105.3b            | 105      |                      | PS.1st.c6
1418  WS.106.1a            | 106      |                      | PS.1st.d1
1419  WS.106.1b            | 106      |                      | PS.1st.d2
1420  WS.106.2a            | 106      |                      | PS.1st.d3
1421  WS.106.2b            | 106      |                      | PS.1st.d4
1422  WS.106.3a            | 106      |                      | PS.1st.d5
1423  WS.106.3b            | 106      |                      | PS.1st.d6
1424 (42 rows)
1425
1426 --
1427 -- Install the central phone system and create the phone numbers.
1428 -- They are weired on insert to the patchfields. Again the
1429 -- triggers automatically tell the PSlots to update their
1430 -- backlink field.
1431 --
1432 insert into PLine values ('PL.001', '-0', 'Central call', 'PS.base.ta1');
1433 insert into PLine values ('PL.002', '-101', '', 'PS.base.ta2');
1434 insert into PLine values ('PL.003', '-102', '', 'PS.base.ta3');
1435 insert into PLine values ('PL.004', '-103', '', 'PS.base.ta5');
1436 insert into PLine values ('PL.005', '-104', '', 'PS.base.ta6');
1437 insert into PLine values ('PL.006', '-106', '', 'PS.base.tb2');
1438 insert into PLine values ('PL.007', '-108', '', 'PS.base.tb3');
1439 insert into PLine values ('PL.008', '-109', '', 'PS.base.tb4');
1440 insert into PLine values ('PL.009', '-121', '', 'PS.base.tb5');
1441 insert into PLine values ('PL.010', '-122', '', 'PS.base.tb6');
1442 insert into PLine values ('PL.015', '-134', '', 'PS.1st.ta1');
1443 insert into PLine values ('PL.016', '-137', '', 'PS.1st.ta3');
1444 insert into PLine values ('PL.017', '-139', '', 'PS.1st.ta4');
1445 insert into PLine values ('PL.018', '-362', '', 'PS.1st.tb1');
1446 insert into PLine values ('PL.019', '-363', '', 'PS.1st.tb2');
1447 insert into PLine values ('PL.020', '-364', '', 'PS.1st.tb3');
1448 insert into PLine values ('PL.021', '-365', '', 'PS.1st.tb5');
1449 insert into PLine values ('PL.022', '-367', '', 'PS.1st.tb6');
1450 insert into PLine values ('PL.028', '-501', 'Fax entrance', 'PS.base.ta2');
1451 insert into PLine values ('PL.029', '-502', 'Fax 1st floor', 'PS.1st.ta1');
1452 --
1453 -- Buy some phones, plug them into the wall and patch the
1454 -- phone lines to the corresponding patchfield slots.
1455 --
1456 insert into PHone values ('PH.hc001', 'Hicom standard', 'WS.001.1a');
1457 update PSlot set slotlink = 'PS.base.ta1' where slotname = 'PS.base.a1';
1458 insert into PHone values ('PH.hc002', 'Hicom standard', 'WS.002.1a');
1459 update PSlot set slotlink = 'PS.base.ta5' where slotname = 'PS.base.b1';
1460 insert into PHone values ('PH.hc003', 'Hicom standard', 'WS.002.2a');
1461 update PSlot set slotlink = 'PS.base.tb2' where slotname = 'PS.base.b3';
1462 insert into PHone values ('PH.fax001', 'Canon fax', 'WS.001.2a');
1463 update PSlot set slotlink = 'PS.base.ta2' where slotname = 'PS.base.a3';
1464 --
1465 -- Install a hub at one of the patchfields, plug a computers
1466 -- ethernet interface into the wall and patch it to the hub.
1467 --
1468 insert into Hub values ('base.hub1', 'Patchfield PF0_1 hub', 16);
1469 insert into System values ('orion', 'PC');
1470 insert into IFace values ('IF', 'orion', 'eth0', 'WS.002.1b');
1471 update PSlot set slotlink = 'HS.base.hub1.1' where slotname = 'PS.base.b2';
1472 --
1473 -- Now we take a look at the patchfield
1474 --
1475 select * from PField_v1 where pfname = 'PF0_1' order by slotname;
1476  pfname |       slotname       |                         backside                         |                     patch
1477 --------+----------------------+----------------------------------------------------------+-----------------------------------------------
1478  PF0_1  | PS.base.a1           | WS.001.1a in room 001 -> Phone PH.hc001 (Hicom standard) | PS.base.ta1 -> Phone line -0 (Central call)
1479  PF0_1  | PS.base.a2           | WS.001.1b in room 001 -> -                               | -
1480  PF0_1  | PS.base.a3           | WS.001.2a in room 001 -> Phone PH.fax001 (Canon fax)     | PS.base.ta2 -> Phone line -501 (Fax entrance)
1481  PF0_1  | PS.base.a4           | WS.001.2b in room 001 -> -                               | -
1482  PF0_1  | PS.base.a5           | WS.001.3a in room 001 -> -                               | -
1483  PF0_1  | PS.base.a6           | WS.001.3b in room 001 -> -                               | -
1484  PF0_1  | PS.base.b1           | WS.002.1a in room 002 -> Phone PH.hc002 (Hicom standard) | PS.base.ta5 -> Phone line -103
1485  PF0_1  | PS.base.b2           | WS.002.1b in room 002 -> orion IF eth0 (PC)              | Patchfield PF0_1 hub slot 1
1486  PF0_1  | PS.base.b3           | WS.002.2a in room 002 -> Phone PH.hc003 (Hicom standard) | PS.base.tb2 -> Phone line -106
1487  PF0_1  | PS.base.b4           | WS.002.2b in room 002 -> -                               | -
1488  PF0_1  | PS.base.b5           | WS.002.3a in room 002 -> -                               | -
1489  PF0_1  | PS.base.b6           | WS.002.3b in room 002 -> -                               | -
1490  PF0_1  | PS.base.c1           | WS.003.1a in room 003 -> -                               | -
1491  PF0_1  | PS.base.c2           | WS.003.1b in room 003 -> -                               | -
1492  PF0_1  | PS.base.c3           | WS.003.2a in room 003 -> -                               | -
1493  PF0_1  | PS.base.c4           | WS.003.2b in room 003 -> -                               | -
1494  PF0_1  | PS.base.c5           | WS.003.3a in room 003 -> -                               | -
1495  PF0_1  | PS.base.c6           | WS.003.3b in room 003 -> -                               | -
1496 (18 rows)
1497
1498 select * from PField_v1 where pfname = 'PF0_2' order by slotname;
1499  pfname |       slotname       |            backside            |                                 patch
1500 --------+----------------------+--------------------------------+------------------------------------------------------------------------
1501  PF0_2  | PS.base.ta1          | Phone line -0 (Central call)   | PS.base.a1 -> WS.001.1a in room 001 -> Phone PH.hc001 (Hicom standard)
1502  PF0_2  | PS.base.ta2          | Phone line -501 (Fax entrance) | PS.base.a3 -> WS.001.2a in room 001 -> Phone PH.fax001 (Canon fax)
1503  PF0_2  | PS.base.ta3          | Phone line -102                | -
1504  PF0_2  | PS.base.ta4          | -                              | -
1505  PF0_2  | PS.base.ta5          | Phone line -103                | PS.base.b1 -> WS.002.1a in room 002 -> Phone PH.hc002 (Hicom standard)
1506  PF0_2  | PS.base.ta6          | Phone line -104                | -
1507  PF0_2  | PS.base.tb1          | -                              | -
1508  PF0_2  | PS.base.tb2          | Phone line -106                | PS.base.b3 -> WS.002.2a in room 002 -> Phone PH.hc003 (Hicom standard)
1509  PF0_2  | PS.base.tb3          | Phone line -108                | -
1510  PF0_2  | PS.base.tb4          | Phone line -109                | -
1511  PF0_2  | PS.base.tb5          | Phone line -121                | -
1512  PF0_2  | PS.base.tb6          | Phone line -122                | -
1513 (12 rows)
1514
1515 --
1516 -- Finally we want errors
1517 --
1518 insert into PField values ('PF1_1', 'should fail due to unique index');
1519 ERROR:  duplicate key violates unique constraint "pfield_name"
1520 update PSlot set backlink = 'WS.not.there' where slotname = 'PS.base.a1';
1521 ERROR:  WS.not.there         does not exist
1522 CONTEXT:  PL/pgSQL function "tg_backlink_a" line 16 at assignment
1523 update PSlot set backlink = 'XX.illegal' where slotname = 'PS.base.a1';
1524 ERROR:  illegal backlink beginning with XX
1525 CONTEXT:  PL/pgSQL function "tg_backlink_a" line 16 at assignment
1526 update PSlot set slotlink = 'PS.not.there' where slotname = 'PS.base.a1';
1527 ERROR:  PS.not.there         does not exist
1528 CONTEXT:  PL/pgSQL function "tg_slotlink_a" line 16 at assignment
1529 update PSlot set slotlink = 'XX.illegal' where slotname = 'PS.base.a1';
1530 ERROR:  illegal slotlink beginning with XX
1531 CONTEXT:  PL/pgSQL function "tg_slotlink_a" line 16 at assignment
1532 insert into HSlot values ('HS', 'base.hub1', 1, '');
1533 ERROR:  duplicate key violates unique constraint "hslot_name"
1534 insert into HSlot values ('HS', 'base.hub1', 20, '');
1535 ERROR:  no manual manipulation of HSlot
1536 delete from HSlot;
1537 ERROR:  no manual manipulation of HSlot
1538 insert into IFace values ('IF', 'notthere', 'eth0', '');
1539 ERROR:  system "notthere" does not exist
1540 insert into IFace values ('IF', 'orion', 'ethernet_interface_name_too_long', '');
1541 ERROR:  IFace slotname "IF.orion.ethernet_interface_name_too_long" too long (20 char max)
1542 --
1543 -- The following tests are unrelated to the scenario outlined above;
1544 -- they merely exercise specific parts of PL/PgSQL
1545 --
1546 --
1547 -- Test recursion, per bug report 7-Sep-01
1548 --
1549 CREATE FUNCTION recursion_test(int,int) RETURNS text AS '
1550 DECLARE rslt text;
1551 BEGIN
1552     IF $1 <= 0 THEN
1553         rslt = CAST($2 AS TEXT);
1554     ELSE
1555         rslt = CAST($1 AS TEXT) || '','' || recursion_test($1 - 1, $2);
1556     END IF;
1557     RETURN rslt;
1558 END;' LANGUAGE 'plpgsql';
1559 SELECT recursion_test(4,3);
1560  recursion_test
1561 ----------------
1562  4,3,2,1,3
1563 (1 row)
1564
1565 --
1566 -- Test the FOUND magic variable
1567 --
1568 CREATE TABLE found_test_tbl (a int);
1569 create function test_found()
1570   returns boolean as '
1571   declare
1572   begin
1573   insert into found_test_tbl values (1);
1574   if FOUND then
1575      insert into found_test_tbl values (2);
1576   end if;
1577
1578   update found_test_tbl set a = 100 where a = 1;
1579   if FOUND then
1580     insert into found_test_tbl values (3);
1581   end if;
1582
1583   delete from found_test_tbl where a = 9999; -- matches no rows
1584   if not FOUND then
1585     insert into found_test_tbl values (4);
1586   end if;
1587
1588   for i in 1 .. 10 loop
1589     -- no need to do anything
1590   end loop;
1591   if FOUND then
1592     insert into found_test_tbl values (5);
1593   end if;
1594
1595   -- never executes the loop
1596   for i in 2 .. 1 loop
1597     -- no need to do anything
1598   end loop;
1599   if not FOUND then
1600     insert into found_test_tbl values (6);
1601   end if;
1602   return true;
1603   end;' language 'plpgsql';
1604 select test_found();
1605  test_found
1606 ------------
1607  t
1608 (1 row)
1609
1610 select * from found_test_tbl;
1611   a
1612 -----
1613    2
1614  100
1615    3
1616    4
1617    5
1618    6
1619 (6 rows)
1620
1621 --
1622 -- Test set-returning functions for PL/pgSQL
1623 --
1624 create function test_table_func_rec() returns setof found_test_tbl as '
1625 DECLARE
1626         rec RECORD;
1627 BEGIN
1628         FOR rec IN select * from found_test_tbl LOOP
1629                 RETURN NEXT rec;
1630         END LOOP;
1631         RETURN;
1632 END;' language 'plpgsql';
1633 select * from test_table_func_rec();
1634   a
1635 -----
1636    2
1637  100
1638    3
1639    4
1640    5
1641    6
1642 (6 rows)
1643
1644 create function test_table_func_row() returns setof found_test_tbl as '
1645 DECLARE
1646         row found_test_tbl%ROWTYPE;
1647 BEGIN
1648         FOR row IN select * from found_test_tbl LOOP
1649                 RETURN NEXT row;
1650         END LOOP;
1651         RETURN;
1652 END;' language 'plpgsql';
1653 select * from test_table_func_row();
1654   a
1655 -----
1656    2
1657  100
1658    3
1659    4
1660    5
1661    6
1662 (6 rows)
1663
1664 create function test_ret_set_scalar(int,int) returns setof int as '
1665 DECLARE
1666         i int;
1667 BEGIN
1668         FOR i IN $1 .. $2 LOOP
1669                 RETURN NEXT i + 1;
1670         END LOOP;
1671         RETURN;
1672 END;' language 'plpgsql';
1673 select * from test_ret_set_scalar(1,10);
1674  test_ret_set_scalar
1675 ---------------------
1676                    2
1677                    3
1678                    4
1679                    5
1680                    6
1681                    7
1682                    8
1683                    9
1684                   10
1685                   11
1686 (10 rows)
1687
1688 create function test_ret_set_rec_dyn(int) returns setof record as '
1689 DECLARE
1690         retval RECORD;
1691 BEGIN
1692         IF $1 > 10 THEN
1693                 SELECT INTO retval 5, 10, 15;
1694                 RETURN NEXT retval;
1695                 RETURN NEXT retval;
1696         ELSE
1697                 SELECT INTO retval 50, 5::numeric, ''xxx''::text;
1698                 RETURN NEXT retval;
1699                 RETURN NEXT retval;
1700         END IF;
1701         RETURN;
1702 END;' language 'plpgsql';
1703 SELECT * FROM test_ret_set_rec_dyn(1500) AS (a int, b int, c int);
1704  a | b  | c
1705 ---+----+----
1706  5 | 10 | 15
1707  5 | 10 | 15
1708 (2 rows)
1709
1710 SELECT * FROM test_ret_set_rec_dyn(5) AS (a int, b numeric, c text);
1711  a  | b |  c
1712 ----+---+-----
1713  50 | 5 | xxx
1714  50 | 5 | xxx
1715 (2 rows)
1716
1717 create function test_ret_rec_dyn(int) returns record as '
1718 DECLARE
1719         retval RECORD;
1720 BEGIN
1721         IF $1 > 10 THEN
1722                 SELECT INTO retval 5, 10, 15;
1723                 RETURN retval;
1724         ELSE
1725                 SELECT INTO retval 50, 5::numeric, ''xxx''::text;
1726                 RETURN retval;
1727         END IF;
1728 END;' language 'plpgsql';
1729 SELECT * FROM test_ret_rec_dyn(1500) AS (a int, b int, c int);
1730  a | b  | c
1731 ---+----+----
1732  5 | 10 | 15
1733 (1 row)
1734
1735 SELECT * FROM test_ret_rec_dyn(5) AS (a int, b numeric, c text);
1736  a  | b |  c
1737 ----+---+-----
1738  50 | 5 | xxx
1739 (1 row)
1740
1741 --
1742 -- Test handling of OUT parameters, including polymorphic cases.
1743 -- Note that RETURN is optional with OUT params; we try both ways.
1744 --
1745 -- wrong way to do it:
1746 create function f1(in i int, out j int) returns int as $$
1747 begin
1748   return i+1;
1749 end$$ language plpgsql;
1750 ERROR:  RETURN cannot have a parameter in function with OUT parameters at or near "i" at character 74
1751 LINE 3:   return i+1;
1752                  ^
1753 create function f1(in i int, out j int) as $$
1754 begin
1755   j := i+1;
1756   return;
1757 end$$ language plpgsql;
1758 select f1(42);
1759  f1
1760 ----
1761  43
1762 (1 row)
1763
1764 select * from f1(42);
1765  f1
1766 ----
1767  43
1768 (1 row)
1769
1770 create or replace function f1(inout i int) as $$
1771 begin
1772   i := i+1;
1773 end$$ language plpgsql;
1774 select f1(42);
1775  f1
1776 ----
1777  43
1778 (1 row)
1779
1780 select * from f1(42);
1781  f1
1782 ----
1783  43
1784 (1 row)
1785
1786 drop function f1(int);
1787 create function f1(in i int, out j int) returns setof int as $$
1788 begin
1789   j := i+1;
1790   return next;
1791   j := i+2;
1792   return next;
1793   return;
1794 end$$ language plpgsql;
1795 select * from f1(42);
1796  f1
1797 ----
1798  43
1799  44
1800 (2 rows)
1801
1802 drop function f1(int);
1803 create function f1(in i int, out j int, out k text) as $$
1804 begin
1805   j := i;
1806   j := j+1;
1807   k := 'foo';
1808 end$$ language plpgsql;
1809 select f1(42);
1810     f1
1811 ----------
1812  (43,foo)
1813 (1 row)
1814
1815 select * from f1(42);
1816  j  |  k
1817 ----+-----
1818  43 | foo
1819 (1 row)
1820
1821 drop function f1(int);
1822 create function f1(in i int, out j int, out k text) returns setof record as $$
1823 begin
1824   j := i+1;
1825   k := 'foo';
1826   return next;
1827   j := j+1;
1828   k := 'foot';
1829   return next;
1830 end$$ language plpgsql;
1831 select * from f1(42);
1832  j  |  k
1833 ----+------
1834  43 | foo
1835  44 | foot
1836 (2 rows)
1837
1838 drop function f1(int);
1839 create function dup(in i anyelement, out j anyelement, out k anyarray) as $$
1840 begin
1841   j := i;
1842   k := array[j,j];
1843   return;
1844 end$$ language plpgsql;
1845 select * from dup(42);
1846  j  |    k
1847 ----+---------
1848  42 | {42,42}
1849 (1 row)
1850
1851 select * from dup('foo'::text);
1852   j  |     k
1853 -----+-----------
1854  foo | {foo,foo}
1855 (1 row)
1856
1857 drop function dup(anyelement);
1858 --
1859 -- test PERFORM
1860 --
1861 create table perform_test (
1862         a       INT,
1863         b       INT
1864 );
1865 create function simple_func(int) returns boolean as '
1866 BEGIN
1867         IF $1 < 20 THEN
1868                 INSERT INTO perform_test VALUES ($1, $1 + 10);
1869                 RETURN TRUE;
1870         ELSE
1871                 RETURN FALSE;
1872         END IF;
1873 END;' language 'plpgsql';
1874 create function perform_test_func() returns void as '
1875 BEGIN
1876         IF FOUND then
1877                 INSERT INTO perform_test VALUES (100, 100);
1878         END IF;
1879
1880         PERFORM simple_func(5);
1881
1882         IF FOUND then
1883                 INSERT INTO perform_test VALUES (100, 100);
1884         END IF;
1885
1886         PERFORM simple_func(50);
1887
1888         IF FOUND then
1889                 INSERT INTO perform_test VALUES (100, 100);
1890         END IF;
1891
1892         RETURN;
1893 END;' language 'plpgsql';
1894 SELECT perform_test_func();
1895  perform_test_func
1896 -------------------
1897
1898 (1 row)
1899
1900 SELECT * FROM perform_test;
1901   a  |  b
1902 -----+-----
1903    5 |  15
1904  100 | 100
1905  100 | 100
1906 (3 rows)
1907
1908 drop table perform_test;
1909 --
1910 -- Test error trapping
1911 --
1912 create function trap_zero_divide(int) returns int as $$
1913 declare x int;
1914         sx smallint;
1915 begin
1916         begin   -- start a subtransaction
1917                 raise notice 'should see this';
1918                 x := 100 / $1;
1919                 raise notice 'should see this only if % <> 0', $1;
1920                 sx := $1;
1921                 raise notice 'should see this only if % fits in smallint', $1;
1922                 if $1 < 0 then
1923                         raise exception '% is less than zero', $1;
1924                 end if;
1925         exception
1926                 when division_by_zero then
1927                         raise notice 'caught division_by_zero';
1928                         x := -1;
1929                 when NUMERIC_VALUE_OUT_OF_RANGE then
1930                         raise notice 'caught numeric_value_out_of_range';
1931                         x := -2;
1932         end;
1933         return x;
1934 end$$ language plpgsql;
1935 select trap_zero_divide(50);
1936 NOTICE:  should see this
1937 NOTICE:  should see this only if 50 <> 0
1938 NOTICE:  should see this only if 50 fits in smallint
1939  trap_zero_divide
1940 ------------------
1941                 2
1942 (1 row)
1943
1944 select trap_zero_divide(0);
1945 NOTICE:  should see this
1946 NOTICE:  caught division_by_zero
1947  trap_zero_divide
1948 ------------------
1949                -1
1950 (1 row)
1951
1952 select trap_zero_divide(100000);
1953 NOTICE:  should see this
1954 NOTICE:  should see this only if 100000 <> 0
1955 NOTICE:  caught numeric_value_out_of_range
1956  trap_zero_divide
1957 ------------------
1958                -2
1959 (1 row)
1960
1961 select trap_zero_divide(-100);
1962 NOTICE:  should see this
1963 NOTICE:  should see this only if -100 <> 0
1964 NOTICE:  should see this only if -100 fits in smallint
1965 ERROR:  -100 is less than zero
1966 create function trap_matching_test(int) returns int as $$
1967 declare x int;
1968         sx smallint;
1969         y int;
1970 begin
1971         begin   -- start a subtransaction
1972                 x := 100 / $1;
1973                 sx := $1;
1974                 select into y unique1 from tenk1 where unique2 =
1975                         (select unique2 from tenk1 b where ten = $1);
1976         exception
1977                 when data_exception then  -- category match
1978                         raise notice 'caught data_exception';
1979                         x := -1;
1980                 when NUMERIC_VALUE_OUT_OF_RANGE OR CARDINALITY_VIOLATION then
1981                         raise notice 'caught numeric_value_out_of_range or cardinality_violation';
1982                         x := -2;
1983         end;
1984         return x;
1985 end$$ language plpgsql;
1986 select trap_matching_test(50);
1987  trap_matching_test
1988 --------------------
1989                   2
1990 (1 row)
1991
1992 select trap_matching_test(0);
1993 NOTICE:  caught data_exception
1994  trap_matching_test
1995 --------------------
1996                  -1
1997 (1 row)
1998
1999 select trap_matching_test(100000);
2000 NOTICE:  caught data_exception
2001  trap_matching_test
2002 --------------------
2003                  -1
2004 (1 row)
2005
2006 select trap_matching_test(1);
2007 NOTICE:  caught numeric_value_out_of_range or cardinality_violation
2008  trap_matching_test
2009 --------------------
2010                  -2
2011 (1 row)
2012
2013 create temp table foo (f1 int);
2014 create function blockme() returns int as $$
2015 declare x int;
2016 begin
2017   x := 1;
2018   insert into foo values(x);
2019   begin
2020     x := x + 1;
2021     insert into foo values(x);
2022     -- we assume this will take longer than 1 second:
2023     select count(*) into x from tenk1 a, tenk1 b, tenk1 c;
2024   exception
2025     when others then
2026       raise notice 'caught others?';
2027       return -1;
2028     when query_canceled then
2029       raise notice 'nyeah nyeah, can''t stop me';
2030       x := x * 10;
2031   end;
2032   insert into foo values(x);
2033   return x;
2034 end$$ language plpgsql;
2035 set statement_timeout to 1000;
2036 select blockme();
2037 NOTICE:  nyeah nyeah, can't stop me
2038  blockme
2039 ---------
2040       20
2041 (1 row)
2042
2043 reset statement_timeout;
2044 select * from foo;
2045  f1
2046 ----
2047   1
2048  20
2049 (2 rows)
2050
2051 -- Test for pass-by-ref values being stored in proper context
2052 create function test_variable_storage() returns text as $$
2053 declare x text;
2054 begin
2055   x := '1234';
2056   begin
2057     x := x || '5678';
2058     -- force error inside subtransaction SPI context
2059     perform trap_zero_divide(-100);
2060   exception
2061     when others then
2062       x := x || '9012';
2063   end;
2064   return x;
2065 end$$ language plpgsql;
2066 select test_variable_storage();
2067 NOTICE:  should see this
2068 CONTEXT:  SQL statement "SELECT  trap_zero_divide(-100)"
2069 PL/pgSQL function "test_variable_storage" line 7 at perform
2070 NOTICE:  should see this only if -100 <> 0
2071 CONTEXT:  SQL statement "SELECT  trap_zero_divide(-100)"
2072 PL/pgSQL function "test_variable_storage" line 7 at perform
2073 NOTICE:  should see this only if -100 fits in smallint
2074 CONTEXT:  SQL statement "SELECT  trap_zero_divide(-100)"
2075 PL/pgSQL function "test_variable_storage" line 7 at perform
2076  test_variable_storage
2077 -----------------------
2078  123456789012
2079 (1 row)
2080
2081 --
2082 -- test foreign key error trapping
2083 --
2084 create temp table master(f1 int primary key);
2085 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "master_pkey" for table "master"
2086 create temp table slave(f1 int references master deferrable);
2087 insert into master values(1);
2088 insert into slave values(1);
2089 insert into slave values(2);    -- fails
2090 ERROR:  insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
2091 DETAIL:  Key (f1)=(2) is not present in table "master".
2092 create function trap_foreign_key(int) returns int as $$
2093 begin
2094         begin   -- start a subtransaction
2095                 insert into slave values($1);
2096         exception
2097                 when foreign_key_violation then
2098                         raise notice 'caught foreign_key_violation';
2099                         return 0;
2100         end;
2101         return 1;
2102 end$$ language plpgsql;
2103 create function trap_foreign_key_2() returns int as $$
2104 begin
2105         begin   -- start a subtransaction
2106                 set constraints all immediate;
2107         exception
2108                 when foreign_key_violation then
2109                         raise notice 'caught foreign_key_violation';
2110                         return 0;
2111         end;
2112         return 1;
2113 end$$ language plpgsql;
2114 select trap_foreign_key(1);
2115  trap_foreign_key
2116 ------------------
2117                 1
2118 (1 row)
2119
2120 select trap_foreign_key(2);     -- detects FK violation
2121 NOTICE:  caught foreign_key_violation
2122  trap_foreign_key
2123 ------------------
2124                 0
2125 (1 row)
2126
2127 begin;
2128   set constraints all deferred;
2129   select trap_foreign_key(2);   -- should not detect FK violation
2130  trap_foreign_key
2131 ------------------
2132                 1
2133 (1 row)
2134
2135   savepoint x;
2136     set constraints all immediate; -- fails
2137 ERROR:  insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
2138 DETAIL:  Key (f1)=(2) is not present in table "master".
2139   rollback to x;
2140   select trap_foreign_key_2();  -- detects FK violation
2141 NOTICE:  caught foreign_key_violation
2142  trap_foreign_key_2
2143 --------------------
2144                   0
2145 (1 row)
2146
2147 commit;                         -- still fails
2148 ERROR:  insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
2149 DETAIL:  Key (f1)=(2) is not present in table "master".
2150 drop function trap_foreign_key(int);
2151 drop function trap_foreign_key_2();
2152 --
2153 -- Test proper snapshot handling in simple expressions
2154 --
2155 create temp table users(login text, id serial);
2156 NOTICE:  CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id"
2157 create function sp_id_user(a_login text) returns int as $$
2158 declare x int;
2159 begin
2160   select into x id from users where login = a_login;
2161   if found then return x; end if;
2162   return 0;
2163 end$$ language plpgsql stable;
2164 insert into users values('user1');
2165 select sp_id_user('user1');
2166  sp_id_user
2167 ------------
2168           1
2169 (1 row)
2170
2171 select sp_id_user('userx');
2172  sp_id_user
2173 ------------
2174           0
2175 (1 row)
2176
2177 create function sp_add_user(a_login text) returns int as $$
2178 declare my_id_user int;
2179 begin
2180   my_id_user = sp_id_user( a_login );
2181   IF  my_id_user > 0 THEN
2182     RETURN -1;  -- error code for existing user
2183   END IF;
2184   INSERT INTO users ( login ) VALUES ( a_login );
2185   my_id_user = sp_id_user( a_login );
2186   IF  my_id_user = 0 THEN
2187     RETURN -2;  -- error code for insertion failure
2188   END IF;
2189   RETURN my_id_user;
2190 end$$ language plpgsql;
2191 select sp_add_user('user1');
2192  sp_add_user
2193 -------------
2194           -1
2195 (1 row)
2196
2197 select sp_add_user('user2');
2198  sp_add_user
2199 -------------
2200            2
2201 (1 row)
2202
2203 select sp_add_user('user2');
2204  sp_add_user
2205 -------------
2206           -1
2207 (1 row)
2208
2209 select sp_add_user('user3');
2210  sp_add_user
2211 -------------
2212            3
2213 (1 row)
2214
2215 select sp_add_user('user3');
2216  sp_add_user
2217 -------------
2218           -1
2219 (1 row)
2220
2221 drop function sp_add_user(text);
2222 drop function sp_id_user(text);
2223 --
2224 -- tests for refcursors
2225 --
2226 create table rc_test (a int, b int);
2227 copy rc_test from stdin;
2228 create function return_refcursor(rc refcursor) returns refcursor as $$
2229 begin
2230     open rc for select a from rc_test;
2231     return rc;
2232 end
2233 $$ language 'plpgsql';
2234 create function refcursor_test1(refcursor) returns refcursor as $$
2235 begin
2236     perform return_refcursor($1);
2237     return $1;
2238 end
2239 $$ language 'plpgsql';
2240 begin;
2241 select refcursor_test1('test1');
2242  refcursor_test1
2243 -----------------
2244  test1
2245 (1 row)
2246
2247 fetch next from test1;
2248  a
2249 ---
2250  5
2251 (1 row)
2252
2253 select refcursor_test1('test2');
2254  refcursor_test1
2255 -----------------
2256  test2
2257 (1 row)
2258
2259 fetch all from test2;
2260   a
2261 -----
2262    5
2263   50
2264  500
2265 (3 rows)
2266
2267 commit;
2268 -- should fail
2269 fetch next from test1;
2270 ERROR:  cursor "test1" does not exist
2271 create function refcursor_test2(int) returns boolean as $$
2272 declare
2273     c1 cursor (param integer) for select * from rc_test where a > param;
2274     nonsense record;
2275 begin
2276     open c1($1);
2277     fetch c1 into nonsense;
2278     close c1;
2279     if found then
2280         return true;
2281     else
2282         return false;
2283     end if;
2284 end
2285 $$ language 'plpgsql';
2286 select refcursor_test2(20000) as "Should be false",
2287        refcursor_test2(20) as "Should be true";
2288  Should be false | Should be true
2289 -----------------+----------------
2290  f               | t
2291 (1 row)
2292
2293 --
2294 -- tests for "raise" processing
2295 --
2296 create function raise_test1(int) returns int as $$
2297 begin
2298     raise notice 'This message has too many parameters!', $1;
2299     return $1;
2300 end;
2301 $$ language plpgsql;
2302 select raise_test1(5);
2303 ERROR:  too many parameters specified for RAISE
2304 CONTEXT:  PL/pgSQL function "raise_test1" line 2 at raise
2305 create function raise_test2(int) returns int as $$
2306 begin
2307     raise notice 'This message has too few parameters: %, %, %', $1, $1;
2308     return $1;
2309 end;
2310 $$ language plpgsql;
2311 select raise_test2(10);
2312 ERROR:  too few parameters specified for RAISE
2313 CONTEXT:  PL/pgSQL function "raise_test2" line 2 at raise
2314 --
2315 -- reject function definitions that contain malformed SQL queries at
2316 -- compile-time, where possible
2317 --
2318 create function bad_sql1() returns int as $$
2319 declare a int;
2320 begin
2321     a := 5;
2322     Johnny Yuma;
2323     a := 10;
2324     return a;
2325 end$$ language plpgsql;
2326 ERROR:  syntax error at or near "Johnny" at character 1
2327 QUERY:  Johnny Yuma
2328 CONTEXT:  SQL statement in PL/PgSQL function "bad_sql1" near line 4
2329 LINE 1: Johnny Yuma
2330         ^
2331 create function bad_sql2() returns int as $$
2332 declare r record;
2333 begin
2334     for r in select I fought the law, the law won LOOP
2335         raise notice 'in loop';
2336     end loop;
2337     return 5;
2338 end;$$ language plpgsql;
2339 ERROR:  syntax error at or near "fought" at character 11
2340 QUERY:   select I fought the law, the law won
2341 CONTEXT:  SQL statement in PL/PgSQL function "bad_sql2" near line 3
2342 LINE 1:  select I fought the law, the law won
2343                   ^
2344 -- a RETURN expression is mandatory, except for void-returning
2345 -- functions, where it is not allowed
2346 create function missing_return_expr() returns int as $$
2347 begin
2348     return ;
2349 end;$$ language plpgsql;
2350 ERROR:  syntax error at end of input at character 8
2351 QUERY:  SELECT
2352 CONTEXT:  SQL statement in PL/PgSQL function "missing_return_expr" near line 2
2353 LINE 1: SELECT
2354                ^
2355 create function void_return_expr() returns void as $$
2356 begin
2357     return 5;
2358 end;$$ language plpgsql;
2359 ERROR:  RETURN cannot have a parameter in function returning void at or near "5" at character 72
2360 LINE 3:     return 5;
2361                    ^
2362 -- VOID functions are allowed to omit RETURN
2363 create function void_return_expr() returns void as $$
2364 begin
2365     perform 2+2;
2366 end;$$ language plpgsql;
2367 select void_return_expr();
2368  void_return_expr
2369 ------------------
2370
2371 (1 row)
2372
2373 -- but ordinary functions are not
2374 create function missing_return_expr() returns int as $$
2375 begin
2376     perform 2+2;
2377 end;$$ language plpgsql;
2378 select missing_return_expr();
2379 ERROR:  control reached end of function without RETURN
2380 CONTEXT:  PL/pgSQL function "missing_return_expr"
2381 drop function void_return_expr();
2382 drop function missing_return_expr();
2383 -- test SQLSTATE and SQLERRM
2384 create function trap_exceptions() returns void as $_$
2385 begin
2386    begin
2387      raise exception 'first exception';
2388    exception when others then
2389      raise notice '% %', SQLSTATE, SQLERRM;
2390    end;
2391    raise notice '% %', SQLSTATE, SQLERRM;
2392    begin
2393      raise exception 'last exception';
2394    exception when others then
2395      raise notice '% %', SQLSTATE, SQLERRM;
2396    end;
2397    return;
2398 end; $_$ language plpgsql;
2399 select trap_exceptions();
2400 NOTICE:  P0001 first exception
2401 NOTICE:  00000 Successful completion
2402 NOTICE:  P0001 last exception
2403  trap_exceptions
2404 -----------------
2405
2406 (1 row)
2407
2408 drop function trap_exceptions();