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-- 23create table Room ( 24 roomno char(8), 25 comment text 26); 27create unique index Room_rno on Room using btree (roomno bpchar_ops); 28create table WSlot ( 29 slotname char(20), 30 roomno char(8), 31 slotlink char(20), 32 backlink char(20) 33); 34create unique index WSlot_name on WSlot using btree (slotname bpchar_ops); 35create table PField ( 36 name text, 37 comment text 38); 39create unique index PField_name on PField using btree (name text_ops); 40create table PSlot ( 41 slotname char(20), 42 pfname text, 43 slotlink char(20), 44 backlink char(20) 45); 46create unique index PSlot_name on PSlot using btree (slotname bpchar_ops); 47create table PLine ( 48 slotname char(20), 49 phonenumber char(20), 50 comment text, 51 backlink char(20) 52); 53create unique index PLine_name on PLine using btree (slotname bpchar_ops); 54create table Hub ( 55 name char(14), 56 comment text, 57 nslots integer 58); 59create unique index Hub_name on Hub using btree (name bpchar_ops); 60create table HSlot ( 61 slotname char(20), 62 hubname char(14), 63 slotno integer, 64 slotlink char(20) 65); 66create unique index HSlot_name on HSlot using btree (slotname bpchar_ops); 67create index HSlot_hubname on HSlot using btree (hubname bpchar_ops); 68create table System ( 69 name text, 70 comment text 71); 72create unique index System_name on System using btree (name text_ops); 73create table IFace ( 74 slotname char(20), 75 sysname text, 76 ifname text, 77 slotlink char(20) 78); 79create unique index IFace_name on IFace using btree (slotname bpchar_ops); 80create table PHone ( 81 slotname char(20), 82 comment text, 83 slotlink char(20) 84); 85create 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-- ************************************************************ 96create function tg_room_au() returns trigger as ' 97begin 98 if new.roomno != old.roomno then 99 update WSlot set roomno = new.roomno where roomno = old.roomno; 100 end if; 101 return new; 102end; 103' language plpgsql; 104create 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-- ************************************************************ 110create function tg_room_ad() returns trigger as ' 111begin 112 delete from WSlot where roomno = old.roomno; 113 return old; 114end; 115' language plpgsql; 116create 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-- ************************************************************ 122create function tg_wslot_biu() returns trigger as $$ 123begin 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; 128end; 129$$ language plpgsql; 130create 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-- ************************************************************ 136create function tg_pfield_au() returns trigger as ' 137begin 138 if new.name != old.name then 139 update PSlot set pfname = new.name where pfname = old.name; 140 end if; 141 return new; 142end; 143' language plpgsql; 144create 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-- ************************************************************ 150create function tg_pfield_ad() returns trigger as ' 151begin 152 delete from PSlot where pfname = old.name; 153 return old; 154end; 155' language plpgsql; 156create 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-- ************************************************************ 162create function tg_pslot_biu() returns trigger as $proc$ 163declare 164 pfrec record; 165 ps alias for new; 166begin 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; 172end; 173$proc$ language plpgsql; 174create 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-- ************************************************************ 180create function tg_system_au() returns trigger as ' 181begin 182 if new.name != old.name then 183 update IFace set sysname = new.name where sysname = old.name; 184 end if; 185 return new; 186end; 187' language plpgsql; 188create 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-- ************************************************************ 194create function tg_iface_biu() returns trigger as $$ 195declare 196 sname text; 197 sysrec record; 198begin 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; 211end; 212$$ language plpgsql; 213create 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-- ************************************************************ 219create function tg_hub_a() returns trigger as ' 220declare 221 hname text; 222 dummy integer; 223begin 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; 239end; 240' language plpgsql; 241create 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-- ************************************************************ 246create function tg_hub_adjustslots(hname bpchar, 247 oldnslots integer, 248 newnslots integer) 249returns integer as ' 250begin 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; 263end 264' language plpgsql; 265-- Test comments 266COMMENT ON FUNCTION tg_hub_adjustslots_wrong(bpchar, integer, integer) IS 'function with args'; 267ERROR: function tg_hub_adjustslots_wrong(character, integer, integer) does not exist 268COMMENT ON FUNCTION tg_hub_adjustslots(bpchar, integer, integer) IS 'function with args'; 269COMMENT 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-- ************************************************************ 275create function tg_hslot_biu() returns trigger as ' 276declare 277 sname text; 278 xname HSlot.slotname%TYPE; 279 hubrec record; 280begin 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; 301end; 302' language plpgsql; 303create 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-- ************************************************************ 309create function tg_hslot_bd() returns trigger as ' 310declare 311 hubrec record; 312begin 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''; 321end; 322' language plpgsql; 323create 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-- ************************************************************ 329create function tg_chkslotname() returns trigger as ' 330begin 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; 335end; 336' language plpgsql; 337create trigger tg_chkslotname before insert 338 on PSlot for each row execute procedure tg_chkslotname('PS'); 339create trigger tg_chkslotname before insert 340 on WSlot for each row execute procedure tg_chkslotname('WS'); 341create trigger tg_chkslotname before insert 342 on PLine for each row execute procedure tg_chkslotname('PL'); 343create trigger tg_chkslotname before insert 344 on IFace for each row execute procedure tg_chkslotname('IF'); 345create 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-- ************************************************************ 351create function tg_chkslotlink() returns trigger as ' 352begin 353 if new.slotlink isnull then 354 new.slotlink := ''''; 355 end if; 356 return new; 357end; 358' language plpgsql; 359create trigger tg_chkslotlink before insert or update 360 on PSlot for each row execute procedure tg_chkslotlink(); 361create trigger tg_chkslotlink before insert or update 362 on WSlot for each row execute procedure tg_chkslotlink(); 363create trigger tg_chkslotlink before insert or update 364 on IFace for each row execute procedure tg_chkslotlink(); 365create trigger tg_chkslotlink before insert or update 366 on HSlot for each row execute procedure tg_chkslotlink(); 367create 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-- ************************************************************ 373create function tg_chkbacklink() returns trigger as ' 374begin 375 if new.backlink isnull then 376 new.backlink := ''''; 377 end if; 378 return new; 379end; 380' language plpgsql; 381create trigger tg_chkbacklink before insert or update 382 on PSlot for each row execute procedure tg_chkbacklink(); 383create trigger tg_chkbacklink before insert or update 384 on WSlot for each row execute procedure tg_chkbacklink(); 385create 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-- ************************************************************ 391create function tg_pslot_bu() returns trigger as ' 392begin 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; 409end; 410' language plpgsql; 411create 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-- ************************************************************ 417create function tg_wslot_bu() returns trigger as ' 418begin 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; 435end; 436' language plpgsql; 437create 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-- ************************************************************ 443create function tg_pline_bu() returns trigger as ' 444begin 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; 461end; 462' language plpgsql; 463create 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-- ************************************************************ 469create function tg_iface_bu() returns trigger as ' 470begin 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; 487end; 488' language plpgsql; 489create 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-- ************************************************************ 495create function tg_hslot_bu() returns trigger as ' 496begin 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; 513end; 514' language plpgsql; 515create 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-- ************************************************************ 521create function tg_phone_bu() returns trigger as ' 522begin 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; 537end; 538' language plpgsql; 539create 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-- ************************************************************ 545create function tg_backlink_a() returns trigger as ' 546declare 547 dummy integer; 548begin 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; 576end; 577' language plpgsql; 578create trigger tg_backlink_a after insert or update or delete 579 on PSlot for each row execute procedure tg_backlink_a('PS'); 580create trigger tg_backlink_a after insert or update or delete 581 on WSlot for each row execute procedure tg_backlink_a('WS'); 582create 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-- ************************************************************ 588create function tg_backlink_set(myname bpchar, blname bpchar) 589returns integer as ' 590declare 591 mytype char(2); 592 link char(4); 593 rec record; 594begin 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; 636end; 637' language plpgsql; 638-- ************************************************************ 639-- * Support function to clear out the backlink field if 640-- * it still points to specific slot 641-- ************************************************************ 642create function tg_backlink_unset(bpchar, bpchar) 643returns integer as ' 644declare 645 myname alias for $1; 646 blname alias for $2; 647 mytype char(2); 648 rec record; 649begin 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; 681end 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-- ************************************************************ 687create function tg_slotlink_a() returns trigger as ' 688declare 689 dummy integer; 690begin 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; 718end; 719' language plpgsql; 720create trigger tg_slotlink_a after insert or update or delete 721 on PSlot for each row execute procedure tg_slotlink_a('PS'); 722create trigger tg_slotlink_a after insert or update or delete 723 on WSlot for each row execute procedure tg_slotlink_a('WS'); 724create trigger tg_slotlink_a after insert or update or delete 725 on IFace for each row execute procedure tg_slotlink_a('IF'); 726create trigger tg_slotlink_a after insert or update or delete 727 on HSlot for each row execute procedure tg_slotlink_a('HS'); 728create 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-- ************************************************************ 734create function tg_slotlink_set(bpchar, bpchar) 735returns integer as ' 736declare 737 myname alias for $1; 738 blname alias for $2; 739 mytype char(2); 740 link char(4); 741 rec record; 742begin 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; 812end; 813' language plpgsql; 814-- ************************************************************ 815-- * Support function to clear out the slotlink field if 816-- * it still points to specific slot 817-- ************************************************************ 818create function tg_slotlink_unset(bpchar, bpchar) 819returns integer as ' 820declare 821 myname alias for $1; 822 blname alias for $2; 823 mytype char(2); 824 rec record; 825begin 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; 877end; 878' language plpgsql; 879-- ************************************************************ 880-- * Describe the backside of a patchfield slot 881-- ************************************************************ 882create function pslot_backlink_view(bpchar) 883returns text as ' 884<<outer>> 885declare 886 rec record; 887 bltype char(2); 888 retval text; 889begin 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; 920end; 921' language plpgsql; 922-- ************************************************************ 923-- * Describe the front of a patchfield slot 924-- ************************************************************ 925create function pslot_slotlink_view(bpchar) 926returns text as ' 927declare 928 psrec record; 929 sltype char(2); 930 retval text; 931begin 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; 954end; 955' language plpgsql; 956-- ************************************************************ 957-- * Describe the front of a wall connector slot 958-- ************************************************************ 959create function wslot_slotlink_view(bpchar) 960returns text as ' 961declare 962 rec record; 963 sltype char(2); 964 retval text; 965begin 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; 1002end; 1003' language plpgsql; 1004-- ************************************************************ 1005-- * View of a patchfield describing backside and patches 1006-- ************************************************************ 1007create 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-- 1014insert into Room values ('001', 'Entrance'); 1015insert into Room values ('002', 'Office'); 1016insert into Room values ('003', 'Office'); 1017insert into Room values ('004', 'Technical'); 1018insert into Room values ('101', 'Office'); 1019insert into Room values ('102', 'Conference'); 1020insert into Room values ('103', 'Restroom'); 1021insert into Room values ('104', 'Technical'); 1022insert into Room values ('105', 'Office'); 1023insert into Room values ('106', 'Office'); 1024-- 1025-- Second we install the wall connectors 1026-- 1027insert into WSlot values ('WS.001.1a', '001', '', ''); 1028insert into WSlot values ('WS.001.1b', '001', '', ''); 1029insert into WSlot values ('WS.001.2a', '001', '', ''); 1030insert into WSlot values ('WS.001.2b', '001', '', ''); 1031insert into WSlot values ('WS.001.3a', '001', '', ''); 1032insert into WSlot values ('WS.001.3b', '001', '', ''); 1033insert into WSlot values ('WS.002.1a', '002', '', ''); 1034insert into WSlot values ('WS.002.1b', '002', '', ''); 1035insert into WSlot values ('WS.002.2a', '002', '', ''); 1036insert into WSlot values ('WS.002.2b', '002', '', ''); 1037insert into WSlot values ('WS.002.3a', '002', '', ''); 1038insert into WSlot values ('WS.002.3b', '002', '', ''); 1039insert into WSlot values ('WS.003.1a', '003', '', ''); 1040insert into WSlot values ('WS.003.1b', '003', '', ''); 1041insert into WSlot values ('WS.003.2a', '003', '', ''); 1042insert into WSlot values ('WS.003.2b', '003', '', ''); 1043insert into WSlot values ('WS.003.3a', '003', '', ''); 1044insert into WSlot values ('WS.003.3b', '003', '', ''); 1045insert into WSlot values ('WS.101.1a', '101', '', ''); 1046insert into WSlot values ('WS.101.1b', '101', '', ''); 1047insert into WSlot values ('WS.101.2a', '101', '', ''); 1048insert into WSlot values ('WS.101.2b', '101', '', ''); 1049insert into WSlot values ('WS.101.3a', '101', '', ''); 1050insert into WSlot values ('WS.101.3b', '101', '', ''); 1051insert into WSlot values ('WS.102.1a', '102', '', ''); 1052insert into WSlot values ('WS.102.1b', '102', '', ''); 1053insert into WSlot values ('WS.102.2a', '102', '', ''); 1054insert into WSlot values ('WS.102.2b', '102', '', ''); 1055insert into WSlot values ('WS.102.3a', '102', '', ''); 1056insert into WSlot values ('WS.102.3b', '102', '', ''); 1057insert into WSlot values ('WS.105.1a', '105', '', ''); 1058insert into WSlot values ('WS.105.1b', '105', '', ''); 1059insert into WSlot values ('WS.105.2a', '105', '', ''); 1060insert into WSlot values ('WS.105.2b', '105', '', ''); 1061insert into WSlot values ('WS.105.3a', '105', '', ''); 1062insert into WSlot values ('WS.105.3b', '105', '', ''); 1063insert into WSlot values ('WS.106.1a', '106', '', ''); 1064insert into WSlot values ('WS.106.1b', '106', '', ''); 1065insert into WSlot values ('WS.106.2a', '106', '', ''); 1066insert into WSlot values ('WS.106.2b', '106', '', ''); 1067insert into WSlot values ('WS.106.3a', '106', '', ''); 1068insert into WSlot values ('WS.106.3b', '106', '', ''); 1069-- 1070-- Now create the patch fields and their slots 1071-- 1072insert into PField values ('PF0_1', 'Wallslots basement'); 1073-- 1074-- The cables for these will be made later, so they are unconnected for now 1075-- 1076insert into PSlot values ('PS.base.a1', 'PF0_1', '', ''); 1077insert into PSlot values ('PS.base.a2', 'PF0_1', '', ''); 1078insert into PSlot values ('PS.base.a3', 'PF0_1', '', ''); 1079insert into PSlot values ('PS.base.a4', 'PF0_1', '', ''); 1080insert into PSlot values ('PS.base.a5', 'PF0_1', '', ''); 1081insert into PSlot values ('PS.base.a6', 'PF0_1', '', ''); 1082-- 1083-- These are already wired to the wall connectors 1084-- 1085insert into PSlot values ('PS.base.b1', 'PF0_1', '', 'WS.002.1a'); 1086insert into PSlot values ('PS.base.b2', 'PF0_1', '', 'WS.002.1b'); 1087insert into PSlot values ('PS.base.b3', 'PF0_1', '', 'WS.002.2a'); 1088insert into PSlot values ('PS.base.b4', 'PF0_1', '', 'WS.002.2b'); 1089insert into PSlot values ('PS.base.b5', 'PF0_1', '', 'WS.002.3a'); 1090insert into PSlot values ('PS.base.b6', 'PF0_1', '', 'WS.002.3b'); 1091insert into PSlot values ('PS.base.c1', 'PF0_1', '', 'WS.003.1a'); 1092insert into PSlot values ('PS.base.c2', 'PF0_1', '', 'WS.003.1b'); 1093insert into PSlot values ('PS.base.c3', 'PF0_1', '', 'WS.003.2a'); 1094insert into PSlot values ('PS.base.c4', 'PF0_1', '', 'WS.003.2b'); 1095insert into PSlot values ('PS.base.c5', 'PF0_1', '', 'WS.003.3a'); 1096insert 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-- 1101insert into PField values ('PF0_X', 'Phonelines basement'); 1102insert into PSlot values ('PS.base.ta1', 'PF0_X', '', ''); 1103insert into PSlot values ('PS.base.ta2', 'PF0_X', '', ''); 1104insert into PSlot values ('PS.base.ta3', 'PF0_X', '', ''); 1105insert into PSlot values ('PS.base.ta4', 'PF0_X', '', ''); 1106insert into PSlot values ('PS.base.ta5', 'PF0_X', '', ''); 1107insert into PSlot values ('PS.base.ta6', 'PF0_X', '', ''); 1108insert into PSlot values ('PS.base.tb1', 'PF0_X', '', ''); 1109insert into PSlot values ('PS.base.tb2', 'PF0_X', '', ''); 1110insert into PSlot values ('PS.base.tb3', 'PF0_X', '', ''); 1111insert into PSlot values ('PS.base.tb4', 'PF0_X', '', ''); 1112insert into PSlot values ('PS.base.tb5', 'PF0_X', '', ''); 1113insert into PSlot values ('PS.base.tb6', 'PF0_X', '', ''); 1114insert into PField values ('PF1_1', 'Wallslots first floor'); 1115insert into PSlot values ('PS.first.a1', 'PF1_1', '', 'WS.101.1a'); 1116insert into PSlot values ('PS.first.a2', 'PF1_1', '', 'WS.101.1b'); 1117insert into PSlot values ('PS.first.a3', 'PF1_1', '', 'WS.101.2a'); 1118insert into PSlot values ('PS.first.a4', 'PF1_1', '', 'WS.101.2b'); 1119insert into PSlot values ('PS.first.a5', 'PF1_1', '', 'WS.101.3a'); 1120insert into PSlot values ('PS.first.a6', 'PF1_1', '', 'WS.101.3b'); 1121insert into PSlot values ('PS.first.b1', 'PF1_1', '', 'WS.102.1a'); 1122insert into PSlot values ('PS.first.b2', 'PF1_1', '', 'WS.102.1b'); 1123insert into PSlot values ('PS.first.b3', 'PF1_1', '', 'WS.102.2a'); 1124insert into PSlot values ('PS.first.b4', 'PF1_1', '', 'WS.102.2b'); 1125insert into PSlot values ('PS.first.b5', 'PF1_1', '', 'WS.102.3a'); 1126insert into PSlot values ('PS.first.b6', 'PF1_1', '', 'WS.102.3b'); 1127insert into PSlot values ('PS.first.c1', 'PF1_1', '', 'WS.105.1a'); 1128insert into PSlot values ('PS.first.c2', 'PF1_1', '', 'WS.105.1b'); 1129insert into PSlot values ('PS.first.c3', 'PF1_1', '', 'WS.105.2a'); 1130insert into PSlot values ('PS.first.c4', 'PF1_1', '', 'WS.105.2b'); 1131insert into PSlot values ('PS.first.c5', 'PF1_1', '', 'WS.105.3a'); 1132insert into PSlot values ('PS.first.c6', 'PF1_1', '', 'WS.105.3b'); 1133insert into PSlot values ('PS.first.d1', 'PF1_1', '', 'WS.106.1a'); 1134insert into PSlot values ('PS.first.d2', 'PF1_1', '', 'WS.106.1b'); 1135insert into PSlot values ('PS.first.d3', 'PF1_1', '', 'WS.106.2a'); 1136insert into PSlot values ('PS.first.d4', 'PF1_1', '', 'WS.106.2b'); 1137insert into PSlot values ('PS.first.d5', 'PF1_1', '', 'WS.106.3a'); 1138insert 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-- 1144update PSlot set backlink = 'WS.001.1a' where slotname = 'PS.base.a1'; 1145update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a3'; 1146select * 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 1157select * 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 1168update PSlot set backlink = 'WS.001.2a' where slotname = 'PS.base.a3'; 1169select * 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 1180select * 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 1191update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a2'; 1192select * 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 1203select * 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-- 1219update WSlot set backlink = 'PS.base.a4' where slotname = 'WS.001.2b'; 1220update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3a'; 1221select * 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 1232select * 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 1243update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3b'; 1244select * 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 1255select * 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 1266update WSlot set backlink = 'PS.base.a5' where slotname = 'WS.001.3a'; 1267select * 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 1278select * 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 1289insert into PField values ('PF1_2', 'Phonelines first floor'); 1290insert into PSlot values ('PS.first.ta1', 'PF1_2', '', ''); 1291insert into PSlot values ('PS.first.ta2', 'PF1_2', '', ''); 1292insert into PSlot values ('PS.first.ta3', 'PF1_2', '', ''); 1293insert into PSlot values ('PS.first.ta4', 'PF1_2', '', ''); 1294insert into PSlot values ('PS.first.ta5', 'PF1_2', '', ''); 1295insert into PSlot values ('PS.first.ta6', 'PF1_2', '', ''); 1296insert into PSlot values ('PS.first.tb1', 'PF1_2', '', ''); 1297insert into PSlot values ('PS.first.tb2', 'PF1_2', '', ''); 1298insert into PSlot values ('PS.first.tb3', 'PF1_2', '', ''); 1299insert into PSlot values ('PS.first.tb4', 'PF1_2', '', ''); 1300insert into PSlot values ('PS.first.tb5', 'PF1_2', '', ''); 1301insert into PSlot values ('PS.first.tb6', 'PF1_2', '', ''); 1302-- 1303-- Fix the wrong name for patchfield PF0_2 1304-- 1305update PField set name = 'PF0_2' where name = 'PF0_X'; 1306select * 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 1377select * 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 wired on insert to the patchfields. Again the 1427-- triggers automatically tell the PSlots to update their 1428-- backlink field. 1429-- 1430insert into PLine values ('PL.001', '-0', 'Central call', 'PS.base.ta1'); 1431insert into PLine values ('PL.002', '-101', '', 'PS.base.ta2'); 1432insert into PLine values ('PL.003', '-102', '', 'PS.base.ta3'); 1433insert into PLine values ('PL.004', '-103', '', 'PS.base.ta5'); 1434insert into PLine values ('PL.005', '-104', '', 'PS.base.ta6'); 1435insert into PLine values ('PL.006', '-106', '', 'PS.base.tb2'); 1436insert into PLine values ('PL.007', '-108', '', 'PS.base.tb3'); 1437insert into PLine values ('PL.008', '-109', '', 'PS.base.tb4'); 1438insert into PLine values ('PL.009', '-121', '', 'PS.base.tb5'); 1439insert into PLine values ('PL.010', '-122', '', 'PS.base.tb6'); 1440insert into PLine values ('PL.015', '-134', '', 'PS.first.ta1'); 1441insert into PLine values ('PL.016', '-137', '', 'PS.first.ta3'); 1442insert into PLine values ('PL.017', '-139', '', 'PS.first.ta4'); 1443insert into PLine values ('PL.018', '-362', '', 'PS.first.tb1'); 1444insert into PLine values ('PL.019', '-363', '', 'PS.first.tb2'); 1445insert into PLine values ('PL.020', '-364', '', 'PS.first.tb3'); 1446insert into PLine values ('PL.021', '-365', '', 'PS.first.tb5'); 1447insert into PLine values ('PL.022', '-367', '', 'PS.first.tb6'); 1448insert into PLine values ('PL.028', '-501', 'Fax entrance', 'PS.base.ta2'); 1449insert 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-- 1454insert into PHone values ('PH.hc001', 'Hicom standard', 'WS.001.1a'); 1455update PSlot set slotlink = 'PS.base.ta1' where slotname = 'PS.base.a1'; 1456insert into PHone values ('PH.hc002', 'Hicom standard', 'WS.002.1a'); 1457update PSlot set slotlink = 'PS.base.ta5' where slotname = 'PS.base.b1'; 1458insert into PHone values ('PH.hc003', 'Hicom standard', 'WS.002.2a'); 1459update PSlot set slotlink = 'PS.base.tb2' where slotname = 'PS.base.b3'; 1460insert into PHone values ('PH.fax001', 'Canon fax', 'WS.001.2a'); 1461update 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-- 1466insert into Hub values ('base.hub1', 'Patchfield PF0_1 hub', 16); 1467insert into System values ('orion', 'PC'); 1468insert into IFace values ('IF', 'orion', 'eth0', 'WS.002.1b'); 1469update PSlot set slotlink = 'HS.base.hub1.1' where slotname = 'PS.base.b2'; 1470-- 1471-- Now we take a look at the patchfield 1472-- 1473select * 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 1496select * 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-- 1516insert into PField values ('PF1_1', 'should fail due to unique index'); 1517ERROR: duplicate key value violates unique constraint "pfield_name" 1518DETAIL: Key (name)=(PF1_1) already exists. 1519update PSlot set backlink = 'WS.not.there' where slotname = 'PS.base.a1'; 1520ERROR: WS.not.there does not exist 1521CONTEXT: PL/pgSQL function tg_backlink_set(character,character) line 30 at RAISE 1522PL/pgSQL function tg_backlink_a() line 17 at assignment 1523update PSlot set backlink = 'XX.illegal' where slotname = 'PS.base.a1'; 1524ERROR: illegal backlink beginning with XX 1525CONTEXT: PL/pgSQL function tg_backlink_set(character,character) line 47 at RAISE 1526PL/pgSQL function tg_backlink_a() line 17 at assignment 1527update PSlot set slotlink = 'PS.not.there' where slotname = 'PS.base.a1'; 1528ERROR: PS.not.there does not exist 1529CONTEXT: PL/pgSQL function tg_slotlink_set(character,character) line 30 at RAISE 1530PL/pgSQL function tg_slotlink_a() line 17 at assignment 1531update PSlot set slotlink = 'XX.illegal' where slotname = 'PS.base.a1'; 1532ERROR: illegal slotlink beginning with XX 1533CONTEXT: PL/pgSQL function tg_slotlink_set(character,character) line 77 at RAISE 1534PL/pgSQL function tg_slotlink_a() line 17 at assignment 1535insert into HSlot values ('HS', 'base.hub1', 1, ''); 1536ERROR: duplicate key value violates unique constraint "hslot_name" 1537DETAIL: Key (slotname)=(HS.base.hub1.1 ) already exists. 1538insert into HSlot values ('HS', 'base.hub1', 20, ''); 1539ERROR: no manual manipulation of HSlot 1540CONTEXT: PL/pgSQL function tg_hslot_biu() line 12 at RAISE 1541delete from HSlot; 1542ERROR: no manual manipulation of HSlot 1543CONTEXT: PL/pgSQL function tg_hslot_bd() line 12 at RAISE 1544insert into IFace values ('IF', 'notthere', 'eth0', ''); 1545ERROR: system "notthere" does not exist 1546CONTEXT: PL/pgSQL function tg_iface_biu() line 8 at RAISE 1547insert into IFace values ('IF', 'orion', 'ethernet_interface_name_too_long', ''); 1548ERROR: IFace slotname "IF.orion.ethernet_interface_name_too_long" too long (20 char max) 1549CONTEXT: PL/pgSQL function tg_iface_biu() line 14 at RAISE 1550-- 1551-- The following tests are unrelated to the scenario outlined above; 1552-- they merely exercise specific parts of PL/pgSQL 1553-- 1554-- 1555-- Test recursion, per bug report 7-Sep-01 1556-- 1557CREATE FUNCTION recursion_test(int,int) RETURNS text AS ' 1558DECLARE rslt text; 1559BEGIN 1560 IF $1 <= 0 THEN 1561 rslt = CAST($2 AS TEXT); 1562 ELSE 1563 rslt = CAST($1 AS TEXT) || '','' || recursion_test($1 - 1, $2); 1564 END IF; 1565 RETURN rslt; 1566END;' LANGUAGE plpgsql; 1567SELECT recursion_test(4,3); 1568 recursion_test 1569---------------- 1570 4,3,2,1,3 1571(1 row) 1572 1573-- 1574-- Test the FOUND magic variable 1575-- 1576CREATE TABLE found_test_tbl (a int); 1577create function test_found() 1578 returns boolean as ' 1579 declare 1580 begin 1581 insert into found_test_tbl values (1); 1582 if FOUND then 1583 insert into found_test_tbl values (2); 1584 end if; 1585 1586 update found_test_tbl set a = 100 where a = 1; 1587 if FOUND then 1588 insert into found_test_tbl values (3); 1589 end if; 1590 1591 delete from found_test_tbl where a = 9999; -- matches no rows 1592 if not FOUND then 1593 insert into found_test_tbl values (4); 1594 end if; 1595 1596 for i in 1 .. 10 loop 1597 -- no need to do anything 1598 end loop; 1599 if FOUND then 1600 insert into found_test_tbl values (5); 1601 end if; 1602 1603 -- never executes the loop 1604 for i in 2 .. 1 loop 1605 -- no need to do anything 1606 end loop; 1607 if not FOUND then 1608 insert into found_test_tbl values (6); 1609 end if; 1610 return true; 1611 end;' language plpgsql; 1612select test_found(); 1613 test_found 1614------------ 1615 t 1616(1 row) 1617 1618select * from found_test_tbl; 1619 a 1620----- 1621 2 1622 100 1623 3 1624 4 1625 5 1626 6 1627(6 rows) 1628 1629-- 1630-- Test set-returning functions for PL/pgSQL 1631-- 1632create function test_table_func_rec() returns setof found_test_tbl as ' 1633DECLARE 1634 rec RECORD; 1635BEGIN 1636 FOR rec IN select * from found_test_tbl LOOP 1637 RETURN NEXT rec; 1638 END LOOP; 1639 RETURN; 1640END;' language plpgsql; 1641select * from test_table_func_rec(); 1642 a 1643----- 1644 2 1645 100 1646 3 1647 4 1648 5 1649 6 1650(6 rows) 1651 1652create function test_table_func_row() returns setof found_test_tbl as ' 1653DECLARE 1654 row found_test_tbl%ROWTYPE; 1655BEGIN 1656 FOR row IN select * from found_test_tbl LOOP 1657 RETURN NEXT row; 1658 END LOOP; 1659 RETURN; 1660END;' language plpgsql; 1661select * from test_table_func_row(); 1662 a 1663----- 1664 2 1665 100 1666 3 1667 4 1668 5 1669 6 1670(6 rows) 1671 1672create function test_ret_set_scalar(int,int) returns setof int as ' 1673DECLARE 1674 i int; 1675BEGIN 1676 FOR i IN $1 .. $2 LOOP 1677 RETURN NEXT i + 1; 1678 END LOOP; 1679 RETURN; 1680END;' language plpgsql; 1681select * from test_ret_set_scalar(1,10); 1682 test_ret_set_scalar 1683--------------------- 1684 2 1685 3 1686 4 1687 5 1688 6 1689 7 1690 8 1691 9 1692 10 1693 11 1694(10 rows) 1695 1696create function test_ret_set_rec_dyn(int) returns setof record as ' 1697DECLARE 1698 retval RECORD; 1699BEGIN 1700 IF $1 > 10 THEN 1701 SELECT INTO retval 5, 10, 15; 1702 RETURN NEXT retval; 1703 RETURN NEXT retval; 1704 ELSE 1705 SELECT INTO retval 50, 5::numeric, ''xxx''::text; 1706 RETURN NEXT retval; 1707 RETURN NEXT retval; 1708 END IF; 1709 RETURN; 1710END;' language plpgsql; 1711SELECT * FROM test_ret_set_rec_dyn(1500) AS (a int, b int, c int); 1712 a | b | c 1713---+----+---- 1714 5 | 10 | 15 1715 5 | 10 | 15 1716(2 rows) 1717 1718SELECT * FROM test_ret_set_rec_dyn(5) AS (a int, b numeric, c text); 1719 a | b | c 1720----+---+----- 1721 50 | 5 | xxx 1722 50 | 5 | xxx 1723(2 rows) 1724 1725create function test_ret_rec_dyn(int) returns record as ' 1726DECLARE 1727 retval RECORD; 1728BEGIN 1729 IF $1 > 10 THEN 1730 SELECT INTO retval 5, 10, 15; 1731 RETURN retval; 1732 ELSE 1733 SELECT INTO retval 50, 5::numeric, ''xxx''::text; 1734 RETURN retval; 1735 END IF; 1736END;' language plpgsql; 1737SELECT * FROM test_ret_rec_dyn(1500) AS (a int, b int, c int); 1738 a | b | c 1739---+----+---- 1740 5 | 10 | 15 1741(1 row) 1742 1743SELECT * FROM test_ret_rec_dyn(5) AS (a int, b numeric, c text); 1744 a | b | c 1745----+---+----- 1746 50 | 5 | xxx 1747(1 row) 1748 1749-- 1750-- Test some simple polymorphism cases. 1751-- 1752create function f1(x anyelement) returns anyelement as $$ 1753begin 1754 return x + 1; 1755end$$ language plpgsql; 1756select f1(42) as int, f1(4.5) as num; 1757 int | num 1758-----+----- 1759 43 | 5.5 1760(1 row) 1761 1762select f1(point(3,4)); -- fail for lack of + operator 1763ERROR: operator does not exist: point + integer 1764LINE 1: SELECT x + 1 1765 ^ 1766HINT: No operator matches the given name and argument types. You might need to add explicit type casts. 1767QUERY: SELECT x + 1 1768CONTEXT: PL/pgSQL function f1(anyelement) line 3 at RETURN 1769drop function f1(x anyelement); 1770create function f1(x anyelement) returns anyarray as $$ 1771begin 1772 return array[x + 1, x + 2]; 1773end$$ language plpgsql; 1774select f1(42) as int, f1(4.5) as num; 1775 int | num 1776---------+----------- 1777 {43,44} | {5.5,6.5} 1778(1 row) 1779 1780drop function f1(x anyelement); 1781create function f1(x anyarray) returns anyelement as $$ 1782begin 1783 return x[1]; 1784end$$ language plpgsql; 1785select f1(array[2,4]) as int, f1(array[4.5, 7.7]) as num; 1786 int | num 1787-----+----- 1788 2 | 4.5 1789(1 row) 1790 1791select f1(stavalues1) from pg_statistic; -- fail, can't infer element type 1792ERROR: cannot determine element type of "anyarray" argument 1793drop function f1(x anyarray); 1794create function f1(x anyarray) returns anyarray as $$ 1795begin 1796 return x; 1797end$$ language plpgsql; 1798select f1(array[2,4]) as int, f1(array[4.5, 7.7]) as num; 1799 int | num 1800-------+----------- 1801 {2,4} | {4.5,7.7} 1802(1 row) 1803 1804select f1(stavalues1) from pg_statistic; -- fail, can't infer element type 1805ERROR: PL/pgSQL functions cannot accept type anyarray 1806CONTEXT: compilation of PL/pgSQL function "f1" near line 1 1807drop function f1(x anyarray); 1808-- fail, can't infer type: 1809create function f1(x anyelement) returns anyrange as $$ 1810begin 1811 return array[x + 1, x + 2]; 1812end$$ language plpgsql; 1813ERROR: cannot determine result data type 1814DETAIL: A result of type anyrange requires at least one input of type anyrange. 1815create function f1(x anyrange) returns anyarray as $$ 1816begin 1817 return array[lower(x), upper(x)]; 1818end$$ language plpgsql; 1819select f1(int4range(42, 49)) as int, f1(float8range(4.5, 7.8)) as num; 1820 int | num 1821---------+----------- 1822 {42,49} | {4.5,7.8} 1823(1 row) 1824 1825drop function f1(x anyrange); 1826create function f1(x anycompatible, y anycompatible) returns anycompatiblearray as $$ 1827begin 1828 return array[x, y]; 1829end$$ language plpgsql; 1830select f1(2, 4) as int, f1(2, 4.5) as num; 1831 int | num 1832-------+--------- 1833 {2,4} | {2,4.5} 1834(1 row) 1835 1836drop function f1(x anycompatible, y anycompatible); 1837create function f1(x anycompatiblerange, y anycompatible, z anycompatible) returns anycompatiblearray as $$ 1838begin 1839 return array[lower(x), upper(x), y, z]; 1840end$$ language plpgsql; 1841select f1(int4range(42, 49), 11, 2::smallint) as int, f1(float8range(4.5, 7.8), 7.8, 11::real) as num; 1842 int | num 1843--------------+------------------ 1844 {42,49,11,2} | {4.5,7.8,7.8,11} 1845(1 row) 1846 1847select f1(int4range(42, 49), 11, 4.5) as fail; -- range type doesn't fit 1848ERROR: function f1(int4range, integer, numeric) does not exist 1849LINE 1: select f1(int4range(42, 49), 11, 4.5) as fail; 1850 ^ 1851HINT: No function matches the given name and argument types. You might need to add explicit type casts. 1852drop function f1(x anycompatiblerange, y anycompatible, z anycompatible); 1853-- fail, can't infer type: 1854create function f1(x anycompatible) returns anycompatiblerange as $$ 1855begin 1856 return array[x + 1, x + 2]; 1857end$$ language plpgsql; 1858ERROR: cannot determine result data type 1859DETAIL: A result of type anycompatiblerange requires at least one input of type anycompatiblerange. 1860create function f1(x anycompatiblerange, y anycompatiblearray) returns anycompatiblerange as $$ 1861begin 1862 return x; 1863end$$ language plpgsql; 1864select f1(int4range(42, 49), array[11]) as int, f1(float8range(4.5, 7.8), array[7]) as num; 1865 int | num 1866---------+----------- 1867 [42,49) | [4.5,7.8) 1868(1 row) 1869 1870drop function f1(x anycompatiblerange, y anycompatiblearray); 1871create function f1(a anyelement, b anyarray, 1872 c anycompatible, d anycompatible, 1873 OUT x anyarray, OUT y anycompatiblearray) 1874as $$ 1875begin 1876 x := a || b; 1877 y := array[c, d]; 1878end$$ language plpgsql; 1879select x, pg_typeof(x), y, pg_typeof(y) 1880 from f1(11, array[1, 2], 42, 34.5); 1881 x | pg_typeof | y | pg_typeof 1882----------+-----------+-----------+----------- 1883 {11,1,2} | integer[] | {42,34.5} | numeric[] 1884(1 row) 1885 1886select x, pg_typeof(x), y, pg_typeof(y) 1887 from f1(11, array[1, 2], point(1,2), point(3,4)); 1888 x | pg_typeof | y | pg_typeof 1889----------+-----------+-------------------+----------- 1890 {11,1,2} | integer[] | {"(1,2)","(3,4)"} | point[] 1891(1 row) 1892 1893select x, pg_typeof(x), y, pg_typeof(y) 1894 from f1(11, '{1,2}', point(1,2), '(3,4)'); 1895 x | pg_typeof | y | pg_typeof 1896----------+-----------+-------------------+----------- 1897 {11,1,2} | integer[] | {"(1,2)","(3,4)"} | point[] 1898(1 row) 1899 1900select x, pg_typeof(x), y, pg_typeof(y) 1901 from f1(11, array[1, 2.2], 42, 34.5); -- fail 1902ERROR: function f1(integer, numeric[], integer, numeric) does not exist 1903LINE 2: from f1(11, array[1, 2.2], 42, 34.5); 1904 ^ 1905HINT: No function matches the given name and argument types. You might need to add explicit type casts. 1906drop function f1(a anyelement, b anyarray, 1907 c anycompatible, d anycompatible); 1908-- 1909-- Test handling of OUT parameters, including polymorphic cases. 1910-- Note that RETURN is optional with OUT params; we try both ways. 1911-- 1912-- wrong way to do it: 1913create function f1(in i int, out j int) returns int as $$ 1914begin 1915 return i+1; 1916end$$ language plpgsql; 1917ERROR: RETURN cannot have a parameter in function with OUT parameters 1918LINE 3: return i+1; 1919 ^ 1920create function f1(in i int, out j int) as $$ 1921begin 1922 j := i+1; 1923 return; 1924end$$ language plpgsql; 1925select f1(42); 1926 f1 1927---- 1928 43 1929(1 row) 1930 1931select * from f1(42); 1932 j 1933---- 1934 43 1935(1 row) 1936 1937create or replace function f1(inout i int) as $$ 1938begin 1939 i := i+1; 1940end$$ language plpgsql; 1941select f1(42); 1942 f1 1943---- 1944 43 1945(1 row) 1946 1947select * from f1(42); 1948 i 1949---- 1950 43 1951(1 row) 1952 1953drop function f1(int); 1954create function f1(in i int, out j int) returns setof int as $$ 1955begin 1956 j := i+1; 1957 return next; 1958 j := i+2; 1959 return next; 1960 return; 1961end$$ language plpgsql; 1962select * from f1(42); 1963 j 1964---- 1965 43 1966 44 1967(2 rows) 1968 1969drop function f1(int); 1970create function f1(in i int, out j int, out k text) as $$ 1971begin 1972 j := i; 1973 j := j+1; 1974 k := 'foo'; 1975end$$ language plpgsql; 1976select f1(42); 1977 f1 1978---------- 1979 (43,foo) 1980(1 row) 1981 1982select * from f1(42); 1983 j | k 1984----+----- 1985 43 | foo 1986(1 row) 1987 1988drop function f1(int); 1989create function f1(in i int, out j int, out k text) returns setof record as $$ 1990begin 1991 j := i+1; 1992 k := 'foo'; 1993 return next; 1994 j := j+1; 1995 k := 'foot'; 1996 return next; 1997end$$ language plpgsql; 1998select * from f1(42); 1999 j | k 2000----+------ 2001 43 | foo 2002 44 | foot 2003(2 rows) 2004 2005drop function f1(int); 2006create function duplic(in i anyelement, out j anyelement, out k anyarray) as $$ 2007begin 2008 j := i; 2009 k := array[j,j]; 2010 return; 2011end$$ language plpgsql; 2012select * from duplic(42); 2013 j | k 2014----+--------- 2015 42 | {42,42} 2016(1 row) 2017 2018select * from duplic('foo'::text); 2019 j | k 2020-----+----------- 2021 foo | {foo,foo} 2022(1 row) 2023 2024drop function duplic(anyelement); 2025create function duplic(in i anycompatiblerange, out j anycompatible, out k anycompatiblearray) as $$ 2026begin 2027 j := lower(i); 2028 k := array[lower(i),upper(i)]; 2029 return; 2030end$$ language plpgsql; 2031select * from duplic(int4range(42,49)); 2032 j | k 2033----+--------- 2034 42 | {42,49} 2035(1 row) 2036 2037select * from duplic(textrange('aaa', 'bbb')); 2038 j | k 2039-----+----------- 2040 aaa | {aaa,bbb} 2041(1 row) 2042 2043drop function duplic(anycompatiblerange); 2044-- 2045-- test PERFORM 2046-- 2047create table perform_test ( 2048 a INT, 2049 b INT 2050); 2051create function perform_simple_func(int) returns boolean as ' 2052BEGIN 2053 IF $1 < 20 THEN 2054 INSERT INTO perform_test VALUES ($1, $1 + 10); 2055 RETURN TRUE; 2056 ELSE 2057 RETURN FALSE; 2058 END IF; 2059END;' language plpgsql; 2060create function perform_test_func() returns void as ' 2061BEGIN 2062 IF FOUND then 2063 INSERT INTO perform_test VALUES (100, 100); 2064 END IF; 2065 2066 PERFORM perform_simple_func(5); 2067 2068 IF FOUND then 2069 INSERT INTO perform_test VALUES (100, 100); 2070 END IF; 2071 2072 PERFORM perform_simple_func(50); 2073 2074 IF FOUND then 2075 INSERT INTO perform_test VALUES (100, 100); 2076 END IF; 2077 2078 RETURN; 2079END;' language plpgsql; 2080SELECT perform_test_func(); 2081 perform_test_func 2082------------------- 2083 2084(1 row) 2085 2086SELECT * FROM perform_test; 2087 a | b 2088-----+----- 2089 5 | 15 2090 100 | 100 2091 100 | 100 2092(3 rows) 2093 2094drop table perform_test; 2095-- 2096-- Test proper snapshot handling in simple expressions 2097-- 2098create temp table users(login text, id serial); 2099create function sp_id_user(a_login text) returns int as $$ 2100declare x int; 2101begin 2102 select into x id from users where login = a_login; 2103 if found then return x; end if; 2104 return 0; 2105end$$ language plpgsql stable; 2106insert into users values('user1'); 2107select sp_id_user('user1'); 2108 sp_id_user 2109------------ 2110 1 2111(1 row) 2112 2113select sp_id_user('userx'); 2114 sp_id_user 2115------------ 2116 0 2117(1 row) 2118 2119create function sp_add_user(a_login text) returns int as $$ 2120declare my_id_user int; 2121begin 2122 my_id_user = sp_id_user( a_login ); 2123 IF my_id_user > 0 THEN 2124 RETURN -1; -- error code for existing user 2125 END IF; 2126 INSERT INTO users ( login ) VALUES ( a_login ); 2127 my_id_user = sp_id_user( a_login ); 2128 IF my_id_user = 0 THEN 2129 RETURN -2; -- error code for insertion failure 2130 END IF; 2131 RETURN my_id_user; 2132end$$ language plpgsql; 2133select sp_add_user('user1'); 2134 sp_add_user 2135------------- 2136 -1 2137(1 row) 2138 2139select sp_add_user('user2'); 2140 sp_add_user 2141------------- 2142 2 2143(1 row) 2144 2145select sp_add_user('user2'); 2146 sp_add_user 2147------------- 2148 -1 2149(1 row) 2150 2151select sp_add_user('user3'); 2152 sp_add_user 2153------------- 2154 3 2155(1 row) 2156 2157select sp_add_user('user3'); 2158 sp_add_user 2159------------- 2160 -1 2161(1 row) 2162 2163drop function sp_add_user(text); 2164drop function sp_id_user(text); 2165-- 2166-- tests for refcursors 2167-- 2168create table rc_test (a int, b int); 2169copy rc_test from stdin; 2170create function return_unnamed_refcursor() returns refcursor as $$ 2171declare 2172 rc refcursor; 2173begin 2174 open rc for select a from rc_test; 2175 return rc; 2176end 2177$$ language plpgsql; 2178create function use_refcursor(rc refcursor) returns int as $$ 2179declare 2180 rc refcursor; 2181 x record; 2182begin 2183 rc := return_unnamed_refcursor(); 2184 fetch next from rc into x; 2185 return x.a; 2186end 2187$$ language plpgsql; 2188select use_refcursor(return_unnamed_refcursor()); 2189 use_refcursor 2190--------------- 2191 5 2192(1 row) 2193 2194create function return_refcursor(rc refcursor) returns refcursor as $$ 2195begin 2196 open rc for select a from rc_test; 2197 return rc; 2198end 2199$$ language plpgsql; 2200create function refcursor_test1(refcursor) returns refcursor as $$ 2201begin 2202 perform return_refcursor($1); 2203 return $1; 2204end 2205$$ language plpgsql; 2206begin; 2207select refcursor_test1('test1'); 2208 refcursor_test1 2209----------------- 2210 test1 2211(1 row) 2212 2213fetch next in test1; 2214 a 2215--- 2216 5 2217(1 row) 2218 2219select refcursor_test1('test2'); 2220 refcursor_test1 2221----------------- 2222 test2 2223(1 row) 2224 2225fetch all from test2; 2226 a 2227----- 2228 5 2229 50 2230 500 2231(3 rows) 2232 2233commit; 2234-- should fail 2235fetch next from test1; 2236ERROR: cursor "test1" does not exist 2237create function refcursor_test2(int, int) returns boolean as $$ 2238declare 2239 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2; 2240 nonsense record; 2241begin 2242 open c1($1, $2); 2243 fetch c1 into nonsense; 2244 close c1; 2245 if found then 2246 return true; 2247 else 2248 return false; 2249 end if; 2250end 2251$$ language plpgsql; 2252select refcursor_test2(20000, 20000) as "Should be false", 2253 refcursor_test2(20, 20) as "Should be true"; 2254 Should be false | Should be true 2255-----------------+---------------- 2256 f | t 2257(1 row) 2258 2259-- 2260-- tests for cursors with named parameter arguments 2261-- 2262create function namedparmcursor_test1(int, int) returns boolean as $$ 2263declare 2264 c1 cursor (param1 int, param12 int) for select * from rc_test where a > param1 and b > param12; 2265 nonsense record; 2266begin 2267 open c1(param12 := $2, param1 := $1); 2268 fetch c1 into nonsense; 2269 close c1; 2270 if found then 2271 return true; 2272 else 2273 return false; 2274 end if; 2275end 2276$$ language plpgsql; 2277select namedparmcursor_test1(20000, 20000) as "Should be false", 2278 namedparmcursor_test1(20, 20) as "Should be true"; 2279 Should be false | Should be true 2280-----------------+---------------- 2281 f | t 2282(1 row) 2283 2284-- mixing named and positional argument notations 2285create function namedparmcursor_test2(int, int) returns boolean as $$ 2286declare 2287 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2; 2288 nonsense record; 2289begin 2290 open c1(param1 := $1, $2); 2291 fetch c1 into nonsense; 2292 close c1; 2293 if found then 2294 return true; 2295 else 2296 return false; 2297 end if; 2298end 2299$$ language plpgsql; 2300select namedparmcursor_test2(20, 20); 2301 namedparmcursor_test2 2302----------------------- 2303 t 2304(1 row) 2305 2306-- mixing named and positional: param2 is given twice, once in named notation 2307-- and second time in positional notation. Should throw an error at parse time 2308create function namedparmcursor_test3() returns void as $$ 2309declare 2310 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2; 2311begin 2312 open c1(param2 := 20, 21); 2313end 2314$$ language plpgsql; 2315ERROR: value for parameter "param2" of cursor "c1" specified more than once 2316LINE 5: open c1(param2 := 20, 21); 2317 ^ 2318-- mixing named and positional: same as previous test, but param1 is duplicated 2319create function namedparmcursor_test4() returns void as $$ 2320declare 2321 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2; 2322begin 2323 open c1(20, param1 := 21); 2324end 2325$$ language plpgsql; 2326ERROR: value for parameter "param1" of cursor "c1" specified more than once 2327LINE 5: open c1(20, param1 := 21); 2328 ^ 2329-- duplicate named parameter, should throw an error at parse time 2330create function namedparmcursor_test5() returns void as $$ 2331declare 2332 c1 cursor (p1 int, p2 int) for 2333 select * from tenk1 where thousand = p1 and tenthous = p2; 2334begin 2335 open c1 (p2 := 77, p2 := 42); 2336end 2337$$ language plpgsql; 2338ERROR: value for parameter "p2" of cursor "c1" specified more than once 2339LINE 6: open c1 (p2 := 77, p2 := 42); 2340 ^ 2341-- not enough parameters, should throw an error at parse time 2342create function namedparmcursor_test6() returns void as $$ 2343declare 2344 c1 cursor (p1 int, p2 int) for 2345 select * from tenk1 where thousand = p1 and tenthous = p2; 2346begin 2347 open c1 (p2 := 77); 2348end 2349$$ language plpgsql; 2350ERROR: not enough arguments for cursor "c1" 2351LINE 6: open c1 (p2 := 77); 2352 ^ 2353-- division by zero runtime error, the context given in the error message 2354-- should be sensible 2355create function namedparmcursor_test7() returns void as $$ 2356declare 2357 c1 cursor (p1 int, p2 int) for 2358 select * from tenk1 where thousand = p1 and tenthous = p2; 2359begin 2360 open c1 (p2 := 77, p1 := 42/0); 2361end $$ language plpgsql; 2362select namedparmcursor_test7(); 2363ERROR: division by zero 2364CONTEXT: SQL statement "SELECT 42/0 AS p1, 77 AS p2;" 2365PL/pgSQL function namedparmcursor_test7() line 6 at OPEN 2366-- check that line comments work correctly within the argument list (there 2367-- is some special handling of this case in the code: the newline after the 2368-- comment must be preserved when the argument-evaluating query is 2369-- constructed, otherwise the comment effectively comments out the next 2370-- argument, too) 2371create function namedparmcursor_test8() returns int4 as $$ 2372declare 2373 c1 cursor (p1 int, p2 int) for 2374 select count(*) from tenk1 where thousand = p1 and tenthous = p2; 2375 n int4; 2376begin 2377 open c1 (77 -- test 2378 , 42); 2379 fetch c1 into n; 2380 return n; 2381end $$ language plpgsql; 2382select namedparmcursor_test8(); 2383 namedparmcursor_test8 2384----------------------- 2385 0 2386(1 row) 2387 2388-- cursor parameter name can match plpgsql variable or unreserved keyword 2389create function namedparmcursor_test9(p1 int) returns int4 as $$ 2390declare 2391 c1 cursor (p1 int, p2 int, debug int) for 2392 select count(*) from tenk1 where thousand = p1 and tenthous = p2 2393 and four = debug; 2394 p2 int4 := 1006; 2395 n int4; 2396begin 2397 open c1 (p1 := p1, p2 := p2, debug := 2); 2398 fetch c1 into n; 2399 return n; 2400end $$ language plpgsql; 2401select namedparmcursor_test9(6); 2402 namedparmcursor_test9 2403----------------------- 2404 1 2405(1 row) 2406 2407-- 2408-- tests for "raise" processing 2409-- 2410create function raise_test1(int) returns int as $$ 2411begin 2412 raise notice 'This message has too many parameters!', $1; 2413 return $1; 2414end; 2415$$ language plpgsql; 2416ERROR: too many parameters specified for RAISE 2417CONTEXT: compilation of PL/pgSQL function "raise_test1" near line 3 2418create function raise_test2(int) returns int as $$ 2419begin 2420 raise notice 'This message has too few parameters: %, %, %', $1, $1; 2421 return $1; 2422end; 2423$$ language plpgsql; 2424ERROR: too few parameters specified for RAISE 2425CONTEXT: compilation of PL/pgSQL function "raise_test2" near line 3 2426create function raise_test3(int) returns int as $$ 2427begin 2428 raise notice 'This message has no parameters (despite having %% signs in it)!'; 2429 return $1; 2430end; 2431$$ language plpgsql; 2432select raise_test3(1); 2433NOTICE: This message has no parameters (despite having % signs in it)! 2434 raise_test3 2435------------- 2436 1 2437(1 row) 2438 2439-- Test re-RAISE inside a nested exception block. This case is allowed 2440-- by Oracle's PL/SQL but was handled differently by PG before 9.1. 2441CREATE FUNCTION reraise_test() RETURNS void AS $$ 2442BEGIN 2443 BEGIN 2444 RAISE syntax_error; 2445 EXCEPTION 2446 WHEN syntax_error THEN 2447 BEGIN 2448 raise notice 'exception % thrown in inner block, reraising', sqlerrm; 2449 RAISE; 2450 EXCEPTION 2451 WHEN OTHERS THEN 2452 raise notice 'RIGHT - exception % caught in inner block', sqlerrm; 2453 END; 2454 END; 2455EXCEPTION 2456 WHEN OTHERS THEN 2457 raise notice 'WRONG - exception % caught in outer block', sqlerrm; 2458END; 2459$$ LANGUAGE plpgsql; 2460SELECT reraise_test(); 2461NOTICE: exception syntax_error thrown in inner block, reraising 2462NOTICE: RIGHT - exception syntax_error caught in inner block 2463 reraise_test 2464-------------- 2465 2466(1 row) 2467 2468-- 2469-- reject function definitions that contain malformed SQL queries at 2470-- compile-time, where possible 2471-- 2472create function bad_sql1() returns int as $$ 2473declare a int; 2474begin 2475 a := 5; 2476 Johnny Yuma; 2477 a := 10; 2478 return a; 2479end$$ language plpgsql; 2480ERROR: syntax error at or near "Johnny" 2481LINE 5: Johnny Yuma; 2482 ^ 2483create function bad_sql2() returns int as $$ 2484declare r record; 2485begin 2486 for r in select I fought the law, the law won LOOP 2487 raise notice 'in loop'; 2488 end loop; 2489 return 5; 2490end;$$ language plpgsql; 2491ERROR: syntax error at or near "the" 2492LINE 4: for r in select I fought the law, the law won LOOP 2493 ^ 2494-- a RETURN expression is mandatory, except for void-returning 2495-- functions, where it is not allowed 2496create function missing_return_expr() returns int as $$ 2497begin 2498 return ; 2499end;$$ language plpgsql; 2500ERROR: missing expression at or near ";" 2501LINE 3: return ; 2502 ^ 2503create function void_return_expr() returns void as $$ 2504begin 2505 return 5; 2506end;$$ language plpgsql; 2507ERROR: RETURN cannot have a parameter in function returning void 2508LINE 3: return 5; 2509 ^ 2510-- VOID functions are allowed to omit RETURN 2511create function void_return_expr() returns void as $$ 2512begin 2513 perform 2+2; 2514end;$$ language plpgsql; 2515select void_return_expr(); 2516 void_return_expr 2517------------------ 2518 2519(1 row) 2520 2521-- but ordinary functions are not 2522create function missing_return_expr() returns int as $$ 2523begin 2524 perform 2+2; 2525end;$$ language plpgsql; 2526select missing_return_expr(); 2527ERROR: control reached end of function without RETURN 2528CONTEXT: PL/pgSQL function missing_return_expr() 2529drop function void_return_expr(); 2530drop function missing_return_expr(); 2531-- 2532-- EXECUTE ... INTO test 2533-- 2534create table eifoo (i integer, y integer); 2535create type eitype as (i integer, y integer); 2536create or replace function execute_into_test(varchar) returns record as $$ 2537declare 2538 _r record; 2539 _rt eifoo%rowtype; 2540 _v eitype; 2541 i int; 2542 j int; 2543 k int; 2544begin 2545 execute 'insert into '||$1||' values(10,15)'; 2546 execute 'select (row).* from (select row(10,1)::eifoo) s' into _r; 2547 raise notice '% %', _r.i, _r.y; 2548 execute 'select * from '||$1||' limit 1' into _rt; 2549 raise notice '% %', _rt.i, _rt.y; 2550 execute 'select *, 20 from '||$1||' limit 1' into i, j, k; 2551 raise notice '% % %', i, j, k; 2552 execute 'select 1,2' into _v; 2553 return _v; 2554end; $$ language plpgsql; 2555select execute_into_test('eifoo'); 2556NOTICE: 10 1 2557NOTICE: 10 15 2558NOTICE: 10 15 20 2559 execute_into_test 2560------------------- 2561 (1,2) 2562(1 row) 2563 2564drop table eifoo cascade; 2565drop type eitype cascade; 2566-- 2567-- SQLSTATE and SQLERRM test 2568-- 2569create function excpt_test1() returns void as $$ 2570begin 2571 raise notice '% %', sqlstate, sqlerrm; 2572end; $$ language plpgsql; 2573-- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION 2574-- blocks 2575select excpt_test1(); 2576ERROR: column "sqlstate" does not exist 2577LINE 1: SELECT sqlstate 2578 ^ 2579QUERY: SELECT sqlstate 2580CONTEXT: PL/pgSQL function excpt_test1() line 3 at RAISE 2581create function excpt_test2() returns void as $$ 2582begin 2583 begin 2584 begin 2585 raise notice '% %', sqlstate, sqlerrm; 2586 end; 2587 end; 2588end; $$ language plpgsql; 2589-- should fail 2590select excpt_test2(); 2591ERROR: column "sqlstate" does not exist 2592LINE 1: SELECT sqlstate 2593 ^ 2594QUERY: SELECT sqlstate 2595CONTEXT: PL/pgSQL function excpt_test2() line 5 at RAISE 2596create function excpt_test3() returns void as $$ 2597begin 2598 begin 2599 raise exception 'user exception'; 2600 exception when others then 2601 raise notice 'caught exception % %', sqlstate, sqlerrm; 2602 begin 2603 raise notice '% %', sqlstate, sqlerrm; 2604 perform 10/0; 2605 exception 2606 when substring_error then 2607 -- this exception handler shouldn't be invoked 2608 raise notice 'unexpected exception: % %', sqlstate, sqlerrm; 2609 when division_by_zero then 2610 raise notice 'caught exception % %', sqlstate, sqlerrm; 2611 end; 2612 raise notice '% %', sqlstate, sqlerrm; 2613 end; 2614end; $$ language plpgsql; 2615select excpt_test3(); 2616NOTICE: caught exception P0001 user exception 2617NOTICE: P0001 user exception 2618NOTICE: caught exception 22012 division by zero 2619NOTICE: P0001 user exception 2620 excpt_test3 2621------------- 2622 2623(1 row) 2624 2625create function excpt_test4() returns text as $$ 2626begin 2627 begin perform 1/0; 2628 exception when others then return sqlerrm; end; 2629end; $$ language plpgsql; 2630select excpt_test4(); 2631 excpt_test4 2632------------------ 2633 division by zero 2634(1 row) 2635 2636drop function excpt_test1(); 2637drop function excpt_test2(); 2638drop function excpt_test3(); 2639drop function excpt_test4(); 2640-- parameters of raise stmt can be expressions 2641create function raise_exprs() returns void as $$ 2642declare 2643 a integer[] = '{10,20,30}'; 2644 c varchar = 'xyz'; 2645 i integer; 2646begin 2647 i := 2; 2648 raise notice '%; %; %; %; %; %', a, a[i], c, (select c || 'abc'), row(10,'aaa',NULL,30), NULL; 2649end;$$ language plpgsql; 2650select raise_exprs(); 2651NOTICE: {10,20,30}; 20; xyz; xyzabc; (10,aaa,,30); <NULL> 2652 raise_exprs 2653------------- 2654 2655(1 row) 2656 2657drop function raise_exprs(); 2658-- regression test: verify that multiple uses of same plpgsql datum within 2659-- a SQL command all get mapped to the same $n parameter. The return value 2660-- of the SELECT is not important, we only care that it doesn't fail with 2661-- a complaint about an ungrouped column reference. 2662create function multi_datum_use(p1 int) returns bool as $$ 2663declare 2664 x int; 2665 y int; 2666begin 2667 select into x,y unique1/p1, unique1/$1 from tenk1 group by unique1/p1; 2668 return x = y; 2669end$$ language plpgsql; 2670select multi_datum_use(42); 2671 multi_datum_use 2672----------------- 2673 t 2674(1 row) 2675 2676-- 2677-- Test STRICT limiter in both planned and EXECUTE invocations. 2678-- Note that a data-modifying query is quasi strict (disallow multi rows) 2679-- by default in the planned case, but not in EXECUTE. 2680-- 2681create temp table foo (f1 int, f2 int); 2682insert into foo values (1,2), (3,4); 2683create or replace function stricttest() returns void as $$ 2684declare x record; 2685begin 2686 -- should work 2687 insert into foo values(5,6) returning * into x; 2688 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2689end$$ language plpgsql; 2690select stricttest(); 2691NOTICE: x.f1 = 5, x.f2 = 6 2692 stricttest 2693------------ 2694 2695(1 row) 2696 2697create or replace function stricttest() returns void as $$ 2698declare x record; 2699begin 2700 -- should fail due to implicit strict 2701 insert into foo values(7,8),(9,10) returning * into x; 2702 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2703end$$ language plpgsql; 2704select stricttest(); 2705ERROR: query returned more than one row 2706HINT: Make sure the query returns a single row, or use LIMIT 1. 2707CONTEXT: PL/pgSQL function stricttest() line 5 at SQL statement 2708create or replace function stricttest() returns void as $$ 2709declare x record; 2710begin 2711 -- should work 2712 execute 'insert into foo values(5,6) returning *' into x; 2713 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2714end$$ language plpgsql; 2715select stricttest(); 2716NOTICE: x.f1 = 5, x.f2 = 6 2717 stricttest 2718------------ 2719 2720(1 row) 2721 2722create or replace function stricttest() returns void as $$ 2723declare x record; 2724begin 2725 -- this should work since EXECUTE isn't as picky 2726 execute 'insert into foo values(7,8),(9,10) returning *' into x; 2727 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2728end$$ language plpgsql; 2729select stricttest(); 2730NOTICE: x.f1 = 7, x.f2 = 8 2731 stricttest 2732------------ 2733 2734(1 row) 2735 2736select * from foo; 2737 f1 | f2 2738----+---- 2739 1 | 2 2740 3 | 4 2741 5 | 6 2742 5 | 6 2743 7 | 8 2744 9 | 10 2745(6 rows) 2746 2747create or replace function stricttest() returns void as $$ 2748declare x record; 2749begin 2750 -- should work 2751 select * from foo where f1 = 3 into strict x; 2752 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2753end$$ language plpgsql; 2754select stricttest(); 2755NOTICE: x.f1 = 3, x.f2 = 4 2756 stricttest 2757------------ 2758 2759(1 row) 2760 2761create or replace function stricttest() returns void as $$ 2762declare x record; 2763begin 2764 -- should fail, no rows 2765 select * from foo where f1 = 0 into strict x; 2766 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2767end$$ language plpgsql; 2768select stricttest(); 2769ERROR: query returned no rows 2770CONTEXT: PL/pgSQL function stricttest() line 5 at SQL statement 2771create or replace function stricttest() returns void as $$ 2772declare x record; 2773begin 2774 -- should fail, too many rows 2775 select * from foo where f1 > 3 into strict x; 2776 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2777end$$ language plpgsql; 2778select stricttest(); 2779ERROR: query returned more than one row 2780HINT: Make sure the query returns a single row, or use LIMIT 1. 2781CONTEXT: PL/pgSQL function stricttest() line 5 at SQL statement 2782create or replace function stricttest() returns void as $$ 2783declare x record; 2784begin 2785 -- should work 2786 execute 'select * from foo where f1 = 3' into strict x; 2787 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2788end$$ language plpgsql; 2789select stricttest(); 2790NOTICE: x.f1 = 3, x.f2 = 4 2791 stricttest 2792------------ 2793 2794(1 row) 2795 2796create or replace function stricttest() returns void as $$ 2797declare x record; 2798begin 2799 -- should fail, no rows 2800 execute 'select * from foo where f1 = 0' into strict x; 2801 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2802end$$ language plpgsql; 2803select stricttest(); 2804ERROR: query returned no rows 2805CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE 2806create or replace function stricttest() returns void as $$ 2807declare x record; 2808begin 2809 -- should fail, too many rows 2810 execute 'select * from foo where f1 > 3' into strict x; 2811 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2812end$$ language plpgsql; 2813select stricttest(); 2814ERROR: query returned more than one row 2815CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE 2816drop function stricttest(); 2817-- test printing parameters after failure due to STRICT 2818set plpgsql.print_strict_params to true; 2819create or replace function stricttest() returns void as $$ 2820declare 2821x record; 2822p1 int := 2; 2823p3 text := 'foo'; 2824begin 2825 -- no rows 2826 select * from foo where f1 = p1 and f1::text = p3 into strict x; 2827 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2828end$$ language plpgsql; 2829select stricttest(); 2830ERROR: query returned no rows 2831DETAIL: parameters: p1 = '2', p3 = 'foo' 2832CONTEXT: PL/pgSQL function stricttest() line 8 at SQL statement 2833create or replace function stricttest() returns void as $$ 2834declare 2835x record; 2836p1 int := 2; 2837p3 text := $a$'Valame Dios!' dijo Sancho; 'no le dije yo a vuestra merced que mirase bien lo que hacia?'$a$; 2838begin 2839 -- no rows 2840 select * from foo where f1 = p1 and f1::text = p3 into strict x; 2841 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2842end$$ language plpgsql; 2843select stricttest(); 2844ERROR: query returned no rows 2845DETAIL: parameters: p1 = '2', p3 = '''Valame Dios!'' dijo Sancho; ''no le dije yo a vuestra merced que mirase bien lo que hacia?''' 2846CONTEXT: PL/pgSQL function stricttest() line 8 at SQL statement 2847create or replace function stricttest() returns void as $$ 2848declare 2849x record; 2850p1 int := 2; 2851p3 text := 'foo'; 2852begin 2853 -- too many rows 2854 select * from foo where f1 > p1 or f1::text = p3 into strict x; 2855 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2856end$$ language plpgsql; 2857select stricttest(); 2858ERROR: query returned more than one row 2859DETAIL: parameters: p1 = '2', p3 = 'foo' 2860HINT: Make sure the query returns a single row, or use LIMIT 1. 2861CONTEXT: PL/pgSQL function stricttest() line 8 at SQL statement 2862create or replace function stricttest() returns void as $$ 2863declare x record; 2864begin 2865 -- too many rows, no params 2866 select * from foo where f1 > 3 into strict x; 2867 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2868end$$ language plpgsql; 2869select stricttest(); 2870ERROR: query returned more than one row 2871HINT: Make sure the query returns a single row, or use LIMIT 1. 2872CONTEXT: PL/pgSQL function stricttest() line 5 at SQL statement 2873create or replace function stricttest() returns void as $$ 2874declare x record; 2875begin 2876 -- no rows 2877 execute 'select * from foo where f1 = $1 or f1::text = $2' using 0, 'foo' into strict x; 2878 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2879end$$ language plpgsql; 2880select stricttest(); 2881ERROR: query returned no rows 2882DETAIL: parameters: $1 = '0', $2 = 'foo' 2883CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE 2884create or replace function stricttest() returns void as $$ 2885declare x record; 2886begin 2887 -- too many rows 2888 execute 'select * from foo where f1 > $1' using 1 into strict x; 2889 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2890end$$ language plpgsql; 2891select stricttest(); 2892ERROR: query returned more than one row 2893DETAIL: parameters: $1 = '1' 2894CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE 2895create or replace function stricttest() returns void as $$ 2896declare x record; 2897begin 2898 -- too many rows, no parameters 2899 execute 'select * from foo where f1 > 3' into strict x; 2900 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2901end$$ language plpgsql; 2902select stricttest(); 2903ERROR: query returned more than one row 2904CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE 2905create or replace function stricttest() returns void as $$ 2906-- override the global 2907#print_strict_params off 2908declare 2909x record; 2910p1 int := 2; 2911p3 text := 'foo'; 2912begin 2913 -- too many rows 2914 select * from foo where f1 > p1 or f1::text = p3 into strict x; 2915 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2916end$$ language plpgsql; 2917select stricttest(); 2918ERROR: query returned more than one row 2919HINT: Make sure the query returns a single row, or use LIMIT 1. 2920CONTEXT: PL/pgSQL function stricttest() line 10 at SQL statement 2921reset plpgsql.print_strict_params; 2922create or replace function stricttest() returns void as $$ 2923-- override the global 2924#print_strict_params on 2925declare 2926x record; 2927p1 int := 2; 2928p3 text := 'foo'; 2929begin 2930 -- too many rows 2931 select * from foo where f1 > p1 or f1::text = p3 into strict x; 2932 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; 2933end$$ language plpgsql; 2934select stricttest(); 2935ERROR: query returned more than one row 2936DETAIL: parameters: p1 = '2', p3 = 'foo' 2937HINT: Make sure the query returns a single row, or use LIMIT 1. 2938CONTEXT: PL/pgSQL function stricttest() line 10 at SQL statement 2939-- test warnings and errors 2940set plpgsql.extra_warnings to 'all'; 2941set plpgsql.extra_warnings to 'none'; 2942set plpgsql.extra_errors to 'all'; 2943set plpgsql.extra_errors to 'none'; 2944-- test warnings when shadowing a variable 2945set plpgsql.extra_warnings to 'shadowed_variables'; 2946-- simple shadowing of input and output parameters 2947create or replace function shadowtest(in1 int) 2948 returns table (out1 int) as $$ 2949declare 2950in1 int; 2951out1 int; 2952begin 2953end 2954$$ language plpgsql; 2955WARNING: variable "in1" shadows a previously defined variable 2956LINE 4: in1 int; 2957 ^ 2958WARNING: variable "out1" shadows a previously defined variable 2959LINE 5: out1 int; 2960 ^ 2961select shadowtest(1); 2962 shadowtest 2963------------ 2964(0 rows) 2965 2966set plpgsql.extra_warnings to 'shadowed_variables'; 2967select shadowtest(1); 2968 shadowtest 2969------------ 2970(0 rows) 2971 2972create or replace function shadowtest(in1 int) 2973 returns table (out1 int) as $$ 2974declare 2975in1 int; 2976out1 int; 2977begin 2978end 2979$$ language plpgsql; 2980WARNING: variable "in1" shadows a previously defined variable 2981LINE 4: in1 int; 2982 ^ 2983WARNING: variable "out1" shadows a previously defined variable 2984LINE 5: out1 int; 2985 ^ 2986select shadowtest(1); 2987 shadowtest 2988------------ 2989(0 rows) 2990 2991drop function shadowtest(int); 2992-- shadowing in a second DECLARE block 2993create or replace function shadowtest() 2994 returns void as $$ 2995declare 2996f1 int; 2997begin 2998 declare 2999 f1 int; 3000 begin 3001 end; 3002end$$ language plpgsql; 3003WARNING: variable "f1" shadows a previously defined variable 3004LINE 7: f1 int; 3005 ^ 3006drop function shadowtest(); 3007-- several levels of shadowing 3008create or replace function shadowtest(in1 int) 3009 returns void as $$ 3010declare 3011in1 int; 3012begin 3013 declare 3014 in1 int; 3015 begin 3016 end; 3017end$$ language plpgsql; 3018WARNING: variable "in1" shadows a previously defined variable 3019LINE 4: in1 int; 3020 ^ 3021WARNING: variable "in1" shadows a previously defined variable 3022LINE 7: in1 int; 3023 ^ 3024drop function shadowtest(int); 3025-- shadowing in cursor definitions 3026create or replace function shadowtest() 3027 returns void as $$ 3028declare 3029f1 int; 3030c1 cursor (f1 int) for select 1; 3031begin 3032end$$ language plpgsql; 3033WARNING: variable "f1" shadows a previously defined variable 3034LINE 5: c1 cursor (f1 int) for select 1; 3035 ^ 3036drop function shadowtest(); 3037-- test errors when shadowing a variable 3038set plpgsql.extra_errors to 'shadowed_variables'; 3039create or replace function shadowtest(f1 int) 3040 returns boolean as $$ 3041declare f1 int; begin return 1; end $$ language plpgsql; 3042ERROR: variable "f1" shadows a previously defined variable 3043LINE 3: declare f1 int; begin return 1; end $$ language plpgsql; 3044 ^ 3045select shadowtest(1); 3046ERROR: function shadowtest(integer) does not exist 3047LINE 1: select shadowtest(1); 3048 ^ 3049HINT: No function matches the given name and argument types. You might need to add explicit type casts. 3050reset plpgsql.extra_errors; 3051reset plpgsql.extra_warnings; 3052create or replace function shadowtest(f1 int) 3053 returns boolean as $$ 3054declare f1 int; begin return 1; end $$ language plpgsql; 3055select shadowtest(1); 3056 shadowtest 3057------------ 3058 t 3059(1 row) 3060 3061-- runtime extra checks 3062set plpgsql.extra_warnings to 'too_many_rows'; 3063do $$ 3064declare x int; 3065begin 3066 select v from generate_series(1,2) g(v) into x; 3067end; 3068$$; 3069WARNING: query returned more than one row 3070HINT: Make sure the query returns a single row, or use LIMIT 1. 3071set plpgsql.extra_errors to 'too_many_rows'; 3072do $$ 3073declare x int; 3074begin 3075 select v from generate_series(1,2) g(v) into x; 3076end; 3077$$; 3078ERROR: query returned more than one row 3079HINT: Make sure the query returns a single row, or use LIMIT 1. 3080CONTEXT: PL/pgSQL function inline_code_block line 4 at SQL statement 3081reset plpgsql.extra_errors; 3082reset plpgsql.extra_warnings; 3083set plpgsql.extra_warnings to 'strict_multi_assignment'; 3084do $$ 3085declare 3086 x int; 3087 y int; 3088begin 3089 select 1 into x, y; 3090 select 1,2 into x, y; 3091 select 1,2,3 into x, y; 3092end 3093$$; 3094WARNING: number of source and target fields in assignment does not match 3095DETAIL: strict_multi_assignment check of extra_warnings is active. 3096HINT: Make sure the query returns the exact list of columns. 3097WARNING: number of source and target fields in assignment does not match 3098DETAIL: strict_multi_assignment check of extra_warnings is active. 3099HINT: Make sure the query returns the exact list of columns. 3100set plpgsql.extra_errors to 'strict_multi_assignment'; 3101do $$ 3102declare 3103 x int; 3104 y int; 3105begin 3106 select 1 into x, y; 3107 select 1,2 into x, y; 3108 select 1,2,3 into x, y; 3109end 3110$$; 3111ERROR: number of source and target fields in assignment does not match 3112DETAIL: strict_multi_assignment check of extra_errors is active. 3113HINT: Make sure the query returns the exact list of columns. 3114CONTEXT: PL/pgSQL function inline_code_block line 6 at SQL statement 3115create table test_01(a int, b int, c int); 3116alter table test_01 drop column a; 3117-- the check is active only when source table is not empty 3118insert into test_01 values(10,20); 3119do $$ 3120declare 3121 x int; 3122 y int; 3123begin 3124 select * from test_01 into x, y; -- should be ok 3125 raise notice 'ok'; 3126 select * from test_01 into x; -- should to fail 3127end; 3128$$; 3129NOTICE: ok 3130ERROR: number of source and target fields in assignment does not match 3131DETAIL: strict_multi_assignment check of extra_errors is active. 3132HINT: Make sure the query returns the exact list of columns. 3133CONTEXT: PL/pgSQL function inline_code_block line 8 at SQL statement 3134do $$ 3135declare 3136 t test_01; 3137begin 3138 select 1, 2 into t; -- should be ok 3139 raise notice 'ok'; 3140 select 1, 2, 3 into t; -- should fail; 3141end; 3142$$; 3143NOTICE: ok 3144ERROR: number of source and target fields in assignment does not match 3145DETAIL: strict_multi_assignment check of extra_errors is active. 3146HINT: Make sure the query returns the exact list of columns. 3147CONTEXT: PL/pgSQL function inline_code_block line 7 at SQL statement 3148do $$ 3149declare 3150 t test_01; 3151begin 3152 select 1 into t; -- should fail; 3153end; 3154$$; 3155ERROR: number of source and target fields in assignment does not match 3156DETAIL: strict_multi_assignment check of extra_errors is active. 3157HINT: Make sure the query returns the exact list of columns. 3158CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement 3159drop table test_01; 3160reset plpgsql.extra_errors; 3161reset plpgsql.extra_warnings; 3162-- test scrollable cursor support 3163create function sc_test() returns setof integer as $$ 3164declare 3165 c scroll cursor for select f1 from int4_tbl; 3166 x integer; 3167begin 3168 open c; 3169 fetch last from c into x; 3170 while found loop 3171 return next x; 3172 fetch prior from c into x; 3173 end loop; 3174 close c; 3175end; 3176$$ language plpgsql; 3177select * from sc_test(); 3178 sc_test 3179------------- 3180 -2147483647 3181 2147483647 3182 -123456 3183 123456 3184 0 3185(5 rows) 3186 3187create or replace function sc_test() returns setof integer as $$ 3188declare 3189 c no scroll cursor for select f1 from int4_tbl; 3190 x integer; 3191begin 3192 open c; 3193 fetch last from c into x; 3194 while found loop 3195 return next x; 3196 fetch prior from c into x; 3197 end loop; 3198 close c; 3199end; 3200$$ language plpgsql; 3201select * from sc_test(); -- fails because of NO SCROLL specification 3202ERROR: cursor can only scan forward 3203HINT: Declare it with SCROLL option to enable backward scan. 3204CONTEXT: PL/pgSQL function sc_test() line 7 at FETCH 3205create or replace function sc_test() returns setof integer as $$ 3206declare 3207 c refcursor; 3208 x integer; 3209begin 3210 open c scroll for select f1 from int4_tbl; 3211 fetch last from c into x; 3212 while found loop 3213 return next x; 3214 fetch prior from c into x; 3215 end loop; 3216 close c; 3217end; 3218$$ language plpgsql; 3219select * from sc_test(); 3220 sc_test 3221------------- 3222 -2147483647 3223 2147483647 3224 -123456 3225 123456 3226 0 3227(5 rows) 3228 3229create or replace function sc_test() returns setof integer as $$ 3230declare 3231 c refcursor; 3232 x integer; 3233begin 3234 open c scroll for execute 'select f1 from int4_tbl'; 3235 fetch last from c into x; 3236 while found loop 3237 return next x; 3238 fetch relative -2 from c into x; 3239 end loop; 3240 close c; 3241end; 3242$$ language plpgsql; 3243select * from sc_test(); 3244 sc_test 3245------------- 3246 -2147483647 3247 -123456 3248 0 3249(3 rows) 3250 3251create or replace function sc_test() returns setof integer as $$ 3252declare 3253 c refcursor; 3254 x integer; 3255begin 3256 open c scroll for execute 'select f1 from int4_tbl'; 3257 fetch last from c into x; 3258 while found loop 3259 return next x; 3260 move backward 2 from c; 3261 fetch relative -1 from c into x; 3262 end loop; 3263 close c; 3264end; 3265$$ language plpgsql; 3266select * from sc_test(); 3267 sc_test 3268------------- 3269 -2147483647 3270 123456 3271(2 rows) 3272 3273create or replace function sc_test() returns setof integer as $$ 3274declare 3275 c cursor for select * from generate_series(1, 10); 3276 x integer; 3277begin 3278 open c; 3279 loop 3280 move relative 2 in c; 3281 if not found then 3282 exit; 3283 end if; 3284 fetch next from c into x; 3285 if found then 3286 return next x; 3287 end if; 3288 end loop; 3289 close c; 3290end; 3291$$ language plpgsql; 3292select * from sc_test(); 3293 sc_test 3294--------- 3295 3 3296 6 3297 9 3298(3 rows) 3299 3300create or replace function sc_test() returns setof integer as $$ 3301declare 3302 c cursor for select * from generate_series(1, 10); 3303 x integer; 3304begin 3305 open c; 3306 move forward all in c; 3307 fetch backward from c into x; 3308 if found then 3309 return next x; 3310 end if; 3311 close c; 3312end; 3313$$ language plpgsql; 3314select * from sc_test(); 3315 sc_test 3316--------- 3317 10 3318(1 row) 3319 3320drop function sc_test(); 3321-- test qualified variable names 3322create function pl_qual_names (param1 int) returns void as $$ 3323<<outerblock>> 3324declare 3325 param1 int := 1; 3326begin 3327 <<innerblock>> 3328 declare 3329 param1 int := 2; 3330 begin 3331 raise notice 'param1 = %', param1; 3332 raise notice 'pl_qual_names.param1 = %', pl_qual_names.param1; 3333 raise notice 'outerblock.param1 = %', outerblock.param1; 3334 raise notice 'innerblock.param1 = %', innerblock.param1; 3335 end; 3336end; 3337$$ language plpgsql; 3338select pl_qual_names(42); 3339NOTICE: param1 = 2 3340NOTICE: pl_qual_names.param1 = 42 3341NOTICE: outerblock.param1 = 1 3342NOTICE: innerblock.param1 = 2 3343 pl_qual_names 3344--------------- 3345 3346(1 row) 3347 3348drop function pl_qual_names(int); 3349-- tests for RETURN QUERY 3350create function ret_query1(out int, out int) returns setof record as $$ 3351begin 3352 $1 := -1; 3353 $2 := -2; 3354 return next; 3355 return query select x + 1, x * 10 from generate_series(0, 10) s (x); 3356 return next; 3357end; 3358$$ language plpgsql; 3359select * from ret_query1(); 3360 column1 | column2 3361---------+--------- 3362 -1 | -2 3363 1 | 0 3364 2 | 10 3365 3 | 20 3366 4 | 30 3367 5 | 40 3368 6 | 50 3369 7 | 60 3370 8 | 70 3371 9 | 80 3372 10 | 90 3373 11 | 100 3374 -1 | -2 3375(13 rows) 3376 3377create type record_type as (x text, y int, z boolean); 3378create or replace function ret_query2(lim int) returns setof record_type as $$ 3379begin 3380 return query select md5(s.x::text), s.x, s.x > 0 3381 from generate_series(-8, lim) s (x) where s.x % 2 = 0; 3382end; 3383$$ language plpgsql; 3384select * from ret_query2(8); 3385 x | y | z 3386----------------------------------+----+--- 3387 a8d2ec85eaf98407310b72eb73dda247 | -8 | f 3388 596a3d04481816330f07e4f97510c28f | -6 | f 3389 0267aaf632e87a63288a08331f22c7c3 | -4 | f 3390 5d7b9adcbe1c629ec722529dd12e5129 | -2 | f 3391 cfcd208495d565ef66e7dff9f98764da | 0 | f 3392 c81e728d9d4c2f636f067f89cc14862c | 2 | t 3393 a87ff679a2f3e71d9181a67b7542122c | 4 | t 3394 1679091c5a880faf6fb5e6087eb1b2dc | 6 | t 3395 c9f0f895fb98ab9159f51fd0297e236d | 8 | t 3396(9 rows) 3397 3398-- test EXECUTE USING 3399create function exc_using(int, text) returns int as $$ 3400declare i int; 3401begin 3402 for i in execute 'select * from generate_series(1,$1)' using $1+1 loop 3403 raise notice '%', i; 3404 end loop; 3405 execute 'select $2 + $2*3 + length($1)' into i using $2,$1; 3406 return i; 3407end 3408$$ language plpgsql; 3409select exc_using(5, 'foobar'); 3410NOTICE: 1 3411NOTICE: 2 3412NOTICE: 3 3413NOTICE: 4 3414NOTICE: 5 3415NOTICE: 6 3416 exc_using 3417----------- 3418 26 3419(1 row) 3420 3421drop function exc_using(int, text); 3422create or replace function exc_using(int) returns void as $$ 3423declare 3424 c refcursor; 3425 i int; 3426begin 3427 open c for execute 'select * from generate_series(1,$1)' using $1+1; 3428 loop 3429 fetch c into i; 3430 exit when not found; 3431 raise notice '%', i; 3432 end loop; 3433 close c; 3434 return; 3435end; 3436$$ language plpgsql; 3437select exc_using(5); 3438NOTICE: 1 3439NOTICE: 2 3440NOTICE: 3 3441NOTICE: 4 3442NOTICE: 5 3443NOTICE: 6 3444 exc_using 3445----------- 3446 3447(1 row) 3448 3449drop function exc_using(int); 3450-- test FOR-over-cursor 3451create or replace function forc01() returns void as $$ 3452declare 3453 c cursor(r1 integer, r2 integer) 3454 for select * from generate_series(r1,r2) i; 3455 c2 cursor 3456 for select * from generate_series(41,43) i; 3457begin 3458 for r in c(5,7) loop 3459 raise notice '% from %', r.i, c; 3460 end loop; 3461 -- again, to test if cursor was closed properly 3462 for r in c(9,10) loop 3463 raise notice '% from %', r.i, c; 3464 end loop; 3465 -- and test a parameterless cursor 3466 for r in c2 loop 3467 raise notice '% from %', r.i, c2; 3468 end loop; 3469 -- and try it with a hand-assigned name 3470 raise notice 'after loop, c2 = %', c2; 3471 c2 := 'special_name'; 3472 for r in c2 loop 3473 raise notice '% from %', r.i, c2; 3474 end loop; 3475 raise notice 'after loop, c2 = %', c2; 3476 -- and try it with a generated name 3477 -- (which we can't show in the output because it's variable) 3478 c2 := null; 3479 for r in c2 loop 3480 raise notice '%', r.i; 3481 end loop; 3482 raise notice 'after loop, c2 = %', c2; 3483 return; 3484end; 3485$$ language plpgsql; 3486select forc01(); 3487NOTICE: 5 from c 3488NOTICE: 6 from c 3489NOTICE: 7 from c 3490NOTICE: 9 from c 3491NOTICE: 10 from c 3492NOTICE: 41 from c2 3493NOTICE: 42 from c2 3494NOTICE: 43 from c2 3495NOTICE: after loop, c2 = c2 3496NOTICE: 41 from special_name 3497NOTICE: 42 from special_name 3498NOTICE: 43 from special_name 3499NOTICE: after loop, c2 = special_name 3500NOTICE: 41 3501NOTICE: 42 3502NOTICE: 43 3503NOTICE: after loop, c2 = <NULL> 3504 forc01 3505-------- 3506 3507(1 row) 3508 3509-- try updating the cursor's current row 3510create temp table forc_test as 3511 select n as i, n as j from generate_series(1,10) n; 3512create or replace function forc01() returns void as $$ 3513declare 3514 c cursor for select * from forc_test; 3515begin 3516 for r in c loop 3517 raise notice '%, %', r.i, r.j; 3518 update forc_test set i = i * 100, j = r.j * 2 where current of c; 3519 end loop; 3520end; 3521$$ language plpgsql; 3522select forc01(); 3523NOTICE: 1, 1 3524NOTICE: 2, 2 3525NOTICE: 3, 3 3526NOTICE: 4, 4 3527NOTICE: 5, 5 3528NOTICE: 6, 6 3529NOTICE: 7, 7 3530NOTICE: 8, 8 3531NOTICE: 9, 9 3532NOTICE: 10, 10 3533 forc01 3534-------- 3535 3536(1 row) 3537 3538select * from forc_test; 3539 i | j 3540------+---- 3541 100 | 2 3542 200 | 4 3543 300 | 6 3544 400 | 8 3545 500 | 10 3546 600 | 12 3547 700 | 14 3548 800 | 16 3549 900 | 18 3550 1000 | 20 3551(10 rows) 3552 3553-- same, with a cursor whose portal name doesn't match variable name 3554create or replace function forc01() returns void as $$ 3555declare 3556 c refcursor := 'fooled_ya'; 3557 r record; 3558begin 3559 open c for select * from forc_test; 3560 loop 3561 fetch c into r; 3562 exit when not found; 3563 raise notice '%, %', r.i, r.j; 3564 update forc_test set i = i * 100, j = r.j * 2 where current of c; 3565 end loop; 3566end; 3567$$ language plpgsql; 3568select forc01(); 3569NOTICE: 100, 2 3570NOTICE: 200, 4 3571NOTICE: 300, 6 3572NOTICE: 400, 8 3573NOTICE: 500, 10 3574NOTICE: 600, 12 3575NOTICE: 700, 14 3576NOTICE: 800, 16 3577NOTICE: 900, 18 3578NOTICE: 1000, 20 3579 forc01 3580-------- 3581 3582(1 row) 3583 3584select * from forc_test; 3585 i | j 3586--------+---- 3587 10000 | 4 3588 20000 | 8 3589 30000 | 12 3590 40000 | 16 3591 50000 | 20 3592 60000 | 24 3593 70000 | 28 3594 80000 | 32 3595 90000 | 36 3596 100000 | 40 3597(10 rows) 3598 3599drop function forc01(); 3600-- fail because cursor has no query bound to it 3601create or replace function forc_bad() returns void as $$ 3602declare 3603 c refcursor; 3604begin 3605 for r in c loop 3606 raise notice '%', r.i; 3607 end loop; 3608end; 3609$$ language plpgsql; 3610ERROR: cursor FOR loop must use a bound cursor variable 3611LINE 5: for r in c loop 3612 ^ 3613-- test RETURN QUERY EXECUTE 3614create or replace function return_dquery() 3615returns setof int as $$ 3616begin 3617 return query execute 'select * from (values(10),(20)) f'; 3618 return query execute 'select * from (values($1),($2)) f' using 40,50; 3619end; 3620$$ language plpgsql; 3621select * from return_dquery(); 3622 return_dquery 3623--------------- 3624 10 3625 20 3626 40 3627 50 3628(4 rows) 3629 3630drop function return_dquery(); 3631-- test RETURN QUERY with dropped columns 3632create table tabwithcols(a int, b int, c int, d int); 3633insert into tabwithcols values(10,20,30,40),(50,60,70,80); 3634create or replace function returnqueryf() 3635returns setof tabwithcols as $$ 3636begin 3637 return query select * from tabwithcols; 3638 return query execute 'select * from tabwithcols'; 3639end; 3640$$ language plpgsql; 3641select * from returnqueryf(); 3642 a | b | c | d 3643----+----+----+---- 3644 10 | 20 | 30 | 40 3645 50 | 60 | 70 | 80 3646 10 | 20 | 30 | 40 3647 50 | 60 | 70 | 80 3648(4 rows) 3649 3650alter table tabwithcols drop column b; 3651select * from returnqueryf(); 3652 a | c | d 3653----+----+---- 3654 10 | 30 | 40 3655 50 | 70 | 80 3656 10 | 30 | 40 3657 50 | 70 | 80 3658(4 rows) 3659 3660alter table tabwithcols drop column d; 3661select * from returnqueryf(); 3662 a | c 3663----+---- 3664 10 | 30 3665 50 | 70 3666 10 | 30 3667 50 | 70 3668(4 rows) 3669 3670alter table tabwithcols add column d int; 3671select * from returnqueryf(); 3672 a | c | d 3673----+----+--- 3674 10 | 30 | 3675 50 | 70 | 3676 10 | 30 | 3677 50 | 70 | 3678(4 rows) 3679 3680drop function returnqueryf(); 3681drop table tabwithcols; 3682-- 3683-- Tests for composite-type results 3684-- 3685create type compostype as (x int, y varchar); 3686-- test: use of variable of composite type in return statement 3687create or replace function compos() returns compostype as $$ 3688declare 3689 v compostype; 3690begin 3691 v := (1, 'hello'); 3692 return v; 3693end; 3694$$ language plpgsql; 3695select compos(); 3696 compos 3697----------- 3698 (1,hello) 3699(1 row) 3700 3701-- test: use of variable of record type in return statement 3702create or replace function compos() returns compostype as $$ 3703declare 3704 v record; 3705begin 3706 v := (1, 'hello'::varchar); 3707 return v; 3708end; 3709$$ language plpgsql; 3710select compos(); 3711 compos 3712----------- 3713 (1,hello) 3714(1 row) 3715 3716-- test: use of row expr in return statement 3717create or replace function compos() returns compostype as $$ 3718begin 3719 return (1, 'hello'::varchar); 3720end; 3721$$ language plpgsql; 3722select compos(); 3723 compos 3724----------- 3725 (1,hello) 3726(1 row) 3727 3728-- this does not work currently (no implicit casting) 3729create or replace function compos() returns compostype as $$ 3730begin 3731 return (1, 'hello'); 3732end; 3733$$ language plpgsql; 3734select compos(); 3735ERROR: returned record type does not match expected record type 3736DETAIL: Returned type unknown does not match expected type character varying in column 2. 3737CONTEXT: PL/pgSQL function compos() while casting return value to function's return type 3738-- ... but this does 3739create or replace function compos() returns compostype as $$ 3740begin 3741 return (1, 'hello')::compostype; 3742end; 3743$$ language plpgsql; 3744select compos(); 3745 compos 3746----------- 3747 (1,hello) 3748(1 row) 3749 3750drop function compos(); 3751-- test: return a row expr as record. 3752create or replace function composrec() returns record as $$ 3753declare 3754 v record; 3755begin 3756 v := (1, 'hello'); 3757 return v; 3758end; 3759$$ language plpgsql; 3760select composrec(); 3761 composrec 3762----------- 3763 (1,hello) 3764(1 row) 3765 3766-- test: return row expr in return statement. 3767create or replace function composrec() returns record as $$ 3768begin 3769 return (1, 'hello'); 3770end; 3771$$ language plpgsql; 3772select composrec(); 3773 composrec 3774----------- 3775 (1,hello) 3776(1 row) 3777 3778drop function composrec(); 3779-- test: row expr in RETURN NEXT statement. 3780create or replace function compos() returns setof compostype as $$ 3781begin 3782 for i in 1..3 3783 loop 3784 return next (1, 'hello'::varchar); 3785 end loop; 3786 return next null::compostype; 3787 return next (2, 'goodbye')::compostype; 3788end; 3789$$ language plpgsql; 3790select * from compos(); 3791 x | y 3792---+--------- 3793 1 | hello 3794 1 | hello 3795 1 | hello 3796 | 3797 2 | goodbye 3798(5 rows) 3799 3800drop function compos(); 3801-- test: use invalid expr in return statement. 3802create or replace function compos() returns compostype as $$ 3803begin 3804 return 1 + 1; 3805end; 3806$$ language plpgsql; 3807select compos(); 3808ERROR: cannot return non-composite value from function returning composite type 3809CONTEXT: PL/pgSQL function compos() line 3 at RETURN 3810-- RETURN variable is a different code path ... 3811create or replace function compos() returns compostype as $$ 3812declare x int := 42; 3813begin 3814 return x; 3815end; 3816$$ language plpgsql; 3817select * from compos(); 3818ERROR: cannot return non-composite value from function returning composite type 3819CONTEXT: PL/pgSQL function compos() line 4 at RETURN 3820drop function compos(); 3821-- test: invalid use of composite variable in scalar-returning function 3822create or replace function compos() returns int as $$ 3823declare 3824 v compostype; 3825begin 3826 v := (1, 'hello'); 3827 return v; 3828end; 3829$$ language plpgsql; 3830select compos(); 3831ERROR: invalid input syntax for type integer: "(1,hello)" 3832CONTEXT: PL/pgSQL function compos() while casting return value to function's return type 3833-- test: invalid use of composite expression in scalar-returning function 3834create or replace function compos() returns int as $$ 3835begin 3836 return (1, 'hello')::compostype; 3837end; 3838$$ language plpgsql; 3839select compos(); 3840ERROR: invalid input syntax for type integer: "(1,hello)" 3841CONTEXT: PL/pgSQL function compos() while casting return value to function's return type 3842drop function compos(); 3843drop type compostype; 3844-- 3845-- Tests for 8.4's new RAISE features 3846-- 3847create or replace function raise_test() returns void as $$ 3848begin 3849 raise notice '% % %', 1, 2, 3 3850 using errcode = '55001', detail = 'some detail info', hint = 'some hint'; 3851 raise '% % %', 1, 2, 3 3852 using errcode = 'division_by_zero', detail = 'some detail info'; 3853end; 3854$$ language plpgsql; 3855select raise_test(); 3856NOTICE: 1 2 3 3857DETAIL: some detail info 3858HINT: some hint 3859ERROR: 1 2 3 3860DETAIL: some detail info 3861CONTEXT: PL/pgSQL function raise_test() line 5 at RAISE 3862-- Since we can't actually see the thrown SQLSTATE in default psql output, 3863-- test it like this; this also tests re-RAISE 3864create or replace function raise_test() returns void as $$ 3865begin 3866 raise 'check me' 3867 using errcode = 'division_by_zero', detail = 'some detail info'; 3868 exception 3869 when others then 3870 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; 3871 raise; 3872end; 3873$$ language plpgsql; 3874select raise_test(); 3875NOTICE: SQLSTATE: 22012 SQLERRM: check me 3876ERROR: check me 3877DETAIL: some detail info 3878CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE 3879create or replace function raise_test() returns void as $$ 3880begin 3881 raise 'check me' 3882 using errcode = '1234F', detail = 'some detail info'; 3883 exception 3884 when others then 3885 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; 3886 raise; 3887end; 3888$$ language plpgsql; 3889select raise_test(); 3890NOTICE: SQLSTATE: 1234F SQLERRM: check me 3891ERROR: check me 3892DETAIL: some detail info 3893CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE 3894-- SQLSTATE specification in WHEN 3895create or replace function raise_test() returns void as $$ 3896begin 3897 raise 'check me' 3898 using errcode = '1234F', detail = 'some detail info'; 3899 exception 3900 when sqlstate '1234F' then 3901 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; 3902 raise; 3903end; 3904$$ language plpgsql; 3905select raise_test(); 3906NOTICE: SQLSTATE: 1234F SQLERRM: check me 3907ERROR: check me 3908DETAIL: some detail info 3909CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE 3910create or replace function raise_test() returns void as $$ 3911begin 3912 raise division_by_zero using detail = 'some detail info'; 3913 exception 3914 when others then 3915 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; 3916 raise; 3917end; 3918$$ language plpgsql; 3919select raise_test(); 3920NOTICE: SQLSTATE: 22012 SQLERRM: division_by_zero 3921ERROR: division_by_zero 3922DETAIL: some detail info 3923CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE 3924create or replace function raise_test() returns void as $$ 3925begin 3926 raise division_by_zero; 3927end; 3928$$ language plpgsql; 3929select raise_test(); 3930ERROR: division_by_zero 3931CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE 3932create or replace function raise_test() returns void as $$ 3933begin 3934 raise sqlstate '1234F'; 3935end; 3936$$ language plpgsql; 3937select raise_test(); 3938ERROR: 1234F 3939CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE 3940create or replace function raise_test() returns void as $$ 3941begin 3942 raise division_by_zero using message = 'custom' || ' message'; 3943end; 3944$$ language plpgsql; 3945select raise_test(); 3946ERROR: custom message 3947CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE 3948create or replace function raise_test() returns void as $$ 3949begin 3950 raise using message = 'custom' || ' message', errcode = '22012'; 3951end; 3952$$ language plpgsql; 3953select raise_test(); 3954ERROR: custom message 3955CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE 3956-- conflict on message 3957create or replace function raise_test() returns void as $$ 3958begin 3959 raise notice 'some message' using message = 'custom' || ' message', errcode = '22012'; 3960end; 3961$$ language plpgsql; 3962select raise_test(); 3963ERROR: RAISE option already specified: MESSAGE 3964CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE 3965-- conflict on errcode 3966create or replace function raise_test() returns void as $$ 3967begin 3968 raise division_by_zero using message = 'custom' || ' message', errcode = '22012'; 3969end; 3970$$ language plpgsql; 3971select raise_test(); 3972ERROR: RAISE option already specified: ERRCODE 3973CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE 3974-- nothing to re-RAISE 3975create or replace function raise_test() returns void as $$ 3976begin 3977 raise; 3978end; 3979$$ language plpgsql; 3980select raise_test(); 3981ERROR: RAISE without parameters cannot be used outside an exception handler 3982CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE 3983-- test access to exception data 3984create function zero_divide() returns int as $$ 3985declare v int := 0; 3986begin 3987 return 10 / v; 3988end; 3989$$ language plpgsql; 3990create or replace function raise_test() returns void as $$ 3991begin 3992 raise exception 'custom exception' 3993 using detail = 'some detail of custom exception', 3994 hint = 'some hint related to custom exception'; 3995end; 3996$$ language plpgsql; 3997create function stacked_diagnostics_test() returns void as $$ 3998declare _sqlstate text; 3999 _message text; 4000 _context text; 4001begin 4002 perform zero_divide(); 4003exception when others then 4004 get stacked diagnostics 4005 _sqlstate = returned_sqlstate, 4006 _message = message_text, 4007 _context = pg_exception_context; 4008 raise notice 'sqlstate: %, message: %, context: [%]', 4009 _sqlstate, _message, replace(_context, E'\n', ' <- '); 4010end; 4011$$ language plpgsql; 4012select stacked_diagnostics_test(); 4013NOTICE: 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] 4014 stacked_diagnostics_test 4015-------------------------- 4016 4017(1 row) 4018 4019create or replace function stacked_diagnostics_test() returns void as $$ 4020declare _detail text; 4021 _hint text; 4022 _message text; 4023begin 4024 perform raise_test(); 4025exception when others then 4026 get stacked diagnostics 4027 _message = message_text, 4028 _detail = pg_exception_detail, 4029 _hint = pg_exception_hint; 4030 raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint; 4031end; 4032$$ language plpgsql; 4033select stacked_diagnostics_test(); 4034NOTICE: message: custom exception, detail: some detail of custom exception, hint: some hint related to custom exception 4035 stacked_diagnostics_test 4036-------------------------- 4037 4038(1 row) 4039 4040-- fail, cannot use stacked diagnostics statement outside handler 4041create or replace function stacked_diagnostics_test() returns void as $$ 4042declare _detail text; 4043 _hint text; 4044 _message text; 4045begin 4046 get stacked diagnostics 4047 _message = message_text, 4048 _detail = pg_exception_detail, 4049 _hint = pg_exception_hint; 4050 raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint; 4051end; 4052$$ language plpgsql; 4053select stacked_diagnostics_test(); 4054ERROR: GET STACKED DIAGNOSTICS cannot be used outside an exception handler 4055CONTEXT: PL/pgSQL function stacked_diagnostics_test() line 6 at GET STACKED DIAGNOSTICS 4056drop function zero_divide(); 4057drop function stacked_diagnostics_test(); 4058-- check cases where implicit SQLSTATE variable could be confused with 4059-- SQLSTATE as a keyword, cf bug #5524 4060create or replace function raise_test() returns void as $$ 4061begin 4062 perform 1/0; 4063exception 4064 when sqlstate '22012' then 4065 raise notice using message = sqlstate; 4066 raise sqlstate '22012' using message = 'substitute message'; 4067end; 4068$$ language plpgsql; 4069select raise_test(); 4070NOTICE: 22012 4071ERROR: substitute message 4072CONTEXT: PL/pgSQL function raise_test() line 7 at RAISE 4073drop function raise_test(); 4074-- test passing column_name, constraint_name, datatype_name, table_name 4075-- and schema_name error fields 4076create or replace function stacked_diagnostics_test() returns void as $$ 4077declare _column_name text; 4078 _constraint_name text; 4079 _datatype_name text; 4080 _table_name text; 4081 _schema_name text; 4082begin 4083 raise exception using 4084 column = '>>some column name<<', 4085 constraint = '>>some constraint name<<', 4086 datatype = '>>some datatype name<<', 4087 table = '>>some table name<<', 4088 schema = '>>some schema name<<'; 4089exception when others then 4090 get stacked diagnostics 4091 _column_name = column_name, 4092 _constraint_name = constraint_name, 4093 _datatype_name = pg_datatype_name, 4094 _table_name = table_name, 4095 _schema_name = schema_name; 4096 raise notice 'column %, constraint %, type %, table %, schema %', 4097 _column_name, _constraint_name, _datatype_name, _table_name, _schema_name; 4098end; 4099$$ language plpgsql; 4100select stacked_diagnostics_test(); 4101NOTICE: column >>some column name<<, constraint >>some constraint name<<, type >>some datatype name<<, table >>some table name<<, schema >>some schema name<< 4102 stacked_diagnostics_test 4103-------------------------- 4104 4105(1 row) 4106 4107drop function stacked_diagnostics_test(); 4108-- test variadic functions 4109create or replace function vari(variadic int[]) 4110returns void as $$ 4111begin 4112 for i in array_lower($1,1)..array_upper($1,1) loop 4113 raise notice '%', $1[i]; 4114 end loop; end; 4115$$ language plpgsql; 4116select vari(1,2,3,4,5); 4117NOTICE: 1 4118NOTICE: 2 4119NOTICE: 3 4120NOTICE: 4 4121NOTICE: 5 4122 vari 4123------ 4124 4125(1 row) 4126 4127select vari(3,4,5); 4128NOTICE: 3 4129NOTICE: 4 4130NOTICE: 5 4131 vari 4132------ 4133 4134(1 row) 4135 4136select vari(variadic array[5,6,7]); 4137NOTICE: 5 4138NOTICE: 6 4139NOTICE: 7 4140 vari 4141------ 4142 4143(1 row) 4144 4145drop function vari(int[]); 4146-- coercion test 4147create or replace function pleast(variadic numeric[]) 4148returns numeric as $$ 4149declare aux numeric = $1[array_lower($1,1)]; 4150begin 4151 for i in array_lower($1,1)+1..array_upper($1,1) loop 4152 if $1[i] < aux then aux := $1[i]; end if; 4153 end loop; 4154 return aux; 4155end; 4156$$ language plpgsql immutable strict; 4157select pleast(10,1,2,3,-16); 4158 pleast 4159-------- 4160 -16 4161(1 row) 4162 4163select pleast(10.2,2.2,-1.1); 4164 pleast 4165-------- 4166 -1.1 4167(1 row) 4168 4169select pleast(10.2,10, -20); 4170 pleast 4171-------- 4172 -20 4173(1 row) 4174 4175select pleast(10,20, -1.0); 4176 pleast 4177-------- 4178 -1.0 4179(1 row) 4180 4181-- in case of conflict, non-variadic version is preferred 4182create or replace function pleast(numeric) 4183returns numeric as $$ 4184begin 4185 raise notice 'non-variadic function called'; 4186 return $1; 4187end; 4188$$ language plpgsql immutable strict; 4189select pleast(10); 4190NOTICE: non-variadic function called 4191 pleast 4192-------- 4193 10 4194(1 row) 4195 4196drop function pleast(numeric[]); 4197drop function pleast(numeric); 4198-- test table functions 4199create function tftest(int) returns table(a int, b int) as $$ 4200begin 4201 return query select $1, $1+i from generate_series(1,5) g(i); 4202end; 4203$$ language plpgsql immutable strict; 4204select * from tftest(10); 4205 a | b 4206----+---- 4207 10 | 11 4208 10 | 12 4209 10 | 13 4210 10 | 14 4211 10 | 15 4212(5 rows) 4213 4214create or replace function tftest(a1 int) returns table(a int, b int) as $$ 4215begin 4216 a := a1; b := a1 + 1; 4217 return next; 4218 a := a1 * 10; b := a1 * 10 + 1; 4219 return next; 4220end; 4221$$ language plpgsql immutable strict; 4222select * from tftest(10); 4223 a | b 4224-----+----- 4225 10 | 11 4226 100 | 101 4227(2 rows) 4228 4229drop function tftest(int); 4230create or replace function rttest() 4231returns setof int as $$ 4232declare rc int; 4233 rca int[]; 4234begin 4235 return query values(10),(20); 4236 get diagnostics rc = row_count; 4237 raise notice '% %', found, rc; 4238 return query select * from (values(10),(20)) f(a) where false; 4239 get diagnostics rc = row_count; 4240 raise notice '% %', found, rc; 4241 return query execute 'values(10),(20)'; 4242 -- just for fun, let's use array elements as targets 4243 get diagnostics rca[1] = row_count; 4244 raise notice '% %', found, rca[1]; 4245 return query execute 'select * from (values(10),(20)) f(a) where false'; 4246 get diagnostics rca[2] = row_count; 4247 raise notice '% %', found, rca[2]; 4248end; 4249$$ language plpgsql; 4250select * from rttest(); 4251NOTICE: t 2 4252NOTICE: f 0 4253NOTICE: t 2 4254NOTICE: f 0 4255 rttest 4256-------- 4257 10 4258 20 4259 10 4260 20 4261(4 rows) 4262 4263drop function rttest(); 4264-- Test for proper cleanup at subtransaction exit. This example 4265-- exposed a bug in PG 8.2. 4266CREATE FUNCTION leaker_1(fail BOOL) RETURNS INTEGER AS $$ 4267DECLARE 4268 v_var INTEGER; 4269BEGIN 4270 BEGIN 4271 v_var := (leaker_2(fail)).error_code; 4272 EXCEPTION 4273 WHEN others THEN RETURN 0; 4274 END; 4275 RETURN 1; 4276END; 4277$$ LANGUAGE plpgsql; 4278CREATE FUNCTION leaker_2(fail BOOL, OUT error_code INTEGER, OUT new_id INTEGER) 4279 RETURNS RECORD AS $$ 4280BEGIN 4281 IF fail THEN 4282 RAISE EXCEPTION 'fail ...'; 4283 END IF; 4284 error_code := 1; 4285 new_id := 1; 4286 RETURN; 4287END; 4288$$ LANGUAGE plpgsql; 4289SELECT * FROM leaker_1(false); 4290 leaker_1 4291---------- 4292 1 4293(1 row) 4294 4295SELECT * FROM leaker_1(true); 4296 leaker_1 4297---------- 4298 0 4299(1 row) 4300 4301DROP FUNCTION leaker_1(bool); 4302DROP FUNCTION leaker_2(bool); 4303-- Test for appropriate cleanup of non-simple expression evaluations 4304-- (bug in all versions prior to August 2010) 4305CREATE FUNCTION nonsimple_expr_test() RETURNS text[] AS $$ 4306DECLARE 4307 arr text[]; 4308 lr text; 4309 i integer; 4310BEGIN 4311 arr := array[array['foo','bar'], array['baz', 'quux']]; 4312 lr := 'fool'; 4313 i := 1; 4314 -- use sub-SELECTs to make expressions non-simple 4315 arr[(SELECT i)][(SELECT i+1)] := (SELECT lr); 4316 RETURN arr; 4317END; 4318$$ LANGUAGE plpgsql; 4319SELECT nonsimple_expr_test(); 4320 nonsimple_expr_test 4321------------------------- 4322 {{foo,fool},{baz,quux}} 4323(1 row) 4324 4325DROP FUNCTION nonsimple_expr_test(); 4326CREATE FUNCTION nonsimple_expr_test() RETURNS integer AS $$ 4327declare 4328 i integer NOT NULL := 0; 4329begin 4330 begin 4331 i := (SELECT NULL::integer); -- should throw error 4332 exception 4333 WHEN OTHERS THEN 4334 i := (SELECT 1::integer); 4335 end; 4336 return i; 4337end; 4338$$ LANGUAGE plpgsql; 4339SELECT nonsimple_expr_test(); 4340 nonsimple_expr_test 4341--------------------- 4342 1 4343(1 row) 4344 4345DROP FUNCTION nonsimple_expr_test(); 4346-- 4347-- Test cases involving recursion and error recovery in simple expressions 4348-- (bugs in all versions before October 2010). The problems are most 4349-- easily exposed by mutual recursion between plpgsql and sql functions. 4350-- 4351create function recurse(float8) returns float8 as 4352$$ 4353begin 4354 if ($1 > 0) then 4355 return sql_recurse($1 - 1); 4356 else 4357 return $1; 4358 end if; 4359end; 4360$$ language plpgsql; 4361-- "limit" is to prevent this from being inlined 4362create function sql_recurse(float8) returns float8 as 4363$$ select recurse($1) limit 1; $$ language sql; 4364select recurse(10); 4365 recurse 4366--------- 4367 0 4368(1 row) 4369 4370create function error1(text) returns text language sql as 4371$$ SELECT relname::text FROM pg_class c WHERE c.oid = $1::regclass $$; 4372create function error2(p_name_table text) returns text language plpgsql as $$ 4373begin 4374 return error1(p_name_table); 4375end$$; 4376BEGIN; 4377create table public.stuffs (stuff text); 4378SAVEPOINT a; 4379select error2('nonexistent.stuffs'); 4380ERROR: schema "nonexistent" does not exist 4381CONTEXT: SQL function "error1" statement 1 4382PL/pgSQL function error2(text) line 3 at RETURN 4383ROLLBACK TO a; 4384select error2('public.stuffs'); 4385 error2 4386-------- 4387 stuffs 4388(1 row) 4389 4390rollback; 4391drop function error2(p_name_table text); 4392drop function error1(text); 4393-- Test for proper handling of cast-expression caching 4394create function sql_to_date(integer) returns date as $$ 4395select $1::text::date 4396$$ language sql immutable strict; 4397create cast (integer as date) with function sql_to_date(integer) as assignment; 4398create function cast_invoker(integer) returns date as $$ 4399begin 4400 return $1; 4401end$$ language plpgsql; 4402select cast_invoker(20150717); 4403 cast_invoker 4404-------------- 4405 07-17-2015 4406(1 row) 4407 4408select cast_invoker(20150718); -- second call crashed in pre-release 9.5 4409 cast_invoker 4410-------------- 4411 07-18-2015 4412(1 row) 4413 4414begin; 4415select cast_invoker(20150717); 4416 cast_invoker 4417-------------- 4418 07-17-2015 4419(1 row) 4420 4421select cast_invoker(20150718); 4422 cast_invoker 4423-------------- 4424 07-18-2015 4425(1 row) 4426 4427savepoint s1; 4428select cast_invoker(20150718); 4429 cast_invoker 4430-------------- 4431 07-18-2015 4432(1 row) 4433 4434select cast_invoker(-1); -- fails 4435ERROR: invalid input syntax for type date: "-1" 4436CONTEXT: SQL function "sql_to_date" statement 1 4437PL/pgSQL function cast_invoker(integer) while casting return value to function's return type 4438rollback to savepoint s1; 4439select cast_invoker(20150719); 4440 cast_invoker 4441-------------- 4442 07-19-2015 4443(1 row) 4444 4445select cast_invoker(20150720); 4446 cast_invoker 4447-------------- 4448 07-20-2015 4449(1 row) 4450 4451commit; 4452drop function cast_invoker(integer); 4453drop function sql_to_date(integer) cascade; 4454NOTICE: drop cascades to cast from integer to date 4455-- Test handling of cast cache inside DO blocks 4456-- (to check the original crash case, this must be a cast not previously 4457-- used in this session) 4458begin; 4459do $$ declare x text[]; begin x := '{1.23, 4.56}'::numeric[]; end $$; 4460do $$ declare x text[]; begin x := '{1.23, 4.56}'::numeric[]; end $$; 4461end; 4462-- Test for consistent reporting of error context 4463create function fail() returns int language plpgsql as $$ 4464begin 4465 return 1/0; 4466end 4467$$; 4468select fail(); 4469ERROR: division by zero 4470CONTEXT: SQL statement "SELECT 1/0" 4471PL/pgSQL function fail() line 3 at RETURN 4472select fail(); 4473ERROR: division by zero 4474CONTEXT: SQL statement "SELECT 1/0" 4475PL/pgSQL function fail() line 3 at RETURN 4476drop function fail(); 4477-- Test handling of string literals. 4478set standard_conforming_strings = off; 4479create or replace function strtest() returns text as $$ 4480begin 4481 raise notice 'foo\\bar\041baz'; 4482 return 'foo\\bar\041baz'; 4483end 4484$$ language plpgsql; 4485WARNING: nonstandard use of \\ in a string literal 4486LINE 3: raise notice 'foo\\bar\041baz'; 4487 ^ 4488HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 4489WARNING: nonstandard use of \\ in a string literal 4490LINE 4: return 'foo\\bar\041baz'; 4491 ^ 4492HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 4493WARNING: nonstandard use of \\ in a string literal 4494LINE 4: return 'foo\\bar\041baz'; 4495 ^ 4496HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 4497select strtest(); 4498NOTICE: foo\bar!baz 4499WARNING: nonstandard use of \\ in a string literal 4500LINE 1: SELECT 'foo\\bar\041baz' 4501 ^ 4502HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 4503QUERY: SELECT 'foo\\bar\041baz' 4504 strtest 4505------------- 4506 foo\bar!baz 4507(1 row) 4508 4509create or replace function strtest() returns text as $$ 4510begin 4511 raise notice E'foo\\bar\041baz'; 4512 return E'foo\\bar\041baz'; 4513end 4514$$ language plpgsql; 4515select strtest(); 4516NOTICE: foo\bar!baz 4517 strtest 4518------------- 4519 foo\bar!baz 4520(1 row) 4521 4522set standard_conforming_strings = on; 4523create or replace function strtest() returns text as $$ 4524begin 4525 raise notice 'foo\\bar\041baz\'; 4526 return 'foo\\bar\041baz\'; 4527end 4528$$ language plpgsql; 4529select strtest(); 4530NOTICE: foo\\bar\041baz\ 4531 strtest 4532------------------ 4533 foo\\bar\041baz\ 4534(1 row) 4535 4536create or replace function strtest() returns text as $$ 4537begin 4538 raise notice E'foo\\bar\041baz'; 4539 return E'foo\\bar\041baz'; 4540end 4541$$ language plpgsql; 4542select strtest(); 4543NOTICE: foo\bar!baz 4544 strtest 4545------------- 4546 foo\bar!baz 4547(1 row) 4548 4549drop function strtest(); 4550-- Test anonymous code blocks. 4551DO $$ 4552DECLARE r record; 4553BEGIN 4554 FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno 4555 LOOP 4556 RAISE NOTICE '%, %', r.roomno, r.comment; 4557 END LOOP; 4558END$$; 4559NOTICE: 001, Entrance 4560NOTICE: 002, Office 4561NOTICE: 003, Office 4562NOTICE: 004, Technical 4563NOTICE: 101, Office 4564NOTICE: 102, Conference 4565NOTICE: 103, Restroom 4566NOTICE: 104, Technical 4567NOTICE: 105, Office 4568NOTICE: 106, Office 4569-- these are to check syntax error reporting 4570DO LANGUAGE plpgsql $$begin return 1; end$$; 4571ERROR: RETURN cannot have a parameter in function returning void 4572LINE 1: DO LANGUAGE plpgsql $$begin return 1; end$$; 4573 ^ 4574DO $$ 4575DECLARE r record; 4576BEGIN 4577 FOR r IN SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno 4578 LOOP 4579 RAISE NOTICE '%, %', r.roomno, r.comment; 4580 END LOOP; 4581END$$; 4582ERROR: column "foo" does not exist 4583LINE 1: SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomn... 4584 ^ 4585QUERY: SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno 4586CONTEXT: PL/pgSQL function inline_code_block line 4 at FOR over SELECT rows 4587-- Check handling of errors thrown from/into anonymous code blocks. 4588do $outer$ 4589begin 4590 for i in 1..10 loop 4591 begin 4592 execute $ex$ 4593 do $$ 4594 declare x int = 0; 4595 begin 4596 x := 1 / x; 4597 end; 4598 $$; 4599 $ex$; 4600 exception when division_by_zero then 4601 raise notice 'caught division by zero'; 4602 end; 4603 end loop; 4604end; 4605$outer$; 4606NOTICE: caught division by zero 4607NOTICE: caught division by zero 4608NOTICE: caught division by zero 4609NOTICE: caught division by zero 4610NOTICE: caught division by zero 4611NOTICE: caught division by zero 4612NOTICE: caught division by zero 4613NOTICE: caught division by zero 4614NOTICE: caught division by zero 4615NOTICE: caught division by zero 4616-- Check variable scoping -- a var is not available in its own or prior 4617-- default expressions. 4618create function scope_test() returns int as $$ 4619declare x int := 42; 4620begin 4621 declare y int := x + 1; 4622 x int := x + 2; 4623 begin 4624 return x * 100 + y; 4625 end; 4626end; 4627$$ language plpgsql; 4628select scope_test(); 4629 scope_test 4630------------ 4631 4443 4632(1 row) 4633 4634drop function scope_test(); 4635-- Check handling of conflicts between plpgsql vars and table columns. 4636set plpgsql.variable_conflict = error; 4637create function conflict_test() returns setof int8_tbl as $$ 4638declare r record; 4639 q1 bigint := 42; 4640begin 4641 for r in select q1,q2 from int8_tbl loop 4642 return next r; 4643 end loop; 4644end; 4645$$ language plpgsql; 4646select * from conflict_test(); 4647ERROR: column reference "q1" is ambiguous 4648LINE 1: select q1,q2 from int8_tbl 4649 ^ 4650DETAIL: It could refer to either a PL/pgSQL variable or a table column. 4651QUERY: select q1,q2 from int8_tbl 4652CONTEXT: PL/pgSQL function conflict_test() line 5 at FOR over SELECT rows 4653create or replace function conflict_test() returns setof int8_tbl as $$ 4654#variable_conflict use_variable 4655declare r record; 4656 q1 bigint := 42; 4657begin 4658 for r in select q1,q2 from int8_tbl loop 4659 return next r; 4660 end loop; 4661end; 4662$$ language plpgsql; 4663select * from conflict_test(); 4664 q1 | q2 4665----+------------------- 4666 42 | 456 4667 42 | 4567890123456789 4668 42 | 123 4669 42 | 4567890123456789 4670 42 | -4567890123456789 4671(5 rows) 4672 4673create or replace function conflict_test() returns setof int8_tbl as $$ 4674#variable_conflict use_column 4675declare r record; 4676 q1 bigint := 42; 4677begin 4678 for r in select q1,q2 from int8_tbl loop 4679 return next r; 4680 end loop; 4681end; 4682$$ language plpgsql; 4683select * from conflict_test(); 4684 q1 | q2 4685------------------+------------------- 4686 123 | 456 4687 123 | 4567890123456789 4688 4567890123456789 | 123 4689 4567890123456789 | 4567890123456789 4690 4567890123456789 | -4567890123456789 4691(5 rows) 4692 4693drop function conflict_test(); 4694-- Check that an unreserved keyword can be used as a variable name 4695create function unreserved_test() returns int as $$ 4696declare 4697 forward int := 21; 4698begin 4699 forward := forward * 2; 4700 return forward; 4701end 4702$$ language plpgsql; 4703select unreserved_test(); 4704 unreserved_test 4705----------------- 4706 42 4707(1 row) 4708 4709create or replace function unreserved_test() returns int as $$ 4710declare 4711 return int := 42; 4712begin 4713 return := return + 1; 4714 return return; 4715end 4716$$ language plpgsql; 4717select unreserved_test(); 4718 unreserved_test 4719----------------- 4720 43 4721(1 row) 4722 4723create or replace function unreserved_test() returns int as $$ 4724declare 4725 comment int := 21; 4726begin 4727 comment := comment * 2; 4728 comment on function unreserved_test() is 'this is a test'; 4729 return comment; 4730end 4731$$ language plpgsql; 4732select unreserved_test(); 4733 unreserved_test 4734----------------- 4735 42 4736(1 row) 4737 4738select obj_description('unreserved_test()'::regprocedure, 'pg_proc'); 4739 obj_description 4740----------------- 4741 this is a test 4742(1 row) 4743 4744drop function unreserved_test(); 4745-- 4746-- Test FOREACH over arrays 4747-- 4748create function foreach_test(anyarray) 4749returns void as $$ 4750declare x int; 4751begin 4752 foreach x in array $1 4753 loop 4754 raise notice '%', x; 4755 end loop; 4756 end; 4757$$ language plpgsql; 4758select foreach_test(ARRAY[1,2,3,4]); 4759NOTICE: 1 4760NOTICE: 2 4761NOTICE: 3 4762NOTICE: 4 4763 foreach_test 4764-------------- 4765 4766(1 row) 4767 4768select foreach_test(ARRAY[[1,2],[3,4]]); 4769NOTICE: 1 4770NOTICE: 2 4771NOTICE: 3 4772NOTICE: 4 4773 foreach_test 4774-------------- 4775 4776(1 row) 4777 4778create or replace function foreach_test(anyarray) 4779returns void as $$ 4780declare x int; 4781begin 4782 foreach x slice 1 in array $1 4783 loop 4784 raise notice '%', x; 4785 end loop; 4786 end; 4787$$ language plpgsql; 4788-- should fail 4789select foreach_test(ARRAY[1,2,3,4]); 4790ERROR: FOREACH ... SLICE loop variable must be of an array type 4791CONTEXT: PL/pgSQL function foreach_test(anyarray) line 4 at FOREACH over array 4792select foreach_test(ARRAY[[1,2],[3,4]]); 4793ERROR: FOREACH ... SLICE loop variable must be of an array type 4794CONTEXT: PL/pgSQL function foreach_test(anyarray) line 4 at FOREACH over array 4795create or replace function foreach_test(anyarray) 4796returns void as $$ 4797declare x int[]; 4798begin 4799 foreach x slice 1 in array $1 4800 loop 4801 raise notice '%', x; 4802 end loop; 4803 end; 4804$$ language plpgsql; 4805select foreach_test(ARRAY[1,2,3,4]); 4806NOTICE: {1,2,3,4} 4807 foreach_test 4808-------------- 4809 4810(1 row) 4811 4812select foreach_test(ARRAY[[1,2],[3,4]]); 4813NOTICE: {1,2} 4814NOTICE: {3,4} 4815 foreach_test 4816-------------- 4817 4818(1 row) 4819 4820-- higher level of slicing 4821create or replace function foreach_test(anyarray) 4822returns void as $$ 4823declare x int[]; 4824begin 4825 foreach x slice 2 in array $1 4826 loop 4827 raise notice '%', x; 4828 end loop; 4829 end; 4830$$ language plpgsql; 4831-- should fail 4832select foreach_test(ARRAY[1,2,3,4]); 4833ERROR: slice dimension (2) is out of the valid range 0..1 4834CONTEXT: PL/pgSQL function foreach_test(anyarray) line 4 at FOREACH over array 4835-- ok 4836select foreach_test(ARRAY[[1,2],[3,4]]); 4837NOTICE: {{1,2},{3,4}} 4838 foreach_test 4839-------------- 4840 4841(1 row) 4842 4843select foreach_test(ARRAY[[[1,2]],[[3,4]]]); 4844NOTICE: {{1,2}} 4845NOTICE: {{3,4}} 4846 foreach_test 4847-------------- 4848 4849(1 row) 4850 4851create type xy_tuple AS (x int, y int); 4852-- iteration over array of records 4853create or replace function foreach_test(anyarray) 4854returns void as $$ 4855declare r record; 4856begin 4857 foreach r in array $1 4858 loop 4859 raise notice '%', r; 4860 end loop; 4861 end; 4862$$ language plpgsql; 4863select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]); 4864NOTICE: (10,20) 4865NOTICE: (40,69) 4866NOTICE: (35,78) 4867 foreach_test 4868-------------- 4869 4870(1 row) 4871 4872select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]); 4873NOTICE: (10,20) 4874NOTICE: (40,69) 4875NOTICE: (35,78) 4876NOTICE: (88,76) 4877 foreach_test 4878-------------- 4879 4880(1 row) 4881 4882create or replace function foreach_test(anyarray) 4883returns void as $$ 4884declare x int; y int; 4885begin 4886 foreach x, y in array $1 4887 loop 4888 raise notice 'x = %, y = %', x, y; 4889 end loop; 4890 end; 4891$$ language plpgsql; 4892select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]); 4893NOTICE: x = 10, y = 20 4894NOTICE: x = 40, y = 69 4895NOTICE: x = 35, y = 78 4896 foreach_test 4897-------------- 4898 4899(1 row) 4900 4901select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]); 4902NOTICE: x = 10, y = 20 4903NOTICE: x = 40, y = 69 4904NOTICE: x = 35, y = 78 4905NOTICE: x = 88, y = 76 4906 foreach_test 4907-------------- 4908 4909(1 row) 4910 4911-- slicing over array of composite types 4912create or replace function foreach_test(anyarray) 4913returns void as $$ 4914declare x xy_tuple[]; 4915begin 4916 foreach x slice 1 in array $1 4917 loop 4918 raise notice '%', x; 4919 end loop; 4920 end; 4921$$ language plpgsql; 4922select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]); 4923NOTICE: {"(10,20)","(40,69)","(35,78)"} 4924 foreach_test 4925-------------- 4926 4927(1 row) 4928 4929select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]); 4930NOTICE: {"(10,20)","(40,69)"} 4931NOTICE: {"(35,78)","(88,76)"} 4932 foreach_test 4933-------------- 4934 4935(1 row) 4936 4937drop function foreach_test(anyarray); 4938drop type xy_tuple; 4939-- 4940-- Assorted tests for array subscript assignment 4941-- 4942create temp table rtype (id int, ar text[]); 4943create function arrayassign1() returns text[] language plpgsql as $$ 4944declare 4945 r record; 4946begin 4947 r := row(12, '{foo,bar,baz}')::rtype; 4948 r.ar[2] := 'replace'; 4949 return r.ar; 4950end$$; 4951select arrayassign1(); 4952 arrayassign1 4953------------------- 4954 {foo,replace,baz} 4955(1 row) 4956 4957select arrayassign1(); -- try again to exercise internal caching 4958 arrayassign1 4959------------------- 4960 {foo,replace,baz} 4961(1 row) 4962 4963create domain orderedarray as int[2] 4964 constraint sorted check (value[1] < value[2]); 4965select '{1,2}'::orderedarray; 4966 orderedarray 4967-------------- 4968 {1,2} 4969(1 row) 4970 4971select '{2,1}'::orderedarray; -- fail 4972ERROR: value for domain orderedarray violates check constraint "sorted" 4973create function testoa(x1 int, x2 int, x3 int) returns orderedarray 4974language plpgsql as $$ 4975declare res orderedarray; 4976begin 4977 res := array[x1, x2]; 4978 res[2] := x3; 4979 return res; 4980end$$; 4981select testoa(1,2,3); 4982 testoa 4983-------- 4984 {1,3} 4985(1 row) 4986 4987select testoa(1,2,3); -- try again to exercise internal caching 4988 testoa 4989-------- 4990 {1,3} 4991(1 row) 4992 4993select testoa(2,1,3); -- fail at initial assign 4994ERROR: value for domain orderedarray violates check constraint "sorted" 4995CONTEXT: PL/pgSQL function testoa(integer,integer,integer) line 4 at assignment 4996select testoa(1,2,1); -- fail at update 4997ERROR: value for domain orderedarray violates check constraint "sorted" 4998CONTEXT: PL/pgSQL function testoa(integer,integer,integer) line 5 at assignment 4999drop function arrayassign1(); 5000drop function testoa(x1 int, x2 int, x3 int); 5001-- 5002-- Test handling of expanded arrays 5003-- 5004create function returns_rw_array(int) returns int[] 5005language plpgsql as $$ 5006 declare r int[]; 5007 begin r := array[$1, $1]; return r; end; 5008$$ stable; 5009create function consumes_rw_array(int[]) returns int 5010language plpgsql as $$ 5011 begin return $1[1]; end; 5012$$ stable; 5013select consumes_rw_array(returns_rw_array(42)); 5014 consumes_rw_array 5015------------------- 5016 42 5017(1 row) 5018 5019-- bug #14174 5020explain (verbose, costs off) 5021select i, a from 5022 (select returns_rw_array(1) as a offset 0) ss, 5023 lateral consumes_rw_array(a) i; 5024 QUERY PLAN 5025----------------------------------------------------------------- 5026 Nested Loop 5027 Output: i.i, (returns_rw_array(1)) 5028 -> Result 5029 Output: returns_rw_array(1) 5030 -> Function Scan on public.consumes_rw_array i 5031 Output: i.i 5032 Function Call: consumes_rw_array((returns_rw_array(1))) 5033(7 rows) 5034 5035select i, a from 5036 (select returns_rw_array(1) as a offset 0) ss, 5037 lateral consumes_rw_array(a) i; 5038 i | a 5039---+------- 5040 1 | {1,1} 5041(1 row) 5042 5043explain (verbose, costs off) 5044select consumes_rw_array(a), a from returns_rw_array(1) a; 5045 QUERY PLAN 5046-------------------------------------------- 5047 Function Scan on public.returns_rw_array a 5048 Output: consumes_rw_array(a), a 5049 Function Call: returns_rw_array(1) 5050(3 rows) 5051 5052select consumes_rw_array(a), a from returns_rw_array(1) a; 5053 consumes_rw_array | a 5054-------------------+------- 5055 1 | {1,1} 5056(1 row) 5057 5058explain (verbose, costs off) 5059select consumes_rw_array(a), a from 5060 (values (returns_rw_array(1)), (returns_rw_array(2))) v(a); 5061 QUERY PLAN 5062--------------------------------------------------------------------- 5063 Values Scan on "*VALUES*" 5064 Output: consumes_rw_array("*VALUES*".column1), "*VALUES*".column1 5065(2 rows) 5066 5067select consumes_rw_array(a), a from 5068 (values (returns_rw_array(1)), (returns_rw_array(2))) v(a); 5069 consumes_rw_array | a 5070-------------------+------- 5071 1 | {1,1} 5072 2 | {2,2} 5073(2 rows) 5074 5075do $$ 5076declare a int[] := array[1,2]; 5077begin 5078 a := a || 3; 5079 raise notice 'a = %', a; 5080end$$; 5081NOTICE: a = {1,2,3} 5082-- 5083-- Test access to call stack 5084-- 5085create function inner_func(int) 5086returns int as $$ 5087declare _context text; 5088begin 5089 get diagnostics _context = pg_context; 5090 raise notice '***%***', _context; 5091 -- lets do it again, just for fun.. 5092 get diagnostics _context = pg_context; 5093 raise notice '***%***', _context; 5094 raise notice 'lets make sure we didnt break anything'; 5095 return 2 * $1; 5096end; 5097$$ language plpgsql; 5098create or replace function outer_func(int) 5099returns int as $$ 5100declare 5101 myresult int; 5102begin 5103 raise notice 'calling down into inner_func()'; 5104 myresult := inner_func($1); 5105 raise notice 'inner_func() done'; 5106 return myresult; 5107end; 5108$$ language plpgsql; 5109create or replace function outer_outer_func(int) 5110returns int as $$ 5111declare 5112 myresult int; 5113begin 5114 raise notice 'calling down into outer_func()'; 5115 myresult := outer_func($1); 5116 raise notice 'outer_func() done'; 5117 return myresult; 5118end; 5119$$ language plpgsql; 5120select outer_outer_func(10); 5121NOTICE: calling down into outer_func() 5122NOTICE: calling down into inner_func() 5123NOTICE: ***PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS 5124PL/pgSQL function outer_func(integer) line 6 at assignment 5125PL/pgSQL function outer_outer_func(integer) line 6 at assignment*** 5126NOTICE: ***PL/pgSQL function inner_func(integer) line 7 at GET DIAGNOSTICS 5127PL/pgSQL function outer_func(integer) line 6 at assignment 5128PL/pgSQL function outer_outer_func(integer) line 6 at assignment*** 5129NOTICE: lets make sure we didnt break anything 5130NOTICE: inner_func() done 5131NOTICE: outer_func() done 5132 outer_outer_func 5133------------------ 5134 20 5135(1 row) 5136 5137-- repeated call should to work 5138select outer_outer_func(20); 5139NOTICE: calling down into outer_func() 5140NOTICE: calling down into inner_func() 5141NOTICE: ***PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS 5142PL/pgSQL function outer_func(integer) line 6 at assignment 5143PL/pgSQL function outer_outer_func(integer) line 6 at assignment*** 5144NOTICE: ***PL/pgSQL function inner_func(integer) line 7 at GET DIAGNOSTICS 5145PL/pgSQL function outer_func(integer) line 6 at assignment 5146PL/pgSQL function outer_outer_func(integer) line 6 at assignment*** 5147NOTICE: lets make sure we didnt break anything 5148NOTICE: inner_func() done 5149NOTICE: outer_func() done 5150 outer_outer_func 5151------------------ 5152 40 5153(1 row) 5154 5155drop function outer_outer_func(int); 5156drop function outer_func(int); 5157drop function inner_func(int); 5158-- access to call stack from exception 5159create function inner_func(int) 5160returns int as $$ 5161declare 5162 _context text; 5163 sx int := 5; 5164begin 5165 begin 5166 perform sx / 0; 5167 exception 5168 when division_by_zero then 5169 get diagnostics _context = pg_context; 5170 raise notice '***%***', _context; 5171 end; 5172 5173 -- lets do it again, just for fun.. 5174 get diagnostics _context = pg_context; 5175 raise notice '***%***', _context; 5176 raise notice 'lets make sure we didnt break anything'; 5177 return 2 * $1; 5178end; 5179$$ language plpgsql; 5180create or replace function outer_func(int) 5181returns int as $$ 5182declare 5183 myresult int; 5184begin 5185 raise notice 'calling down into inner_func()'; 5186 myresult := inner_func($1); 5187 raise notice 'inner_func() done'; 5188 return myresult; 5189end; 5190$$ language plpgsql; 5191create or replace function outer_outer_func(int) 5192returns int as $$ 5193declare 5194 myresult int; 5195begin 5196 raise notice 'calling down into outer_func()'; 5197 myresult := outer_func($1); 5198 raise notice 'outer_func() done'; 5199 return myresult; 5200end; 5201$$ language plpgsql; 5202select outer_outer_func(10); 5203NOTICE: calling down into outer_func() 5204NOTICE: calling down into inner_func() 5205NOTICE: ***PL/pgSQL function inner_func(integer) line 10 at GET DIAGNOSTICS 5206PL/pgSQL function outer_func(integer) line 6 at assignment 5207PL/pgSQL function outer_outer_func(integer) line 6 at assignment*** 5208NOTICE: ***PL/pgSQL function inner_func(integer) line 15 at GET DIAGNOSTICS 5209PL/pgSQL function outer_func(integer) line 6 at assignment 5210PL/pgSQL function outer_outer_func(integer) line 6 at assignment*** 5211NOTICE: lets make sure we didnt break anything 5212NOTICE: inner_func() done 5213NOTICE: outer_func() done 5214 outer_outer_func 5215------------------ 5216 20 5217(1 row) 5218 5219-- repeated call should to work 5220select outer_outer_func(20); 5221NOTICE: calling down into outer_func() 5222NOTICE: calling down into inner_func() 5223NOTICE: ***PL/pgSQL function inner_func(integer) line 10 at GET DIAGNOSTICS 5224PL/pgSQL function outer_func(integer) line 6 at assignment 5225PL/pgSQL function outer_outer_func(integer) line 6 at assignment*** 5226NOTICE: ***PL/pgSQL function inner_func(integer) line 15 at GET DIAGNOSTICS 5227PL/pgSQL function outer_func(integer) line 6 at assignment 5228PL/pgSQL function outer_outer_func(integer) line 6 at assignment*** 5229NOTICE: lets make sure we didnt break anything 5230NOTICE: inner_func() done 5231NOTICE: outer_func() done 5232 outer_outer_func 5233------------------ 5234 40 5235(1 row) 5236 5237drop function outer_outer_func(int); 5238drop function outer_func(int); 5239drop function inner_func(int); 5240-- 5241-- Test ASSERT 5242-- 5243do $$ 5244begin 5245 assert 1=1; -- should succeed 5246end; 5247$$; 5248do $$ 5249begin 5250 assert 1=0; -- should fail 5251end; 5252$$; 5253ERROR: assertion failed 5254CONTEXT: PL/pgSQL function inline_code_block line 3 at ASSERT 5255do $$ 5256begin 5257 assert NULL; -- should fail 5258end; 5259$$; 5260ERROR: assertion failed 5261CONTEXT: PL/pgSQL function inline_code_block line 3 at ASSERT 5262-- check controlling GUC 5263set plpgsql.check_asserts = off; 5264do $$ 5265begin 5266 assert 1=0; -- won't be tested 5267end; 5268$$; 5269reset plpgsql.check_asserts; 5270-- test custom message 5271do $$ 5272declare var text := 'some value'; 5273begin 5274 assert 1=0, format('assertion failed, var = "%s"', var); 5275end; 5276$$; 5277ERROR: assertion failed, var = "some value" 5278CONTEXT: PL/pgSQL function inline_code_block line 4 at ASSERT 5279-- ensure assertions are not trapped by 'others' 5280do $$ 5281begin 5282 assert 1=0, 'unhandled assertion'; 5283exception when others then 5284 null; -- do nothing 5285end; 5286$$; 5287ERROR: unhandled assertion 5288CONTEXT: PL/pgSQL function inline_code_block line 3 at ASSERT 5289-- Test use of plpgsql in a domain check constraint (cf. bug #14414) 5290create function plpgsql_domain_check(val int) returns boolean as $$ 5291begin return val > 0; end 5292$$ language plpgsql immutable; 5293create domain plpgsql_domain as integer check(plpgsql_domain_check(value)); 5294do $$ 5295declare v_test plpgsql_domain; 5296begin 5297 v_test := 1; 5298end; 5299$$; 5300do $$ 5301declare v_test plpgsql_domain := 1; 5302begin 5303 v_test := 0; -- fail 5304end; 5305$$; 5306ERROR: value for domain plpgsql_domain violates check constraint "plpgsql_domain_check" 5307CONTEXT: PL/pgSQL function inline_code_block line 4 at assignment 5308-- Test handling of expanded array passed to a domain constraint (bug #14472) 5309create function plpgsql_arr_domain_check(val int[]) returns boolean as $$ 5310begin return val[1] > 0; end 5311$$ language plpgsql immutable; 5312create domain plpgsql_arr_domain as int[] check(plpgsql_arr_domain_check(value)); 5313do $$ 5314declare v_test plpgsql_arr_domain; 5315begin 5316 v_test := array[1]; 5317 v_test := v_test || 2; 5318end; 5319$$; 5320do $$ 5321declare v_test plpgsql_arr_domain := array[1]; 5322begin 5323 v_test := 0 || v_test; -- fail 5324end; 5325$$; 5326ERROR: value for domain plpgsql_arr_domain violates check constraint "plpgsql_arr_domain_check" 5327CONTEXT: PL/pgSQL function inline_code_block line 4 at assignment 5328-- 5329-- test usage of transition tables in AFTER triggers 5330-- 5331CREATE TABLE transition_table_base (id int PRIMARY KEY, val text); 5332CREATE FUNCTION transition_table_base_ins_func() 5333 RETURNS trigger 5334 LANGUAGE plpgsql 5335AS $$ 5336DECLARE 5337 t text; 5338 l text; 5339BEGIN 5340 t = ''; 5341 FOR l IN EXECUTE 5342 $q$ 5343 EXPLAIN (TIMING off, COSTS off, VERBOSE on) 5344 SELECT * FROM newtable 5345 $q$ LOOP 5346 t = t || l || E'\n'; 5347 END LOOP; 5348 5349 RAISE INFO '%', t; 5350 RETURN new; 5351END; 5352$$; 5353CREATE TRIGGER transition_table_base_ins_trig 5354 AFTER INSERT ON transition_table_base 5355 REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable 5356 FOR EACH STATEMENT 5357 EXECUTE PROCEDURE transition_table_base_ins_func(); 5358ERROR: OLD TABLE can only be specified for a DELETE or UPDATE trigger 5359CREATE TRIGGER transition_table_base_ins_trig 5360 AFTER INSERT ON transition_table_base 5361 REFERENCING NEW TABLE AS newtable 5362 FOR EACH STATEMENT 5363 EXECUTE PROCEDURE transition_table_base_ins_func(); 5364INSERT INTO transition_table_base VALUES (1, 'One'), (2, 'Two'); 5365INFO: Named Tuplestore Scan 5366 Output: id, val 5367 5368INSERT INTO transition_table_base VALUES (3, 'Three'), (4, 'Four'); 5369INFO: Named Tuplestore Scan 5370 Output: id, val 5371 5372CREATE OR REPLACE FUNCTION transition_table_base_upd_func() 5373 RETURNS trigger 5374 LANGUAGE plpgsql 5375AS $$ 5376DECLARE 5377 t text; 5378 l text; 5379BEGIN 5380 t = ''; 5381 FOR l IN EXECUTE 5382 $q$ 5383 EXPLAIN (TIMING off, COSTS off, VERBOSE on) 5384 SELECT * FROM oldtable ot FULL JOIN newtable nt USING (id) 5385 $q$ LOOP 5386 t = t || l || E'\n'; 5387 END LOOP; 5388 5389 RAISE INFO '%', t; 5390 RETURN new; 5391END; 5392$$; 5393CREATE TRIGGER transition_table_base_upd_trig 5394 AFTER UPDATE ON transition_table_base 5395 REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable 5396 FOR EACH STATEMENT 5397 EXECUTE PROCEDURE transition_table_base_upd_func(); 5398UPDATE transition_table_base 5399 SET val = '*' || val || '*' 5400 WHERE id BETWEEN 2 AND 3; 5401INFO: Hash Full Join 5402 Output: COALESCE(ot.id, nt.id), ot.val, nt.val 5403 Hash Cond: (ot.id = nt.id) 5404 -> Named Tuplestore Scan 5405 Output: ot.id, ot.val 5406 -> Hash 5407 Output: nt.id, nt.val 5408 -> Named Tuplestore Scan 5409 Output: nt.id, nt.val 5410 5411CREATE TABLE transition_table_level1 5412( 5413 level1_no serial NOT NULL , 5414 level1_node_name varchar(255), 5415 PRIMARY KEY (level1_no) 5416) WITHOUT OIDS; 5417CREATE TABLE transition_table_level2 5418( 5419 level2_no serial NOT NULL , 5420 parent_no int NOT NULL, 5421 level1_node_name varchar(255), 5422 PRIMARY KEY (level2_no) 5423) WITHOUT OIDS; 5424CREATE TABLE transition_table_status 5425( 5426 level int NOT NULL, 5427 node_no int NOT NULL, 5428 status int, 5429 PRIMARY KEY (level, node_no) 5430) WITHOUT OIDS; 5431CREATE FUNCTION transition_table_level1_ri_parent_del_func() 5432 RETURNS TRIGGER 5433 LANGUAGE plpgsql 5434AS $$ 5435 DECLARE n bigint; 5436 BEGIN 5437 PERFORM FROM p JOIN transition_table_level2 c ON c.parent_no = p.level1_no; 5438 IF FOUND THEN 5439 RAISE EXCEPTION 'RI error'; 5440 END IF; 5441 RETURN NULL; 5442 END; 5443$$; 5444CREATE TRIGGER transition_table_level1_ri_parent_del_trigger 5445 AFTER DELETE ON transition_table_level1 5446 REFERENCING OLD TABLE AS p 5447 FOR EACH STATEMENT EXECUTE PROCEDURE 5448 transition_table_level1_ri_parent_del_func(); 5449CREATE FUNCTION transition_table_level1_ri_parent_upd_func() 5450 RETURNS TRIGGER 5451 LANGUAGE plpgsql 5452AS $$ 5453 DECLARE 5454 x int; 5455 BEGIN 5456 WITH p AS (SELECT level1_no, sum(delta) cnt 5457 FROM (SELECT level1_no, 1 AS delta FROM i 5458 UNION ALL 5459 SELECT level1_no, -1 AS delta FROM d) w 5460 GROUP BY level1_no 5461 HAVING sum(delta) < 0) 5462 SELECT level1_no 5463 FROM p JOIN transition_table_level2 c ON c.parent_no = p.level1_no 5464 INTO x; 5465 IF FOUND THEN 5466 RAISE EXCEPTION 'RI error'; 5467 END IF; 5468 RETURN NULL; 5469 END; 5470$$; 5471CREATE TRIGGER transition_table_level1_ri_parent_upd_trigger 5472 AFTER UPDATE ON transition_table_level1 5473 REFERENCING OLD TABLE AS d NEW TABLE AS i 5474 FOR EACH STATEMENT EXECUTE PROCEDURE 5475 transition_table_level1_ri_parent_upd_func(); 5476CREATE FUNCTION transition_table_level2_ri_child_insupd_func() 5477 RETURNS TRIGGER 5478 LANGUAGE plpgsql 5479AS $$ 5480 BEGIN 5481 PERFORM FROM i 5482 LEFT JOIN transition_table_level1 p 5483 ON p.level1_no IS NOT NULL AND p.level1_no = i.parent_no 5484 WHERE p.level1_no IS NULL; 5485 IF FOUND THEN 5486 RAISE EXCEPTION 'RI error'; 5487 END IF; 5488 RETURN NULL; 5489 END; 5490$$; 5491CREATE TRIGGER transition_table_level2_ri_child_ins_trigger 5492 AFTER INSERT ON transition_table_level2 5493 REFERENCING NEW TABLE AS i 5494 FOR EACH STATEMENT EXECUTE PROCEDURE 5495 transition_table_level2_ri_child_insupd_func(); 5496CREATE TRIGGER transition_table_level2_ri_child_upd_trigger 5497 AFTER UPDATE ON transition_table_level2 5498 REFERENCING NEW TABLE AS i 5499 FOR EACH STATEMENT EXECUTE PROCEDURE 5500 transition_table_level2_ri_child_insupd_func(); 5501-- create initial test data 5502INSERT INTO transition_table_level1 (level1_no) 5503 SELECT generate_series(1,200); 5504ANALYZE transition_table_level1; 5505INSERT INTO transition_table_level2 (level2_no, parent_no) 5506 SELECT level2_no, level2_no / 50 + 1 AS parent_no 5507 FROM generate_series(1,9999) level2_no; 5508ANALYZE transition_table_level2; 5509INSERT INTO transition_table_status (level, node_no, status) 5510 SELECT 1, level1_no, 0 FROM transition_table_level1; 5511INSERT INTO transition_table_status (level, node_no, status) 5512 SELECT 2, level2_no, 0 FROM transition_table_level2; 5513ANALYZE transition_table_status; 5514INSERT INTO transition_table_level1(level1_no) 5515 SELECT generate_series(201,1000); 5516ANALYZE transition_table_level1; 5517-- behave reasonably if someone tries to modify a transition table 5518CREATE FUNCTION transition_table_level2_bad_usage_func() 5519 RETURNS TRIGGER 5520 LANGUAGE plpgsql 5521AS $$ 5522 BEGIN 5523 INSERT INTO dx VALUES (1000000, 1000000, 'x'); 5524 RETURN NULL; 5525 END; 5526$$; 5527CREATE TRIGGER transition_table_level2_bad_usage_trigger 5528 AFTER DELETE ON transition_table_level2 5529 REFERENCING OLD TABLE AS dx 5530 FOR EACH STATEMENT EXECUTE PROCEDURE 5531 transition_table_level2_bad_usage_func(); 5532DELETE FROM transition_table_level2 5533 WHERE level2_no BETWEEN 301 AND 305; 5534ERROR: relation "dx" cannot be the target of a modifying statement 5535CONTEXT: SQL statement "INSERT INTO dx VALUES (1000000, 1000000, 'x')" 5536PL/pgSQL function transition_table_level2_bad_usage_func() line 3 at SQL statement 5537DROP TRIGGER transition_table_level2_bad_usage_trigger 5538 ON transition_table_level2; 5539-- attempt modifications which would break RI (should all fail) 5540DELETE FROM transition_table_level1 5541 WHERE level1_no = 25; 5542ERROR: RI error 5543CONTEXT: PL/pgSQL function transition_table_level1_ri_parent_del_func() line 6 at RAISE 5544UPDATE transition_table_level1 SET level1_no = -1 5545 WHERE level1_no = 30; 5546ERROR: RI error 5547CONTEXT: PL/pgSQL function transition_table_level1_ri_parent_upd_func() line 15 at RAISE 5548INSERT INTO transition_table_level2 (level2_no, parent_no) 5549 VALUES (10000, 10000); 5550ERROR: RI error 5551CONTEXT: PL/pgSQL function transition_table_level2_ri_child_insupd_func() line 8 at RAISE 5552UPDATE transition_table_level2 SET parent_no = 2000 5553 WHERE level2_no = 40; 5554ERROR: RI error 5555CONTEXT: PL/pgSQL function transition_table_level2_ri_child_insupd_func() line 8 at RAISE 5556-- attempt modifications which would not break RI (should all succeed) 5557DELETE FROM transition_table_level1 5558 WHERE level1_no BETWEEN 201 AND 1000; 5559DELETE FROM transition_table_level1 5560 WHERE level1_no BETWEEN 100000000 AND 100000010; 5561SELECT count(*) FROM transition_table_level1; 5562 count 5563------- 5564 200 5565(1 row) 5566 5567DELETE FROM transition_table_level2 5568 WHERE level2_no BETWEEN 211 AND 220; 5569SELECT count(*) FROM transition_table_level2; 5570 count 5571------- 5572 9989 5573(1 row) 5574 5575CREATE TABLE alter_table_under_transition_tables 5576( 5577 id int PRIMARY KEY, 5578 name text 5579); 5580CREATE FUNCTION alter_table_under_transition_tables_upd_func() 5581 RETURNS TRIGGER 5582 LANGUAGE plpgsql 5583AS $$ 5584BEGIN 5585 RAISE WARNING 'old table = %, new table = %', 5586 (SELECT string_agg(id || '=' || name, ',') FROM d), 5587 (SELECT string_agg(id || '=' || name, ',') FROM i); 5588 RAISE NOTICE 'one = %', (SELECT 1 FROM alter_table_under_transition_tables LIMIT 1); 5589 RETURN NULL; 5590END; 5591$$; 5592-- should fail, TRUNCATE is not compatible with transition tables 5593CREATE TRIGGER alter_table_under_transition_tables_upd_trigger 5594 AFTER TRUNCATE OR UPDATE ON alter_table_under_transition_tables 5595 REFERENCING OLD TABLE AS d NEW TABLE AS i 5596 FOR EACH STATEMENT EXECUTE PROCEDURE 5597 alter_table_under_transition_tables_upd_func(); 5598ERROR: TRUNCATE triggers with transition tables are not supported 5599-- should work 5600CREATE TRIGGER alter_table_under_transition_tables_upd_trigger 5601 AFTER UPDATE ON alter_table_under_transition_tables 5602 REFERENCING OLD TABLE AS d NEW TABLE AS i 5603 FOR EACH STATEMENT EXECUTE PROCEDURE 5604 alter_table_under_transition_tables_upd_func(); 5605INSERT INTO alter_table_under_transition_tables 5606 VALUES (1, '1'), (2, '2'), (3, '3'); 5607UPDATE alter_table_under_transition_tables 5608 SET name = name || name; 5609WARNING: old table = 1=1,2=2,3=3, new table = 1=11,2=22,3=33 5610NOTICE: one = 1 5611-- now change 'name' to an integer to see what happens... 5612ALTER TABLE alter_table_under_transition_tables 5613 ALTER COLUMN name TYPE int USING name::integer; 5614UPDATE alter_table_under_transition_tables 5615 SET name = (name::text || name::text)::integer; 5616WARNING: old table = 1=11,2=22,3=33, new table = 1=1111,2=2222,3=3333 5617NOTICE: one = 1 5618-- now drop column 'name' 5619ALTER TABLE alter_table_under_transition_tables 5620 DROP column name; 5621UPDATE alter_table_under_transition_tables 5622 SET id = id; 5623ERROR: column "name" does not exist 5624LINE 1: SELECT (SELECT string_agg(id || '=' || name, ',') FROM d) 5625 ^ 5626QUERY: SELECT (SELECT string_agg(id || '=' || name, ',') FROM d) 5627CONTEXT: PL/pgSQL function alter_table_under_transition_tables_upd_func() line 3 at RAISE 5628-- 5629-- Test multiple reference to a transition table 5630-- 5631CREATE TABLE multi_test (i int); 5632INSERT INTO multi_test VALUES (1); 5633CREATE OR REPLACE FUNCTION multi_test_trig() RETURNS trigger 5634LANGUAGE plpgsql AS $$ 5635BEGIN 5636 RAISE NOTICE 'count = %', (SELECT COUNT(*) FROM new_test); 5637 RAISE NOTICE 'count union = %', 5638 (SELECT COUNT(*) 5639 FROM (SELECT * FROM new_test UNION ALL SELECT * FROM new_test) ss); 5640 RETURN NULL; 5641END$$; 5642CREATE TRIGGER my_trigger AFTER UPDATE ON multi_test 5643 REFERENCING NEW TABLE AS new_test OLD TABLE as old_test 5644 FOR EACH STATEMENT EXECUTE PROCEDURE multi_test_trig(); 5645UPDATE multi_test SET i = i; 5646NOTICE: count = 1 5647NOTICE: count union = 2 5648DROP TABLE multi_test; 5649DROP FUNCTION multi_test_trig(); 5650-- 5651-- Check type parsing and record fetching from partitioned tables 5652-- 5653CREATE TABLE partitioned_table (a int, b text) PARTITION BY LIST (a); 5654CREATE TABLE pt_part1 PARTITION OF partitioned_table FOR VALUES IN (1); 5655CREATE TABLE pt_part2 PARTITION OF partitioned_table FOR VALUES IN (2); 5656INSERT INTO partitioned_table VALUES (1, 'Row 1'); 5657INSERT INTO partitioned_table VALUES (2, 'Row 2'); 5658CREATE OR REPLACE FUNCTION get_from_partitioned_table(partitioned_table.a%type) 5659RETURNS partitioned_table AS $$ 5660DECLARE 5661 a_val partitioned_table.a%TYPE; 5662 result partitioned_table%ROWTYPE; 5663BEGIN 5664 a_val := $1; 5665 SELECT * INTO result FROM partitioned_table WHERE a = a_val; 5666 RETURN result; 5667END; $$ LANGUAGE plpgsql; 5668NOTICE: type reference partitioned_table.a%TYPE converted to integer 5669SELECT * FROM get_from_partitioned_table(1) AS t; 5670 a | b 5671---+------- 5672 1 | Row 1 5673(1 row) 5674 5675CREATE OR REPLACE FUNCTION list_partitioned_table() 5676RETURNS SETOF partitioned_table.a%TYPE AS $$ 5677DECLARE 5678 row partitioned_table%ROWTYPE; 5679 a_val partitioned_table.a%TYPE; 5680BEGIN 5681 FOR row IN SELECT * FROM partitioned_table ORDER BY a LOOP 5682 a_val := row.a; 5683 RETURN NEXT a_val; 5684 END LOOP; 5685 RETURN; 5686END; $$ LANGUAGE plpgsql; 5687NOTICE: type reference partitioned_table.a%TYPE converted to integer 5688SELECT * FROM list_partitioned_table() AS t; 5689 t 5690--- 5691 1 5692 2 5693(2 rows) 5694 5695-- 5696-- Check argument name is used instead of $n in error message 5697-- 5698CREATE FUNCTION fx(x WSlot) RETURNS void AS $$ 5699BEGIN 5700 GET DIAGNOSTICS x = ROW_COUNT; 5701 RETURN; 5702END; $$ LANGUAGE plpgsql; 5703ERROR: "x" is not a scalar variable 5704LINE 3: GET DIAGNOSTICS x = ROW_COUNT; 5705 ^ 5706