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); 27 28CREATE UNIQUE INDEX Room_rno ON Room USING btree (roomno bpchar_ops); 29 30CREATE TABLE WSlot ( 31 slotname char(20), 32 roomno char(8), 33 slotlink char(20), 34 backlink char(20) 35); 36 37CREATE UNIQUE INDEX WSlot_name ON WSlot USING btree (slotname bpchar_ops); 38 39CREATE TABLE PField ( 40 name text, 41 comment text 42); 43 44CREATE UNIQUE INDEX PField_name ON PField USING btree (name text_ops); 45 46CREATE TABLE PSlot ( 47 slotname char(20), 48 pfname text, 49 slotlink char(20), 50 backlink char(20) 51); 52 53CREATE UNIQUE INDEX PSlot_name ON PSlot USING btree (slotname bpchar_ops); 54 55CREATE TABLE PLine ( 56 slotname char(20), 57 phonenumber char(20), 58 comment text, 59 backlink char(20) 60); 61 62CREATE UNIQUE INDEX PLine_name ON PLine USING btree (slotname bpchar_ops); 63 64CREATE TABLE Hub ( 65 name char(14), 66 comment text, 67 nslots integer 68); 69 70CREATE UNIQUE INDEX Hub_name ON Hub USING btree (name bpchar_ops); 71 72CREATE TABLE HSlot ( 73 slotname char(20), 74 hubname char(14), 75 slotno integer, 76 slotlink char(20) 77); 78 79CREATE UNIQUE INDEX HSlot_name ON HSlot USING btree (slotname bpchar_ops); 80 81CREATE INDEX HSlot_hubname ON HSlot USING btree (hubname bpchar_ops); 82 83CREATE TABLE SYSTEM ( 84 name text, 85 comment text 86); 87 88CREATE UNIQUE INDEX System_name ON SYSTEM USING btree (name text_ops); 89 90CREATE TABLE IFace ( 91 slotname char(20), 92 sysname text, 93 ifname text, 94 slotlink char(20) 95); 96 97CREATE UNIQUE INDEX IFace_name ON IFace USING btree (slotname bpchar_ops); 98 99CREATE TABLE PHone ( 100 slotname char(20), 101 comment text, 102 slotlink char(20) 103); 104 105CREATE UNIQUE INDEX PHone_name ON PHone USING btree (slotname bpchar_ops); 106 107-- ************************************************************ 108-- * 109-- * Trigger procedures and functions for the patchfield 110-- * test of PL/pgSQL 111-- * 112-- ************************************************************ 113-- ************************************************************ 114-- * AFTER UPDATE on Room 115-- * - If room no changes let wall slots follow 116-- ************************************************************ 117CREATE FUNCTION tg_room_au () 118 RETURNS TRIGGER 119 AS ' 120BEGIN 121 IF NEW.roomno != OLD.roomno THEN 122 UPDATE 123 WSlot 124 SET 125 roomno = NEW.roomno 126 WHERE 127 roomno = OLD.roomno; 128 END IF; 129 RETURN new; 130END; 131' 132LANGUAGE plpgsql; 133 134CREATE TRIGGER tg_room_au 135 AFTER UPDATE ON Room FOR EACH ROW 136 EXECUTE PROCEDURE tg_room_au (); 137 138-- ************************************************************ 139-- * AFTER DELETE on Room 140-- * - delete wall slots in this room 141-- ************************************************************ 142CREATE FUNCTION tg_room_ad () 143 RETURNS TRIGGER 144 AS ' 145BEGIN 146 DELETE FROM WSlot 147 WHERE roomno = OLD.roomno; 148 RETURN old; 149END; 150' 151LANGUAGE plpgsql; 152 153CREATE TRIGGER tg_room_ad 154 AFTER DELETE ON Room FOR EACH ROW 155 EXECUTE PROCEDURE tg_room_ad (); 156 157-- ************************************************************ 158-- * BEFORE INSERT or UPDATE on WSlot 159-- * - Check that room exists 160-- ************************************************************ 161CREATE FUNCTION tg_wslot_biu () 162 RETURNS TRIGGER 163 AS $$ 164BEGIN 165 IF count(*) = 0 166 FROM 167 Room 168 WHERE 169 roomno = NEW.roomno THEN 170 RAISE EXCEPTION 'Room % does not exist', NEW.roomno; 171 END IF; 172 RETURN new; 173END; 174$$ 175LANGUAGE plpgsql; 176 177CREATE TRIGGER tg_wslot_biu 178 BEFORE INSERT OR UPDATE ON WSlot FOR EACH ROW 179 EXECUTE PROCEDURE tg_wslot_biu (); 180 181-- ************************************************************ 182-- * AFTER UPDATE on PField 183-- * - Let PSlots of this field follow 184-- ************************************************************ 185CREATE FUNCTION tg_pfield_au () 186 RETURNS TRIGGER 187 AS ' 188BEGIN 189 IF NEW.name != OLD.name THEN 190 UPDATE 191 PSlot 192 SET 193 pfname = NEW.name 194 WHERE 195 pfname = OLD.name; 196 END IF; 197 RETURN new; 198END; 199' 200LANGUAGE plpgsql; 201 202CREATE TRIGGER tg_pfield_au 203 AFTER UPDATE ON PField FOR EACH ROW 204 EXECUTE PROCEDURE tg_pfield_au (); 205 206-- ************************************************************ 207-- * AFTER DELETE on PField 208-- * - Remove all slots of this patchfield 209-- ************************************************************ 210CREATE FUNCTION tg_pfield_ad () 211 RETURNS TRIGGER 212 AS ' 213BEGIN 214 DELETE FROM PSlot 215 WHERE pfname = OLD.name; 216 RETURN old; 217END; 218' 219LANGUAGE plpgsql; 220 221CREATE TRIGGER tg_pfield_ad 222 AFTER DELETE ON PField FOR EACH ROW 223 EXECUTE PROCEDURE tg_pfield_ad (); 224 225-- ************************************************************ 226-- * BEFORE INSERT or UPDATE on PSlot 227-- * - Ensure that our patchfield does exist 228-- ************************************************************ 229CREATE FUNCTION tg_pslot_biu () 230 RETURNS TRIGGER 231 AS $proc$ 232DECLARE 233 pfrec record; 234 ps alias FOR new; 235BEGIN 236 SELECT 237 INTO pfrec * 238 FROM 239 PField 240 WHERE 241 name = ps.pfname; 242 IF NOT found THEN 243 RAISE EXCEPTION $$ Patchfield "%" does NOT exist$$, ps.pfname; 244 END IF; 245 RETURN ps; 246END; 247$proc$ 248LANGUAGE plpgsql; 249 250CREATE TRIGGER tg_pslot_biu 251 BEFORE INSERT OR UPDATE ON PSlot FOR EACH ROW 252 EXECUTE PROCEDURE tg_pslot_biu (); 253 254-- ************************************************************ 255-- * AFTER UPDATE on System 256-- * - If system name changes let interfaces follow 257-- ************************************************************ 258CREATE FUNCTION tg_system_au () 259 RETURNS TRIGGER 260 AS ' 261BEGIN 262 IF NEW.name != OLD.name THEN 263 UPDATE 264 IFace 265 SET 266 sysname = NEW.name 267 WHERE 268 sysname = OLD.name; 269 END IF; 270 RETURN new; 271END; 272' 273LANGUAGE plpgsql; 274 275CREATE TRIGGER tg_system_au 276 AFTER UPDATE ON SYSTEM FOR EACH ROW 277 EXECUTE PROCEDURE tg_system_au (); 278 279-- ************************************************************ 280-- * BEFORE INSERT or UPDATE on IFace 281-- * - set the slotname to IF.sysname.ifname 282-- ************************************************************ 283CREATE FUNCTION tg_iface_biu () 284 RETURNS TRIGGER 285 AS $$ 286DECLARE 287 sname text; 288 sysrec record; 289BEGIN 290 SELECT 291 INTO sysrec * 292 FROM 293 SYSTEM 294 WHERE 295 name = NEW.sysname; 296 IF NOT found THEN 297 RAISE EXCEPTION $q$system "%" does not exist$q$, NEW.sysname; 298 END IF; 299 sname := 'IF.' || NEW.sysname; 300 sname := sname || '.'; 301 sname := sname || NEW.ifname; 302 IF length(sname) > 20 THEN 303 RAISE EXCEPTION 'IFace slotname "%" too long (20 char max)', sname; 304 END IF; 305 NEW.slotname := sname; 306 RETURN new; 307END; 308$$ 309LANGUAGE plpgsql; 310 311CREATE TRIGGER tg_iface_biu 312 BEFORE INSERT OR UPDATE ON IFace FOR EACH ROW 313 EXECUTE PROCEDURE tg_iface_biu (); 314 315-- ************************************************************ 316-- * AFTER INSERT or UPDATE or DELETE on Hub 317-- * - insert/delete/rename slots as required 318-- ************************************************************ 319CREATE FUNCTION tg_hub_a () 320 RETURNS TRIGGER 321 AS ' 322DECLARE 323 hname text; 324 dummy integer; 325BEGIN 326 IF tg_op = ''INSERT'' THEN 327 dummy := tg_hub_adjustslots (NEW.name, 0, NEW.nslots); 328 RETURN new; 329 END IF; 330 IF tg_op = ''UPDATE'' THEN 331 IF NEW.name != OLD.name THEN 332 UPDATE 333 HSlot 334 SET 335 hubname = NEW.name 336 WHERE 337 hubname = OLD.name; 338 END IF; 339 dummy := tg_hub_adjustslots (NEW.name, OLD.nslots, NEW.nslots); 340 RETURN new; 341 END IF; 342 IF tg_op = ''DELETE'' THEN 343 dummy := tg_hub_adjustslots (OLD.name, OLD.nslots, 0); 344 RETURN old; 345 END IF; 346END; 347' 348LANGUAGE plpgsql; 349 350CREATE TRIGGER tg_hub_a 351 AFTER INSERT OR UPDATE OR DELETE ON Hub FOR EACH ROW 352 EXECUTE PROCEDURE tg_hub_a (); 353 354-- ************************************************************ 355-- * Support function to add/remove slots of Hub 356-- ************************************************************ 357CREATE FUNCTION tg_hub_adjustslots (hname bpchar, oldnslots integer, newnslots integer) 358 RETURNS integer 359 AS ' 360BEGIN 361 IF newnslots = oldnslots THEN 362 RETURN 0; 363 END IF; 364 IF newnslots < oldnslots THEN 365 DELETE FROM HSlot 366 WHERE hubname = hname 367 AND slotno > newnslots; 368 RETURN 0; 369 END IF; 370 FOR i IN oldnslots + 1..newnslots LOOP 371 INSERT INTO HSlot (slotname, hubname, slotno, slotlink) 372 VALUES (''HS.dummy'', hname, i, ''''); 373 END LOOP; 374 RETURN 0; 375END 376' 377LANGUAGE plpgsql; 378 379-- Test comments 380COMMENT ON FUNCTION tg_hub_adjustslots_wrong (bpchar, integer, integer) IS 'function with args'; 381 382COMMENT ON FUNCTION tg_hub_adjustslots (bpchar, integer, integer) IS 'function with args'; 383 384COMMENT ON FUNCTION tg_hub_adjustslots (bpchar, integer, integer) IS NULL; 385 386-- ************************************************************ 387-- * BEFORE INSERT or UPDATE on HSlot 388-- * - prevent from manual manipulation 389-- * - set the slotname to HS.hubname.slotno 390-- ************************************************************ 391CREATE FUNCTION tg_hslot_biu () 392 RETURNS TRIGGER 393 AS ' 394DECLARE 395 sname text; 396 xname HSlot.slotname%TYPE; 397 hubrec record; 398BEGIN 399 SELECT 400 INTO hubrec * 401 FROM 402 Hub 403 WHERE 404 name = NEW.hubname; 405 IF NOT found THEN 406 RAISE EXCEPTION ''no manual manipulation of HSlot''; 407 END IF; 408 IF NEW.slotno < 1 OR NEW.slotno > hubrec.nslots THEN 409 RAISE EXCEPTION ''no manual manipulation of HSlot''; 410 END IF; 411 IF tg_op = ''UPDATE'' AND NEW.hubname != OLD.hubname THEN 412 IF count(*) > 0 413 FROM 414 Hub 415 WHERE 416 name = OLD.hubname THEN 417 RAISE EXCEPTION ''no manual manipulation of HSlot''; 418 END IF; 419 END IF; 420 sname := ''HS.'' || trim(NEW.hubname); 421 sname := sname || ''.''; 422 sname := sname || NEW.slotno::text; 423 IF length(sname) > 20 THEN 424 RAISE EXCEPTION ''HSlot slotname "%" too long (20 char max)'', sname; 425 END IF; 426 NEW.slotname := sname; 427 RETURN new; 428END; 429' 430LANGUAGE plpgsql; 431 432CREATE TRIGGER tg_hslot_biu 433 BEFORE INSERT OR UPDATE ON HSlot FOR EACH ROW 434 EXECUTE PROCEDURE tg_hslot_biu (); 435 436-- ************************************************************ 437-- * BEFORE DELETE on HSlot 438-- * - prevent from manual manipulation 439-- ************************************************************ 440CREATE FUNCTION tg_hslot_bd () 441 RETURNS TRIGGER 442 AS ' 443DECLARE 444 hubrec record; 445BEGIN 446 SELECT 447 INTO hubrec * 448 FROM 449 Hub 450 WHERE 451 name = OLD.hubname; 452 IF NOT found THEN 453 RETURN old; 454 END IF; 455 IF OLD.slotno > hubrec.nslots THEN 456 RETURN old; 457 END IF; 458 RAISE EXCEPTION ''no manual manipulation of HSlot''; 459END; 460' 461LANGUAGE plpgsql; 462 463CREATE TRIGGER tg_hslot_bd 464 BEFORE DELETE ON HSlot FOR EACH ROW 465 EXECUTE PROCEDURE tg_hslot_bd (); 466 467-- ************************************************************ 468-- * BEFORE INSERT on all slots 469-- * - Check name prefix 470-- ************************************************************ 471CREATE FUNCTION tg_chkslotname () 472 RETURNS TRIGGER 473 AS ' 474BEGIN 475 IF substr(NEW.slotname, 1, 2) != tg_argv[0] THEN 476 RAISE EXCEPTION ''slotname must begin with %'', tg_argv[0]; 477 END IF; 478 RETURN new; 479END; 480' 481LANGUAGE plpgsql; 482 483CREATE TRIGGER tg_chkslotname 484 BEFORE INSERT ON PSlot FOR EACH ROW 485 EXECUTE PROCEDURE tg_chkslotname ('PS'); 486 487CREATE TRIGGER tg_chkslotname 488 BEFORE INSERT ON WSlot FOR EACH ROW 489 EXECUTE PROCEDURE tg_chkslotname ('WS'); 490 491CREATE TRIGGER tg_chkslotname 492 BEFORE INSERT ON PLine FOR EACH ROW 493 EXECUTE PROCEDURE tg_chkslotname ('PL'); 494 495CREATE TRIGGER tg_chkslotname 496 BEFORE INSERT ON IFace FOR EACH ROW 497 EXECUTE PROCEDURE tg_chkslotname ('IF'); 498 499CREATE TRIGGER tg_chkslotname 500 BEFORE INSERT ON PHone FOR EACH ROW 501 EXECUTE PROCEDURE tg_chkslotname ('PH'); 502 503-- ************************************************************ 504-- * BEFORE INSERT or UPDATE on all slots with slotlink 505-- * - Set slotlink to empty string if NULL value given 506-- ************************************************************ 507CREATE FUNCTION tg_chkslotlink () 508 RETURNS TRIGGER 509 AS ' 510BEGIN 511 IF NEW.slotlink ISNULL THEN 512 NEW.slotlink := ''''; 513 END IF; 514 RETURN new; 515END; 516' 517LANGUAGE plpgsql; 518 519CREATE TRIGGER tg_chkslotlink 520 BEFORE INSERT OR UPDATE ON PSlot FOR EACH ROW 521 EXECUTE PROCEDURE tg_chkslotlink (); 522 523CREATE TRIGGER tg_chkslotlink 524 BEFORE INSERT OR UPDATE ON WSlot FOR EACH ROW 525 EXECUTE PROCEDURE tg_chkslotlink (); 526 527CREATE TRIGGER tg_chkslotlink 528 BEFORE INSERT OR UPDATE ON IFace FOR EACH ROW 529 EXECUTE PROCEDURE tg_chkslotlink (); 530 531CREATE TRIGGER tg_chkslotlink 532 BEFORE INSERT OR UPDATE ON HSlot FOR EACH ROW 533 EXECUTE PROCEDURE tg_chkslotlink (); 534 535CREATE TRIGGER tg_chkslotlink 536 BEFORE INSERT OR UPDATE ON PHone FOR EACH ROW 537 EXECUTE PROCEDURE tg_chkslotlink (); 538 539-- ************************************************************ 540-- * BEFORE INSERT or UPDATE on all slots with backlink 541-- * - Set backlink to empty string if NULL value given 542-- ************************************************************ 543CREATE FUNCTION tg_chkbacklink () 544 RETURNS TRIGGER 545 AS ' 546BEGIN 547 IF NEW.backlink ISNULL THEN 548 NEW.backlink := ''''; 549 END IF; 550 RETURN new; 551END; 552' 553LANGUAGE plpgsql; 554 555CREATE TRIGGER tg_chkbacklink 556 BEFORE INSERT OR UPDATE ON PSlot FOR EACH ROW 557 EXECUTE PROCEDURE tg_chkbacklink (); 558 559CREATE TRIGGER tg_chkbacklink 560 BEFORE INSERT OR UPDATE ON WSlot FOR EACH ROW 561 EXECUTE PROCEDURE tg_chkbacklink (); 562 563CREATE TRIGGER tg_chkbacklink 564 BEFORE INSERT OR UPDATE ON PLine FOR EACH ROW 565 EXECUTE PROCEDURE tg_chkbacklink (); 566 567-- ************************************************************ 568-- * BEFORE UPDATE on PSlot 569-- * - do delete/insert instead of update if name changes 570-- ************************************************************ 571CREATE FUNCTION tg_pslot_bu () 572 RETURNS TRIGGER 573 AS ' 574BEGIN 575 IF NEW.slotname != OLD.slotname THEN 576 DELETE FROM PSlot 577 WHERE slotname = OLD.slotname; 578 INSERT INTO PSlot (slotname, pfname, slotlink, backlink) 579 VALUES (NEW.slotname, NEW.pfname, NEW.slotlink, NEW.backlink); 580 RETURN NULL; 581 END IF; 582 RETURN new; 583END; 584' 585LANGUAGE plpgsql; 586 587CREATE TRIGGER tg_pslot_bu 588 BEFORE UPDATE ON PSlot FOR EACH ROW 589 EXECUTE PROCEDURE tg_pslot_bu (); 590 591-- ************************************************************ 592-- * BEFORE UPDATE on WSlot 593-- * - do delete/insert instead of update if name changes 594-- ************************************************************ 595CREATE FUNCTION tg_wslot_bu () 596 RETURNS TRIGGER 597 AS ' 598BEGIN 599 IF NEW.slotname != OLD.slotname THEN 600 DELETE FROM WSlot 601 WHERE slotname = OLD.slotname; 602 INSERT INTO WSlot (slotname, roomno, slotlink, backlink) 603 VALUES (NEW.slotname, NEW.roomno, NEW.slotlink, NEW.backlink); 604 RETURN NULL; 605 END IF; 606 RETURN new; 607END; 608' 609LANGUAGE plpgsql; 610 611CREATE TRIGGER tg_wslot_bu 612 BEFORE UPDATE ON WSlot FOR EACH ROW 613 EXECUTE PROCEDURE tg_Wslot_bu (); 614 615-- ************************************************************ 616-- * BEFORE UPDATE on PLine 617-- * - do delete/insert instead of update if name changes 618-- ************************************************************ 619CREATE FUNCTION tg_pline_bu () 620 RETURNS TRIGGER 621 AS ' 622BEGIN 623 IF NEW.slotname != OLD.slotname THEN 624 DELETE FROM PLine 625 WHERE slotname = OLD.slotname; 626 INSERT INTO PLine (slotname, phonenumber, comment, backlink) 627 VALUES (NEW.slotname, NEW.phonenumber, NEW.comment, NEW.backlink); 628 RETURN NULL; 629 END IF; 630 RETURN new; 631END; 632' 633LANGUAGE plpgsql; 634 635CREATE TRIGGER tg_pline_bu 636 BEFORE UPDATE ON PLine FOR EACH ROW 637 EXECUTE PROCEDURE tg_pline_bu (); 638 639-- ************************************************************ 640-- * BEFORE UPDATE on IFace 641-- * - do delete/insert instead of update if name changes 642-- ************************************************************ 643CREATE FUNCTION tg_iface_bu () 644 RETURNS TRIGGER 645 AS ' 646BEGIN 647 IF NEW.slotname != OLD.slotname THEN 648 DELETE FROM IFace 649 WHERE slotname = OLD.slotname; 650 INSERT INTO IFace (slotname, sysname, ifname, slotlink) 651 VALUES (NEW.slotname, NEW.sysname, NEW.ifname, NEW.slotlink); 652 RETURN NULL; 653 END IF; 654 RETURN new; 655END; 656' 657LANGUAGE plpgsql; 658 659CREATE TRIGGER tg_iface_bu 660 BEFORE UPDATE ON IFace FOR EACH ROW 661 EXECUTE PROCEDURE tg_iface_bu (); 662 663-- ************************************************************ 664-- * BEFORE UPDATE on HSlot 665-- * - do delete/insert instead of update if name changes 666-- ************************************************************ 667CREATE FUNCTION tg_hslot_bu () 668 RETURNS TRIGGER 669 AS ' 670BEGIN 671 IF NEW.slotname != OLD.slotname OR NEW.hubname != OLD.hubname THEN 672 DELETE FROM HSlot 673 WHERE slotname = OLD.slotname; 674 INSERT INTO HSlot (slotname, hubname, slotno, slotlink) 675 VALUES (NEW.slotname, NEW.hubname, NEW.slotno, NEW.slotlink); 676 RETURN NULL; 677 END IF; 678 RETURN new; 679END; 680' 681LANGUAGE plpgsql; 682 683CREATE TRIGGER tg_hslot_bu 684 BEFORE UPDATE ON HSlot FOR EACH ROW 685 EXECUTE PROCEDURE tg_hslot_bu (); 686 687-- ************************************************************ 688-- * BEFORE UPDATE on PHone 689-- * - do delete/insert instead of update if name changes 690-- ************************************************************ 691CREATE FUNCTION tg_phone_bu () 692 RETURNS TRIGGER 693 AS ' 694BEGIN 695 IF NEW.slotname != OLD.slotname THEN 696 DELETE FROM PHone 697 WHERE slotname = OLD.slotname; 698 INSERT INTO PHone (slotname, comment, slotlink) 699 VALUES (NEW.slotname, NEW.comment, NEW.slotlink); 700 RETURN NULL; 701 END IF; 702 RETURN new; 703END; 704' 705LANGUAGE plpgsql; 706 707CREATE TRIGGER tg_phone_bu 708 BEFORE UPDATE ON PHone FOR EACH ROW 709 EXECUTE PROCEDURE tg_phone_bu (); 710 711-- ************************************************************ 712-- * AFTER INSERT or UPDATE or DELETE on slot with backlink 713-- * - Ensure that the opponent correctly points back to us 714-- ************************************************************ 715CREATE FUNCTION tg_backlink_a () 716 RETURNS TRIGGER 717 AS ' 718DECLARE 719 dummy integer; 720BEGIN 721 IF tg_op = ''INSERT'' THEN 722 IF NEW.backlink != '''' THEN 723 dummy := tg_backlink_set (NEW.backlink, NEW.slotname); 724 END IF; 725 RETURN new; 726 END IF; 727 IF tg_op = ''UPDATE'' THEN 728 IF NEW.backlink != OLD.backlink THEN 729 IF OLD.backlink != '''' THEN 730 dummy := tg_backlink_unset (OLD.backlink, OLD.slotname); 731 END IF; 732 IF NEW.backlink != '''' THEN 733 dummy := tg_backlink_set (NEW.backlink, NEW.slotname); 734 END IF; 735 ELSE 736 IF NEW.slotname != OLD.slotname AND NEW.backlink != '''' THEN 737 dummy := tg_slotlink_set (NEW.backlink, NEW.slotname); 738 END IF; 739 END IF; 740 RETURN new; 741 END IF; 742 IF tg_op = ''DELETE'' THEN 743 IF OLD.backlink != '''' THEN 744 dummy := tg_backlink_unset (OLD.backlink, OLD.slotname); 745 END IF; 746 RETURN old; 747 END IF; 748END; 749' 750LANGUAGE plpgsql; 751 752CREATE TRIGGER tg_backlink_a 753 AFTER INSERT OR UPDATE OR DELETE ON PSlot FOR EACH ROW 754 EXECUTE PROCEDURE tg_backlink_a ('PS'); 755 756CREATE TRIGGER tg_backlink_a 757 AFTER INSERT OR UPDATE OR DELETE ON WSlot FOR EACH ROW 758 EXECUTE PROCEDURE tg_backlink_a ('WS'); 759 760CREATE TRIGGER tg_backlink_a 761 AFTER INSERT OR UPDATE OR DELETE ON PLine FOR EACH ROW 762 EXECUTE PROCEDURE tg_backlink_a ('PL'); 763 764-- ************************************************************ 765-- * Support function to set the opponents backlink field 766-- * if it does not already point to the requested slot 767-- ************************************************************ 768CREATE FUNCTION tg_backlink_set (myname bpchar, blname bpchar) 769 RETURNS integer 770 AS ' 771DECLARE 772 mytype char(2); 773 link char(4); 774 rec record; 775BEGIN 776 mytype := substr(myname, 1, 2); 777 link := mytype || substr(blname, 1, 2); 778 IF link = ''PLPL'' THEN 779 RAISE EXCEPTION ''backlink between two phone lines does not make sense''; 780 END IF; 781 IF link IN (''PLWS'', ''WSPL'') THEN 782 RAISE EXCEPTION ''direct link of phone line to wall slot not permitted''; 783 END IF; 784 IF mytype = ''PS'' THEN 785 SELECT 786 INTO rec * 787 FROM 788 PSlot 789 WHERE 790 slotname = myname; 791 IF NOT found THEN 792 RAISE EXCEPTION ''% does not exist'', myname; 793 END IF; 794 IF rec.backlink != blname THEN 795 UPDATE 796 PSlot 797 SET 798 backlink = blname 799 WHERE 800 slotname = myname; 801 END IF; 802 RETURN 0; 803 END IF; 804 IF mytype = ''WS'' THEN 805 SELECT 806 INTO rec * 807 FROM 808 WSlot 809 WHERE 810 slotname = myname; 811 IF NOT found THEN 812 RAISE EXCEPTION ''% does not exist'', myname; 813 END IF; 814 IF rec.backlink != blname THEN 815 UPDATE 816 WSlot 817 SET 818 backlink = blname 819 WHERE 820 slotname = myname; 821 END IF; 822 RETURN 0; 823 END IF; 824 IF mytype = ''PL'' THEN 825 SELECT 826 INTO rec * 827 FROM 828 PLine 829 WHERE 830 slotname = myname; 831 IF NOT found THEN 832 RAISE EXCEPTION ''% does not exist'', myname; 833 END IF; 834 IF rec.backlink != blname THEN 835 UPDATE 836 PLine 837 SET 838 backlink = blname 839 WHERE 840 slotname = myname; 841 END IF; 842 RETURN 0; 843 END IF; 844 RAISE EXCEPTION ''illegal backlink beginning with %'', mytype; 845END; 846' 847LANGUAGE plpgsql; 848 849-- ************************************************************ 850-- * Support function to clear out the backlink field if 851-- * it still points to specific slot 852-- ************************************************************ 853CREATE FUNCTION tg_backlink_unset (bpchar, bpchar) 854 RETURNS integer 855 AS ' 856DECLARE 857 myname alias FOR $1; 858 blname alias FOR $2; 859 mytype char(2); 860 rec record; 861BEGIN 862 mytype := substr(myname, 1, 2); 863 IF mytype = ''PS'' THEN 864 SELECT 865 INTO rec * 866 FROM 867 PSlot 868 WHERE 869 slotname = myname; 870 IF NOT found THEN 871 RETURN 0; 872 END IF; 873 IF rec.backlink = blname THEN 874 UPDATE 875 PSlot 876 SET 877 backlink = '''' 878 WHERE 879 slotname = myname; 880 END IF; 881 RETURN 0; 882 END IF; 883 IF mytype = ''WS'' THEN 884 SELECT 885 INTO rec * 886 FROM 887 WSlot 888 WHERE 889 slotname = myname; 890 IF NOT found THEN 891 RETURN 0; 892 END IF; 893 IF rec.backlink = blname THEN 894 UPDATE 895 WSlot 896 SET 897 backlink = '''' 898 WHERE 899 slotname = myname; 900 END IF; 901 RETURN 0; 902 END IF; 903 IF mytype = ''PL'' THEN 904 SELECT 905 INTO rec * 906 FROM 907 PLine 908 WHERE 909 slotname = myname; 910 IF NOT found THEN 911 RETURN 0; 912 END IF; 913 IF rec.backlink = blname THEN 914 UPDATE 915 PLine 916 SET 917 backlink = '''' 918 WHERE 919 slotname = myname; 920 END IF; 921 RETURN 0; 922 END IF; 923END 924' 925LANGUAGE plpgsql; 926 927-- ************************************************************ 928-- * AFTER INSERT or UPDATE or DELETE on slot with slotlink 929-- * - Ensure that the opponent correctly points back to us 930-- ************************************************************ 931CREATE FUNCTION tg_slotlink_a () 932 RETURNS TRIGGER 933 AS ' 934DECLARE 935 dummy integer; 936BEGIN 937 IF tg_op = ''INSERT'' THEN 938 IF NEW.slotlink != '''' THEN 939 dummy := tg_slotlink_set (NEW.slotlink, NEW.slotname); 940 END IF; 941 RETURN new; 942 END IF; 943 IF tg_op = ''UPDATE'' THEN 944 IF NEW.slotlink != OLD.slotlink THEN 945 IF OLD.slotlink != '''' THEN 946 dummy := tg_slotlink_unset (OLD.slotlink, OLD.slotname); 947 END IF; 948 IF NEW.slotlink != '''' THEN 949 dummy := tg_slotlink_set (NEW.slotlink, NEW.slotname); 950 END IF; 951 ELSE 952 IF NEW.slotname != OLD.slotname AND NEW.slotlink != '''' THEN 953 dummy := tg_slotlink_set (NEW.slotlink, NEW.slotname); 954 END IF; 955 END IF; 956 RETURN new; 957 END IF; 958 IF tg_op = ''DELETE'' THEN 959 IF OLD.slotlink != '''' THEN 960 dummy := tg_slotlink_unset (OLD.slotlink, OLD.slotname); 961 END IF; 962 RETURN old; 963 END IF; 964END; 965' 966LANGUAGE plpgsql; 967 968CREATE TRIGGER tg_slotlink_a 969 AFTER INSERT OR UPDATE OR DELETE ON PSlot FOR EACH ROW 970 EXECUTE PROCEDURE tg_slotlink_a ('PS'); 971 972CREATE TRIGGER tg_slotlink_a 973 AFTER INSERT OR UPDATE OR DELETE ON WSlot FOR EACH ROW 974 EXECUTE PROCEDURE tg_slotlink_a ('WS'); 975 976CREATE TRIGGER tg_slotlink_a 977 AFTER INSERT OR UPDATE OR DELETE ON IFace FOR EACH ROW 978 EXECUTE PROCEDURE tg_slotlink_a ('IF'); 979 980CREATE TRIGGER tg_slotlink_a 981 AFTER INSERT OR UPDATE OR DELETE ON HSlot FOR EACH ROW 982 EXECUTE PROCEDURE tg_slotlink_a ('HS'); 983 984CREATE TRIGGER tg_slotlink_a 985 AFTER INSERT OR UPDATE OR DELETE ON PHone FOR EACH ROW 986 EXECUTE PROCEDURE tg_slotlink_a ('PH'); 987 988-- ************************************************************ 989-- * Support function to set the opponents slotlink field 990-- * if it does not already point to the requested slot 991-- ************************************************************ 992CREATE FUNCTION tg_slotlink_set (bpchar, bpchar) 993 RETURNS integer 994 AS ' 995DECLARE 996 myname alias FOR $1; 997 blname alias FOR $2; 998 mytype char(2); 999 link char(4); 1000 rec record; 1001BEGIN 1002 mytype := substr(myname, 1, 2); 1003 link := mytype || substr(blname, 1, 2); 1004 IF link = ''PHPH'' THEN 1005 RAISE EXCEPTION ''slotlink between two phones does not make sense''; 1006 END IF; 1007 IF link IN (''PHHS'', ''HSPH'') THEN 1008 RAISE EXCEPTION ''link of phone to hub does not make sense''; 1009 END IF; 1010 IF link IN (''PHIF'', ''IFPH'') THEN 1011 RAISE EXCEPTION ''link of phone to hub does not make sense''; 1012 END IF; 1013 IF link IN (''PSWS'', ''WSPS'') THEN 1014 RAISE EXCEPTION ''slotlink from patchslot to wallslot not permitted''; 1015 END IF; 1016 IF mytype = ''PS'' THEN 1017 SELECT 1018 INTO rec * 1019 FROM 1020 PSlot 1021 WHERE 1022 slotname = myname; 1023 IF NOT found THEN 1024 RAISE EXCEPTION ''% does not exist'', myname; 1025 END IF; 1026 IF rec.slotlink != blname THEN 1027 UPDATE 1028 PSlot 1029 SET 1030 slotlink = blname 1031 WHERE 1032 slotname = myname; 1033 END IF; 1034 RETURN 0; 1035 END IF; 1036 IF mytype = ''WS'' THEN 1037 SELECT 1038 INTO rec * 1039 FROM 1040 WSlot 1041 WHERE 1042 slotname = myname; 1043 IF NOT found THEN 1044 RAISE EXCEPTION ''% does not exist'', myname; 1045 END IF; 1046 IF rec.slotlink != blname THEN 1047 UPDATE 1048 WSlot 1049 SET 1050 slotlink = blname 1051 WHERE 1052 slotname = myname; 1053 END IF; 1054 RETURN 0; 1055 END IF; 1056 IF mytype = ''IF'' THEN 1057 SELECT 1058 INTO rec * 1059 FROM 1060 IFace 1061 WHERE 1062 slotname = myname; 1063 IF NOT found THEN 1064 RAISE EXCEPTION ''% does not exist'', myname; 1065 END IF; 1066 IF rec.slotlink != blname THEN 1067 UPDATE 1068 IFace 1069 SET 1070 slotlink = blname 1071 WHERE 1072 slotname = myname; 1073 END IF; 1074 RETURN 0; 1075 END IF; 1076 IF mytype = ''HS'' THEN 1077 SELECT 1078 INTO rec * 1079 FROM 1080 HSlot 1081 WHERE 1082 slotname = myname; 1083 IF NOT found THEN 1084 RAISE EXCEPTION ''% does not exist'', myname; 1085 END IF; 1086 IF rec.slotlink != blname THEN 1087 UPDATE 1088 HSlot 1089 SET 1090 slotlink = blname 1091 WHERE 1092 slotname = myname; 1093 END IF; 1094 RETURN 0; 1095 END IF; 1096 IF mytype = ''PH'' THEN 1097 SELECT 1098 INTO rec * 1099 FROM 1100 PHone 1101 WHERE 1102 slotname = myname; 1103 IF NOT found THEN 1104 RAISE EXCEPTION ''% does not exist'', myname; 1105 END IF; 1106 IF rec.slotlink != blname THEN 1107 UPDATE 1108 PHone 1109 SET 1110 slotlink = blname 1111 WHERE 1112 slotname = myname; 1113 END IF; 1114 RETURN 0; 1115 END IF; 1116 RAISE EXCEPTION ''illegal slotlink beginning with %'', mytype; 1117END; 1118' 1119LANGUAGE plpgsql; 1120 1121-- ************************************************************ 1122-- * Support function to clear out the slotlink field if 1123-- * it still points to specific slot 1124-- ************************************************************ 1125CREATE FUNCTION tg_slotlink_unset (bpchar, bpchar) 1126 RETURNS integer 1127 AS ' 1128DECLARE 1129 myname alias FOR $1; 1130 blname alias FOR $2; 1131 mytype char(2); 1132 rec record; 1133BEGIN 1134 mytype := substr(myname, 1, 2); 1135 IF mytype = ''PS'' THEN 1136 SELECT 1137 INTO rec * 1138 FROM 1139 PSlot 1140 WHERE 1141 slotname = myname; 1142 IF NOT found THEN 1143 RETURN 0; 1144 END IF; 1145 IF rec.slotlink = blname THEN 1146 UPDATE 1147 PSlot 1148 SET 1149 slotlink = '''' 1150 WHERE 1151 slotname = myname; 1152 END IF; 1153 RETURN 0; 1154 END IF; 1155 IF mytype = ''WS'' THEN 1156 SELECT 1157 INTO rec * 1158 FROM 1159 WSlot 1160 WHERE 1161 slotname = myname; 1162 IF NOT found THEN 1163 RETURN 0; 1164 END IF; 1165 IF rec.slotlink = blname THEN 1166 UPDATE 1167 WSlot 1168 SET 1169 slotlink = '''' 1170 WHERE 1171 slotname = myname; 1172 END IF; 1173 RETURN 0; 1174 END IF; 1175 IF mytype = ''IF'' THEN 1176 SELECT 1177 INTO rec * 1178 FROM 1179 IFace 1180 WHERE 1181 slotname = myname; 1182 IF NOT found THEN 1183 RETURN 0; 1184 END IF; 1185 IF rec.slotlink = blname THEN 1186 UPDATE 1187 IFace 1188 SET 1189 slotlink = '''' 1190 WHERE 1191 slotname = myname; 1192 END IF; 1193 RETURN 0; 1194 END IF; 1195 IF mytype = ''HS'' THEN 1196 SELECT 1197 INTO rec * 1198 FROM 1199 HSlot 1200 WHERE 1201 slotname = myname; 1202 IF NOT found THEN 1203 RETURN 0; 1204 END IF; 1205 IF rec.slotlink = blname THEN 1206 UPDATE 1207 HSlot 1208 SET 1209 slotlink = '''' 1210 WHERE 1211 slotname = myname; 1212 END IF; 1213 RETURN 0; 1214 END IF; 1215 IF mytype = ''PH'' THEN 1216 SELECT 1217 INTO rec * 1218 FROM 1219 PHone 1220 WHERE 1221 slotname = myname; 1222 IF NOT found THEN 1223 RETURN 0; 1224 END IF; 1225 IF rec.slotlink = blname THEN 1226 UPDATE 1227 PHone 1228 SET 1229 slotlink = '''' 1230 WHERE 1231 slotname = myname; 1232 END IF; 1233 RETURN 0; 1234 END IF; 1235END; 1236' 1237LANGUAGE plpgsql; 1238 1239-- ************************************************************ 1240-- * Describe the backside of a patchfield slot 1241-- ************************************************************ 1242CREATE FUNCTION pslot_backlink_view (bpchar) 1243 RETURNS text 1244 AS ' 1245 << 1246 OUTER >> 1247DECLARE 1248 rec record; 1249 bltype char(2); 1250 retval text; 1251BEGIN 1252 SELECT 1253 INTO rec * 1254 FROM 1255 PSlot 1256 WHERE 1257 slotname = $1; 1258 IF NOT found THEN 1259 RETURN ''''; 1260 END IF; 1261 IF rec.backlink = '''' THEN 1262 RETURN ''-''; 1263 END IF; 1264 bltype := substr(rec.backlink, 1, 2); 1265 IF bltype = ''PL'' THEN 1266 DECLARE rec record; 1267 BEGIN 1268 SELECT 1269 INTO rec * 1270 FROM 1271 PLine 1272 WHERE 1273 slotname = "outer".rec.backlink; 1274 retval := ''Phone line '' || trim(rec.phonenumber); 1275 IF rec.comment != '''' THEN 1276 retval := retval || '' (''; 1277 retval := retval || rec.comment; 1278 retval := retval || '')''; 1279 END IF; 1280 RETURN retval; 1281 END; 1282 END IF; 1283 IF bltype = ''WS'' THEN 1284 SELECT 1285 INTO rec * 1286 FROM 1287 WSlot 1288 WHERE 1289 slotname = rec.backlink; 1290 retval := trim(rec.slotname) || '' in room ''; 1291 retval := retval || trim(rec.roomno); 1292 retval := retval || '' -> ''; 1293 RETURN retval || wslot_slotlink_view (rec.slotname); 1294 END IF; 1295 RETURN rec.backlink; 1296END; 1297' 1298LANGUAGE plpgsql; 1299 1300-- ************************************************************ 1301-- * Describe the front of a patchfield slot 1302-- ************************************************************ 1303CREATE FUNCTION pslot_slotlink_view (bpchar) 1304 RETURNS text 1305 AS ' 1306DECLARE 1307 psrec record; 1308 sltype char(2); 1309 retval text; 1310BEGIN 1311 SELECT 1312 INTO psrec * 1313 FROM 1314 PSlot 1315 WHERE 1316 slotname = $1; 1317 IF NOT found THEN 1318 RETURN ''''; 1319 END IF; 1320 IF psrec.slotlink = '''' THEN 1321 RETURN ''-''; 1322 END IF; 1323 sltype := substr(psrec.slotlink, 1, 2); 1324 IF sltype = ''PS'' THEN 1325 retval := trim(psrec.slotlink) || '' -> ''; 1326 RETURN retval || pslot_backlink_view (psrec.slotlink); 1327 END IF; 1328 IF sltype = ''HS'' THEN 1329 retval := comment 1330 FROM 1331 Hub H, 1332 HSlot HS 1333 WHERE 1334 HS.slotname = psrec.slotlink 1335 AND H.name = HS.hubname; 1336 retval := retval || '' slot ''; 1337 retval := retval || slotno::text 1338 FROM 1339 HSlot 1340 WHERE 1341 slotname = psrec.slotlink; 1342 RETURN retval; 1343 END IF; 1344 RETURN psrec.slotlink; 1345END; 1346' 1347LANGUAGE plpgsql; 1348 1349-- ************************************************************ 1350-- * Describe the front of a wall connector slot 1351-- ************************************************************ 1352CREATE FUNCTION wslot_slotlink_view (bpchar) 1353 RETURNS text 1354 AS ' 1355DECLARE 1356 rec record; 1357 sltype char(2); 1358 retval text; 1359BEGIN 1360 SELECT 1361 INTO rec * 1362 FROM 1363 WSlot 1364 WHERE 1365 slotname = $1; 1366 IF NOT found THEN 1367 RETURN ''''; 1368 END IF; 1369 IF rec.slotlink = '''' THEN 1370 RETURN ''-''; 1371 END IF; 1372 sltype := substr(rec.slotlink, 1, 2); 1373 IF sltype = ''PH'' THEN 1374 SELECT 1375 INTO rec * 1376 FROM 1377 PHone 1378 WHERE 1379 slotname = rec.slotlink; 1380 retval := ''Phone '' || trim(rec.slotname); 1381 IF rec.comment != '''' THEN 1382 retval := retval || '' (''; 1383 retval := retval || rec.comment; 1384 retval := retval || '')''; 1385 END IF; 1386 RETURN retval; 1387 END IF; 1388 IF sltype = ''IF'' THEN 1389 DECLARE syrow System%RowType; 1390 ifrow IFace%ROWTYPE; 1391 BEGIN 1392 SELECT 1393 INTO ifrow * 1394 FROM 1395 IFace 1396 WHERE 1397 slotname = rec.slotlink; 1398 SELECT 1399 INTO syrow * 1400 FROM 1401 SYSTEM 1402 WHERE 1403 name = ifrow.sysname; 1404 retval := syrow.name || '' IF ''; 1405 retval := retval || ifrow.ifname; 1406 IF syrow.comment != '''' THEN 1407 retval := retval || '' (''; 1408 retval := retval || syrow.comment; 1409 retval := retval || '')''; 1410 END IF; 1411 RETURN retval; 1412 END; 1413 END IF; 1414 RETURN rec.slotlink; 1415END; 1416' 1417LANGUAGE plpgsql; 1418 1419-- ************************************************************ 1420-- * View of a patchfield describing backside and patches 1421-- ************************************************************ 1422CREATE VIEW Pfield_v1 AS 1423SELECT 1424 PF.pfname, 1425 PF.slotname, 1426 pslot_backlink_view (PF.slotname) AS backside, 1427 pslot_slotlink_view (PF.slotname) AS patch 1428FROM 1429 PSlot PF; 1430 1431-- 1432-- First we build the house - so we create the rooms 1433-- 1434INSERT INTO Room 1435 VALUES ('001', 'Entrance'); 1436 1437INSERT INTO Room 1438 VALUES ('002', 'Office'); 1439 1440INSERT INTO Room 1441 VALUES ('003', 'Office'); 1442 1443INSERT INTO Room 1444 VALUES ('004', 'Technical'); 1445 1446INSERT INTO Room 1447 VALUES ('101', 'Office'); 1448 1449INSERT INTO Room 1450 VALUES ('102', 'Conference'); 1451 1452INSERT INTO Room 1453 VALUES ('103', 'Restroom'); 1454 1455INSERT INTO Room 1456 VALUES ('104', 'Technical'); 1457 1458INSERT INTO Room 1459 VALUES ('105', 'Office'); 1460 1461INSERT INTO Room 1462 VALUES ('106', 'Office'); 1463 1464-- 1465-- Second we install the wall connectors 1466-- 1467INSERT INTO WSlot 1468 VALUES ('WS.001.1a', '001', '', ''); 1469 1470INSERT INTO WSlot 1471 VALUES ('WS.001.1b', '001', '', ''); 1472 1473INSERT INTO WSlot 1474 VALUES ('WS.001.2a', '001', '', ''); 1475 1476INSERT INTO WSlot 1477 VALUES ('WS.001.2b', '001', '', ''); 1478 1479INSERT INTO WSlot 1480 VALUES ('WS.001.3a', '001', '', ''); 1481 1482INSERT INTO WSlot 1483 VALUES ('WS.001.3b', '001', '', ''); 1484 1485INSERT INTO WSlot 1486 VALUES ('WS.002.1a', '002', '', ''); 1487 1488INSERT INTO WSlot 1489 VALUES ('WS.002.1b', '002', '', ''); 1490 1491INSERT INTO WSlot 1492 VALUES ('WS.002.2a', '002', '', ''); 1493 1494INSERT INTO WSlot 1495 VALUES ('WS.002.2b', '002', '', ''); 1496 1497INSERT INTO WSlot 1498 VALUES ('WS.002.3a', '002', '', ''); 1499 1500INSERT INTO WSlot 1501 VALUES ('WS.002.3b', '002', '', ''); 1502 1503INSERT INTO WSlot 1504 VALUES ('WS.003.1a', '003', '', ''); 1505 1506INSERT INTO WSlot 1507 VALUES ('WS.003.1b', '003', '', ''); 1508 1509INSERT INTO WSlot 1510 VALUES ('WS.003.2a', '003', '', ''); 1511 1512INSERT INTO WSlot 1513 VALUES ('WS.003.2b', '003', '', ''); 1514 1515INSERT INTO WSlot 1516 VALUES ('WS.003.3a', '003', '', ''); 1517 1518INSERT INTO WSlot 1519 VALUES ('WS.003.3b', '003', '', ''); 1520 1521INSERT INTO WSlot 1522 VALUES ('WS.101.1a', '101', '', ''); 1523 1524INSERT INTO WSlot 1525 VALUES ('WS.101.1b', '101', '', ''); 1526 1527INSERT INTO WSlot 1528 VALUES ('WS.101.2a', '101', '', ''); 1529 1530INSERT INTO WSlot 1531 VALUES ('WS.101.2b', '101', '', ''); 1532 1533INSERT INTO WSlot 1534 VALUES ('WS.101.3a', '101', '', ''); 1535 1536INSERT INTO WSlot 1537 VALUES ('WS.101.3b', '101', '', ''); 1538 1539INSERT INTO WSlot 1540 VALUES ('WS.102.1a', '102', '', ''); 1541 1542INSERT INTO WSlot 1543 VALUES ('WS.102.1b', '102', '', ''); 1544 1545INSERT INTO WSlot 1546 VALUES ('WS.102.2a', '102', '', ''); 1547 1548INSERT INTO WSlot 1549 VALUES ('WS.102.2b', '102', '', ''); 1550 1551INSERT INTO WSlot 1552 VALUES ('WS.102.3a', '102', '', ''); 1553 1554INSERT INTO WSlot 1555 VALUES ('WS.102.3b', '102', '', ''); 1556 1557INSERT INTO WSlot 1558 VALUES ('WS.105.1a', '105', '', ''); 1559 1560INSERT INTO WSlot 1561 VALUES ('WS.105.1b', '105', '', ''); 1562 1563INSERT INTO WSlot 1564 VALUES ('WS.105.2a', '105', '', ''); 1565 1566INSERT INTO WSlot 1567 VALUES ('WS.105.2b', '105', '', ''); 1568 1569INSERT INTO WSlot 1570 VALUES ('WS.105.3a', '105', '', ''); 1571 1572INSERT INTO WSlot 1573 VALUES ('WS.105.3b', '105', '', ''); 1574 1575INSERT INTO WSlot 1576 VALUES ('WS.106.1a', '106', '', ''); 1577 1578INSERT INTO WSlot 1579 VALUES ('WS.106.1b', '106', '', ''); 1580 1581INSERT INTO WSlot 1582 VALUES ('WS.106.2a', '106', '', ''); 1583 1584INSERT INTO WSlot 1585 VALUES ('WS.106.2b', '106', '', ''); 1586 1587INSERT INTO WSlot 1588 VALUES ('WS.106.3a', '106', '', ''); 1589 1590INSERT INTO WSlot 1591 VALUES ('WS.106.3b', '106', '', ''); 1592 1593-- 1594-- Now create the patch fields and their slots 1595-- 1596INSERT INTO PField 1597 VALUES ('PF0_1', 'Wallslots basement'); 1598 1599-- 1600-- The cables for these will be made later, so they are unconnected for now 1601-- 1602INSERT INTO PSlot 1603 VALUES ('PS.base.a1', 'PF0_1', '', ''); 1604 1605INSERT INTO PSlot 1606 VALUES ('PS.base.a2', 'PF0_1', '', ''); 1607 1608INSERT INTO PSlot 1609 VALUES ('PS.base.a3', 'PF0_1', '', ''); 1610 1611INSERT INTO PSlot 1612 VALUES ('PS.base.a4', 'PF0_1', '', ''); 1613 1614INSERT INTO PSlot 1615 VALUES ('PS.base.a5', 'PF0_1', '', ''); 1616 1617INSERT INTO PSlot 1618 VALUES ('PS.base.a6', 'PF0_1', '', ''); 1619 1620-- 1621-- These are already wired to the wall connectors 1622-- 1623INSERT INTO PSlot 1624 VALUES ('PS.base.b1', 'PF0_1', '', 'WS.002.1a'); 1625 1626INSERT INTO PSlot 1627 VALUES ('PS.base.b2', 'PF0_1', '', 'WS.002.1b'); 1628 1629INSERT INTO PSlot 1630 VALUES ('PS.base.b3', 'PF0_1', '', 'WS.002.2a'); 1631 1632INSERT INTO PSlot 1633 VALUES ('PS.base.b4', 'PF0_1', '', 'WS.002.2b'); 1634 1635INSERT INTO PSlot 1636 VALUES ('PS.base.b5', 'PF0_1', '', 'WS.002.3a'); 1637 1638INSERT INTO PSlot 1639 VALUES ('PS.base.b6', 'PF0_1', '', 'WS.002.3b'); 1640 1641INSERT INTO PSlot 1642 VALUES ('PS.base.c1', 'PF0_1', '', 'WS.003.1a'); 1643 1644INSERT INTO PSlot 1645 VALUES ('PS.base.c2', 'PF0_1', '', 'WS.003.1b'); 1646 1647INSERT INTO PSlot 1648 VALUES ('PS.base.c3', 'PF0_1', '', 'WS.003.2a'); 1649 1650INSERT INTO PSlot 1651 VALUES ('PS.base.c4', 'PF0_1', '', 'WS.003.2b'); 1652 1653INSERT INTO PSlot 1654 VALUES ('PS.base.c5', 'PF0_1', '', 'WS.003.3a'); 1655 1656INSERT INTO PSlot 1657 VALUES ('PS.base.c6', 'PF0_1', '', 'WS.003.3b'); 1658 1659-- 1660-- This patchfield will be renamed later into PF0_2 - so its 1661-- slots references in pfname should follow 1662-- 1663INSERT INTO PField 1664 VALUES ('PF0_X', 'Phonelines basement'); 1665 1666INSERT INTO PSlot 1667 VALUES ('PS.base.ta1', 'PF0_X', '', ''); 1668 1669INSERT INTO PSlot 1670 VALUES ('PS.base.ta2', 'PF0_X', '', ''); 1671 1672INSERT INTO PSlot 1673 VALUES ('PS.base.ta3', 'PF0_X', '', ''); 1674 1675INSERT INTO PSlot 1676 VALUES ('PS.base.ta4', 'PF0_X', '', ''); 1677 1678INSERT INTO PSlot 1679 VALUES ('PS.base.ta5', 'PF0_X', '', ''); 1680 1681INSERT INTO PSlot 1682 VALUES ('PS.base.ta6', 'PF0_X', '', ''); 1683 1684INSERT INTO PSlot 1685 VALUES ('PS.base.tb1', 'PF0_X', '', ''); 1686 1687INSERT INTO PSlot 1688 VALUES ('PS.base.tb2', 'PF0_X', '', ''); 1689 1690INSERT INTO PSlot 1691 VALUES ('PS.base.tb3', 'PF0_X', '', ''); 1692 1693INSERT INTO PSlot 1694 VALUES ('PS.base.tb4', 'PF0_X', '', ''); 1695 1696INSERT INTO PSlot 1697 VALUES ('PS.base.tb5', 'PF0_X', '', ''); 1698 1699INSERT INTO PSlot 1700 VALUES ('PS.base.tb6', 'PF0_X', '', ''); 1701 1702INSERT INTO PField 1703 VALUES ('PF1_1', 'Wallslots first floor'); 1704 1705INSERT INTO PSlot 1706 VALUES ('PS.first.a1', 'PF1_1', '', 'WS.101.1a'); 1707 1708INSERT INTO PSlot 1709 VALUES ('PS.first.a2', 'PF1_1', '', 'WS.101.1b'); 1710 1711INSERT INTO PSlot 1712 VALUES ('PS.first.a3', 'PF1_1', '', 'WS.101.2a'); 1713 1714INSERT INTO PSlot 1715 VALUES ('PS.first.a4', 'PF1_1', '', 'WS.101.2b'); 1716 1717INSERT INTO PSlot 1718 VALUES ('PS.first.a5', 'PF1_1', '', 'WS.101.3a'); 1719 1720INSERT INTO PSlot 1721 VALUES ('PS.first.a6', 'PF1_1', '', 'WS.101.3b'); 1722 1723INSERT INTO PSlot 1724 VALUES ('PS.first.b1', 'PF1_1', '', 'WS.102.1a'); 1725 1726INSERT INTO PSlot 1727 VALUES ('PS.first.b2', 'PF1_1', '', 'WS.102.1b'); 1728 1729INSERT INTO PSlot 1730 VALUES ('PS.first.b3', 'PF1_1', '', 'WS.102.2a'); 1731 1732INSERT INTO PSlot 1733 VALUES ('PS.first.b4', 'PF1_1', '', 'WS.102.2b'); 1734 1735INSERT INTO PSlot 1736 VALUES ('PS.first.b5', 'PF1_1', '', 'WS.102.3a'); 1737 1738INSERT INTO PSlot 1739 VALUES ('PS.first.b6', 'PF1_1', '', 'WS.102.3b'); 1740 1741INSERT INTO PSlot 1742 VALUES ('PS.first.c1', 'PF1_1', '', 'WS.105.1a'); 1743 1744INSERT INTO PSlot 1745 VALUES ('PS.first.c2', 'PF1_1', '', 'WS.105.1b'); 1746 1747INSERT INTO PSlot 1748 VALUES ('PS.first.c3', 'PF1_1', '', 'WS.105.2a'); 1749 1750INSERT INTO PSlot 1751 VALUES ('PS.first.c4', 'PF1_1', '', 'WS.105.2b'); 1752 1753INSERT INTO PSlot 1754 VALUES ('PS.first.c5', 'PF1_1', '', 'WS.105.3a'); 1755 1756INSERT INTO PSlot 1757 VALUES ('PS.first.c6', 'PF1_1', '', 'WS.105.3b'); 1758 1759INSERT INTO PSlot 1760 VALUES ('PS.first.d1', 'PF1_1', '', 'WS.106.1a'); 1761 1762INSERT INTO PSlot 1763 VALUES ('PS.first.d2', 'PF1_1', '', 'WS.106.1b'); 1764 1765INSERT INTO PSlot 1766 VALUES ('PS.first.d3', 'PF1_1', '', 'WS.106.2a'); 1767 1768INSERT INTO PSlot 1769 VALUES ('PS.first.d4', 'PF1_1', '', 'WS.106.2b'); 1770 1771INSERT INTO PSlot 1772 VALUES ('PS.first.d5', 'PF1_1', '', 'WS.106.3a'); 1773 1774INSERT INTO PSlot 1775 VALUES ('PS.first.d6', 'PF1_1', '', 'WS.106.3b'); 1776 1777-- 1778-- Now we wire the wall connectors 1a-2a in room 001 to the 1779-- patchfield. In the second update we make an error, and 1780-- correct it after 1781-- 1782UPDATE 1783 PSlot 1784SET 1785 backlink = 'WS.001.1a' 1786WHERE 1787 slotname = 'PS.base.a1'; 1788 1789UPDATE 1790 PSlot 1791SET 1792 backlink = 'WS.001.1b' 1793WHERE 1794 slotname = 'PS.base.a3'; 1795 1796SELECT 1797 * 1798FROM 1799 WSlot 1800WHERE 1801 roomno = '001' 1802ORDER BY 1803 slotname; 1804 1805SELECT 1806 * 1807FROM 1808 PSlot 1809WHERE 1810 slotname ~ 'PS.base.a' 1811ORDER BY 1812 slotname; 1813 1814UPDATE 1815 PSlot 1816SET 1817 backlink = 'WS.001.2a' 1818WHERE 1819 slotname = 'PS.base.a3'; 1820 1821SELECT 1822 * 1823FROM 1824 WSlot 1825WHERE 1826 roomno = '001' 1827ORDER BY 1828 slotname; 1829 1830SELECT 1831 * 1832FROM 1833 PSlot 1834WHERE 1835 slotname ~ 'PS.base.a' 1836ORDER BY 1837 slotname; 1838 1839UPDATE 1840 PSlot 1841SET 1842 backlink = 'WS.001.1b' 1843WHERE 1844 slotname = 'PS.base.a2'; 1845 1846SELECT 1847 * 1848FROM 1849 WSlot 1850WHERE 1851 roomno = '001' 1852ORDER BY 1853 slotname; 1854 1855SELECT 1856 * 1857FROM 1858 PSlot 1859WHERE 1860 slotname ~ 'PS.base.a' 1861ORDER BY 1862 slotname; 1863 1864-- 1865-- Same procedure for 2b-3b but this time updating the WSlot instead 1866-- of the PSlot. Due to the triggers the result is the same: 1867-- WSlot and corresponding PSlot point to each other. 1868-- 1869UPDATE 1870 WSlot 1871SET 1872 backlink = 'PS.base.a4' 1873WHERE 1874 slotname = 'WS.001.2b'; 1875 1876UPDATE 1877 WSlot 1878SET 1879 backlink = 'PS.base.a6' 1880WHERE 1881 slotname = 'WS.001.3a'; 1882 1883SELECT 1884 * 1885FROM 1886 WSlot 1887WHERE 1888 roomno = '001' 1889ORDER BY 1890 slotname; 1891 1892SELECT 1893 * 1894FROM 1895 PSlot 1896WHERE 1897 slotname ~ 'PS.base.a' 1898ORDER BY 1899 slotname; 1900 1901UPDATE 1902 WSlot 1903SET 1904 backlink = 'PS.base.a6' 1905WHERE 1906 slotname = 'WS.001.3b'; 1907 1908SELECT 1909 * 1910FROM 1911 WSlot 1912WHERE 1913 roomno = '001' 1914ORDER BY 1915 slotname; 1916 1917SELECT 1918 * 1919FROM 1920 PSlot 1921WHERE 1922 slotname ~ 'PS.base.a' 1923ORDER BY 1924 slotname; 1925 1926UPDATE 1927 WSlot 1928SET 1929 backlink = 'PS.base.a5' 1930WHERE 1931 slotname = 'WS.001.3a'; 1932 1933SELECT 1934 * 1935FROM 1936 WSlot 1937WHERE 1938 roomno = '001' 1939ORDER BY 1940 slotname; 1941 1942SELECT 1943 * 1944FROM 1945 PSlot 1946WHERE 1947 slotname ~ 'PS.base.a' 1948ORDER BY 1949 slotname; 1950 1951INSERT INTO PField 1952 VALUES ('PF1_2', 'Phonelines first floor'); 1953 1954INSERT INTO PSlot 1955 VALUES ('PS.first.ta1', 'PF1_2', '', ''); 1956 1957INSERT INTO PSlot 1958 VALUES ('PS.first.ta2', 'PF1_2', '', ''); 1959 1960INSERT INTO PSlot 1961 VALUES ('PS.first.ta3', 'PF1_2', '', ''); 1962 1963INSERT INTO PSlot 1964 VALUES ('PS.first.ta4', 'PF1_2', '', ''); 1965 1966INSERT INTO PSlot 1967 VALUES ('PS.first.ta5', 'PF1_2', '', ''); 1968 1969INSERT INTO PSlot 1970 VALUES ('PS.first.ta6', 'PF1_2', '', ''); 1971 1972INSERT INTO PSlot 1973 VALUES ('PS.first.tb1', 'PF1_2', '', ''); 1974 1975INSERT INTO PSlot 1976 VALUES ('PS.first.tb2', 'PF1_2', '', ''); 1977 1978INSERT INTO PSlot 1979 VALUES ('PS.first.tb3', 'PF1_2', '', ''); 1980 1981INSERT INTO PSlot 1982 VALUES ('PS.first.tb4', 'PF1_2', '', ''); 1983 1984INSERT INTO PSlot 1985 VALUES ('PS.first.tb5', 'PF1_2', '', ''); 1986 1987INSERT INTO PSlot 1988 VALUES ('PS.first.tb6', 'PF1_2', '', ''); 1989 1990-- 1991-- Fix the wrong name for patchfield PF0_2 1992-- 1993UPDATE 1994 PField 1995SET 1996 name = 'PF0_2' 1997WHERE 1998 name = 'PF0_X'; 1999 2000SELECT 2001 * 2002FROM 2003 PSlot 2004ORDER BY 2005 slotname; 2006 2007SELECT 2008 * 2009FROM 2010 WSlot 2011ORDER BY 2012 slotname; 2013 2014-- 2015-- Install the central phone system and create the phone numbers. 2016-- They are wired on insert to the patchfields. Again the 2017-- triggers automatically tell the PSlots to update their 2018-- backlink field. 2019-- 2020INSERT INTO PLine 2021 VALUES ('PL.001', '-0', 'Central call', 'PS.base.ta1'); 2022 2023INSERT INTO PLine 2024 VALUES ('PL.002', '-101', '', 'PS.base.ta2'); 2025 2026INSERT INTO PLine 2027 VALUES ('PL.003', '-102', '', 'PS.base.ta3'); 2028 2029INSERT INTO PLine 2030 VALUES ('PL.004', '-103', '', 'PS.base.ta5'); 2031 2032INSERT INTO PLine 2033 VALUES ('PL.005', '-104', '', 'PS.base.ta6'); 2034 2035INSERT INTO PLine 2036 VALUES ('PL.006', '-106', '', 'PS.base.tb2'); 2037 2038INSERT INTO PLine 2039 VALUES ('PL.007', '-108', '', 'PS.base.tb3'); 2040 2041INSERT INTO PLine 2042 VALUES ('PL.008', '-109', '', 'PS.base.tb4'); 2043 2044INSERT INTO PLine 2045 VALUES ('PL.009', '-121', '', 'PS.base.tb5'); 2046 2047INSERT INTO PLine 2048 VALUES ('PL.010', '-122', '', 'PS.base.tb6'); 2049 2050INSERT INTO PLine 2051 VALUES ('PL.015', '-134', '', 'PS.first.ta1'); 2052 2053INSERT INTO PLine 2054 VALUES ('PL.016', '-137', '', 'PS.first.ta3'); 2055 2056INSERT INTO PLine 2057 VALUES ('PL.017', '-139', '', 'PS.first.ta4'); 2058 2059INSERT INTO PLine 2060 VALUES ('PL.018', '-362', '', 'PS.first.tb1'); 2061 2062INSERT INTO PLine 2063 VALUES ('PL.019', '-363', '', 'PS.first.tb2'); 2064 2065INSERT INTO PLine 2066 VALUES ('PL.020', '-364', '', 'PS.first.tb3'); 2067 2068INSERT INTO PLine 2069 VALUES ('PL.021', '-365', '', 'PS.first.tb5'); 2070 2071INSERT INTO PLine 2072 VALUES ('PL.022', '-367', '', 'PS.first.tb6'); 2073 2074INSERT INTO PLine 2075 VALUES ('PL.028', '-501', 'Fax entrance', 'PS.base.ta2'); 2076 2077INSERT INTO PLine 2078 VALUES ('PL.029', '-502', 'Fax first floor', 'PS.first.ta1'); 2079 2080-- 2081-- Buy some phones, plug them into the wall and patch the 2082-- phone lines to the corresponding patchfield slots. 2083-- 2084INSERT INTO PHone 2085 VALUES ('PH.hc001', 'Hicom standard', 'WS.001.1a'); 2086 2087UPDATE 2088 PSlot 2089SET 2090 slotlink = 'PS.base.ta1' 2091WHERE 2092 slotname = 'PS.base.a1'; 2093 2094INSERT INTO PHone 2095 VALUES ('PH.hc002', 'Hicom standard', 'WS.002.1a'); 2096 2097UPDATE 2098 PSlot 2099SET 2100 slotlink = 'PS.base.ta5' 2101WHERE 2102 slotname = 'PS.base.b1'; 2103 2104INSERT INTO PHone 2105 VALUES ('PH.hc003', 'Hicom standard', 'WS.002.2a'); 2106 2107UPDATE 2108 PSlot 2109SET 2110 slotlink = 'PS.base.tb2' 2111WHERE 2112 slotname = 'PS.base.b3'; 2113 2114INSERT INTO PHone 2115 VALUES ('PH.fax001', 'Canon fax', 'WS.001.2a'); 2116 2117UPDATE 2118 PSlot 2119SET 2120 slotlink = 'PS.base.ta2' 2121WHERE 2122 slotname = 'PS.base.a3'; 2123 2124-- 2125-- Install a hub at one of the patchfields, plug a computers 2126-- ethernet interface into the wall and patch it to the hub. 2127-- 2128INSERT INTO Hub 2129 VALUES ('base.hub1', 'Patchfield PF0_1 hub', 16); 2130 2131INSERT INTO SYSTEM 2132 VALUES ('orion', 'PC'); 2133 2134INSERT INTO IFace 2135 VALUES ('IF', 'orion', 'eth0', 'WS.002.1b'); 2136 2137UPDATE 2138 PSlot 2139SET 2140 slotlink = 'HS.base.hub1.1' 2141WHERE 2142 slotname = 'PS.base.b2'; 2143 2144-- 2145-- Now we take a look at the patchfield 2146-- 2147SELECT 2148 * 2149FROM 2150 PField_v1 2151WHERE 2152 pfname = 'PF0_1' 2153ORDER BY 2154 slotname; 2155 2156SELECT 2157 * 2158FROM 2159 PField_v1 2160WHERE 2161 pfname = 'PF0_2' 2162ORDER BY 2163 slotname; 2164 2165-- 2166-- Finally we want errors 2167-- 2168INSERT INTO PField 2169 VALUES ('PF1_1', 'should fail due to unique index'); 2170 2171UPDATE 2172 PSlot 2173SET 2174 backlink = 'WS.not.there' 2175WHERE 2176 slotname = 'PS.base.a1'; 2177 2178UPDATE 2179 PSlot 2180SET 2181 backlink = 'XX.illegal' 2182WHERE 2183 slotname = 'PS.base.a1'; 2184 2185UPDATE 2186 PSlot 2187SET 2188 slotlink = 'PS.not.there' 2189WHERE 2190 slotname = 'PS.base.a1'; 2191 2192UPDATE 2193 PSlot 2194SET 2195 slotlink = 'XX.illegal' 2196WHERE 2197 slotname = 'PS.base.a1'; 2198 2199INSERT INTO HSlot 2200 VALUES ('HS', 'base.hub1', 1, ''); 2201 2202INSERT INTO HSlot 2203 VALUES ('HS', 'base.hub1', 20, ''); 2204 2205DELETE FROM HSlot; 2206 2207INSERT INTO IFace 2208 VALUES ('IF', 'notthere', 'eth0', ''); 2209 2210INSERT INTO IFace 2211 VALUES ('IF', 'orion', 'ethernet_interface_name_too_long', ''); 2212 2213-- 2214-- The following tests are unrelated to the scenario outlined above; 2215-- they merely exercise specific parts of PL/pgSQL 2216-- 2217-- 2218-- Test recursion, per bug report 7-Sep-01 2219-- 2220CREATE FUNCTION recursion_test (int, int) 2221 RETURNS text 2222 AS ' 2223DECLARE 2224 rslt text; 2225BEGIN 2226 IF $1 <= 0 THEN 2227 rslt = CAST($2 AS text); 2228 ELSE 2229 rslt = CAST($1 AS text) || '','' || recursion_test ($1 - 1, $2); 2230 END IF; 2231 RETURN rslt; 2232END; 2233' 2234LANGUAGE plpgsql; 2235 2236SELECT 2237 recursion_test (4, 3); 2238 2239-- 2240-- Test the FOUND magic variable 2241-- 2242CREATE TABLE found_test_tbl ( 2243 a int 2244); 2245 2246CREATE FUNCTION test_found () 2247 RETURNS boolean 2248 AS ' 2249DECLARE 2250BEGIN 2251 INSERT INTO found_test_tbl 2252 VALUES (1); 2253 IF FOUND THEN 2254 INSERT INTO found_test_tbl 2255 VALUES (2); 2256 END IF; 2257 UPDATE 2258 found_test_tbl 2259 SET 2260 a = 100 2261 WHERE 2262 a = 1; 2263 IF FOUND THEN 2264 INSERT INTO found_test_tbl 2265 VALUES (3); 2266 END IF; 2267 DELETE FROM found_test_tbl 2268 WHERE a = 9999; 2269 -- matches no rows 2270 IF NOT FOUND THEN 2271 INSERT INTO found_test_tbl 2272 VALUES (4); 2273 END IF; 2274 FOR i IN 1..10 LOOP 2275 -- no need to do anything 2276 END LOOP; 2277 IF FOUND THEN 2278 INSERT INTO found_test_tbl 2279 VALUES (5); 2280 END IF; 2281 -- never executes the loop 2282 FOR i IN 2..1 LOOP 2283 -- no need to do anything 2284 END LOOP; 2285 IF NOT FOUND THEN 2286 INSERT INTO found_test_tbl 2287 VALUES (6); 2288 END IF; 2289 RETURN TRUE; 2290END; 2291' 2292LANGUAGE plpgsql; 2293 2294SELECT 2295 test_found (); 2296 2297SELECT 2298 * 2299FROM 2300 found_test_tbl; 2301 2302-- 2303-- Test set-returning functions for PL/pgSQL 2304-- 2305CREATE FUNCTION test_table_func_rec () 2306 RETURNS SETOF found_test_tbl 2307 AS ' 2308DECLARE 2309 rec RECORD; 2310BEGIN 2311 FOR rec IN 2312 SELECT 2313 * 2314 FROM 2315 found_test_tbl LOOP 2316 RETURN NEXT rec; 2317 END LOOP; 2318 RETURN; 2319END; 2320' 2321LANGUAGE plpgsql; 2322 2323SELECT 2324 * 2325FROM 2326 test_table_func_rec (); 2327 2328CREATE FUNCTION test_table_func_row () 2329 RETURNS SETOF found_test_tbl 2330 AS ' 2331DECLARE 2332 ROW found_test_tbl%ROWTYPE; 2333BEGIN 2334 FOR ROW IN 2335 SELECT 2336 * 2337 FROM 2338 found_test_tbl LOOP 2339 RETURN NEXT ROW; 2340 END LOOP; 2341 RETURN; 2342END; 2343' 2344LANGUAGE plpgsql; 2345 2346SELECT 2347 * 2348FROM 2349 test_table_func_row (); 2350 2351CREATE FUNCTION test_ret_set_scalar (int, int) 2352 RETURNS SETOF int 2353 AS ' 2354DECLARE 2355 i int; 2356BEGIN 2357 FOR i IN $1..$2 LOOP 2358 RETURN NEXT i + 1; 2359 END LOOP; 2360 RETURN; 2361END; 2362' 2363LANGUAGE plpgsql; 2364 2365SELECT 2366 * 2367FROM 2368 test_ret_set_scalar (1, 10); 2369 2370CREATE FUNCTION test_ret_set_rec_dyn (int) 2371 RETURNS SETOF record 2372 AS ' 2373DECLARE 2374 retval RECORD; 2375BEGIN 2376 IF $1 > 10 THEN 2377 SELECT 2378 INTO retval 5, 2379 10, 2380 15; 2381 RETURN NEXT retval; 2382 RETURN NEXT retval; 2383 ELSE 2384 SELECT 2385 INTO retval 50, 2386 5::numeric, 2387 ''xxx''::text; 2388 RETURN NEXT retval; 2389 RETURN NEXT retval; 2390 END IF; 2391 RETURN; 2392END; 2393' 2394LANGUAGE plpgsql; 2395 2396SELECT 2397 * 2398FROM 2399 test_ret_set_rec_dyn (1500) 2400 AS (a int, b int, c int); 2401 2402SELECT 2403 * 2404FROM 2405 test_ret_set_rec_dyn (5) 2406 AS (a int, b numeric, c text); 2407 2408CREATE FUNCTION test_ret_rec_dyn (int) 2409 RETURNS record 2410 AS ' 2411DECLARE 2412 retval RECORD; 2413BEGIN 2414 IF $1 > 10 THEN 2415 SELECT 2416 INTO retval 5, 2417 10, 2418 15; 2419 RETURN retval; 2420 ELSE 2421 SELECT 2422 INTO retval 50, 2423 5::numeric, 2424 ''xxx''::text; 2425 RETURN retval; 2426 END IF; 2427END; 2428' 2429LANGUAGE plpgsql; 2430 2431SELECT 2432 * 2433FROM 2434 test_ret_rec_dyn (1500) 2435 AS (a int, b int, c int); 2436 2437SELECT 2438 * 2439FROM 2440 test_ret_rec_dyn (5) 2441 AS (a int, b numeric, c text); 2442 2443-- 2444-- Test handling of OUT parameters, including polymorphic cases. 2445-- Note that RETURN is optional with OUT params; we try both ways. 2446-- 2447-- wrong way to do it: 2448CREATE FUNCTION f1 (IN i int, out j int) 2449 RETURNS int 2450 AS $$ 2451BEGIN 2452 RETURN i + 1; 2453END 2454$$ 2455LANGUAGE plpgsql; 2456 2457CREATE FUNCTION f1 (IN i int, out j int) 2458AS $$ 2459BEGIN 2460 j := i + 1; 2461 RETURN; 2462END 2463$$ 2464LANGUAGE plpgsql; 2465 2466SELECT 2467 f1 (42); 2468 2469SELECT 2470 * 2471FROM 2472 f1 (42); 2473 2474CREATE OR REPLACE FUNCTION f1 (INOUT i int) 2475AS $$ 2476BEGIN 2477 i := i + 1; 2478END 2479$$ 2480LANGUAGE plpgsql; 2481 2482SELECT 2483 f1 (42); 2484 2485SELECT 2486 * 2487FROM 2488 f1 (42); 2489 2490DROP FUNCTION f1 (int); 2491 2492CREATE FUNCTION f1 (IN i int, out j int) 2493 RETURNS SETOF int 2494 AS $$ 2495BEGIN 2496 j := i + 1; 2497 RETURN NEXT; 2498 j := i + 2; 2499 RETURN NEXT; 2500 RETURN; 2501END 2502$$ 2503LANGUAGE plpgsql; 2504 2505SELECT 2506 * 2507FROM 2508 f1 (42); 2509 2510DROP FUNCTION f1 (int); 2511 2512CREATE FUNCTION f1 (IN i int, out j int, out k text) 2513AS $$ 2514BEGIN 2515 j := i; 2516 j := j + 1; 2517 k := 'foo'; 2518END 2519$$ 2520LANGUAGE plpgsql; 2521 2522SELECT 2523 f1 (42); 2524 2525SELECT 2526 * 2527FROM 2528 f1 (42); 2529 2530DROP FUNCTION f1 (int); 2531 2532CREATE FUNCTION f1 (IN i int, out j int, out k text) 2533 RETURNS SETOF record 2534 AS $$ 2535BEGIN 2536 j := i + 1; 2537 k := 'foo'; 2538 RETURN NEXT; 2539 j := j + 1; 2540 k := 'foot'; 2541 RETURN NEXT; 2542END 2543$$ 2544LANGUAGE plpgsql; 2545 2546SELECT 2547 * 2548FROM 2549 f1 (42); 2550 2551DROP FUNCTION f1 (int); 2552 2553CREATE FUNCTION duplic (IN i anyelement, out j anyelement, out k anyarray) 2554AS $$ 2555BEGIN 2556 j := i; 2557 k := ARRAY[j, j]; 2558 RETURN; 2559END 2560$$ 2561LANGUAGE plpgsql; 2562 2563SELECT 2564 * 2565FROM 2566 duplic (42); 2567 2568SELECT 2569 * 2570FROM 2571 duplic ('foo'::text); 2572 2573DROP FUNCTION duplic (anyelement); 2574 2575-- 2576-- test PERFORM 2577-- 2578CREATE TABLE perform_test ( 2579 a int, 2580 b int 2581); 2582 2583CREATE FUNCTION perform_simple_func (int) 2584 RETURNS boolean 2585 AS ' 2586BEGIN 2587 IF $1 < 20 THEN 2588 INSERT INTO perform_test 2589 VALUES ($1, $1 + 10); 2590 RETURN TRUE; 2591 ELSE 2592 RETURN FALSE; 2593 END IF; 2594END; 2595' 2596LANGUAGE plpgsql; 2597 2598CREATE FUNCTION perform_test_func () 2599 RETURNS void 2600 AS ' 2601BEGIN 2602 IF FOUND THEN 2603 INSERT INTO perform_test 2604 VALUES (100, 100); 2605 END IF; 2606 PERFORM 2607 perform_simple_func (5); 2608 IF FOUND THEN 2609 INSERT INTO perform_test 2610 VALUES (100, 100); 2611 END IF; 2612 PERFORM 2613 perform_simple_func (50); 2614 IF FOUND THEN 2615 INSERT INTO perform_test 2616 VALUES (100, 100); 2617 END IF; 2618 RETURN; 2619END; 2620' 2621LANGUAGE plpgsql; 2622 2623SELECT 2624 perform_test_func (); 2625 2626SELECT 2627 * 2628FROM 2629 perform_test; 2630 2631DROP TABLE perform_test; 2632 2633-- 2634-- Test proper snapshot handling in simple expressions 2635-- 2636CREATE temp TABLE users ( 2637 login text, 2638 id serial 2639); 2640 2641CREATE FUNCTION sp_id_user (a_login text) 2642 RETURNS int 2643 AS $$ 2644DECLARE 2645 x int; 2646BEGIN 2647 SELECT 2648 INTO x id 2649 FROM 2650 users 2651 WHERE 2652 login = a_login; 2653 IF found THEN 2654 RETURN x; 2655 END IF; 2656 RETURN 0; 2657END 2658$$ 2659LANGUAGE plpgsql 2660STABLE; 2661 2662INSERT INTO users 2663 VALUES ('user1'); 2664 2665SELECT 2666 sp_id_user ('user1'); 2667 2668SELECT 2669 sp_id_user ('userx'); 2670 2671CREATE FUNCTION sp_add_user (a_login text) 2672 RETURNS int 2673 AS $$ 2674DECLARE 2675 my_id_user int; 2676BEGIN 2677 my_id_user = sp_id_user (a_login); 2678 IF my_id_user > 0 THEN 2679 RETURN -1; 2680 -- error code for existing user 2681 END IF; 2682 INSERT INTO users (login) 2683 VALUES (a_login); 2684 my_id_user = sp_id_user (a_login); 2685 IF my_id_user = 0 THEN 2686 RETURN -2; 2687 -- error code for insertion failure 2688 END IF; 2689 RETURN my_id_user; 2690END 2691$$ 2692LANGUAGE plpgsql; 2693 2694SELECT 2695 sp_add_user ('user1'); 2696 2697SELECT 2698 sp_add_user ('user2'); 2699 2700SELECT 2701 sp_add_user ('user2'); 2702 2703SELECT 2704 sp_add_user ('user3'); 2705 2706SELECT 2707 sp_add_user ('user3'); 2708 2709DROP FUNCTION sp_add_user (text); 2710 2711DROP FUNCTION sp_id_user (text); 2712 2713-- 2714-- tests for refcursors 2715-- 2716CREATE TABLE rc_test ( 2717 a int, 2718 b int 2719); 2720 2721CREATE FUNCTION return_unnamed_refcursor () 2722 RETURNS refcursor 2723 AS $$ 2724DECLARE 2725 rc refcursor; 2726BEGIN 2727 OPEN rc FOR 2728 SELECT 2729 a 2730 FROM 2731 rc_test; 2732 RETURN rc; 2733END 2734$$ 2735LANGUAGE plpgsql; 2736 2737CREATE FUNCTION use_refcursor (rc refcursor) 2738 RETURNS int 2739 AS $$ 2740DECLARE 2741 rc refcursor; 2742 x record; 2743BEGIN 2744 rc := return_unnamed_refcursor (); 2745 FETCH NEXT FROM rc INTO x; 2746 RETURN x.a; 2747END 2748$$ 2749LANGUAGE plpgsql; 2750 2751SELECT 2752 use_refcursor (return_unnamed_refcursor ()); 2753 2754CREATE FUNCTION return_refcursor (rc refcursor) 2755 RETURNS refcursor 2756 AS $$ 2757BEGIN 2758 OPEN rc FOR 2759 SELECT 2760 a 2761 FROM 2762 rc_test; 2763 RETURN rc; 2764END 2765$$ 2766LANGUAGE plpgsql; 2767 2768CREATE FUNCTION refcursor_test1 (refcursor) 2769 RETURNS refcursor 2770 AS $$ 2771BEGIN 2772 PERFORM 2773 return_refcursor ($1); 2774 RETURN $1; 2775END 2776$$ 2777LANGUAGE plpgsql; 2778 2779BEGIN; 2780SELECT 2781 refcursor_test1 ('test1'); 2782FETCH NEXT IN test1; 2783SELECT 2784 refcursor_test1 ('test2'); 2785FETCH ALL FROM test2; 2786COMMIT; 2787 2788-- should fail 2789FETCH NEXT 2790FROM 2791 test1; 2792 2793CREATE FUNCTION refcursor_test2 (int, int) 2794 RETURNS boolean 2795 AS $$ 2796DECLARE 2797 c1 CURSOR (param1 int, 2798 param2 int) 2799 FOR 2800 SELECT 2801 * 2802 FROM 2803 rc_test 2804 WHERE 2805 a > param1 2806 AND b > param2; 2807 nonsense record; 2808BEGIN 2809 OPEN c1 ($1, 2810 $2); 2811 FETCH c1 INTO nonsense; 2812 CLOSE c1; 2813 IF found THEN 2814 RETURN TRUE; 2815 ELSE 2816 RETURN FALSE; 2817 END IF; 2818END 2819$$ 2820LANGUAGE plpgsql; 2821 2822SELECT 2823 refcursor_test2 (20000, 20000) AS "Should be false", 2824 refcursor_test2 (20, 20) AS "Should be true"; 2825 2826-- 2827-- tests for cursors with named parameter arguments 2828-- 2829CREATE FUNCTION namedparmcursor_test1 (int, int) 2830 RETURNS boolean 2831 AS $$ 2832DECLARE 2833 c1 CURSOR (param1 int, 2834 param12 int) 2835 FOR 2836 SELECT 2837 * 2838 FROM 2839 rc_test 2840 WHERE 2841 a > param1 2842 AND b > param12; 2843 nonsense record; 2844BEGIN 2845 OPEN c1 (param12 := $2, 2846 param1 := $1); 2847 FETCH c1 INTO nonsense; 2848 CLOSE c1; 2849 IF found THEN 2850 RETURN TRUE; 2851 ELSE 2852 RETURN FALSE; 2853 END IF; 2854END 2855$$ 2856LANGUAGE plpgsql; 2857 2858SELECT 2859 namedparmcursor_test1 (20000, 20000) AS "Should be false", 2860 namedparmcursor_test1 (20, 20) AS "Should be true"; 2861 2862-- mixing named and positional argument notations 2863CREATE FUNCTION namedparmcursor_test2 (int, int) 2864 RETURNS boolean 2865 AS $$ 2866DECLARE 2867 c1 CURSOR (param1 int, 2868 param2 int) 2869 FOR 2870 SELECT 2871 * 2872 FROM 2873 rc_test 2874 WHERE 2875 a > param1 2876 AND b > param2; 2877 nonsense record; 2878BEGIN 2879 OPEN c1 (param1 := $1, 2880 $2); 2881 FETCH c1 INTO nonsense; 2882 CLOSE c1; 2883 IF found THEN 2884 RETURN TRUE; 2885 ELSE 2886 RETURN FALSE; 2887 END IF; 2888END 2889$$ 2890LANGUAGE plpgsql; 2891 2892SELECT 2893 namedparmcursor_test2 (20, 20); 2894 2895-- mixing named and positional: param2 is given twice, once in named notation 2896-- and second time in positional notation. Should throw an error at parse time 2897CREATE FUNCTION namedparmcursor_test3 () 2898 RETURNS void 2899 AS $$ 2900DECLARE 2901 c1 CURSOR (param1 int, 2902 param2 int) 2903 FOR 2904 SELECT 2905 * 2906 FROM 2907 rc_test 2908 WHERE 2909 a > param1 2910 AND b > param2; 2911BEGIN 2912 OPEN c1 (param2 := 20, 2913 21); 2914END 2915$$ 2916LANGUAGE plpgsql; 2917 2918-- mixing named and positional: same as previous test, but param1 is duplicated 2919CREATE FUNCTION namedparmcursor_test4 () 2920 RETURNS void 2921 AS $$ 2922DECLARE 2923 c1 CURSOR (param1 int, 2924 param2 int) 2925 FOR 2926 SELECT 2927 * 2928 FROM 2929 rc_test 2930 WHERE 2931 a > param1 2932 AND b > param2; 2933BEGIN 2934 OPEN c1 (20, 2935 param1 := 21); 2936END 2937$$ 2938LANGUAGE plpgsql; 2939 2940-- duplicate named parameter, should throw an error at parse time 2941CREATE FUNCTION namedparmcursor_test5 () 2942 RETURNS void 2943 AS $$ 2944DECLARE 2945 c1 CURSOR (p1 int, 2946 p2 int) 2947 FOR 2948 SELECT 2949 * 2950 FROM 2951 tenk1 2952 WHERE 2953 thousand = p1 2954 AND tenthous = p2; 2955BEGIN 2956 OPEN c1 (p2 := 77, 2957 p2 := 42); 2958END 2959$$ 2960LANGUAGE plpgsql; 2961 2962-- not enough parameters, should throw an error at parse time 2963CREATE FUNCTION namedparmcursor_test6 () 2964 RETURNS void 2965 AS $$ 2966DECLARE 2967 c1 CURSOR (p1 int, 2968 p2 int) 2969 FOR 2970 SELECT 2971 * 2972 FROM 2973 tenk1 2974 WHERE 2975 thousand = p1 2976 AND tenthous = p2; 2977BEGIN 2978 OPEN c1 (p2 := 77); 2979END 2980$$ 2981LANGUAGE plpgsql; 2982 2983-- division by zero runtime error, the context given in the error message 2984-- should be sensible 2985CREATE FUNCTION namedparmcursor_test7 () 2986 RETURNS void 2987 AS $$ 2988DECLARE 2989 c1 CURSOR (p1 int, 2990 p2 int) 2991 FOR 2992 SELECT 2993 * 2994 FROM 2995 tenk1 2996 WHERE 2997 thousand = p1 2998 AND tenthous = p2; 2999BEGIN 3000 OPEN c1 (p2 := 77, 3001 p1 := 42 / 0); 3002END 3003$$ 3004LANGUAGE plpgsql; 3005 3006SELECT 3007 namedparmcursor_test7 (); 3008 3009-- check that line comments work correctly within the argument list (there 3010-- is some special handling of this case in the code: the newline after the 3011-- comment must be preserved when the argument-evaluating query is 3012-- constructed, otherwise the comment effectively comments out the next 3013-- argument, too) 3014CREATE FUNCTION namedparmcursor_test8 () 3015 RETURNS int4 3016 AS $$ 3017DECLARE 3018 c1 CURSOR (p1 int, 3019 p2 int) 3020 FOR 3021 SELECT 3022 count(*) 3023 FROM 3024 tenk1 3025 WHERE 3026 thousand = p1 3027 AND tenthous = p2; 3028 n int4; 3029BEGIN 3030 OPEN c1 (77, -- test 3031 42); 3032 FETCH c1 INTO n; 3033 RETURN n; 3034END 3035$$ 3036LANGUAGE plpgsql; 3037 3038SELECT 3039 namedparmcursor_test8 (); 3040 3041-- cursor parameter name can match plpgsql variable or unreserved keyword 3042CREATE FUNCTION namedparmcursor_test9 (p1 int) 3043 RETURNS int4 3044 AS $$ 3045DECLARE 3046 c1 CURSOR (p1 int, 3047 p2 int, 3048 debug int) 3049 FOR 3050 SELECT 3051 count(*) 3052 FROM 3053 tenk1 3054 WHERE 3055 thousand = p1 3056 AND tenthous = p2 3057 AND four = debug; 3058 p2 int4 := 1006; 3059 n int4; 3060BEGIN 3061 OPEN c1 (p1 := p1, 3062 p2 := p2, 3063 debug := 2); 3064 FETCH c1 INTO n; 3065 RETURN n; 3066END 3067$$ 3068LANGUAGE plpgsql; 3069 3070SELECT 3071 namedparmcursor_test9 (6); 3072 3073-- 3074-- tests for "raise" processing 3075-- 3076CREATE FUNCTION raise_test1 (int) 3077 RETURNS int 3078 AS $$ 3079BEGIN 3080 RAISE NOTICE 'This message has too many parameters!', $1; 3081 RETURN $1; 3082END; 3083$$ 3084LANGUAGE plpgsql; 3085 3086CREATE FUNCTION raise_test2 (int) 3087 RETURNS int 3088 AS $$ 3089BEGIN 3090 RAISE NOTICE 'This message has too few parameters: %, %, %', $1, $1; 3091 RETURN $1; 3092END; 3093$$ 3094LANGUAGE plpgsql; 3095 3096CREATE FUNCTION raise_test3 (int) 3097 RETURNS int 3098 AS $$ 3099BEGIN 3100 RAISE NOTICE 'This message has no parameters (despite having %% signs in it)!'; 3101 RETURN $1; 3102END; 3103$$ 3104LANGUAGE plpgsql; 3105 3106SELECT 3107 raise_test3 (1); 3108 3109-- Test re-RAISE inside a nested exception block. This case is allowed 3110-- by Oracle's PL/SQL but was handled differently by PG before 9.1. 3111CREATE FUNCTION reraise_test () 3112 RETURNS void 3113 AS $$ 3114BEGIN 3115 BEGIN 3116 RAISE syntax_error; 3117 EXCEPTION 3118 WHEN syntax_error THEN 3119 BEGIN 3120 RAISE NOTICE 'exception % thrown in inner block, reraising', sqlerrm; 3121 RAISE; 3122 EXCEPTION 3123 WHEN OTHERS THEN 3124 RAISE NOTICE 'RIGHT - exception % caught in inner block', sqlerrm; 3125 END; 3126 END; 3127EXCEPTION 3128 WHEN OTHERS THEN 3129 RAISE NOTICE 'WRONG - exception % caught in outer block', sqlerrm; 3130END; 3131 3132$$ 3133LANGUAGE plpgsql; 3134 3135SELECT 3136 reraise_test (); 3137 3138-- 3139-- reject function definitions that contain malformed SQL queries at 3140-- compile-time, where possible 3141-- 3142CREATE FUNCTION bad_sql1 () 3143 RETURNS int 3144 AS $$ 3145DECLARE 3146 a int; 3147BEGIN 3148 a := 5; 3149 Johnny Yuma; 3150 a := 10; 3151 RETURN a; 3152END 3153$$ 3154LANGUAGE plpgsql; 3155 3156CREATE FUNCTION bad_sql2 () 3157 RETURNS int 3158 AS $$ 3159DECLARE 3160 r record; 3161BEGIN 3162 FOR r IN 3163 SELECT 3164 I fought the law, 3165 the law won LOOP 3166 RAISE NOTICE 'in loop'; 3167 END LOOP; 3168 RETURN 5; 3169END; 3170$$ 3171LANGUAGE plpgsql; 3172 3173-- a RETURN expression is mandatory, except for void-returning 3174-- functions, where it is not allowed 3175CREATE FUNCTION missing_return_expr () 3176 RETURNS int 3177 AS $$ 3178BEGIN 3179 RETURN; 3180END; 3181$$ 3182LANGUAGE plpgsql; 3183 3184CREATE FUNCTION void_return_expr () 3185 RETURNS void 3186 AS $$ 3187BEGIN 3188 RETURN 5; 3189END; 3190$$ 3191LANGUAGE plpgsql; 3192 3193-- VOID functions are allowed to omit RETURN 3194CREATE FUNCTION void_return_expr () 3195 RETURNS void 3196 AS $$ 3197BEGIN 3198 PERFORM 3199 2 + 2; 3200END; 3201$$ 3202LANGUAGE plpgsql; 3203 3204SELECT 3205 void_return_expr (); 3206 3207-- but ordinary functions are not 3208CREATE FUNCTION missing_return_expr () 3209 RETURNS int 3210 AS $$ 3211BEGIN 3212 PERFORM 3213 2 + 2; 3214END; 3215$$ 3216LANGUAGE plpgsql; 3217 3218SELECT 3219 missing_return_expr (); 3220 3221DROP FUNCTION void_return_expr (); 3222 3223DROP FUNCTION missing_return_expr (); 3224 3225-- 3226-- EXECUTE ... INTO test 3227-- 3228CREATE TABLE eifoo ( 3229 i integer, 3230 y integer 3231); 3232 3233CREATE TYPE eitype AS ( 3234 i integer, 3235 y integer 3236); 3237 3238CREATE OR REPLACE FUNCTION execute_into_test (varchar) 3239 RETURNS record 3240 AS $$ 3241DECLARE 3242 _r record; 3243 _rt eifoo%rowtype; 3244 _v eitype; 3245 i int; 3246 j int; 3247 k int; 3248BEGIN 3249 EXECUTE 'insert into ' || $1 || ' values(10,15)'; 3250 EXECUTE 'select (row).* from (select row(10,1)::eifoo) s' INTO _r; 3251 RAISE NOTICE '% %', _r.i, _r.y; 3252 EXECUTE 'select * from ' || $1 || ' limit 1' INTO _rt; 3253 RAISE NOTICE '% %', _rt.i, _rt.y; 3254 EXECUTE 'select *, 20 from ' || $1 || ' limit 1' INTO i, 3255 j, 3256 k; 3257 RAISE NOTICE '% % %', i, j, k; 3258 EXECUTE 'select 1,2' INTO _v; 3259 RETURN _v; 3260END; 3261$$ 3262LANGUAGE plpgsql; 3263 3264SELECT 3265 execute_into_test ('eifoo'); 3266 3267DROP TABLE eifoo CASCADE; 3268 3269DROP TYPE eitype CASCADE; 3270 3271-- 3272-- SQLSTATE and SQLERRM test 3273-- 3274CREATE FUNCTION excpt_test1 () 3275 RETURNS void 3276 AS $$ 3277BEGIN 3278 RAISE NOTICE '% %', sqlstate, sqlerrm; 3279END; 3280$$ 3281LANGUAGE plpgsql; 3282 3283-- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION 3284-- blocks 3285SELECT 3286 excpt_test1 (); 3287 3288CREATE FUNCTION excpt_test2 () 3289 RETURNS void 3290 AS $$ 3291BEGIN 3292 BEGIN 3293 BEGIN 3294 RAISE NOTICE '% %', sqlstate, sqlerrm; 3295 END; 3296 END; 3297END; 3298$$ 3299LANGUAGE plpgsql; 3300 3301-- should fail 3302SELECT 3303 excpt_test2 (); 3304 3305CREATE FUNCTION excpt_test3 () 3306 RETURNS void 3307 AS $$ 3308BEGIN 3309 BEGIN 3310 RAISE EXCEPTION 'user exception'; 3311 EXCEPTION 3312 WHEN OTHERS THEN 3313 RAISE NOTICE 'caught exception % %', sqlstate, sqlerrm; 3314 BEGIN 3315 RAISE NOTICE '% %', sqlstate, sqlerrm; 3316 PERFORM 3317 10 / 0; 3318 EXCEPTION 3319 WHEN substring_error THEN 3320 -- this exception handler shouldn't be invoked 3321 RAISE NOTICE 'unexpected exception: % %', sqlstate, sqlerrm; 3322 WHEN division_by_zero THEN 3323 RAISE NOTICE 'caught exception % %', sqlstate, sqlerrm; 3324 END; 3325 RAISE NOTICE '% %', sqlstate, sqlerrm; 3326 END; 3327END; 3328 3329$$ 3330LANGUAGE plpgsql; 3331 3332SELECT 3333 excpt_test3 (); 3334 3335CREATE FUNCTION excpt_test4 () 3336 RETURNS text 3337 AS $$ 3338BEGIN 3339 BEGIN 3340 PERFORM 3341 1 / 0; 3342 EXCEPTION 3343 WHEN OTHERS THEN 3344 RETURN sqlerrm; 3345 END; 3346END; 3347 3348$$ 3349LANGUAGE plpgsql; 3350 3351SELECT 3352 excpt_test4 (); 3353 3354DROP FUNCTION excpt_test1 (); 3355 3356DROP FUNCTION excpt_test2 (); 3357 3358DROP FUNCTION excpt_test3 (); 3359 3360DROP FUNCTION excpt_test4 (); 3361 3362-- parameters of raise stmt can be expressions 3363CREATE FUNCTION raise_exprs () 3364 RETURNS void 3365 AS $$ 3366DECLARE 3367 a integer[] = '{10,20,30}'; 3368 c varchar = 'xyz'; 3369 i integer; 3370BEGIN 3371 i := 2; 3372 RAISE NOTICE '%; %; %; %; %; %', a, a[i], c, ( 3373 SELECT 3374 c || 'abc'), 3375 ROW (10, 3376 'aaa', 3377 NULL, 3378 30), 3379 NULL; 3380END; 3381$$ 3382LANGUAGE plpgsql; 3383 3384SELECT 3385 raise_exprs (); 3386 3387DROP FUNCTION raise_exprs (); 3388 3389-- regression test: verify that multiple uses of same plpgsql datum within 3390-- a SQL command all get mapped to the same $n parameter. The return value 3391-- of the SELECT is not important, we only care that it doesn't fail with 3392-- a complaint about an ungrouped column reference. 3393CREATE FUNCTION multi_datum_use (p1 int) 3394 RETURNS bool 3395 AS $$ 3396DECLARE 3397 x int; 3398 y int; 3399BEGIN 3400 SELECT 3401 INTO x, 3402 y unique1 / p1, 3403 unique1 / $1 3404 FROM 3405 tenk1 3406 GROUP BY 3407 unique1 / p1; 3408 RETURN x = y; 3409END 3410$$ 3411LANGUAGE plpgsql; 3412 3413SELECT 3414 multi_datum_use (42); 3415 3416-- 3417-- Test STRICT limiter in both planned and EXECUTE invocations. 3418-- Note that a data-modifying query is quasi strict (disallow multi rows) 3419-- by default in the planned case, but not in EXECUTE. 3420-- 3421CREATE temp TABLE foo ( 3422 f1 int, 3423 f2 int 3424); 3425 3426INSERT INTO foo 3427 VALUES (1, 2), (3, 4); 3428 3429CREATE OR REPLACE FUNCTION stricttest () 3430 RETURNS void 3431 AS $$ 3432DECLARE 3433 x record; 3434BEGIN 3435 -- should work 3436 INSERT INTO foo 3437 VALUES (5, 6) 3438 RETURNING 3439 * INTO x; 3440 RAISE NOTICE 'x.f1 = %, x.f2 = %', x.f1, x.f2; 3441END 3442$$ 3443LANGUAGE plpgsql; 3444 3445SELECT 3446 stricttest (); 3447 3448CREATE OR REPLACE FUNCTION stricttest () 3449 RETURNS void 3450 AS $$ 3451DECLARE 3452 x record; 3453BEGIN 3454 -- should fail due to implicit strict 3455 INSERT INTO foo 3456 VALUES (7, 8), (9, 10) 3457 RETURNING 3458 * INTO x; 3459 RAISE NOTICE 'x.f1 = %, x.f2 = %', x.f1, x.f2; 3460END 3461$$ 3462LANGUAGE plpgsql; 3463 3464SELECT 3465 stricttest (); 3466 3467CREATE OR REPLACE FUNCTION stricttest () 3468 RETURNS void 3469 AS $$ 3470DECLARE 3471 x record; 3472BEGIN 3473 -- should work 3474 EXECUTE 'insert into foo values(5,6) returning *' INTO x; 3475 RAISE NOTICE 'x.f1 = %, x.f2 = %', x.f1, x.f2; 3476END 3477$$ 3478LANGUAGE plpgsql; 3479 3480SELECT 3481 stricttest (); 3482 3483CREATE OR REPLACE FUNCTION stricttest () 3484 RETURNS void 3485 AS $$ 3486DECLARE 3487 x record; 3488BEGIN 3489 -- this should work since EXECUTE isn't as picky 3490 EXECUTE 'insert into foo values(7,8),(9,10) returning *' INTO x; 3491 RAISE NOTICE 'x.f1 = %, x.f2 = %', x.f1, x.f2; 3492END 3493$$ 3494LANGUAGE plpgsql; 3495 3496SELECT 3497 stricttest (); 3498 3499SELECT 3500 * 3501FROM 3502 foo; 3503 3504CREATE OR REPLACE FUNCTION stricttest () 3505 RETURNS void 3506 AS $$ 3507DECLARE 3508 x record; 3509BEGIN 3510 -- should work 3511 SELECT 3512 * 3513 FROM 3514 foo 3515 WHERE 3516 f1 = 3 INTO STRICT x; 3517 RAISE NOTICE 'x.f1 = %, x.f2 = %', x.f1, x.f2; 3518END 3519$$ 3520LANGUAGE plpgsql; 3521 3522SELECT 3523 stricttest (); 3524 3525CREATE OR REPLACE FUNCTION stricttest () 3526 RETURNS void 3527 AS $$ 3528DECLARE 3529 x record; 3530BEGIN 3531 -- should fail, no rows 3532 SELECT 3533 * 3534 FROM 3535 foo 3536 WHERE 3537 f1 = 0 INTO STRICT x; 3538 RAISE NOTICE 'x.f1 = %, x.f2 = %', x.f1, x.f2; 3539END 3540$$ 3541LANGUAGE plpgsql; 3542 3543SELECT 3544 stricttest (); 3545 3546CREATE OR REPLACE FUNCTION stricttest () 3547 RETURNS void 3548 AS $$ 3549DECLARE 3550 x record; 3551BEGIN 3552 -- should fail, too many rows 3553 SELECT 3554 * 3555 FROM 3556 foo 3557 WHERE 3558 f1 > 3 INTO STRICT x; 3559 RAISE NOTICE 'x.f1 = %, x.f2 = %', x.f1, x.f2; 3560END 3561$$ 3562LANGUAGE plpgsql; 3563 3564SELECT 3565 stricttest (); 3566 3567CREATE OR REPLACE FUNCTION stricttest () 3568 RETURNS void 3569 AS $$ 3570DECLARE 3571 x record; 3572BEGIN 3573 -- should work 3574 EXECUTE 'select * from foo where f1 = 3' INTO STRICT x; 3575 RAISE NOTICE 'x.f1 = %, x.f2 = %', x.f1, x.f2; 3576END 3577$$ 3578LANGUAGE plpgsql; 3579 3580SELECT 3581 stricttest (); 3582 3583CREATE OR REPLACE FUNCTION stricttest () 3584 RETURNS void 3585 AS $$ 3586DECLARE 3587 x record; 3588BEGIN 3589 -- should fail, no rows 3590 EXECUTE 'select * from foo where f1 = 0' INTO STRICT x; 3591 RAISE NOTICE 'x.f1 = %, x.f2 = %', x.f1, x.f2; 3592END 3593$$ 3594LANGUAGE plpgsql; 3595 3596SELECT 3597 stricttest (); 3598 3599CREATE OR REPLACE FUNCTION stricttest () 3600 RETURNS void 3601 AS $$ 3602DECLARE 3603 x record; 3604BEGIN 3605 -- should fail, too many rows 3606 EXECUTE 'select * from foo where f1 > 3' INTO STRICT x; 3607 RAISE NOTICE 'x.f1 = %, x.f2 = %', x.f1, x.f2; 3608END 3609$$ 3610LANGUAGE plpgsql; 3611 3612SELECT 3613 stricttest (); 3614 3615DROP FUNCTION stricttest (); 3616 3617-- test printing parameters after failure due to STRICT 3618SET plpgsql.print_strict_params TO TRUE; 3619 3620CREATE OR REPLACE FUNCTION stricttest () 3621 RETURNS void 3622 AS $$ 3623DECLARE 3624 x record; 3625 p1 int := 2; 3626 p3 text := 'foo'; 3627BEGIN 3628 -- no rows 3629 SELECT 3630 * 3631 FROM 3632 foo 3633 WHERE 3634 f1 = p1 3635 AND f1::text = p3 INTO STRICT x; 3636 RAISE NOTICE 'x.f1 = %, x.f2 = %', x.f1, x.f2; 3637END 3638$$ 3639LANGUAGE plpgsql; 3640 3641SELECT 3642 stricttest (); 3643 3644CREATE OR REPLACE FUNCTION stricttest () 3645 RETURNS void 3646 AS $$ 3647DECLARE 3648 x record; 3649 p1 int := 2; 3650 p3 text := 'foo'; 3651BEGIN 3652 -- too many rows 3653 SELECT 3654 * 3655 FROM 3656 foo 3657 WHERE 3658 f1 > p1 3659 OR f1::text = p3 INTO STRICT x; 3660 RAISE NOTICE 'x.f1 = %, x.f2 = %', x.f1, x.f2; 3661END 3662$$ 3663LANGUAGE plpgsql; 3664 3665SELECT 3666 stricttest (); 3667 3668CREATE OR REPLACE FUNCTION stricttest () 3669 RETURNS void 3670 AS $$ 3671DECLARE 3672 x record; 3673BEGIN 3674 -- too many rows, no params 3675 SELECT 3676 * 3677 FROM 3678 foo 3679 WHERE 3680 f1 > 3 INTO STRICT x; 3681 RAISE NOTICE 'x.f1 = %, x.f2 = %', x.f1, x.f2; 3682END 3683$$ 3684LANGUAGE plpgsql; 3685 3686SELECT 3687 stricttest (); 3688 3689CREATE OR REPLACE FUNCTION stricttest () 3690 RETURNS void 3691 AS $$ 3692DECLARE 3693 x record; 3694BEGIN 3695 -- no rows 3696 EXECUTE 'select * from foo where f1 = $1 or f1::text = $2' 3697 USING 0, 'foo' INTO STRICT x; 3698 RAISE NOTICE 'x.f1 = %, x.f2 = %', x.f1, x.f2; 3699END 3700$$ 3701LANGUAGE plpgsql; 3702 3703SELECT 3704 stricttest (); 3705 3706CREATE OR REPLACE FUNCTION stricttest () 3707 RETURNS void 3708 AS $$ 3709DECLARE 3710 x record; 3711BEGIN 3712 -- too many rows 3713 EXECUTE 'select * from foo where f1 > $1' 3714 USING 1 INTO STRICT x; 3715 RAISE NOTICE 'x.f1 = %, x.f2 = %', x.f1, x.f2; 3716END 3717$$ 3718LANGUAGE plpgsql; 3719 3720SELECT 3721 stricttest (); 3722 3723CREATE OR REPLACE FUNCTION stricttest () 3724 RETURNS void 3725 AS $$ 3726DECLARE 3727 x record; 3728BEGIN 3729 -- too many rows, no parameters 3730 EXECUTE 'select * from foo where f1 > 3' INTO STRICT x; 3731 RAISE NOTICE 'x.f1 = %, x.f2 = %', x.f1, x.f2; 3732END 3733$$ 3734LANGUAGE plpgsql; 3735 3736SELECT 3737 stricttest (); 3738 3739CREATE OR REPLACE FUNCTION stricttest () 3740 RETURNS void 3741 AS $$ 3742 -- override the global 3743 # print_strict_params OFF 3744DECLARE 3745 x record; 3746 p1 int := 2; 3747 p3 text := 'foo'; 3748BEGIN 3749 -- too many rows 3750 SELECT 3751 * 3752 FROM 3753 foo 3754 WHERE 3755 f1 > p1 3756 OR f1::text = p3 INTO STRICT x; 3757 RAISE NOTICE 'x.f1 = %, x.f2 = %', x.f1, x.f2; 3758END 3759$$ 3760LANGUAGE plpgsql; 3761 3762SELECT 3763 stricttest (); 3764 3765RESET plpgsql.print_strict_params; 3766 3767CREATE OR REPLACE FUNCTION stricttest () 3768 RETURNS void 3769 AS $$ 3770 -- override the global 3771 # print_strict_params ON 3772DECLARE 3773 x record; 3774 p1 int := 2; 3775 p3 text := 'foo'; 3776BEGIN 3777 -- too many rows 3778 SELECT 3779 * 3780 FROM 3781 foo 3782 WHERE 3783 f1 > p1 3784 OR f1::text = p3 INTO STRICT x; 3785 RAISE NOTICE 'x.f1 = %, x.f2 = %', x.f1, x.f2; 3786END 3787$$ 3788LANGUAGE plpgsql; 3789 3790SELECT 3791 stricttest (); 3792 3793-- test warnings and errors 3794SET plpgsql.extra_warnings TO 'all'; 3795 3796SET plpgsql.extra_warnings TO 'none'; 3797 3798SET plpgsql.extra_errors TO 'all'; 3799 3800SET plpgsql.extra_errors TO 'none'; 3801 3802-- test warnings when shadowing a variable 3803SET plpgsql.extra_warnings TO 'shadowed_variables'; 3804 3805-- simple shadowing of input and output parameters 3806CREATE OR REPLACE FUNCTION shadowtest (in1 int) 3807 RETURNS TABLE ( 3808 out1 int 3809 ) 3810 AS $$ 3811DECLARE 3812 in1 int; 3813 out1 int; 3814BEGIN 3815END 3816$$ 3817LANGUAGE plpgsql; 3818 3819SELECT 3820 shadowtest (1); 3821 3822SET plpgsql.extra_warnings TO 'shadowed_variables'; 3823 3824SELECT 3825 shadowtest (1); 3826 3827CREATE OR REPLACE FUNCTION shadowtest (in1 int) 3828 RETURNS TABLE ( 3829 out1 int 3830 ) 3831 AS $$ 3832DECLARE 3833 in1 int; 3834 out1 int; 3835BEGIN 3836END 3837$$ 3838LANGUAGE plpgsql; 3839 3840SELECT 3841 shadowtest (1); 3842 3843DROP FUNCTION shadowtest (int); 3844 3845-- shadowing in a second DECLARE block 3846CREATE OR REPLACE FUNCTION shadowtest () 3847 RETURNS void 3848 AS $$ 3849DECLARE 3850 f1 int; 3851BEGIN 3852 DECLARE f1 int; 3853 BEGIN 3854 END; 3855END 3856$$ 3857LANGUAGE plpgsql; 3858 3859DROP FUNCTION shadowtest (); 3860 3861-- several levels of shadowing 3862CREATE OR REPLACE FUNCTION shadowtest (in1 int) 3863 RETURNS void 3864 AS $$ 3865DECLARE 3866 in1 int; 3867BEGIN 3868 DECLARE in1 int; 3869 BEGIN 3870 END; 3871END 3872$$ 3873LANGUAGE plpgsql; 3874 3875DROP FUNCTION shadowtest (int); 3876 3877-- shadowing in cursor definitions 3878CREATE OR REPLACE FUNCTION shadowtest () 3879 RETURNS void 3880 AS $$ 3881DECLARE 3882 f1 int; 3883 c1 CURSOR (f1 int) 3884 FOR 3885 SELECT 3886 1; 3887BEGIN 3888END 3889$$ 3890LANGUAGE plpgsql; 3891 3892DROP FUNCTION shadowtest (); 3893 3894-- test errors when shadowing a variable 3895SET plpgsql.extra_errors TO 'shadowed_variables'; 3896 3897CREATE OR REPLACE FUNCTION shadowtest (f1 int) 3898 RETURNS boolean 3899 AS $$ 3900DECLARE 3901 f1 int; 3902BEGIN 3903 RETURN 1; 3904END 3905$$ 3906LANGUAGE plpgsql; 3907 3908SELECT 3909 shadowtest (1); 3910 3911RESET plpgsql.extra_errors; 3912 3913RESET plpgsql.extra_warnings; 3914 3915CREATE OR REPLACE FUNCTION shadowtest (f1 int) 3916 RETURNS boolean 3917 AS $$ 3918DECLARE 3919 f1 int; 3920BEGIN 3921 RETURN 1; 3922END 3923$$ 3924LANGUAGE plpgsql; 3925 3926SELECT 3927 shadowtest (1); 3928 3929-- runtime extra checks 3930SET plpgsql.extra_warnings TO 'too_many_rows'; 3931 3932DO $$ 3933DECLARE 3934 x int; 3935BEGIN 3936 SELECT 3937 v 3938 FROM 3939 generate_series(1, 2) g (v) INTO x; 3940END; 3941$$; 3942 3943SET plpgsql.extra_errors TO 'too_many_rows'; 3944 3945DO $$ 3946DECLARE 3947 x int; 3948BEGIN 3949 SELECT 3950 v 3951 FROM 3952 generate_series(1, 2) g (v) INTO x; 3953END; 3954$$; 3955 3956RESET plpgsql.extra_errors; 3957 3958RESET plpgsql.extra_warnings; 3959 3960SET plpgsql.extra_warnings TO 'strict_multi_assignment'; 3961 3962DO $$ 3963DECLARE 3964 x int; 3965 y int; 3966BEGIN 3967 SELECT 3968 1 INTO x, 3969 y; 3970 SELECT 3971 1, 3972 2 INTO x, 3973 y; 3974 SELECT 3975 1, 3976 2, 3977 3 INTO x, 3978 y; 3979END 3980$$; 3981 3982SET plpgsql.extra_errors TO 'strict_multi_assignment'; 3983 3984DO $$ 3985DECLARE 3986 x int; 3987 y int; 3988BEGIN 3989 SELECT 3990 1 INTO x, 3991 y; 3992 SELECT 3993 1, 3994 2 INTO x, 3995 y; 3996 SELECT 3997 1, 3998 2, 3999 3 INTO x, 4000 y; 4001END 4002$$; 4003 4004CREATE TABLE test_01 ( 4005 a int, 4006 b int, 4007 c int 4008); 4009 4010ALTER TABLE test_01 4011 DROP COLUMN a; 4012 4013-- the check is active only when source table is not empty 4014INSERT INTO test_01 4015 VALUES (10, 20); 4016 4017DO $$ 4018DECLARE 4019 x int; 4020 y int; 4021BEGIN 4022 SELECT 4023 * 4024 FROM 4025 test_01 INTO x, 4026 y; 4027 -- should be ok 4028 RAISE NOTICE 'ok'; 4029 SELECT 4030 * 4031 FROM 4032 test_01 INTO x; 4033 -- should to fail 4034END; 4035$$; 4036 4037DO $$ 4038DECLARE 4039 t test_01; 4040BEGIN 4041 SELECT 4042 1, 4043 2 INTO t; 4044 -- should be ok 4045 RAISE NOTICE 'ok'; 4046 SELECT 4047 1, 4048 2, 4049 3 INTO t; 4050 -- should fail; 4051END; 4052$$; 4053 4054DO $$ 4055DECLARE 4056 t test_01; 4057BEGIN 4058 SELECT 4059 1 INTO t; 4060 -- should fail; 4061END; 4062$$; 4063 4064DROP TABLE test_01; 4065 4066RESET plpgsql.extra_errors; 4067 4068RESET plpgsql.extra_warnings; 4069 4070-- test scrollable cursor support 4071CREATE FUNCTION sc_test () 4072 RETURNS SETOF integer 4073 AS $$ 4074DECLARE 4075 c SCROLL CURSOR FOR 4076 SELECT 4077 f1 4078 FROM 4079 int4_tbl; 4080 x integer; 4081BEGIN 4082 OPEN c; 4083 FETCH LAST FROM c INTO x; 4084 while found LOOP 4085 RETURN NEXT x; 4086 FETCH prior FROM c INTO x; 4087 END LOOP; 4088 CLOSE c; 4089END; 4090$$ 4091LANGUAGE plpgsql; 4092 4093SELECT 4094 * 4095FROM 4096 sc_test (); 4097 4098CREATE OR REPLACE FUNCTION sc_test () 4099 RETURNS SETOF integer 4100 AS $$ 4101DECLARE 4102 c NO SCROLL CURSOR FOR 4103 SELECT 4104 f1 4105 FROM 4106 int4_tbl; 4107 x integer; 4108BEGIN 4109 OPEN c; 4110 FETCH LAST FROM c INTO x; 4111 while found LOOP 4112 RETURN NEXT x; 4113 FETCH prior FROM c INTO x; 4114 END LOOP; 4115 CLOSE c; 4116END; 4117$$ 4118LANGUAGE plpgsql; 4119 4120SELECT 4121 * 4122FROM 4123 sc_test (); 4124 4125-- fails because of NO SCROLL specification 4126CREATE OR REPLACE FUNCTION sc_test () 4127 RETURNS SETOF integer 4128 AS $$ 4129DECLARE 4130 c refcursor; 4131 x integer; 4132BEGIN 4133 OPEN c SCROLL FOR 4134 SELECT 4135 f1 4136 FROM 4137 int4_tbl; 4138 FETCH LAST FROM c INTO x; 4139 while found LOOP 4140 RETURN NEXT x; 4141 FETCH prior FROM c INTO x; 4142 END LOOP; 4143 CLOSE c; 4144END; 4145$$ 4146LANGUAGE plpgsql; 4147 4148SELECT 4149 * 4150FROM 4151 sc_test (); 4152 4153CREATE OR REPLACE FUNCTION sc_test () 4154 RETURNS SETOF integer 4155 AS $$ 4156DECLARE 4157 c refcursor; 4158 x integer; 4159BEGIN 4160 OPEN c SCROLL FOR EXECUTE 'select f1 from int4_tbl'; 4161 FETCH LAST FROM c INTO x; 4162 while found LOOP 4163 RETURN NEXT x; 4164 FETCH relative - 2 FROM c INTO x; 4165 END LOOP; 4166 CLOSE c; 4167END; 4168$$ 4169LANGUAGE plpgsql; 4170 4171SELECT 4172 * 4173FROM 4174 sc_test (); 4175 4176CREATE OR REPLACE FUNCTION sc_test () 4177 RETURNS SETOF integer 4178 AS $$ 4179DECLARE 4180 c refcursor; 4181 x integer; 4182BEGIN 4183 OPEN c SCROLL FOR EXECUTE 'select f1 from int4_tbl'; 4184 FETCH LAST FROM c INTO x; 4185 while found LOOP 4186 RETURN NEXT x; 4187 MOVE BACKWARD 2 4188 FROM 4189 c; 4190 FETCH relative - 1 FROM c INTO x; 4191 END LOOP; 4192 CLOSE c; 4193END; 4194$$ 4195LANGUAGE plpgsql; 4196 4197SELECT 4198 * 4199FROM 4200 sc_test (); 4201 4202CREATE OR REPLACE FUNCTION sc_test () 4203 RETURNS SETOF integer 4204 AS $$ 4205DECLARE 4206 c CURSOR FOR 4207 SELECT 4208 * 4209 FROM 4210 generate_series(1, 10); 4211 x integer; 4212BEGIN 4213 OPEN c; 4214 LOOP 4215 MOVE relative 2 IN c; 4216 IF NOT found THEN 4217 exit; 4218 END IF; 4219 FETCH NEXT FROM c INTO x; 4220 IF found THEN 4221 RETURN NEXT x; 4222 END IF; 4223 END LOOP; 4224 CLOSE c; 4225END; 4226$$ 4227LANGUAGE plpgsql; 4228 4229SELECT 4230 * 4231FROM 4232 sc_test (); 4233 4234CREATE OR REPLACE FUNCTION sc_test () 4235 RETURNS SETOF integer 4236 AS $$ 4237DECLARE 4238 c CURSOR FOR 4239 SELECT 4240 * 4241 FROM 4242 generate_series(1, 10); 4243 x integer; 4244BEGIN 4245 OPEN c; 4246 MOVE FORWARD ALL IN c; 4247 FETCH BACKWARD FROM c INTO x; 4248 IF found THEN 4249 RETURN NEXT x; 4250 END IF; 4251 CLOSE c; 4252END; 4253$$ 4254LANGUAGE plpgsql; 4255 4256SELECT 4257 * 4258FROM 4259 sc_test (); 4260 4261DROP FUNCTION sc_test (); 4262 4263-- test qualified variable names 4264CREATE FUNCTION pl_qual_names (param1 int) 4265 RETURNS void 4266 AS $$ 4267 << outerblock >> 4268DECLARE 4269 param1 int := 1; 4270BEGIN 4271 << innerblock >> DECLARE param1 int := 2; 4272 BEGIN 4273 RAISE NOTICE 'param1 = %', param1; 4274 RAISE NOTICE 'pl_qual_names.param1 = %', pl_qual_names.param1; 4275 RAISE NOTICE 'outerblock.param1 = %', outerblock.param1; 4276 RAISE NOTICE 'innerblock.param1 = %', innerblock.param1; 4277 END; 4278END; 4279$$ 4280LANGUAGE plpgsql; 4281 4282SELECT 4283 pl_qual_names (42); 4284 4285DROP FUNCTION pl_qual_names (int); 4286 4287-- tests for RETURN QUERY 4288CREATE FUNCTION ret_query1 (out int, out int) 4289 RETURNS SETOF record 4290 AS $$ 4291BEGIN 4292 $1 := - 1; 4293 $2 := - 2; 4294 RETURN NEXT; 4295 RETURN query 4296 SELECT 4297 x + 1, 4298 x * 10 4299 FROM 4300 generate_series(0, 10) s (x); 4301 RETURN NEXT; 4302END; 4303$$ 4304LANGUAGE plpgsql; 4305 4306SELECT 4307 * 4308FROM 4309 ret_query1 (); 4310 4311CREATE TYPE record_type AS ( 4312 x text, 4313 y int, 4314 z boolean 4315); 4316 4317CREATE OR REPLACE FUNCTION ret_query2 (lim int) 4318 RETURNS SETOF record_type 4319 AS $$ 4320BEGIN 4321 RETURN query 4322 SELECT 4323 md5(s.x::text), 4324 s.x, 4325 s.x > 0 4326 FROM 4327 generate_series(-8, lim) s (x) 4328WHERE 4329 s.x % 2 = 0; 4330END; 4331$$ 4332LANGUAGE plpgsql; 4333 4334SELECT 4335 * 4336FROM 4337 ret_query2 (8); 4338 4339-- test EXECUTE USING 4340CREATE FUNCTION exc_using (int, text) 4341 RETURNS int 4342 AS $$ 4343DECLARE 4344 i int; 4345BEGIN 4346 FOR i IN EXECUTE 'select * from generate_series(1,$1)' 4347 USING $1 + 1 LOOP 4348 RAISE NOTICE '%', i; 4349 END LOOP; 4350 EXECUTE 'select $2 + $2*3 + length($1)' INTO i 4351 USING $2, $1; 4352 RETURN i; 4353END 4354$$ 4355LANGUAGE plpgsql; 4356 4357SELECT 4358 exc_using (5, 'foobar'); 4359 4360DROP FUNCTION exc_using (int, text); 4361 4362CREATE OR REPLACE FUNCTION exc_using (int) 4363 RETURNS void 4364 AS $$ 4365DECLARE 4366 c refcursor; 4367 i int; 4368BEGIN 4369 OPEN c FOR EXECUTE 'select * from generate_series(1,$1)' 4370 USING $1 + 1; 4371 LOOP 4372 FETCH c INTO i; 4373 exit 4374 WHEN NOT found; 4375 RAISE NOTICE '%', i; 4376 END LOOP; 4377 CLOSE c; 4378 RETURN; 4379END; 4380$$ 4381LANGUAGE plpgsql; 4382 4383SELECT 4384 exc_using (5); 4385 4386DROP FUNCTION exc_using (int); 4387 4388-- test FOR-over-cursor 4389CREATE OR REPLACE FUNCTION forc01 () 4390 RETURNS void 4391 AS $$ 4392DECLARE 4393 c CURSOR (r1 integer, 4394 r2 integer) 4395 FOR 4396 SELECT 4397 * 4398 FROM 4399 generate_series(r1, r2) i; 4400 c2 CURSOR FOR 4401 SELECT 4402 * 4403 FROM 4404 generate_series(41, 43) i; 4405BEGIN 4406 FOR r IN c (5, 4407 7) 4408 LOOP 4409 RAISE NOTICE '% from %', r.i, c; 4410 END LOOP; 4411 -- again, to test if cursor was closed properly 4412 FOR r IN c (9, 4413 10) 4414 LOOP 4415 RAISE NOTICE '% from %', r.i, c; 4416 END LOOP; 4417 -- and test a parameterless cursor 4418 FOR r IN c2 LOOP 4419 RAISE NOTICE '% from %', r.i, c2; 4420 END LOOP; 4421 -- and try it with a hand-assigned name 4422 RAISE NOTICE 'after loop, c2 = %', c2; 4423 c2 := 'special_name'; 4424 FOR r IN c2 LOOP 4425 RAISE NOTICE '% from %', r.i, c2; 4426 END LOOP; 4427 RAISE NOTICE 'after loop, c2 = %', c2; 4428 -- and try it with a generated name 4429 -- (which we can't show in the output because it's variable) 4430 c2 := NULL; 4431 FOR r IN c2 LOOP 4432 RAISE NOTICE '%', r.i; 4433 END LOOP; 4434 RAISE NOTICE 'after loop, c2 = %', c2; 4435 RETURN; 4436END; 4437$$ 4438LANGUAGE plpgsql; 4439 4440SELECT 4441 forc01 (); 4442 4443-- try updating the cursor's current row 4444CREATE temp TABLE forc_test AS 4445SELECT 4446 n AS i, 4447 n AS j 4448FROM 4449 generate_series(1, 10) n; 4450 4451CREATE OR REPLACE FUNCTION forc01 () 4452 RETURNS void 4453 AS $$ 4454DECLARE 4455 c CURSOR FOR 4456 SELECT 4457 * 4458 FROM 4459 forc_test; 4460BEGIN 4461 FOR r IN c LOOP 4462 RAISE NOTICE '%, %', r.i, r.j; 4463 UPDATE 4464 forc_test 4465 SET 4466 i = i * 100, 4467 j = r.j * 2 4468 WHERE 4469 CURRENT OF c; 4470 END LOOP; 4471END; 4472$$ 4473LANGUAGE plpgsql; 4474 4475SELECT 4476 forc01 (); 4477 4478SELECT 4479 * 4480FROM 4481 forc_test; 4482 4483-- same, with a cursor whose portal name doesn't match variable name 4484CREATE OR REPLACE FUNCTION forc01 () 4485 RETURNS void 4486 AS $$ 4487DECLARE 4488 c refcursor := 'fooled_ya'; 4489 r record; 4490BEGIN 4491 OPEN c FOR 4492 SELECT 4493 * 4494 FROM 4495 forc_test; 4496 LOOP 4497 FETCH c INTO r; 4498 exit 4499 WHEN NOT found; 4500 RAISE NOTICE '%, %', r.i, r.j; 4501 UPDATE 4502 forc_test 4503 SET 4504 i = i * 100, 4505 j = r.j * 2 4506 WHERE 4507 CURRENT OF c; 4508 END LOOP; 4509END; 4510$$ 4511LANGUAGE plpgsql; 4512 4513SELECT 4514 forc01 (); 4515 4516SELECT 4517 * 4518FROM 4519 forc_test; 4520 4521DROP FUNCTION forc01 (); 4522 4523-- fail because cursor has no query bound to it 4524CREATE OR REPLACE FUNCTION forc_bad () 4525 RETURNS void 4526 AS $$ 4527DECLARE 4528 c refcursor; 4529BEGIN 4530 FOR r IN c LOOP 4531 RAISE NOTICE '%', r.i; 4532 END LOOP; 4533END; 4534$$ 4535LANGUAGE plpgsql; 4536 4537-- test RETURN QUERY EXECUTE 4538CREATE OR REPLACE FUNCTION return_dquery () 4539 RETURNS SETOF int 4540 AS $$ 4541BEGIN 4542 RETURN query EXECUTE 'select * from (values(10),(20)) f'; 4543 RETURN query EXECUTE 'select * from (values($1),($2)) f' 4544 USING 40, 50; 4545END; 4546$$ 4547LANGUAGE plpgsql; 4548 4549SELECT 4550 * 4551FROM 4552 return_dquery (); 4553 4554DROP FUNCTION return_dquery (); 4555 4556-- test RETURN QUERY with dropped columns 4557CREATE TABLE tabwithcols ( 4558 a int, 4559 b int, 4560 c int, 4561 d int 4562); 4563 4564INSERT INTO tabwithcols 4565 VALUES (10, 20, 30, 40), (50, 60, 70, 80); 4566 4567CREATE OR REPLACE FUNCTION returnqueryf () 4568 RETURNS SETOF tabwithcols 4569 AS $$ 4570BEGIN 4571 RETURN query 4572 SELECT 4573 * 4574 FROM 4575 tabwithcols; 4576 RETURN query EXECUTE 'select * from tabwithcols'; 4577END; 4578$$ 4579LANGUAGE plpgsql; 4580 4581SELECT 4582 * 4583FROM 4584 returnqueryf (); 4585 4586ALTER TABLE tabwithcols 4587 DROP COLUMN b; 4588 4589SELECT 4590 * 4591FROM 4592 returnqueryf (); 4593 4594ALTER TABLE tabwithcols 4595 DROP COLUMN d; 4596 4597SELECT 4598 * 4599FROM 4600 returnqueryf (); 4601 4602ALTER TABLE tabwithcols 4603 ADD COLUMN d int; 4604 4605SELECT 4606 * 4607FROM 4608 returnqueryf (); 4609 4610DROP FUNCTION returnqueryf (); 4611 4612DROP TABLE tabwithcols; 4613 4614-- 4615-- Tests for composite-type results 4616-- 4617CREATE TYPE compostype AS ( 4618 x int, 4619 y varchar 4620); 4621 4622-- test: use of variable of composite type in return statement 4623CREATE OR REPLACE FUNCTION compos () 4624 RETURNS compostype 4625 AS $$ 4626DECLARE 4627 v compostype; 4628BEGIN 4629 v := (1, 4630 'hello'); 4631 RETURN v; 4632END; 4633$$ 4634LANGUAGE plpgsql; 4635 4636SELECT 4637 compos (); 4638 4639-- test: use of variable of record type in return statement 4640CREATE OR REPLACE FUNCTION compos () 4641 RETURNS compostype 4642 AS $$ 4643DECLARE 4644 v record; 4645BEGIN 4646 v := (1, 4647 'hello'::varchar); 4648 RETURN v; 4649END; 4650$$ 4651LANGUAGE plpgsql; 4652 4653SELECT 4654 compos (); 4655 4656-- test: use of row expr in return statement 4657CREATE OR REPLACE FUNCTION compos () 4658 RETURNS compostype 4659 AS $$ 4660BEGIN 4661 RETURN (1, 4662 'hello'::varchar); 4663END; 4664$$ 4665LANGUAGE plpgsql; 4666 4667SELECT 4668 compos (); 4669 4670-- this does not work currently (no implicit casting) 4671CREATE OR REPLACE FUNCTION compos () 4672 RETURNS compostype 4673 AS $$ 4674BEGIN 4675 RETURN (1, 4676 'hello'); 4677END; 4678$$ 4679LANGUAGE plpgsql; 4680 4681SELECT 4682 compos (); 4683 4684-- ... but this does 4685CREATE OR REPLACE FUNCTION compos () 4686 RETURNS compostype 4687 AS $$ 4688BEGIN 4689 RETURN (1, 4690 'hello')::compostype; 4691END; 4692$$ 4693LANGUAGE plpgsql; 4694 4695SELECT 4696 compos (); 4697 4698DROP FUNCTION compos (); 4699 4700-- test: return a row expr as record. 4701CREATE OR REPLACE FUNCTION composrec () 4702 RETURNS record 4703 AS $$ 4704DECLARE 4705 v record; 4706BEGIN 4707 v := (1, 4708 'hello'); 4709 RETURN v; 4710END; 4711$$ 4712LANGUAGE plpgsql; 4713 4714SELECT 4715 composrec (); 4716 4717-- test: return row expr in return statement. 4718CREATE OR REPLACE FUNCTION composrec () 4719 RETURNS record 4720 AS $$ 4721BEGIN 4722 RETURN (1, 4723 'hello'); 4724END; 4725$$ 4726LANGUAGE plpgsql; 4727 4728SELECT 4729 composrec (); 4730 4731DROP FUNCTION composrec (); 4732 4733-- test: row expr in RETURN NEXT statement. 4734CREATE OR REPLACE FUNCTION compos () 4735 RETURNS SETOF compostype 4736 AS $$ 4737BEGIN 4738 FOR i IN 1..3 LOOP 4739 RETURN NEXT (1, 4740 'hello'::varchar); 4741 END LOOP; 4742 RETURN NEXT NULL::compostype; 4743 RETURN NEXT (2, 4744 'goodbye')::compostype; 4745END; 4746$$ 4747LANGUAGE plpgsql; 4748 4749SELECT 4750 * 4751FROM 4752 compos (); 4753 4754DROP FUNCTION compos (); 4755 4756-- test: use invalid expr in return statement. 4757CREATE OR REPLACE FUNCTION compos () 4758 RETURNS compostype 4759 AS $$ 4760BEGIN 4761 RETURN 1 + 1; 4762END; 4763$$ 4764LANGUAGE plpgsql; 4765 4766SELECT 4767 compos (); 4768 4769-- RETURN variable is a different code path ... 4770CREATE OR REPLACE FUNCTION compos () 4771 RETURNS compostype 4772 AS $$ 4773DECLARE 4774 x int := 42; 4775BEGIN 4776 RETURN x; 4777END; 4778$$ 4779LANGUAGE plpgsql; 4780 4781SELECT 4782 * 4783FROM 4784 compos (); 4785 4786DROP FUNCTION compos (); 4787 4788-- test: invalid use of composite variable in scalar-returning function 4789CREATE OR REPLACE FUNCTION compos () 4790 RETURNS int 4791 AS $$ 4792DECLARE 4793 v compostype; 4794BEGIN 4795 v := (1, 4796 'hello'); 4797 RETURN v; 4798END; 4799$$ 4800LANGUAGE plpgsql; 4801 4802SELECT 4803 compos (); 4804 4805-- test: invalid use of composite expression in scalar-returning function 4806CREATE OR REPLACE FUNCTION compos () 4807 RETURNS int 4808 AS $$ 4809BEGIN 4810 RETURN (1, 4811 'hello')::compostype; 4812END; 4813$$ 4814LANGUAGE plpgsql; 4815 4816SELECT 4817 compos (); 4818 4819DROP FUNCTION compos (); 4820 4821DROP TYPE compostype; 4822 4823-- 4824-- Tests for 8.4's new RAISE features 4825-- 4826CREATE OR REPLACE FUNCTION raise_test () 4827 RETURNS void 4828 AS $$ 4829BEGIN 4830 RAISE NOTICE '% % %', 1, 2, 3 4831 USING errcode = '55001', detail = 'some detail info', hint = 'some hint'; 4832 RAISE '% % %', 1, 2, 3 4833 USING errcode = 'division_by_zero', detail = 'some detail info'; 4834END; 4835$$ 4836LANGUAGE plpgsql; 4837 4838SELECT 4839 raise_test (); 4840 4841-- Since we can't actually see the thrown SQLSTATE in default psql output, 4842-- test it like this; this also tests re-RAISE 4843CREATE OR REPLACE FUNCTION raise_test () 4844 RETURNS void 4845 AS $$ 4846BEGIN 4847 RAISE 'check me' 4848 USING errcode = 'division_by_zero', detail = 'some detail info'; 4849EXCEPTION 4850 WHEN OTHERS THEN 4851 RAISE NOTICE 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; 4852 RAISE; 4853END; 4854 4855$$ 4856LANGUAGE plpgsql; 4857 4858SELECT 4859 raise_test (); 4860 4861CREATE OR REPLACE FUNCTION raise_test () 4862 RETURNS void 4863 AS $$ 4864BEGIN 4865 RAISE 'check me' 4866 USING errcode = '1234F', detail = 'some detail info'; 4867EXCEPTION 4868 WHEN OTHERS THEN 4869 RAISE NOTICE 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; 4870 RAISE; 4871END; 4872 4873$$ 4874LANGUAGE plpgsql; 4875 4876SELECT 4877 raise_test (); 4878 4879-- SQLSTATE specification in WHEN 4880CREATE OR REPLACE FUNCTION raise_test () 4881 RETURNS void 4882 AS $$ 4883BEGIN 4884 RAISE 'check me' 4885 USING errcode = '1234F', detail = 'some detail info'; 4886EXCEPTION 4887 WHEN sqlstate '1234F' THEN 4888 RAISE NOTICE 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; 4889 RAISE; 4890END; 4891 4892$$ 4893LANGUAGE plpgsql; 4894 4895SELECT 4896 raise_test (); 4897 4898CREATE OR REPLACE FUNCTION raise_test () 4899 RETURNS void 4900 AS $$ 4901BEGIN 4902 RAISE division_by_zero 4903 USING detail = 'some detail info'; 4904EXCEPTION 4905 WHEN OTHERS THEN 4906 RAISE NOTICE 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; 4907 RAISE; 4908END; 4909 4910$$ 4911LANGUAGE plpgsql; 4912 4913SELECT 4914 raise_test (); 4915 4916CREATE OR REPLACE FUNCTION raise_test () 4917 RETURNS void 4918 AS $$ 4919BEGIN 4920 RAISE division_by_zero; 4921END; 4922$$ 4923LANGUAGE plpgsql; 4924 4925SELECT 4926 raise_test (); 4927 4928CREATE OR REPLACE FUNCTION raise_test () 4929 RETURNS void 4930 AS $$ 4931BEGIN 4932 RAISE sqlstate '1234F'; 4933END; 4934$$ 4935LANGUAGE plpgsql; 4936 4937SELECT 4938 raise_test (); 4939 4940CREATE OR REPLACE FUNCTION raise_test () 4941 RETURNS void 4942 AS $$ 4943BEGIN 4944 RAISE division_by_zero 4945 USING message = 'custom' || ' message'; 4946END; 4947$$ 4948LANGUAGE plpgsql; 4949 4950SELECT 4951 raise_test (); 4952 4953CREATE OR REPLACE FUNCTION raise_test () 4954 RETURNS void 4955 AS $$ 4956BEGIN 4957 RAISE 4958 USING message = 'custom' || ' message', errcode = '22012'; 4959END; 4960$$ 4961LANGUAGE plpgsql; 4962 4963SELECT 4964 raise_test (); 4965 4966-- conflict on message 4967CREATE OR REPLACE FUNCTION raise_test () 4968 RETURNS void 4969 AS $$ 4970BEGIN 4971 RAISE NOTICE 'some message' 4972 USING message = 'custom' || ' message', errcode = '22012'; 4973END; 4974$$ 4975LANGUAGE plpgsql; 4976 4977SELECT 4978 raise_test (); 4979 4980-- conflict on errcode 4981CREATE OR REPLACE FUNCTION raise_test () 4982 RETURNS void 4983 AS $$ 4984BEGIN 4985 RAISE division_by_zero 4986 USING message = 'custom' || ' message', errcode = '22012'; 4987END; 4988$$ 4989LANGUAGE plpgsql; 4990 4991SELECT 4992 raise_test (); 4993 4994-- nothing to re-RAISE 4995CREATE OR REPLACE FUNCTION raise_test () 4996 RETURNS void 4997 AS $$ 4998BEGIN 4999 RAISE; 5000END; 5001$$ 5002LANGUAGE plpgsql; 5003 5004SELECT 5005 raise_test (); 5006 5007-- test access to exception data 5008CREATE FUNCTION zero_divide () 5009 RETURNS int 5010 AS $$ 5011DECLARE 5012 v int := 0; 5013BEGIN 5014 RETURN 10 / v; 5015END; 5016$$ 5017LANGUAGE plpgsql; 5018 5019CREATE OR REPLACE FUNCTION raise_test () 5020 RETURNS void 5021 AS $$ 5022BEGIN 5023 RAISE EXCEPTION 'custom exception' 5024 USING detail = 'some detail of custom exception', hint = 'some hint related to custom exception'; 5025END; 5026$$ 5027LANGUAGE plpgsql; 5028 5029CREATE FUNCTION stacked_diagnostics_test () 5030 RETURNS void 5031 AS $$ 5032DECLARE 5033 _sqlstate text; 5034 _message text; 5035 _context text; 5036BEGIN 5037 PERFORM 5038 zero_divide (); 5039EXCEPTION 5040 WHEN OTHERS THEN 5041 get stacked diagnostics _sqlstate = returned_sqlstate, 5042 _message = message_text, 5043 _context = pg_exception_context; 5044 RAISE NOTICE 'sqlstate: %, message: %, context: [%]', _sqlstate, _message, replace(_context, E'\n', ' <- '); 5045END; 5046 5047$$ 5048LANGUAGE plpgsql; 5049 5050SELECT 5051 stacked_diagnostics_test (); 5052 5053CREATE OR REPLACE FUNCTION stacked_diagnostics_test () 5054 RETURNS void 5055 AS $$ 5056DECLARE 5057 _detail text; 5058 _hint text; 5059 _message text; 5060BEGIN 5061 PERFORM 5062 raise_test (); 5063EXCEPTION 5064 WHEN OTHERS THEN 5065 get stacked diagnostics _message = message_text, 5066 _detail = pg_exception_detail, 5067 _hint = pg_exception_hint; 5068 RAISE NOTICE 'message: %, detail: %, hint: %', _message, _detail, _hint; 5069END; 5070 5071$$ 5072LANGUAGE plpgsql; 5073 5074SELECT 5075 stacked_diagnostics_test (); 5076 5077-- fail, cannot use stacked diagnostics statement outside handler 5078CREATE OR REPLACE FUNCTION stacked_diagnostics_test () 5079 RETURNS void 5080 AS $$ 5081DECLARE 5082 _detail text; 5083 _hint text; 5084 _message text; 5085BEGIN 5086 get stacked diagnostics _message = message_text, 5087 _detail = pg_exception_detail, 5088 _hint = pg_exception_hint; 5089 RAISE NOTICE 'message: %, detail: %, hint: %', _message, _detail, _hint; 5090END; 5091$$ 5092LANGUAGE plpgsql; 5093 5094SELECT 5095 stacked_diagnostics_test (); 5096 5097DROP FUNCTION zero_divide (); 5098 5099DROP FUNCTION stacked_diagnostics_test (); 5100 5101-- check cases where implicit SQLSTATE variable could be confused with 5102-- SQLSTATE as a keyword, cf bug #5524 5103CREATE OR REPLACE FUNCTION raise_test () 5104 RETURNS void 5105 AS $$ 5106BEGIN 5107 PERFORM 5108 1 / 0; 5109EXCEPTION 5110 WHEN sqlstate '22012' THEN 5111 RAISE NOTICE USING message = sqlstate; 5112 RAISE sqlstate '22012' USING message = 'substitute message'; 5113END; 5114 5115$$ 5116LANGUAGE plpgsql; 5117 5118SELECT 5119 raise_test (); 5120 5121DROP FUNCTION raise_test (); 5122 5123-- test passing column_name, constraint_name, datatype_name, table_name 5124-- and schema_name error fields 5125CREATE OR REPLACE FUNCTION stacked_diagnostics_test () 5126 RETURNS void 5127 AS $$ 5128DECLARE 5129 _column_name text; 5130 _constraint_name text; 5131 _datatype_name text; 5132 _table_name text; 5133 _schema_name text; 5134BEGIN 5135 RAISE EXCEPTION 5136 USING COLUMN = '>>some column name<<', CONSTRAINT = '>>some constraint name<<', datatype = '>>some datatype name<<', TABLE = '>>some table name<<', schema = '>>some schema name<<'; 5137EXCEPTION 5138 WHEN OTHERS THEN 5139 get stacked diagnostics _column_name = column_name, 5140 _constraint_name = constraint_name, 5141 _datatype_name = pg_datatype_name, 5142 _table_name = table_name, 5143 _schema_name = schema_name; 5144 RAISE NOTICE 'column %, constraint %, type %, table %, schema %', _column_name, _constraint_name, _datatype_name, _table_name, _schema_name; 5145END; 5146 5147$$ 5148LANGUAGE plpgsql; 5149 5150SELECT 5151 stacked_diagnostics_test (); 5152 5153DROP FUNCTION stacked_diagnostics_test (); 5154 5155-- test variadic functions 5156CREATE OR REPLACE FUNCTION vari (VARIADIC int[]) 5157 RETURNS void 5158 AS $$ 5159BEGIN 5160 FOR i IN array_lower($1, 1)..array_upper($1, 1) 5161 LOOP 5162 RAISE NOTICE '%', $1[i]; 5163 END LOOP; 5164END; 5165$$ 5166LANGUAGE plpgsql; 5167 5168SELECT 5169 vari (1, 2, 3, 4, 5); 5170 5171SELECT 5172 vari (3, 4, 5); 5173 5174SELECT 5175 vari (VARIADIC ARRAY[5, 6, 7]); 5176 5177DROP FUNCTION vari (int[]); 5178 5179-- coercion test 5180CREATE OR REPLACE FUNCTION pleast (VARIADIC numeric[]) 5181 RETURNS numeric 5182 AS $$ 5183DECLARE 5184 aux numeric = $1[array_lower($1, 1)]; 5185BEGIN 5186 FOR i IN array_lower($1, 1) + 1..array_upper($1, 1) 5187 LOOP 5188 IF $1[i] < aux THEN 5189 aux := $1[i]; 5190 END IF; 5191 END LOOP; 5192 RETURN aux; 5193END; 5194$$ 5195LANGUAGE plpgsql 5196IMMUTABLE STRICT; 5197 5198SELECT 5199 pleast (10, 1, 2, 3, -16); 5200 5201SELECT 5202 pleast (10.2, 2.2, -1.1); 5203 5204SELECT 5205 pleast (10.2, 10, -20); 5206 5207SELECT 5208 pleast (10, 20, -1.0); 5209 5210-- in case of conflict, non-variadic version is preferred 5211CREATE OR REPLACE FUNCTION pleast (numeric) 5212 RETURNS numeric 5213 AS $$ 5214BEGIN 5215 RAISE NOTICE 'non-variadic function called'; 5216 RETURN $1; 5217END; 5218$$ 5219LANGUAGE plpgsql 5220IMMUTABLE STRICT; 5221 5222SELECT 5223 pleast (10); 5224 5225DROP FUNCTION pleast (numeric[]); 5226 5227DROP FUNCTION pleast (numeric); 5228 5229-- test table functions 5230CREATE FUNCTION tftest (int) 5231 RETURNS TABLE ( 5232 a int, 5233 b int 5234 ) 5235 AS $$ 5236BEGIN 5237 RETURN query 5238 SELECT 5239 $1, 5240 $1 + i 5241 FROM 5242 generate_series(1, 5) g (i); 5243END; 5244$$ 5245LANGUAGE plpgsql 5246IMMUTABLE STRICT; 5247 5248SELECT 5249 * 5250FROM 5251 tftest (10); 5252 5253CREATE OR REPLACE FUNCTION tftest (a1 int) 5254 RETURNS TABLE ( 5255 a int, 5256 b int 5257 ) 5258 AS $$ 5259BEGIN 5260 a := a1; 5261 b := a1 + 1; 5262 RETURN NEXT; 5263 a := a1 * 10; 5264 b := a1 * 10 + 1; 5265 RETURN NEXT; 5266END; 5267$$ 5268LANGUAGE plpgsql 5269IMMUTABLE STRICT; 5270 5271SELECT 5272 * 5273FROM 5274 tftest (10); 5275 5276DROP FUNCTION tftest (int); 5277 5278CREATE OR REPLACE FUNCTION rttest () 5279 RETURNS SETOF int 5280 AS $$ 5281DECLARE 5282 rc int; 5283 rca int[]; 5284BEGIN 5285 RETURN query 5286VALUES (10), 5287(20); 5288 get diagnostics rc = row_count; 5289 RAISE NOTICE '% %', found, rc; 5290 RETURN query 5291 SELECT 5292 * 5293 FROM ( 5294 VALUES (10), 5295 (20)) f (a) 5296WHERE 5297 FALSE; 5298 get diagnostics rc = row_count; 5299 RAISE NOTICE '% %', found, rc; 5300 RETURN query EXECUTE 'values(10),(20)'; 5301 -- just for fun, let's use array elements as targets 5302 get diagnostics rca[1] = row_count; 5303 RAISE NOTICE '% %', found, rca[1]; 5304 RETURN query EXECUTE 'select * from (values(10),(20)) f(a) where false'; 5305 get diagnostics rca[2] = row_count; 5306 RAISE NOTICE '% %', found, rca[2]; 5307END; 5308$$ 5309LANGUAGE plpgsql; 5310 5311SELECT 5312 * 5313FROM 5314 rttest (); 5315 5316DROP FUNCTION rttest (); 5317 5318-- Test for proper cleanup at subtransaction exit. This example 5319-- exposed a bug in PG 8.2. 5320CREATE FUNCTION leaker_1 (fail bool) 5321 RETURNS integer 5322 AS $$ 5323DECLARE 5324 v_var integer; 5325BEGIN 5326 BEGIN 5327 v_var := (leaker_2 (fail)).error_code; 5328 EXCEPTION 5329 WHEN OTHERS THEN 5330 RETURN 0; 5331 END; 5332 RETURN 1; 5333END; 5334 5335$$ 5336LANGUAGE plpgsql; 5337 5338CREATE FUNCTION leaker_2 (fail bool, OUT error_code integer, OUT new_id integer) 5339 RETURNS RECORD 5340 AS $$ 5341BEGIN 5342 IF fail THEN 5343 RAISE EXCEPTION 'fail ...'; 5344 END IF; 5345 error_code := 1; 5346 new_id := 1; 5347 RETURN; 5348END; 5349$$ 5350LANGUAGE plpgsql; 5351 5352SELECT 5353 * 5354FROM 5355 leaker_1 (FALSE); 5356 5357SELECT 5358 * 5359FROM 5360 leaker_1 (TRUE); 5361 5362DROP FUNCTION leaker_1 (bool); 5363 5364DROP FUNCTION leaker_2 (bool); 5365 5366-- Test for appropriate cleanup of non-simple expression evaluations 5367-- (bug in all versions prior to August 2010) 5368CREATE FUNCTION nonsimple_expr_test () 5369 RETURNS text[] 5370 AS $$ 5371DECLARE 5372 arr text[]; 5373 lr text; 5374 i integer; 5375BEGIN 5376 arr := ARRAY[ARRAY['foo', 'bar'], ARRAY['baz', 'quux']]; 5377 lr := 'fool'; 5378 i := 1; 5379 -- use sub-SELECTs to make expressions non-simple 5380 arr[( 5381 SELECT 5382 i)][( 5383 SELECT 5384 i + 1)] := ( 5385 SELECT 5386 lr); 5387 RETURN arr; 5388END; 5389$$ 5390LANGUAGE plpgsql; 5391 5392SELECT 5393 nonsimple_expr_test (); 5394 5395DROP FUNCTION nonsimple_expr_test (); 5396 5397CREATE FUNCTION nonsimple_expr_test () 5398 RETURNS integer 5399 AS $$ 5400DECLARE 5401 i integer NOT NULL := 0; 5402BEGIN 5403 BEGIN 5404 i := ( 5405 SELECT 5406 NULL::integer); 5407 -- should throw error 5408 EXCEPTION 5409 WHEN OTHERS THEN 5410 i := ( 5411 SELECT 5412 1::integer); 5413 END; 5414 RETURN i; 5415END; 5416 5417$$ 5418LANGUAGE plpgsql; 5419 5420SELECT 5421 nonsimple_expr_test (); 5422 5423DROP FUNCTION nonsimple_expr_test (); 5424 5425-- 5426-- Test cases involving recursion and error recovery in simple expressions 5427-- (bugs in all versions before October 2010). The problems are most 5428-- easily exposed by mutual recursion between plpgsql and sql functions. 5429-- 5430CREATE FUNCTION recurse (float8) 5431 RETURNS float8 5432 AS $$ 5433BEGIN 5434 IF ($1 > 0) THEN 5435 RETURN sql_recurse ($1 - 1); 5436 ELSE 5437 RETURN $1; 5438 END IF; 5439END; 5440$$ 5441LANGUAGE plpgsql; 5442 5443-- "limit" is to prevent this from being inlined 5444CREATE FUNCTION sql_recurse (float8) 5445 RETURNS float8 5446 AS $$ 5447 SELECT 5448 recurse ($1) 5449 LIMIT 1; 5450 5451$$ 5452LANGUAGE sql; 5453 5454SELECT 5455 recurse (10); 5456 5457CREATE FUNCTION error1 (text) 5458 RETURNS text 5459 LANGUAGE sql 5460 AS $$ 5461 SELECT 5462 relname::text 5463 FROM 5464 pg_class c 5465 WHERE 5466 c.oid = $1::regclass 5467$$; 5468 5469CREATE FUNCTION error2 (p_name_table text) 5470 RETURNS text 5471 LANGUAGE plpgsql 5472 AS $$ 5473BEGIN 5474 RETURN error1 (p_name_table); 5475END 5476$$; 5477 5478BEGIN; 5479CREATE TABLE public.stuffs ( 5480 stuff text 5481); 5482SAVEPOINT a; 5483SELECT 5484 error2 ('nonexistent.stuffs'); 5485ROLLBACK TO a; 5486 5487SELECT 5488 error2 ('public.stuffs'); 5489 5490ROLLBACK; 5491 5492DROP FUNCTION error2 (p_name_table text); 5493 5494DROP FUNCTION error1 (text); 5495 5496-- Test for proper handling of cast-expression caching 5497CREATE FUNCTION sql_to_date (integer) 5498 RETURNS date 5499 AS $$ 5500 SELECT 5501 $1::text::date 5502$$ 5503LANGUAGE sql 5504IMMUTABLE STRICT; 5505 5506CREATE CAST (integer AS date) WITH FUNCTION sql_to_date (integer) AS assignment; 5507 5508CREATE FUNCTION cast_invoker (integer) 5509 RETURNS date 5510 AS $$ 5511BEGIN 5512 RETURN $1; 5513END 5514$$ 5515LANGUAGE plpgsql; 5516 5517SELECT 5518 cast_invoker (20150717); 5519 5520SELECT 5521 cast_invoker (20150718); 5522 5523-- second call crashed in pre-release 9.5 5524BEGIN; 5525SELECT 5526 cast_invoker (20150717); 5527SELECT 5528 cast_invoker (20150718); 5529SAVEPOINT s1; 5530SELECT 5531 cast_invoker (20150718); 5532SELECT 5533 cast_invoker (-1); 5534-- fails 5535ROLLBACK TO SAVEPOINT s1; 5536 5537SELECT 5538 cast_invoker (20150719); 5539 5540SELECT 5541 cast_invoker (20150720); 5542 5543COMMIT; 5544 5545DROP FUNCTION cast_invoker (integer); 5546 5547DROP FUNCTION sql_to_date (integer) CASCADE; 5548 5549-- Test handling of cast cache inside DO blocks 5550-- (to check the original crash case, this must be a cast not previously 5551-- used in this session) 5552BEGIN; 5553DO $$ 5554DECLARE 5555 x text[]; 5556BEGIN 5557 x := '{1.23, 4.56}'::numeric[]; 5558END 5559$$; 5560DO $$ 5561DECLARE 5562 x text[]; 5563BEGIN 5564 x := '{1.23, 4.56}'::numeric[]; 5565END 5566$$; 5567END; 5568-- Test for consistent reporting of error context 5569CREATE FUNCTION fail () 5570 RETURNS int 5571 LANGUAGE plpgsql 5572 AS $$ 5573BEGIN 5574 RETURN 1 / 0; 5575END 5576$$; 5577SELECT 5578 fail (); 5579SELECT 5580 fail (); 5581DROP FUNCTION fail (); 5582-- Test handling of string literals. 5583SET standard_conforming_strings = OFF; 5584CREATE OR REPLACE FUNCTION strtest () 5585 RETURNS text 5586 AS $$ 5587BEGIN 5588 RAISE NOTICE 'foo\\bar\041baz'; 5589 RETURN 'foo\\bar\041baz'; 5590END 5591$$ 5592LANGUAGE plpgsql; 5593SELECT 5594 strtest (); 5595CREATE OR REPLACE FUNCTION strtest () 5596 RETURNS text 5597 AS $$ 5598BEGIN 5599 RAISE NOTICE E'foo\\bar\041baz'; 5600 RETURN E'foo\\bar\041baz'; 5601END 5602$$ 5603LANGUAGE plpgsql; 5604SELECT 5605 strtest (); 5606SET standard_conforming_strings = ON; 5607CREATE OR REPLACE FUNCTION strtest () 5608 RETURNS text 5609 AS $$ 5610BEGIN 5611 RAISE NOTICE 'foo\\bar\041baz\'; 5612 return ' foo\\bar 041baz\'; 5613END 5614$$ 5615LANGUAGE plpgsql; 5616SELECT 5617 strtest (); 5618CREATE OR REPLACE FUNCTION strtest () 5619 RETURNS text 5620 AS $$ 5621BEGIN 5622 RAISE NOTICE E'foo\\bar\041baz'; 5623 RETURN E'foo\\bar\041baz'; 5624END 5625$$ 5626LANGUAGE plpgsql; 5627SELECT 5628 strtest (); 5629DROP FUNCTION strtest (); 5630-- Test anonymous code blocks. 5631DO $$ 5632DECLARE 5633 r record; 5634BEGIN 5635 FOR r IN 5636 SELECT 5637 rtrim(roomno) AS roomno, 5638 comment 5639 FROM 5640 Room 5641 ORDER BY 5642 roomno LOOP 5643 RAISE NOTICE '%, %', r.roomno, r.comment; 5644 END LOOP; 5645END 5646$$; 5647-- these are to check syntax error reporting 5648DO 5649LANGUAGE plpgsql 5650$$ 5651BEGIN 5652 RETURN 1; 5653END $$; 5654DO $$ 5655DECLARE 5656 r record; 5657BEGIN 5658 FOR r IN 5659 SELECT 5660 rtrim(roomno) AS roomno, 5661 foo 5662 FROM 5663 Room 5664 ORDER BY 5665 roomno LOOP 5666 RAISE NOTICE '%, %', r.roomno, r.comment; 5667 END LOOP; 5668END 5669$$; 5670-- Check handling of errors thrown from/into anonymous code blocks. 5671DO $outer$ 5672BEGIN 5673 FOR i IN 1..10 LOOP 5674 BEGIN 5675 EXECUTE $ex$ 5676 do $$ 5677 declare x int = 0; 5678 begin 5679 x := 1 / x; 5680 end; 5681 $$; 5682 $ex$; 5683 EXCEPTION 5684 WHEN division_by_zero THEN 5685 RAISE NOTICE 'caught division by zero'; 5686 END; 5687 END LOOP; 5688END; 5689$outer$; 5690-- Check variable scoping -- a var is not available in its own or prior 5691-- default expressions. 5692CREATE FUNCTION scope_test () 5693 RETURNS int 5694 AS $$ 5695DECLARE 5696 x int := 42; 5697BEGIN 5698 DECLARE y int := x + 1; 5699 x int := x + 2; 5700 BEGIN 5701 RETURN x * 100 + y; 5702 END; 5703END; 5704$$ 5705LANGUAGE plpgsql; 5706SELECT 5707 scope_test (); 5708DROP FUNCTION scope_test (); 5709-- Check handling of conflicts between plpgsql vars and table columns. 5710SET plpgsql.variable_conflict = error; 5711CREATE FUNCTION conflict_test () 5712 RETURNS SETOF int8_tbl 5713 AS $$ 5714DECLARE 5715 r record; 5716 q1 bigint := 42; 5717BEGIN 5718 FOR r IN 5719 SELECT 5720 q1, 5721 q2 5722 FROM 5723 int8_tbl LOOP 5724 RETURN NEXT r; 5725 END LOOP; 5726END; 5727$$ 5728LANGUAGE plpgsql; 5729SELECT 5730 * 5731FROM 5732 conflict_test (); 5733CREATE OR REPLACE FUNCTION conflict_test () 5734 RETURNS SETOF int8_tbl 5735 AS $$ 5736 # variable_conflict use_variable 5737DECLARE 5738 r record; 5739 q1 bigint := 42; 5740BEGIN 5741 FOR r IN 5742 SELECT 5743 q1, 5744 q2 5745 FROM 5746 int8_tbl LOOP 5747 RETURN NEXT r; 5748 END LOOP; 5749END; 5750$$ 5751LANGUAGE plpgsql; 5752SELECT 5753 * 5754FROM 5755 conflict_test (); 5756CREATE OR REPLACE FUNCTION conflict_test () 5757 RETURNS SETOF int8_tbl 5758 AS $$ 5759 # variable_conflict use_column 5760DECLARE 5761 r record; 5762 q1 bigint := 42; 5763BEGIN 5764 FOR r IN 5765 SELECT 5766 q1, 5767 q2 5768 FROM 5769 int8_tbl LOOP 5770 RETURN NEXT r; 5771 END LOOP; 5772END; 5773$$ 5774LANGUAGE plpgsql; 5775SELECT 5776 * 5777FROM 5778 conflict_test (); 5779DROP FUNCTION conflict_test (); 5780-- Check that an unreserved keyword can be used as a variable name 5781CREATE FUNCTION unreserved_test () 5782 RETURNS int 5783 AS $$ 5784DECLARE 5785 FORWARD int := 21; 5786BEGIN 5787 FORWARD := FORWARD * 2; 5788 RETURN FORWARD; 5789END 5790$$ 5791LANGUAGE plpgsql; 5792SELECT 5793 unreserved_test (); 5794CREATE OR REPLACE FUNCTION unreserved_test () 5795 RETURNS int 5796 AS $$ 5797DECLARE 5798 RETURN int := 42; 5799BEGIN 5800 RETURN := RETURN +1; 5801 RETURN RETURN; 5802END 5803$$ 5804LANGUAGE plpgsql; 5805SELECT 5806 unreserved_test (); 5807CREATE OR REPLACE FUNCTION unreserved_test () 5808 RETURNS int 5809 AS $$ 5810DECLARE 5811 comment int := 21; 5812BEGIN 5813 comment := comment * 2; 5814 COMMENT ON FUNCTION unreserved_test () IS 'this is a test'; 5815 RETURN comment; 5816END 5817$$ 5818LANGUAGE plpgsql; 5819SELECT 5820 unreserved_test (); 5821SELECT 5822 obj_description('unreserved_test()'::regprocedure, 'pg_proc'); 5823DROP FUNCTION unreserved_test (); 5824-- 5825-- Test FOREACH over arrays 5826-- 5827CREATE FUNCTION foreach_test (anyarray) 5828 RETURNS void 5829 AS $$ 5830DECLARE 5831 x int; 5832BEGIN 5833 foreach x IN ARRAY $1 LOOP 5834 RAISE NOTICE '%', x; 5835 END LOOP; 5836END; 5837$$ 5838LANGUAGE plpgsql; 5839SELECT 5840 foreach_test (ARRAY[1, 2, 3, 4]); 5841SELECT 5842 foreach_test (ARRAY[[1, 2],[3, 4]]); 5843CREATE OR REPLACE FUNCTION foreach_test (anyarray) 5844 RETURNS void 5845 AS $$ 5846DECLARE 5847 x int; 5848BEGIN 5849 foreach x slice 1 IN ARRAY $1 LOOP 5850 RAISE NOTICE '%', x; 5851 END LOOP; 5852END; 5853$$ 5854LANGUAGE plpgsql; 5855-- should fail 5856SELECT 5857 foreach_test (ARRAY[1, 2, 3, 4]); 5858SELECT 5859 foreach_test (ARRAY[[1, 2],[3, 4]]); 5860CREATE OR REPLACE FUNCTION foreach_test (anyarray) 5861 RETURNS void 5862 AS $$ 5863DECLARE 5864 x int[]; 5865BEGIN 5866 foreach x slice 1 IN ARRAY $1 LOOP 5867 RAISE NOTICE '%', x; 5868 END LOOP; 5869END; 5870$$ 5871LANGUAGE plpgsql; 5872SELECT 5873 foreach_test (ARRAY[1, 2, 3, 4]); 5874SELECT 5875 foreach_test (ARRAY[[1, 2],[3, 4]]); 5876-- higher level of slicing 5877CREATE OR REPLACE FUNCTION foreach_test (anyarray) 5878 RETURNS void 5879 AS $$ 5880DECLARE 5881 x int[]; 5882BEGIN 5883 foreach x slice 2 IN ARRAY $1 LOOP 5884 RAISE NOTICE '%', x; 5885 END LOOP; 5886END; 5887$$ 5888LANGUAGE plpgsql; 5889-- should fail 5890SELECT 5891 foreach_test (ARRAY[1, 2, 3, 4]); 5892-- ok 5893SELECT 5894 foreach_test (ARRAY[[1, 2],[3, 4]]); 5895SELECT 5896 foreach_test (ARRAY[[[1, 2]],[[3, 4]]]); 5897CREATE TYPE xy_tuple AS ( 5898 x int, 5899 y int 5900); 5901-- iteration over array of records 5902CREATE OR REPLACE FUNCTION foreach_test (anyarray) 5903 RETURNS void 5904 AS $$ 5905DECLARE 5906 r record; 5907BEGIN 5908 foreach r IN ARRAY $1 LOOP 5909 RAISE NOTICE '%', r; 5910 END LOOP; 5911END; 5912$$ 5913LANGUAGE plpgsql; 5914SELECT 5915 foreach_test (ARRAY[(10, 20), (40, 69), (35, 78)]::xy_tuple[]); 5916SELECT 5917 foreach_test (ARRAY[[(10, 20), (40, 69)],[(35, 78), (88, 76)]]::xy_tuple[]); 5918CREATE OR REPLACE FUNCTION foreach_test (anyarray) 5919 RETURNS void 5920 AS $$ 5921DECLARE 5922 x int; 5923 y int; 5924BEGIN 5925 foreach x, 5926 y IN ARRAY $1 LOOP 5927 RAISE NOTICE 'x = %, y = %', x, y; 5928 END LOOP; 5929END; 5930$$ 5931LANGUAGE plpgsql; 5932SELECT 5933 foreach_test (ARRAY[(10, 20), (40, 69), (35, 78)]::xy_tuple[]); 5934SELECT 5935 foreach_test (ARRAY[[(10, 20), (40, 69)],[(35, 78), (88, 76)]]::xy_tuple[]); 5936-- slicing over array of composite types 5937CREATE OR REPLACE FUNCTION foreach_test (anyarray) 5938 RETURNS void 5939 AS $$ 5940DECLARE 5941 x xy_tuple[]; 5942BEGIN 5943 foreach x slice 1 IN ARRAY $1 LOOP 5944 RAISE NOTICE '%', x; 5945 END LOOP; 5946END; 5947$$ 5948LANGUAGE plpgsql; 5949SELECT 5950 foreach_test (ARRAY[(10, 20), (40, 69), (35, 78)]::xy_tuple[]); 5951SELECT 5952 foreach_test (ARRAY[[(10, 20), (40, 69)],[(35, 78), (88, 76)]]::xy_tuple[]); 5953DROP FUNCTION foreach_test (anyarray); 5954DROP TYPE xy_tuple; 5955-- 5956-- Assorted tests for array subscript assignment 5957-- 5958CREATE temp TABLE rtype ( 5959 id int, 5960 ar text[] 5961); 5962CREATE FUNCTION arrayassign1 () 5963 RETURNS text[] 5964 LANGUAGE plpgsql 5965 AS $$ 5966DECLARE 5967 r record; 5968BEGIN 5969 r := ROW (12, 5970 '{foo,bar,baz}')::rtype; 5971 r.ar[2] := 'replace'; 5972 RETURN r.ar; 5973END 5974$$; 5975SELECT 5976 arrayassign1 (); 5977SELECT 5978 arrayassign1 (); 5979-- try again to exercise internal caching 5980CREATE DOMAIN orderedarray AS int[2] CONSTRAINT sorted CHECK (value[1] < value[2]); 5981SELECT 5982 '{1,2}'::orderedarray; 5983SELECT 5984 '{2,1}'::orderedarray; 5985-- fail 5986CREATE FUNCTION testoa (x1 int, x2 int, x3 int) 5987 RETURNS orderedarray 5988 LANGUAGE plpgsql 5989 AS $$ 5990DECLARE 5991 res orderedarray; 5992BEGIN 5993 res := ARRAY[x1, x2]; 5994 res[2] := x3; 5995 RETURN res; 5996END 5997$$; 5998SELECT 5999 testoa (1, 2, 3); 6000SELECT 6001 testoa (1, 2, 3); 6002-- try again to exercise internal caching 6003SELECT 6004 testoa (2, 1, 3); 6005-- fail at initial assign 6006SELECT 6007 testoa (1, 2, 1); 6008-- fail at update 6009DROP FUNCTION arrayassign1 (); 6010DROP FUNCTION testoa (x1 int, x2 int, x3 int); 6011-- 6012-- Test handling of expanded arrays 6013-- 6014CREATE FUNCTION returns_rw_array (int) 6015 RETURNS int[] 6016 LANGUAGE plpgsql 6017 AS $$ 6018DECLARE 6019 r int[]; 6020BEGIN 6021 r := ARRAY[$1, $1]; 6022 RETURN r; 6023END; 6024$$ STABLE; 6025CREATE FUNCTION consumes_rw_array (int[]) 6026 RETURNS int 6027 LANGUAGE plpgsql 6028 AS $$ 6029BEGIN 6030 RETURN $1[1]; 6031END; 6032$$ STABLE; 6033SELECT 6034 consumes_rw_array (returns_rw_array (42)); 6035-- bug #14174 6036EXPLAIN ( 6037 VERBOSE, 6038 COSTS OFF 6039) 6040SELECT 6041 i, 6042 a 6043FROM ( 6044 SELECT 6045 returns_rw_array (1) AS a offset 0) ss, 6046 LATERAL consumes_rw_array (a) i; 6047SELECT 6048 i, 6049 a 6050FROM ( 6051 SELECT 6052 returns_rw_array (1) AS a offset 0) ss, 6053 LATERAL consumes_rw_array (a) i; 6054EXPLAIN ( 6055 VERBOSE, 6056 COSTS OFF 6057) 6058SELECT 6059 consumes_rw_array (a), 6060 a 6061FROM 6062 returns_rw_array (1) a; 6063SELECT 6064 consumes_rw_array (a), 6065 a 6066FROM 6067 returns_rw_array (1) a; 6068EXPLAIN ( 6069 VERBOSE, 6070 COSTS OFF 6071) 6072SELECT 6073 consumes_rw_array (a), 6074 a 6075FROM ( 6076 VALUES (returns_rw_array (1)), 6077 (returns_rw_array (2))) v (a); 6078SELECT 6079 consumes_rw_array (a), 6080 a 6081FROM ( 6082 VALUES (returns_rw_array (1)), 6083 (returns_rw_array (2))) v (a); 6084DO $$ 6085DECLARE 6086 a int[] := ARRAY[1, 2]; 6087BEGIN 6088 a := a || 3; 6089 RAISE NOTICE 'a = %', a; 6090END 6091$$; 6092-- 6093-- Test access to call stack 6094-- 6095CREATE FUNCTION inner_func (int) 6096 RETURNS int 6097 AS $$ 6098DECLARE 6099 _context text; 6100BEGIN 6101 get diagnostics _context = pg_context; 6102 RAISE NOTICE '***%***', _context; 6103 -- lets do it again, just for fun.. 6104 get diagnostics _context = pg_context; 6105 RAISE NOTICE '***%***', _context; 6106 RAISE NOTICE 'lets make sure we didnt break anything'; 6107 RETURN 2 * $1; 6108 END; 6109$$ 6110LANGUAGE plpgsql; 6111CREATE OR REPLACE FUNCTION outer_func (int) 6112 RETURNS int 6113 AS $$ 6114DECLARE 6115 myresult int; 6116BEGIN 6117 RAISE NOTICE 'calling down into inner_func()'; 6118 myresult := inner_func ($1); 6119 RAISE NOTICE 'inner_func() done'; 6120 RETURN myresult; 6121END; 6122$$ 6123LANGUAGE plpgsql; 6124CREATE OR REPLACE FUNCTION outer_outer_func (int) 6125 RETURNS int 6126 AS $$ 6127DECLARE 6128 myresult int; 6129BEGIN 6130 RAISE NOTICE 'calling down into outer_func()'; 6131 myresult := outer_func ($1); 6132 RAISE NOTICE 'outer_func() done'; 6133 RETURN myresult; 6134END; 6135$$ 6136LANGUAGE plpgsql; 6137SELECT 6138 outer_outer_func (10); 6139-- repeated call should to work 6140SELECT 6141 outer_outer_func (20); 6142DROP FUNCTION outer_outer_func (int); 6143DROP FUNCTION outer_func (int); 6144DROP FUNCTION inner_func (int); 6145-- access to call stack from exception 6146CREATE FUNCTION inner_func (int) 6147 RETURNS int 6148 AS $$ 6149DECLARE 6150 _context text; 6151 sx int := 5; 6152BEGIN 6153 BEGIN 6154 PERFORM 6155 sx / 0; 6156 EXCEPTION 6157 WHEN division_by_zero THEN 6158 get diagnostics _context = pg_context; 6159 RAISE NOTICE '***%***', _context; 6160 END; 6161 -- lets do it again, just for fun.. 6162 get diagnostics _context = pg_context; 6163 RAISE NOTICE '***%***', _context; 6164 RAISE NOTICE 'lets make sure we didnt break anything'; 6165 RETURN 2 * $1; 6166END; 6167$$ 6168LANGUAGE plpgsql; 6169CREATE OR REPLACE FUNCTION outer_func (int) 6170 RETURNS int 6171 AS $$ 6172DECLARE 6173 myresult int; 6174BEGIN 6175 RAISE NOTICE 'calling down into inner_func()'; 6176 myresult := inner_func ($1); 6177 RAISE NOTICE 'inner_func() done'; 6178 RETURN myresult; 6179END; 6180$$ 6181LANGUAGE plpgsql; 6182CREATE OR REPLACE FUNCTION outer_outer_func (int) 6183 RETURNS int 6184 AS $$ 6185DECLARE 6186 myresult int; 6187BEGIN 6188 RAISE NOTICE 'calling down into outer_func()'; 6189 myresult := outer_func ($1); 6190 RAISE NOTICE 'outer_func() done'; 6191 RETURN myresult; 6192END; 6193$$ 6194LANGUAGE plpgsql; 6195SELECT 6196 outer_outer_func (10); 6197-- repeated call should to work 6198SELECT 6199 outer_outer_func (20); 6200DROP FUNCTION outer_outer_func (int); 6201DROP FUNCTION outer_func (int); 6202DROP FUNCTION inner_func (int); 6203-- 6204-- Test ASSERT 6205-- 6206DO $$ 6207BEGIN 6208 assert 1 = 1; 6209 -- should succeed 6210END; 6211$$; 6212DO $$ 6213BEGIN 6214 assert 1 = 0; 6215 -- should fail 6216END; 6217$$; 6218DO $$ 6219BEGIN 6220 assert NULL; 6221 -- should fail 6222END; 6223$$; 6224-- check controlling GUC 6225SET plpgsql.check_asserts = OFF; 6226DO $$ 6227BEGIN 6228 assert 1 = 0; 6229 -- won't be tested 6230END; 6231$$; 6232RESET plpgsql.check_asserts; 6233-- test custom message 6234DO $$ 6235DECLARE 6236 var text := 'some value'; 6237BEGIN 6238 assert 1 = 0, 6239 format('assertion failed, var = "%s"', var); 6240END; 6241$$; 6242-- ensure assertions are not trapped by 'others' 6243DO $$ 6244BEGIN 6245 assert 1 = 0, 6246 'unhandled assertion'; 6247EXCEPTION 6248 WHEN OTHERS THEN 6249 NULL; 6250 -- do nothing 6251END; 6252$$; 6253-- Test use of plpgsql in a domain check constraint (cf. bug #14414) 6254CREATE FUNCTION plpgsql_domain_check (val int) 6255 RETURNS boolean 6256 AS $$ 6257BEGIN 6258 RETURN val > 0; 6259END 6260$$ 6261LANGUAGE plpgsql 6262IMMUTABLE; 6263CREATE DOMAIN plpgsql_domain AS integer CHECK (plpgsql_domain_check (value)); 6264DO $$ 6265DECLARE 6266 v_test plpgsql_domain; 6267BEGIN 6268 v_test := 1; 6269END; 6270$$; 6271DO $$ 6272DECLARE 6273 v_test plpgsql_domain := 1; 6274BEGIN 6275 v_test := 0; 6276 -- fail 6277END; 6278$$; 6279-- Test handling of expanded array passed to a domain constraint (bug #14472) 6280CREATE FUNCTION plpgsql_arr_domain_check (val int[]) 6281 RETURNS boolean 6282 AS $$ 6283BEGIN 6284 RETURN val[1] > 0; 6285END 6286$$ 6287LANGUAGE plpgsql 6288IMMUTABLE; 6289CREATE DOMAIN plpgsql_arr_domain AS int[] CHECK (plpgsql_arr_domain_check (value)); 6290DO $$ 6291DECLARE 6292 v_test plpgsql_arr_domain; 6293BEGIN 6294 v_test := ARRAY[1]; 6295 v_test := v_test || 2; 6296END; 6297$$; 6298DO $$ 6299DECLARE 6300 v_test plpgsql_arr_domain := ARRAY[1]; 6301BEGIN 6302 v_test := 0 || v_test; 6303 -- fail 6304END; 6305$$; 6306-- 6307-- test usage of transition tables in AFTER triggers 6308-- 6309CREATE TABLE transition_table_base ( 6310 id int PRIMARY KEY, 6311 val text 6312); 6313CREATE FUNCTION transition_table_base_ins_func () 6314 RETURNS TRIGGER 6315 LANGUAGE plpgsql 6316 AS $$ 6317DECLARE 6318 t text; 6319 l text; 6320BEGIN 6321 t = ''; 6322 FOR l IN EXECUTE $q$ 6323 EXPLAIN (TIMING off, COSTS off, VERBOSE on) 6324 SELECT * FROM newtable 6325 $q$ LOOP 6326 t = t || l || E'\n'; 6327 END LOOP; 6328 RAISE INFO '%', t; 6329 RETURN new; 6330END; 6331$$; 6332CREATE TRIGGER transition_table_base_ins_trig 6333 AFTER INSERT ON transition_table_base REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable 6334 FOR EACH STATEMENT 6335 EXECUTE PROCEDURE transition_table_base_ins_func (); 6336CREATE TRIGGER transition_table_base_ins_trig 6337 AFTER INSERT ON transition_table_base REFERENCING NEW TABLE AS newtable 6338 FOR EACH STATEMENT 6339 EXECUTE PROCEDURE transition_table_base_ins_func (); 6340INSERT INTO transition_table_base 6341 VALUES (1, 'One'), (2, 'Two'); 6342INSERT INTO transition_table_base 6343 VALUES (3, 'Three'), (4, 'Four'); 6344CREATE OR REPLACE FUNCTION transition_table_base_upd_func () 6345 RETURNS TRIGGER 6346 LANGUAGE plpgsql 6347 AS $$ 6348DECLARE 6349 t text; 6350 l text; 6351BEGIN 6352 t = ''; 6353 FOR l IN EXECUTE $q$ 6354 EXPLAIN (TIMING off, COSTS off, VERBOSE on) 6355 SELECT * FROM oldtable ot FULL JOIN newtable nt USING (id) 6356 $q$ LOOP 6357 t = t || l || E'\n'; 6358 END LOOP; 6359 RAISE INFO '%', t; 6360 RETURN new; 6361END; 6362$$; 6363CREATE TRIGGER transition_table_base_upd_trig 6364 AFTER UPDATE ON transition_table_base REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable 6365 FOR EACH STATEMENT 6366 EXECUTE PROCEDURE transition_table_base_upd_func (); 6367UPDATE 6368 transition_table_base 6369SET 6370 val = '*' || val || '*' 6371WHERE 6372 id BETWEEN 2 AND 3; 6373CREATE TABLE transition_table_level1 ( 6374 level1_no serial NOT NULL, 6375 level1_node_name varchar(255), 6376 PRIMARY KEY (level1_no)) WITHOUT OIDS; 6377CREATE TABLE transition_table_level2 ( 6378 level2_no serial NOT NULL, 6379 parent_no int NOT NULL, 6380 level1_node_name varchar(255), 6381 PRIMARY KEY (level2_no)) WITHOUT OIDS; 6382CREATE TABLE transition_table_status ( 6383 level int NOT NULL, 6384 node_no int NOT NULL, 6385 status int, 6386 PRIMARY KEY (level, node_no)) WITHOUT OIDS; 6387CREATE FUNCTION transition_table_level1_ri_parent_del_func () 6388 RETURNS TRIGGER 6389 LANGUAGE plpgsql 6390 AS $$ 6391DECLARE 6392 n bigint; 6393BEGIN 6394 PERFORM 6395 FROM 6396 p 6397 JOIN transition_table_level2 c ON c.parent_no = p.level1_no; 6398 IF FOUND THEN 6399 RAISE EXCEPTION 'RI error'; 6400 END IF; 6401 RETURN NULL; 6402END; 6403$$; 6404CREATE TRIGGER transition_table_level1_ri_parent_del_trigger 6405 AFTER DELETE ON transition_table_level1 REFERENCING OLD TABLE AS p 6406 FOR EACH STATEMENT 6407 EXECUTE PROCEDURE transition_table_level1_ri_parent_del_func (); 6408CREATE FUNCTION transition_table_level1_ri_parent_upd_func () 6409 RETURNS TRIGGER 6410 LANGUAGE plpgsql 6411 AS $$ 6412DECLARE 6413 x int; 6414BEGIN 6415 WITH p AS ( 6416 SELECT 6417 level1_no, 6418 sum(delta) cnt 6419 FROM ( 6420 SELECT 6421 level1_no, 6422 1 AS delta 6423 FROM 6424 i 6425 UNION ALL 6426 SELECT 6427 level1_no, 6428 -1 AS delta 6429 FROM 6430 d) w 6431 GROUP BY 6432 level1_no 6433 HAVING 6434 sum(delta) < 0 6435) 6436SELECT 6437 level1_no 6438FROM 6439 p 6440 JOIN transition_table_level2 c ON c.parent_no = p.level1_no INTO x; 6441 IF FOUND THEN 6442 RAISE EXCEPTION 'RI error'; 6443 END IF; 6444 RETURN NULL; 6445END; 6446$$; 6447CREATE TRIGGER transition_table_level1_ri_parent_upd_trigger 6448 AFTER UPDATE ON transition_table_level1 REFERENCING OLD TABLE AS d NEW TABLE AS i 6449 FOR EACH STATEMENT 6450 EXECUTE PROCEDURE transition_table_level1_ri_parent_upd_func (); 6451CREATE FUNCTION transition_table_level2_ri_child_insupd_func () 6452 RETURNS TRIGGER 6453 LANGUAGE plpgsql 6454 AS $$ 6455BEGIN 6456 PERFORM 6457 FROM 6458 i 6459 LEFT JOIN transition_table_level1 p ON p.level1_no IS NOT NULL 6460 AND p.level1_no = i.parent_no 6461WHERE 6462 p.level1_no IS NULL; 6463 IF FOUND THEN 6464 RAISE EXCEPTION 'RI error'; 6465 END IF; 6466 RETURN NULL; 6467END; 6468$$; 6469CREATE TRIGGER transition_table_level2_ri_child_ins_trigger 6470 AFTER INSERT ON transition_table_level2 REFERENCING NEW TABLE AS i 6471 FOR EACH STATEMENT 6472 EXECUTE PROCEDURE transition_table_level2_ri_child_insupd_func (); 6473CREATE TRIGGER transition_table_level2_ri_child_upd_trigger 6474 AFTER UPDATE ON transition_table_level2 REFERENCING NEW TABLE AS i 6475 FOR EACH STATEMENT 6476 EXECUTE PROCEDURE transition_table_level2_ri_child_insupd_func (); 6477-- create initial test data 6478INSERT INTO transition_table_level1 (level1_no) 6479SELECT 6480 generate_series(1, 200); 6481ANALYZE transition_table_level1; 6482INSERT INTO transition_table_level2 (level2_no, parent_no) 6483SELECT 6484 level2_no, 6485 level2_no / 50 + 1 AS parent_no 6486FROM 6487 generate_series(1, 9999) level2_no; 6488ANALYZE transition_table_level2; 6489INSERT INTO transition_table_status (level, node_no, status) 6490SELECT 6491 1, 6492 level1_no, 6493 0 6494FROM 6495 transition_table_level1; 6496INSERT INTO transition_table_status (level, node_no, status) 6497SELECT 6498 2, 6499 level2_no, 6500 0 6501FROM 6502 transition_table_level2; 6503ANALYZE transition_table_status; 6504INSERT INTO transition_table_level1 (level1_no) 6505SELECT 6506 generate_series(201, 1000); 6507ANALYZE transition_table_level1; 6508-- behave reasonably if someone tries to modify a transition table 6509CREATE FUNCTION transition_table_level2_bad_usage_func () 6510 RETURNS TRIGGER 6511 LANGUAGE plpgsql 6512 AS $$ 6513BEGIN 6514 INSERT INTO dx 6515 VALUES (1000000, 1000000, 'x'); 6516 RETURN NULL; 6517END; 6518$$; 6519CREATE TRIGGER transition_table_level2_bad_usage_trigger 6520 AFTER DELETE ON transition_table_level2 REFERENCING OLD TABLE AS dx 6521 FOR EACH STATEMENT 6522 EXECUTE PROCEDURE transition_table_level2_bad_usage_func (); 6523DELETE FROM transition_table_level2 6524WHERE level2_no BETWEEN 301 AND 305; 6525DROP TRIGGER transition_table_level2_bad_usage_trigger ON transition_table_level2; 6526-- attempt modifications which would break RI (should all fail) 6527DELETE FROM transition_table_level1 6528WHERE level1_no = 25; 6529UPDATE 6530 transition_table_level1 6531SET 6532 level1_no = - 1 6533WHERE 6534 level1_no = 30; 6535INSERT INTO transition_table_level2 (level2_no, parent_no) 6536 VALUES (10000, 10000); 6537UPDATE 6538 transition_table_level2 6539SET 6540 parent_no = 2000 6541WHERE 6542 level2_no = 40; 6543-- attempt modifications which would not break RI (should all succeed) 6544DELETE FROM transition_table_level1 6545WHERE level1_no BETWEEN 201 AND 1000; 6546DELETE FROM transition_table_level1 6547WHERE level1_no BETWEEN 100000000 AND 100000010; 6548SELECT 6549 count(*) 6550FROM 6551 transition_table_level1; 6552DELETE FROM transition_table_level2 6553WHERE level2_no BETWEEN 211 AND 220; 6554SELECT 6555 count(*) 6556FROM 6557 transition_table_level2; 6558CREATE TABLE alter_table_under_transition_tables ( 6559 id int PRIMARY KEY, 6560 name text 6561); 6562CREATE FUNCTION alter_table_under_transition_tables_upd_func () 6563 RETURNS TRIGGER 6564 LANGUAGE plpgsql 6565 AS $$ 6566BEGIN 6567 RAISE WARNING 'old table = %, new table = %', ( 6568 SELECT 6569 string_agg(id || '=' || name, ',') 6570 FROM 6571 d), 6572 ( 6573 SELECT 6574 string_agg(id || '=' || name, ',') 6575 FROM 6576 i); 6577 RAISE NOTICE 'one = %', ( 6578 SELECT 6579 1 6580 FROM 6581 alter_table_under_transition_tables 6582 LIMIT 1); 6583 RETURN NULL; 6584END; 6585$$; 6586-- should fail, TRUNCATE is not compatible with transition tables 6587CREATE TRIGGER alter_table_under_transition_tables_upd_trigger 6588 AFTER TRUNCATE OR UPDATE ON alter_table_under_transition_tables REFERENCING OLD TABLE AS d NEW TABLE AS i 6589 FOR EACH STATEMENT 6590 EXECUTE PROCEDURE alter_table_under_transition_tables_upd_func (); 6591-- should work 6592CREATE TRIGGER alter_table_under_transition_tables_upd_trigger 6593 AFTER UPDATE ON alter_table_under_transition_tables REFERENCING OLD TABLE AS d NEW TABLE AS i 6594 FOR EACH STATEMENT 6595 EXECUTE PROCEDURE alter_table_under_transition_tables_upd_func (); 6596INSERT INTO alter_table_under_transition_tables 6597 VALUES (1, '1'), (2, '2'), (3, '3'); 6598UPDATE 6599 alter_table_under_transition_tables 6600SET 6601 name = name || name; 6602-- now change 'name' to an integer to see what happens... 6603ALTER TABLE alter_table_under_transition_tables 6604 ALTER COLUMN name TYPE int 6605 USING name::integer; 6606UPDATE 6607 alter_table_under_transition_tables 6608SET 6609 name = (name::text || name::text)::integer; 6610-- now drop column 'name' 6611ALTER TABLE alter_table_under_transition_tables 6612 DROP COLUMN name; 6613UPDATE 6614 alter_table_under_transition_tables 6615SET 6616 id = id; 6617-- 6618-- Test multiple reference to a transition table 6619-- 6620CREATE TABLE multi_test ( 6621 i int 6622); 6623INSERT INTO multi_test 6624 VALUES (1); 6625CREATE OR REPLACE FUNCTION multi_test_trig () 6626 RETURNS TRIGGER 6627 LANGUAGE plpgsql 6628 AS $$ 6629BEGIN 6630 RAISE NOTICE 'count = %', ( 6631 SELECT 6632 COUNT(*) 6633 FROM 6634 new_test); 6635 RAISE NOTICE 'count union = %', ( 6636 SELECT 6637 COUNT(*) 6638 FROM ( 6639 SELECT 6640 * 6641 FROM 6642 new_test 6643 UNION ALL 6644 SELECT 6645 * 6646 FROM 6647 new_test) ss); 6648 RETURN NULL; 6649END 6650$$; 6651CREATE TRIGGER my_trigger 6652 AFTER UPDATE ON multi_test REFERENCING NEW TABLE AS new_test OLD TABLE AS old_test 6653 FOR EACH STATEMENT 6654 EXECUTE PROCEDURE multi_test_trig (); 6655UPDATE 6656 multi_test 6657SET 6658 i = i; 6659DROP TABLE multi_test; 6660DROP FUNCTION multi_test_trig (); 6661-- 6662-- Check type parsing and record fetching from partitioned tables 6663-- 6664CREATE TABLE partitioned_table ( 6665 a int, 6666 b text 6667) 6668PARTITION BY LIST (a); 6669CREATE TABLE pt_part1 PARTITION OF partitioned_table 6670FOR VALUES IN (1); 6671CREATE TABLE pt_part2 PARTITION OF partitioned_table 6672FOR VALUES IN (2); 6673INSERT INTO partitioned_table 6674 VALUES (1, 'Row 1'); 6675INSERT INTO partitioned_table 6676 VALUES (2, 'Row 2'); 6677CREATE OR REPLACE FUNCTION get_from_partitioned_table (partitioned_table.a%type) 6678 RETURNS partitioned_table 6679 AS $$ 6680DECLARE 6681 a_val partitioned_table.a%TYPE; 6682 result partitioned_table%ROWTYPE; 6683BEGIN 6684 a_val := $1; 6685 SELECT 6686 * INTO result 6687 FROM 6688 partitioned_table 6689 WHERE 6690 a = a_val; 6691 RETURN result; 6692 END; 6693$$ 6694LANGUAGE plpgsql; 6695SELECT 6696 * 6697FROM 6698 get_from_partitioned_table (1) AS t; 6699CREATE OR REPLACE FUNCTION list_partitioned_table () 6700 RETURNS SETOF partitioned_table.a%TYPE 6701 AS $$ 6702DECLARE 6703 ROW partitioned_table%ROWTYPE; 6704 a_val partitioned_table.a%TYPE; 6705BEGIN 6706 FOR ROW IN 6707 SELECT 6708 * 6709 FROM 6710 partitioned_table 6711 ORDER BY 6712 a LOOP 6713 a_val := row.a; 6714 RETURN NEXT a_val; 6715 END LOOP; 6716 RETURN; 6717END; 6718$$ 6719LANGUAGE plpgsql; 6720SELECT 6721 * 6722FROM 6723 list_partitioned_table () AS t; 6724-- 6725-- Check argument name is used instead of $n in error message 6726-- 6727CREATE FUNCTION fx (x WSlot) 6728 RETURNS void 6729 AS $$ 6730BEGIN 6731 GET DIAGNOSTICS x = ROW_COUNT; 6732 RETURN; 6733END; 6734$$ 6735LANGUAGE plpgsql; 6736