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     ps          alias for new;
 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'' and new.hubname != old.hubname then
 289         if count(*) > 0 from Hub where name = old.hubname then
 290             raise exception ''no manual manipulation of HSlot'';
 291         end if;
 292     end if;
 293     sname := ''HS.'' || trim(new.hubname);
 294     sname := sname || ''.'';
 295     sname := sname || new.slotno::text;
 296     if length(sname) > 20 then
 297         raise exception ''HSlot slotname "%" too long (20 char max)'', sname;
 298     end if;
 299     new.slotname := sname;
 300     return new;
 301 end;
 302 ' language plpgsql;
 303 create trigger tg_hslot_biu before insert or update
 304     on HSlot for each row execute procedure tg_hslot_biu();
 305 -- ************************************************************
 306 -- * BEFORE DELETE on HSlot
 307 -- *    - prevent from manual manipulation
 308 -- ************************************************************
 309 create function tg_hslot_bd() returns trigger as '
 310 declare
 311     hubrec      record;
 312 begin
 313     select into hubrec * from Hub where name = old.hubname;
 314     if not found then
 315         return old;
 316     end if;
 317     if old.slotno > hubrec.nslots then
 318         return old;
 319     end if;
 320     raise exception ''no manual manipulation of HSlot'';
 321 end;
 322 ' language plpgsql;
 323 create trigger tg_hslot_bd before delete
 324     on HSlot for each row execute procedure tg_hslot_bd();
 325 -- ************************************************************
 326 -- * BEFORE INSERT on all slots
 327 -- *    - Check name prefix
 328 -- ************************************************************
 329 create function tg_chkslotname() returns trigger as '
 330 begin
 331     if substr(new.slotname, 1, 2) != tg_argv[0] then
 332         raise exception ''slotname must begin with %'', tg_argv[0];
 333     end if;
 334     return new;
 335 end;
 336 ' language plpgsql;
 337 create trigger tg_chkslotname before insert
 338     on PSlot for each row execute procedure tg_chkslotname('PS');
 339 create trigger tg_chkslotname before insert
 340     on WSlot for each row execute procedure tg_chkslotname('WS');
 341 create trigger tg_chkslotname before insert
 342     on PLine for each row execute procedure tg_chkslotname('PL');
 343 create trigger tg_chkslotname before insert
 344     on IFace for each row execute procedure tg_chkslotname('IF');
 345 create trigger tg_chkslotname before insert
 346     on PHone for each row execute procedure tg_chkslotname('PH');
 347 -- ************************************************************
 348 -- * BEFORE INSERT or UPDATE on all slots with slotlink
 349 -- *    - Set slotlink to empty string if NULL value given
 350 -- ************************************************************
 351 create function tg_chkslotlink() returns trigger as '
 352 begin
 353     if new.slotlink isnull then
 354         new.slotlink := '''';
 355     end if;
 356     return new;
 357 end;
 358 ' language plpgsql;
 359 create trigger tg_chkslotlink before insert or update
 360     on PSlot for each row execute procedure tg_chkslotlink();
 361 create trigger tg_chkslotlink before insert or update
 362     on WSlot for each row execute procedure tg_chkslotlink();
 363 create trigger tg_chkslotlink before insert or update
 364     on IFace for each row execute procedure tg_chkslotlink();
 365 create trigger tg_chkslotlink before insert or update
 366     on HSlot for each row execute procedure tg_chkslotlink();
 367 create trigger tg_chkslotlink before insert or update
 368     on PHone for each row execute procedure tg_chkslotlink();
 369 -- ************************************************************
 370 -- * BEFORE INSERT or UPDATE on all slots with backlink
 371 -- *    - Set backlink to empty string if NULL value given
 372 -- ************************************************************
 373 create function tg_chkbacklink() returns trigger as '
 374 begin
 375     if new.backlink isnull then
 376         new.backlink := '''';
 377     end if;
 378     return new;
 379 end;
 380 ' language plpgsql;
 381 create trigger tg_chkbacklink before insert or update
 382     on PSlot for each row execute procedure tg_chkbacklink();
 383 create trigger tg_chkbacklink before insert or update
 384     on WSlot for each row execute procedure tg_chkbacklink();
 385 create trigger tg_chkbacklink before insert or update
 386     on PLine for each row execute procedure tg_chkbacklink();
 387 -- ************************************************************
 388 -- * BEFORE UPDATE on PSlot
 389 -- *    - do delete/insert instead of update if name changes
 390 -- ************************************************************
 391 create function tg_pslot_bu() returns trigger as '
 392 begin
 393     if new.slotname != old.slotname then
 394         delete from PSlot where slotname = old.slotname;
 395         insert into PSlot (
 396                     slotname,
 397                     pfname,
 398                     slotlink,
 399                     backlink
 400                 ) values (
 401                     new.slotname,
 402                     new.pfname,
 403                     new.slotlink,
 404                     new.backlink
 405                 );
 406         return null;
 407     end if;
 408     return new;
 409 end;
 410 ' language plpgsql;
 411 create trigger tg_pslot_bu before update
 412     on PSlot for each row execute procedure tg_pslot_bu();
 413 -- ************************************************************
 414 -- * BEFORE UPDATE on WSlot
 415 -- *    - do delete/insert instead of update if name changes
 416 -- ************************************************************
 417 create function tg_wslot_bu() returns trigger as '
 418 begin
 419     if new.slotname != old.slotname then
 420         delete from WSlot where slotname = old.slotname;
 421         insert into WSlot (
 422                     slotname,
 423                     roomno,
 424                     slotlink,
 425                     backlink
 426                 ) values (
 427                     new.slotname,
 428                     new.roomno,
 429                     new.slotlink,
 430                     new.backlink
 431                 );
 432         return null;
 433     end if;
 434     return new;
 435 end;
 436 ' language plpgsql;
 437 create trigger tg_wslot_bu before update
 438     on WSlot for each row execute procedure tg_Wslot_bu();
 439 -- ************************************************************
 440 -- * BEFORE UPDATE on PLine
 441 -- *    - do delete/insert instead of update if name changes
 442 -- ************************************************************
 443 create function tg_pline_bu() returns trigger as '
 444 begin
 445     if new.slotname != old.slotname then
 446         delete from PLine where slotname = old.slotname;
 447         insert into PLine (
 448                     slotname,
 449                     phonenumber,
 450                     comment,
 451                     backlink
 452                 ) values (
 453                     new.slotname,
 454                     new.phonenumber,
 455                     new.comment,
 456                     new.backlink
 457                 );
 458         return null;
 459     end if;
 460     return new;
 461 end;
 462 ' language plpgsql;
 463 create trigger tg_pline_bu before update
 464     on PLine for each row execute procedure tg_pline_bu();
 465 -- ************************************************************
 466 -- * BEFORE UPDATE on IFace
 467 -- *    - do delete/insert instead of update if name changes
 468 -- ************************************************************
 469 create function tg_iface_bu() returns trigger as '
 470 begin
 471     if new.slotname != old.slotname then
 472         delete from IFace where slotname = old.slotname;
 473         insert into IFace (
 474                     slotname,
 475                     sysname,
 476                     ifname,
 477                     slotlink
 478                 ) values (
 479                     new.slotname,
 480                     new.sysname,
 481                     new.ifname,
 482                     new.slotlink
 483                 );
 484         return null;
 485     end if;
 486     return new;
 487 end;
 488 ' language plpgsql;
 489 create trigger tg_iface_bu before update
 490     on IFace for each row execute procedure tg_iface_bu();
 491 -- ************************************************************
 492 -- * BEFORE UPDATE on HSlot
 493 -- *    - do delete/insert instead of update if name changes
 494 -- ************************************************************
 495 create function tg_hslot_bu() returns trigger as '
 496 begin
 497     if new.slotname != old.slotname or new.hubname != old.hubname then
 498         delete from HSlot where slotname = old.slotname;
 499         insert into HSlot (
 500                     slotname,
 501                     hubname,
 502                     slotno,
 503                     slotlink
 504                 ) values (
 505                     new.slotname,
 506                     new.hubname,
 507                     new.slotno,
 508                     new.slotlink
 509                 );
 510         return null;
 511     end if;
 512     return new;
 513 end;
 514 ' language plpgsql;
 515 create trigger tg_hslot_bu before update
 516     on HSlot for each row execute procedure tg_hslot_bu();
 517 -- ************************************************************
 518 -- * BEFORE UPDATE on PHone
 519 -- *    - do delete/insert instead of update if name changes
 520 -- ************************************************************
 521 create function tg_phone_bu() returns trigger as '
 522 begin
 523     if new.slotname != old.slotname then
 524         delete from PHone where slotname = old.slotname;
 525         insert into PHone (
 526                     slotname,
 527                     comment,
 528                     slotlink
 529                 ) values (
 530                     new.slotname,
 531                     new.comment,
 532                     new.slotlink
 533                 );
 534         return null;
 535     end if;
 536     return new;
 537 end;
 538 ' language plpgsql;
 539 create trigger tg_phone_bu before update
 540     on PHone for each row execute procedure tg_phone_bu();
 541 -- ************************************************************
 542 -- * AFTER INSERT or UPDATE or DELETE on slot with backlink
 543 -- *    - Ensure that the opponent correctly points back to us
 544 -- ************************************************************
 545 create function tg_backlink_a() returns trigger as '
 546 declare
 547     dummy       integer;
 548 begin
 549     if tg_op = ''INSERT'' then
 550         if new.backlink != '''' then
 551             dummy := tg_backlink_set(new.backlink, new.slotname);
 552         end if;
 553         return new;
 554     end if;
 555     if tg_op = ''UPDATE'' then
 556         if new.backlink != old.backlink then
 557             if old.backlink != '''' then
 558                 dummy := tg_backlink_unset(old.backlink, old.slotname);
 559             end if;
 560             if new.backlink != '''' then
 561                 dummy := tg_backlink_set(new.backlink, new.slotname);
 562             end if;
 563         else
 564             if new.slotname != old.slotname and new.backlink != '''' then
 565                 dummy := tg_slotlink_set(new.backlink, new.slotname);
 566             end if;
 567         end if;
 568         return new;
 569     end if;
 570     if tg_op = ''DELETE'' then
 571         if old.backlink != '''' then
 572             dummy := tg_backlink_unset(old.backlink, old.slotname);
 573         end if;
 574         return old;
 575     end if;
 576 end;
 577 ' language plpgsql;
 578 create trigger tg_backlink_a after insert or update or delete
 579     on PSlot for each row execute procedure tg_backlink_a('PS');
 580 create trigger tg_backlink_a after insert or update or delete
 581     on WSlot for each row execute procedure tg_backlink_a('WS');
 582 create trigger tg_backlink_a after insert or update or delete
 583     on PLine for each row execute procedure tg_backlink_a('PL');
 584 -- ************************************************************
 585 -- * Support function to set the opponents backlink field
 586 -- * if it does not already point to the requested slot
 587 -- ************************************************************
 588 create function tg_backlink_set(myname bpchar, blname bpchar)
 589 returns integer as '
 590 declare
 591     mytype      char(2);
 592     link        char(4);
 593     rec         record;
 594 begin
 595     mytype := substr(myname, 1, 2);
 596     link := mytype || substr(blname, 1, 2);
 597     if link = ''PLPL'' then
 598         raise exception
 599                 ''backlink between two phone lines does not make sense'';
 600     end if;
 601     if link in (''PLWS'', ''WSPL'') then
 602         raise exception
 603                 ''direct link of phone line to wall slot not permitted'';
 604     end if;
 605     if mytype = ''PS'' then
 606         select into rec * from PSlot where slotname = myname;
 607         if not found then
 608             raise exception ''% does not exist'', myname;
 609         end if;
 610         if rec.backlink != blname then
 611             update PSlot set backlink = blname where slotname = myname;
 612         end if;
 613         return 0;
 614     end if;
 615     if mytype = ''WS'' then
 616         select into rec * from WSlot where slotname = myname;
 617         if not found then
 618             raise exception ''% does not exist'', myname;
 619         end if;
 620         if rec.backlink != blname then
 621             update WSlot set backlink = blname where slotname = myname;
 622         end if;
 623         return 0;
 624     end if;
 625     if mytype = ''PL'' then
 626         select into rec * from PLine where slotname = myname;
 627         if not found then
 628             raise exception ''% does not exist'', myname;
 629         end if;
 630         if rec.backlink != blname then
 631             update PLine set backlink = blname where slotname = myname;
 632         end if;
 633         return 0;
 634     end if;
 635     raise exception ''illegal backlink beginning with %'', mytype;
 636 end;
 637 ' language plpgsql;
 638 -- ************************************************************
 639 -- * Support function to clear out the backlink field if
 640 -- * it still points to specific slot
 641 -- ************************************************************
 642 create function tg_backlink_unset(bpchar, bpchar)
 643 returns integer as '
 644 declare
 645     myname      alias for $1;
 646     blname      alias for $2;
 647     mytype      char(2);
 648     rec         record;
 649 begin
 650     mytype := substr(myname, 1, 2);
 651     if mytype = ''PS'' then
 652         select into rec * from PSlot where slotname = myname;
 653         if not found then
 654             return 0;
 655         end if;
 656         if rec.backlink = blname then
 657             update PSlot set backlink = '''' where slotname = myname;
 658         end if;
 659         return 0;
 660     end if;
 661     if mytype = ''WS'' then
 662         select into rec * from WSlot where slotname = myname;
 663         if not found then
 664             return 0;
 665         end if;
 666         if rec.backlink = blname then
 667             update WSlot set backlink = '''' where slotname = myname;
 668         end if;
 669         return 0;
 670     end if;
 671     if mytype = ''PL'' then
 672         select into rec * from PLine where slotname = myname;
 673         if not found then
 674             return 0;
 675         end if;
 676         if rec.backlink = blname then
 677             update PLine set backlink = '''' where slotname = myname;
 678         end if;
 679         return 0;
 680     end if;
 681 end
 682 ' language plpgsql;
 683 -- ************************************************************
 684 -- * AFTER INSERT or UPDATE or DELETE on slot with slotlink
 685 -- *    - Ensure that the opponent correctly points back to us
 686 -- ************************************************************
 687 create function tg_slotlink_a() returns trigger as '
 688 declare
 689     dummy       integer;
 690 begin
 691     if tg_op = ''INSERT'' then
 692         if new.slotlink != '''' then
 693             dummy := tg_slotlink_set(new.slotlink, new.slotname);
 694         end if;
 695         return new;
 696     end if;
 697     if tg_op = ''UPDATE'' then
 698         if new.slotlink != old.slotlink then
 699             if old.slotlink != '''' then
 700                 dummy := tg_slotlink_unset(old.slotlink, old.slotname);
 701             end if;
 702             if new.slotlink != '''' then
 703                 dummy := tg_slotlink_set(new.slotlink, new.slotname);
 704             end if;
 705         else
 706             if new.slotname != old.slotname and new.slotlink != '''' then
 707                 dummy := tg_slotlink_set(new.slotlink, new.slotname);
 708             end if;
 709         end if;
 710         return new;
 711     end if;
 712     if tg_op = ''DELETE'' then
 713         if old.slotlink != '''' then
 714             dummy := tg_slotlink_unset(old.slotlink, old.slotname);
 715         end if;
 716         return old;
 717     end if;
 718 end;
 719 ' language plpgsql;
 720 create trigger tg_slotlink_a after insert or update or delete
 721     on PSlot for each row execute procedure tg_slotlink_a('PS');
 722 create trigger tg_slotlink_a after insert or update or delete
 723     on WSlot for each row execute procedure tg_slotlink_a('WS');
 724 create trigger tg_slotlink_a after insert or update or delete
 725     on IFace for each row execute procedure tg_slotlink_a('IF');
 726 create trigger tg_slotlink_a after insert or update or delete
 727     on HSlot for each row execute procedure tg_slotlink_a('HS');
 728 create trigger tg_slotlink_a after insert or update or delete
 729     on PHone for each row execute procedure tg_slotlink_a('PH');
 730 -- ************************************************************
 731 -- * Support function to set the opponents slotlink field
 732 -- * if it does not already point to the requested slot
 733 -- ************************************************************
 734 create function tg_slotlink_set(bpchar, bpchar)
 735 returns integer as '
 736 declare
 737     myname      alias for $1;
 738     blname      alias for $2;
 739     mytype      char(2);
 740     link        char(4);
 741     rec         record;
 742 begin
 743     mytype := substr(myname, 1, 2);
 744     link := mytype || substr(blname, 1, 2);
 745     if link = ''PHPH'' then
 746         raise exception
 747                 ''slotlink between two phones does not make sense'';
 748     end if;
 749     if link in (''PHHS'', ''HSPH'') then
 750         raise exception
 751                 ''link of phone to hub does not make sense'';
 752     end if;
 753     if link in (''PHIF'', ''IFPH'') then
 754         raise exception
 755                 ''link of phone to hub does not make sense'';
 756     end if;
 757     if link in (''PSWS'', ''WSPS'') then
 758         raise exception
 759                 ''slotlink from patchslot to wallslot not permitted'';
 760     end if;
 761     if mytype = ''PS'' then
 762         select into rec * from PSlot where slotname = myname;
 763         if not found then
 764             raise exception ''% does not exist'', myname;
 765         end if;
 766         if rec.slotlink != blname then
 767             update PSlot set slotlink = blname where slotname = myname;
 768         end if;
 769         return 0;
 770     end if;
 771     if mytype = ''WS'' then
 772         select into rec * from WSlot where slotname = myname;
 773         if not found then
 774             raise exception ''% does not exist'', myname;
 775         end if;
 776         if rec.slotlink != blname then
 777             update WSlot set slotlink = blname where slotname = myname;
 778         end if;
 779         return 0;
 780     end if;
 781     if mytype = ''IF'' then
 782         select into rec * from IFace where slotname = myname;
 783         if not found then
 784             raise exception ''% does not exist'', myname;
 785         end if;
 786         if rec.slotlink != blname then
 787             update IFace set slotlink = blname where slotname = myname;
 788         end if;
 789         return 0;
 790     end if;
 791     if mytype = ''HS'' then
 792         select into rec * from HSlot where slotname = myname;
 793         if not found then
 794             raise exception ''% does not exist'', myname;
 795         end if;
 796         if rec.slotlink != blname then
 797             update HSlot set slotlink = blname where slotname = myname;
 798         end if;
 799         return 0;
 800     end if;
 801     if mytype = ''PH'' then
 802         select into rec * from PHone where slotname = myname;
 803         if not found then
 804             raise exception ''% does not exist'', myname;
 805         end if;
 806         if rec.slotlink != blname then
 807             update PHone set slotlink = blname where slotname = myname;
 808         end if;
 809         return 0;
 810     end if;
 811     raise exception ''illegal slotlink beginning with %'', mytype;
 812 end;
 813 ' language plpgsql;
 814 -- ************************************************************
 815 -- * Support function to clear out the slotlink field if
 816 -- * it still points to specific slot
 817 -- ************************************************************
 818 create function tg_slotlink_unset(bpchar, bpchar)
 819 returns integer as '
 820 declare
 821     myname      alias for $1;
 822     blname      alias for $2;
 823     mytype      char(2);
 824     rec         record;
 825 begin
 826     mytype := substr(myname, 1, 2);
 827     if mytype = ''PS'' then
 828         select into rec * from PSlot where slotname = myname;
 829         if not found then
 830             return 0;
 831         end if;
 832         if rec.slotlink = blname then
 833             update PSlot set slotlink = '''' where slotname = myname;
 834         end if;
 835         return 0;
 836     end if;
 837     if mytype = ''WS'' then
 838         select into rec * from WSlot where slotname = myname;
 839         if not found then
 840             return 0;
 841         end if;
 842         if rec.slotlink = blname then
 843             update WSlot set slotlink = '''' where slotname = myname;
 844         end if;
 845         return 0;
 846     end if;
 847     if mytype = ''IF'' then
 848         select into rec * from IFace where slotname = myname;
 849         if not found then
 850             return 0;
 851         end if;
 852         if rec.slotlink = blname then
 853             update IFace set slotlink = '''' where slotname = myname;
 854         end if;
 855         return 0;
 856     end if;
 857     if mytype = ''HS'' then
 858         select into rec * from HSlot where slotname = myname;
 859         if not found then
 860             return 0;
 861         end if;
 862         if rec.slotlink = blname then
 863             update HSlot set slotlink = '''' where slotname = myname;
 864         end if;
 865         return 0;
 866     end if;
 867     if mytype = ''PH'' then
 868         select into rec * from PHone where slotname = myname;
 869         if not found then
 870             return 0;
 871         end if;
 872         if rec.slotlink = blname then
 873             update PHone set slotlink = '''' where slotname = myname;
 874         end if;
 875         return 0;
 876     end if;
 877 end;
 878 ' language plpgsql;
 879 -- ************************************************************
 880 -- * Describe the backside of a patchfield slot
 881 -- ************************************************************
 882 create function pslot_backlink_view(bpchar)
 883 returns text as '
 884 <<outer>>
 885 declare
 886     rec         record;
 887     bltype      char(2);
 888     retval      text;
 889 begin
 890     select into rec * from PSlot where slotname = $1;
 891     if not found then
 892         return '''';
 893     end if;
 894     if rec.backlink = '''' then
 895         return ''-'';
 896     end if;
 897     bltype := substr(rec.backlink, 1, 2);
 898     if bltype = ''PL'' then
 899         declare
 900             rec         record;
 901         begin
 902             select into rec * from PLine where slotname = "outer".rec.backlink;
 903             retval := ''Phone line '' || trim(rec.phonenumber);
 904             if rec.comment != '''' then
 905                 retval := retval || '' ('';
 906                 retval := retval || rec.comment;
 907                 retval := retval || '')'';
 908             end if;
 909             return retval;
 910         end;
 911     end if;
 912     if bltype = ''WS'' then
 913         select into rec * from WSlot where slotname = rec.backlink;
 914         retval := trim(rec.slotname) || '' in room '';
 915         retval := retval || trim(rec.roomno);
 916         retval := retval || '' -> '';
 917         return retval || wslot_slotlink_view(rec.slotname);
 918     end if;
 919     return rec.backlink;
 920 end;
 921 ' language plpgsql;
 922 -- ************************************************************
 923 -- * Describe the front of a patchfield slot
 924 -- ************************************************************
 925 create function pslot_slotlink_view(bpchar)
 926 returns text as '
 927 declare
 928     psrec       record;
 929     sltype      char(2);
 930     retval      text;
 931 begin
 932     select into psrec * from PSlot where slotname = $1;
 933     if not found then
 934         return '''';
 935     end if;
 936     if psrec.slotlink = '''' then
 937         return ''-'';
 938     end if;
 939     sltype := substr(psrec.slotlink, 1, 2);
 940     if sltype = ''PS'' then
 941         retval := trim(psrec.slotlink) || '' -> '';
 942         return retval || pslot_backlink_view(psrec.slotlink);
 943     end if;
 944     if sltype = ''HS'' then
 945         retval := comment from Hub H, HSlot HS
 946                         where HS.slotname = psrec.slotlink
 947                           and H.name = HS.hubname;
 948         retval := retval || '' slot '';
 949         retval := retval || slotno::text from HSlot
 950                         where slotname = psrec.slotlink;
 951         return retval;
 952     end if;
 953     return psrec.slotlink;
 954 end;
 955 ' language plpgsql;
 956 -- ************************************************************
 957 -- * Describe the front of a wall connector slot
 958 -- ************************************************************
 959 create function wslot_slotlink_view(bpchar)
 960 returns text as '
 961 declare
 962     rec         record;
 963     sltype      char(2);
 964     retval      text;
 965 begin
 966     select into rec * from WSlot where slotname = $1;
 967     if not found then
 968         return '''';
 969     end if;
 970     if rec.slotlink = '''' then
 971         return ''-'';
 972     end if;
 973     sltype := substr(rec.slotlink, 1, 2);
 974     if sltype = ''PH'' then
 975         select into rec * from PHone where slotname = rec.slotlink;
 976         retval := ''Phone '' || trim(rec.slotname);
 977         if rec.comment != '''' then
 978             retval := retval || '' ('';
 979             retval := retval || rec.comment;
 980             retval := retval || '')'';
 981         end if;
 982         return retval;
 983     end if;
 984     if sltype = ''IF'' then
 985         declare
 986             syrow       System%RowType;
 987             ifrow       IFace%ROWTYPE;
 988         begin
 989             select into ifrow * from IFace where slotname = rec.slotlink;
 990             select into syrow * from System where name = ifrow.sysname;
 991             retval := syrow.name || '' IF '';
 992             retval := retval || ifrow.ifname;
 993             if syrow.comment != '''' then
 994                 retval := retval || '' ('';
 995                 retval := retval || syrow.comment;
 996                 retval := retval || '')'';
 997             end if;
 998             return retval;
 999         end;
1000     end if;
1001     return rec.slotlink;
1002 end;
1003 ' language plpgsql;
1004 -- ************************************************************
1005 -- * View of a patchfield describing backside and patches
1006 -- ************************************************************
1007 create view Pfield_v1 as select PF.pfname, PF.slotname,
1008         pslot_backlink_view(PF.slotname) as backside,
1009         pslot_slotlink_view(PF.slotname) as patch
1010     from PSlot PF;
1011 --
1012 -- First we build the house - so we create the rooms
1013 --
1014 insert into Room values ('001', 'Entrance');
1015 insert into Room values ('002', 'Office');
1016 insert into Room values ('003', 'Office');
1017 insert into Room values ('004', 'Technical');
1018 insert into Room values ('101', 'Office');
1019 insert into Room values ('102', 'Conference');
1020 insert into Room values ('103', 'Restroom');
1021 insert into Room values ('104', 'Technical');
1022 insert into Room values ('105', 'Office');
1023 insert into Room values ('106', 'Office');
1024 --
1025 -- Second we install the wall connectors
1026 --
1027 insert into WSlot values ('WS.001.1a', '001', '', '');
1028 insert into WSlot values ('WS.001.1b', '001', '', '');
1029 insert into WSlot values ('WS.001.2a', '001', '', '');
1030 insert into WSlot values ('WS.001.2b', '001', '', '');
1031 insert into WSlot values ('WS.001.3a', '001', '', '');
1032 insert into WSlot values ('WS.001.3b', '001', '', '');
1033 insert into WSlot values ('WS.002.1a', '002', '', '');
1034 insert into WSlot values ('WS.002.1b', '002', '', '');
1035 insert into WSlot values ('WS.002.2a', '002', '', '');
1036 insert into WSlot values ('WS.002.2b', '002', '', '');
1037 insert into WSlot values ('WS.002.3a', '002', '', '');
1038 insert into WSlot values ('WS.002.3b', '002', '', '');
1039 insert into WSlot values ('WS.003.1a', '003', '', '');
1040 insert into WSlot values ('WS.003.1b', '003', '', '');
1041 insert into WSlot values ('WS.003.2a', '003', '', '');
1042 insert into WSlot values ('WS.003.2b', '003', '', '');
1043 insert into WSlot values ('WS.003.3a', '003', '', '');
1044 insert into WSlot values ('WS.003.3b', '003', '', '');
1045 insert into WSlot values ('WS.101.1a', '101', '', '');
1046 insert into WSlot values ('WS.101.1b', '101', '', '');
1047 insert into WSlot values ('WS.101.2a', '101', '', '');
1048 insert into WSlot values ('WS.101.2b', '101', '', '');
1049 insert into WSlot values ('WS.101.3a', '101', '', '');
1050 insert into WSlot values ('WS.101.3b', '101', '', '');
1051 insert into WSlot values ('WS.102.1a', '102', '', '');
1052 insert into WSlot values ('WS.102.1b', '102', '', '');
1053 insert into WSlot values ('WS.102.2a', '102', '', '');
1054 insert into WSlot values ('WS.102.2b', '102', '', '');
1055 insert into WSlot values ('WS.102.3a', '102', '', '');
1056 insert into WSlot values ('WS.102.3b', '102', '', '');
1057 insert into WSlot values ('WS.105.1a', '105', '', '');
1058 insert into WSlot values ('WS.105.1b', '105', '', '');
1059 insert into WSlot values ('WS.105.2a', '105', '', '');
1060 insert into WSlot values ('WS.105.2b', '105', '', '');
1061 insert into WSlot values ('WS.105.3a', '105', '', '');
1062 insert into WSlot values ('WS.105.3b', '105', '', '');
1063 insert into WSlot values ('WS.106.1a', '106', '', '');
1064 insert into WSlot values ('WS.106.1b', '106', '', '');
1065 insert into WSlot values ('WS.106.2a', '106', '', '');
1066 insert into WSlot values ('WS.106.2b', '106', '', '');
1067 insert into WSlot values ('WS.106.3a', '106', '', '');
1068 insert into WSlot values ('WS.106.3b', '106', '', '');
1069 --
1070 -- Now create the patch fields and their slots
1071 --
1072 insert into PField values ('PF0_1', 'Wallslots basement');
1073 --
1074 -- The cables for these will be made later, so they are unconnected for now
1075 --
1076 insert into PSlot values ('PS.base.a1', 'PF0_1', '', '');
1077 insert into PSlot values ('PS.base.a2', 'PF0_1', '', '');
1078 insert into PSlot values ('PS.base.a3', 'PF0_1', '', '');
1079 insert into PSlot values ('PS.base.a4', 'PF0_1', '', '');
1080 insert into PSlot values ('PS.base.a5', 'PF0_1', '', '');
1081 insert into PSlot values ('PS.base.a6', 'PF0_1', '', '');
1082 --
1083 -- These are already wired to the wall connectors
1084 --
1085 insert into PSlot values ('PS.base.b1', 'PF0_1', '', 'WS.002.1a');
1086 insert into PSlot values ('PS.base.b2', 'PF0_1', '', 'WS.002.1b');
1087 insert into PSlot values ('PS.base.b3', 'PF0_1', '', 'WS.002.2a');
1088 insert into PSlot values ('PS.base.b4', 'PF0_1', '', 'WS.002.2b');
1089 insert into PSlot values ('PS.base.b5', 'PF0_1', '', 'WS.002.3a');
1090 insert into PSlot values ('PS.base.b6', 'PF0_1', '', 'WS.002.3b');
1091 insert into PSlot values ('PS.base.c1', 'PF0_1', '', 'WS.003.1a');
1092 insert into PSlot values ('PS.base.c2', 'PF0_1', '', 'WS.003.1b');
1093 insert into PSlot values ('PS.base.c3', 'PF0_1', '', 'WS.003.2a');
1094 insert into PSlot values ('PS.base.c4', 'PF0_1', '', 'WS.003.2b');
1095 insert into PSlot values ('PS.base.c5', 'PF0_1', '', 'WS.003.3a');
1096 insert into PSlot values ('PS.base.c6', 'PF0_1', '', 'WS.003.3b');
1097 --
1098 -- This patchfield will be renamed later into PF0_2 - so its
1099 -- slots references in pfname should follow
1100 --
1101 insert into PField values ('PF0_X', 'Phonelines basement');
1102 insert into PSlot values ('PS.base.ta1', 'PF0_X', '', '');
1103 insert into PSlot values ('PS.base.ta2', 'PF0_X', '', '');
1104 insert into PSlot values ('PS.base.ta3', 'PF0_X', '', '');
1105 insert into PSlot values ('PS.base.ta4', 'PF0_X', '', '');
1106 insert into PSlot values ('PS.base.ta5', 'PF0_X', '', '');
1107 insert into PSlot values ('PS.base.ta6', 'PF0_X', '', '');
1108 insert into PSlot values ('PS.base.tb1', 'PF0_X', '', '');
1109 insert into PSlot values ('PS.base.tb2', 'PF0_X', '', '');
1110 insert into PSlot values ('PS.base.tb3', 'PF0_X', '', '');
1111 insert into PSlot values ('PS.base.tb4', 'PF0_X', '', '');
1112 insert into PSlot values ('PS.base.tb5', 'PF0_X', '', '');
1113 insert into PSlot values ('PS.base.tb6', 'PF0_X', '', '');
1114 insert into PField values ('PF1_1', 'Wallslots first floor');
1115 insert into PSlot values ('PS.first.a1', 'PF1_1', '', 'WS.101.1a');
1116 insert into PSlot values ('PS.first.a2', 'PF1_1', '', 'WS.101.1b');
1117 insert into PSlot values ('PS.first.a3', 'PF1_1', '', 'WS.101.2a');
1118 insert into PSlot values ('PS.first.a4', 'PF1_1', '', 'WS.101.2b');
1119 insert into PSlot values ('PS.first.a5', 'PF1_1', '', 'WS.101.3a');
1120 insert into PSlot values ('PS.first.a6', 'PF1_1', '', 'WS.101.3b');
1121 insert into PSlot values ('PS.first.b1', 'PF1_1', '', 'WS.102.1a');
1122 insert into PSlot values ('PS.first.b2', 'PF1_1', '', 'WS.102.1b');
1123 insert into PSlot values ('PS.first.b3', 'PF1_1', '', 'WS.102.2a');
1124 insert into PSlot values ('PS.first.b4', 'PF1_1', '', 'WS.102.2b');
1125 insert into PSlot values ('PS.first.b5', 'PF1_1', '', 'WS.102.3a');
1126 insert into PSlot values ('PS.first.b6', 'PF1_1', '', 'WS.102.3b');
1127 insert into PSlot values ('PS.first.c1', 'PF1_1', '', 'WS.105.1a');
1128 insert into PSlot values ('PS.first.c2', 'PF1_1', '', 'WS.105.1b');
1129 insert into PSlot values ('PS.first.c3', 'PF1_1', '', 'WS.105.2a');
1130 insert into PSlot values ('PS.first.c4', 'PF1_1', '', 'WS.105.2b');
1131 insert into PSlot values ('PS.first.c5', 'PF1_1', '', 'WS.105.3a');
1132 insert into PSlot values ('PS.first.c6', 'PF1_1', '', 'WS.105.3b');
1133 insert into PSlot values ('PS.first.d1', 'PF1_1', '', 'WS.106.1a');
1134 insert into PSlot values ('PS.first.d2', 'PF1_1', '', 'WS.106.1b');
1135 insert into PSlot values ('PS.first.d3', 'PF1_1', '', 'WS.106.2a');
1136 insert into PSlot values ('PS.first.d4', 'PF1_1', '', 'WS.106.2b');
1137 insert into PSlot values ('PS.first.d5', 'PF1_1', '', 'WS.106.3a');
1138 insert into PSlot values ('PS.first.d6', 'PF1_1', '', 'WS.106.3b');
1139 --
1140 -- Now we wire the wall connectors 1a-2a in room 001 to the
1141 -- patchfield. In the second update we make an error, and
1142 -- correct it after
1143 --
1144 update PSlot set backlink = 'WS.001.1a' where slotname = 'PS.base.a1';
1145 update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a3';
1146 select * from WSlot where roomno = '001' order by slotname;
1147        slotname       |  roomno  |       slotlink       |       backlink
1148 ----------------------+----------+----------------------+----------------------
1149  WS.001.1a            | 001      |                      | PS.base.a1
1150  WS.001.1b            | 001      |                      | PS.base.a3
1151  WS.001.2a            | 001      |                      |
1152  WS.001.2b            | 001      |                      |
1153  WS.001.3a            | 001      |                      |
1154  WS.001.3b            | 001      |                      |
1155 (6 rows)
1156
1157 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1158        slotname       | pfname |       slotlink       |       backlink
1159 ----------------------+--------+----------------------+----------------------
1160  PS.base.a1           | PF0_1  |                      | WS.001.1a
1161  PS.base.a2           | PF0_1  |                      |
1162  PS.base.a3           | PF0_1  |                      | WS.001.1b
1163  PS.base.a4           | PF0_1  |                      |
1164  PS.base.a5           | PF0_1  |                      |
1165  PS.base.a6           | PF0_1  |                      |
1166 (6 rows)
1167
1168 update PSlot set backlink = 'WS.001.2a' where slotname = 'PS.base.a3';
1169 select * from WSlot where roomno = '001' order by slotname;
1170        slotname       |  roomno  |       slotlink       |       backlink
1171 ----------------------+----------+----------------------+----------------------
1172  WS.001.1a            | 001      |                      | PS.base.a1
1173  WS.001.1b            | 001      |                      |
1174  WS.001.2a            | 001      |                      | PS.base.a3
1175  WS.001.2b            | 001      |                      |
1176  WS.001.3a            | 001      |                      |
1177  WS.001.3b            | 001      |                      |
1178 (6 rows)
1179
1180 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1181        slotname       | pfname |       slotlink       |       backlink
1182 ----------------------+--------+----------------------+----------------------
1183  PS.base.a1           | PF0_1  |                      | WS.001.1a
1184  PS.base.a2           | PF0_1  |                      |
1185  PS.base.a3           | PF0_1  |                      | WS.001.2a
1186  PS.base.a4           | PF0_1  |                      |
1187  PS.base.a5           | PF0_1  |                      |
1188  PS.base.a6           | PF0_1  |                      |
1189 (6 rows)
1190
1191 update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a2';
1192 select * from WSlot where roomno = '001' order by slotname;
1193        slotname       |  roomno  |       slotlink       |       backlink
1194 ----------------------+----------+----------------------+----------------------
1195  WS.001.1a            | 001      |                      | PS.base.a1
1196  WS.001.1b            | 001      |                      | PS.base.a2
1197  WS.001.2a            | 001      |                      | PS.base.a3
1198  WS.001.2b            | 001      |                      |
1199  WS.001.3a            | 001      |                      |
1200  WS.001.3b            | 001      |                      |
1201 (6 rows)
1202
1203 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1204        slotname       | pfname |       slotlink       |       backlink
1205 ----------------------+--------+----------------------+----------------------
1206  PS.base.a1           | PF0_1  |                      | WS.001.1a
1207  PS.base.a2           | PF0_1  |                      | WS.001.1b
1208  PS.base.a3           | PF0_1  |                      | WS.001.2a
1209  PS.base.a4           | PF0_1  |                      |
1210  PS.base.a5           | PF0_1  |                      |
1211  PS.base.a6           | PF0_1  |                      |
1212 (6 rows)
1213
1214 --
1215 -- Same procedure for 2b-3b but this time updating the WSlot instead
1216 -- of the PSlot. Due to the triggers the result is the same:
1217 -- WSlot and corresponding PSlot point to each other.
1218 --
1219 update WSlot set backlink = 'PS.base.a4' where slotname = 'WS.001.2b';
1220 update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3a';
1221 select * from WSlot where roomno = '001' order by slotname;
1222        slotname       |  roomno  |       slotlink       |       backlink
1223 ----------------------+----------+----------------------+----------------------
1224  WS.001.1a            | 001      |                      | PS.base.a1
1225  WS.001.1b            | 001      |                      | PS.base.a2
1226  WS.001.2a            | 001      |                      | PS.base.a3
1227  WS.001.2b            | 001      |                      | PS.base.a4
1228  WS.001.3a            | 001      |                      | PS.base.a6
1229  WS.001.3b            | 001      |                      |
1230 (6 rows)
1231
1232 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1233        slotname       | pfname |       slotlink       |       backlink
1234 ----------------------+--------+----------------------+----------------------
1235  PS.base.a1           | PF0_1  |                      | WS.001.1a
1236  PS.base.a2           | PF0_1  |                      | WS.001.1b
1237  PS.base.a3           | PF0_1  |                      | WS.001.2a
1238  PS.base.a4           | PF0_1  |                      | WS.001.2b
1239  PS.base.a5           | PF0_1  |                      |
1240  PS.base.a6           | PF0_1  |                      | WS.001.3a
1241 (6 rows)
1242
1243 update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3b';
1244 select * from WSlot where roomno = '001' order by slotname;
1245        slotname       |  roomno  |       slotlink       |       backlink
1246 ----------------------+----------+----------------------+----------------------
1247  WS.001.1a            | 001      |                      | PS.base.a1
1248  WS.001.1b            | 001      |                      | PS.base.a2
1249  WS.001.2a            | 001      |                      | PS.base.a3
1250  WS.001.2b            | 001      |                      | PS.base.a4
1251  WS.001.3a            | 001      |                      |
1252  WS.001.3b            | 001      |                      | PS.base.a6
1253 (6 rows)
1254
1255 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1256        slotname       | pfname |       slotlink       |       backlink
1257 ----------------------+--------+----------------------+----------------------
1258  PS.base.a1           | PF0_1  |                      | WS.001.1a
1259  PS.base.a2           | PF0_1  |                      | WS.001.1b
1260  PS.base.a3           | PF0_1  |                      | WS.001.2a
1261  PS.base.a4           | PF0_1  |                      | WS.001.2b
1262  PS.base.a5           | PF0_1  |                      |
1263  PS.base.a6           | PF0_1  |                      | WS.001.3b
1264 (6 rows)
1265
1266 update WSlot set backlink = 'PS.base.a5' where slotname = 'WS.001.3a';
1267 select * from WSlot where roomno = '001' order by slotname;
1268        slotname       |  roomno  |       slotlink       |       backlink
1269 ----------------------+----------+----------------------+----------------------
1270  WS.001.1a            | 001      |                      | PS.base.a1
1271  WS.001.1b            | 001      |                      | PS.base.a2
1272  WS.001.2a            | 001      |                      | PS.base.a3
1273  WS.001.2b            | 001      |                      | PS.base.a4
1274  WS.001.3a            | 001      |                      | PS.base.a5
1275  WS.001.3b            | 001      |                      | PS.base.a6
1276 (6 rows)
1277
1278 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1279        slotname       | pfname |       slotlink       |       backlink
1280 ----------------------+--------+----------------------+----------------------
1281  PS.base.a1           | PF0_1  |                      | WS.001.1a
1282  PS.base.a2           | PF0_1  |                      | WS.001.1b
1283  PS.base.a3           | PF0_1  |                      | WS.001.2a
1284  PS.base.a4           | PF0_1  |                      | WS.001.2b
1285  PS.base.a5           | PF0_1  |                      | WS.001.3a
1286  PS.base.a6           | PF0_1  |                      | WS.001.3b
1287 (6 rows)
1288
1289 insert into PField values ('PF1_2', 'Phonelines first floor');
1290 insert into PSlot values ('PS.first.ta1', 'PF1_2', '', '');
1291 insert into PSlot values ('PS.first.ta2', 'PF1_2', '', '');
1292 insert into PSlot values ('PS.first.ta3', 'PF1_2', '', '');
1293 insert into PSlot values ('PS.first.ta4', 'PF1_2', '', '');
1294 insert into PSlot values ('PS.first.ta5', 'PF1_2', '', '');
1295 insert into PSlot values ('PS.first.ta6', 'PF1_2', '', '');
1296 insert into PSlot values ('PS.first.tb1', 'PF1_2', '', '');
1297 insert into PSlot values ('PS.first.tb2', 'PF1_2', '', '');
1298 insert into PSlot values ('PS.first.tb3', 'PF1_2', '', '');
1299 insert into PSlot values ('PS.first.tb4', 'PF1_2', '', '');
1300 insert into PSlot values ('PS.first.tb5', 'PF1_2', '', '');
1301 insert into PSlot values ('PS.first.tb6', 'PF1_2', '', '');
1302 --
1303 -- Fix the wrong name for patchfield PF0_2
1304 --
1305 update PField set name = 'PF0_2' where name = 'PF0_X';
1306 select * from PSlot order by slotname;
1307        slotname       | pfname |       slotlink       |       backlink
1308 ----------------------+--------+----------------------+----------------------
1309  PS.base.a1           | PF0_1  |                      | WS.001.1a
1310  PS.base.a2           | PF0_1  |                      | WS.001.1b
1311  PS.base.a3           | PF0_1  |                      | WS.001.2a
1312  PS.base.a4           | PF0_1  |                      | WS.001.2b
1313  PS.base.a5           | PF0_1  |                      | WS.001.3a
1314  PS.base.a6           | PF0_1  |                      | WS.001.3b
1315  PS.base.b1           | PF0_1  |                      | WS.002.1a
1316  PS.base.b2           | PF0_1  |                      | WS.002.1b
1317  PS.base.b3           | PF0_1  |                      | WS.002.2a
1318  PS.base.b4           | PF0_1  |                      | WS.002.2b
1319  PS.base.b5           | PF0_1  |                      | WS.002.3a
1320  PS.base.b6           | PF0_1  |                      | WS.002.3b
1321  PS.base.c1           | PF0_1  |                      | WS.003.1a
1322  PS.base.c2           | PF0_1  |                      | WS.003.1b
1323  PS.base.c3           | PF0_1  |                      | WS.003.2a
1324  PS.base.c4           | PF0_1  |                      | WS.003.2b
1325  PS.base.c5           | PF0_1  |                      | WS.003.3a
1326  PS.base.c6           | PF0_1  |                      | WS.003.3b
1327  PS.base.ta1          | PF0_2  |                      |
1328  PS.base.ta2          | PF0_2  |                      |
1329  PS.base.ta3          | PF0_2  |                      |
1330  PS.base.ta4          | PF0_2  |                      |
1331  PS.base.ta5          | PF0_2  |                      |
1332  PS.base.ta6          | PF0_2  |                      |
1333  PS.base.tb1          | PF0_2  |                      |
1334  PS.base.tb2          | PF0_2  |                      |
1335  PS.base.tb3          | PF0_2  |                      |
1336  PS.base.tb4          | PF0_2  |                      |
1337  PS.base.tb5          | PF0_2  |                      |
1338  PS.base.tb6          | PF0_2  |                      |
1339  PS.first.a1          | PF1_1  |                      | WS.101.1a
1340  PS.first.a2          | PF1_1  |                      | WS.101.1b
1341  PS.first.a3          | PF1_1  |                      | WS.101.2a
1342  PS.first.a4          | PF1_1  |                      | WS.101.2b
1343  PS.first.a5          | PF1_1  |                      | WS.101.3a
1344  PS.first.a6          | PF1_1  |                      | WS.101.3b
1345  PS.first.b1          | PF1_1  |                      | WS.102.1a
1346  PS.first.b2          | PF1_1  |                      | WS.102.1b
1347  PS.first.b3          | PF1_1  |                      | WS.102.2a
1348  PS.first.b4          | PF1_1  |                      | WS.102.2b
1349  PS.first.b5          | PF1_1  |                      | WS.102.3a
1350  PS.first.b6          | PF1_1  |                      | WS.102.3b
1351  PS.first.c1          | PF1_1  |                      | WS.105.1a
1352  PS.first.c2          | PF1_1  |                      | WS.105.1b
1353  PS.first.c3          | PF1_1  |                      | WS.105.2a
1354  PS.first.c4          | PF1_1  |                      | WS.105.2b
1355  PS.first.c5          | PF1_1  |                      | WS.105.3a
1356  PS.first.c6          | PF1_1  |                      | WS.105.3b
1357  PS.first.d1          | PF1_1  |                      | WS.106.1a
1358  PS.first.d2          | PF1_1  |                      | WS.106.1b
1359  PS.first.d3          | PF1_1  |                      | WS.106.2a
1360  PS.first.d4          | PF1_1  |                      | WS.106.2b
1361  PS.first.d5          | PF1_1  |                      | WS.106.3a
1362  PS.first.d6          | PF1_1  |                      | WS.106.3b
1363  PS.first.ta1         | PF1_2  |                      |
1364  PS.first.ta2         | PF1_2  |                      |
1365  PS.first.ta3         | PF1_2  |                      |
1366  PS.first.ta4         | PF1_2  |                      |
1367  PS.first.ta5         | PF1_2  |                      |
1368  PS.first.ta6         | PF1_2  |                      |
1369  PS.first.tb1         | PF1_2  |                      |
1370  PS.first.tb2         | PF1_2  |                      |
1371  PS.first.tb3         | PF1_2  |                      |
1372  PS.first.tb4         | PF1_2  |                      |
1373  PS.first.tb5         | PF1_2  |                      |
1374  PS.first.tb6         | PF1_2  |                      |
1375 (66 rows)
1376
1377 select * from WSlot order by slotname;
1378        slotname       |  roomno  |       slotlink       |       backlink
1379 ----------------------+----------+----------------------+----------------------
1380  WS.001.1a            | 001      |                      | PS.base.a1
1381  WS.001.1b            | 001      |                      | PS.base.a2
1382  WS.001.2a            | 001      |                      | PS.base.a3
1383  WS.001.2b            | 001      |                      | PS.base.a4
1384  WS.001.3a            | 001      |                      | PS.base.a5
1385  WS.001.3b            | 001      |                      | PS.base.a6
1386  WS.002.1a            | 002      |                      | PS.base.b1
1387  WS.002.1b            | 002      |                      | PS.base.b2
1388  WS.002.2a            | 002      |                      | PS.base.b3
1389  WS.002.2b            | 002      |                      | PS.base.b4
1390  WS.002.3a            | 002      |                      | PS.base.b5
1391  WS.002.3b            | 002      |                      | PS.base.b6
1392  WS.003.1a            | 003      |                      | PS.base.c1
1393  WS.003.1b            | 003      |                      | PS.base.c2
1394  WS.003.2a            | 003      |                      | PS.base.c3
1395  WS.003.2b            | 003      |                      | PS.base.c4
1396  WS.003.3a            | 003      |                      | PS.base.c5
1397  WS.003.3b            | 003      |                      | PS.base.c6
1398  WS.101.1a            | 101      |                      | PS.first.a1
1399  WS.101.1b            | 101      |                      | PS.first.a2
1400  WS.101.2a            | 101      |                      | PS.first.a3
1401  WS.101.2b            | 101      |                      | PS.first.a4
1402  WS.101.3a            | 101      |                      | PS.first.a5
1403  WS.101.3b            | 101      |                      | PS.first.a6
1404  WS.102.1a            | 102      |                      | PS.first.b1
1405  WS.102.1b            | 102      |                      | PS.first.b2
1406  WS.102.2a            | 102      |                      | PS.first.b3
1407  WS.102.2b            | 102      |                      | PS.first.b4
1408  WS.102.3a            | 102      |                      | PS.first.b5
1409  WS.102.3b            | 102      |                      | PS.first.b6
1410  WS.105.1a            | 105      |                      | PS.first.c1
1411  WS.105.1b            | 105      |                      | PS.first.c2
1412  WS.105.2a            | 105      |                      | PS.first.c3
1413  WS.105.2b            | 105      |                      | PS.first.c4
1414  WS.105.3a            | 105      |                      | PS.first.c5
1415  WS.105.3b            | 105      |                      | PS.first.c6
1416  WS.106.1a            | 106      |                      | PS.first.d1
1417  WS.106.1b            | 106      |                      | PS.first.d2
1418  WS.106.2a            | 106      |                      | PS.first.d3
1419  WS.106.2b            | 106      |                      | PS.first.d4
1420  WS.106.3a            | 106      |                      | PS.first.d5
1421  WS.106.3b            | 106      |                      | PS.first.d6
1422 (42 rows)
1423
1424 --
1425 -- Install the central phone system and create the phone numbers.
1426 -- They are weired on insert to the patchfields. Again the
1427 -- triggers automatically tell the PSlots to update their
1428 -- backlink field.
1429 --
1430 insert into PLine values ('PL.001', '-0', 'Central call', 'PS.base.ta1');
1431 insert into PLine values ('PL.002', '-101', '', 'PS.base.ta2');
1432 insert into PLine values ('PL.003', '-102', '', 'PS.base.ta3');
1433 insert into PLine values ('PL.004', '-103', '', 'PS.base.ta5');
1434 insert into PLine values ('PL.005', '-104', '', 'PS.base.ta6');
1435 insert into PLine values ('PL.006', '-106', '', 'PS.base.tb2');
1436 insert into PLine values ('PL.007', '-108', '', 'PS.base.tb3');
1437 insert into PLine values ('PL.008', '-109', '', 'PS.base.tb4');
1438 insert into PLine values ('PL.009', '-121', '', 'PS.base.tb5');
1439 insert into PLine values ('PL.010', '-122', '', 'PS.base.tb6');
1440 insert into PLine values ('PL.015', '-134', '', 'PS.first.ta1');
1441 insert into PLine values ('PL.016', '-137', '', 'PS.first.ta3');
1442 insert into PLine values ('PL.017', '-139', '', 'PS.first.ta4');
1443 insert into PLine values ('PL.018', '-362', '', 'PS.first.tb1');
1444 insert into PLine values ('PL.019', '-363', '', 'PS.first.tb2');
1445 insert into PLine values ('PL.020', '-364', '', 'PS.first.tb3');
1446 insert into PLine values ('PL.021', '-365', '', 'PS.first.tb5');
1447 insert into PLine values ('PL.022', '-367', '', 'PS.first.tb6');
1448 insert into PLine values ('PL.028', '-501', 'Fax entrance', 'PS.base.ta2');
1449 insert into PLine values ('PL.029', '-502', 'Fax first floor', 'PS.first.ta1');
1450 --
1451 -- Buy some phones, plug them into the wall and patch the
1452 -- phone lines to the corresponding patchfield slots.
1453 --
1454 insert into PHone values ('PH.hc001', 'Hicom standard', 'WS.001.1a');
1455 update PSlot set slotlink = 'PS.base.ta1' where slotname = 'PS.base.a1';
1456 insert into PHone values ('PH.hc002', 'Hicom standard', 'WS.002.1a');
1457 update PSlot set slotlink = 'PS.base.ta5' where slotname = 'PS.base.b1';
1458 insert into PHone values ('PH.hc003', 'Hicom standard', 'WS.002.2a');
1459 update PSlot set slotlink = 'PS.base.tb2' where slotname = 'PS.base.b3';
1460 insert into PHone values ('PH.fax001', 'Canon fax', 'WS.001.2a');
1461 update PSlot set slotlink = 'PS.base.ta2' where slotname = 'PS.base.a3';
1462 --
1463 -- Install a hub at one of the patchfields, plug a computers
1464 -- ethernet interface into the wall and patch it to the hub.
1465 --
1466 insert into Hub values ('base.hub1', 'Patchfield PF0_1 hub', 16);
1467 insert into System values ('orion', 'PC');
1468 insert into IFace values ('IF', 'orion', 'eth0', 'WS.002.1b');
1469 update PSlot set slotlink = 'HS.base.hub1.1' where slotname = 'PS.base.b2';
1470 --
1471 -- Now we take a look at the patchfield
1472 --
1473 select * from PField_v1 where pfname = 'PF0_1' order by slotname;
1474  pfname |       slotname       |                         backside                         |                     patch
1475 --------+----------------------+----------------------------------------------------------+-----------------------------------------------
1476  PF0_1  | PS.base.a1           | WS.001.1a in room 001 -> Phone PH.hc001 (Hicom standard) | PS.base.ta1 -> Phone line -0 (Central call)
1477  PF0_1  | PS.base.a2           | WS.001.1b in room 001 -> -                               | -
1478  PF0_1  | PS.base.a3           | WS.001.2a in room 001 -> Phone PH.fax001 (Canon fax)     | PS.base.ta2 -> Phone line -501 (Fax entrance)
1479  PF0_1  | PS.base.a4           | WS.001.2b in room 001 -> -                               | -
1480  PF0_1  | PS.base.a5           | WS.001.3a in room 001 -> -                               | -
1481  PF0_1  | PS.base.a6           | WS.001.3b in room 001 -> -                               | -
1482  PF0_1  | PS.base.b1           | WS.002.1a in room 002 -> Phone PH.hc002 (Hicom standard) | PS.base.ta5 -> Phone line -103
1483  PF0_1  | PS.base.b2           | WS.002.1b in room 002 -> orion IF eth0 (PC)              | Patchfield PF0_1 hub slot 1
1484  PF0_1  | PS.base.b3           | WS.002.2a in room 002 -> Phone PH.hc003 (Hicom standard) | PS.base.tb2 -> Phone line -106
1485  PF0_1  | PS.base.b4           | WS.002.2b in room 002 -> -                               | -
1486  PF0_1  | PS.base.b5           | WS.002.3a in room 002 -> -                               | -
1487  PF0_1  | PS.base.b6           | WS.002.3b in room 002 -> -                               | -
1488  PF0_1  | PS.base.c1           | WS.003.1a in room 003 -> -                               | -
1489  PF0_1  | PS.base.c2           | WS.003.1b in room 003 -> -                               | -
1490  PF0_1  | PS.base.c3           | WS.003.2a in room 003 -> -                               | -
1491  PF0_1  | PS.base.c4           | WS.003.2b in room 003 -> -                               | -
1492  PF0_1  | PS.base.c5           | WS.003.3a in room 003 -> -                               | -
1493  PF0_1  | PS.base.c6           | WS.003.3b in room 003 -> -                               | -
1494 (18 rows)
1495
1496 select * from PField_v1 where pfname = 'PF0_2' order by slotname;
1497  pfname |       slotname       |            backside            |                                 patch
1498 --------+----------------------+--------------------------------+------------------------------------------------------------------------
1499  PF0_2  | PS.base.ta1          | Phone line -0 (Central call)   | PS.base.a1 -> WS.001.1a in room 001 -> Phone PH.hc001 (Hicom standard)
1500  PF0_2  | PS.base.ta2          | Phone line -501 (Fax entrance) | PS.base.a3 -> WS.001.2a in room 001 -> Phone PH.fax001 (Canon fax)
1501  PF0_2  | PS.base.ta3          | Phone line -102                | -
1502  PF0_2  | PS.base.ta4          | -                              | -
1503  PF0_2  | PS.base.ta5          | Phone line -103                | PS.base.b1 -> WS.002.1a in room 002 -> Phone PH.hc002 (Hicom standard)
1504  PF0_2  | PS.base.ta6          | Phone line -104                | -
1505  PF0_2  | PS.base.tb1          | -                              | -
1506  PF0_2  | PS.base.tb2          | Phone line -106                | PS.base.b3 -> WS.002.2a in room 002 -> Phone PH.hc003 (Hicom standard)
1507  PF0_2  | PS.base.tb3          | Phone line -108                | -
1508  PF0_2  | PS.base.tb4          | Phone line -109                | -
1509  PF0_2  | PS.base.tb5          | Phone line -121                | -
1510  PF0_2  | PS.base.tb6          | Phone line -122                | -
1511 (12 rows)
1512
1513 --
1514 -- Finally we want errors
1515 --
1516 insert into PField values ('PF1_1', 'should fail due to unique index');
1517 ERROR:  duplicate key value violates unique constraint "pfield_name"
1518 DETAIL:  Key (name)=(PF1_1) already exists.
1519 update PSlot set backlink = 'WS.not.there' where slotname = 'PS.base.a1';
1520 ERROR:  WS.not.there         does not exist
1521 CONTEXT:  PL/pgSQL function "tg_backlink_a" line 16 at assignment
1522 update PSlot set backlink = 'XX.illegal' where slotname = 'PS.base.a1';
1523 ERROR:  illegal backlink beginning with XX
1524 CONTEXT:  PL/pgSQL function "tg_backlink_a" line 16 at assignment
1525 update PSlot set slotlink = 'PS.not.there' where slotname = 'PS.base.a1';
1526 ERROR:  PS.not.there         does not exist
1527 CONTEXT:  PL/pgSQL function "tg_slotlink_a" line 16 at assignment
1528 update PSlot set slotlink = 'XX.illegal' where slotname = 'PS.base.a1';
1529 ERROR:  illegal slotlink beginning with XX
1530 CONTEXT:  PL/pgSQL function "tg_slotlink_a" line 16 at assignment
1531 insert into HSlot values ('HS', 'base.hub1', 1, '');
1532 ERROR:  duplicate key value violates unique constraint "hslot_name"
1533 DETAIL:  Key (slotname)=(HS.base.hub1.1      ) already exists.
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
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  j
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  i
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  j
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 duplic(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 duplic(42);
1846  j  |    k
1847 ----+---------
1848  42 | {42,42}
1849 (1 row)
1850
1851 select * from duplic('foo'::text);
1852   j  |     k
1853 -----+-----------
1854  foo | {foo,foo}
1855 (1 row)
1856
1857 drop function duplic(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 2 seconds:
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 2000;
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 drop table foo;
2052 -- Test for pass-by-ref values being stored in proper context
2053 create function test_variable_storage() returns text as $$
2054 declare x text;
2055 begin
2056   x := '1234';
2057   begin
2058     x := x || '5678';
2059     -- force error inside subtransaction SPI context
2060     perform trap_zero_divide(-100);
2061   exception
2062     when others then
2063       x := x || '9012';
2064   end;
2065   return x;
2066 end$$ language plpgsql;
2067 select test_variable_storage();
2068 NOTICE:  should see this
2069 CONTEXT:  SQL statement "SELECT trap_zero_divide(-100)"
2070 PL/pgSQL function "test_variable_storage" line 7 at PERFORM
2071 NOTICE:  should see this only if -100 <> 0
2072 CONTEXT:  SQL statement "SELECT trap_zero_divide(-100)"
2073 PL/pgSQL function "test_variable_storage" line 7 at PERFORM
2074 NOTICE:  should see this only if -100 fits in smallint
2075 CONTEXT:  SQL statement "SELECT trap_zero_divide(-100)"
2076 PL/pgSQL function "test_variable_storage" line 7 at PERFORM
2077  test_variable_storage
2078 -----------------------
2079  123456789012
2080 (1 row)
2081
2082 --
2083 -- test foreign key error trapping
2084 --
2085 create temp table master(f1 int primary key);
2086 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "master_pkey" for table "master"
2087 create temp table slave(f1 int references master deferrable);
2088 insert into master values(1);
2089 insert into slave values(1);
2090 insert into slave values(2);    -- fails
2091 ERROR:  insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
2092 DETAIL:  Key (f1)=(2) is not present in table "master".
2093 create function trap_foreign_key(int) returns int as $$
2094 begin
2095         begin   -- start a subtransaction
2096                 insert into slave values($1);
2097         exception
2098                 when foreign_key_violation then
2099                         raise notice 'caught foreign_key_violation';
2100                         return 0;
2101         end;
2102         return 1;
2103 end$$ language plpgsql;
2104 create function trap_foreign_key_2() returns int as $$
2105 begin
2106         begin   -- start a subtransaction
2107                 set constraints all immediate;
2108         exception
2109                 when foreign_key_violation then
2110                         raise notice 'caught foreign_key_violation';
2111                         return 0;
2112         end;
2113         return 1;
2114 end$$ language plpgsql;
2115 select trap_foreign_key(1);
2116  trap_foreign_key
2117 ------------------
2118                 1
2119 (1 row)
2120
2121 select trap_foreign_key(2);     -- detects FK violation
2122 NOTICE:  caught foreign_key_violation
2123  trap_foreign_key
2124 ------------------
2125                 0
2126 (1 row)
2127
2128 begin;
2129   set constraints all deferred;
2130   select trap_foreign_key(2);   -- should not detect FK violation
2131  trap_foreign_key
2132 ------------------
2133                 1
2134 (1 row)
2135
2136   savepoint x;
2137     set constraints all immediate; -- fails
2138 ERROR:  insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
2139 DETAIL:  Key (f1)=(2) is not present in table "master".
2140   rollback to x;
2141   select trap_foreign_key_2();  -- detects FK violation
2142 NOTICE:  caught foreign_key_violation
2143  trap_foreign_key_2
2144 --------------------
2145                   0
2146 (1 row)
2147
2148 commit;                         -- still fails
2149 ERROR:  insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
2150 DETAIL:  Key (f1)=(2) is not present in table "master".
2151 drop function trap_foreign_key(int);
2152 drop function trap_foreign_key_2();
2153 --
2154 -- Test proper snapshot handling in simple expressions
2155 --
2156 create temp table users(login text, id serial);
2157 NOTICE:  CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id"
2158 create function sp_id_user(a_login text) returns int as $$
2159 declare x int;
2160 begin
2161   select into x id from users where login = a_login;
2162   if found then return x; end if;
2163   return 0;
2164 end$$ language plpgsql stable;
2165 insert into users values('user1');
2166 select sp_id_user('user1');
2167  sp_id_user
2168 ------------
2169           1
2170 (1 row)
2171
2172 select sp_id_user('userx');
2173  sp_id_user
2174 ------------
2175           0
2176 (1 row)
2177
2178 create function sp_add_user(a_login text) returns int as $$
2179 declare my_id_user int;
2180 begin
2181   my_id_user = sp_id_user( a_login );
2182   IF  my_id_user > 0 THEN
2183     RETURN -1;  -- error code for existing user
2184   END IF;
2185   INSERT INTO users ( login ) VALUES ( a_login );
2186   my_id_user = sp_id_user( a_login );
2187   IF  my_id_user = 0 THEN
2188     RETURN -2;  -- error code for insertion failure
2189   END IF;
2190   RETURN my_id_user;
2191 end$$ language plpgsql;
2192 select sp_add_user('user1');
2193  sp_add_user
2194 -------------
2195           -1
2196 (1 row)
2197
2198 select sp_add_user('user2');
2199  sp_add_user
2200 -------------
2201            2
2202 (1 row)
2203
2204 select sp_add_user('user2');
2205  sp_add_user
2206 -------------
2207           -1
2208 (1 row)
2209
2210 select sp_add_user('user3');
2211  sp_add_user
2212 -------------
2213            3
2214 (1 row)
2215
2216 select sp_add_user('user3');
2217  sp_add_user
2218 -------------
2219           -1
2220 (1 row)
2221
2222 drop function sp_add_user(text);
2223 drop function sp_id_user(text);
2224 --
2225 -- tests for refcursors
2226 --
2227 create table rc_test (a int, b int);
2228 copy rc_test from stdin;
2229 create function return_refcursor(rc refcursor) returns refcursor as $$
2230 begin
2231     open rc for select a from rc_test;
2232     return rc;
2233 end
2234 $$ language plpgsql;
2235 create function refcursor_test1(refcursor) returns refcursor as $$
2236 begin
2237     perform return_refcursor($1);
2238     return $1;
2239 end
2240 $$ language plpgsql;
2241 begin;
2242 select refcursor_test1('test1');
2243  refcursor_test1
2244 -----------------
2245  test1
2246 (1 row)
2247
2248 fetch next in test1;
2249  a
2250 ---
2251  5
2252 (1 row)
2253
2254 select refcursor_test1('test2');
2255  refcursor_test1
2256 -----------------
2257  test2
2258 (1 row)
2259
2260 fetch all from test2;
2261   a
2262 -----
2263    5
2264   50
2265  500
2266 (3 rows)
2267
2268 commit;
2269 -- should fail
2270 fetch next from test1;
2271 ERROR:  cursor "test1" does not exist
2272 create function refcursor_test2(int, int) returns boolean as $$
2273 declare
2274     c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
2275     nonsense record;
2276 begin
2277     open c1($1, $2);
2278     fetch c1 into nonsense;
2279     close c1;
2280     if found then
2281         return true;
2282     else
2283         return false;
2284     end if;
2285 end
2286 $$ language plpgsql;
2287 select refcursor_test2(20000, 20000) as "Should be false",
2288        refcursor_test2(20, 20) as "Should be true";
2289  Should be false | Should be true
2290 -----------------+----------------
2291  f               | t
2292 (1 row)
2293
2294 --
2295 -- tests for "raise" processing
2296 --
2297 create function raise_test1(int) returns int as $$
2298 begin
2299     raise notice 'This message has too many parameters!', $1;
2300     return $1;
2301 end;
2302 $$ language plpgsql;
2303 select raise_test1(5);
2304 ERROR:  too many parameters specified for RAISE
2305 CONTEXT:  PL/pgSQL function "raise_test1" line 2 at RAISE
2306 create function raise_test2(int) returns int as $$
2307 begin
2308     raise notice 'This message has too few parameters: %, %, %', $1, $1;
2309     return $1;
2310 end;
2311 $$ language plpgsql;
2312 select raise_test2(10);
2313 ERROR:  too few parameters specified for RAISE
2314 CONTEXT:  PL/pgSQL function "raise_test2" line 2 at RAISE
2315 --
2316 -- reject function definitions that contain malformed SQL queries at
2317 -- compile-time, where possible
2318 --
2319 create function bad_sql1() returns int as $$
2320 declare a int;
2321 begin
2322     a := 5;
2323     Johnny Yuma;
2324     a := 10;
2325     return a;
2326 end$$ language plpgsql;
2327 ERROR:  syntax error at or near "Johnny"
2328 LINE 5:     Johnny Yuma;
2329             ^
2330 create function bad_sql2() returns int as $$
2331 declare r record;
2332 begin
2333     for r in select I fought the law, the law won LOOP
2334         raise notice 'in loop';
2335     end loop;
2336     return 5;
2337 end;$$ language plpgsql;
2338 ERROR:  syntax error at or near "the"
2339 LINE 4:     for r in select I fought the law, the law won LOOP
2340                                      ^
2341 -- a RETURN expression is mandatory, except for void-returning
2342 -- functions, where it is not allowed
2343 create function missing_return_expr() returns int as $$
2344 begin
2345     return ;
2346 end;$$ language plpgsql;
2347 ERROR:  missing expression at or near ";"
2348 LINE 3:     return ;
2349                    ^
2350 create function void_return_expr() returns void as $$
2351 begin
2352     return 5;
2353 end;$$ language plpgsql;
2354 ERROR:  RETURN cannot have a parameter in function returning void
2355 LINE 3:     return 5;
2356                    ^
2357 -- VOID functions are allowed to omit RETURN
2358 create function void_return_expr() returns void as $$
2359 begin
2360     perform 2+2;
2361 end;$$ language plpgsql;
2362 select void_return_expr();
2363  void_return_expr
2364 ------------------
2365
2366 (1 row)
2367
2368 -- but ordinary functions are not
2369 create function missing_return_expr() returns int as $$
2370 begin
2371     perform 2+2;
2372 end;$$ language plpgsql;
2373 select missing_return_expr();
2374 ERROR:  control reached end of function without RETURN
2375 CONTEXT:  PL/pgSQL function "missing_return_expr"
2376 drop function void_return_expr();
2377 drop function missing_return_expr();
2378 --
2379 -- EXECUTE ... INTO test
2380 --
2381 create table eifoo (i integer, y integer);
2382 create type eitype as (i integer, y integer);
2383 create or replace function execute_into_test(varchar) returns record as $$
2384 declare
2385     _r record;
2386     _rt eifoo%rowtype;
2387     _v eitype;
2388     i int;
2389     j int;
2390     k int;
2391 begin
2392     execute 'insert into '||$1||' values(10,15)';
2393     execute 'select (row).* from (select row(10,1)::eifoo) s' into _r;
2394     raise notice '% %', _r.i, _r.y;
2395     execute 'select * from '||$1||' limit 1' into _rt;
2396     raise notice '% %', _rt.i, _rt.y;
2397     execute 'select *, 20 from '||$1||' limit 1' into i, j, k;
2398     raise notice '% % %', i, j, k;
2399     execute 'select 1,2' into _v;
2400     return _v;
2401 end; $$ language plpgsql;
2402 select execute_into_test('eifoo');
2403 NOTICE:  10 1
2404 NOTICE:  10 15
2405 NOTICE:  10 15 20
2406  execute_into_test
2407 -------------------
2408  (1,2)
2409 (1 row)
2410
2411 drop table eifoo cascade;
2412 drop type eitype cascade;
2413 --
2414 -- SQLSTATE and SQLERRM test
2415 --
2416 create function excpt_test1() returns void as $$
2417 begin
2418     raise notice '% %', sqlstate, sqlerrm;
2419 end; $$ language plpgsql;
2420 -- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
2421 -- blocks
2422 select excpt_test1();
2423 ERROR:  column "sqlstate" does not exist
2424 LINE 1: SELECT sqlstate
2425                ^
2426 QUERY:  SELECT sqlstate
2427 CONTEXT:  PL/pgSQL function "excpt_test1" line 2 at RAISE
2428 create function excpt_test2() returns void as $$
2429 begin
2430     begin
2431         begin
2432             raise notice '% %', sqlstate, sqlerrm;
2433         end;
2434     end;
2435 end; $$ language plpgsql;
2436 -- should fail
2437 select excpt_test2();
2438 ERROR:  column "sqlstate" does not exist
2439 LINE 1: SELECT sqlstate
2440                ^
2441 QUERY:  SELECT sqlstate
2442 CONTEXT:  PL/pgSQL function "excpt_test2" line 4 at RAISE
2443 create function excpt_test3() returns void as $$
2444 begin
2445     begin
2446         raise exception 'user exception';
2447     exception when others then
2448             raise notice 'caught exception % %', sqlstate, sqlerrm;
2449             begin
2450                 raise notice '% %', sqlstate, sqlerrm;
2451                 perform 10/0;
2452         exception
2453             when substring_error then
2454                 -- this exception handler shouldn't be invoked
2455                 raise notice 'unexpected exception: % %', sqlstate, sqlerrm;
2456                 when division_by_zero then
2457                     raise notice 'caught exception % %', sqlstate, sqlerrm;
2458             end;
2459             raise notice '% %', sqlstate, sqlerrm;
2460     end;
2461 end; $$ language plpgsql;
2462 select excpt_test3();
2463 NOTICE:  caught exception P0001 user exception
2464 NOTICE:  P0001 user exception
2465 NOTICE:  caught exception 22012 division by zero
2466 NOTICE:  P0001 user exception
2467  excpt_test3
2468 -------------
2469
2470 (1 row)
2471
2472 drop function excpt_test1();
2473 drop function excpt_test2();
2474 drop function excpt_test3();
2475 -- parameters of raise stmt can be expressions
2476 create function raise_exprs() returns void as $$
2477 declare
2478     a integer[] = '{10,20,30}';
2479     c varchar = 'xyz';
2480     i integer;
2481 begin
2482     i := 2;
2483     raise notice '%; %; %; %; %; %', a, a[i], c, (select c || 'abc'), row(10,'aaa',NULL,30), NULL;
2484 end;$$ language plpgsql;
2485 select raise_exprs();
2486 NOTICE:  {10,20,30}; 20; xyz; xyzabc; (10,aaa,,30); <NULL>
2487  raise_exprs
2488 -------------
2489
2490 (1 row)
2491
2492 drop function raise_exprs();
2493 -- continue statement
2494 create table conttesttbl(idx serial, v integer);
2495 NOTICE:  CREATE TABLE will create implicit sequence "conttesttbl_idx_seq" for serial column "conttesttbl.idx"
2496 insert into conttesttbl(v) values(10);
2497 insert into conttesttbl(v) values(20);
2498 insert into conttesttbl(v) values(30);
2499 insert into conttesttbl(v) values(40);
2500 create function continue_test1() returns void as $$
2501 declare _i integer = 0; _r record;
2502 begin
2503   raise notice '---1---';
2504   loop
2505     _i := _i + 1;
2506     raise notice '%', _i;
2507     continue when _i < 10;
2508     exit;
2509   end loop;
2510
2511   raise notice '---2---';
2512   <<lbl>>
2513   loop
2514     _i := _i - 1;
2515     loop
2516       raise notice '%', _i;
2517       continue lbl when _i > 0;
2518       exit lbl;
2519     end loop;
2520   end loop;
2521
2522   raise notice '---3---';
2523   <<the_loop>>
2524   while _i < 10 loop
2525     _i := _i + 1;
2526     continue the_loop when _i % 2 = 0;
2527     raise notice '%', _i;
2528   end loop;
2529
2530   raise notice '---4---';
2531   for _i in 1..10 loop
2532     begin
2533       -- applies to outer loop, not the nested begin block
2534       continue when _i < 5;
2535       raise notice '%', _i;
2536     end;
2537   end loop;
2538
2539   raise notice '---5---';
2540   for _r in select * from conttesttbl loop
2541     continue when _r.v <= 20;
2542     raise notice '%', _r.v;
2543   end loop;
2544
2545   raise notice '---6---';
2546   for _r in execute 'select * from conttesttbl' loop
2547     continue when _r.v <= 20;
2548     raise notice '%', _r.v;
2549   end loop;
2550
2551   raise notice '---7---';
2552   for _i in 1..3 loop
2553     raise notice '%', _i;
2554     continue when _i = 3;
2555   end loop;
2556
2557   raise notice '---8---';
2558   _i := 1;
2559   while _i <= 3 loop
2560     raise notice '%', _i;
2561     _i := _i + 1;
2562     continue when _i = 3;
2563   end loop;
2564
2565   raise notice '---9---';
2566   for _r in select * from conttesttbl order by v limit 1 loop
2567     raise notice '%', _r.v;
2568     continue;
2569   end loop;
2570
2571   raise notice '---10---';
2572   for _r in execute 'select * from conttesttbl order by v limit 1' loop
2573     raise notice '%', _r.v;
2574     continue;
2575   end loop;
2576 end; $$ language plpgsql;
2577 select continue_test1();
2578 NOTICE:  ---1---
2579 NOTICE:  1
2580 NOTICE:  2
2581 NOTICE:  3
2582 NOTICE:  4
2583 NOTICE:  5
2584 NOTICE:  6
2585 NOTICE:  7
2586 NOTICE:  8
2587 NOTICE:  9
2588 NOTICE:  10
2589 NOTICE:  ---2---
2590 NOTICE:  9
2591 NOTICE:  8
2592 NOTICE:  7
2593 NOTICE:  6
2594 NOTICE:  5
2595 NOTICE:  4
2596 NOTICE:  3
2597 NOTICE:  2
2598 NOTICE:  1
2599 NOTICE:  0
2600 NOTICE:  ---3---
2601 NOTICE:  1
2602 NOTICE:  3
2603 NOTICE:  5
2604 NOTICE:  7
2605 NOTICE:  9
2606 NOTICE:  ---4---
2607 NOTICE:  5
2608 NOTICE:  6
2609 NOTICE:  7
2610 NOTICE:  8
2611 NOTICE:  9
2612 NOTICE:  10
2613 NOTICE:  ---5---
2614 NOTICE:  30
2615 NOTICE:  40
2616 NOTICE:  ---6---
2617 NOTICE:  30
2618 NOTICE:  40
2619 NOTICE:  ---7---
2620 NOTICE:  1
2621 NOTICE:  2
2622 NOTICE:  3
2623 NOTICE:  ---8---
2624 NOTICE:  1
2625 NOTICE:  2
2626 NOTICE:  3
2627 NOTICE:  ---9---
2628 NOTICE:  10
2629 NOTICE:  ---10---
2630 NOTICE:  10
2631  continue_test1
2632 ----------------
2633
2634 (1 row)
2635
2636 -- CONTINUE is only legal inside a loop
2637 create function continue_test2() returns void as $$
2638 begin
2639     begin
2640         continue;
2641     end;
2642     return;
2643 end;
2644 $$ language plpgsql;
2645 -- should fail
2646 select continue_test2();
2647 ERROR:  CONTINUE cannot be used outside a loop
2648 CONTEXT:  PL/pgSQL function "continue_test2"
2649 -- CONTINUE can't reference the label of a named block
2650 create function continue_test3() returns void as $$
2651 begin
2652     <<begin_block1>>
2653     begin
2654         loop
2655             continue begin_block1;
2656         end loop;
2657     end;
2658 end;
2659 $$ language plpgsql;
2660 -- should fail
2661 select continue_test3();
2662 ERROR:  CONTINUE cannot be used outside a loop
2663 CONTEXT:  PL/pgSQL function "continue_test3"
2664 drop function continue_test1();
2665 drop function continue_test2();
2666 drop function continue_test3();
2667 drop table conttesttbl;
2668 -- verbose end block and end loop
2669 create function end_label1() returns void as $$
2670 <<blbl>>
2671 begin
2672   <<flbl1>>
2673   for _i in 1 .. 10 loop
2674     exit flbl1;
2675   end loop flbl1;
2676   <<flbl2>>
2677   for _i in 1 .. 10 loop
2678     exit flbl2;
2679   end loop;
2680 end blbl;
2681 $$ language plpgsql;
2682 select end_label1();
2683  end_label1
2684 ------------
2685
2686 (1 row)
2687
2688 drop function end_label1();
2689 -- should fail: undefined end label
2690 create function end_label2() returns void as $$
2691 begin
2692   for _i in 1 .. 10 loop
2693     exit;
2694   end loop flbl1;
2695 end;
2696 $$ language plpgsql;
2697 ERROR:  label does not exist at or near "flbl1"
2698 LINE 5:   end loop flbl1;
2699                    ^
2700 -- should fail: end label does not match start label
2701 create function end_label3() returns void as $$
2702 <<outer_label>>
2703 begin
2704   <<inner_label>>
2705   for _i in 1 .. 10 loop
2706     exit;
2707   end loop outer_label;
2708 end;
2709 $$ language plpgsql;
2710 ERROR:  end label "outer_label" differs from block's label "inner_label"
2711 LINE 7:   end loop outer_label;
2712                    ^
2713 -- should fail: end label on a block without a start label
2714 create function end_label4() returns void as $$
2715 <<outer_label>>
2716 begin
2717   for _i in 1 .. 10 loop
2718     exit;
2719   end loop outer_label;
2720 end;
2721 $$ language plpgsql;
2722 ERROR:  end label "outer_label" specified for unlabelled block
2723 LINE 6:   end loop outer_label;
2724                    ^
2725 -- using list of scalars in fori and fore stmts
2726 create function for_vect() returns void as $proc$
2727 <<lbl>>declare a integer; b varchar; c varchar; r record;
2728 begin
2729   -- fori
2730   for i in 1 .. 3 loop
2731     raise notice '%', i;
2732   end loop;
2733   -- fore with record var
2734   for r in select gs as aa, 'BB' as bb, 'CC' as cc from generate_series(1,4) gs loop
2735     raise notice '% % %', r.aa, r.bb, r.cc;
2736   end loop;
2737   -- fore with single scalar
2738   for a in select gs from generate_series(1,4) gs loop
2739     raise notice '%', a;
2740   end loop;
2741   -- fore with multiple scalars
2742   for a,b,c in select gs, 'BB','CC' from generate_series(1,4) gs loop
2743     raise notice '% % %', a, b, c;
2744   end loop;
2745   -- using qualified names in fors, fore is enabled, disabled only for fori
2746   for lbl.a, lbl.b, lbl.c in execute $$select gs, 'bb','cc' from generate_series(1,4) gs$$ loop
2747     raise notice '% % %', a, b, c;
2748   end loop;
2749 end;
2750 $proc$ language plpgsql;
2751 select for_vect();
2752 NOTICE:  1
2753 NOTICE:  2
2754 NOTICE:  3
2755 NOTICE:  1 BB CC
2756 NOTICE:  2 BB CC
2757 NOTICE:  3 BB CC
2758 NOTICE:  4 BB CC
2759 NOTICE:  1
2760 NOTICE:  2
2761 NOTICE:  3
2762 NOTICE:  4
2763 NOTICE:  1 BB CC
2764 NOTICE:  2 BB CC
2765 NOTICE:  3 BB CC
2766 NOTICE:  4 BB CC
2767 NOTICE:  1 bb cc
2768 NOTICE:  2 bb cc
2769 NOTICE:  3 bb cc
2770 NOTICE:  4 bb cc
2771  for_vect
2772 ----------
2773
2774 (1 row)
2775
2776 -- regression test: verify that multiple uses of same plpgsql datum within
2777 -- a SQL command all get mapped to the same $n parameter.  The return value
2778 -- of the SELECT is not important, we only care that it doesn't fail with
2779 -- a complaint about an ungrouped column reference.
2780 create function multi_datum_use(p1 int) returns bool as $$
2781 declare
2782   x int;
2783   y int;
2784 begin
2785   select into x,y unique1/p1, unique1/$1 from tenk1 group by unique1/p1;
2786   return x = y;
2787 end$$ language plpgsql;
2788 select multi_datum_use(42);
2789  multi_datum_use
2790 -----------------
2791  t
2792 (1 row)
2793
2794 --
2795 -- Test STRICT limiter in both planned and EXECUTE invocations.
2796 -- Note that a data-modifying query is quasi strict (disallow multi rows)
2797 -- by default in the planned case, but not in EXECUTE.
2798 --
2799 create temp table foo (f1 int, f2 int);
2800 insert into foo values (1,2), (3,4);
2801 create or replace function footest() returns void as $$
2802 declare x record;
2803 begin
2804   -- should work
2805   insert into foo values(5,6) returning * into x;
2806   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2807 end$$ language plpgsql;
2808 select footest();
2809 NOTICE:  x.f1 = 5, x.f2 = 6
2810  footest
2811 ---------
2812
2813 (1 row)
2814
2815 create or replace function footest() returns void as $$
2816 declare x record;
2817 begin
2818   -- should fail due to implicit strict
2819   insert into foo values(7,8),(9,10) returning * into x;
2820   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2821 end$$ language plpgsql;
2822 select footest();
2823 ERROR:  query returned more than one row
2824 CONTEXT:  PL/pgSQL function "footest" line 4 at SQL statement
2825 create or replace function footest() returns void as $$
2826 declare x record;
2827 begin
2828   -- should work
2829   execute 'insert into foo values(5,6) returning *' into x;
2830   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2831 end$$ language plpgsql;
2832 select footest();
2833 NOTICE:  x.f1 = 5, x.f2 = 6
2834  footest
2835 ---------
2836
2837 (1 row)
2838
2839 create or replace function footest() returns void as $$
2840 declare x record;
2841 begin
2842   -- this should work since EXECUTE isn't as picky
2843   execute 'insert into foo values(7,8),(9,10) returning *' into x;
2844   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2845 end$$ language plpgsql;
2846 select footest();
2847 NOTICE:  x.f1 = 7, x.f2 = 8
2848  footest
2849 ---------
2850
2851 (1 row)
2852
2853 select * from foo;
2854  f1 | f2
2855 ----+----
2856   1 |  2
2857   3 |  4
2858   5 |  6
2859   5 |  6
2860   7 |  8
2861   9 | 10
2862 (6 rows)
2863
2864 create or replace function footest() returns void as $$
2865 declare x record;
2866 begin
2867   -- should work
2868   select * from foo where f1 = 3 into strict x;
2869   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2870 end$$ language plpgsql;
2871 select footest();
2872 NOTICE:  x.f1 = 3, x.f2 = 4
2873  footest
2874 ---------
2875
2876 (1 row)
2877
2878 create or replace function footest() returns void as $$
2879 declare x record;
2880 begin
2881   -- should fail, no rows
2882   select * from foo where f1 = 0 into strict x;
2883   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2884 end$$ language plpgsql;
2885 select footest();
2886 ERROR:  query returned no rows
2887 CONTEXT:  PL/pgSQL function "footest" line 4 at SQL statement
2888 create or replace function footest() returns void as $$
2889 declare x record;
2890 begin
2891   -- should fail, too many rows
2892   select * from foo where f1 > 3 into strict x;
2893   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2894 end$$ language plpgsql;
2895 select footest();
2896 ERROR:  query returned more than one row
2897 CONTEXT:  PL/pgSQL function "footest" line 4 at SQL statement
2898 create or replace function footest() returns void as $$
2899 declare x record;
2900 begin
2901   -- should work
2902   execute 'select * from foo where f1 = 3' into strict x;
2903   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2904 end$$ language plpgsql;
2905 select footest();
2906 NOTICE:  x.f1 = 3, x.f2 = 4
2907  footest
2908 ---------
2909
2910 (1 row)
2911
2912 create or replace function footest() returns void as $$
2913 declare x record;
2914 begin
2915   -- should fail, no rows
2916   execute 'select * from foo where f1 = 0' into strict x;
2917   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2918 end$$ language plpgsql;
2919 select footest();
2920 ERROR:  query returned no rows
2921 CONTEXT:  PL/pgSQL function "footest" line 4 at EXECUTE statement
2922 create or replace function footest() returns void as $$
2923 declare x record;
2924 begin
2925   -- should fail, too many rows
2926   execute 'select * from foo where f1 > 3' into strict x;
2927   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2928 end$$ language plpgsql;
2929 select footest();
2930 ERROR:  query returned more than one row
2931 CONTEXT:  PL/pgSQL function "footest" line 4 at EXECUTE statement
2932 drop function footest();
2933 -- test scrollable cursor support
2934 create function sc_test() returns setof integer as $$
2935 declare
2936   c scroll cursor for select f1 from int4_tbl;
2937   x integer;
2938 begin
2939   open c;
2940   fetch last from c into x;
2941   while found loop
2942     return next x;
2943     fetch prior from c into x;
2944   end loop;
2945   close c;
2946 end;
2947 $$ language plpgsql;
2948 select * from sc_test();
2949    sc_test
2950 -------------
2951  -2147483647
2952   2147483647
2953      -123456
2954       123456
2955            0
2956 (5 rows)
2957
2958 create or replace function sc_test() returns setof integer as $$
2959 declare
2960   c no scroll cursor for select f1 from int4_tbl;
2961   x integer;
2962 begin
2963   open c;
2964   fetch last from c into x;
2965   while found loop
2966     return next x;
2967     fetch prior from c into x;
2968   end loop;
2969   close c;
2970 end;
2971 $$ language plpgsql;
2972 select * from sc_test();  -- fails because of NO SCROLL specification
2973 ERROR:  cursor can only scan forward
2974 HINT:  Declare it with SCROLL option to enable backward scan.
2975 CONTEXT:  PL/pgSQL function "sc_test" line 6 at FETCH
2976 create or replace function sc_test() returns setof integer as $$
2977 declare
2978   c refcursor;
2979   x integer;
2980 begin
2981   open c scroll for select f1 from int4_tbl;
2982   fetch last from c into x;
2983   while found loop
2984     return next x;
2985     fetch prior from c into x;
2986   end loop;
2987   close c;
2988 end;
2989 $$ language plpgsql;
2990 select * from sc_test();
2991    sc_test
2992 -------------
2993  -2147483647
2994   2147483647
2995      -123456
2996       123456
2997            0
2998 (5 rows)
2999
3000 create or replace function sc_test() returns setof integer as $$
3001 declare
3002   c refcursor;
3003   x integer;
3004 begin
3005   open c scroll for execute 'select f1 from int4_tbl';
3006   fetch last from c into x;
3007   while found loop
3008     return next x;
3009     fetch relative -2 from c into x;
3010   end loop;
3011   close c;
3012 end;
3013 $$ language plpgsql;
3014 select * from sc_test();
3015    sc_test
3016 -------------
3017  -2147483647
3018      -123456
3019            0
3020 (3 rows)
3021
3022 create or replace function sc_test() returns setof integer as $$
3023 declare
3024   c refcursor;
3025   x integer;
3026 begin
3027   open c scroll for execute 'select f1 from int4_tbl';
3028   fetch last from c into x;
3029   while found loop
3030     return next x;
3031     move backward 2 from c;
3032     fetch relative -1 from c into x;
3033   end loop;
3034   close c;
3035 end;
3036 $$ language plpgsql;
3037 select * from sc_test();
3038    sc_test
3039 -------------
3040  -2147483647
3041       123456
3042 (2 rows)
3043
3044 create or replace function sc_test() returns setof integer as $$
3045 declare
3046   c cursor for select * from generate_series(1, 10);
3047   x integer;
3048 begin
3049   open c;
3050   loop
3051       move relative 2 in c;
3052       if not found then
3053           exit;
3054       end if;
3055       fetch next from c into x;
3056       if found then
3057           return next x;
3058       end if;
3059   end loop;
3060   close c;
3061 end;
3062 $$ language plpgsql;
3063 select * from sc_test();
3064  sc_test
3065 ---------
3066        3
3067        6
3068        9
3069 (3 rows)
3070
3071 create or replace function sc_test() returns setof integer as $$
3072 declare
3073   c cursor for select * from generate_series(1, 10);
3074   x integer;
3075 begin
3076   open c;
3077   move forward all in c;
3078   fetch backward from c into x;
3079   if found then
3080     return next x;
3081   end if;
3082   close c;
3083 end;
3084 $$ language plpgsql;
3085 select * from sc_test();
3086  sc_test
3087 ---------
3088       10
3089 (1 row)
3090
3091 drop function sc_test();
3092 -- test qualified variable names
3093 create function pl_qual_names (param1 int) returns void as $$
3094 <<outerblock>>
3095 declare
3096   param1 int := 1;
3097 begin
3098   <<innerblock>>
3099   declare
3100     param1 int := 2;
3101   begin
3102     raise notice 'param1 = %', param1;
3103     raise notice 'pl_qual_names.param1 = %', pl_qual_names.param1;
3104     raise notice 'outerblock.param1 = %', outerblock.param1;
3105     raise notice 'innerblock.param1 = %', innerblock.param1;
3106   end;
3107 end;
3108 $$ language plpgsql;
3109 select pl_qual_names(42);
3110 NOTICE:  param1 = 2
3111 NOTICE:  pl_qual_names.param1 = 42
3112 NOTICE:  outerblock.param1 = 1
3113 NOTICE:  innerblock.param1 = 2
3114  pl_qual_names
3115 ---------------
3116
3117 (1 row)
3118
3119 drop function pl_qual_names(int);
3120 -- tests for RETURN QUERY
3121 create function ret_query1(out int, out int) returns setof record as $$
3122 begin
3123     $1 := -1;
3124     $2 := -2;
3125     return next;
3126     return query select x + 1, x * 10 from generate_series(0, 10) s (x);
3127     return next;
3128 end;
3129 $$ language plpgsql;
3130 select * from ret_query1();
3131  column1 | column2
3132 ---------+---------
3133       -1 |      -2
3134        1 |       0
3135        2 |      10
3136        3 |      20
3137        4 |      30
3138        5 |      40
3139        6 |      50
3140        7 |      60
3141        8 |      70
3142        9 |      80
3143       10 |      90
3144       11 |     100
3145       -1 |      -2
3146 (13 rows)
3147
3148 create type record_type as (x text, y int, z boolean);
3149 create or replace function ret_query2(lim int) returns setof record_type as $$
3150 begin
3151     return query select md5(s.x::text), s.x, s.x > 0
3152                  from generate_series(-8, lim) s (x) where s.x % 2 = 0;
3153 end;
3154 $$ language plpgsql;
3155 select * from ret_query2(8);
3156                 x                 | y  | z
3157 ----------------------------------+----+---
3158  a8d2ec85eaf98407310b72eb73dda247 | -8 | f
3159  596a3d04481816330f07e4f97510c28f | -6 | f
3160  0267aaf632e87a63288a08331f22c7c3 | -4 | f
3161  5d7b9adcbe1c629ec722529dd12e5129 | -2 | f
3162  cfcd208495d565ef66e7dff9f98764da |  0 | f
3163  c81e728d9d4c2f636f067f89cc14862c |  2 | t
3164  a87ff679a2f3e71d9181a67b7542122c |  4 | t
3165  1679091c5a880faf6fb5e6087eb1b2dc |  6 | t
3166  c9f0f895fb98ab9159f51fd0297e236d |  8 | t
3167 (9 rows)
3168
3169 -- test EXECUTE USING
3170 create function exc_using(int, text) returns int as $$
3171 declare i int;
3172 begin
3173   for i in execute 'select * from generate_series(1,$1)' using $1+1 loop
3174     raise notice '%', i;
3175   end loop;
3176   execute 'select $2 + $2*3 + length($1)' into i using $2,$1;
3177   return i;
3178 end
3179 $$ language plpgsql;
3180 select exc_using(5, 'foobar');
3181 NOTICE:  1
3182 NOTICE:  2
3183 NOTICE:  3
3184 NOTICE:  4
3185 NOTICE:  5
3186 NOTICE:  6
3187  exc_using
3188 -----------
3189         26
3190 (1 row)
3191
3192 drop function exc_using(int, text);
3193 create or replace function exc_using(int) returns void as $$
3194 declare
3195   c refcursor;
3196   i int;
3197 begin
3198   open c for execute 'select * from generate_series(1,$1)' using $1+1;
3199   loop
3200     fetch c into i;
3201     exit when not found;
3202     raise notice '%', i;
3203   end loop;
3204   close c;
3205   return;
3206 end;
3207 $$ language plpgsql;
3208 select exc_using(5);
3209 NOTICE:  1
3210 NOTICE:  2
3211 NOTICE:  3
3212 NOTICE:  4
3213 NOTICE:  5
3214 NOTICE:  6
3215  exc_using
3216 -----------
3217
3218 (1 row)
3219
3220 drop function exc_using(int);
3221 -- test FOR-over-cursor
3222 create or replace function forc01() returns void as $$
3223 declare
3224   c cursor(r1 integer, r2 integer)
3225        for select * from generate_series(r1,r2) i;
3226   c2 cursor
3227        for select * from generate_series(41,43) i;
3228 begin
3229   for r in c(5,7) loop
3230     raise notice '% from %', r.i, c;
3231   end loop;
3232   -- again, to test if cursor was closed properly
3233   for r in c(9,10) loop
3234     raise notice '% from %', r.i, c;
3235   end loop;
3236   -- and test a parameterless cursor
3237   for r in c2 loop
3238     raise notice '% from %', r.i, c2;
3239   end loop;
3240   -- and try it with a hand-assigned name
3241   raise notice 'after loop, c2 = %', c2;
3242   c2 := 'special_name';
3243   for r in c2 loop
3244     raise notice '% from %', r.i, c2;
3245   end loop;
3246   raise notice 'after loop, c2 = %', c2;
3247   -- and try it with a generated name
3248   -- (which we can't show in the output because it's variable)
3249   c2 := null;
3250   for r in c2 loop
3251     raise notice '%', r.i;
3252   end loop;
3253   raise notice 'after loop, c2 = %', c2;
3254   return;
3255 end;
3256 $$ language plpgsql;
3257 select forc01();
3258 NOTICE:  5 from c
3259 NOTICE:  6 from c
3260 NOTICE:  7 from c
3261 NOTICE:  9 from c
3262 NOTICE:  10 from c
3263 NOTICE:  41 from c2
3264 NOTICE:  42 from c2
3265 NOTICE:  43 from c2
3266 NOTICE:  after loop, c2 = c2
3267 NOTICE:  41 from special_name
3268 NOTICE:  42 from special_name
3269 NOTICE:  43 from special_name
3270 NOTICE:  after loop, c2 = special_name
3271 NOTICE:  41
3272 NOTICE:  42
3273 NOTICE:  43
3274 NOTICE:  after loop, c2 = <NULL>
3275  forc01
3276 --------
3277
3278 (1 row)
3279
3280 -- try updating the cursor's current row
3281 create temp table forc_test as
3282   select n as i, n as j from generate_series(1,10) n;
3283 create or replace function forc01() returns void as $$
3284 declare
3285   c cursor for select * from forc_test;
3286 begin
3287   for r in c loop
3288     raise notice '%, %', r.i, r.j;
3289     update forc_test set i = i * 100, j = r.j * 2 where current of c;
3290   end loop;
3291 end;
3292 $$ language plpgsql;
3293 select forc01();
3294 NOTICE:  1, 1
3295 NOTICE:  2, 2
3296 NOTICE:  3, 3
3297 NOTICE:  4, 4
3298 NOTICE:  5, 5
3299 NOTICE:  6, 6
3300 NOTICE:  7, 7
3301 NOTICE:  8, 8
3302 NOTICE:  9, 9
3303 NOTICE:  10, 10
3304  forc01
3305 --------
3306
3307 (1 row)
3308
3309 select * from forc_test;
3310   i   | j
3311 ------+----
3312   100 |  2
3313   200 |  4
3314   300 |  6
3315   400 |  8
3316   500 | 10
3317   600 | 12
3318   700 | 14
3319   800 | 16
3320   900 | 18
3321  1000 | 20
3322 (10 rows)
3323
3324 -- same, with a cursor whose portal name doesn't match variable name
3325 create or replace function forc01() returns void as $$
3326 declare
3327   c refcursor := 'fooled_ya';
3328   r record;
3329 begin
3330   open c for select * from forc_test;
3331   loop
3332     fetch c into r;
3333     exit when not found;
3334     raise notice '%, %', r.i, r.j;
3335     update forc_test set i = i * 100, j = r.j * 2 where current of c;
3336   end loop;
3337 end;
3338 $$ language plpgsql;
3339 select forc01();
3340 NOTICE:  100, 2
3341 NOTICE:  200, 4
3342 NOTICE:  300, 6
3343 NOTICE:  400, 8
3344 NOTICE:  500, 10
3345 NOTICE:  600, 12
3346 NOTICE:  700, 14
3347 NOTICE:  800, 16
3348 NOTICE:  900, 18
3349 NOTICE:  1000, 20
3350  forc01
3351 --------
3352
3353 (1 row)
3354
3355 select * from forc_test;
3356    i    | j
3357 --------+----
3358   10000 |  4
3359   20000 |  8
3360   30000 | 12
3361   40000 | 16
3362   50000 | 20
3363   60000 | 24
3364   70000 | 28
3365   80000 | 32
3366   90000 | 36
3367  100000 | 40
3368 (10 rows)
3369
3370 drop function forc01();
3371 -- fail because cursor has no query bound to it
3372 create or replace function forc_bad() returns void as $$
3373 declare
3374   c refcursor;
3375 begin
3376   for r in c loop
3377     raise notice '%', r.i;
3378   end loop;
3379 end;
3380 $$ language plpgsql;
3381 ERROR:  cursor FOR loop must use a bound cursor variable
3382 LINE 5:   for r in c loop
3383                    ^
3384 -- test RETURN QUERY EXECUTE
3385 create or replace function return_dquery()
3386 returns setof int as $$
3387 begin
3388   return query execute 'select * from (values(10),(20)) f';
3389   return query execute 'select * from (values($1),($2)) f' using 40,50;
3390 end;
3391 $$ language plpgsql;
3392 select * from return_dquery();
3393  return_dquery
3394 ---------------
3395             10
3396             20
3397             40
3398             50
3399 (4 rows)
3400
3401 drop function return_dquery();
3402 -- test RETURN QUERY with dropped columns
3403 create table tabwithcols(a int, b int, c int, d int);
3404 insert into tabwithcols values(10,20,30,40),(50,60,70,80);
3405 create or replace function returnqueryf()
3406 returns setof tabwithcols as $$
3407 begin
3408   return query select * from tabwithcols;
3409   return query execute 'select * from tabwithcols';
3410 end;
3411 $$ language plpgsql;
3412 select * from returnqueryf();
3413  a  | b  | c  | d
3414 ----+----+----+----
3415  10 | 20 | 30 | 40
3416  50 | 60 | 70 | 80
3417  10 | 20 | 30 | 40
3418  50 | 60 | 70 | 80
3419 (4 rows)
3420
3421 alter table tabwithcols drop column b;
3422 select * from returnqueryf();
3423  a  | c  | d
3424 ----+----+----
3425  10 | 30 | 40
3426  50 | 70 | 80
3427  10 | 30 | 40
3428  50 | 70 | 80
3429 (4 rows)
3430
3431 alter table tabwithcols drop column d;
3432 select * from returnqueryf();
3433  a  | c
3434 ----+----
3435  10 | 30
3436  50 | 70
3437  10 | 30
3438  50 | 70
3439 (4 rows)
3440
3441 alter table tabwithcols add column d int;
3442 select * from returnqueryf();
3443  a  | c  | d
3444 ----+----+---
3445  10 | 30 |
3446  50 | 70 |
3447  10 | 30 |
3448  50 | 70 |
3449 (4 rows)
3450
3451 drop function returnqueryf();
3452 drop table tabwithcols;
3453 -- Tests for 8.4's new RAISE features
3454 create or replace function raise_test() returns void as $$
3455 begin
3456   raise notice '% % %', 1, 2, 3
3457      using errcode = '55001', detail = 'some detail info', hint = 'some hint';
3458   raise '% % %', 1, 2, 3
3459      using errcode = 'division_by_zero', detail = 'some detail info';
3460 end;
3461 $$ language plpgsql;
3462 select raise_test();
3463 NOTICE:  1 2 3
3464 DETAIL:  some detail info
3465 HINT:  some hint
3466 ERROR:  1 2 3
3467 DETAIL:  some detail info
3468 -- Since we can't actually see the thrown SQLSTATE in default psql output,
3469 -- test it like this; this also tests re-RAISE
3470 create or replace function raise_test() returns void as $$
3471 begin
3472   raise 'check me'
3473      using errcode = 'division_by_zero', detail = 'some detail info';
3474   exception
3475     when others then
3476       raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3477       raise;
3478 end;
3479 $$ language plpgsql;
3480 select raise_test();
3481 NOTICE:  SQLSTATE: 22012 SQLERRM: check me
3482 ERROR:  check me
3483 DETAIL:  some detail info
3484 create or replace function raise_test() returns void as $$
3485 begin
3486   raise 'check me'
3487      using errcode = '1234F', detail = 'some detail info';
3488   exception
3489     when others then
3490       raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3491       raise;
3492 end;
3493 $$ language plpgsql;
3494 select raise_test();
3495 NOTICE:  SQLSTATE: 1234F SQLERRM: check me
3496 ERROR:  check me
3497 DETAIL:  some detail info
3498 -- SQLSTATE specification in WHEN
3499 create or replace function raise_test() returns void as $$
3500 begin
3501   raise 'check me'
3502      using errcode = '1234F', detail = 'some detail info';
3503   exception
3504     when sqlstate '1234F' then
3505       raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3506       raise;
3507 end;
3508 $$ language plpgsql;
3509 select raise_test();
3510 NOTICE:  SQLSTATE: 1234F SQLERRM: check me
3511 ERROR:  check me
3512 DETAIL:  some detail info
3513 create or replace function raise_test() returns void as $$
3514 begin
3515   raise division_by_zero using detail = 'some detail info';
3516   exception
3517     when others then
3518       raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3519       raise;
3520 end;
3521 $$ language plpgsql;
3522 select raise_test();
3523 NOTICE:  SQLSTATE: 22012 SQLERRM: division_by_zero
3524 ERROR:  division_by_zero
3525 DETAIL:  some detail info
3526 create or replace function raise_test() returns void as $$
3527 begin
3528   raise division_by_zero;
3529 end;
3530 $$ language plpgsql;
3531 select raise_test();
3532 ERROR:  division_by_zero
3533 create or replace function raise_test() returns void as $$
3534 begin
3535   raise sqlstate '1234F';
3536 end;
3537 $$ language plpgsql;
3538 select raise_test();
3539 ERROR:  1234F
3540 create or replace function raise_test() returns void as $$
3541 begin
3542   raise division_by_zero using message = 'custom' || ' message';
3543 end;
3544 $$ language plpgsql;
3545 select raise_test();
3546 ERROR:  custom message
3547 create or replace function raise_test() returns void as $$
3548 begin
3549   raise using message = 'custom' || ' message', errcode = '22012';
3550 end;
3551 $$ language plpgsql;
3552 select raise_test();
3553 ERROR:  custom message
3554 -- conflict on message
3555 create or replace function raise_test() returns void as $$
3556 begin
3557   raise notice 'some message' using message = 'custom' || ' message', errcode = '22012';
3558 end;
3559 $$ language plpgsql;
3560 select raise_test();
3561 ERROR:  RAISE option already specified: MESSAGE
3562 CONTEXT:  PL/pgSQL function "raise_test" line 2 at RAISE
3563 -- conflict on errcode
3564 create or replace function raise_test() returns void as $$
3565 begin
3566   raise division_by_zero using message = 'custom' || ' message', errcode = '22012';
3567 end;
3568 $$ language plpgsql;
3569 select raise_test();
3570 ERROR:  RAISE option already specified: ERRCODE
3571 CONTEXT:  PL/pgSQL function "raise_test" line 2 at RAISE
3572 -- nothing to re-RAISE
3573 create or replace function raise_test() returns void as $$
3574 begin
3575   raise;
3576 end;
3577 $$ language plpgsql;
3578 select raise_test();
3579 ERROR:  RAISE without parameters cannot be used outside an exception handler
3580 CONTEXT:  PL/pgSQL function "raise_test"
3581 -- check cases where implicit SQLSTATE variable could be confused with
3582 -- SQLSTATE as a keyword, cf bug #5524
3583 create or replace function raise_test() returns void as $$
3584 begin
3585   perform 1/0;
3586 exception
3587   when sqlstate '22012' then
3588     raise notice using message = sqlstate;
3589     raise sqlstate '22012' using message = 'substitute message';
3590 end;
3591 $$ language plpgsql;
3592 select raise_test();
3593 NOTICE:  22012
3594 ERROR:  substitute message
3595 drop function raise_test();
3596 -- test CASE statement
3597 create or replace function case_test(bigint) returns text as $$
3598 declare a int = 10;
3599         b int = 1;
3600 begin
3601   case $1
3602     when 1 then
3603       return 'one';
3604     when 2 then
3605       return 'two';
3606     when 3,4,3+5 then
3607       return 'three, four or eight';
3608     when a then
3609       return 'ten';
3610     when a+b, a+b+1 then
3611       return 'eleven, twelve';
3612   end case;
3613 end;
3614 $$ language plpgsql immutable;
3615 select case_test(1);
3616  case_test
3617 -----------
3618  one
3619 (1 row)
3620
3621 select case_test(2);
3622  case_test
3623 -----------
3624  two
3625 (1 row)
3626
3627 select case_test(3);
3628       case_test
3629 ----------------------
3630  three, four or eight
3631 (1 row)
3632
3633 select case_test(4);
3634       case_test
3635 ----------------------
3636  three, four or eight
3637 (1 row)
3638
3639 select case_test(5); -- fails
3640 ERROR:  case not found
3641 HINT:  CASE statement is missing ELSE part.
3642 CONTEXT:  PL/pgSQL function "case_test" line 4 at CASE
3643 select case_test(8);
3644       case_test
3645 ----------------------
3646  three, four or eight
3647 (1 row)
3648
3649 select case_test(10);
3650  case_test
3651 -----------
3652  ten
3653 (1 row)
3654
3655 select case_test(11);
3656    case_test
3657 ----------------
3658  eleven, twelve
3659 (1 row)
3660
3661 select case_test(12);
3662    case_test
3663 ----------------
3664  eleven, twelve
3665 (1 row)
3666
3667 select case_test(13); -- fails
3668 ERROR:  case not found
3669 HINT:  CASE statement is missing ELSE part.
3670 CONTEXT:  PL/pgSQL function "case_test" line 4 at CASE
3671 create or replace function catch() returns void as $$
3672 begin
3673   raise notice '%', case_test(6);
3674 exception
3675   when case_not_found then
3676     raise notice 'caught case_not_found % %', SQLSTATE, SQLERRM;
3677 end
3678 $$ language plpgsql;
3679 select catch();
3680 NOTICE:  caught case_not_found 20000 case not found
3681  catch
3682 -------
3683
3684 (1 row)
3685
3686 -- test the searched variant too, as well as ELSE
3687 create or replace function case_test(bigint) returns text as $$
3688 declare a int = 10;
3689 begin
3690   case
3691     when $1 = 1 then
3692       return 'one';
3693     when $1 = a + 2 then
3694       return 'twelve';
3695     else
3696       return 'other';
3697   end case;
3698 end;
3699 $$ language plpgsql immutable;
3700 select case_test(1);
3701  case_test
3702 -----------
3703  one
3704 (1 row)
3705
3706 select case_test(2);
3707  case_test
3708 -----------
3709  other
3710 (1 row)
3711
3712 select case_test(12);
3713  case_test
3714 -----------
3715  twelve
3716 (1 row)
3717
3718 select case_test(13);
3719  case_test
3720 -----------
3721  other
3722 (1 row)
3723
3724 drop function catch();
3725 drop function case_test(bigint);
3726 -- test variadic functions
3727 create or replace function vari(variadic int[])
3728 returns void as $$
3729 begin
3730   for i in array_lower($1,1)..array_upper($1,1) loop
3731     raise notice '%', $1[i];
3732   end loop; end;
3733 $$ language plpgsql;
3734 select vari(1,2,3,4,5);
3735 NOTICE:  1
3736 NOTICE:  2
3737 NOTICE:  3
3738 NOTICE:  4
3739 NOTICE:  5
3740  vari
3741 ------
3742
3743 (1 row)
3744
3745 select vari(3,4,5);
3746 NOTICE:  3
3747 NOTICE:  4
3748 NOTICE:  5
3749  vari
3750 ------
3751
3752 (1 row)
3753
3754 select vari(variadic array[5,6,7]);
3755 NOTICE:  5
3756 NOTICE:  6
3757 NOTICE:  7
3758  vari
3759 ------
3760
3761 (1 row)
3762
3763 drop function vari(int[]);
3764 -- coercion test
3765 create or replace function pleast(variadic numeric[])
3766 returns numeric as $$
3767 declare aux numeric = $1[array_lower($1,1)];
3768 begin
3769   for i in array_lower($1,1)+1..array_upper($1,1) loop
3770     if $1[i] < aux then aux := $1[i]; end if;
3771   end loop;
3772   return aux;
3773 end;
3774 $$ language plpgsql immutable strict;
3775 select pleast(10,1,2,3,-16);
3776  pleast
3777 --------
3778     -16
3779 (1 row)
3780
3781 select pleast(10.2,2.2,-1.1);
3782  pleast
3783 --------
3784    -1.1
3785 (1 row)
3786
3787 select pleast(10.2,10, -20);
3788  pleast
3789 --------
3790     -20
3791 (1 row)
3792
3793 select pleast(10,20, -1.0);
3794  pleast
3795 --------
3796    -1.0
3797 (1 row)
3798
3799 -- in case of conflict, non-variadic version is preferred
3800 create or replace function pleast(numeric)
3801 returns numeric as $$
3802 begin
3803   raise notice 'non-variadic function called';
3804   return $1;
3805 end;
3806 $$ language plpgsql immutable strict;
3807 select pleast(10);
3808 NOTICE:  non-variadic function called
3809  pleast
3810 --------
3811      10
3812 (1 row)
3813
3814 drop function pleast(numeric[]);
3815 drop function pleast(numeric);
3816 -- test table functions
3817 create function tftest(int) returns table(a int, b int) as $$
3818 begin
3819   return query select $1, $1+i from generate_series(1,5) g(i);
3820 end;
3821 $$ language plpgsql immutable strict;
3822 select * from tftest(10);
3823  a  | b
3824 ----+----
3825  10 | 11
3826  10 | 12
3827  10 | 13
3828  10 | 14
3829  10 | 15
3830 (5 rows)
3831
3832 create or replace function tftest(a1 int) returns table(a int, b int) as $$
3833 begin
3834   a := a1; b := a1 + 1;
3835   return next;
3836   a := a1 * 10; b := a1 * 10 + 1;
3837   return next;
3838 end;
3839 $$ language plpgsql immutable strict;
3840 select * from tftest(10);
3841   a  |  b
3842 -----+-----
3843   10 |  11
3844  100 | 101
3845 (2 rows)
3846
3847 drop function tftest(int);
3848 create or replace function rttest()
3849 returns setof int as $$
3850 declare rc int;
3851 begin
3852   return query values(10),(20);
3853   get diagnostics rc = row_count;
3854   raise notice '% %', found, rc;
3855   return query select * from (values(10),(20)) f(a) where false;
3856   get diagnostics rc = row_count;
3857   raise notice '% %', found, rc;
3858   return query execute 'values(10),(20)';
3859   get diagnostics rc = row_count;
3860   raise notice '% %', found, rc;
3861   return query execute 'select * from (values(10),(20)) f(a) where false';
3862   get diagnostics rc = row_count;
3863   raise notice '% %', found, rc;
3864 end;
3865 $$ language plpgsql;
3866 select * from rttest();
3867 NOTICE:  t 2
3868 NOTICE:  f 0
3869 NOTICE:  t 2
3870 NOTICE:  f 0
3871  rttest
3872 --------
3873      10
3874      20
3875      10
3876      20
3877 (4 rows)
3878
3879 drop function rttest();
3880 -- Test for proper cleanup at subtransaction exit.  This example
3881 -- exposed a bug in PG 8.2.
3882 CREATE FUNCTION leaker_1(fail BOOL) RETURNS INTEGER AS $$
3883 DECLARE
3884   v_var INTEGER;
3885 BEGIN
3886   BEGIN
3887     v_var := (leaker_2(fail)).error_code;
3888   EXCEPTION
3889     WHEN others THEN RETURN 0;
3890   END;
3891   RETURN 1;
3892 END;
3893 $$ LANGUAGE plpgsql;
3894 CREATE FUNCTION leaker_2(fail BOOL, OUT error_code INTEGER, OUT new_id INTEGER)
3895   RETURNS RECORD AS $$
3896 BEGIN
3897   IF fail THEN
3898     RAISE EXCEPTION 'fail ...';
3899   END IF;
3900   error_code := 1;
3901   new_id := 1;
3902   RETURN;
3903 END;
3904 $$ LANGUAGE plpgsql;
3905 SELECT * FROM leaker_1(false);
3906  leaker_1
3907 ----------
3908         1
3909 (1 row)
3910
3911 SELECT * FROM leaker_1(true);
3912  leaker_1
3913 ----------
3914         0
3915 (1 row)
3916
3917 DROP FUNCTION leaker_1(bool);
3918 DROP FUNCTION leaker_2(bool);
3919 -- Test for appropriate cleanup of non-simple expression evaluations
3920 -- (bug in all versions prior to August 2010)
3921 CREATE FUNCTION nonsimple_expr_test() RETURNS text[] AS $$
3922 DECLARE
3923   arr text[];
3924   lr text;
3925   i integer;
3926 BEGIN
3927   arr := array[array['foo','bar'], array['baz', 'quux']];
3928   lr := 'fool';
3929   i := 1;
3930   -- use sub-SELECTs to make expressions non-simple
3931   arr[(SELECT i)][(SELECT i+1)] := (SELECT lr);
3932   RETURN arr;
3933 END;
3934 $$ LANGUAGE plpgsql;
3935 SELECT nonsimple_expr_test();
3936    nonsimple_expr_test
3937 -------------------------
3938  {{foo,fool},{baz,quux}}
3939 (1 row)
3940
3941 DROP FUNCTION nonsimple_expr_test();
3942 CREATE FUNCTION nonsimple_expr_test() RETURNS integer AS $$
3943 declare
3944    i integer NOT NULL := 0;
3945 begin
3946   begin
3947     i := (SELECT NULL::integer);  -- should throw error
3948   exception
3949     WHEN OTHERS THEN
3950       i := (SELECT 1::integer);
3951   end;
3952   return i;
3953 end;
3954 $$ LANGUAGE plpgsql;
3955 SELECT nonsimple_expr_test();
3956  nonsimple_expr_test
3957 ---------------------
3958                    1
3959 (1 row)
3960
3961 DROP FUNCTION nonsimple_expr_test();
3962 --
3963 -- Test cases involving recursion and error recovery in simple expressions
3964 -- (bugs in all versions before October 2010).  The problems are most
3965 -- easily exposed by mutual recursion between plpgsql and sql functions.
3966 --
3967 create function recurse(float8) returns float8 as
3968 $$
3969 begin
3970   if ($1 < 10) then
3971     return sql_recurse($1 + 1);
3972   else
3973     return $1;
3974   end if;
3975 end;
3976 $$ language plpgsql;
3977 -- "limit" is to prevent this from being inlined
3978 create function sql_recurse(float8) returns float8 as
3979 $$ select recurse($1) limit 1; $$ language sql;
3980 select recurse(0);
3981  recurse
3982 ---------
3983       10
3984 (1 row)
3985
3986 create function error1(text) returns text language sql as
3987 $$ SELECT relname::text FROM pg_class c WHERE c.oid = $1::regclass $$;
3988 create function error2(p_name_table text) returns text language plpgsql as $$
3989 begin
3990   return error1(p_name_table);
3991 end$$;
3992 BEGIN;
3993 create table public.stuffs (stuff text);
3994 SAVEPOINT a;
3995 select error2('nonexistent.stuffs');
3996 ERROR:  schema "nonexistent" does not exist
3997 CONTEXT:  SQL function "error1" statement 1
3998 PL/pgSQL function "error2" line 2 at RETURN
3999 ROLLBACK TO a;
4000 select error2('public.stuffs');
4001  error2
4002 --------
4003  stuffs
4004 (1 row)
4005
4006 rollback;
4007 drop function error2(p_name_table text);
4008 drop function error1(text);
4009 -- Test handling of string literals.
4010 set standard_conforming_strings = off;
4011 create or replace function strtest() returns text as $$
4012 begin
4013   raise notice 'foo\\bar\041baz';
4014   return 'foo\\bar\041baz';
4015 end
4016 $$ language plpgsql;
4017 WARNING:  nonstandard use of \\ in a string literal
4018 LINE 3:   raise notice 'foo\\bar\041baz';
4019                        ^
4020 HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
4021 WARNING:  nonstandard use of \\ in a string literal
4022 LINE 4:   return 'foo\\bar\041baz';
4023                  ^
4024 HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
4025 WARNING:  nonstandard use of \\ in a string literal
4026 LINE 4:   return 'foo\\bar\041baz';
4027                  ^
4028 HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
4029 select strtest();
4030 NOTICE:  foo\bar!baz
4031 WARNING:  nonstandard use of \\ in a string literal
4032 LINE 1: SELECT 'foo\\bar\041baz'
4033                ^
4034 HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
4035 QUERY:  SELECT 'foo\\bar\041baz'
4036 CONTEXT:  PL/pgSQL function "strtest" line 3 at RETURN
4037    strtest
4038 -------------
4039  foo\bar!baz
4040 (1 row)
4041
4042 create or replace function strtest() returns text as $$
4043 begin
4044   raise notice E'foo\\bar\041baz';
4045   return E'foo\\bar\041baz';
4046 end
4047 $$ language plpgsql;
4048 select strtest();
4049 NOTICE:  foo\bar!baz
4050    strtest
4051 -------------
4052  foo\bar!baz
4053 (1 row)
4054
4055 set standard_conforming_strings = on;
4056 create or replace function strtest() returns text as $$
4057 begin
4058   raise notice 'foo\\bar\041baz\';
4059   return 'foo\\bar\041baz\';
4060 end
4061 $$ language plpgsql;
4062 select strtest();
4063 NOTICE:  foo\\bar\041baz\
4064      strtest
4065 ------------------
4066  foo\\bar\041baz\
4067 (1 row)
4068
4069 create or replace function strtest() returns text as $$
4070 begin
4071   raise notice E'foo\\bar\041baz';
4072   return E'foo\\bar\041baz';
4073 end
4074 $$ language plpgsql;
4075 select strtest();
4076 NOTICE:  foo\bar!baz
4077    strtest
4078 -------------
4079  foo\bar!baz
4080 (1 row)
4081
4082 drop function strtest();
4083 -- Test anonymous code blocks.
4084 DO $$
4085 DECLARE r record;
4086 BEGIN
4087     FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno
4088     LOOP
4089         RAISE NOTICE '%, %', r.roomno, r.comment;
4090     END LOOP;
4091 END$$;
4092 NOTICE:  001, Entrance
4093 NOTICE:  002, Office
4094 NOTICE:  003, Office
4095 NOTICE:  004, Technical
4096 NOTICE:  101, Office
4097 NOTICE:  102, Conference
4098 NOTICE:  103, Restroom
4099 NOTICE:  104, Technical
4100 NOTICE:  105, Office
4101 NOTICE:  106, Office
4102 -- these are to check syntax error reporting
4103 DO LANGUAGE plpgsql $$begin return 1; end$$;
4104 ERROR:  RETURN cannot have a parameter in function returning void
4105 LINE 1: DO LANGUAGE plpgsql $$begin return 1; end$$;
4106                                            ^
4107 DO $$
4108 DECLARE r record;
4109 BEGIN
4110     FOR r IN SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno
4111     LOOP
4112         RAISE NOTICE '%, %', r.roomno, r.comment;
4113     END LOOP;
4114 END$$;
4115 ERROR:  column "foo" does not exist
4116 LINE 1: SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomn...
4117                                         ^
4118 QUERY:  SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno
4119 CONTEXT:  PL/pgSQL function "inline_code_block" line 3 at FOR over SELECT rows
4120 -- Check variable scoping -- a var is not available in its own or prior
4121 -- default expressions.
4122 create function scope_test() returns int as $$
4123 declare x int := 42;
4124 begin
4125   declare y int := x + 1;
4126           x int := x + 2;
4127   begin
4128     return x * 100 + y;
4129   end;
4130 end;
4131 $$ language plpgsql;
4132 select scope_test();
4133  scope_test
4134 ------------
4135        4443
4136 (1 row)
4137
4138 drop function scope_test();
4139 -- Check handling of conflicts between plpgsql vars and table columns.
4140 set plpgsql.variable_conflict = error;
4141 create function conflict_test() returns setof int8_tbl as $$
4142 declare r record;
4143   q1 bigint := 42;
4144 begin
4145   for r in select q1,q2 from int8_tbl loop
4146     return next r;
4147   end loop;
4148 end;
4149 $$ language plpgsql;
4150 select * from conflict_test();
4151 ERROR:  column reference "q1" is ambiguous
4152 LINE 1: select q1,q2 from int8_tbl
4153                ^
4154 DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
4155 QUERY:  select q1,q2 from int8_tbl
4156 CONTEXT:  PL/pgSQL function "conflict_test" line 4 at FOR over SELECT rows
4157 create or replace function conflict_test() returns setof int8_tbl as $$
4158 #variable_conflict use_variable
4159 declare r record;
4160   q1 bigint := 42;
4161 begin
4162   for r in select q1,q2 from int8_tbl loop
4163     return next r;
4164   end loop;
4165 end;
4166 $$ language plpgsql;
4167 select * from conflict_test();
4168  q1 |        q2
4169 ----+-------------------
4170  42 |               456
4171  42 |  4567890123456789
4172  42 |               123
4173  42 |  4567890123456789
4174  42 | -4567890123456789
4175 (5 rows)
4176
4177 create or replace function conflict_test() returns setof int8_tbl as $$
4178 #variable_conflict use_column
4179 declare r record;
4180   q1 bigint := 42;
4181 begin
4182   for r in select q1,q2 from int8_tbl loop
4183     return next r;
4184   end loop;
4185 end;
4186 $$ language plpgsql;
4187 select * from conflict_test();
4188         q1        |        q2
4189 ------------------+-------------------
4190               123 |               456
4191               123 |  4567890123456789
4192  4567890123456789 |               123
4193  4567890123456789 |  4567890123456789
4194  4567890123456789 | -4567890123456789
4195 (5 rows)
4196
4197 drop function conflict_test();
4198 -- Check that an unreserved keyword can be used as a variable name
4199 create function unreserved_test() returns int as $$
4200 declare
4201   forward int := 21;
4202 begin
4203   forward := forward * 2;
4204   return forward;
4205 end
4206 $$ language plpgsql;
4207 select unreserved_test();
4208  unreserved_test
4209 -----------------
4210               42
4211 (1 row)
4212
4213 drop function unreserved_test();