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.
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
17 -- Triggers ensure consistency of the patching information.
19 -- Functions are used to build up powerful views that let
20 -- you look behind the wall when looking at a patchfield
27 create unique index Room_rno on Room using btree (roomno bpchar_ops);
34 create unique index WSlot_name on WSlot using btree (slotname bpchar_ops);
39 create unique index PField_name on PField using btree (name text_ops);
46 create unique index PSlot_name on PSlot using btree (slotname bpchar_ops);
53 create unique index PLine_name on PLine using btree (slotname bpchar_ops);
59 create unique index Hub_name on Hub using btree (name bpchar_ops);
66 create unique index HSlot_name on HSlot using btree (slotname bpchar_ops);
67 create index HSlot_hubname on HSlot using btree (hubname bpchar_ops);
72 create unique index System_name on System using btree (name text_ops);
79 create unique index IFace_name on IFace using btree (slotname bpchar_ops);
85 create unique index PHone_name on PHone using btree (slotname bpchar_ops);
86 -- ************************************************************
88 -- * Trigger procedures and functions for the patchfield
91 -- ************************************************************
92 -- ************************************************************
93 -- * AFTER UPDATE on Room
94 -- * - If room no changes let wall slots follow
95 -- ************************************************************
96 create function tg_room_au() returns trigger as '
98 if new.roomno != old.roomno then
99 update WSlot set roomno = new.roomno where roomno = old.roomno;
104 create trigger tg_room_au after update
105 on Room for each row execute procedure tg_room_au();
106 -- ************************************************************
107 -- * AFTER DELETE on Room
108 -- * - delete wall slots in this room
109 -- ************************************************************
110 create function tg_room_ad() returns trigger as '
112 delete from WSlot where roomno = old.roomno;
116 create trigger tg_room_ad after delete
117 on Room for each row execute procedure tg_room_ad();
118 -- ************************************************************
119 -- * BEFORE INSERT or UPDATE on WSlot
120 -- * - Check that room exists
121 -- ************************************************************
122 create function tg_wslot_biu() returns trigger as $$
124 if count(*) = 0 from Room where roomno = new.roomno then
125 raise exception 'Room % does not exist', new.roomno;
130 create trigger tg_wslot_biu before insert or update
131 on WSlot for each row execute procedure tg_wslot_biu();
132 -- ************************************************************
133 -- * AFTER UPDATE on PField
134 -- * - Let PSlots of this field follow
135 -- ************************************************************
136 create function tg_pfield_au() returns trigger as '
138 if new.name != old.name then
139 update PSlot set pfname = new.name where pfname = old.name;
144 create trigger tg_pfield_au after update
145 on PField for each row execute procedure tg_pfield_au();
146 -- ************************************************************
147 -- * AFTER DELETE on PField
148 -- * - Remove all slots of this patchfield
149 -- ************************************************************
150 create function tg_pfield_ad() returns trigger as '
152 delete from PSlot where pfname = old.name;
156 create trigger tg_pfield_ad after delete
157 on PField for each row execute procedure tg_pfield_ad();
158 -- ************************************************************
159 -- * BEFORE INSERT or UPDATE on PSlot
160 -- * - Ensure that our patchfield does exist
161 -- ************************************************************
162 create function tg_pslot_biu() returns trigger as $proc$
167 select into pfrec * from PField where name = ps.pfname;
169 raise exception $$Patchfield "%" does not exist$$, ps.pfname;
173 $proc$ language plpgsql;
174 create trigger tg_pslot_biu before insert or update
175 on PSlot for each row execute procedure tg_pslot_biu();
176 -- ************************************************************
177 -- * AFTER UPDATE on System
178 -- * - If system name changes let interfaces follow
179 -- ************************************************************
180 create function tg_system_au() returns trigger as '
182 if new.name != old.name then
183 update IFace set sysname = new.name where sysname = old.name;
188 create trigger tg_system_au after update
189 on System for each row execute procedure tg_system_au();
190 -- ************************************************************
191 -- * BEFORE INSERT or UPDATE on IFace
192 -- * - set the slotname to IF.sysname.ifname
193 -- ************************************************************
194 create function tg_iface_biu() returns trigger as $$
199 select into sysrec * from system where name = new.sysname;
201 raise exception $q$system "%" does not exist$q$, new.sysname;
203 sname := 'IF.' || new.sysname;
204 sname := sname || '.';
205 sname := sname || new.ifname;
206 if length(sname) > 20 then
207 raise exception 'IFace slotname "%" too long (20 char max)', sname;
209 new.slotname := sname;
213 create trigger tg_iface_biu before insert or update
214 on IFace for each row execute procedure tg_iface_biu();
215 -- ************************************************************
216 -- * AFTER INSERT or UPDATE or DELETE on Hub
217 -- * - insert/delete/rename slots as required
218 -- ************************************************************
219 create function tg_hub_a() returns trigger as '
224 if tg_op = ''INSERT'' then
225 dummy := tg_hub_adjustslots(new.name, 0, new.nslots);
228 if tg_op = ''UPDATE'' then
229 if new.name != old.name then
230 update HSlot set hubname = new.name where hubname = old.name;
232 dummy := tg_hub_adjustslots(new.name, old.nslots, new.nslots);
235 if tg_op = ''DELETE'' then
236 dummy := tg_hub_adjustslots(old.name, old.nslots, 0);
241 create trigger tg_hub_a after insert or update or delete
242 on Hub for each row execute procedure tg_hub_a();
243 -- ************************************************************
244 -- * Support function to add/remove slots of Hub
245 -- ************************************************************
246 create function tg_hub_adjustslots(hname bpchar,
251 if newnslots = oldnslots then
254 if newnslots < oldnslots then
255 delete from HSlot where hubname = hname and slotno > newnslots;
258 for i in oldnslots + 1 .. newnslots loop
259 insert into HSlot (slotname, hubname, slotno, slotlink)
260 values (''HS.dummy'', hname, i, '''');
266 COMMENT ON FUNCTION tg_hub_adjustslots_wrong(bpchar, integer, integer) IS 'function with args';
267 ERROR: function tg_hub_adjustslots_wrong(character, integer, integer) does not exist
268 COMMENT ON FUNCTION tg_hub_adjustslots(bpchar, integer, integer) IS 'function with args';
269 COMMENT ON FUNCTION tg_hub_adjustslots(bpchar, integer, integer) IS NULL;
270 -- ************************************************************
271 -- * BEFORE INSERT or UPDATE on HSlot
272 -- * - prevent from manual manipulation
273 -- * - set the slotname to HS.hubname.slotno
274 -- ************************************************************
275 create function tg_hslot_biu() returns trigger as '
278 xname HSlot.slotname%TYPE;
281 select into hubrec * from Hub where name = new.hubname;
283 raise exception ''no manual manipulation of HSlot'';
285 if new.slotno < 1 or new.slotno > hubrec.nslots then
286 raise exception ''no manual manipulation of HSlot'';
288 if tg_op = ''UPDATE'' and new.hubname != old.hubname then
289 if count(*) > 0 from Hub where name = old.hubname then
290 raise exception ''no manual manipulation of HSlot'';
293 sname := ''HS.'' || trim(new.hubname);
294 sname := sname || ''.'';
295 sname := sname || new.slotno::text;
296 if length(sname) > 20 then
297 raise exception ''HSlot slotname "%" too long (20 char max)'', sname;
299 new.slotname := sname;
303 create trigger tg_hslot_biu before insert or update
304 on HSlot for each row execute procedure tg_hslot_biu();
305 -- ************************************************************
306 -- * BEFORE DELETE on HSlot
307 -- * - prevent from manual manipulation
308 -- ************************************************************
309 create function tg_hslot_bd() returns trigger as '
313 select into hubrec * from Hub where name = old.hubname;
317 if old.slotno > hubrec.nslots then
320 raise exception ''no manual manipulation of HSlot'';
323 create trigger tg_hslot_bd before delete
324 on HSlot for each row execute procedure tg_hslot_bd();
325 -- ************************************************************
326 -- * BEFORE INSERT on all slots
327 -- * - Check name prefix
328 -- ************************************************************
329 create function tg_chkslotname() returns trigger as '
331 if substr(new.slotname, 1, 2) != tg_argv[0] then
332 raise exception ''slotname must begin with %'', tg_argv[0];
337 create trigger tg_chkslotname before insert
338 on PSlot for each row execute procedure tg_chkslotname('PS');
339 create trigger tg_chkslotname before insert
340 on WSlot for each row execute procedure tg_chkslotname('WS');
341 create trigger tg_chkslotname before insert
342 on PLine for each row execute procedure tg_chkslotname('PL');
343 create trigger tg_chkslotname before insert
344 on IFace for each row execute procedure tg_chkslotname('IF');
345 create trigger tg_chkslotname before insert
346 on PHone for each row execute procedure tg_chkslotname('PH');
347 -- ************************************************************
348 -- * BEFORE INSERT or UPDATE on all slots with slotlink
349 -- * - Set slotlink to empty string if NULL value given
350 -- ************************************************************
351 create function tg_chkslotlink() returns trigger as '
353 if new.slotlink isnull then
354 new.slotlink := '''';
359 create trigger tg_chkslotlink before insert or update
360 on PSlot for each row execute procedure tg_chkslotlink();
361 create trigger tg_chkslotlink before insert or update
362 on WSlot for each row execute procedure tg_chkslotlink();
363 create trigger tg_chkslotlink before insert or update
364 on IFace for each row execute procedure tg_chkslotlink();
365 create trigger tg_chkslotlink before insert or update
366 on HSlot for each row execute procedure tg_chkslotlink();
367 create trigger tg_chkslotlink before insert or update
368 on PHone for each row execute procedure tg_chkslotlink();
369 -- ************************************************************
370 -- * BEFORE INSERT or UPDATE on all slots with backlink
371 -- * - Set backlink to empty string if NULL value given
372 -- ************************************************************
373 create function tg_chkbacklink() returns trigger as '
375 if new.backlink isnull then
376 new.backlink := '''';
381 create trigger tg_chkbacklink before insert or update
382 on PSlot for each row execute procedure tg_chkbacklink();
383 create trigger tg_chkbacklink before insert or update
384 on WSlot for each row execute procedure tg_chkbacklink();
385 create trigger tg_chkbacklink before insert or update
386 on PLine for each row execute procedure tg_chkbacklink();
387 -- ************************************************************
388 -- * BEFORE UPDATE on PSlot
389 -- * - do delete/insert instead of update if name changes
390 -- ************************************************************
391 create function tg_pslot_bu() returns trigger as '
393 if new.slotname != old.slotname then
394 delete from PSlot where slotname = old.slotname;
411 create trigger tg_pslot_bu before update
412 on PSlot for each row execute procedure tg_pslot_bu();
413 -- ************************************************************
414 -- * BEFORE UPDATE on WSlot
415 -- * - do delete/insert instead of update if name changes
416 -- ************************************************************
417 create function tg_wslot_bu() returns trigger as '
419 if new.slotname != old.slotname then
420 delete from WSlot where slotname = old.slotname;
437 create trigger tg_wslot_bu before update
438 on WSlot for each row execute procedure tg_Wslot_bu();
439 -- ************************************************************
440 -- * BEFORE UPDATE on PLine
441 -- * - do delete/insert instead of update if name changes
442 -- ************************************************************
443 create function tg_pline_bu() returns trigger as '
445 if new.slotname != old.slotname then
446 delete from PLine where slotname = old.slotname;
463 create trigger tg_pline_bu before update
464 on PLine for each row execute procedure tg_pline_bu();
465 -- ************************************************************
466 -- * BEFORE UPDATE on IFace
467 -- * - do delete/insert instead of update if name changes
468 -- ************************************************************
469 create function tg_iface_bu() returns trigger as '
471 if new.slotname != old.slotname then
472 delete from IFace where slotname = old.slotname;
489 create trigger tg_iface_bu before update
490 on IFace for each row execute procedure tg_iface_bu();
491 -- ************************************************************
492 -- * BEFORE UPDATE on HSlot
493 -- * - do delete/insert instead of update if name changes
494 -- ************************************************************
495 create function tg_hslot_bu() returns trigger as '
497 if new.slotname != old.slotname or new.hubname != old.hubname then
498 delete from HSlot where slotname = old.slotname;
515 create trigger tg_hslot_bu before update
516 on HSlot for each row execute procedure tg_hslot_bu();
517 -- ************************************************************
518 -- * BEFORE UPDATE on PHone
519 -- * - do delete/insert instead of update if name changes
520 -- ************************************************************
521 create function tg_phone_bu() returns trigger as '
523 if new.slotname != old.slotname then
524 delete from PHone where slotname = old.slotname;
539 create trigger tg_phone_bu before update
540 on PHone for each row execute procedure tg_phone_bu();
541 -- ************************************************************
542 -- * AFTER INSERT or UPDATE or DELETE on slot with backlink
543 -- * - Ensure that the opponent correctly points back to us
544 -- ************************************************************
545 create function tg_backlink_a() returns trigger as '
549 if tg_op = ''INSERT'' then
550 if new.backlink != '''' then
551 dummy := tg_backlink_set(new.backlink, new.slotname);
555 if tg_op = ''UPDATE'' then
556 if new.backlink != old.backlink then
557 if old.backlink != '''' then
558 dummy := tg_backlink_unset(old.backlink, old.slotname);
560 if new.backlink != '''' then
561 dummy := tg_backlink_set(new.backlink, new.slotname);
564 if new.slotname != old.slotname and new.backlink != '''' then
565 dummy := tg_slotlink_set(new.backlink, new.slotname);
570 if tg_op = ''DELETE'' then
571 if old.backlink != '''' then
572 dummy := tg_backlink_unset(old.backlink, old.slotname);
578 create trigger tg_backlink_a after insert or update or delete
579 on PSlot for each row execute procedure tg_backlink_a('PS');
580 create trigger tg_backlink_a after insert or update or delete
581 on WSlot for each row execute procedure tg_backlink_a('WS');
582 create trigger tg_backlink_a after insert or update or delete
583 on PLine for each row execute procedure tg_backlink_a('PL');
584 -- ************************************************************
585 -- * Support function to set the opponents backlink field
586 -- * if it does not already point to the requested slot
587 -- ************************************************************
588 create function tg_backlink_set(myname bpchar, blname bpchar)
595 mytype := substr(myname, 1, 2);
596 link := mytype || substr(blname, 1, 2);
597 if link = ''PLPL'' then
599 ''backlink between two phone lines does not make sense'';
601 if link in (''PLWS'', ''WSPL'') then
603 ''direct link of phone line to wall slot not permitted'';
605 if mytype = ''PS'' then
606 select into rec * from PSlot where slotname = myname;
608 raise exception ''% does not exist'', myname;
610 if rec.backlink != blname then
611 update PSlot set backlink = blname where slotname = myname;
615 if mytype = ''WS'' then
616 select into rec * from WSlot where slotname = myname;
618 raise exception ''% does not exist'', myname;
620 if rec.backlink != blname then
621 update WSlot set backlink = blname where slotname = myname;
625 if mytype = ''PL'' then
626 select into rec * from PLine where slotname = myname;
628 raise exception ''% does not exist'', myname;
630 if rec.backlink != blname then
631 update PLine set backlink = blname where slotname = myname;
635 raise exception ''illegal backlink beginning with %'', mytype;
638 -- ************************************************************
639 -- * Support function to clear out the backlink field if
640 -- * it still points to specific slot
641 -- ************************************************************
642 create function tg_backlink_unset(bpchar, bpchar)
650 mytype := substr(myname, 1, 2);
651 if mytype = ''PS'' then
652 select into rec * from PSlot where slotname = myname;
656 if rec.backlink = blname then
657 update PSlot set backlink = '''' where slotname = myname;
661 if mytype = ''WS'' then
662 select into rec * from WSlot where slotname = myname;
666 if rec.backlink = blname then
667 update WSlot set backlink = '''' where slotname = myname;
671 if mytype = ''PL'' then
672 select into rec * from PLine where slotname = myname;
676 if rec.backlink = blname then
677 update PLine set backlink = '''' where slotname = myname;
683 -- ************************************************************
684 -- * AFTER INSERT or UPDATE or DELETE on slot with slotlink
685 -- * - Ensure that the opponent correctly points back to us
686 -- ************************************************************
687 create function tg_slotlink_a() returns trigger as '
691 if tg_op = ''INSERT'' then
692 if new.slotlink != '''' then
693 dummy := tg_slotlink_set(new.slotlink, new.slotname);
697 if tg_op = ''UPDATE'' then
698 if new.slotlink != old.slotlink then
699 if old.slotlink != '''' then
700 dummy := tg_slotlink_unset(old.slotlink, old.slotname);
702 if new.slotlink != '''' then
703 dummy := tg_slotlink_set(new.slotlink, new.slotname);
706 if new.slotname != old.slotname and new.slotlink != '''' then
707 dummy := tg_slotlink_set(new.slotlink, new.slotname);
712 if tg_op = ''DELETE'' then
713 if old.slotlink != '''' then
714 dummy := tg_slotlink_unset(old.slotlink, old.slotname);
720 create trigger tg_slotlink_a after insert or update or delete
721 on PSlot for each row execute procedure tg_slotlink_a('PS');
722 create trigger tg_slotlink_a after insert or update or delete
723 on WSlot for each row execute procedure tg_slotlink_a('WS');
724 create trigger tg_slotlink_a after insert or update or delete
725 on IFace for each row execute procedure tg_slotlink_a('IF');
726 create trigger tg_slotlink_a after insert or update or delete
727 on HSlot for each row execute procedure tg_slotlink_a('HS');
728 create trigger tg_slotlink_a after insert or update or delete
729 on PHone for each row execute procedure tg_slotlink_a('PH');
730 -- ************************************************************
731 -- * Support function to set the opponents slotlink field
732 -- * if it does not already point to the requested slot
733 -- ************************************************************
734 create function tg_slotlink_set(bpchar, bpchar)
743 mytype := substr(myname, 1, 2);
744 link := mytype || substr(blname, 1, 2);
745 if link = ''PHPH'' then
747 ''slotlink between two phones does not make sense'';
749 if link in (''PHHS'', ''HSPH'') then
751 ''link of phone to hub does not make sense'';
753 if link in (''PHIF'', ''IFPH'') then
755 ''link of phone to hub does not make sense'';
757 if link in (''PSWS'', ''WSPS'') then
759 ''slotlink from patchslot to wallslot not permitted'';
761 if mytype = ''PS'' then
762 select into rec * from PSlot where slotname = myname;
764 raise exception ''% does not exist'', myname;
766 if rec.slotlink != blname then
767 update PSlot set slotlink = blname where slotname = myname;
771 if mytype = ''WS'' then
772 select into rec * from WSlot where slotname = myname;
774 raise exception ''% does not exist'', myname;
776 if rec.slotlink != blname then
777 update WSlot set slotlink = blname where slotname = myname;
781 if mytype = ''IF'' then
782 select into rec * from IFace where slotname = myname;
784 raise exception ''% does not exist'', myname;
786 if rec.slotlink != blname then
787 update IFace set slotlink = blname where slotname = myname;
791 if mytype = ''HS'' then
792 select into rec * from HSlot where slotname = myname;
794 raise exception ''% does not exist'', myname;
796 if rec.slotlink != blname then
797 update HSlot set slotlink = blname where slotname = myname;
801 if mytype = ''PH'' then
802 select into rec * from PHone where slotname = myname;
804 raise exception ''% does not exist'', myname;
806 if rec.slotlink != blname then
807 update PHone set slotlink = blname where slotname = myname;
811 raise exception ''illegal slotlink beginning with %'', mytype;
814 -- ************************************************************
815 -- * Support function to clear out the slotlink field if
816 -- * it still points to specific slot
817 -- ************************************************************
818 create function tg_slotlink_unset(bpchar, bpchar)
826 mytype := substr(myname, 1, 2);
827 if mytype = ''PS'' then
828 select into rec * from PSlot where slotname = myname;
832 if rec.slotlink = blname then
833 update PSlot set slotlink = '''' where slotname = myname;
837 if mytype = ''WS'' then
838 select into rec * from WSlot where slotname = myname;
842 if rec.slotlink = blname then
843 update WSlot set slotlink = '''' where slotname = myname;
847 if mytype = ''IF'' then
848 select into rec * from IFace where slotname = myname;
852 if rec.slotlink = blname then
853 update IFace set slotlink = '''' where slotname = myname;
857 if mytype = ''HS'' then
858 select into rec * from HSlot where slotname = myname;
862 if rec.slotlink = blname then
863 update HSlot set slotlink = '''' where slotname = myname;
867 if mytype = ''PH'' then
868 select into rec * from PHone where slotname = myname;
872 if rec.slotlink = blname then
873 update PHone set slotlink = '''' where slotname = myname;
879 -- ************************************************************
880 -- * Describe the backside of a patchfield slot
881 -- ************************************************************
882 create function pslot_backlink_view(bpchar)
890 select into rec * from PSlot where slotname = $1;
894 if rec.backlink = '''' then
897 bltype := substr(rec.backlink, 1, 2);
898 if bltype = ''PL'' then
902 select into rec * from PLine where slotname = "outer".rec.backlink;
903 retval := ''Phone line '' || trim(rec.phonenumber);
904 if rec.comment != '''' then
905 retval := retval || '' ('';
906 retval := retval || rec.comment;
907 retval := retval || '')'';
912 if bltype = ''WS'' then
913 select into rec * from WSlot where slotname = rec.backlink;
914 retval := trim(rec.slotname) || '' in room '';
915 retval := retval || trim(rec.roomno);
916 retval := retval || '' -> '';
917 return retval || wslot_slotlink_view(rec.slotname);
922 -- ************************************************************
923 -- * Describe the front of a patchfield slot
924 -- ************************************************************
925 create function pslot_slotlink_view(bpchar)
932 select into psrec * from PSlot where slotname = $1;
936 if psrec.slotlink = '''' then
939 sltype := substr(psrec.slotlink, 1, 2);
940 if sltype = ''PS'' then
941 retval := trim(psrec.slotlink) || '' -> '';
942 return retval || pslot_backlink_view(psrec.slotlink);
944 if sltype = ''HS'' then
945 retval := comment from Hub H, HSlot HS
946 where HS.slotname = psrec.slotlink
947 and H.name = HS.hubname;
948 retval := retval || '' slot '';
949 retval := retval || slotno::text from HSlot
950 where slotname = psrec.slotlink;
953 return psrec.slotlink;
956 -- ************************************************************
957 -- * Describe the front of a wall connector slot
958 -- ************************************************************
959 create function wslot_slotlink_view(bpchar)
966 select into rec * from WSlot where slotname = $1;
970 if rec.slotlink = '''' then
973 sltype := substr(rec.slotlink, 1, 2);
974 if sltype = ''PH'' then
975 select into rec * from PHone where slotname = rec.slotlink;
976 retval := ''Phone '' || trim(rec.slotname);
977 if rec.comment != '''' then
978 retval := retval || '' ('';
979 retval := retval || rec.comment;
980 retval := retval || '')'';
984 if sltype = ''IF'' then
986 syrow System%RowType;
989 select into ifrow * from IFace where slotname = rec.slotlink;
990 select into syrow * from System where name = ifrow.sysname;
991 retval := syrow.name || '' IF '';
992 retval := retval || ifrow.ifname;
993 if syrow.comment != '''' then
994 retval := retval || '' ('';
995 retval := retval || syrow.comment;
996 retval := retval || '')'';
1001 return rec.slotlink;
1004 -- ************************************************************
1005 -- * View of a patchfield describing backside and patches
1006 -- ************************************************************
1007 create view Pfield_v1 as select PF.pfname, PF.slotname,
1008 pslot_backlink_view(PF.slotname) as backside,
1009 pslot_slotlink_view(PF.slotname) as patch
1012 -- First we build the house - so we create the rooms
1014 insert into Room values ('001', 'Entrance');
1015 insert into Room values ('002', 'Office');
1016 insert into Room values ('003', 'Office');
1017 insert into Room values ('004', 'Technical');
1018 insert into Room values ('101', 'Office');
1019 insert into Room values ('102', 'Conference');
1020 insert into Room values ('103', 'Restroom');
1021 insert into Room values ('104', 'Technical');
1022 insert into Room values ('105', 'Office');
1023 insert into Room values ('106', 'Office');
1025 -- Second we install the wall connectors
1027 insert into WSlot values ('WS.001.1a', '001', '', '');
1028 insert into WSlot values ('WS.001.1b', '001', '', '');
1029 insert into WSlot values ('WS.001.2a', '001', '', '');
1030 insert into WSlot values ('WS.001.2b', '001', '', '');
1031 insert into WSlot values ('WS.001.3a', '001', '', '');
1032 insert into WSlot values ('WS.001.3b', '001', '', '');
1033 insert into WSlot values ('WS.002.1a', '002', '', '');
1034 insert into WSlot values ('WS.002.1b', '002', '', '');
1035 insert into WSlot values ('WS.002.2a', '002', '', '');
1036 insert into WSlot values ('WS.002.2b', '002', '', '');
1037 insert into WSlot values ('WS.002.3a', '002', '', '');
1038 insert into WSlot values ('WS.002.3b', '002', '', '');
1039 insert into WSlot values ('WS.003.1a', '003', '', '');
1040 insert into WSlot values ('WS.003.1b', '003', '', '');
1041 insert into WSlot values ('WS.003.2a', '003', '', '');
1042 insert into WSlot values ('WS.003.2b', '003', '', '');
1043 insert into WSlot values ('WS.003.3a', '003', '', '');
1044 insert into WSlot values ('WS.003.3b', '003', '', '');
1045 insert into WSlot values ('WS.101.1a', '101', '', '');
1046 insert into WSlot values ('WS.101.1b', '101', '', '');
1047 insert into WSlot values ('WS.101.2a', '101', '', '');
1048 insert into WSlot values ('WS.101.2b', '101', '', '');
1049 insert into WSlot values ('WS.101.3a', '101', '', '');
1050 insert into WSlot values ('WS.101.3b', '101', '', '');
1051 insert into WSlot values ('WS.102.1a', '102', '', '');
1052 insert into WSlot values ('WS.102.1b', '102', '', '');
1053 insert into WSlot values ('WS.102.2a', '102', '', '');
1054 insert into WSlot values ('WS.102.2b', '102', '', '');
1055 insert into WSlot values ('WS.102.3a', '102', '', '');
1056 insert into WSlot values ('WS.102.3b', '102', '', '');
1057 insert into WSlot values ('WS.105.1a', '105', '', '');
1058 insert into WSlot values ('WS.105.1b', '105', '', '');
1059 insert into WSlot values ('WS.105.2a', '105', '', '');
1060 insert into WSlot values ('WS.105.2b', '105', '', '');
1061 insert into WSlot values ('WS.105.3a', '105', '', '');
1062 insert into WSlot values ('WS.105.3b', '105', '', '');
1063 insert into WSlot values ('WS.106.1a', '106', '', '');
1064 insert into WSlot values ('WS.106.1b', '106', '', '');
1065 insert into WSlot values ('WS.106.2a', '106', '', '');
1066 insert into WSlot values ('WS.106.2b', '106', '', '');
1067 insert into WSlot values ('WS.106.3a', '106', '', '');
1068 insert into WSlot values ('WS.106.3b', '106', '', '');
1070 -- Now create the patch fields and their slots
1072 insert into PField values ('PF0_1', 'Wallslots basement');
1074 -- The cables for these will be made later, so they are unconnected for now
1076 insert into PSlot values ('PS.base.a1', 'PF0_1', '', '');
1077 insert into PSlot values ('PS.base.a2', 'PF0_1', '', '');
1078 insert into PSlot values ('PS.base.a3', 'PF0_1', '', '');
1079 insert into PSlot values ('PS.base.a4', 'PF0_1', '', '');
1080 insert into PSlot values ('PS.base.a5', 'PF0_1', '', '');
1081 insert into PSlot values ('PS.base.a6', 'PF0_1', '', '');
1083 -- These are already wired to the wall connectors
1085 insert into PSlot values ('PS.base.b1', 'PF0_1', '', 'WS.002.1a');
1086 insert into PSlot values ('PS.base.b2', 'PF0_1', '', 'WS.002.1b');
1087 insert into PSlot values ('PS.base.b3', 'PF0_1', '', 'WS.002.2a');
1088 insert into PSlot values ('PS.base.b4', 'PF0_1', '', 'WS.002.2b');
1089 insert into PSlot values ('PS.base.b5', 'PF0_1', '', 'WS.002.3a');
1090 insert into PSlot values ('PS.base.b6', 'PF0_1', '', 'WS.002.3b');
1091 insert into PSlot values ('PS.base.c1', 'PF0_1', '', 'WS.003.1a');
1092 insert into PSlot values ('PS.base.c2', 'PF0_1', '', 'WS.003.1b');
1093 insert into PSlot values ('PS.base.c3', 'PF0_1', '', 'WS.003.2a');
1094 insert into PSlot values ('PS.base.c4', 'PF0_1', '', 'WS.003.2b');
1095 insert into PSlot values ('PS.base.c5', 'PF0_1', '', 'WS.003.3a');
1096 insert into PSlot values ('PS.base.c6', 'PF0_1', '', 'WS.003.3b');
1098 -- This patchfield will be renamed later into PF0_2 - so its
1099 -- slots references in pfname should follow
1101 insert into PField values ('PF0_X', 'Phonelines basement');
1102 insert into PSlot values ('PS.base.ta1', 'PF0_X', '', '');
1103 insert into PSlot values ('PS.base.ta2', 'PF0_X', '', '');
1104 insert into PSlot values ('PS.base.ta3', 'PF0_X', '', '');
1105 insert into PSlot values ('PS.base.ta4', 'PF0_X', '', '');
1106 insert into PSlot values ('PS.base.ta5', 'PF0_X', '', '');
1107 insert into PSlot values ('PS.base.ta6', 'PF0_X', '', '');
1108 insert into PSlot values ('PS.base.tb1', 'PF0_X', '', '');
1109 insert into PSlot values ('PS.base.tb2', 'PF0_X', '', '');
1110 insert into PSlot values ('PS.base.tb3', 'PF0_X', '', '');
1111 insert into PSlot values ('PS.base.tb4', 'PF0_X', '', '');
1112 insert into PSlot values ('PS.base.tb5', 'PF0_X', '', '');
1113 insert into PSlot values ('PS.base.tb6', 'PF0_X', '', '');
1114 insert into PField values ('PF1_1', 'Wallslots first floor');
1115 insert into PSlot values ('PS.first.a1', 'PF1_1', '', 'WS.101.1a');
1116 insert into PSlot values ('PS.first.a2', 'PF1_1', '', 'WS.101.1b');
1117 insert into PSlot values ('PS.first.a3', 'PF1_1', '', 'WS.101.2a');
1118 insert into PSlot values ('PS.first.a4', 'PF1_1', '', 'WS.101.2b');
1119 insert into PSlot values ('PS.first.a5', 'PF1_1', '', 'WS.101.3a');
1120 insert into PSlot values ('PS.first.a6', 'PF1_1', '', 'WS.101.3b');
1121 insert into PSlot values ('PS.first.b1', 'PF1_1', '', 'WS.102.1a');
1122 insert into PSlot values ('PS.first.b2', 'PF1_1', '', 'WS.102.1b');
1123 insert into PSlot values ('PS.first.b3', 'PF1_1', '', 'WS.102.2a');
1124 insert into PSlot values ('PS.first.b4', 'PF1_1', '', 'WS.102.2b');
1125 insert into PSlot values ('PS.first.b5', 'PF1_1', '', 'WS.102.3a');
1126 insert into PSlot values ('PS.first.b6', 'PF1_1', '', 'WS.102.3b');
1127 insert into PSlot values ('PS.first.c1', 'PF1_1', '', 'WS.105.1a');
1128 insert into PSlot values ('PS.first.c2', 'PF1_1', '', 'WS.105.1b');
1129 insert into PSlot values ('PS.first.c3', 'PF1_1', '', 'WS.105.2a');
1130 insert into PSlot values ('PS.first.c4', 'PF1_1', '', 'WS.105.2b');
1131 insert into PSlot values ('PS.first.c5', 'PF1_1', '', 'WS.105.3a');
1132 insert into PSlot values ('PS.first.c6', 'PF1_1', '', 'WS.105.3b');
1133 insert into PSlot values ('PS.first.d1', 'PF1_1', '', 'WS.106.1a');
1134 insert into PSlot values ('PS.first.d2', 'PF1_1', '', 'WS.106.1b');
1135 insert into PSlot values ('PS.first.d3', 'PF1_1', '', 'WS.106.2a');
1136 insert into PSlot values ('PS.first.d4', 'PF1_1', '', 'WS.106.2b');
1137 insert into PSlot values ('PS.first.d5', 'PF1_1', '', 'WS.106.3a');
1138 insert into PSlot values ('PS.first.d6', 'PF1_1', '', 'WS.106.3b');
1140 -- Now we wire the wall connectors 1a-2a in room 001 to the
1141 -- patchfield. In the second update we make an error, and
1144 update PSlot set backlink = 'WS.001.1a' where slotname = 'PS.base.a1';
1145 update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a3';
1146 select * from WSlot where roomno = '001' order by slotname;
1147 slotname | roomno | slotlink | backlink
1148 ----------------------+----------+----------------------+----------------------
1149 WS.001.1a | 001 | | PS.base.a1
1150 WS.001.1b | 001 | | PS.base.a3
1157 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1158 slotname | pfname | slotlink | backlink
1159 ----------------------+--------+----------------------+----------------------
1160 PS.base.a1 | PF0_1 | | WS.001.1a
1161 PS.base.a2 | PF0_1 | |
1162 PS.base.a3 | PF0_1 | | WS.001.1b
1163 PS.base.a4 | PF0_1 | |
1164 PS.base.a5 | PF0_1 | |
1165 PS.base.a6 | PF0_1 | |
1168 update PSlot set backlink = 'WS.001.2a' where slotname = 'PS.base.a3';
1169 select * from WSlot where roomno = '001' order by slotname;
1170 slotname | roomno | slotlink | backlink
1171 ----------------------+----------+----------------------+----------------------
1172 WS.001.1a | 001 | | PS.base.a1
1174 WS.001.2a | 001 | | PS.base.a3
1180 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1181 slotname | pfname | slotlink | backlink
1182 ----------------------+--------+----------------------+----------------------
1183 PS.base.a1 | PF0_1 | | WS.001.1a
1184 PS.base.a2 | PF0_1 | |
1185 PS.base.a3 | PF0_1 | | WS.001.2a
1186 PS.base.a4 | PF0_1 | |
1187 PS.base.a5 | PF0_1 | |
1188 PS.base.a6 | PF0_1 | |
1191 update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a2';
1192 select * from WSlot where roomno = '001' order by slotname;
1193 slotname | roomno | slotlink | backlink
1194 ----------------------+----------+----------------------+----------------------
1195 WS.001.1a | 001 | | PS.base.a1
1196 WS.001.1b | 001 | | PS.base.a2
1197 WS.001.2a | 001 | | PS.base.a3
1203 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1204 slotname | pfname | slotlink | backlink
1205 ----------------------+--------+----------------------+----------------------
1206 PS.base.a1 | PF0_1 | | WS.001.1a
1207 PS.base.a2 | PF0_1 | | WS.001.1b
1208 PS.base.a3 | PF0_1 | | WS.001.2a
1209 PS.base.a4 | PF0_1 | |
1210 PS.base.a5 | PF0_1 | |
1211 PS.base.a6 | PF0_1 | |
1215 -- Same procedure for 2b-3b but this time updating the WSlot instead
1216 -- of the PSlot. Due to the triggers the result is the same:
1217 -- WSlot and corresponding PSlot point to each other.
1219 update WSlot set backlink = 'PS.base.a4' where slotname = 'WS.001.2b';
1220 update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3a';
1221 select * from WSlot where roomno = '001' order by slotname;
1222 slotname | roomno | slotlink | backlink
1223 ----------------------+----------+----------------------+----------------------
1224 WS.001.1a | 001 | | PS.base.a1
1225 WS.001.1b | 001 | | PS.base.a2
1226 WS.001.2a | 001 | | PS.base.a3
1227 WS.001.2b | 001 | | PS.base.a4
1228 WS.001.3a | 001 | | PS.base.a6
1232 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1233 slotname | pfname | slotlink | backlink
1234 ----------------------+--------+----------------------+----------------------
1235 PS.base.a1 | PF0_1 | | WS.001.1a
1236 PS.base.a2 | PF0_1 | | WS.001.1b
1237 PS.base.a3 | PF0_1 | | WS.001.2a
1238 PS.base.a4 | PF0_1 | | WS.001.2b
1239 PS.base.a5 | PF0_1 | |
1240 PS.base.a6 | PF0_1 | | WS.001.3a
1243 update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3b';
1244 select * from WSlot where roomno = '001' order by slotname;
1245 slotname | roomno | slotlink | backlink
1246 ----------------------+----------+----------------------+----------------------
1247 WS.001.1a | 001 | | PS.base.a1
1248 WS.001.1b | 001 | | PS.base.a2
1249 WS.001.2a | 001 | | PS.base.a3
1250 WS.001.2b | 001 | | PS.base.a4
1252 WS.001.3b | 001 | | PS.base.a6
1255 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1256 slotname | pfname | slotlink | backlink
1257 ----------------------+--------+----------------------+----------------------
1258 PS.base.a1 | PF0_1 | | WS.001.1a
1259 PS.base.a2 | PF0_1 | | WS.001.1b
1260 PS.base.a3 | PF0_1 | | WS.001.2a
1261 PS.base.a4 | PF0_1 | | WS.001.2b
1262 PS.base.a5 | PF0_1 | |
1263 PS.base.a6 | PF0_1 | | WS.001.3b
1266 update WSlot set backlink = 'PS.base.a5' where slotname = 'WS.001.3a';
1267 select * from WSlot where roomno = '001' order by slotname;
1268 slotname | roomno | slotlink | backlink
1269 ----------------------+----------+----------------------+----------------------
1270 WS.001.1a | 001 | | PS.base.a1
1271 WS.001.1b | 001 | | PS.base.a2
1272 WS.001.2a | 001 | | PS.base.a3
1273 WS.001.2b | 001 | | PS.base.a4
1274 WS.001.3a | 001 | | PS.base.a5
1275 WS.001.3b | 001 | | PS.base.a6
1278 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1279 slotname | pfname | slotlink | backlink
1280 ----------------------+--------+----------------------+----------------------
1281 PS.base.a1 | PF0_1 | | WS.001.1a
1282 PS.base.a2 | PF0_1 | | WS.001.1b
1283 PS.base.a3 | PF0_1 | | WS.001.2a
1284 PS.base.a4 | PF0_1 | | WS.001.2b
1285 PS.base.a5 | PF0_1 | | WS.001.3a
1286 PS.base.a6 | PF0_1 | | WS.001.3b
1289 insert into PField values ('PF1_2', 'Phonelines first floor');
1290 insert into PSlot values ('PS.first.ta1', 'PF1_2', '', '');
1291 insert into PSlot values ('PS.first.ta2', 'PF1_2', '', '');
1292 insert into PSlot values ('PS.first.ta3', 'PF1_2', '', '');
1293 insert into PSlot values ('PS.first.ta4', 'PF1_2', '', '');
1294 insert into PSlot values ('PS.first.ta5', 'PF1_2', '', '');
1295 insert into PSlot values ('PS.first.ta6', 'PF1_2', '', '');
1296 insert into PSlot values ('PS.first.tb1', 'PF1_2', '', '');
1297 insert into PSlot values ('PS.first.tb2', 'PF1_2', '', '');
1298 insert into PSlot values ('PS.first.tb3', 'PF1_2', '', '');
1299 insert into PSlot values ('PS.first.tb4', 'PF1_2', '', '');
1300 insert into PSlot values ('PS.first.tb5', 'PF1_2', '', '');
1301 insert into PSlot values ('PS.first.tb6', 'PF1_2', '', '');
1303 -- Fix the wrong name for patchfield PF0_2
1305 update PField set name = 'PF0_2' where name = 'PF0_X';
1306 select * from PSlot order by slotname;
1307 slotname | pfname | slotlink | backlink
1308 ----------------------+--------+----------------------+----------------------
1309 PS.base.a1 | PF0_1 | | WS.001.1a
1310 PS.base.a2 | PF0_1 | | WS.001.1b
1311 PS.base.a3 | PF0_1 | | WS.001.2a
1312 PS.base.a4 | PF0_1 | | WS.001.2b
1313 PS.base.a5 | PF0_1 | | WS.001.3a
1314 PS.base.a6 | PF0_1 | | WS.001.3b
1315 PS.base.b1 | PF0_1 | | WS.002.1a
1316 PS.base.b2 | PF0_1 | | WS.002.1b
1317 PS.base.b3 | PF0_1 | | WS.002.2a
1318 PS.base.b4 | PF0_1 | | WS.002.2b
1319 PS.base.b5 | PF0_1 | | WS.002.3a
1320 PS.base.b6 | PF0_1 | | WS.002.3b
1321 PS.base.c1 | PF0_1 | | WS.003.1a
1322 PS.base.c2 | PF0_1 | | WS.003.1b
1323 PS.base.c3 | PF0_1 | | WS.003.2a
1324 PS.base.c4 | PF0_1 | | WS.003.2b
1325 PS.base.c5 | PF0_1 | | WS.003.3a
1326 PS.base.c6 | PF0_1 | | WS.003.3b
1327 PS.base.ta1 | PF0_2 | |
1328 PS.base.ta2 | PF0_2 | |
1329 PS.base.ta3 | PF0_2 | |
1330 PS.base.ta4 | PF0_2 | |
1331 PS.base.ta5 | PF0_2 | |
1332 PS.base.ta6 | PF0_2 | |
1333 PS.base.tb1 | PF0_2 | |
1334 PS.base.tb2 | PF0_2 | |
1335 PS.base.tb3 | PF0_2 | |
1336 PS.base.tb4 | PF0_2 | |
1337 PS.base.tb5 | PF0_2 | |
1338 PS.base.tb6 | PF0_2 | |
1339 PS.first.a1 | PF1_1 | | WS.101.1a
1340 PS.first.a2 | PF1_1 | | WS.101.1b
1341 PS.first.a3 | PF1_1 | | WS.101.2a
1342 PS.first.a4 | PF1_1 | | WS.101.2b
1343 PS.first.a5 | PF1_1 | | WS.101.3a
1344 PS.first.a6 | PF1_1 | | WS.101.3b
1345 PS.first.b1 | PF1_1 | | WS.102.1a
1346 PS.first.b2 | PF1_1 | | WS.102.1b
1347 PS.first.b3 | PF1_1 | | WS.102.2a
1348 PS.first.b4 | PF1_1 | | WS.102.2b
1349 PS.first.b5 | PF1_1 | | WS.102.3a
1350 PS.first.b6 | PF1_1 | | WS.102.3b
1351 PS.first.c1 | PF1_1 | | WS.105.1a
1352 PS.first.c2 | PF1_1 | | WS.105.1b
1353 PS.first.c3 | PF1_1 | | WS.105.2a
1354 PS.first.c4 | PF1_1 | | WS.105.2b
1355 PS.first.c5 | PF1_1 | | WS.105.3a
1356 PS.first.c6 | PF1_1 | | WS.105.3b
1357 PS.first.d1 | PF1_1 | | WS.106.1a
1358 PS.first.d2 | PF1_1 | | WS.106.1b
1359 PS.first.d3 | PF1_1 | | WS.106.2a
1360 PS.first.d4 | PF1_1 | | WS.106.2b
1361 PS.first.d5 | PF1_1 | | WS.106.3a
1362 PS.first.d6 | PF1_1 | | WS.106.3b
1363 PS.first.ta1 | PF1_2 | |
1364 PS.first.ta2 | PF1_2 | |
1365 PS.first.ta3 | PF1_2 | |
1366 PS.first.ta4 | PF1_2 | |
1367 PS.first.ta5 | PF1_2 | |
1368 PS.first.ta6 | PF1_2 | |
1369 PS.first.tb1 | PF1_2 | |
1370 PS.first.tb2 | PF1_2 | |
1371 PS.first.tb3 | PF1_2 | |
1372 PS.first.tb4 | PF1_2 | |
1373 PS.first.tb5 | PF1_2 | |
1374 PS.first.tb6 | PF1_2 | |
1377 select * from WSlot order by slotname;
1378 slotname | roomno | slotlink | backlink
1379 ----------------------+----------+----------------------+----------------------
1380 WS.001.1a | 001 | | PS.base.a1
1381 WS.001.1b | 001 | | PS.base.a2
1382 WS.001.2a | 001 | | PS.base.a3
1383 WS.001.2b | 001 | | PS.base.a4
1384 WS.001.3a | 001 | | PS.base.a5
1385 WS.001.3b | 001 | | PS.base.a6
1386 WS.002.1a | 002 | | PS.base.b1
1387 WS.002.1b | 002 | | PS.base.b2
1388 WS.002.2a | 002 | | PS.base.b3
1389 WS.002.2b | 002 | | PS.base.b4
1390 WS.002.3a | 002 | | PS.base.b5
1391 WS.002.3b | 002 | | PS.base.b6
1392 WS.003.1a | 003 | | PS.base.c1
1393 WS.003.1b | 003 | | PS.base.c2
1394 WS.003.2a | 003 | | PS.base.c3
1395 WS.003.2b | 003 | | PS.base.c4
1396 WS.003.3a | 003 | | PS.base.c5
1397 WS.003.3b | 003 | | PS.base.c6
1398 WS.101.1a | 101 | | PS.first.a1
1399 WS.101.1b | 101 | | PS.first.a2
1400 WS.101.2a | 101 | | PS.first.a3
1401 WS.101.2b | 101 | | PS.first.a4
1402 WS.101.3a | 101 | | PS.first.a5
1403 WS.101.3b | 101 | | PS.first.a6
1404 WS.102.1a | 102 | | PS.first.b1
1405 WS.102.1b | 102 | | PS.first.b2
1406 WS.102.2a | 102 | | PS.first.b3
1407 WS.102.2b | 102 | | PS.first.b4
1408 WS.102.3a | 102 | | PS.first.b5
1409 WS.102.3b | 102 | | PS.first.b6
1410 WS.105.1a | 105 | | PS.first.c1
1411 WS.105.1b | 105 | | PS.first.c2
1412 WS.105.2a | 105 | | PS.first.c3
1413 WS.105.2b | 105 | | PS.first.c4
1414 WS.105.3a | 105 | | PS.first.c5
1415 WS.105.3b | 105 | | PS.first.c6
1416 WS.106.1a | 106 | | PS.first.d1
1417 WS.106.1b | 106 | | PS.first.d2
1418 WS.106.2a | 106 | | PS.first.d3
1419 WS.106.2b | 106 | | PS.first.d4
1420 WS.106.3a | 106 | | PS.first.d5
1421 WS.106.3b | 106 | | PS.first.d6
1425 -- Install the central phone system and create the phone numbers.
1426 -- They are weired on insert to the patchfields. Again the
1427 -- triggers automatically tell the PSlots to update their
1430 insert into PLine values ('PL.001', '-0', 'Central call', 'PS.base.ta1');
1431 insert into PLine values ('PL.002', '-101', '', 'PS.base.ta2');
1432 insert into PLine values ('PL.003', '-102', '', 'PS.base.ta3');
1433 insert into PLine values ('PL.004', '-103', '', 'PS.base.ta5');
1434 insert into PLine values ('PL.005', '-104', '', 'PS.base.ta6');
1435 insert into PLine values ('PL.006', '-106', '', 'PS.base.tb2');
1436 insert into PLine values ('PL.007', '-108', '', 'PS.base.tb3');
1437 insert into PLine values ('PL.008', '-109', '', 'PS.base.tb4');
1438 insert into PLine values ('PL.009', '-121', '', 'PS.base.tb5');
1439 insert into PLine values ('PL.010', '-122', '', 'PS.base.tb6');
1440 insert into PLine values ('PL.015', '-134', '', 'PS.first.ta1');
1441 insert into PLine values ('PL.016', '-137', '', 'PS.first.ta3');
1442 insert into PLine values ('PL.017', '-139', '', 'PS.first.ta4');
1443 insert into PLine values ('PL.018', '-362', '', 'PS.first.tb1');
1444 insert into PLine values ('PL.019', '-363', '', 'PS.first.tb2');
1445 insert into PLine values ('PL.020', '-364', '', 'PS.first.tb3');
1446 insert into PLine values ('PL.021', '-365', '', 'PS.first.tb5');
1447 insert into PLine values ('PL.022', '-367', '', 'PS.first.tb6');
1448 insert into PLine values ('PL.028', '-501', 'Fax entrance', 'PS.base.ta2');
1449 insert into PLine values ('PL.029', '-502', 'Fax first floor', 'PS.first.ta1');
1451 -- Buy some phones, plug them into the wall and patch the
1452 -- phone lines to the corresponding patchfield slots.
1454 insert into PHone values ('PH.hc001', 'Hicom standard', 'WS.001.1a');
1455 update PSlot set slotlink = 'PS.base.ta1' where slotname = 'PS.base.a1';
1456 insert into PHone values ('PH.hc002', 'Hicom standard', 'WS.002.1a');
1457 update PSlot set slotlink = 'PS.base.ta5' where slotname = 'PS.base.b1';
1458 insert into PHone values ('PH.hc003', 'Hicom standard', 'WS.002.2a');
1459 update PSlot set slotlink = 'PS.base.tb2' where slotname = 'PS.base.b3';
1460 insert into PHone values ('PH.fax001', 'Canon fax', 'WS.001.2a');
1461 update PSlot set slotlink = 'PS.base.ta2' where slotname = 'PS.base.a3';
1463 -- Install a hub at one of the patchfields, plug a computers
1464 -- ethernet interface into the wall and patch it to the hub.
1466 insert into Hub values ('base.hub1', 'Patchfield PF0_1 hub', 16);
1467 insert into System values ('orion', 'PC');
1468 insert into IFace values ('IF', 'orion', 'eth0', 'WS.002.1b');
1469 update PSlot set slotlink = 'HS.base.hub1.1' where slotname = 'PS.base.b2';
1471 -- Now we take a look at the patchfield
1473 select * from PField_v1 where pfname = 'PF0_1' order by slotname;
1474 pfname | slotname | backside | patch
1475 --------+----------------------+----------------------------------------------------------+-----------------------------------------------
1476 PF0_1 | PS.base.a1 | WS.001.1a in room 001 -> Phone PH.hc001 (Hicom standard) | PS.base.ta1 -> Phone line -0 (Central call)
1477 PF0_1 | PS.base.a2 | WS.001.1b in room 001 -> - | -
1478 PF0_1 | PS.base.a3 | WS.001.2a in room 001 -> Phone PH.fax001 (Canon fax) | PS.base.ta2 -> Phone line -501 (Fax entrance)
1479 PF0_1 | PS.base.a4 | WS.001.2b in room 001 -> - | -
1480 PF0_1 | PS.base.a5 | WS.001.3a in room 001 -> - | -
1481 PF0_1 | PS.base.a6 | WS.001.3b in room 001 -> - | -
1482 PF0_1 | PS.base.b1 | WS.002.1a in room 002 -> Phone PH.hc002 (Hicom standard) | PS.base.ta5 -> Phone line -103
1483 PF0_1 | PS.base.b2 | WS.002.1b in room 002 -> orion IF eth0 (PC) | Patchfield PF0_1 hub slot 1
1484 PF0_1 | PS.base.b3 | WS.002.2a in room 002 -> Phone PH.hc003 (Hicom standard) | PS.base.tb2 -> Phone line -106
1485 PF0_1 | PS.base.b4 | WS.002.2b in room 002 -> - | -
1486 PF0_1 | PS.base.b5 | WS.002.3a in room 002 -> - | -
1487 PF0_1 | PS.base.b6 | WS.002.3b in room 002 -> - | -
1488 PF0_1 | PS.base.c1 | WS.003.1a in room 003 -> - | -
1489 PF0_1 | PS.base.c2 | WS.003.1b in room 003 -> - | -
1490 PF0_1 | PS.base.c3 | WS.003.2a in room 003 -> - | -
1491 PF0_1 | PS.base.c4 | WS.003.2b in room 003 -> - | -
1492 PF0_1 | PS.base.c5 | WS.003.3a in room 003 -> - | -
1493 PF0_1 | PS.base.c6 | WS.003.3b in room 003 -> - | -
1496 select * from PField_v1 where pfname = 'PF0_2' order by slotname;
1497 pfname | slotname | backside | patch
1498 --------+----------------------+--------------------------------+------------------------------------------------------------------------
1499 PF0_2 | PS.base.ta1 | Phone line -0 (Central call) | PS.base.a1 -> WS.001.1a in room 001 -> Phone PH.hc001 (Hicom standard)
1500 PF0_2 | PS.base.ta2 | Phone line -501 (Fax entrance) | PS.base.a3 -> WS.001.2a in room 001 -> Phone PH.fax001 (Canon fax)
1501 PF0_2 | PS.base.ta3 | Phone line -102 | -
1502 PF0_2 | PS.base.ta4 | - | -
1503 PF0_2 | PS.base.ta5 | Phone line -103 | PS.base.b1 -> WS.002.1a in room 002 -> Phone PH.hc002 (Hicom standard)
1504 PF0_2 | PS.base.ta6 | Phone line -104 | -
1505 PF0_2 | PS.base.tb1 | - | -
1506 PF0_2 | PS.base.tb2 | Phone line -106 | PS.base.b3 -> WS.002.2a in room 002 -> Phone PH.hc003 (Hicom standard)
1507 PF0_2 | PS.base.tb3 | Phone line -108 | -
1508 PF0_2 | PS.base.tb4 | Phone line -109 | -
1509 PF0_2 | PS.base.tb5 | Phone line -121 | -
1510 PF0_2 | PS.base.tb6 | Phone line -122 | -
1514 -- Finally we want errors
1516 insert into PField values ('PF1_1', 'should fail due to unique index');
1517 ERROR: duplicate key value violates unique constraint "pfield_name"
1518 DETAIL: Key (name)=(PF1_1) already exists.
1519 update PSlot set backlink = 'WS.not.there' where slotname = 'PS.base.a1';
1520 ERROR: WS.not.there does not exist
1521 CONTEXT: PL/pgSQL function "tg_backlink_a" line 16 at assignment
1522 update PSlot set backlink = 'XX.illegal' where slotname = 'PS.base.a1';
1523 ERROR: illegal backlink beginning with XX
1524 CONTEXT: PL/pgSQL function "tg_backlink_a" line 16 at assignment
1525 update PSlot set slotlink = 'PS.not.there' where slotname = 'PS.base.a1';
1526 ERROR: PS.not.there does not exist
1527 CONTEXT: PL/pgSQL function "tg_slotlink_a" line 16 at assignment
1528 update PSlot set slotlink = 'XX.illegal' where slotname = 'PS.base.a1';
1529 ERROR: illegal slotlink beginning with XX
1530 CONTEXT: PL/pgSQL function "tg_slotlink_a" line 16 at assignment
1531 insert into HSlot values ('HS', 'base.hub1', 1, '');
1532 ERROR: duplicate key value violates unique constraint "hslot_name"
1533 DETAIL: Key (slotname)=(HS.base.hub1.1 ) already exists.
1534 insert into HSlot values ('HS', 'base.hub1', 20, '');
1535 ERROR: no manual manipulation of HSlot
1537 ERROR: no manual manipulation of HSlot
1538 insert into IFace values ('IF', 'notthere', 'eth0', '');
1539 ERROR: system "notthere" does not exist
1540 insert into IFace values ('IF', 'orion', 'ethernet_interface_name_too_long', '');
1541 ERROR: IFace slotname "IF.orion.ethernet_interface_name_too_long" too long (20 char max)
1543 -- The following tests are unrelated to the scenario outlined above;
1544 -- they merely exercise specific parts of PL/PgSQL
1547 -- Test recursion, per bug report 7-Sep-01
1549 CREATE FUNCTION recursion_test(int,int) RETURNS text AS '
1553 rslt = CAST($2 AS TEXT);
1555 rslt = CAST($1 AS TEXT) || '','' || recursion_test($1 - 1, $2);
1558 END;' LANGUAGE plpgsql;
1559 SELECT recursion_test(4,3);
1566 -- Test the FOUND magic variable
1568 CREATE TABLE found_test_tbl (a int);
1569 create function test_found()
1570 returns boolean as '
1573 insert into found_test_tbl values (1);
1575 insert into found_test_tbl values (2);
1578 update found_test_tbl set a = 100 where a = 1;
1580 insert into found_test_tbl values (3);
1583 delete from found_test_tbl where a = 9999; -- matches no rows
1585 insert into found_test_tbl values (4);
1588 for i in 1 .. 10 loop
1589 -- no need to do anything
1592 insert into found_test_tbl values (5);
1595 -- never executes the loop
1596 for i in 2 .. 1 loop
1597 -- no need to do anything
1600 insert into found_test_tbl values (6);
1603 end;' language plpgsql;
1604 select test_found();
1610 select * from found_test_tbl;
1622 -- Test set-returning functions for PL/pgSQL
1624 create function test_table_func_rec() returns setof found_test_tbl as '
1628 FOR rec IN select * from found_test_tbl LOOP
1632 END;' language plpgsql;
1633 select * from test_table_func_rec();
1644 create function test_table_func_row() returns setof found_test_tbl as '
1646 row found_test_tbl%ROWTYPE;
1648 FOR row IN select * from found_test_tbl LOOP
1652 END;' language plpgsql;
1653 select * from test_table_func_row();
1664 create function test_ret_set_scalar(int,int) returns setof int as '
1668 FOR i IN $1 .. $2 LOOP
1672 END;' language plpgsql;
1673 select * from test_ret_set_scalar(1,10);
1675 ---------------------
1688 create function test_ret_set_rec_dyn(int) returns setof record as '
1693 SELECT INTO retval 5, 10, 15;
1697 SELECT INTO retval 50, 5::numeric, ''xxx''::text;
1702 END;' language plpgsql;
1703 SELECT * FROM test_ret_set_rec_dyn(1500) AS (a int, b int, c int);
1710 SELECT * FROM test_ret_set_rec_dyn(5) AS (a int, b numeric, c text);
1717 create function test_ret_rec_dyn(int) returns record as '
1722 SELECT INTO retval 5, 10, 15;
1725 SELECT INTO retval 50, 5::numeric, ''xxx''::text;
1728 END;' language plpgsql;
1729 SELECT * FROM test_ret_rec_dyn(1500) AS (a int, b int, c int);
1735 SELECT * FROM test_ret_rec_dyn(5) AS (a int, b numeric, c text);
1742 -- Test handling of OUT parameters, including polymorphic cases.
1743 -- Note that RETURN is optional with OUT params; we try both ways.
1745 -- wrong way to do it:
1746 create function f1(in i int, out j int) returns int as $$
1749 end$$ language plpgsql;
1750 ERROR: RETURN cannot have a parameter in function with OUT parameters
1753 create function f1(in i int, out j int) as $$
1757 end$$ language plpgsql;
1764 select * from f1(42);
1770 create or replace function f1(inout i int) as $$
1773 end$$ language plpgsql;
1780 select * from f1(42);
1786 drop function f1(int);
1787 create function f1(in i int, out j int) returns setof int as $$
1794 end$$ language plpgsql;
1795 select * from f1(42);
1802 drop function f1(int);
1803 create function f1(in i int, out j int, out k text) as $$
1808 end$$ language plpgsql;
1815 select * from f1(42);
1821 drop function f1(int);
1822 create function f1(in i int, out j int, out k text) returns setof record as $$
1830 end$$ language plpgsql;
1831 select * from f1(42);
1838 drop function f1(int);
1839 create function duplic(in i anyelement, out j anyelement, out k anyarray) as $$
1844 end$$ language plpgsql;
1845 select * from duplic(42);
1851 select * from duplic('foo'::text);
1857 drop function duplic(anyelement);
1861 create table perform_test (
1865 create function simple_func(int) returns boolean as '
1868 INSERT INTO perform_test VALUES ($1, $1 + 10);
1873 END;' language plpgsql;
1874 create function perform_test_func() returns void as '
1877 INSERT INTO perform_test VALUES (100, 100);
1880 PERFORM simple_func(5);
1883 INSERT INTO perform_test VALUES (100, 100);
1886 PERFORM simple_func(50);
1889 INSERT INTO perform_test VALUES (100, 100);
1893 END;' language plpgsql;
1894 SELECT perform_test_func();
1900 SELECT * FROM perform_test;
1908 drop table perform_test;
1910 -- Test error trapping
1912 create function trap_zero_divide(int) returns int as $$
1916 begin -- start a subtransaction
1917 raise notice 'should see this';
1919 raise notice 'should see this only if % <> 0', $1;
1921 raise notice 'should see this only if % fits in smallint', $1;
1923 raise exception '% is less than zero', $1;
1926 when division_by_zero then
1927 raise notice 'caught division_by_zero';
1929 when NUMERIC_VALUE_OUT_OF_RANGE then
1930 raise notice 'caught numeric_value_out_of_range';
1934 end$$ language plpgsql;
1935 select trap_zero_divide(50);
1936 NOTICE: should see this
1937 NOTICE: should see this only if 50 <> 0
1938 NOTICE: should see this only if 50 fits in smallint
1944 select trap_zero_divide(0);
1945 NOTICE: should see this
1946 NOTICE: caught division_by_zero
1952 select trap_zero_divide(100000);
1953 NOTICE: should see this
1954 NOTICE: should see this only if 100000 <> 0
1955 NOTICE: caught numeric_value_out_of_range
1961 select trap_zero_divide(-100);
1962 NOTICE: should see this
1963 NOTICE: should see this only if -100 <> 0
1964 NOTICE: should see this only if -100 fits in smallint
1965 ERROR: -100 is less than zero
1966 create function trap_matching_test(int) returns int as $$
1971 begin -- start a subtransaction
1974 select into y unique1 from tenk1 where unique2 =
1975 (select unique2 from tenk1 b where ten = $1);
1977 when data_exception then -- category match
1978 raise notice 'caught data_exception';
1980 when NUMERIC_VALUE_OUT_OF_RANGE OR CARDINALITY_VIOLATION then
1981 raise notice 'caught numeric_value_out_of_range or cardinality_violation';
1985 end$$ language plpgsql;
1986 select trap_matching_test(50);
1988 --------------------
1992 select trap_matching_test(0);
1993 NOTICE: caught data_exception
1995 --------------------
1999 select trap_matching_test(100000);
2000 NOTICE: caught data_exception
2002 --------------------
2006 select trap_matching_test(1);
2007 NOTICE: caught numeric_value_out_of_range or cardinality_violation
2009 --------------------
2013 create temp table foo (f1 int);
2014 create function blockme() returns int as $$
2018 insert into foo values(x);
2021 insert into foo values(x);
2022 -- we assume this will take longer than 2 seconds:
2023 select count(*) into x from tenk1 a, tenk1 b, tenk1 c;
2026 raise notice 'caught others?';
2028 when query_canceled then
2029 raise notice 'nyeah nyeah, can''t stop me';
2032 insert into foo values(x);
2034 end$$ language plpgsql;
2035 set statement_timeout to 2000;
2037 NOTICE: nyeah nyeah, can't stop me
2043 reset statement_timeout;
2052 -- Test for pass-by-ref values being stored in proper context
2053 create function test_variable_storage() returns text as $$
2059 -- force error inside subtransaction SPI context
2060 perform trap_zero_divide(-100);
2066 end$$ language plpgsql;
2067 select test_variable_storage();
2068 NOTICE: should see this
2069 CONTEXT: SQL statement "SELECT trap_zero_divide(-100)"
2070 PL/pgSQL function "test_variable_storage" line 7 at PERFORM
2071 NOTICE: should see this only if -100 <> 0
2072 CONTEXT: SQL statement "SELECT trap_zero_divide(-100)"
2073 PL/pgSQL function "test_variable_storage" line 7 at PERFORM
2074 NOTICE: should see this only if -100 fits in smallint
2075 CONTEXT: SQL statement "SELECT trap_zero_divide(-100)"
2076 PL/pgSQL function "test_variable_storage" line 7 at PERFORM
2077 test_variable_storage
2078 -----------------------
2083 -- test foreign key error trapping
2085 create temp table master(f1 int primary key);
2086 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "master_pkey" for table "master"
2087 create temp table slave(f1 int references master deferrable);
2088 insert into master values(1);
2089 insert into slave values(1);
2090 insert into slave values(2); -- fails
2091 ERROR: insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
2092 DETAIL: Key (f1)=(2) is not present in table "master".
2093 create function trap_foreign_key(int) returns int as $$
2095 begin -- start a subtransaction
2096 insert into slave values($1);
2098 when foreign_key_violation then
2099 raise notice 'caught foreign_key_violation';
2103 end$$ language plpgsql;
2104 create function trap_foreign_key_2() returns int as $$
2106 begin -- start a subtransaction
2107 set constraints all immediate;
2109 when foreign_key_violation then
2110 raise notice 'caught foreign_key_violation';
2114 end$$ language plpgsql;
2115 select trap_foreign_key(1);
2121 select trap_foreign_key(2); -- detects FK violation
2122 NOTICE: caught foreign_key_violation
2129 set constraints all deferred;
2130 select trap_foreign_key(2); -- should not detect FK violation
2137 set constraints all immediate; -- fails
2138 ERROR: insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
2139 DETAIL: Key (f1)=(2) is not present in table "master".
2141 select trap_foreign_key_2(); -- detects FK violation
2142 NOTICE: caught foreign_key_violation
2144 --------------------
2148 commit; -- still fails
2149 ERROR: insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
2150 DETAIL: Key (f1)=(2) is not present in table "master".
2151 drop function trap_foreign_key(int);
2152 drop function trap_foreign_key_2();
2154 -- Test proper snapshot handling in simple expressions
2156 create temp table users(login text, id serial);
2157 NOTICE: CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id"
2158 create function sp_id_user(a_login text) returns int as $$
2161 select into x id from users where login = a_login;
2162 if found then return x; end if;
2164 end$$ language plpgsql stable;
2165 insert into users values('user1');
2166 select sp_id_user('user1');
2172 select sp_id_user('userx');
2178 create function sp_add_user(a_login text) returns int as $$
2179 declare my_id_user int;
2181 my_id_user = sp_id_user( a_login );
2182 IF my_id_user > 0 THEN
2183 RETURN -1; -- error code for existing user
2185 INSERT INTO users ( login ) VALUES ( a_login );
2186 my_id_user = sp_id_user( a_login );
2187 IF my_id_user = 0 THEN
2188 RETURN -2; -- error code for insertion failure
2191 end$$ language plpgsql;
2192 select sp_add_user('user1');
2198 select sp_add_user('user2');
2204 select sp_add_user('user2');
2210 select sp_add_user('user3');
2216 select sp_add_user('user3');
2222 drop function sp_add_user(text);
2223 drop function sp_id_user(text);
2225 -- tests for refcursors
2227 create table rc_test (a int, b int);
2228 copy rc_test from stdin;
2229 create function return_refcursor(rc refcursor) returns refcursor as $$
2231 open rc for select a from rc_test;
2234 $$ language plpgsql;
2235 create function refcursor_test1(refcursor) returns refcursor as $$
2237 perform return_refcursor($1);
2240 $$ language plpgsql;
2242 select refcursor_test1('test1');
2248 fetch next in test1;
2254 select refcursor_test1('test2');
2260 fetch all from test2;
2270 fetch next from test1;
2271 ERROR: cursor "test1" does not exist
2272 create function refcursor_test2(int, int) returns boolean as $$
2274 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
2278 fetch c1 into nonsense;
2286 $$ language plpgsql;
2287 select refcursor_test2(20000, 20000) as "Should be false",
2288 refcursor_test2(20, 20) as "Should be true";
2289 Should be false | Should be true
2290 -----------------+----------------
2295 -- tests for "raise" processing
2297 create function raise_test1(int) returns int as $$
2299 raise notice 'This message has too many parameters!', $1;
2302 $$ language plpgsql;
2303 select raise_test1(5);
2304 ERROR: too many parameters specified for RAISE
2305 CONTEXT: PL/pgSQL function "raise_test1" line 2 at RAISE
2306 create function raise_test2(int) returns int as $$
2308 raise notice 'This message has too few parameters: %, %, %', $1, $1;
2311 $$ language plpgsql;
2312 select raise_test2(10);
2313 ERROR: too few parameters specified for RAISE
2314 CONTEXT: PL/pgSQL function "raise_test2" line 2 at RAISE
2316 -- reject function definitions that contain malformed SQL queries at
2317 -- compile-time, where possible
2319 create function bad_sql1() returns int as $$
2326 end$$ language plpgsql;
2327 ERROR: syntax error at or near "Johnny"
2328 LINE 5: Johnny Yuma;
2330 create function bad_sql2() returns int as $$
2333 for r in select I fought the law, the law won LOOP
2334 raise notice 'in loop';
2337 end;$$ language plpgsql;
2338 ERROR: syntax error at or near "the"
2339 LINE 4: for r in select I fought the law, the law won LOOP
2341 -- a RETURN expression is mandatory, except for void-returning
2342 -- functions, where it is not allowed
2343 create function missing_return_expr() returns int as $$
2346 end;$$ language plpgsql;
2347 ERROR: missing expression at or near ";"
2350 create function void_return_expr() returns void as $$
2353 end;$$ language plpgsql;
2354 ERROR: RETURN cannot have a parameter in function returning void
2357 -- VOID functions are allowed to omit RETURN
2358 create function void_return_expr() returns void as $$
2361 end;$$ language plpgsql;
2362 select void_return_expr();
2368 -- but ordinary functions are not
2369 create function missing_return_expr() returns int as $$
2372 end;$$ language plpgsql;
2373 select missing_return_expr();
2374 ERROR: control reached end of function without RETURN
2375 CONTEXT: PL/pgSQL function "missing_return_expr"
2376 drop function void_return_expr();
2377 drop function missing_return_expr();
2379 -- EXECUTE ... INTO test
2381 create table eifoo (i integer, y integer);
2382 create type eitype as (i integer, y integer);
2383 create or replace function execute_into_test(varchar) returns record as $$
2392 execute 'insert into '||$1||' values(10,15)';
2393 execute 'select (row).* from (select row(10,1)::eifoo) s' into _r;
2394 raise notice '% %', _r.i, _r.y;
2395 execute 'select * from '||$1||' limit 1' into _rt;
2396 raise notice '% %', _rt.i, _rt.y;
2397 execute 'select *, 20 from '||$1||' limit 1' into i, j, k;
2398 raise notice '% % %', i, j, k;
2399 execute 'select 1,2' into _v;
2401 end; $$ language plpgsql;
2402 select execute_into_test('eifoo');
2411 drop table eifoo cascade;
2412 drop type eitype cascade;
2414 -- SQLSTATE and SQLERRM test
2416 create function excpt_test1() returns void as $$
2418 raise notice '% %', sqlstate, sqlerrm;
2419 end; $$ language plpgsql;
2420 -- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
2422 select excpt_test1();
2423 ERROR: column "sqlstate" does not exist
2424 LINE 1: SELECT sqlstate
2426 QUERY: SELECT sqlstate
2427 CONTEXT: PL/pgSQL function "excpt_test1" line 2 at RAISE
2428 create function excpt_test2() returns void as $$
2432 raise notice '% %', sqlstate, sqlerrm;
2435 end; $$ language plpgsql;
2437 select excpt_test2();
2438 ERROR: column "sqlstate" does not exist
2439 LINE 1: SELECT sqlstate
2441 QUERY: SELECT sqlstate
2442 CONTEXT: PL/pgSQL function "excpt_test2" line 4 at RAISE
2443 create function excpt_test3() returns void as $$
2446 raise exception 'user exception';
2447 exception when others then
2448 raise notice 'caught exception % %', sqlstate, sqlerrm;
2450 raise notice '% %', sqlstate, sqlerrm;
2453 when substring_error then
2454 -- this exception handler shouldn't be invoked
2455 raise notice 'unexpected exception: % %', sqlstate, sqlerrm;
2456 when division_by_zero then
2457 raise notice 'caught exception % %', sqlstate, sqlerrm;
2459 raise notice '% %', sqlstate, sqlerrm;
2461 end; $$ language plpgsql;
2462 select excpt_test3();
2463 NOTICE: caught exception P0001 user exception
2464 NOTICE: P0001 user exception
2465 NOTICE: caught exception 22012 division by zero
2466 NOTICE: P0001 user exception
2472 drop function excpt_test1();
2473 drop function excpt_test2();
2474 drop function excpt_test3();
2475 -- parameters of raise stmt can be expressions
2476 create function raise_exprs() returns void as $$
2478 a integer[] = '{10,20,30}';
2483 raise notice '%; %; %; %; %; %', a, a[i], c, (select c || 'abc'), row(10,'aaa',NULL,30), NULL;
2484 end;$$ language plpgsql;
2485 select raise_exprs();
2486 NOTICE: {10,20,30}; 20; xyz; xyzabc; (10,aaa,,30); <NULL>
2492 drop function raise_exprs();
2493 -- continue statement
2494 create table conttesttbl(idx serial, v integer);
2495 NOTICE: CREATE TABLE will create implicit sequence "conttesttbl_idx_seq" for serial column "conttesttbl.idx"
2496 insert into conttesttbl(v) values(10);
2497 insert into conttesttbl(v) values(20);
2498 insert into conttesttbl(v) values(30);
2499 insert into conttesttbl(v) values(40);
2500 create function continue_test1() returns void as $$
2501 declare _i integer = 0; _r record;
2503 raise notice '---1---';
2506 raise notice '%', _i;
2507 continue when _i < 10;
2511 raise notice '---2---';
2516 raise notice '%', _i;
2517 continue lbl when _i > 0;
2522 raise notice '---3---';
2526 continue the_loop when _i % 2 = 0;
2527 raise notice '%', _i;
2530 raise notice '---4---';
2531 for _i in 1..10 loop
2533 -- applies to outer loop, not the nested begin block
2534 continue when _i < 5;
2535 raise notice '%', _i;
2539 raise notice '---5---';
2540 for _r in select * from conttesttbl loop
2541 continue when _r.v <= 20;
2542 raise notice '%', _r.v;
2545 raise notice '---6---';
2546 for _r in execute 'select * from conttesttbl' loop
2547 continue when _r.v <= 20;
2548 raise notice '%', _r.v;
2551 raise notice '---7---';
2553 raise notice '%', _i;
2554 continue when _i = 3;
2557 raise notice '---8---';
2560 raise notice '%', _i;
2562 continue when _i = 3;
2565 raise notice '---9---';
2566 for _r in select * from conttesttbl order by v limit 1 loop
2567 raise notice '%', _r.v;
2571 raise notice '---10---';
2572 for _r in execute 'select * from conttesttbl order by v limit 1' loop
2573 raise notice '%', _r.v;
2576 end; $$ language plpgsql;
2577 select continue_test1();
2636 -- CONTINUE is only legal inside a loop
2637 create function continue_test2() returns void as $$
2644 $$ language plpgsql;
2646 select continue_test2();
2647 ERROR: CONTINUE cannot be used outside a loop
2648 CONTEXT: PL/pgSQL function "continue_test2"
2649 -- CONTINUE can't reference the label of a named block
2650 create function continue_test3() returns void as $$
2655 continue begin_block1;
2659 $$ language plpgsql;
2661 select continue_test3();
2662 ERROR: CONTINUE cannot be used outside a loop
2663 CONTEXT: PL/pgSQL function "continue_test3"
2664 drop function continue_test1();
2665 drop function continue_test2();
2666 drop function continue_test3();
2667 drop table conttesttbl;
2668 -- verbose end block and end loop
2669 create function end_label1() returns void as $$
2673 for _i in 1 .. 10 loop
2677 for _i in 1 .. 10 loop
2681 $$ language plpgsql;
2682 select end_label1();
2688 drop function end_label1();
2689 -- should fail: undefined end label
2690 create function end_label2() returns void as $$
2692 for _i in 1 .. 10 loop
2696 $$ language plpgsql;
2697 ERROR: label does not exist at or near "flbl1"
2698 LINE 5: end loop flbl1;
2700 -- should fail: end label does not match start label
2701 create function end_label3() returns void as $$
2705 for _i in 1 .. 10 loop
2707 end loop outer_label;
2709 $$ language plpgsql;
2710 ERROR: end label "outer_label" differs from block's label "inner_label"
2711 LINE 7: end loop outer_label;
2713 -- should fail: end label on a block without a start label
2714 create function end_label4() returns void as $$
2717 for _i in 1 .. 10 loop
2719 end loop outer_label;
2721 $$ language plpgsql;
2722 ERROR: end label "outer_label" specified for unlabelled block
2723 LINE 6: end loop outer_label;
2725 -- using list of scalars in fori and fore stmts
2726 create function for_vect() returns void as $proc$
2727 <<lbl>>declare a integer; b varchar; c varchar; r record;
2730 for i in 1 .. 3 loop
2731 raise notice '%', i;
2733 -- fore with record var
2734 for r in select gs as aa, 'BB' as bb, 'CC' as cc from generate_series(1,4) gs loop
2735 raise notice '% % %', r.aa, r.bb, r.cc;
2737 -- fore with single scalar
2738 for a in select gs from generate_series(1,4) gs loop
2739 raise notice '%', a;
2741 -- fore with multiple scalars
2742 for a,b,c in select gs, 'BB','CC' from generate_series(1,4) gs loop
2743 raise notice '% % %', a, b, c;
2745 -- using qualified names in fors, fore is enabled, disabled only for fori
2746 for lbl.a, lbl.b, lbl.c in execute $$select gs, 'bb','cc' from generate_series(1,4) gs$$ loop
2747 raise notice '% % %', a, b, c;
2750 $proc$ language plpgsql;
2776 -- regression test: verify that multiple uses of same plpgsql datum within
2777 -- a SQL command all get mapped to the same $n parameter. The return value
2778 -- of the SELECT is not important, we only care that it doesn't fail with
2779 -- a complaint about an ungrouped column reference.
2780 create function multi_datum_use(p1 int) returns bool as $$
2785 select into x,y unique1/p1, unique1/$1 from tenk1 group by unique1/p1;
2787 end$$ language plpgsql;
2788 select multi_datum_use(42);
2795 -- Test STRICT limiter in both planned and EXECUTE invocations.
2796 -- Note that a data-modifying query is quasi strict (disallow multi rows)
2797 -- by default in the planned case, but not in EXECUTE.
2799 create temp table foo (f1 int, f2 int);
2800 insert into foo values (1,2), (3,4);
2801 create or replace function footest() returns void as $$
2805 insert into foo values(5,6) returning * into x;
2806 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2807 end$$ language plpgsql;
2809 NOTICE: x.f1 = 5, x.f2 = 6
2815 create or replace function footest() returns void as $$
2818 -- should fail due to implicit strict
2819 insert into foo values(7,8),(9,10) returning * into x;
2820 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2821 end$$ language plpgsql;
2823 ERROR: query returned more than one row
2824 CONTEXT: PL/pgSQL function "footest" line 4 at SQL statement
2825 create or replace function footest() returns void as $$
2829 execute 'insert into foo values(5,6) returning *' into x;
2830 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2831 end$$ language plpgsql;
2833 NOTICE: x.f1 = 5, x.f2 = 6
2839 create or replace function footest() returns void as $$
2842 -- this should work since EXECUTE isn't as picky
2843 execute 'insert into foo values(7,8),(9,10) returning *' into x;
2844 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2845 end$$ language plpgsql;
2847 NOTICE: x.f1 = 7, x.f2 = 8
2864 create or replace function footest() returns void as $$
2868 select * from foo where f1 = 3 into strict x;
2869 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2870 end$$ language plpgsql;
2872 NOTICE: x.f1 = 3, x.f2 = 4
2878 create or replace function footest() returns void as $$
2881 -- should fail, no rows
2882 select * from foo where f1 = 0 into strict x;
2883 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2884 end$$ language plpgsql;
2886 ERROR: query returned no rows
2887 CONTEXT: PL/pgSQL function "footest" line 4 at SQL statement
2888 create or replace function footest() returns void as $$
2891 -- should fail, too many rows
2892 select * from foo where f1 > 3 into strict x;
2893 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2894 end$$ language plpgsql;
2896 ERROR: query returned more than one row
2897 CONTEXT: PL/pgSQL function "footest" line 4 at SQL statement
2898 create or replace function footest() returns void as $$
2902 execute 'select * from foo where f1 = 3' into strict x;
2903 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2904 end$$ language plpgsql;
2906 NOTICE: x.f1 = 3, x.f2 = 4
2912 create or replace function footest() returns void as $$
2915 -- should fail, no rows
2916 execute 'select * from foo where f1 = 0' into strict x;
2917 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2918 end$$ language plpgsql;
2920 ERROR: query returned no rows
2921 CONTEXT: PL/pgSQL function "footest" line 4 at EXECUTE statement
2922 create or replace function footest() returns void as $$
2925 -- should fail, too many rows
2926 execute 'select * from foo where f1 > 3' into strict x;
2927 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2928 end$$ language plpgsql;
2930 ERROR: query returned more than one row
2931 CONTEXT: PL/pgSQL function "footest" line 4 at EXECUTE statement
2932 drop function footest();
2933 -- test scrollable cursor support
2934 create function sc_test() returns setof integer as $$
2936 c scroll cursor for select f1 from int4_tbl;
2940 fetch last from c into x;
2943 fetch prior from c into x;
2947 $$ language plpgsql;
2948 select * from sc_test();
2958 create or replace function sc_test() returns setof integer as $$
2960 c no scroll cursor for select f1 from int4_tbl;
2964 fetch last from c into x;
2967 fetch prior from c into x;
2971 $$ language plpgsql;
2972 select * from sc_test(); -- fails because of NO SCROLL specification
2973 ERROR: cursor can only scan forward
2974 HINT: Declare it with SCROLL option to enable backward scan.
2975 CONTEXT: PL/pgSQL function "sc_test" line 6 at FETCH
2976 create or replace function sc_test() returns setof integer as $$
2981 open c scroll for select f1 from int4_tbl;
2982 fetch last from c into x;
2985 fetch prior from c into x;
2989 $$ language plpgsql;
2990 select * from sc_test();
3000 create or replace function sc_test() returns setof integer as $$
3005 open c scroll for execute 'select f1 from int4_tbl';
3006 fetch last from c into x;
3009 fetch relative -2 from c into x;
3013 $$ language plpgsql;
3014 select * from sc_test();
3022 create or replace function sc_test() returns setof integer as $$
3027 open c scroll for execute 'select f1 from int4_tbl';
3028 fetch last from c into x;
3031 move backward 2 from c;
3032 fetch relative -1 from c into x;
3036 $$ language plpgsql;
3037 select * from sc_test();
3044 create or replace function sc_test() returns setof integer as $$
3046 c cursor for select * from generate_series(1, 10);
3051 move relative 2 in c;
3055 fetch next from c into x;
3062 $$ language plpgsql;
3063 select * from sc_test();
3071 create or replace function sc_test() returns setof integer as $$
3073 c cursor for select * from generate_series(1, 10);
3077 move forward all in c;
3078 fetch backward from c into x;
3084 $$ language plpgsql;
3085 select * from sc_test();
3091 drop function sc_test();
3092 -- test qualified variable names
3093 create function pl_qual_names (param1 int) returns void as $$
3102 raise notice 'param1 = %', param1;
3103 raise notice 'pl_qual_names.param1 = %', pl_qual_names.param1;
3104 raise notice 'outerblock.param1 = %', outerblock.param1;
3105 raise notice 'innerblock.param1 = %', innerblock.param1;
3108 $$ language plpgsql;
3109 select pl_qual_names(42);
3111 NOTICE: pl_qual_names.param1 = 42
3112 NOTICE: outerblock.param1 = 1
3113 NOTICE: innerblock.param1 = 2
3119 drop function pl_qual_names(int);
3120 -- tests for RETURN QUERY
3121 create function ret_query1(out int, out int) returns setof record as $$
3126 return query select x + 1, x * 10 from generate_series(0, 10) s (x);
3129 $$ language plpgsql;
3130 select * from ret_query1();
3148 create type record_type as (x text, y int, z boolean);
3149 create or replace function ret_query2(lim int) returns setof record_type as $$
3151 return query select md5(s.x::text), s.x, s.x > 0
3152 from generate_series(-8, lim) s (x) where s.x % 2 = 0;
3154 $$ language plpgsql;
3155 select * from ret_query2(8);
3157 ----------------------------------+----+---
3158 a8d2ec85eaf98407310b72eb73dda247 | -8 | f
3159 596a3d04481816330f07e4f97510c28f | -6 | f
3160 0267aaf632e87a63288a08331f22c7c3 | -4 | f
3161 5d7b9adcbe1c629ec722529dd12e5129 | -2 | f
3162 cfcd208495d565ef66e7dff9f98764da | 0 | f
3163 c81e728d9d4c2f636f067f89cc14862c | 2 | t
3164 a87ff679a2f3e71d9181a67b7542122c | 4 | t
3165 1679091c5a880faf6fb5e6087eb1b2dc | 6 | t
3166 c9f0f895fb98ab9159f51fd0297e236d | 8 | t
3169 -- test EXECUTE USING
3170 create function exc_using(int, text) returns int as $$
3173 for i in execute 'select * from generate_series(1,$1)' using $1+1 loop
3174 raise notice '%', i;
3176 execute 'select $2 + $2*3 + length($1)' into i using $2,$1;
3179 $$ language plpgsql;
3180 select exc_using(5, 'foobar');
3192 drop function exc_using(int, text);
3193 create or replace function exc_using(int) returns void as $$
3198 open c for execute 'select * from generate_series(1,$1)' using $1+1;
3201 exit when not found;
3202 raise notice '%', i;
3207 $$ language plpgsql;
3208 select exc_using(5);
3220 drop function exc_using(int);
3221 -- test FOR-over-cursor
3222 create or replace function forc01() returns void as $$
3224 c cursor(r1 integer, r2 integer)
3225 for select * from generate_series(r1,r2) i;
3227 for select * from generate_series(41,43) i;
3229 for r in c(5,7) loop
3230 raise notice '% from %', r.i, c;
3232 -- again, to test if cursor was closed properly
3233 for r in c(9,10) loop
3234 raise notice '% from %', r.i, c;
3236 -- and test a parameterless cursor
3238 raise notice '% from %', r.i, c2;
3240 -- and try it with a hand-assigned name
3241 raise notice 'after loop, c2 = %', c2;
3242 c2 := 'special_name';
3244 raise notice '% from %', r.i, c2;
3246 raise notice 'after loop, c2 = %', c2;
3247 -- and try it with a generated name
3248 -- (which we can't show in the output because it's variable)
3251 raise notice '%', r.i;
3253 raise notice 'after loop, c2 = %', c2;
3256 $$ language plpgsql;
3266 NOTICE: after loop, c2 = c2
3267 NOTICE: 41 from special_name
3268 NOTICE: 42 from special_name
3269 NOTICE: 43 from special_name
3270 NOTICE: after loop, c2 = special_name
3274 NOTICE: after loop, c2 = <NULL>
3280 -- try updating the cursor's current row
3281 create temp table forc_test as
3282 select n as i, n as j from generate_series(1,10) n;
3283 create or replace function forc01() returns void as $$
3285 c cursor for select * from forc_test;
3288 raise notice '%, %', r.i, r.j;
3289 update forc_test set i = i * 100, j = r.j * 2 where current of c;
3292 $$ language plpgsql;
3309 select * from forc_test;
3324 -- same, with a cursor whose portal name doesn't match variable name
3325 create or replace function forc01() returns void as $$
3327 c refcursor := 'fooled_ya';
3330 open c for select * from forc_test;
3333 exit when not found;
3334 raise notice '%, %', r.i, r.j;
3335 update forc_test set i = i * 100, j = r.j * 2 where current of c;
3338 $$ language plpgsql;
3355 select * from forc_test;
3370 drop function forc01();
3371 -- fail because cursor has no query bound to it
3372 create or replace function forc_bad() returns void as $$
3377 raise notice '%', r.i;
3380 $$ language plpgsql;
3381 ERROR: cursor FOR loop must use a bound cursor variable
3382 LINE 5: for r in c loop
3384 -- test RETURN QUERY EXECUTE
3385 create or replace function return_dquery()
3386 returns setof int as $$
3388 return query execute 'select * from (values(10),(20)) f';
3389 return query execute 'select * from (values($1),($2)) f' using 40,50;
3391 $$ language plpgsql;
3392 select * from return_dquery();
3401 drop function return_dquery();
3402 -- test RETURN QUERY with dropped columns
3403 create table tabwithcols(a int, b int, c int, d int);
3404 insert into tabwithcols values(10,20,30,40),(50,60,70,80);
3405 create or replace function returnqueryf()
3406 returns setof tabwithcols as $$
3408 return query select * from tabwithcols;
3409 return query execute 'select * from tabwithcols';
3411 $$ language plpgsql;
3412 select * from returnqueryf();
3421 alter table tabwithcols drop column b;
3422 select * from returnqueryf();
3431 alter table tabwithcols drop column d;
3432 select * from returnqueryf();
3441 alter table tabwithcols add column d int;
3442 select * from returnqueryf();
3451 drop function returnqueryf();
3452 drop table tabwithcols;
3453 -- Tests for 8.4's new RAISE features
3454 create or replace function raise_test() returns void as $$
3456 raise notice '% % %', 1, 2, 3
3457 using errcode = '55001', detail = 'some detail info', hint = 'some hint';
3458 raise '% % %', 1, 2, 3
3459 using errcode = 'division_by_zero', detail = 'some detail info';
3461 $$ language plpgsql;
3462 select raise_test();
3464 DETAIL: some detail info
3467 DETAIL: some detail info
3468 -- Since we can't actually see the thrown SQLSTATE in default psql output,
3469 -- test it like this; this also tests re-RAISE
3470 create or replace function raise_test() returns void as $$
3473 using errcode = 'division_by_zero', detail = 'some detail info';
3476 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3479 $$ language plpgsql;
3480 select raise_test();
3481 NOTICE: SQLSTATE: 22012 SQLERRM: check me
3483 DETAIL: some detail info
3484 create or replace function raise_test() returns void as $$
3487 using errcode = '1234F', detail = 'some detail info';
3490 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3493 $$ language plpgsql;
3494 select raise_test();
3495 NOTICE: SQLSTATE: 1234F SQLERRM: check me
3497 DETAIL: some detail info
3498 -- SQLSTATE specification in WHEN
3499 create or replace function raise_test() returns void as $$
3502 using errcode = '1234F', detail = 'some detail info';
3504 when sqlstate '1234F' then
3505 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3508 $$ language plpgsql;
3509 select raise_test();
3510 NOTICE: SQLSTATE: 1234F SQLERRM: check me
3512 DETAIL: some detail info
3513 create or replace function raise_test() returns void as $$
3515 raise division_by_zero using detail = 'some detail info';
3518 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3521 $$ language plpgsql;
3522 select raise_test();
3523 NOTICE: SQLSTATE: 22012 SQLERRM: division_by_zero
3524 ERROR: division_by_zero
3525 DETAIL: some detail info
3526 create or replace function raise_test() returns void as $$
3528 raise division_by_zero;
3530 $$ language plpgsql;
3531 select raise_test();
3532 ERROR: division_by_zero
3533 create or replace function raise_test() returns void as $$
3535 raise sqlstate '1234F';
3537 $$ language plpgsql;
3538 select raise_test();
3540 create or replace function raise_test() returns void as $$
3542 raise division_by_zero using message = 'custom' || ' message';
3544 $$ language plpgsql;
3545 select raise_test();
3546 ERROR: custom message
3547 create or replace function raise_test() returns void as $$
3549 raise using message = 'custom' || ' message', errcode = '22012';
3551 $$ language plpgsql;
3552 select raise_test();
3553 ERROR: custom message
3554 -- conflict on message
3555 create or replace function raise_test() returns void as $$
3557 raise notice 'some message' using message = 'custom' || ' message', errcode = '22012';
3559 $$ language plpgsql;
3560 select raise_test();
3561 ERROR: RAISE option already specified: MESSAGE
3562 CONTEXT: PL/pgSQL function "raise_test" line 2 at RAISE
3563 -- conflict on errcode
3564 create or replace function raise_test() returns void as $$
3566 raise division_by_zero using message = 'custom' || ' message', errcode = '22012';
3568 $$ language plpgsql;
3569 select raise_test();
3570 ERROR: RAISE option already specified: ERRCODE
3571 CONTEXT: PL/pgSQL function "raise_test" line 2 at RAISE
3572 -- nothing to re-RAISE
3573 create or replace function raise_test() returns void as $$
3577 $$ language plpgsql;
3578 select raise_test();
3579 ERROR: RAISE without parameters cannot be used outside an exception handler
3580 CONTEXT: PL/pgSQL function "raise_test"
3581 -- check cases where implicit SQLSTATE variable could be confused with
3582 -- SQLSTATE as a keyword, cf bug #5524
3583 create or replace function raise_test() returns void as $$
3587 when sqlstate '22012' then
3588 raise notice using message = sqlstate;
3589 raise sqlstate '22012' using message = 'substitute message';
3591 $$ language plpgsql;
3592 select raise_test();
3594 ERROR: substitute message
3595 drop function raise_test();
3596 -- test CASE statement
3597 create or replace function case_test(bigint) returns text as $$
3607 return 'three, four or eight';
3610 when a+b, a+b+1 then
3611 return 'eleven, twelve';
3614 $$ language plpgsql immutable;
3615 select case_test(1);
3621 select case_test(2);
3627 select case_test(3);
3629 ----------------------
3630 three, four or eight
3633 select case_test(4);
3635 ----------------------
3636 three, four or eight
3639 select case_test(5); -- fails
3640 ERROR: case not found
3641 HINT: CASE statement is missing ELSE part.
3642 CONTEXT: PL/pgSQL function "case_test" line 4 at CASE
3643 select case_test(8);
3645 ----------------------
3646 three, four or eight
3649 select case_test(10);
3655 select case_test(11);
3661 select case_test(12);
3667 select case_test(13); -- fails
3668 ERROR: case not found
3669 HINT: CASE statement is missing ELSE part.
3670 CONTEXT: PL/pgSQL function "case_test" line 4 at CASE
3671 create or replace function catch() returns void as $$
3673 raise notice '%', case_test(6);
3675 when case_not_found then
3676 raise notice 'caught case_not_found % %', SQLSTATE, SQLERRM;
3678 $$ language plpgsql;
3680 NOTICE: caught case_not_found 20000 case not found
3686 -- test the searched variant too, as well as ELSE
3687 create or replace function case_test(bigint) returns text as $$
3693 when $1 = a + 2 then
3699 $$ language plpgsql immutable;
3700 select case_test(1);
3706 select case_test(2);
3712 select case_test(12);
3718 select case_test(13);
3724 drop function catch();
3725 drop function case_test(bigint);
3726 -- test variadic functions
3727 create or replace function vari(variadic int[])
3730 for i in array_lower($1,1)..array_upper($1,1) loop
3731 raise notice '%', $1[i];
3733 $$ language plpgsql;
3734 select vari(1,2,3,4,5);
3754 select vari(variadic array[5,6,7]);
3763 drop function vari(int[]);
3765 create or replace function pleast(variadic numeric[])
3766 returns numeric as $$
3767 declare aux numeric = $1[array_lower($1,1)];
3769 for i in array_lower($1,1)+1..array_upper($1,1) loop
3770 if $1[i] < aux then aux := $1[i]; end if;
3774 $$ language plpgsql immutable strict;
3775 select pleast(10,1,2,3,-16);
3781 select pleast(10.2,2.2,-1.1);
3787 select pleast(10.2,10, -20);
3793 select pleast(10,20, -1.0);
3799 -- in case of conflict, non-variadic version is preferred
3800 create or replace function pleast(numeric)
3801 returns numeric as $$
3803 raise notice 'non-variadic function called';
3806 $$ language plpgsql immutable strict;
3808 NOTICE: non-variadic function called
3814 drop function pleast(numeric[]);
3815 drop function pleast(numeric);
3816 -- test table functions
3817 create function tftest(int) returns table(a int, b int) as $$
3819 return query select $1, $1+i from generate_series(1,5) g(i);
3821 $$ language plpgsql immutable strict;
3822 select * from tftest(10);
3832 create or replace function tftest(a1 int) returns table(a int, b int) as $$
3834 a := a1; b := a1 + 1;
3836 a := a1 * 10; b := a1 * 10 + 1;
3839 $$ language plpgsql immutable strict;
3840 select * from tftest(10);
3847 drop function tftest(int);
3848 create or replace function rttest()
3849 returns setof int as $$
3852 return query values(10),(20);
3853 get diagnostics rc = row_count;
3854 raise notice '% %', found, rc;
3855 return query select * from (values(10),(20)) f(a) where false;
3856 get diagnostics rc = row_count;
3857 raise notice '% %', found, rc;
3858 return query execute 'values(10),(20)';
3859 get diagnostics rc = row_count;
3860 raise notice '% %', found, rc;
3861 return query execute 'select * from (values(10),(20)) f(a) where false';
3862 get diagnostics rc = row_count;
3863 raise notice '% %', found, rc;
3865 $$ language plpgsql;
3866 select * from rttest();
3879 drop function rttest();
3880 -- Test for proper cleanup at subtransaction exit. This example
3881 -- exposed a bug in PG 8.2.
3882 CREATE FUNCTION leaker_1(fail BOOL) RETURNS INTEGER AS $$
3887 v_var := (leaker_2(fail)).error_code;
3889 WHEN others THEN RETURN 0;
3893 $$ LANGUAGE plpgsql;
3894 CREATE FUNCTION leaker_2(fail BOOL, OUT error_code INTEGER, OUT new_id INTEGER)
3895 RETURNS RECORD AS $$
3898 RAISE EXCEPTION 'fail ...';
3904 $$ LANGUAGE plpgsql;
3905 SELECT * FROM leaker_1(false);
3911 SELECT * FROM leaker_1(true);
3917 DROP FUNCTION leaker_1(bool);
3918 DROP FUNCTION leaker_2(bool);
3919 -- Test for appropriate cleanup of non-simple expression evaluations
3920 -- (bug in all versions prior to August 2010)
3921 CREATE FUNCTION nonsimple_expr_test() RETURNS text[] AS $$
3927 arr := array[array['foo','bar'], array['baz', 'quux']];
3930 -- use sub-SELECTs to make expressions non-simple
3931 arr[(SELECT i)][(SELECT i+1)] := (SELECT lr);
3934 $$ LANGUAGE plpgsql;
3935 SELECT nonsimple_expr_test();
3937 -------------------------
3938 {{foo,fool},{baz,quux}}
3941 DROP FUNCTION nonsimple_expr_test();
3942 CREATE FUNCTION nonsimple_expr_test() RETURNS integer AS $$
3944 i integer NOT NULL := 0;
3947 i := (SELECT NULL::integer); -- should throw error
3950 i := (SELECT 1::integer);
3954 $$ LANGUAGE plpgsql;
3955 SELECT nonsimple_expr_test();
3957 ---------------------
3961 DROP FUNCTION nonsimple_expr_test();
3963 -- Test cases involving recursion and error recovery in simple expressions
3964 -- (bugs in all versions before October 2010). The problems are most
3965 -- easily exposed by mutual recursion between plpgsql and sql functions.
3967 create function recurse(float8) returns float8 as
3971 return sql_recurse($1 + 1);
3976 $$ language plpgsql;
3977 -- "limit" is to prevent this from being inlined
3978 create function sql_recurse(float8) returns float8 as
3979 $$ select recurse($1) limit 1; $$ language sql;
3986 create function error1(text) returns text language sql as
3987 $$ SELECT relname::text FROM pg_class c WHERE c.oid = $1::regclass $$;
3988 create function error2(p_name_table text) returns text language plpgsql as $$
3990 return error1(p_name_table);
3993 create table public.stuffs (stuff text);
3995 select error2('nonexistent.stuffs');
3996 ERROR: schema "nonexistent" does not exist
3997 CONTEXT: SQL function "error1" statement 1
3998 PL/pgSQL function "error2" line 2 at RETURN
4000 select error2('public.stuffs');
4007 drop function error2(p_name_table text);
4008 drop function error1(text);
4009 -- Test handling of string literals.
4010 set standard_conforming_strings = off;
4011 create or replace function strtest() returns text as $$
4013 raise notice 'foo\\bar\041baz';
4014 return 'foo\\bar\041baz';
4016 $$ language plpgsql;
4017 WARNING: nonstandard use of \\ in a string literal
4018 LINE 3: raise notice 'foo\\bar\041baz';
4020 HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
4021 WARNING: nonstandard use of \\ in a string literal
4022 LINE 4: return 'foo\\bar\041baz';
4024 HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
4025 WARNING: nonstandard use of \\ in a string literal
4026 LINE 4: return 'foo\\bar\041baz';
4028 HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
4031 WARNING: nonstandard use of \\ in a string literal
4032 LINE 1: SELECT 'foo\\bar\041baz'
4034 HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
4035 QUERY: SELECT 'foo\\bar\041baz'
4036 CONTEXT: PL/pgSQL function "strtest" line 3 at RETURN
4042 create or replace function strtest() returns text as $$
4044 raise notice E'foo\\bar\041baz';
4045 return E'foo\\bar\041baz';
4047 $$ language plpgsql;
4055 set standard_conforming_strings = on;
4056 create or replace function strtest() returns text as $$
4058 raise notice 'foo\\bar\041baz\';
4059 return 'foo\\bar\041baz\';
4061 $$ language plpgsql;
4063 NOTICE: foo\\bar\041baz\
4069 create or replace function strtest() returns text as $$
4071 raise notice E'foo\\bar\041baz';
4072 return E'foo\\bar\041baz';
4074 $$ language plpgsql;
4082 drop function strtest();
4083 -- Test anonymous code blocks.
4087 FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno
4089 RAISE NOTICE '%, %', r.roomno, r.comment;
4092 NOTICE: 001, Entrance
4095 NOTICE: 004, Technical
4097 NOTICE: 102, Conference
4098 NOTICE: 103, Restroom
4099 NOTICE: 104, Technical
4102 -- these are to check syntax error reporting
4103 DO LANGUAGE plpgsql $$begin return 1; end$$;
4104 ERROR: RETURN cannot have a parameter in function returning void
4105 LINE 1: DO LANGUAGE plpgsql $$begin return 1; end$$;
4110 FOR r IN SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno
4112 RAISE NOTICE '%, %', r.roomno, r.comment;
4115 ERROR: column "foo" does not exist
4116 LINE 1: SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomn...
4118 QUERY: SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno
4119 CONTEXT: PL/pgSQL function "inline_code_block" line 3 at FOR over SELECT rows
4120 -- Check variable scoping -- a var is not available in its own or prior
4121 -- default expressions.
4122 create function scope_test() returns int as $$
4123 declare x int := 42;
4125 declare y int := x + 1;
4131 $$ language plpgsql;
4132 select scope_test();
4138 drop function scope_test();
4139 -- Check handling of conflicts between plpgsql vars and table columns.
4140 set plpgsql.variable_conflict = error;
4141 create function conflict_test() returns setof int8_tbl as $$
4145 for r in select q1,q2 from int8_tbl loop
4149 $$ language plpgsql;
4150 select * from conflict_test();
4151 ERROR: column reference "q1" is ambiguous
4152 LINE 1: select q1,q2 from int8_tbl
4154 DETAIL: It could refer to either a PL/pgSQL variable or a table column.
4155 QUERY: select q1,q2 from int8_tbl
4156 CONTEXT: PL/pgSQL function "conflict_test" line 4 at FOR over SELECT rows
4157 create or replace function conflict_test() returns setof int8_tbl as $$
4158 #variable_conflict use_variable
4162 for r in select q1,q2 from int8_tbl loop
4166 $$ language plpgsql;
4167 select * from conflict_test();
4169 ----+-------------------
4171 42 | 4567890123456789
4173 42 | 4567890123456789
4174 42 | -4567890123456789
4177 create or replace function conflict_test() returns setof int8_tbl as $$
4178 #variable_conflict use_column
4182 for r in select q1,q2 from int8_tbl loop
4186 $$ language plpgsql;
4187 select * from conflict_test();
4189 ------------------+-------------------
4191 123 | 4567890123456789
4192 4567890123456789 | 123
4193 4567890123456789 | 4567890123456789
4194 4567890123456789 | -4567890123456789
4197 drop function conflict_test();
4198 -- Check that an unreserved keyword can be used as a variable name
4199 create function unreserved_test() returns int as $$
4203 forward := forward * 2;
4206 $$ language plpgsql;
4207 select unreserved_test();
4213 drop function unreserved_test();