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