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

   1 --
   2 -- PLPGSQL
   3 --
   4 -- Scenario:
   5 --
   6 --     A building with a modern TP cable installation where any
   7 --     of the wall connectors can be used to plug in phones,
   8 --     ethernet interfaces or local office hubs. The backside
   9 --     of the wall connectors is wired to one of several patch-
  10 --     fields in the building.
  11 --
  12 --     In the patchfields, there are hubs and all the slots
  13 --     representing the wall connectors. In addition there are
  14 --     slots that can represent a phone line from the central
  15 --     phone system.
  16 --
  17 --     Triggers ensure consistency of the patching information.
  18 --
  19 --     Functions are used to build up powerful views that let
  20 --     you look behind the wall when looking at a patchfield
  21 --     or into a room.
  22 --
  23 create table Room (
  24     roomno      char(8),
  25     comment     text
  26 );
  27 create unique index Room_rno on Room using btree (roomno bpchar_ops);
  28 create table WSlot (
  29     slotname    char(20),
  30     roomno      char(8),
  31     slotlink    char(20),
  32     backlink    char(20)
  33 );
  34 create unique index WSlot_name on WSlot using btree (slotname bpchar_ops);
  35 create table PField (
  36     name        text,
  37     comment     text
  38 );
  39 create unique index PField_name on PField using btree (name text_ops);
  40 create table PSlot (
  41     slotname    char(20),
  42     pfname      text,
  43     slotlink    char(20),
  44     backlink    char(20)
  45 );
  46 create unique index PSlot_name on PSlot using btree (slotname bpchar_ops);
  47 create table PLine (
  48     slotname    char(20),
  49     phonenumber char(20),
  50     comment     text,
  51     backlink    char(20)
  52 );
  53 create unique index PLine_name on PLine using btree (slotname bpchar_ops);
  54 create table Hub (
  55     name        char(14),
  56     comment     text,
  57     nslots      integer
  58 );
  59 create unique index Hub_name on Hub using btree (name bpchar_ops);
  60 create table HSlot (
  61     slotname    char(20),
  62     hubname     char(14),
  63     slotno      integer,
  64     slotlink    char(20)
  65 );
  66 create unique index HSlot_name on HSlot using btree (slotname bpchar_ops);
  67 create index HSlot_hubname on HSlot using btree (hubname bpchar_ops);
  68 create table System (
  69     name        text,
  70     comment     text
  71 );
  72 create unique index System_name on System using btree (name text_ops);
  73 create table IFace (
  74     slotname    char(20),
  75     sysname     text,
  76     ifname      text,
  77     slotlink    char(20)
  78 );
  79 create unique index IFace_name on IFace using btree (slotname bpchar_ops);
  80 create table PHone (
  81     slotname    char(20),
  82     comment     text,
  83     slotlink    char(20)
  84 );
  85 create unique index PHone_name on PHone using btree (slotname bpchar_ops);
  86 -- ************************************************************
  87 -- *
  88 -- * Trigger procedures and functions for the patchfield
  89 -- * test of PL/pgSQL
  90 -- *
  91 -- ************************************************************
  92 -- ************************************************************
  93 -- * AFTER UPDATE on Room
  94 -- *    - If room no changes let wall slots follow
  95 -- ************************************************************
  96 create function tg_room_au() returns trigger as '
  97 begin
  98     if new.roomno != old.roomno then
  99         update WSlot set roomno = new.roomno where roomno = old.roomno;
 100     end if;
 101     return new;
 102 end;
 103 ' language plpgsql;
 104 create trigger tg_room_au after update
 105     on Room for each row execute procedure tg_room_au();
 106 -- ************************************************************
 107 -- * AFTER DELETE on Room
 108 -- *    - delete wall slots in this room
 109 -- ************************************************************
 110 create function tg_room_ad() returns trigger as '
 111 begin
 112     delete from WSlot where roomno = old.roomno;
 113     return old;
 114 end;
 115 ' language plpgsql;
 116 create trigger tg_room_ad after delete
 117     on Room for each row execute procedure tg_room_ad();
 118 -- ************************************************************
 119 -- * BEFORE INSERT or UPDATE on WSlot
 120 -- *    - Check that room exists
 121 -- ************************************************************
 122 create function tg_wslot_biu() returns trigger as $$
 123 begin
 124     if count(*) = 0 from Room where roomno = new.roomno then
 125         raise exception 'Room % does not exist', new.roomno;
 126     end if;
 127     return new;
 128 end;
 129 $$ language plpgsql;
 130 create trigger tg_wslot_biu before insert or update
 131     on WSlot for each row execute procedure tg_wslot_biu();
 132 -- ************************************************************
 133 -- * AFTER UPDATE on PField
 134 -- *    - Let PSlots of this field follow
 135 -- ************************************************************
 136 create function tg_pfield_au() returns trigger as '
 137 begin
 138     if new.name != old.name then
 139         update PSlot set pfname = new.name where pfname = old.name;
 140     end if;
 141     return new;
 142 end;
 143 ' language plpgsql;
 144 create trigger tg_pfield_au after update
 145     on PField for each row execute procedure tg_pfield_au();
 146 -- ************************************************************
 147 -- * AFTER DELETE on PField
 148 -- *    - Remove all slots of this patchfield
 149 -- ************************************************************
 150 create function tg_pfield_ad() returns trigger as '
 151 begin
 152     delete from PSlot where pfname = old.name;
 153     return old;
 154 end;
 155 ' language plpgsql;
 156 create trigger tg_pfield_ad after delete
 157     on PField for each row execute procedure tg_pfield_ad();
 158 -- ************************************************************
 159 -- * BEFORE INSERT or UPDATE on PSlot
 160 -- *    - Ensure that our patchfield does exist
 161 -- ************************************************************
 162 create function tg_pslot_biu() returns trigger as $proc$
 163 declare
 164     pfrec       record;
 165     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 17 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 17 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 17 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 17 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 8 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 8 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 8 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 cursors with named parameter arguments
2296 --
2297 create function namedparmcursor_test1(int, int) returns boolean as $$
2298 declare
2299     c1 cursor (param1 int, param12 int) for select * from rc_test where a > param1 and b > param12;
2300     nonsense record;
2301 begin
2302     open c1(param12 := $2, param1 := $1);
2303     fetch c1 into nonsense;
2304     close c1;
2305     if found then
2306         return true;
2307     else
2308         return false;
2309     end if;
2310 end
2311 $$ language plpgsql;
2312 select namedparmcursor_test1(20000, 20000) as "Should be false",
2313        namedparmcursor_test1(20, 20) as "Should be true";
2314  Should be false | Should be true
2315 -----------------+----------------
2316  f               | t
2317 (1 row)
2318
2319 -- mixing named and positional argument notations
2320 create function namedparmcursor_test2(int, int) returns boolean as $$
2321 declare
2322     c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
2323     nonsense record;
2324 begin
2325     open c1(param1 := $1, $2);
2326     fetch c1 into nonsense;
2327     close c1;
2328     if found then
2329         return true;
2330     else
2331         return false;
2332     end if;
2333 end
2334 $$ language plpgsql;
2335 select namedparmcursor_test2(20, 20);
2336  namedparmcursor_test2
2337 -----------------------
2338  t
2339 (1 row)
2340
2341 -- mixing named and positional: param2 is given twice, once in named notation
2342 -- and second time in positional notation. Should throw an error at parse time
2343 create function namedparmcursor_test3() returns void as $$
2344 declare
2345     c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
2346 begin
2347     open c1(param2 := 20, 21);
2348 end
2349 $$ language plpgsql;
2350 ERROR:  duplicate value for cursor "c1" parameter "param2"
2351 LINE 5:     open c1(param2 := 20, 21);
2352                                   ^
2353 -- mixing named and positional: same as previous test, but param1 is duplicated
2354 create function namedparmcursor_test4() returns void as $$
2355 declare
2356     c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
2357 begin
2358     open c1(20, param1 := 21);
2359 end
2360 $$ language plpgsql;
2361 ERROR:  duplicate value for cursor "c1" parameter "param1"
2362 LINE 5:     open c1(20, param1 := 21);
2363                         ^
2364 -- duplicate named parameter, should throw an error at parse time
2365 create function namedparmcursor_test5() returns void as $$
2366 declare
2367   c1 cursor (p1 int, p2 int) for
2368     select * from tenk1 where thousand = p1 and tenthous = p2;
2369 begin
2370   open c1 (p2 := 77, p2 := 42);
2371 end
2372 $$ language plpgsql;
2373 ERROR:  duplicate value for cursor "c1" parameter "p2"
2374 LINE 6:   open c1 (p2 := 77, p2 := 42);
2375                              ^
2376 -- not enough parameters, should throw an error at parse time
2377 create function namedparmcursor_test6() returns void as $$
2378 declare
2379   c1 cursor (p1 int, p2 int) for
2380     select * from tenk1 where thousand = p1 and tenthous = p2;
2381 begin
2382   open c1 (p2 := 77);
2383 end
2384 $$ language plpgsql;
2385 ERROR:  not enough arguments for cursor "c1"
2386 LINE 6:   open c1 (p2 := 77);
2387                            ^
2388 -- division by zero runtime error, the context given in the error message
2389 -- should be sensible
2390 create function namedparmcursor_test7() returns void as $$
2391 declare
2392   c1 cursor (p1 int, p2 int) for
2393     select * from tenk1 where thousand = p1 and tenthous = p2;
2394 begin
2395   open c1 (p2 := 77, p1 := 42/0);
2396 end $$ language plpgsql;
2397 select namedparmcursor_test7();
2398 ERROR:  division by zero
2399 CONTEXT:  SQL statement "SELECT 42/0 AS p1, 77 AS p2;"
2400 PL/pgSQL function namedparmcursor_test7() line 6 at OPEN
2401 -- check that line comments work correctly within the argument list (there
2402 -- is some special handling of this case in the code: the newline after the
2403 -- comment must be preserved when the argument-evaluating query is
2404 -- constructed, otherwise the comment effectively comments out the next
2405 -- argument, too)
2406 create function namedparmcursor_test8() returns int4 as $$
2407 declare
2408   c1 cursor (p1 int, p2 int) for
2409     select count(*) from tenk1 where thousand = p1 and tenthous = p2;
2410   n int4;
2411 begin
2412   open c1 (77 -- test
2413   , 42);
2414   fetch c1 into n;
2415   return n;
2416 end $$ language plpgsql;
2417 select namedparmcursor_test8();
2418  namedparmcursor_test8
2419 -----------------------
2420                      0
2421 (1 row)
2422
2423 -- cursor parameter name can match plpgsql variable or unreserved keyword
2424 create function namedparmcursor_test9(p1 int) returns int4 as $$
2425 declare
2426   c1 cursor (p1 int, p2 int, debug int) for
2427     select count(*) from tenk1 where thousand = p1 and tenthous = p2
2428       and four = debug;
2429   p2 int4 := 1006;
2430   n int4;
2431 begin
2432   open c1 (p1 := p1, p2 := p2, debug := 2);
2433   fetch c1 into n;
2434   return n;
2435 end $$ language plpgsql;
2436 select namedparmcursor_test9(6);
2437  namedparmcursor_test9
2438 -----------------------
2439                      1
2440 (1 row)
2441
2442 --
2443 -- tests for "raise" processing
2444 --
2445 create function raise_test1(int) returns int as $$
2446 begin
2447     raise notice 'This message has too many parameters!', $1;
2448     return $1;
2449 end;
2450 $$ language plpgsql;
2451 select raise_test1(5);
2452 ERROR:  too many parameters specified for RAISE
2453 CONTEXT:  PL/pgSQL function raise_test1(integer) line 3 at RAISE
2454 create function raise_test2(int) returns int as $$
2455 begin
2456     raise notice 'This message has too few parameters: %, %, %', $1, $1;
2457     return $1;
2458 end;
2459 $$ language plpgsql;
2460 select raise_test2(10);
2461 ERROR:  too few parameters specified for RAISE
2462 CONTEXT:  PL/pgSQL function raise_test2(integer) line 3 at RAISE
2463 -- Test re-RAISE inside a nested exception block.  This case is allowed
2464 -- by Oracle's PL/SQL but was handled differently by PG before 9.1.
2465 CREATE FUNCTION reraise_test() RETURNS void AS $$
2466 BEGIN
2467    BEGIN
2468        RAISE syntax_error;
2469    EXCEPTION
2470        WHEN syntax_error THEN
2471            BEGIN
2472                raise notice 'exception % thrown in inner block, reraising', sqlerrm;
2473                RAISE;
2474            EXCEPTION
2475                WHEN OTHERS THEN
2476                    raise notice 'RIGHT - exception % caught in inner block', sqlerrm;
2477            END;
2478    END;
2479 EXCEPTION
2480    WHEN OTHERS THEN
2481        raise notice 'WRONG - exception % caught in outer block', sqlerrm;
2482 END;
2483 $$ LANGUAGE plpgsql;
2484 SELECT reraise_test();
2485 NOTICE:  exception syntax_error thrown in inner block, reraising
2486 NOTICE:  RIGHT - exception syntax_error caught in inner block
2487  reraise_test
2488 --------------
2489
2490 (1 row)
2491
2492 --
2493 -- reject function definitions that contain malformed SQL queries at
2494 -- compile-time, where possible
2495 --
2496 create function bad_sql1() returns int as $$
2497 declare a int;
2498 begin
2499     a := 5;
2500     Johnny Yuma;
2501     a := 10;
2502     return a;
2503 end$$ language plpgsql;
2504 ERROR:  syntax error at or near "Johnny"
2505 LINE 5:     Johnny Yuma;
2506             ^
2507 create function bad_sql2() returns int as $$
2508 declare r record;
2509 begin
2510     for r in select I fought the law, the law won LOOP
2511         raise notice 'in loop';
2512     end loop;
2513     return 5;
2514 end;$$ language plpgsql;
2515 ERROR:  syntax error at or near "the"
2516 LINE 4:     for r in select I fought the law, the law won LOOP
2517                                      ^
2518 -- a RETURN expression is mandatory, except for void-returning
2519 -- functions, where it is not allowed
2520 create function missing_return_expr() returns int as $$
2521 begin
2522     return ;
2523 end;$$ language plpgsql;
2524 ERROR:  missing expression at or near ";"
2525 LINE 3:     return ;
2526                    ^
2527 create function void_return_expr() returns void as $$
2528 begin
2529     return 5;
2530 end;$$ language plpgsql;
2531 ERROR:  RETURN cannot have a parameter in function returning void
2532 LINE 3:     return 5;
2533                    ^
2534 -- VOID functions are allowed to omit RETURN
2535 create function void_return_expr() returns void as $$
2536 begin
2537     perform 2+2;
2538 end;$$ language plpgsql;
2539 select void_return_expr();
2540  void_return_expr
2541 ------------------
2542
2543 (1 row)
2544
2545 -- but ordinary functions are not
2546 create function missing_return_expr() returns int as $$
2547 begin
2548     perform 2+2;
2549 end;$$ language plpgsql;
2550 select missing_return_expr();
2551 ERROR:  control reached end of function without RETURN
2552 CONTEXT:  PL/pgSQL function missing_return_expr()
2553 drop function void_return_expr();
2554 drop function missing_return_expr();
2555 --
2556 -- EXECUTE ... INTO test
2557 --
2558 create table eifoo (i integer, y integer);
2559 create type eitype as (i integer, y integer);
2560 create or replace function execute_into_test(varchar) returns record as $$
2561 declare
2562     _r record;
2563     _rt eifoo%rowtype;
2564     _v eitype;
2565     i int;
2566     j int;
2567     k int;
2568 begin
2569     execute 'insert into '||$1||' values(10,15)';
2570     execute 'select (row).* from (select row(10,1)::eifoo) s' into _r;
2571     raise notice '% %', _r.i, _r.y;
2572     execute 'select * from '||$1||' limit 1' into _rt;
2573     raise notice '% %', _rt.i, _rt.y;
2574     execute 'select *, 20 from '||$1||' limit 1' into i, j, k;
2575     raise notice '% % %', i, j, k;
2576     execute 'select 1,2' into _v;
2577     return _v;
2578 end; $$ language plpgsql;
2579 select execute_into_test('eifoo');
2580 NOTICE:  10 1
2581 NOTICE:  10 15
2582 NOTICE:  10 15 20
2583  execute_into_test
2584 -------------------
2585  (1,2)
2586 (1 row)
2587
2588 drop table eifoo cascade;
2589 drop type eitype cascade;
2590 --
2591 -- SQLSTATE and SQLERRM test
2592 --
2593 create function excpt_test1() returns void as $$
2594 begin
2595     raise notice '% %', sqlstate, sqlerrm;
2596 end; $$ language plpgsql;
2597 -- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
2598 -- blocks
2599 select excpt_test1();
2600 ERROR:  column "sqlstate" does not exist
2601 LINE 1: SELECT sqlstate
2602                ^
2603 QUERY:  SELECT sqlstate
2604 CONTEXT:  PL/pgSQL function excpt_test1() line 3 at RAISE
2605 create function excpt_test2() returns void as $$
2606 begin
2607     begin
2608         begin
2609             raise notice '% %', sqlstate, sqlerrm;
2610         end;
2611     end;
2612 end; $$ language plpgsql;
2613 -- should fail
2614 select excpt_test2();
2615 ERROR:  column "sqlstate" does not exist
2616 LINE 1: SELECT sqlstate
2617                ^
2618 QUERY:  SELECT sqlstate
2619 CONTEXT:  PL/pgSQL function excpt_test2() line 5 at RAISE
2620 create function excpt_test3() returns void as $$
2621 begin
2622     begin
2623         raise exception 'user exception';
2624     exception when others then
2625             raise notice 'caught exception % %', sqlstate, sqlerrm;
2626             begin
2627                 raise notice '% %', sqlstate, sqlerrm;
2628                 perform 10/0;
2629         exception
2630             when substring_error then
2631                 -- this exception handler shouldn't be invoked
2632                 raise notice 'unexpected exception: % %', sqlstate, sqlerrm;
2633                 when division_by_zero then
2634                     raise notice 'caught exception % %', sqlstate, sqlerrm;
2635             end;
2636             raise notice '% %', sqlstate, sqlerrm;
2637     end;
2638 end; $$ language plpgsql;
2639 select excpt_test3();
2640 NOTICE:  caught exception P0001 user exception
2641 NOTICE:  P0001 user exception
2642 NOTICE:  caught exception 22012 division by zero
2643 NOTICE:  P0001 user exception
2644  excpt_test3
2645 -------------
2646
2647 (1 row)
2648
2649 drop function excpt_test1();
2650 drop function excpt_test2();
2651 drop function excpt_test3();
2652 -- parameters of raise stmt can be expressions
2653 create function raise_exprs() returns void as $$
2654 declare
2655     a integer[] = '{10,20,30}';
2656     c varchar = 'xyz';
2657     i integer;
2658 begin
2659     i := 2;
2660     raise notice '%; %; %; %; %; %', a, a[i], c, (select c || 'abc'), row(10,'aaa',NULL,30), NULL;
2661 end;$$ language plpgsql;
2662 select raise_exprs();
2663 NOTICE:  {10,20,30}; 20; xyz; xyzabc; (10,aaa,,30); <NULL>
2664  raise_exprs
2665 -------------
2666
2667 (1 row)
2668
2669 drop function raise_exprs();
2670 -- continue statement
2671 create table conttesttbl(idx serial, v integer);
2672 NOTICE:  CREATE TABLE will create implicit sequence "conttesttbl_idx_seq" for serial column "conttesttbl.idx"
2673 insert into conttesttbl(v) values(10);
2674 insert into conttesttbl(v) values(20);
2675 insert into conttesttbl(v) values(30);
2676 insert into conttesttbl(v) values(40);
2677 create function continue_test1() returns void as $$
2678 declare _i integer = 0; _r record;
2679 begin
2680   raise notice '---1---';
2681   loop
2682     _i := _i + 1;
2683     raise notice '%', _i;
2684     continue when _i < 10;
2685     exit;
2686   end loop;
2687
2688   raise notice '---2---';
2689   <<lbl>>
2690   loop
2691     _i := _i - 1;
2692     loop
2693       raise notice '%', _i;
2694       continue lbl when _i > 0;
2695       exit lbl;
2696     end loop;
2697   end loop;
2698
2699   raise notice '---3---';
2700   <<the_loop>>
2701   while _i < 10 loop
2702     _i := _i + 1;
2703     continue the_loop when _i % 2 = 0;
2704     raise notice '%', _i;
2705   end loop;
2706
2707   raise notice '---4---';
2708   for _i in 1..10 loop
2709     begin
2710       -- applies to outer loop, not the nested begin block
2711       continue when _i < 5;
2712       raise notice '%', _i;
2713     end;
2714   end loop;
2715
2716   raise notice '---5---';
2717   for _r in select * from conttesttbl loop
2718     continue when _r.v <= 20;
2719     raise notice '%', _r.v;
2720   end loop;
2721
2722   raise notice '---6---';
2723   for _r in execute 'select * from conttesttbl' loop
2724     continue when _r.v <= 20;
2725     raise notice '%', _r.v;
2726   end loop;
2727
2728   raise notice '---7---';
2729   for _i in 1..3 loop
2730     raise notice '%', _i;
2731     continue when _i = 3;
2732   end loop;
2733
2734   raise notice '---8---';
2735   _i := 1;
2736   while _i <= 3 loop
2737     raise notice '%', _i;
2738     _i := _i + 1;
2739     continue when _i = 3;
2740   end loop;
2741
2742   raise notice '---9---';
2743   for _r in select * from conttesttbl order by v limit 1 loop
2744     raise notice '%', _r.v;
2745     continue;
2746   end loop;
2747
2748   raise notice '---10---';
2749   for _r in execute 'select * from conttesttbl order by v limit 1' loop
2750     raise notice '%', _r.v;
2751     continue;
2752   end loop;
2753 end; $$ language plpgsql;
2754 select continue_test1();
2755 NOTICE:  ---1---
2756 NOTICE:  1
2757 NOTICE:  2
2758 NOTICE:  3
2759 NOTICE:  4
2760 NOTICE:  5
2761 NOTICE:  6
2762 NOTICE:  7
2763 NOTICE:  8
2764 NOTICE:  9
2765 NOTICE:  10
2766 NOTICE:  ---2---
2767 NOTICE:  9
2768 NOTICE:  8
2769 NOTICE:  7
2770 NOTICE:  6
2771 NOTICE:  5
2772 NOTICE:  4
2773 NOTICE:  3
2774 NOTICE:  2
2775 NOTICE:  1
2776 NOTICE:  0
2777 NOTICE:  ---3---
2778 NOTICE:  1
2779 NOTICE:  3
2780 NOTICE:  5
2781 NOTICE:  7
2782 NOTICE:  9
2783 NOTICE:  ---4---
2784 NOTICE:  5
2785 NOTICE:  6
2786 NOTICE:  7
2787 NOTICE:  8
2788 NOTICE:  9
2789 NOTICE:  10
2790 NOTICE:  ---5---
2791 NOTICE:  30
2792 NOTICE:  40
2793 NOTICE:  ---6---
2794 NOTICE:  30
2795 NOTICE:  40
2796 NOTICE:  ---7---
2797 NOTICE:  1
2798 NOTICE:  2
2799 NOTICE:  3
2800 NOTICE:  ---8---
2801 NOTICE:  1
2802 NOTICE:  2
2803 NOTICE:  3
2804 NOTICE:  ---9---
2805 NOTICE:  10
2806 NOTICE:  ---10---
2807 NOTICE:  10
2808  continue_test1
2809 ----------------
2810
2811 (1 row)
2812
2813 -- CONTINUE is only legal inside a loop
2814 create function continue_test2() returns void as $$
2815 begin
2816     begin
2817         continue;
2818     end;
2819     return;
2820 end;
2821 $$ language plpgsql;
2822 -- should fail
2823 select continue_test2();
2824 ERROR:  CONTINUE cannot be used outside a loop
2825 CONTEXT:  PL/pgSQL function continue_test2()
2826 -- CONTINUE can't reference the label of a named block
2827 create function continue_test3() returns void as $$
2828 begin
2829     <<begin_block1>>
2830     begin
2831         loop
2832             continue begin_block1;
2833         end loop;
2834     end;
2835 end;
2836 $$ language plpgsql;
2837 -- should fail
2838 select continue_test3();
2839 ERROR:  CONTINUE cannot be used outside a loop
2840 CONTEXT:  PL/pgSQL function continue_test3()
2841 drop function continue_test1();
2842 drop function continue_test2();
2843 drop function continue_test3();
2844 drop table conttesttbl;
2845 -- verbose end block and end loop
2846 create function end_label1() returns void as $$
2847 <<blbl>>
2848 begin
2849   <<flbl1>>
2850   for _i in 1 .. 10 loop
2851     exit flbl1;
2852   end loop flbl1;
2853   <<flbl2>>
2854   for _i in 1 .. 10 loop
2855     exit flbl2;
2856   end loop;
2857 end blbl;
2858 $$ language plpgsql;
2859 select end_label1();
2860  end_label1
2861 ------------
2862
2863 (1 row)
2864
2865 drop function end_label1();
2866 -- should fail: undefined end label
2867 create function end_label2() returns void as $$
2868 begin
2869   for _i in 1 .. 10 loop
2870     exit;
2871   end loop flbl1;
2872 end;
2873 $$ language plpgsql;
2874 ERROR:  label does not exist at or near "flbl1"
2875 LINE 5:   end loop flbl1;
2876                    ^
2877 -- should fail: end label does not match start label
2878 create function end_label3() returns void as $$
2879 <<outer_label>>
2880 begin
2881   <<inner_label>>
2882   for _i in 1 .. 10 loop
2883     exit;
2884   end loop outer_label;
2885 end;
2886 $$ language plpgsql;
2887 ERROR:  end label "outer_label" differs from block's label "inner_label"
2888 LINE 7:   end loop outer_label;
2889                    ^
2890 -- should fail: end label on a block without a start label
2891 create function end_label4() returns void as $$
2892 <<outer_label>>
2893 begin
2894   for _i in 1 .. 10 loop
2895     exit;
2896   end loop outer_label;
2897 end;
2898 $$ language plpgsql;
2899 ERROR:  end label "outer_label" specified for unlabelled block
2900 LINE 6:   end loop outer_label;
2901                    ^
2902 -- using list of scalars in fori and fore stmts
2903 create function for_vect() returns void as $proc$
2904 <<lbl>>declare a integer; b varchar; c varchar; r record;
2905 begin
2906   -- fori
2907   for i in 1 .. 3 loop
2908     raise notice '%', i;
2909   end loop;
2910   -- fore with record var
2911   for r in select gs as aa, 'BB' as bb, 'CC' as cc from generate_series(1,4) gs loop
2912     raise notice '% % %', r.aa, r.bb, r.cc;
2913   end loop;
2914   -- fore with single scalar
2915   for a in select gs from generate_series(1,4) gs loop
2916     raise notice '%', a;
2917   end loop;
2918   -- fore with multiple scalars
2919   for a,b,c in select gs, 'BB','CC' from generate_series(1,4) gs loop
2920     raise notice '% % %', a, b, c;
2921   end loop;
2922   -- using qualified names in fors, fore is enabled, disabled only for fori
2923   for lbl.a, lbl.b, lbl.c in execute $$select gs, 'bb','cc' from generate_series(1,4) gs$$ loop
2924     raise notice '% % %', a, b, c;
2925   end loop;
2926 end;
2927 $proc$ language plpgsql;
2928 select for_vect();
2929 NOTICE:  1
2930 NOTICE:  2
2931 NOTICE:  3
2932 NOTICE:  1 BB CC
2933 NOTICE:  2 BB CC
2934 NOTICE:  3 BB CC
2935 NOTICE:  4 BB CC
2936 NOTICE:  1
2937 NOTICE:  2
2938 NOTICE:  3
2939 NOTICE:  4
2940 NOTICE:  1 BB CC
2941 NOTICE:  2 BB CC
2942 NOTICE:  3 BB CC
2943 NOTICE:  4 BB CC
2944 NOTICE:  1 bb cc
2945 NOTICE:  2 bb cc
2946 NOTICE:  3 bb cc
2947 NOTICE:  4 bb cc
2948  for_vect
2949 ----------
2950
2951 (1 row)
2952
2953 -- regression test: verify that multiple uses of same plpgsql datum within
2954 -- a SQL command all get mapped to the same $n parameter.  The return value
2955 -- of the SELECT is not important, we only care that it doesn't fail with
2956 -- a complaint about an ungrouped column reference.
2957 create function multi_datum_use(p1 int) returns bool as $$
2958 declare
2959   x int;
2960   y int;
2961 begin
2962   select into x,y unique1/p1, unique1/$1 from tenk1 group by unique1/p1;
2963   return x = y;
2964 end$$ language plpgsql;
2965 select multi_datum_use(42);
2966  multi_datum_use
2967 -----------------
2968  t
2969 (1 row)
2970
2971 --
2972 -- Test STRICT limiter in both planned and EXECUTE invocations.
2973 -- Note that a data-modifying query is quasi strict (disallow multi rows)
2974 -- by default in the planned case, but not in EXECUTE.
2975 --
2976 create temp table foo (f1 int, f2 int);
2977 insert into foo values (1,2), (3,4);
2978 create or replace function footest() returns void as $$
2979 declare x record;
2980 begin
2981   -- should work
2982   insert into foo values(5,6) returning * into x;
2983   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2984 end$$ language plpgsql;
2985 select footest();
2986 NOTICE:  x.f1 = 5, x.f2 = 6
2987  footest
2988 ---------
2989
2990 (1 row)
2991
2992 create or replace function footest() returns void as $$
2993 declare x record;
2994 begin
2995   -- should fail due to implicit strict
2996   insert into foo values(7,8),(9,10) returning * into x;
2997   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2998 end$$ language plpgsql;
2999 select footest();
3000 ERROR:  query returned more than one row
3001 CONTEXT:  PL/pgSQL function footest() line 5 at SQL statement
3002 create or replace function footest() returns void as $$
3003 declare x record;
3004 begin
3005   -- should work
3006   execute 'insert into foo values(5,6) returning *' into x;
3007   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
3008 end$$ language plpgsql;
3009 select footest();
3010 NOTICE:  x.f1 = 5, x.f2 = 6
3011  footest
3012 ---------
3013
3014 (1 row)
3015
3016 create or replace function footest() returns void as $$
3017 declare x record;
3018 begin
3019   -- this should work since EXECUTE isn't as picky
3020   execute 'insert into foo values(7,8),(9,10) returning *' into x;
3021   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
3022 end$$ language plpgsql;
3023 select footest();
3024 NOTICE:  x.f1 = 7, x.f2 = 8
3025  footest
3026 ---------
3027
3028 (1 row)
3029
3030 select * from foo;
3031  f1 | f2
3032 ----+----
3033   1 |  2
3034   3 |  4
3035   5 |  6
3036   5 |  6
3037   7 |  8
3038   9 | 10
3039 (6 rows)
3040
3041 create or replace function footest() returns void as $$
3042 declare x record;
3043 begin
3044   -- should work
3045   select * from foo where f1 = 3 into strict x;
3046   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
3047 end$$ language plpgsql;
3048 select footest();
3049 NOTICE:  x.f1 = 3, x.f2 = 4
3050  footest
3051 ---------
3052
3053 (1 row)
3054
3055 create or replace function footest() returns void as $$
3056 declare x record;
3057 begin
3058   -- should fail, no rows
3059   select * from foo where f1 = 0 into strict x;
3060   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
3061 end$$ language plpgsql;
3062 select footest();
3063 ERROR:  query returned no rows
3064 CONTEXT:  PL/pgSQL function footest() line 5 at SQL statement
3065 create or replace function footest() returns void as $$
3066 declare x record;
3067 begin
3068   -- should fail, too many rows
3069   select * from foo where f1 > 3 into strict x;
3070   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
3071 end$$ language plpgsql;
3072 select footest();
3073 ERROR:  query returned more than one row
3074 CONTEXT:  PL/pgSQL function footest() line 5 at SQL statement
3075 create or replace function footest() returns void as $$
3076 declare x record;
3077 begin
3078   -- should work
3079   execute 'select * from foo where f1 = 3' into strict x;
3080   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
3081 end$$ language plpgsql;
3082 select footest();
3083 NOTICE:  x.f1 = 3, x.f2 = 4
3084  footest
3085 ---------
3086
3087 (1 row)
3088
3089 create or replace function footest() returns void as $$
3090 declare x record;
3091 begin
3092   -- should fail, no rows
3093   execute 'select * from foo where f1 = 0' into strict x;
3094   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
3095 end$$ language plpgsql;
3096 select footest();
3097 ERROR:  query returned no rows
3098 CONTEXT:  PL/pgSQL function footest() line 5 at EXECUTE statement
3099 create or replace function footest() returns void as $$
3100 declare x record;
3101 begin
3102   -- should fail, too many rows
3103   execute 'select * from foo where f1 > 3' into strict x;
3104   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
3105 end$$ language plpgsql;
3106 select footest();
3107 ERROR:  query returned more than one row
3108 CONTEXT:  PL/pgSQL function footest() line 5 at EXECUTE statement
3109 drop function footest();
3110 -- test scrollable cursor support
3111 create function sc_test() returns setof integer as $$
3112 declare
3113   c scroll cursor for select f1 from int4_tbl;
3114   x integer;
3115 begin
3116   open c;
3117   fetch last from c into x;
3118   while found loop
3119     return next x;
3120     fetch prior from c into x;
3121   end loop;
3122   close c;
3123 end;
3124 $$ language plpgsql;
3125 select * from sc_test();
3126    sc_test
3127 -------------
3128  -2147483647
3129   2147483647
3130      -123456
3131       123456
3132            0
3133 (5 rows)
3134
3135 create or replace function sc_test() returns setof integer as $$
3136 declare
3137   c no scroll cursor for select f1 from int4_tbl;
3138   x integer;
3139 begin
3140   open c;
3141   fetch last from c into x;
3142   while found loop
3143     return next x;
3144     fetch prior from c into x;
3145   end loop;
3146   close c;
3147 end;
3148 $$ language plpgsql;
3149 select * from sc_test();  -- fails because of NO SCROLL specification
3150 ERROR:  cursor can only scan forward
3151 HINT:  Declare it with SCROLL option to enable backward scan.
3152 CONTEXT:  PL/pgSQL function sc_test() line 7 at FETCH
3153 create or replace function sc_test() returns setof integer as $$
3154 declare
3155   c refcursor;
3156   x integer;
3157 begin
3158   open c scroll for select f1 from int4_tbl;
3159   fetch last from c into x;
3160   while found loop
3161     return next x;
3162     fetch prior from c into x;
3163   end loop;
3164   close c;
3165 end;
3166 $$ language plpgsql;
3167 select * from sc_test();
3168    sc_test
3169 -------------
3170  -2147483647
3171   2147483647
3172      -123456
3173       123456
3174            0
3175 (5 rows)
3176
3177 create or replace function sc_test() returns setof integer as $$
3178 declare
3179   c refcursor;
3180   x integer;
3181 begin
3182   open c scroll for execute 'select f1 from int4_tbl';
3183   fetch last from c into x;
3184   while found loop
3185     return next x;
3186     fetch relative -2 from c into x;
3187   end loop;
3188   close c;
3189 end;
3190 $$ language plpgsql;
3191 select * from sc_test();
3192    sc_test
3193 -------------
3194  -2147483647
3195      -123456
3196            0
3197 (3 rows)
3198
3199 create or replace function sc_test() returns setof integer as $$
3200 declare
3201   c refcursor;
3202   x integer;
3203 begin
3204   open c scroll for execute 'select f1 from int4_tbl';
3205   fetch last from c into x;
3206   while found loop
3207     return next x;
3208     move backward 2 from c;
3209     fetch relative -1 from c into x;
3210   end loop;
3211   close c;
3212 end;
3213 $$ language plpgsql;
3214 select * from sc_test();
3215    sc_test
3216 -------------
3217  -2147483647
3218       123456
3219 (2 rows)
3220
3221 create or replace function sc_test() returns setof integer as $$
3222 declare
3223   c cursor for select * from generate_series(1, 10);
3224   x integer;
3225 begin
3226   open c;
3227   loop
3228       move relative 2 in c;
3229       if not found then
3230           exit;
3231       end if;
3232       fetch next from c into x;
3233       if found then
3234           return next x;
3235       end if;
3236   end loop;
3237   close c;
3238 end;
3239 $$ language plpgsql;
3240 select * from sc_test();
3241  sc_test
3242 ---------
3243        3
3244        6
3245        9
3246 (3 rows)
3247
3248 create or replace function sc_test() returns setof integer as $$
3249 declare
3250   c cursor for select * from generate_series(1, 10);
3251   x integer;
3252 begin
3253   open c;
3254   move forward all in c;
3255   fetch backward from c into x;
3256   if found then
3257     return next x;
3258   end if;
3259   close c;
3260 end;
3261 $$ language plpgsql;
3262 select * from sc_test();
3263  sc_test
3264 ---------
3265       10
3266 (1 row)
3267
3268 drop function sc_test();
3269 -- test qualified variable names
3270 create function pl_qual_names (param1 int) returns void as $$
3271 <<outerblock>>
3272 declare
3273   param1 int := 1;
3274 begin
3275   <<innerblock>>
3276   declare
3277     param1 int := 2;
3278   begin
3279     raise notice 'param1 = %', param1;
3280     raise notice 'pl_qual_names.param1 = %', pl_qual_names.param1;
3281     raise notice 'outerblock.param1 = %', outerblock.param1;
3282     raise notice 'innerblock.param1 = %', innerblock.param1;
3283   end;
3284 end;
3285 $$ language plpgsql;
3286 select pl_qual_names(42);
3287 NOTICE:  param1 = 2
3288 NOTICE:  pl_qual_names.param1 = 42
3289 NOTICE:  outerblock.param1 = 1
3290 NOTICE:  innerblock.param1 = 2
3291  pl_qual_names
3292 ---------------
3293
3294 (1 row)
3295
3296 drop function pl_qual_names(int);
3297 -- tests for RETURN QUERY
3298 create function ret_query1(out int, out int) returns setof record as $$
3299 begin
3300     $1 := -1;
3301     $2 := -2;
3302     return next;
3303     return query select x + 1, x * 10 from generate_series(0, 10) s (x);
3304     return next;
3305 end;
3306 $$ language plpgsql;
3307 select * from ret_query1();
3308  column1 | column2
3309 ---------+---------
3310       -1 |      -2
3311        1 |       0
3312        2 |      10
3313        3 |      20
3314        4 |      30
3315        5 |      40
3316        6 |      50
3317        7 |      60
3318        8 |      70
3319        9 |      80
3320       10 |      90
3321       11 |     100
3322       -1 |      -2
3323 (13 rows)
3324
3325 create type record_type as (x text, y int, z boolean);
3326 create or replace function ret_query2(lim int) returns setof record_type as $$
3327 begin
3328     return query select md5(s.x::text), s.x, s.x > 0
3329                  from generate_series(-8, lim) s (x) where s.x % 2 = 0;
3330 end;
3331 $$ language plpgsql;
3332 select * from ret_query2(8);
3333                 x                 | y  | z
3334 ----------------------------------+----+---
3335  a8d2ec85eaf98407310b72eb73dda247 | -8 | f
3336  596a3d04481816330f07e4f97510c28f | -6 | f
3337  0267aaf632e87a63288a08331f22c7c3 | -4 | f
3338  5d7b9adcbe1c629ec722529dd12e5129 | -2 | f
3339  cfcd208495d565ef66e7dff9f98764da |  0 | f
3340  c81e728d9d4c2f636f067f89cc14862c |  2 | t
3341  a87ff679a2f3e71d9181a67b7542122c |  4 | t
3342  1679091c5a880faf6fb5e6087eb1b2dc |  6 | t
3343  c9f0f895fb98ab9159f51fd0297e236d |  8 | t
3344 (9 rows)
3345
3346 -- test EXECUTE USING
3347 create function exc_using(int, text) returns int as $$
3348 declare i int;
3349 begin
3350   for i in execute 'select * from generate_series(1,$1)' using $1+1 loop
3351     raise notice '%', i;
3352   end loop;
3353   execute 'select $2 + $2*3 + length($1)' into i using $2,$1;
3354   return i;
3355 end
3356 $$ language plpgsql;
3357 select exc_using(5, 'foobar');
3358 NOTICE:  1
3359 NOTICE:  2
3360 NOTICE:  3
3361 NOTICE:  4
3362 NOTICE:  5
3363 NOTICE:  6
3364  exc_using
3365 -----------
3366         26
3367 (1 row)
3368
3369 drop function exc_using(int, text);
3370 create or replace function exc_using(int) returns void as $$
3371 declare
3372   c refcursor;
3373   i int;
3374 begin
3375   open c for execute 'select * from generate_series(1,$1)' using $1+1;
3376   loop
3377     fetch c into i;
3378     exit when not found;
3379     raise notice '%', i;
3380   end loop;
3381   close c;
3382   return;
3383 end;
3384 $$ language plpgsql;
3385 select exc_using(5);
3386 NOTICE:  1
3387 NOTICE:  2
3388 NOTICE:  3
3389 NOTICE:  4
3390 NOTICE:  5
3391 NOTICE:  6
3392  exc_using
3393 -----------
3394
3395 (1 row)
3396
3397 drop function exc_using(int);
3398 -- test FOR-over-cursor
3399 create or replace function forc01() returns void as $$
3400 declare
3401   c cursor(r1 integer, r2 integer)
3402        for select * from generate_series(r1,r2) i;
3403   c2 cursor
3404        for select * from generate_series(41,43) i;
3405 begin
3406   for r in c(5,7) loop
3407     raise notice '% from %', r.i, c;
3408   end loop;
3409   -- again, to test if cursor was closed properly
3410   for r in c(9,10) loop
3411     raise notice '% from %', r.i, c;
3412   end loop;
3413   -- and test a parameterless cursor
3414   for r in c2 loop
3415     raise notice '% from %', r.i, c2;
3416   end loop;
3417   -- and try it with a hand-assigned name
3418   raise notice 'after loop, c2 = %', c2;
3419   c2 := 'special_name';
3420   for r in c2 loop
3421     raise notice '% from %', r.i, c2;
3422   end loop;
3423   raise notice 'after loop, c2 = %', c2;
3424   -- and try it with a generated name
3425   -- (which we can't show in the output because it's variable)
3426   c2 := null;
3427   for r in c2 loop
3428     raise notice '%', r.i;
3429   end loop;
3430   raise notice 'after loop, c2 = %', c2;
3431   return;
3432 end;
3433 $$ language plpgsql;
3434 select forc01();
3435 NOTICE:  5 from c
3436 NOTICE:  6 from c
3437 NOTICE:  7 from c
3438 NOTICE:  9 from c
3439 NOTICE:  10 from c
3440 NOTICE:  41 from c2
3441 NOTICE:  42 from c2
3442 NOTICE:  43 from c2
3443 NOTICE:  after loop, c2 = c2
3444 NOTICE:  41 from special_name
3445 NOTICE:  42 from special_name
3446 NOTICE:  43 from special_name
3447 NOTICE:  after loop, c2 = special_name
3448 NOTICE:  41
3449 NOTICE:  42
3450 NOTICE:  43
3451 NOTICE:  after loop, c2 = <NULL>
3452  forc01
3453 --------
3454
3455 (1 row)
3456
3457 -- try updating the cursor's current row
3458 create temp table forc_test as
3459   select n as i, n as j from generate_series(1,10) n;
3460 create or replace function forc01() returns void as $$
3461 declare
3462   c cursor for select * from forc_test;
3463 begin
3464   for r in c loop
3465     raise notice '%, %', r.i, r.j;
3466     update forc_test set i = i * 100, j = r.j * 2 where current of c;
3467   end loop;
3468 end;
3469 $$ language plpgsql;
3470 select forc01();
3471 NOTICE:  1, 1
3472 NOTICE:  2, 2
3473 NOTICE:  3, 3
3474 NOTICE:  4, 4
3475 NOTICE:  5, 5
3476 NOTICE:  6, 6
3477 NOTICE:  7, 7
3478 NOTICE:  8, 8
3479 NOTICE:  9, 9
3480 NOTICE:  10, 10
3481  forc01
3482 --------
3483
3484 (1 row)
3485
3486 select * from forc_test;
3487   i   | j
3488 ------+----
3489   100 |  2
3490   200 |  4
3491   300 |  6
3492   400 |  8
3493   500 | 10
3494   600 | 12
3495   700 | 14
3496   800 | 16
3497   900 | 18
3498  1000 | 20
3499 (10 rows)
3500
3501 -- same, with a cursor whose portal name doesn't match variable name
3502 create or replace function forc01() returns void as $$
3503 declare
3504   c refcursor := 'fooled_ya';
3505   r record;
3506 begin
3507   open c for select * from forc_test;
3508   loop
3509     fetch c into r;
3510     exit when not found;
3511     raise notice '%, %', r.i, r.j;
3512     update forc_test set i = i * 100, j = r.j * 2 where current of c;
3513   end loop;
3514 end;
3515 $$ language plpgsql;
3516 select forc01();
3517 NOTICE:  100, 2
3518 NOTICE:  200, 4
3519 NOTICE:  300, 6
3520 NOTICE:  400, 8
3521 NOTICE:  500, 10
3522 NOTICE:  600, 12
3523 NOTICE:  700, 14
3524 NOTICE:  800, 16
3525 NOTICE:  900, 18
3526 NOTICE:  1000, 20
3527  forc01
3528 --------
3529
3530 (1 row)
3531
3532 select * from forc_test;
3533    i    | j
3534 --------+----
3535   10000 |  4
3536   20000 |  8
3537   30000 | 12
3538   40000 | 16
3539   50000 | 20
3540   60000 | 24
3541   70000 | 28
3542   80000 | 32
3543   90000 | 36
3544  100000 | 40
3545 (10 rows)
3546
3547 drop function forc01();
3548 -- fail because cursor has no query bound to it
3549 create or replace function forc_bad() returns void as $$
3550 declare
3551   c refcursor;
3552 begin
3553   for r in c loop
3554     raise notice '%', r.i;
3555   end loop;
3556 end;
3557 $$ language plpgsql;
3558 ERROR:  cursor FOR loop must use a bound cursor variable
3559 LINE 5:   for r in c loop
3560                    ^
3561 -- test RETURN QUERY EXECUTE
3562 create or replace function return_dquery()
3563 returns setof int as $$
3564 begin
3565   return query execute 'select * from (values(10),(20)) f';
3566   return query execute 'select * from (values($1),($2)) f' using 40,50;
3567 end;
3568 $$ language plpgsql;
3569 select * from return_dquery();
3570  return_dquery
3571 ---------------
3572             10
3573             20
3574             40
3575             50
3576 (4 rows)
3577
3578 drop function return_dquery();
3579 -- test RETURN QUERY with dropped columns
3580 create table tabwithcols(a int, b int, c int, d int);
3581 insert into tabwithcols values(10,20,30,40),(50,60,70,80);
3582 create or replace function returnqueryf()
3583 returns setof tabwithcols as $$
3584 begin
3585   return query select * from tabwithcols;
3586   return query execute 'select * from tabwithcols';
3587 end;
3588 $$ language plpgsql;
3589 select * from returnqueryf();
3590  a  | b  | c  | d
3591 ----+----+----+----
3592  10 | 20 | 30 | 40
3593  50 | 60 | 70 | 80
3594  10 | 20 | 30 | 40
3595  50 | 60 | 70 | 80
3596 (4 rows)
3597
3598 alter table tabwithcols drop column b;
3599 select * from returnqueryf();
3600  a  | c  | d
3601 ----+----+----
3602  10 | 30 | 40
3603  50 | 70 | 80
3604  10 | 30 | 40
3605  50 | 70 | 80
3606 (4 rows)
3607
3608 alter table tabwithcols drop column d;
3609 select * from returnqueryf();
3610  a  | c
3611 ----+----
3612  10 | 30
3613  50 | 70
3614  10 | 30
3615  50 | 70
3616 (4 rows)
3617
3618 alter table tabwithcols add column d int;
3619 select * from returnqueryf();
3620  a  | c  | d
3621 ----+----+---
3622  10 | 30 |
3623  50 | 70 |
3624  10 | 30 |
3625  50 | 70 |
3626 (4 rows)
3627
3628 drop function returnqueryf();
3629 drop table tabwithcols;
3630 -- Tests for 8.4's new RAISE features
3631 create or replace function raise_test() returns void as $$
3632 begin
3633   raise notice '% % %', 1, 2, 3
3634      using errcode = '55001', detail = 'some detail info', hint = 'some hint';
3635   raise '% % %', 1, 2, 3
3636      using errcode = 'division_by_zero', detail = 'some detail info';
3637 end;
3638 $$ language plpgsql;
3639 select raise_test();
3640 NOTICE:  1 2 3
3641 DETAIL:  some detail info
3642 HINT:  some hint
3643 ERROR:  1 2 3
3644 DETAIL:  some detail info
3645 -- Since we can't actually see the thrown SQLSTATE in default psql output,
3646 -- test it like this; this also tests re-RAISE
3647 create or replace function raise_test() returns void as $$
3648 begin
3649   raise 'check me'
3650      using errcode = 'division_by_zero', detail = 'some detail info';
3651   exception
3652     when others then
3653       raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3654       raise;
3655 end;
3656 $$ language plpgsql;
3657 select raise_test();
3658 NOTICE:  SQLSTATE: 22012 SQLERRM: check me
3659 ERROR:  check me
3660 DETAIL:  some detail info
3661 create or replace function raise_test() returns void as $$
3662 begin
3663   raise 'check me'
3664      using errcode = '1234F', detail = 'some detail info';
3665   exception
3666     when others then
3667       raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3668       raise;
3669 end;
3670 $$ language plpgsql;
3671 select raise_test();
3672 NOTICE:  SQLSTATE: 1234F SQLERRM: check me
3673 ERROR:  check me
3674 DETAIL:  some detail info
3675 -- SQLSTATE specification in WHEN
3676 create or replace function raise_test() returns void as $$
3677 begin
3678   raise 'check me'
3679      using errcode = '1234F', detail = 'some detail info';
3680   exception
3681     when sqlstate '1234F' then
3682       raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3683       raise;
3684 end;
3685 $$ language plpgsql;
3686 select raise_test();
3687 NOTICE:  SQLSTATE: 1234F SQLERRM: check me
3688 ERROR:  check me
3689 DETAIL:  some detail info
3690 create or replace function raise_test() returns void as $$
3691 begin
3692   raise division_by_zero using detail = 'some detail info';
3693   exception
3694     when others then
3695       raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3696       raise;
3697 end;
3698 $$ language plpgsql;
3699 select raise_test();
3700 NOTICE:  SQLSTATE: 22012 SQLERRM: division_by_zero
3701 ERROR:  division_by_zero
3702 DETAIL:  some detail info
3703 create or replace function raise_test() returns void as $$
3704 begin
3705   raise division_by_zero;
3706 end;
3707 $$ language plpgsql;
3708 select raise_test();
3709 ERROR:  division_by_zero
3710 create or replace function raise_test() returns void as $$
3711 begin
3712   raise sqlstate '1234F';
3713 end;
3714 $$ language plpgsql;
3715 select raise_test();
3716 ERROR:  1234F
3717 create or replace function raise_test() returns void as $$
3718 begin
3719   raise division_by_zero using message = 'custom' || ' message';
3720 end;
3721 $$ language plpgsql;
3722 select raise_test();
3723 ERROR:  custom message
3724 create or replace function raise_test() returns void as $$
3725 begin
3726   raise using message = 'custom' || ' message', errcode = '22012';
3727 end;
3728 $$ language plpgsql;
3729 select raise_test();
3730 ERROR:  custom message
3731 -- conflict on message
3732 create or replace function raise_test() returns void as $$
3733 begin
3734   raise notice 'some message' using message = 'custom' || ' message', errcode = '22012';
3735 end;
3736 $$ language plpgsql;
3737 select raise_test();
3738 ERROR:  RAISE option already specified: MESSAGE
3739 CONTEXT:  PL/pgSQL function raise_test() line 3 at RAISE
3740 -- conflict on errcode
3741 create or replace function raise_test() returns void as $$
3742 begin
3743   raise division_by_zero using message = 'custom' || ' message', errcode = '22012';
3744 end;
3745 $$ language plpgsql;
3746 select raise_test();
3747 ERROR:  RAISE option already specified: ERRCODE
3748 CONTEXT:  PL/pgSQL function raise_test() line 3 at RAISE
3749 -- nothing to re-RAISE
3750 create or replace function raise_test() returns void as $$
3751 begin
3752   raise;
3753 end;
3754 $$ language plpgsql;
3755 select raise_test();
3756 ERROR:  RAISE without parameters cannot be used outside an exception handler
3757 CONTEXT:  PL/pgSQL function raise_test() line 3 at RAISE
3758 -- test access to exception data
3759 create function zero_divide() returns int as $$
3760 declare v int := 0;
3761 begin
3762   return 10 / v;
3763 end;
3764 $$ language plpgsql;
3765 create or replace function raise_test() returns void as $$
3766 begin
3767   raise exception 'custom exception'
3768      using detail = 'some detail of custom exception',
3769            hint = 'some hint related to custom exception';
3770 end;
3771 $$ language plpgsql;
3772 create function stacked_diagnostics_test() returns void as $$
3773 declare _sqlstate text;
3774         _message text;
3775         _context text;
3776 begin
3777   perform zero_divide();
3778 exception when others then
3779   get stacked diagnostics
3780         _sqlstate = returned_sqlstate,
3781         _message = message_text,
3782         _context = pg_exception_context;
3783   raise notice 'sqlstate: %, message: %, context: [%]',
3784     _sqlstate, _message, replace(_context, E'\n', ' <- ');
3785 end;
3786 $$ language plpgsql;
3787 select stacked_diagnostics_test();
3788 NOTICE:  sqlstate: 22012, message: division by zero, context: [PL/pgSQL function zero_divide() line 4 at RETURN <- SQL statement "SELECT zero_divide()" <- PL/pgSQL function stacked_diagnostics_test() line 6 at PERFORM]
3789  stacked_diagnostics_test
3790 --------------------------
3791
3792 (1 row)
3793
3794 create or replace function stacked_diagnostics_test() returns void as $$
3795 declare _detail text;
3796         _hint text;
3797         _message text;
3798 begin
3799   perform raise_test();
3800 exception when others then
3801   get stacked diagnostics
3802         _message = message_text,
3803         _detail = pg_exception_detail,
3804         _hint = pg_exception_hint;
3805   raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
3806 end;
3807 $$ language plpgsql;
3808 select stacked_diagnostics_test();
3809 NOTICE:  message: custom exception, detail: some detail of custom exception, hint: some hint related to custom exception
3810  stacked_diagnostics_test
3811 --------------------------
3812
3813 (1 row)
3814
3815 -- fail, cannot use stacked diagnostics statement outside handler
3816 create or replace function stacked_diagnostics_test() returns void as $$
3817 declare _detail text;
3818         _hint text;
3819         _message text;
3820 begin
3821   get stacked diagnostics
3822         _message = message_text,
3823         _detail = pg_exception_detail,
3824         _hint = pg_exception_hint;
3825   raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
3826 end;
3827 $$ language plpgsql;
3828 select stacked_diagnostics_test();
3829 ERROR:  GET STACKED DIAGNOSTICS cannot be used outside an exception handler
3830 CONTEXT:  PL/pgSQL function stacked_diagnostics_test() line 6 at GET DIAGNOSTICS
3831 drop function zero_divide();
3832 drop function stacked_diagnostics_test();
3833 -- check cases where implicit SQLSTATE variable could be confused with
3834 -- SQLSTATE as a keyword, cf bug #5524
3835 create or replace function raise_test() returns void as $$
3836 begin
3837   perform 1/0;
3838 exception
3839   when sqlstate '22012' then
3840     raise notice using message = sqlstate;
3841     raise sqlstate '22012' using message = 'substitute message';
3842 end;
3843 $$ language plpgsql;
3844 select raise_test();
3845 NOTICE:  22012
3846 ERROR:  substitute message
3847 drop function raise_test();
3848 -- test CASE statement
3849 create or replace function case_test(bigint) returns text as $$
3850 declare a int = 10;
3851         b int = 1;
3852 begin
3853   case $1
3854     when 1 then
3855       return 'one';
3856     when 2 then
3857       return 'two';
3858     when 3,4,3+5 then
3859       return 'three, four or eight';
3860     when a then
3861       return 'ten';
3862     when a+b, a+b+1 then
3863       return 'eleven, twelve';
3864   end case;
3865 end;
3866 $$ language plpgsql immutable;
3867 select case_test(1);
3868  case_test
3869 -----------
3870  one
3871 (1 row)
3872
3873 select case_test(2);
3874  case_test
3875 -----------
3876  two
3877 (1 row)
3878
3879 select case_test(3);
3880       case_test
3881 ----------------------
3882  three, four or eight
3883 (1 row)
3884
3885 select case_test(4);
3886       case_test
3887 ----------------------
3888  three, four or eight
3889 (1 row)
3890
3891 select case_test(5); -- fails
3892 ERROR:  case not found
3893 HINT:  CASE statement is missing ELSE part.
3894 CONTEXT:  PL/pgSQL function case_test(bigint) line 5 at CASE
3895 select case_test(8);
3896       case_test
3897 ----------------------
3898  three, four or eight
3899 (1 row)
3900
3901 select case_test(10);
3902  case_test
3903 -----------
3904  ten
3905 (1 row)
3906
3907 select case_test(11);
3908    case_test
3909 ----------------
3910  eleven, twelve
3911 (1 row)
3912
3913 select case_test(12);
3914    case_test
3915 ----------------
3916  eleven, twelve
3917 (1 row)
3918
3919 select case_test(13); -- fails
3920 ERROR:  case not found
3921 HINT:  CASE statement is missing ELSE part.
3922 CONTEXT:  PL/pgSQL function case_test(bigint) line 5 at CASE
3923 create or replace function catch() returns void as $$
3924 begin
3925   raise notice '%', case_test(6);
3926 exception
3927   when case_not_found then
3928     raise notice 'caught case_not_found % %', SQLSTATE, SQLERRM;
3929 end
3930 $$ language plpgsql;
3931 select catch();
3932 NOTICE:  caught case_not_found 20000 case not found
3933  catch
3934 -------
3935
3936 (1 row)
3937
3938 -- test the searched variant too, as well as ELSE
3939 create or replace function case_test(bigint) returns text as $$
3940 declare a int = 10;
3941 begin
3942   case
3943     when $1 = 1 then
3944       return 'one';
3945     when $1 = a + 2 then
3946       return 'twelve';
3947     else
3948       return 'other';
3949   end case;
3950 end;
3951 $$ language plpgsql immutable;
3952 select case_test(1);
3953  case_test
3954 -----------
3955  one
3956 (1 row)
3957
3958 select case_test(2);
3959  case_test
3960 -----------
3961  other
3962 (1 row)
3963
3964 select case_test(12);
3965  case_test
3966 -----------
3967  twelve
3968 (1 row)
3969
3970 select case_test(13);
3971  case_test
3972 -----------
3973  other
3974 (1 row)
3975
3976 drop function catch();
3977 drop function case_test(bigint);
3978 -- test variadic functions
3979 create or replace function vari(variadic int[])
3980 returns void as $$
3981 begin
3982   for i in array_lower($1,1)..array_upper($1,1) loop
3983     raise notice '%', $1[i];
3984   end loop; end;
3985 $$ language plpgsql;
3986 select vari(1,2,3,4,5);
3987 NOTICE:  1
3988 NOTICE:  2
3989 NOTICE:  3
3990 NOTICE:  4
3991 NOTICE:  5
3992  vari
3993 ------
3994
3995 (1 row)
3996
3997 select vari(3,4,5);
3998 NOTICE:  3
3999 NOTICE:  4
4000 NOTICE:  5
4001  vari
4002 ------
4003
4004 (1 row)
4005
4006 select vari(variadic array[5,6,7]);
4007 NOTICE:  5
4008 NOTICE:  6
4009 NOTICE:  7
4010  vari
4011 ------
4012
4013 (1 row)
4014
4015 drop function vari(int[]);
4016 -- coercion test
4017 create or replace function pleast(variadic numeric[])
4018 returns numeric as $$
4019 declare aux numeric = $1[array_lower($1,1)];
4020 begin
4021   for i in array_lower($1,1)+1..array_upper($1,1) loop
4022     if $1[i] < aux then aux := $1[i]; end if;
4023   end loop;
4024   return aux;
4025 end;
4026 $$ language plpgsql immutable strict;
4027 select pleast(10,1,2,3,-16);
4028  pleast
4029 --------
4030     -16
4031 (1 row)
4032
4033 select pleast(10.2,2.2,-1.1);
4034  pleast
4035 --------
4036    -1.1
4037 (1 row)
4038
4039 select pleast(10.2,10, -20);
4040  pleast
4041 --------
4042     -20
4043 (1 row)
4044
4045 select pleast(10,20, -1.0);
4046  pleast
4047 --------
4048    -1.0
4049 (1 row)
4050
4051 -- in case of conflict, non-variadic version is preferred
4052 create or replace function pleast(numeric)
4053 returns numeric as $$
4054 begin
4055   raise notice 'non-variadic function called';
4056   return $1;
4057 end;
4058 $$ language plpgsql immutable strict;
4059 select pleast(10);
4060 NOTICE:  non-variadic function called
4061  pleast
4062 --------
4063      10
4064 (1 row)
4065
4066 drop function pleast(numeric[]);
4067 drop function pleast(numeric);
4068 -- test table functions
4069 create function tftest(int) returns table(a int, b int) as $$
4070 begin
4071   return query select $1, $1+i from generate_series(1,5) g(i);
4072 end;
4073 $$ language plpgsql immutable strict;
4074 select * from tftest(10);
4075  a  | b
4076 ----+----
4077  10 | 11
4078  10 | 12
4079  10 | 13
4080  10 | 14
4081  10 | 15
4082 (5 rows)
4083
4084 create or replace function tftest(a1 int) returns table(a int, b int) as $$
4085 begin
4086   a := a1; b := a1 + 1;
4087   return next;
4088   a := a1 * 10; b := a1 * 10 + 1;
4089   return next;
4090 end;
4091 $$ language plpgsql immutable strict;
4092 select * from tftest(10);
4093   a  |  b
4094 -----+-----
4095   10 |  11
4096  100 | 101
4097 (2 rows)
4098
4099 drop function tftest(int);
4100 create or replace function rttest()
4101 returns setof int as $$
4102 declare rc int;
4103 begin
4104   return query values(10),(20);
4105   get diagnostics rc = row_count;
4106   raise notice '% %', found, rc;
4107   return query select * from (values(10),(20)) f(a) where false;
4108   get diagnostics rc = row_count;
4109   raise notice '% %', found, rc;
4110   return query execute 'values(10),(20)';
4111   get diagnostics rc = row_count;
4112   raise notice '% %', found, rc;
4113   return query execute 'select * from (values(10),(20)) f(a) where false';
4114   get diagnostics rc = row_count;
4115   raise notice '% %', found, rc;
4116 end;
4117 $$ language plpgsql;
4118 select * from rttest();
4119 NOTICE:  t 2
4120 NOTICE:  f 0
4121 NOTICE:  t 2
4122 NOTICE:  f 0
4123  rttest
4124 --------
4125      10
4126      20
4127      10
4128      20
4129 (4 rows)
4130
4131 drop function rttest();
4132 -- Test for proper cleanup at subtransaction exit.  This example
4133 -- exposed a bug in PG 8.2.
4134 CREATE FUNCTION leaker_1(fail BOOL) RETURNS INTEGER AS $$
4135 DECLARE
4136   v_var INTEGER;
4137 BEGIN
4138   BEGIN
4139     v_var := (leaker_2(fail)).error_code;
4140   EXCEPTION
4141     WHEN others THEN RETURN 0;
4142   END;
4143   RETURN 1;
4144 END;
4145 $$ LANGUAGE plpgsql;
4146 CREATE FUNCTION leaker_2(fail BOOL, OUT error_code INTEGER, OUT new_id INTEGER)
4147   RETURNS RECORD AS $$
4148 BEGIN
4149   IF fail THEN
4150     RAISE EXCEPTION 'fail ...';
4151   END IF;
4152   error_code := 1;
4153   new_id := 1;
4154   RETURN;
4155 END;
4156 $$ LANGUAGE plpgsql;
4157 SELECT * FROM leaker_1(false);
4158  leaker_1
4159 ----------
4160         1
4161 (1 row)
4162
4163 SELECT * FROM leaker_1(true);
4164  leaker_1
4165 ----------
4166         0
4167 (1 row)
4168
4169 DROP FUNCTION leaker_1(bool);
4170 DROP FUNCTION leaker_2(bool);
4171 -- Test for appropriate cleanup of non-simple expression evaluations
4172 -- (bug in all versions prior to August 2010)
4173 CREATE FUNCTION nonsimple_expr_test() RETURNS text[] AS $$
4174 DECLARE
4175   arr text[];
4176   lr text;
4177   i integer;
4178 BEGIN
4179   arr := array[array['foo','bar'], array['baz', 'quux']];
4180   lr := 'fool';
4181   i := 1;
4182   -- use sub-SELECTs to make expressions non-simple
4183   arr[(SELECT i)][(SELECT i+1)] := (SELECT lr);
4184   RETURN arr;
4185 END;
4186 $$ LANGUAGE plpgsql;
4187 SELECT nonsimple_expr_test();
4188    nonsimple_expr_test
4189 -------------------------
4190  {{foo,fool},{baz,quux}}
4191 (1 row)
4192
4193 DROP FUNCTION nonsimple_expr_test();
4194 CREATE FUNCTION nonsimple_expr_test() RETURNS integer AS $$
4195 declare
4196    i integer NOT NULL := 0;
4197 begin
4198   begin
4199     i := (SELECT NULL::integer);  -- should throw error
4200   exception
4201     WHEN OTHERS THEN
4202       i := (SELECT 1::integer);
4203   end;
4204   return i;
4205 end;
4206 $$ LANGUAGE plpgsql;
4207 SELECT nonsimple_expr_test();
4208  nonsimple_expr_test
4209 ---------------------
4210                    1
4211 (1 row)
4212
4213 DROP FUNCTION nonsimple_expr_test();
4214 --
4215 -- Test cases involving recursion and error recovery in simple expressions
4216 -- (bugs in all versions before October 2010).  The problems are most
4217 -- easily exposed by mutual recursion between plpgsql and sql functions.
4218 --
4219 create function recurse(float8) returns float8 as
4220 $$
4221 begin
4222   if ($1 > 0) then
4223     return sql_recurse($1 - 1);
4224   else
4225     return $1;
4226   end if;
4227 end;
4228 $$ language plpgsql;
4229 -- "limit" is to prevent this from being inlined
4230 create function sql_recurse(float8) returns float8 as
4231 $$ select recurse($1) limit 1; $$ language sql;
4232 select recurse(10);
4233  recurse
4234 ---------
4235        0
4236 (1 row)
4237
4238 create function error1(text) returns text language sql as
4239 $$ SELECT relname::text FROM pg_class c WHERE c.oid = $1::regclass $$;
4240 create function error2(p_name_table text) returns text language plpgsql as $$
4241 begin
4242   return error1(p_name_table);
4243 end$$;
4244 BEGIN;
4245 create table public.stuffs (stuff text);
4246 SAVEPOINT a;
4247 select error2('nonexistent.stuffs');
4248 ERROR:  schema "nonexistent" does not exist
4249 CONTEXT:  SQL function "error1" statement 1
4250 PL/pgSQL function error2(text) line 3 at RETURN
4251 ROLLBACK TO a;
4252 select error2('public.stuffs');
4253  error2
4254 --------
4255  stuffs
4256 (1 row)
4257
4258 rollback;
4259 drop function error2(p_name_table text);
4260 drop function error1(text);
4261 -- Test handling of string literals.
4262 set standard_conforming_strings = off;
4263 create or replace function strtest() returns text as $$
4264 begin
4265   raise notice 'foo\\bar\041baz';
4266   return 'foo\\bar\041baz';
4267 end
4268 $$ language plpgsql;
4269 WARNING:  nonstandard use of \\ in a string literal
4270 LINE 3:   raise notice 'foo\\bar\041baz';
4271                        ^
4272 HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
4273 WARNING:  nonstandard use of \\ in a string literal
4274 LINE 4:   return 'foo\\bar\041baz';
4275                  ^
4276 HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
4277 WARNING:  nonstandard use of \\ in a string literal
4278 LINE 4:   return 'foo\\bar\041baz';
4279                  ^
4280 HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
4281 select strtest();
4282 NOTICE:  foo\bar!baz
4283 WARNING:  nonstandard use of \\ in a string literal
4284 LINE 1: SELECT 'foo\\bar\041baz'
4285                ^
4286 HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
4287 QUERY:  SELECT 'foo\\bar\041baz'
4288 CONTEXT:  PL/pgSQL function strtest() line 4 at RETURN
4289    strtest
4290 -------------
4291  foo\bar!baz
4292 (1 row)
4293
4294 create or replace function strtest() returns text as $$
4295 begin
4296   raise notice E'foo\\bar\041baz';
4297   return E'foo\\bar\041baz';
4298 end
4299 $$ language plpgsql;
4300 select strtest();
4301 NOTICE:  foo\bar!baz
4302    strtest
4303 -------------
4304  foo\bar!baz
4305 (1 row)
4306
4307 set standard_conforming_strings = on;
4308 create or replace function strtest() returns text as $$
4309 begin
4310   raise notice 'foo\\bar\041baz\';
4311   return 'foo\\bar\041baz\';
4312 end
4313 $$ language plpgsql;
4314 select strtest();
4315 NOTICE:  foo\\bar\041baz\
4316      strtest
4317 ------------------
4318  foo\\bar\041baz\
4319 (1 row)
4320
4321 create or replace function strtest() returns text as $$
4322 begin
4323   raise notice E'foo\\bar\041baz';
4324   return E'foo\\bar\041baz';
4325 end
4326 $$ language plpgsql;
4327 select strtest();
4328 NOTICE:  foo\bar!baz
4329    strtest
4330 -------------
4331  foo\bar!baz
4332 (1 row)
4333
4334 drop function strtest();
4335 -- Test anonymous code blocks.
4336 DO $$
4337 DECLARE r record;
4338 BEGIN
4339     FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno
4340     LOOP
4341         RAISE NOTICE '%, %', r.roomno, r.comment;
4342     END LOOP;
4343 END$$;
4344 NOTICE:  001, Entrance
4345 NOTICE:  002, Office
4346 NOTICE:  003, Office
4347 NOTICE:  004, Technical
4348 NOTICE:  101, Office
4349 NOTICE:  102, Conference
4350 NOTICE:  103, Restroom
4351 NOTICE:  104, Technical
4352 NOTICE:  105, Office
4353 NOTICE:  106, Office
4354 -- these are to check syntax error reporting
4355 DO LANGUAGE plpgsql $$begin return 1; end$$;
4356 ERROR:  RETURN cannot have a parameter in function returning void
4357 LINE 1: DO LANGUAGE plpgsql $$begin return 1; end$$;
4358                                            ^
4359 DO $$
4360 DECLARE r record;
4361 BEGIN
4362     FOR r IN SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno
4363     LOOP
4364         RAISE NOTICE '%, %', r.roomno, r.comment;
4365     END LOOP;
4366 END$$;
4367 ERROR:  column "foo" does not exist
4368 LINE 1: SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomn...
4369                                         ^
4370 QUERY:  SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno
4371 CONTEXT:  PL/pgSQL function inline_code_block line 4 at FOR over SELECT rows
4372 -- Check variable scoping -- a var is not available in its own or prior
4373 -- default expressions.
4374 create function scope_test() returns int as $$
4375 declare x int := 42;
4376 begin
4377   declare y int := x + 1;
4378           x int := x + 2;
4379   begin
4380     return x * 100 + y;
4381   end;
4382 end;
4383 $$ language plpgsql;
4384 select scope_test();
4385  scope_test
4386 ------------
4387        4443
4388 (1 row)
4389
4390 drop function scope_test();
4391 -- Check handling of conflicts between plpgsql vars and table columns.
4392 set plpgsql.variable_conflict = error;
4393 create function conflict_test() returns setof int8_tbl as $$
4394 declare r record;
4395   q1 bigint := 42;
4396 begin
4397   for r in select q1,q2 from int8_tbl loop
4398     return next r;
4399   end loop;
4400 end;
4401 $$ language plpgsql;
4402 select * from conflict_test();
4403 ERROR:  column reference "q1" is ambiguous
4404 LINE 1: select q1,q2 from int8_tbl
4405                ^
4406 DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
4407 QUERY:  select q1,q2 from int8_tbl
4408 CONTEXT:  PL/pgSQL function conflict_test() line 5 at FOR over SELECT rows
4409 create or replace function conflict_test() returns setof int8_tbl as $$
4410 #variable_conflict use_variable
4411 declare r record;
4412   q1 bigint := 42;
4413 begin
4414   for r in select q1,q2 from int8_tbl loop
4415     return next r;
4416   end loop;
4417 end;
4418 $$ language plpgsql;
4419 select * from conflict_test();
4420  q1 |        q2
4421 ----+-------------------
4422  42 |               456
4423  42 |  4567890123456789
4424  42 |               123
4425  42 |  4567890123456789
4426  42 | -4567890123456789
4427 (5 rows)
4428
4429 create or replace function conflict_test() returns setof int8_tbl as $$
4430 #variable_conflict use_column
4431 declare r record;
4432   q1 bigint := 42;
4433 begin
4434   for r in select q1,q2 from int8_tbl loop
4435     return next r;
4436   end loop;
4437 end;
4438 $$ language plpgsql;
4439 select * from conflict_test();
4440         q1        |        q2
4441 ------------------+-------------------
4442               123 |               456
4443               123 |  4567890123456789
4444  4567890123456789 |               123
4445  4567890123456789 |  4567890123456789
4446  4567890123456789 | -4567890123456789
4447 (5 rows)
4448
4449 drop function conflict_test();
4450 -- Check that an unreserved keyword can be used as a variable name
4451 create function unreserved_test() returns int as $$
4452 declare
4453   forward int := 21;
4454 begin
4455   forward := forward * 2;
4456   return forward;
4457 end
4458 $$ language plpgsql;
4459 select unreserved_test();
4460  unreserved_test
4461 -----------------
4462               42
4463 (1 row)
4464
4465 drop function unreserved_test();
4466 --
4467 -- Test FOREACH over arrays
4468 --
4469 create function foreach_test(anyarray)
4470 returns void as $$
4471 declare x int;
4472 begin
4473   foreach x in array $1
4474   loop
4475     raise notice '%', x;
4476   end loop;
4477   end;
4478 $$ language plpgsql;
4479 select foreach_test(ARRAY[1,2,3,4]);
4480 NOTICE:  1
4481 NOTICE:  2
4482 NOTICE:  3
4483 NOTICE:  4
4484  foreach_test
4485 --------------
4486
4487 (1 row)
4488
4489 select foreach_test(ARRAY[[1,2],[3,4]]);
4490 NOTICE:  1
4491 NOTICE:  2
4492 NOTICE:  3
4493 NOTICE:  4
4494  foreach_test
4495 --------------
4496
4497 (1 row)
4498
4499 create or replace function foreach_test(anyarray)
4500 returns void as $$
4501 declare x int;
4502 begin
4503   foreach x slice 1 in array $1
4504   loop
4505     raise notice '%', x;
4506   end loop;
4507   end;
4508 $$ language plpgsql;
4509 -- should fail
4510 select foreach_test(ARRAY[1,2,3,4]);
4511 ERROR:  FOREACH ... SLICE loop variable must be of an array type
4512 CONTEXT:  PL/pgSQL function foreach_test(anyarray) line 4 at FOREACH over array
4513 select foreach_test(ARRAY[[1,2],[3,4]]);
4514 ERROR:  FOREACH ... SLICE loop variable must be of an array type
4515 CONTEXT:  PL/pgSQL function foreach_test(anyarray) line 4 at FOREACH over array
4516 create or replace function foreach_test(anyarray)
4517 returns void as $$
4518 declare x int[];
4519 begin
4520   foreach x slice 1 in array $1
4521   loop
4522     raise notice '%', x;
4523   end loop;
4524   end;
4525 $$ language plpgsql;
4526 select foreach_test(ARRAY[1,2,3,4]);
4527 NOTICE:  {1,2,3,4}
4528  foreach_test
4529 --------------
4530
4531 (1 row)
4532
4533 select foreach_test(ARRAY[[1,2],[3,4]]);
4534 NOTICE:  {1,2}
4535 NOTICE:  {3,4}
4536  foreach_test
4537 --------------
4538
4539 (1 row)
4540
4541 -- higher level of slicing
4542 create or replace function foreach_test(anyarray)
4543 returns void as $$
4544 declare x int[];
4545 begin
4546   foreach x slice 2 in array $1
4547   loop
4548     raise notice '%', x;
4549   end loop;
4550   end;
4551 $$ language plpgsql;
4552 -- should fail
4553 select foreach_test(ARRAY[1,2,3,4]);
4554 ERROR:  slice dimension (2) is out of the valid range 0..1
4555 CONTEXT:  PL/pgSQL function foreach_test(anyarray) line 4 at FOREACH over array
4556 -- ok
4557 select foreach_test(ARRAY[[1,2],[3,4]]);
4558 NOTICE:  {{1,2},{3,4}}
4559  foreach_test
4560 --------------
4561
4562 (1 row)
4563
4564 select foreach_test(ARRAY[[[1,2]],[[3,4]]]);
4565 NOTICE:  {{1,2}}
4566 NOTICE:  {{3,4}}
4567  foreach_test
4568 --------------
4569
4570 (1 row)
4571
4572 create type xy_tuple AS (x int, y int);
4573 -- iteration over array of records
4574 create or replace function foreach_test(anyarray)
4575 returns void as $$
4576 declare r record;
4577 begin
4578   foreach r in array $1
4579   loop
4580     raise notice '%', r;
4581   end loop;
4582   end;
4583 $$ language plpgsql;
4584 select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]);
4585 NOTICE:  (10,20)
4586 NOTICE:  (40,69)
4587 NOTICE:  (35,78)
4588  foreach_test
4589 --------------
4590
4591 (1 row)
4592
4593 select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]);
4594 NOTICE:  (10,20)
4595 NOTICE:  (40,69)
4596 NOTICE:  (35,78)
4597 NOTICE:  (88,76)
4598  foreach_test
4599 --------------
4600
4601 (1 row)
4602
4603 create or replace function foreach_test(anyarray)
4604 returns void as $$
4605 declare x int; y int;
4606 begin
4607   foreach x, y in array $1
4608   loop
4609     raise notice 'x = %, y = %', x, y;
4610   end loop;
4611   end;
4612 $$ language plpgsql;
4613 select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]);
4614 NOTICE:  x = 10, y = 20
4615 NOTICE:  x = 40, y = 69
4616 NOTICE:  x = 35, y = 78
4617  foreach_test
4618 --------------
4619
4620 (1 row)
4621
4622 select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]);
4623 NOTICE:  x = 10, y = 20
4624 NOTICE:  x = 40, y = 69
4625 NOTICE:  x = 35, y = 78
4626 NOTICE:  x = 88, y = 76
4627  foreach_test
4628 --------------
4629
4630 (1 row)
4631
4632 -- slicing over array of composite types
4633 create or replace function foreach_test(anyarray)
4634 returns void as $$
4635 declare x xy_tuple[];
4636 begin
4637   foreach x slice 1 in array $1
4638   loop
4639     raise notice '%', x;
4640   end loop;
4641   end;
4642 $$ language plpgsql;
4643 select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]);
4644 NOTICE:  {"(10,20)","(40,69)","(35,78)"}
4645  foreach_test
4646 --------------
4647
4648 (1 row)
4649
4650 select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]);
4651 NOTICE:  {"(10,20)","(40,69)"}
4652 NOTICE:  {"(35,78)","(88,76)"}
4653  foreach_test
4654 --------------
4655
4656 (1 row)
4657
4658 drop function foreach_test(anyarray);
4659 drop type xy_tuple;
4660 --
4661 -- Assorted tests for array subscript assignment
4662 --
4663 create temp table rtype (id int, ar text[]);
4664 create function arrayassign1() returns text[] language plpgsql as $$
4665 declare
4666  r record;
4667 begin
4668   r := row(12, '{foo,bar,baz}')::rtype;
4669   r.ar[2] := 'replace';
4670   return r.ar;
4671 end$$;
4672 select arrayassign1();
4673    arrayassign1
4674 -------------------
4675  {foo,replace,baz}
4676 (1 row)
4677
4678 select arrayassign1(); -- try again to exercise internal caching
4679    arrayassign1
4680 -------------------
4681  {foo,replace,baz}
4682 (1 row)
4683
4684 create domain orderedarray as int[2]
4685   constraint sorted check (value[1] < value[2]);
4686 select '{1,2}'::orderedarray;
4687  orderedarray
4688 --------------
4689  {1,2}
4690 (1 row)
4691
4692 select '{2,1}'::orderedarray;  -- fail
4693 ERROR:  value for domain orderedarray violates check constraint "sorted"
4694 create function testoa(x1 int, x2 int, x3 int) returns orderedarray
4695 language plpgsql as $$
4696 declare res orderedarray;
4697 begin
4698   res := array[x1, x2];
4699   res[2] := x3;
4700   return res;
4701 end$$;
4702 select testoa(1,2,3);
4703  testoa
4704 --------
4705  {1,3}
4706 (1 row)
4707
4708 select testoa(1,2,3); -- try again to exercise internal caching
4709  testoa
4710 --------
4711  {1,3}
4712 (1 row)
4713
4714 select testoa(2,1,3); -- fail at initial assign
4715 ERROR:  value for domain orderedarray violates check constraint "sorted"
4716 CONTEXT:  PL/pgSQL function testoa(integer,integer,integer) line 4 at assignment
4717 select testoa(1,2,1); -- fail at update
4718 ERROR:  value for domain orderedarray violates check constraint "sorted"
4719 CONTEXT:  PL/pgSQL function testoa(integer,integer,integer) line 5 at assignment
4720 drop function arrayassign1();
4721 drop function testoa(x1 int, x2 int, x3 int);