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
30 create unique index Room_rno on Room using btree (roomno bpchar_ops);
40 create unique index WSlot_name on WSlot using btree (slotname bpchar_ops);
48 create unique index PField_name on PField using btree (name text_ops);
58 create unique index PSlot_name on PSlot using btree (slotname bpchar_ops);
68 create unique index PLine_name on PLine using btree (slotname bpchar_ops);
77 create unique index Hub_name on Hub using btree (name bpchar_ops);
87 create unique index HSlot_name on HSlot using btree (slotname bpchar_ops);
88 create index HSlot_hubname on HSlot using btree (hubname bpchar_ops);
96 create unique index System_name on System using btree (name text_ops);
106 create unique index IFace_name on IFace using btree (slotname bpchar_ops);
115 create unique index PHone_name on PHone using btree (slotname bpchar_ops);
118 -- ************************************************************
120 -- * Trigger procedures and functions for the patchfield
121 -- * test of PL/pgSQL
123 -- ************************************************************
126 -- ************************************************************
127 -- * AFTER UPDATE on Room
128 -- * - If room no changes let wall slots follow
129 -- ************************************************************
130 create function tg_room_au() returns trigger as '
132 if new.roomno != old.roomno then
133 update WSlot set roomno = new.roomno where roomno = old.roomno;
139 create trigger tg_room_au after update
140 on Room for each row execute procedure tg_room_au();
143 -- ************************************************************
144 -- * AFTER DELETE on Room
145 -- * - delete wall slots in this room
146 -- ************************************************************
147 create function tg_room_ad() returns trigger as '
149 delete from WSlot where roomno = old.roomno;
154 create trigger tg_room_ad after delete
155 on Room for each row execute procedure tg_room_ad();
158 -- ************************************************************
159 -- * BEFORE INSERT or UPDATE on WSlot
160 -- * - Check that room exists
161 -- ************************************************************
162 create function tg_wslot_biu() returns trigger as $$
164 if count(*) = 0 from Room where roomno = new.roomno then
165 raise exception 'Room % does not exist', new.roomno;
171 create trigger tg_wslot_biu before insert or update
172 on WSlot for each row execute procedure tg_wslot_biu();
175 -- ************************************************************
176 -- * AFTER UPDATE on PField
177 -- * - Let PSlots of this field follow
178 -- ************************************************************
179 create function tg_pfield_au() returns trigger as '
181 if new.name != old.name then
182 update PSlot set pfname = new.name where pfname = old.name;
188 create trigger tg_pfield_au after update
189 on PField for each row execute procedure tg_pfield_au();
192 -- ************************************************************
193 -- * AFTER DELETE on PField
194 -- * - Remove all slots of this patchfield
195 -- ************************************************************
196 create function tg_pfield_ad() returns trigger as '
198 delete from PSlot where pfname = old.name;
203 create trigger tg_pfield_ad after delete
204 on PField for each row execute procedure tg_pfield_ad();
207 -- ************************************************************
208 -- * BEFORE INSERT or UPDATE on PSlot
209 -- * - Ensure that our patchfield does exist
210 -- ************************************************************
211 create function tg_pslot_biu() returns trigger as $proc$
216 select into pfrec * from PField where name = ps.pfname;
218 raise exception $$Patchfield "%" does not exist$$, ps.pfname;
222 $proc$ language plpgsql;
224 create trigger tg_pslot_biu before insert or update
225 on PSlot for each row execute procedure tg_pslot_biu();
228 -- ************************************************************
229 -- * AFTER UPDATE on System
230 -- * - If system name changes let interfaces follow
231 -- ************************************************************
232 create function tg_system_au() returns trigger as '
234 if new.name != old.name then
235 update IFace set sysname = new.name where sysname = old.name;
241 create trigger tg_system_au after update
242 on System for each row execute procedure tg_system_au();
245 -- ************************************************************
246 -- * BEFORE INSERT or UPDATE on IFace
247 -- * - set the slotname to IF.sysname.ifname
248 -- ************************************************************
249 create function tg_iface_biu() returns trigger as $$
254 select into sysrec * from system where name = new.sysname;
256 raise exception $q$system "%" does not exist$q$, new.sysname;
258 sname := 'IF.' || new.sysname;
259 sname := sname || '.';
260 sname := sname || new.ifname;
261 if length(sname) > 20 then
262 raise exception 'IFace slotname "%" too long (20 char max)', sname;
264 new.slotname := sname;
269 create trigger tg_iface_biu before insert or update
270 on IFace for each row execute procedure tg_iface_biu();
273 -- ************************************************************
274 -- * AFTER INSERT or UPDATE or DELETE on Hub
275 -- * - insert/delete/rename slots as required
276 -- ************************************************************
277 create function tg_hub_a() returns trigger as '
282 if tg_op = ''INSERT'' then
283 dummy := tg_hub_adjustslots(new.name, 0, new.nslots);
286 if tg_op = ''UPDATE'' then
287 if new.name != old.name then
288 update HSlot set hubname = new.name where hubname = old.name;
290 dummy := tg_hub_adjustslots(new.name, old.nslots, new.nslots);
293 if tg_op = ''DELETE'' then
294 dummy := tg_hub_adjustslots(old.name, old.nslots, 0);
300 create trigger tg_hub_a after insert or update or delete
301 on Hub for each row execute procedure tg_hub_a();
304 -- ************************************************************
305 -- * Support function to add/remove slots of Hub
306 -- ************************************************************
307 create function tg_hub_adjustslots(hname bpchar,
312 if newnslots = oldnslots then
315 if newnslots < oldnslots then
316 delete from HSlot where hubname = hname and slotno > newnslots;
319 for i in oldnslots + 1 .. newnslots loop
320 insert into HSlot (slotname, hubname, slotno, slotlink)
321 values (''HS.dummy'', hname, i, '''');
328 COMMENT ON FUNCTION tg_hub_adjustslots_wrong(bpchar, integer, integer) IS 'function with args';
329 COMMENT ON FUNCTION tg_hub_adjustslots(bpchar, integer, integer) IS 'function with args';
330 COMMENT ON FUNCTION tg_hub_adjustslots(bpchar, integer, integer) IS NULL;
332 -- ************************************************************
333 -- * BEFORE INSERT or UPDATE on HSlot
334 -- * - prevent from manual manipulation
335 -- * - set the slotname to HS.hubname.slotno
336 -- ************************************************************
337 create function tg_hslot_biu() returns trigger as '
340 xname HSlot.slotname%TYPE;
343 select into hubrec * from Hub where name = new.hubname;
345 raise exception ''no manual manipulation of HSlot'';
347 if new.slotno < 1 or new.slotno > hubrec.nslots then
348 raise exception ''no manual manipulation of HSlot'';
350 if tg_op = ''UPDATE'' and new.hubname != old.hubname then
351 if count(*) > 0 from Hub where name = old.hubname then
352 raise exception ''no manual manipulation of HSlot'';
355 sname := ''HS.'' || trim(new.hubname);
356 sname := sname || ''.'';
357 sname := sname || new.slotno::text;
358 if length(sname) > 20 then
359 raise exception ''HSlot slotname "%" too long (20 char max)'', sname;
361 new.slotname := sname;
366 create trigger tg_hslot_biu before insert or update
367 on HSlot for each row execute procedure tg_hslot_biu();
370 -- ************************************************************
371 -- * BEFORE DELETE on HSlot
372 -- * - prevent from manual manipulation
373 -- ************************************************************
374 create function tg_hslot_bd() returns trigger as '
378 select into hubrec * from Hub where name = old.hubname;
382 if old.slotno > hubrec.nslots then
385 raise exception ''no manual manipulation of HSlot'';
389 create trigger tg_hslot_bd before delete
390 on HSlot for each row execute procedure tg_hslot_bd();
393 -- ************************************************************
394 -- * BEFORE INSERT on all slots
395 -- * - Check name prefix
396 -- ************************************************************
397 create function tg_chkslotname() returns trigger as '
399 if substr(new.slotname, 1, 2) != tg_argv[0] then
400 raise exception ''slotname must begin with %'', tg_argv[0];
406 create trigger tg_chkslotname before insert
407 on PSlot for each row execute procedure tg_chkslotname('PS');
409 create trigger tg_chkslotname before insert
410 on WSlot for each row execute procedure tg_chkslotname('WS');
412 create trigger tg_chkslotname before insert
413 on PLine for each row execute procedure tg_chkslotname('PL');
415 create trigger tg_chkslotname before insert
416 on IFace for each row execute procedure tg_chkslotname('IF');
418 create trigger tg_chkslotname before insert
419 on PHone for each row execute procedure tg_chkslotname('PH');
422 -- ************************************************************
423 -- * BEFORE INSERT or UPDATE on all slots with slotlink
424 -- * - Set slotlink to empty string if NULL value given
425 -- ************************************************************
426 create function tg_chkslotlink() returns trigger as '
428 if new.slotlink isnull then
429 new.slotlink := '''';
435 create trigger tg_chkslotlink before insert or update
436 on PSlot for each row execute procedure tg_chkslotlink();
438 create trigger tg_chkslotlink before insert or update
439 on WSlot for each row execute procedure tg_chkslotlink();
441 create trigger tg_chkslotlink before insert or update
442 on IFace for each row execute procedure tg_chkslotlink();
444 create trigger tg_chkslotlink before insert or update
445 on HSlot for each row execute procedure tg_chkslotlink();
447 create trigger tg_chkslotlink before insert or update
448 on PHone for each row execute procedure tg_chkslotlink();
451 -- ************************************************************
452 -- * BEFORE INSERT or UPDATE on all slots with backlink
453 -- * - Set backlink to empty string if NULL value given
454 -- ************************************************************
455 create function tg_chkbacklink() returns trigger as '
457 if new.backlink isnull then
458 new.backlink := '''';
464 create trigger tg_chkbacklink before insert or update
465 on PSlot for each row execute procedure tg_chkbacklink();
467 create trigger tg_chkbacklink before insert or update
468 on WSlot for each row execute procedure tg_chkbacklink();
470 create trigger tg_chkbacklink before insert or update
471 on PLine for each row execute procedure tg_chkbacklink();
474 -- ************************************************************
475 -- * BEFORE UPDATE on PSlot
476 -- * - do delete/insert instead of update if name changes
477 -- ************************************************************
478 create function tg_pslot_bu() returns trigger as '
480 if new.slotname != old.slotname then
481 delete from PSlot where slotname = old.slotname;
499 create trigger tg_pslot_bu before update
500 on PSlot for each row execute procedure tg_pslot_bu();
503 -- ************************************************************
504 -- * BEFORE UPDATE on WSlot
505 -- * - do delete/insert instead of update if name changes
506 -- ************************************************************
507 create function tg_wslot_bu() returns trigger as '
509 if new.slotname != old.slotname then
510 delete from WSlot where slotname = old.slotname;
528 create trigger tg_wslot_bu before update
529 on WSlot for each row execute procedure tg_Wslot_bu();
532 -- ************************************************************
533 -- * BEFORE UPDATE on PLine
534 -- * - do delete/insert instead of update if name changes
535 -- ************************************************************
536 create function tg_pline_bu() returns trigger as '
538 if new.slotname != old.slotname then
539 delete from PLine where slotname = old.slotname;
557 create trigger tg_pline_bu before update
558 on PLine for each row execute procedure tg_pline_bu();
561 -- ************************************************************
562 -- * BEFORE UPDATE on IFace
563 -- * - do delete/insert instead of update if name changes
564 -- ************************************************************
565 create function tg_iface_bu() returns trigger as '
567 if new.slotname != old.slotname then
568 delete from IFace where slotname = old.slotname;
586 create trigger tg_iface_bu before update
587 on IFace for each row execute procedure tg_iface_bu();
590 -- ************************************************************
591 -- * BEFORE UPDATE on HSlot
592 -- * - do delete/insert instead of update if name changes
593 -- ************************************************************
594 create function tg_hslot_bu() returns trigger as '
596 if new.slotname != old.slotname or new.hubname != old.hubname then
597 delete from HSlot where slotname = old.slotname;
615 create trigger tg_hslot_bu before update
616 on HSlot for each row execute procedure tg_hslot_bu();
619 -- ************************************************************
620 -- * BEFORE UPDATE on PHone
621 -- * - do delete/insert instead of update if name changes
622 -- ************************************************************
623 create function tg_phone_bu() returns trigger as '
625 if new.slotname != old.slotname then
626 delete from PHone where slotname = old.slotname;
642 create trigger tg_phone_bu before update
643 on PHone for each row execute procedure tg_phone_bu();
646 -- ************************************************************
647 -- * AFTER INSERT or UPDATE or DELETE on slot with backlink
648 -- * - Ensure that the opponent correctly points back to us
649 -- ************************************************************
650 create function tg_backlink_a() returns trigger as '
654 if tg_op = ''INSERT'' then
655 if new.backlink != '''' then
656 dummy := tg_backlink_set(new.backlink, new.slotname);
660 if tg_op = ''UPDATE'' then
661 if new.backlink != old.backlink then
662 if old.backlink != '''' then
663 dummy := tg_backlink_unset(old.backlink, old.slotname);
665 if new.backlink != '''' then
666 dummy := tg_backlink_set(new.backlink, new.slotname);
669 if new.slotname != old.slotname and new.backlink != '''' then
670 dummy := tg_slotlink_set(new.backlink, new.slotname);
675 if tg_op = ''DELETE'' then
676 if old.backlink != '''' then
677 dummy := tg_backlink_unset(old.backlink, old.slotname);
685 create trigger tg_backlink_a after insert or update or delete
686 on PSlot for each row execute procedure tg_backlink_a('PS');
688 create trigger tg_backlink_a after insert or update or delete
689 on WSlot for each row execute procedure tg_backlink_a('WS');
691 create trigger tg_backlink_a after insert or update or delete
692 on PLine for each row execute procedure tg_backlink_a('PL');
695 -- ************************************************************
696 -- * Support function to set the opponents backlink field
697 -- * if it does not already point to the requested slot
698 -- ************************************************************
699 create function tg_backlink_set(myname bpchar, blname bpchar)
706 mytype := substr(myname, 1, 2);
707 link := mytype || substr(blname, 1, 2);
708 if link = ''PLPL'' then
710 ''backlink between two phone lines does not make sense'';
712 if link in (''PLWS'', ''WSPL'') then
714 ''direct link of phone line to wall slot not permitted'';
716 if mytype = ''PS'' then
717 select into rec * from PSlot where slotname = myname;
719 raise exception ''% does not exist'', myname;
721 if rec.backlink != blname then
722 update PSlot set backlink = blname where slotname = myname;
726 if mytype = ''WS'' then
727 select into rec * from WSlot where slotname = myname;
729 raise exception ''% does not exist'', myname;
731 if rec.backlink != blname then
732 update WSlot set backlink = blname where slotname = myname;
736 if mytype = ''PL'' then
737 select into rec * from PLine where slotname = myname;
739 raise exception ''% does not exist'', myname;
741 if rec.backlink != blname then
742 update PLine set backlink = blname where slotname = myname;
746 raise exception ''illegal backlink beginning with %'', mytype;
751 -- ************************************************************
752 -- * Support function to clear out the backlink field if
753 -- * it still points to specific slot
754 -- ************************************************************
755 create function tg_backlink_unset(bpchar, bpchar)
763 mytype := substr(myname, 1, 2);
764 if mytype = ''PS'' then
765 select into rec * from PSlot where slotname = myname;
769 if rec.backlink = blname then
770 update PSlot set backlink = '''' where slotname = myname;
774 if mytype = ''WS'' then
775 select into rec * from WSlot where slotname = myname;
779 if rec.backlink = blname then
780 update WSlot set backlink = '''' where slotname = myname;
784 if mytype = ''PL'' then
785 select into rec * from PLine where slotname = myname;
789 if rec.backlink = blname then
790 update PLine set backlink = '''' where slotname = myname;
798 -- ************************************************************
799 -- * AFTER INSERT or UPDATE or DELETE on slot with slotlink
800 -- * - Ensure that the opponent correctly points back to us
801 -- ************************************************************
802 create function tg_slotlink_a() returns trigger as '
806 if tg_op = ''INSERT'' then
807 if new.slotlink != '''' then
808 dummy := tg_slotlink_set(new.slotlink, new.slotname);
812 if tg_op = ''UPDATE'' then
813 if new.slotlink != old.slotlink then
814 if old.slotlink != '''' then
815 dummy := tg_slotlink_unset(old.slotlink, old.slotname);
817 if new.slotlink != '''' then
818 dummy := tg_slotlink_set(new.slotlink, new.slotname);
821 if new.slotname != old.slotname and new.slotlink != '''' then
822 dummy := tg_slotlink_set(new.slotlink, new.slotname);
827 if tg_op = ''DELETE'' then
828 if old.slotlink != '''' then
829 dummy := tg_slotlink_unset(old.slotlink, old.slotname);
837 create trigger tg_slotlink_a after insert or update or delete
838 on PSlot for each row execute procedure tg_slotlink_a('PS');
840 create trigger tg_slotlink_a after insert or update or delete
841 on WSlot for each row execute procedure tg_slotlink_a('WS');
843 create trigger tg_slotlink_a after insert or update or delete
844 on IFace for each row execute procedure tg_slotlink_a('IF');
846 create trigger tg_slotlink_a after insert or update or delete
847 on HSlot for each row execute procedure tg_slotlink_a('HS');
849 create trigger tg_slotlink_a after insert or update or delete
850 on PHone for each row execute procedure tg_slotlink_a('PH');
853 -- ************************************************************
854 -- * Support function to set the opponents slotlink field
855 -- * if it does not already point to the requested slot
856 -- ************************************************************
857 create function tg_slotlink_set(bpchar, bpchar)
866 mytype := substr(myname, 1, 2);
867 link := mytype || substr(blname, 1, 2);
868 if link = ''PHPH'' then
870 ''slotlink between two phones does not make sense'';
872 if link in (''PHHS'', ''HSPH'') then
874 ''link of phone to hub does not make sense'';
876 if link in (''PHIF'', ''IFPH'') then
878 ''link of phone to hub does not make sense'';
880 if link in (''PSWS'', ''WSPS'') then
882 ''slotlink from patchslot to wallslot not permitted'';
884 if mytype = ''PS'' then
885 select into rec * from PSlot where slotname = myname;
887 raise exception ''% does not exist'', myname;
889 if rec.slotlink != blname then
890 update PSlot set slotlink = blname where slotname = myname;
894 if mytype = ''WS'' then
895 select into rec * from WSlot where slotname = myname;
897 raise exception ''% does not exist'', myname;
899 if rec.slotlink != blname then
900 update WSlot set slotlink = blname where slotname = myname;
904 if mytype = ''IF'' then
905 select into rec * from IFace where slotname = myname;
907 raise exception ''% does not exist'', myname;
909 if rec.slotlink != blname then
910 update IFace set slotlink = blname where slotname = myname;
914 if mytype = ''HS'' then
915 select into rec * from HSlot where slotname = myname;
917 raise exception ''% does not exist'', myname;
919 if rec.slotlink != blname then
920 update HSlot set slotlink = blname where slotname = myname;
924 if mytype = ''PH'' then
925 select into rec * from PHone where slotname = myname;
927 raise exception ''% does not exist'', myname;
929 if rec.slotlink != blname then
930 update PHone set slotlink = blname where slotname = myname;
934 raise exception ''illegal slotlink beginning with %'', mytype;
939 -- ************************************************************
940 -- * Support function to clear out the slotlink field if
941 -- * it still points to specific slot
942 -- ************************************************************
943 create function tg_slotlink_unset(bpchar, bpchar)
951 mytype := substr(myname, 1, 2);
952 if mytype = ''PS'' then
953 select into rec * from PSlot where slotname = myname;
957 if rec.slotlink = blname then
958 update PSlot set slotlink = '''' where slotname = myname;
962 if mytype = ''WS'' then
963 select into rec * from WSlot where slotname = myname;
967 if rec.slotlink = blname then
968 update WSlot set slotlink = '''' where slotname = myname;
972 if mytype = ''IF'' then
973 select into rec * from IFace where slotname = myname;
977 if rec.slotlink = blname then
978 update IFace set slotlink = '''' where slotname = myname;
982 if mytype = ''HS'' then
983 select into rec * from HSlot where slotname = myname;
987 if rec.slotlink = blname then
988 update HSlot set slotlink = '''' where slotname = myname;
992 if mytype = ''PH'' then
993 select into rec * from PHone where slotname = myname;
997 if rec.slotlink = blname then
998 update PHone set slotlink = '''' where slotname = myname;
1006 -- ************************************************************
1007 -- * Describe the backside of a patchfield slot
1008 -- ************************************************************
1009 create function pslot_backlink_view(bpchar)
1017 select into rec * from PSlot where slotname = $1;
1021 if rec.backlink = '''' then
1024 bltype := substr(rec.backlink, 1, 2);
1025 if bltype = ''PL'' then
1029 select into rec * from PLine where slotname = "outer".rec.backlink;
1030 retval := ''Phone line '' || trim(rec.phonenumber);
1031 if rec.comment != '''' then
1032 retval := retval || '' ('';
1033 retval := retval || rec.comment;
1034 retval := retval || '')'';
1039 if bltype = ''WS'' then
1040 select into rec * from WSlot where slotname = rec.backlink;
1041 retval := trim(rec.slotname) || '' in room '';
1042 retval := retval || trim(rec.roomno);
1043 retval := retval || '' -> '';
1044 return retval || wslot_slotlink_view(rec.slotname);
1046 return rec.backlink;
1051 -- ************************************************************
1052 -- * Describe the front of a patchfield slot
1053 -- ************************************************************
1054 create function pslot_slotlink_view(bpchar)
1061 select into psrec * from PSlot where slotname = $1;
1065 if psrec.slotlink = '''' then
1068 sltype := substr(psrec.slotlink, 1, 2);
1069 if sltype = ''PS'' then
1070 retval := trim(psrec.slotlink) || '' -> '';
1071 return retval || pslot_backlink_view(psrec.slotlink);
1073 if sltype = ''HS'' then
1074 retval := comment from Hub H, HSlot HS
1075 where HS.slotname = psrec.slotlink
1076 and H.name = HS.hubname;
1077 retval := retval || '' slot '';
1078 retval := retval || slotno::text from HSlot
1079 where slotname = psrec.slotlink;
1082 return psrec.slotlink;
1087 -- ************************************************************
1088 -- * Describe the front of a wall connector slot
1089 -- ************************************************************
1090 create function wslot_slotlink_view(bpchar)
1097 select into rec * from WSlot where slotname = $1;
1101 if rec.slotlink = '''' then
1104 sltype := substr(rec.slotlink, 1, 2);
1105 if sltype = ''PH'' then
1106 select into rec * from PHone where slotname = rec.slotlink;
1107 retval := ''Phone '' || trim(rec.slotname);
1108 if rec.comment != '''' then
1109 retval := retval || '' ('';
1110 retval := retval || rec.comment;
1111 retval := retval || '')'';
1115 if sltype = ''IF'' then
1117 syrow System%RowType;
1118 ifrow IFace%ROWTYPE;
1120 select into ifrow * from IFace where slotname = rec.slotlink;
1121 select into syrow * from System where name = ifrow.sysname;
1122 retval := syrow.name || '' IF '';
1123 retval := retval || ifrow.ifname;
1124 if syrow.comment != '''' then
1125 retval := retval || '' ('';
1126 retval := retval || syrow.comment;
1127 retval := retval || '')'';
1132 return rec.slotlink;
1138 -- ************************************************************
1139 -- * View of a patchfield describing backside and patches
1140 -- ************************************************************
1141 create view Pfield_v1 as select PF.pfname, PF.slotname,
1142 pslot_backlink_view(PF.slotname) as backside,
1143 pslot_slotlink_view(PF.slotname) as patch
1148 -- First we build the house - so we create the rooms
1150 insert into Room values ('001', 'Entrance');
1151 insert into Room values ('002', 'Office');
1152 insert into Room values ('003', 'Office');
1153 insert into Room values ('004', 'Technical');
1154 insert into Room values ('101', 'Office');
1155 insert into Room values ('102', 'Conference');
1156 insert into Room values ('103', 'Restroom');
1157 insert into Room values ('104', 'Technical');
1158 insert into Room values ('105', 'Office');
1159 insert into Room values ('106', 'Office');
1162 -- Second we install the wall connectors
1164 insert into WSlot values ('WS.001.1a', '001', '', '');
1165 insert into WSlot values ('WS.001.1b', '001', '', '');
1166 insert into WSlot values ('WS.001.2a', '001', '', '');
1167 insert into WSlot values ('WS.001.2b', '001', '', '');
1168 insert into WSlot values ('WS.001.3a', '001', '', '');
1169 insert into WSlot values ('WS.001.3b', '001', '', '');
1171 insert into WSlot values ('WS.002.1a', '002', '', '');
1172 insert into WSlot values ('WS.002.1b', '002', '', '');
1173 insert into WSlot values ('WS.002.2a', '002', '', '');
1174 insert into WSlot values ('WS.002.2b', '002', '', '');
1175 insert into WSlot values ('WS.002.3a', '002', '', '');
1176 insert into WSlot values ('WS.002.3b', '002', '', '');
1178 insert into WSlot values ('WS.003.1a', '003', '', '');
1179 insert into WSlot values ('WS.003.1b', '003', '', '');
1180 insert into WSlot values ('WS.003.2a', '003', '', '');
1181 insert into WSlot values ('WS.003.2b', '003', '', '');
1182 insert into WSlot values ('WS.003.3a', '003', '', '');
1183 insert into WSlot values ('WS.003.3b', '003', '', '');
1185 insert into WSlot values ('WS.101.1a', '101', '', '');
1186 insert into WSlot values ('WS.101.1b', '101', '', '');
1187 insert into WSlot values ('WS.101.2a', '101', '', '');
1188 insert into WSlot values ('WS.101.2b', '101', '', '');
1189 insert into WSlot values ('WS.101.3a', '101', '', '');
1190 insert into WSlot values ('WS.101.3b', '101', '', '');
1192 insert into WSlot values ('WS.102.1a', '102', '', '');
1193 insert into WSlot values ('WS.102.1b', '102', '', '');
1194 insert into WSlot values ('WS.102.2a', '102', '', '');
1195 insert into WSlot values ('WS.102.2b', '102', '', '');
1196 insert into WSlot values ('WS.102.3a', '102', '', '');
1197 insert into WSlot values ('WS.102.3b', '102', '', '');
1199 insert into WSlot values ('WS.105.1a', '105', '', '');
1200 insert into WSlot values ('WS.105.1b', '105', '', '');
1201 insert into WSlot values ('WS.105.2a', '105', '', '');
1202 insert into WSlot values ('WS.105.2b', '105', '', '');
1203 insert into WSlot values ('WS.105.3a', '105', '', '');
1204 insert into WSlot values ('WS.105.3b', '105', '', '');
1206 insert into WSlot values ('WS.106.1a', '106', '', '');
1207 insert into WSlot values ('WS.106.1b', '106', '', '');
1208 insert into WSlot values ('WS.106.2a', '106', '', '');
1209 insert into WSlot values ('WS.106.2b', '106', '', '');
1210 insert into WSlot values ('WS.106.3a', '106', '', '');
1211 insert into WSlot values ('WS.106.3b', '106', '', '');
1214 -- Now create the patch fields and their slots
1216 insert into PField values ('PF0_1', 'Wallslots basement');
1219 -- The cables for these will be made later, so they are unconnected for now
1221 insert into PSlot values ('PS.base.a1', 'PF0_1', '', '');
1222 insert into PSlot values ('PS.base.a2', 'PF0_1', '', '');
1223 insert into PSlot values ('PS.base.a3', 'PF0_1', '', '');
1224 insert into PSlot values ('PS.base.a4', 'PF0_1', '', '');
1225 insert into PSlot values ('PS.base.a5', 'PF0_1', '', '');
1226 insert into PSlot values ('PS.base.a6', 'PF0_1', '', '');
1229 -- These are already wired to the wall connectors
1231 insert into PSlot values ('PS.base.b1', 'PF0_1', '', 'WS.002.1a');
1232 insert into PSlot values ('PS.base.b2', 'PF0_1', '', 'WS.002.1b');
1233 insert into PSlot values ('PS.base.b3', 'PF0_1', '', 'WS.002.2a');
1234 insert into PSlot values ('PS.base.b4', 'PF0_1', '', 'WS.002.2b');
1235 insert into PSlot values ('PS.base.b5', 'PF0_1', '', 'WS.002.3a');
1236 insert into PSlot values ('PS.base.b6', 'PF0_1', '', 'WS.002.3b');
1238 insert into PSlot values ('PS.base.c1', 'PF0_1', '', 'WS.003.1a');
1239 insert into PSlot values ('PS.base.c2', 'PF0_1', '', 'WS.003.1b');
1240 insert into PSlot values ('PS.base.c3', 'PF0_1', '', 'WS.003.2a');
1241 insert into PSlot values ('PS.base.c4', 'PF0_1', '', 'WS.003.2b');
1242 insert into PSlot values ('PS.base.c5', 'PF0_1', '', 'WS.003.3a');
1243 insert into PSlot values ('PS.base.c6', 'PF0_1', '', 'WS.003.3b');
1246 -- This patchfield will be renamed later into PF0_2 - so its
1247 -- slots references in pfname should follow
1249 insert into PField values ('PF0_X', 'Phonelines basement');
1251 insert into PSlot values ('PS.base.ta1', 'PF0_X', '', '');
1252 insert into PSlot values ('PS.base.ta2', 'PF0_X', '', '');
1253 insert into PSlot values ('PS.base.ta3', 'PF0_X', '', '');
1254 insert into PSlot values ('PS.base.ta4', 'PF0_X', '', '');
1255 insert into PSlot values ('PS.base.ta5', 'PF0_X', '', '');
1256 insert into PSlot values ('PS.base.ta6', 'PF0_X', '', '');
1258 insert into PSlot values ('PS.base.tb1', 'PF0_X', '', '');
1259 insert into PSlot values ('PS.base.tb2', 'PF0_X', '', '');
1260 insert into PSlot values ('PS.base.tb3', 'PF0_X', '', '');
1261 insert into PSlot values ('PS.base.tb4', 'PF0_X', '', '');
1262 insert into PSlot values ('PS.base.tb5', 'PF0_X', '', '');
1263 insert into PSlot values ('PS.base.tb6', 'PF0_X', '', '');
1265 insert into PField values ('PF1_1', 'Wallslots first floor');
1267 insert into PSlot values ('PS.first.a1', 'PF1_1', '', 'WS.101.1a');
1268 insert into PSlot values ('PS.first.a2', 'PF1_1', '', 'WS.101.1b');
1269 insert into PSlot values ('PS.first.a3', 'PF1_1', '', 'WS.101.2a');
1270 insert into PSlot values ('PS.first.a4', 'PF1_1', '', 'WS.101.2b');
1271 insert into PSlot values ('PS.first.a5', 'PF1_1', '', 'WS.101.3a');
1272 insert into PSlot values ('PS.first.a6', 'PF1_1', '', 'WS.101.3b');
1274 insert into PSlot values ('PS.first.b1', 'PF1_1', '', 'WS.102.1a');
1275 insert into PSlot values ('PS.first.b2', 'PF1_1', '', 'WS.102.1b');
1276 insert into PSlot values ('PS.first.b3', 'PF1_1', '', 'WS.102.2a');
1277 insert into PSlot values ('PS.first.b4', 'PF1_1', '', 'WS.102.2b');
1278 insert into PSlot values ('PS.first.b5', 'PF1_1', '', 'WS.102.3a');
1279 insert into PSlot values ('PS.first.b6', 'PF1_1', '', 'WS.102.3b');
1281 insert into PSlot values ('PS.first.c1', 'PF1_1', '', 'WS.105.1a');
1282 insert into PSlot values ('PS.first.c2', 'PF1_1', '', 'WS.105.1b');
1283 insert into PSlot values ('PS.first.c3', 'PF1_1', '', 'WS.105.2a');
1284 insert into PSlot values ('PS.first.c4', 'PF1_1', '', 'WS.105.2b');
1285 insert into PSlot values ('PS.first.c5', 'PF1_1', '', 'WS.105.3a');
1286 insert into PSlot values ('PS.first.c6', 'PF1_1', '', 'WS.105.3b');
1288 insert into PSlot values ('PS.first.d1', 'PF1_1', '', 'WS.106.1a');
1289 insert into PSlot values ('PS.first.d2', 'PF1_1', '', 'WS.106.1b');
1290 insert into PSlot values ('PS.first.d3', 'PF1_1', '', 'WS.106.2a');
1291 insert into PSlot values ('PS.first.d4', 'PF1_1', '', 'WS.106.2b');
1292 insert into PSlot values ('PS.first.d5', 'PF1_1', '', 'WS.106.3a');
1293 insert into PSlot values ('PS.first.d6', 'PF1_1', '', 'WS.106.3b');
1296 -- Now we wire the wall connectors 1a-2a in room 001 to the
1297 -- patchfield. In the second update we make an error, and
1300 update PSlot set backlink = 'WS.001.1a' where slotname = 'PS.base.a1';
1301 update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a3';
1302 select * from WSlot where roomno = '001' order by slotname;
1303 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1304 update PSlot set backlink = 'WS.001.2a' where slotname = 'PS.base.a3';
1305 select * from WSlot where roomno = '001' order by slotname;
1306 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1307 update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a2';
1308 select * from WSlot where roomno = '001' order by slotname;
1309 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1312 -- Same procedure for 2b-3b but this time updating the WSlot instead
1313 -- of the PSlot. Due to the triggers the result is the same:
1314 -- WSlot and corresponding PSlot point to each other.
1316 update WSlot set backlink = 'PS.base.a4' where slotname = 'WS.001.2b';
1317 update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3a';
1318 select * from WSlot where roomno = '001' order by slotname;
1319 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1320 update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3b';
1321 select * from WSlot where roomno = '001' order by slotname;
1322 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1323 update WSlot set backlink = 'PS.base.a5' where slotname = 'WS.001.3a';
1324 select * from WSlot where roomno = '001' order by slotname;
1325 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1327 insert into PField values ('PF1_2', 'Phonelines first floor');
1329 insert into PSlot values ('PS.first.ta1', 'PF1_2', '', '');
1330 insert into PSlot values ('PS.first.ta2', 'PF1_2', '', '');
1331 insert into PSlot values ('PS.first.ta3', 'PF1_2', '', '');
1332 insert into PSlot values ('PS.first.ta4', 'PF1_2', '', '');
1333 insert into PSlot values ('PS.first.ta5', 'PF1_2', '', '');
1334 insert into PSlot values ('PS.first.ta6', 'PF1_2', '', '');
1336 insert into PSlot values ('PS.first.tb1', 'PF1_2', '', '');
1337 insert into PSlot values ('PS.first.tb2', 'PF1_2', '', '');
1338 insert into PSlot values ('PS.first.tb3', 'PF1_2', '', '');
1339 insert into PSlot values ('PS.first.tb4', 'PF1_2', '', '');
1340 insert into PSlot values ('PS.first.tb5', 'PF1_2', '', '');
1341 insert into PSlot values ('PS.first.tb6', 'PF1_2', '', '');
1344 -- Fix the wrong name for patchfield PF0_2
1346 update PField set name = 'PF0_2' where name = 'PF0_X';
1348 select * from PSlot order by slotname;
1349 select * from WSlot order by slotname;
1352 -- Install the central phone system and create the phone numbers.
1353 -- They are weired on insert to the patchfields. Again the
1354 -- triggers automatically tell the PSlots to update their
1357 insert into PLine values ('PL.001', '-0', 'Central call', 'PS.base.ta1');
1358 insert into PLine values ('PL.002', '-101', '', 'PS.base.ta2');
1359 insert into PLine values ('PL.003', '-102', '', 'PS.base.ta3');
1360 insert into PLine values ('PL.004', '-103', '', 'PS.base.ta5');
1361 insert into PLine values ('PL.005', '-104', '', 'PS.base.ta6');
1362 insert into PLine values ('PL.006', '-106', '', 'PS.base.tb2');
1363 insert into PLine values ('PL.007', '-108', '', 'PS.base.tb3');
1364 insert into PLine values ('PL.008', '-109', '', 'PS.base.tb4');
1365 insert into PLine values ('PL.009', '-121', '', 'PS.base.tb5');
1366 insert into PLine values ('PL.010', '-122', '', 'PS.base.tb6');
1367 insert into PLine values ('PL.015', '-134', '', 'PS.first.ta1');
1368 insert into PLine values ('PL.016', '-137', '', 'PS.first.ta3');
1369 insert into PLine values ('PL.017', '-139', '', 'PS.first.ta4');
1370 insert into PLine values ('PL.018', '-362', '', 'PS.first.tb1');
1371 insert into PLine values ('PL.019', '-363', '', 'PS.first.tb2');
1372 insert into PLine values ('PL.020', '-364', '', 'PS.first.tb3');
1373 insert into PLine values ('PL.021', '-365', '', 'PS.first.tb5');
1374 insert into PLine values ('PL.022', '-367', '', 'PS.first.tb6');
1375 insert into PLine values ('PL.028', '-501', 'Fax entrance', 'PS.base.ta2');
1376 insert into PLine values ('PL.029', '-502', 'Fax first floor', 'PS.first.ta1');
1379 -- Buy some phones, plug them into the wall and patch the
1380 -- phone lines to the corresponding patchfield slots.
1382 insert into PHone values ('PH.hc001', 'Hicom standard', 'WS.001.1a');
1383 update PSlot set slotlink = 'PS.base.ta1' where slotname = 'PS.base.a1';
1384 insert into PHone values ('PH.hc002', 'Hicom standard', 'WS.002.1a');
1385 update PSlot set slotlink = 'PS.base.ta5' where slotname = 'PS.base.b1';
1386 insert into PHone values ('PH.hc003', 'Hicom standard', 'WS.002.2a');
1387 update PSlot set slotlink = 'PS.base.tb2' where slotname = 'PS.base.b3';
1388 insert into PHone values ('PH.fax001', 'Canon fax', 'WS.001.2a');
1389 update PSlot set slotlink = 'PS.base.ta2' where slotname = 'PS.base.a3';
1392 -- Install a hub at one of the patchfields, plug a computers
1393 -- ethernet interface into the wall and patch it to the hub.
1395 insert into Hub values ('base.hub1', 'Patchfield PF0_1 hub', 16);
1396 insert into System values ('orion', 'PC');
1397 insert into IFace values ('IF', 'orion', 'eth0', 'WS.002.1b');
1398 update PSlot set slotlink = 'HS.base.hub1.1' where slotname = 'PS.base.b2';
1401 -- Now we take a look at the patchfield
1403 select * from PField_v1 where pfname = 'PF0_1' order by slotname;
1404 select * from PField_v1 where pfname = 'PF0_2' order by slotname;
1407 -- Finally we want errors
1409 insert into PField values ('PF1_1', 'should fail due to unique index');
1410 update PSlot set backlink = 'WS.not.there' where slotname = 'PS.base.a1';
1411 update PSlot set backlink = 'XX.illegal' where slotname = 'PS.base.a1';
1412 update PSlot set slotlink = 'PS.not.there' where slotname = 'PS.base.a1';
1413 update PSlot set slotlink = 'XX.illegal' where slotname = 'PS.base.a1';
1414 insert into HSlot values ('HS', 'base.hub1', 1, '');
1415 insert into HSlot values ('HS', 'base.hub1', 20, '');
1417 insert into IFace values ('IF', 'notthere', 'eth0', '');
1418 insert into IFace values ('IF', 'orion', 'ethernet_interface_name_too_long', '');
1422 -- The following tests are unrelated to the scenario outlined above;
1423 -- they merely exercise specific parts of PL/PgSQL
1427 -- Test recursion, per bug report 7-Sep-01
1429 CREATE FUNCTION recursion_test(int,int) RETURNS text AS '
1433 rslt = CAST($2 AS TEXT);
1435 rslt = CAST($1 AS TEXT) || '','' || recursion_test($1 - 1, $2);
1438 END;' LANGUAGE plpgsql;
1440 SELECT recursion_test(4,3);
1443 -- Test the FOUND magic variable
1445 CREATE TABLE found_test_tbl (a int);
1447 create function test_found()
1448 returns boolean as '
1451 insert into found_test_tbl values (1);
1453 insert into found_test_tbl values (2);
1456 update found_test_tbl set a = 100 where a = 1;
1458 insert into found_test_tbl values (3);
1461 delete from found_test_tbl where a = 9999; -- matches no rows
1463 insert into found_test_tbl values (4);
1466 for i in 1 .. 10 loop
1467 -- no need to do anything
1470 insert into found_test_tbl values (5);
1473 -- never executes the loop
1474 for i in 2 .. 1 loop
1475 -- no need to do anything
1478 insert into found_test_tbl values (6);
1481 end;' language plpgsql;
1483 select test_found();
1484 select * from found_test_tbl;
1487 -- Test set-returning functions for PL/pgSQL
1490 create function test_table_func_rec() returns setof found_test_tbl as '
1494 FOR rec IN select * from found_test_tbl LOOP
1498 END;' language plpgsql;
1500 select * from test_table_func_rec();
1502 create function test_table_func_row() returns setof found_test_tbl as '
1504 row found_test_tbl%ROWTYPE;
1506 FOR row IN select * from found_test_tbl LOOP
1510 END;' language plpgsql;
1512 select * from test_table_func_row();
1514 create function test_ret_set_scalar(int,int) returns setof int as '
1518 FOR i IN $1 .. $2 LOOP
1522 END;' language plpgsql;
1524 select * from test_ret_set_scalar(1,10);
1526 create function test_ret_set_rec_dyn(int) returns setof record as '
1531 SELECT INTO retval 5, 10, 15;
1535 SELECT INTO retval 50, 5::numeric, ''xxx''::text;
1540 END;' language plpgsql;
1542 SELECT * FROM test_ret_set_rec_dyn(1500) AS (a int, b int, c int);
1543 SELECT * FROM test_ret_set_rec_dyn(5) AS (a int, b numeric, c text);
1545 create function test_ret_rec_dyn(int) returns record as '
1550 SELECT INTO retval 5, 10, 15;
1553 SELECT INTO retval 50, 5::numeric, ''xxx''::text;
1556 END;' language plpgsql;
1558 SELECT * FROM test_ret_rec_dyn(1500) AS (a int, b int, c int);
1559 SELECT * FROM test_ret_rec_dyn(5) AS (a int, b numeric, c text);
1562 -- Test handling of OUT parameters, including polymorphic cases.
1563 -- Note that RETURN is optional with OUT params; we try both ways.
1566 -- wrong way to do it:
1567 create function f1(in i int, out j int) returns int as $$
1570 end$$ language plpgsql;
1572 create function f1(in i int, out j int) as $$
1576 end$$ language plpgsql;
1579 select * from f1(42);
1581 create or replace function f1(inout i int) as $$
1584 end$$ language plpgsql;
1587 select * from f1(42);
1589 drop function f1(int);
1591 create function f1(in i int, out j int) returns setof int as $$
1598 end$$ language plpgsql;
1600 select * from f1(42);
1602 drop function f1(int);
1604 create function f1(in i int, out j int, out k text) as $$
1609 end$$ language plpgsql;
1612 select * from f1(42);
1614 drop function f1(int);
1616 create function f1(in i int, out j int, out k text) returns setof record as $$
1624 end$$ language plpgsql;
1626 select * from f1(42);
1628 drop function f1(int);
1630 create function duplic(in i anyelement, out j anyelement, out k anyarray) as $$
1635 end$$ language plpgsql;
1637 select * from duplic(42);
1638 select * from duplic('foo'::text);
1640 drop function duplic(anyelement);
1646 create table perform_test (
1651 create function simple_func(int) returns boolean as '
1654 INSERT INTO perform_test VALUES ($1, $1 + 10);
1659 END;' language plpgsql;
1661 create function perform_test_func() returns void as '
1664 INSERT INTO perform_test VALUES (100, 100);
1667 PERFORM simple_func(5);
1670 INSERT INTO perform_test VALUES (100, 100);
1673 PERFORM simple_func(50);
1676 INSERT INTO perform_test VALUES (100, 100);
1680 END;' language plpgsql;
1682 SELECT perform_test_func();
1683 SELECT * FROM perform_test;
1685 drop table perform_test;
1688 -- Test error trapping
1691 create function trap_zero_divide(int) returns int as $$
1695 begin -- start a subtransaction
1696 raise notice 'should see this';
1698 raise notice 'should see this only if % <> 0', $1;
1700 raise notice 'should see this only if % fits in smallint', $1;
1702 raise exception '% is less than zero', $1;
1705 when division_by_zero then
1706 raise notice 'caught division_by_zero';
1708 when NUMERIC_VALUE_OUT_OF_RANGE then
1709 raise notice 'caught numeric_value_out_of_range';
1713 end$$ language plpgsql;
1715 select trap_zero_divide(50);
1716 select trap_zero_divide(0);
1717 select trap_zero_divide(100000);
1718 select trap_zero_divide(-100);
1720 create function trap_matching_test(int) returns int as $$
1725 begin -- start a subtransaction
1728 select into y unique1 from tenk1 where unique2 =
1729 (select unique2 from tenk1 b where ten = $1);
1731 when data_exception then -- category match
1732 raise notice 'caught data_exception';
1734 when NUMERIC_VALUE_OUT_OF_RANGE OR CARDINALITY_VIOLATION then
1735 raise notice 'caught numeric_value_out_of_range or cardinality_violation';
1739 end$$ language plpgsql;
1741 select trap_matching_test(50);
1742 select trap_matching_test(0);
1743 select trap_matching_test(100000);
1744 select trap_matching_test(1);
1746 create temp table foo (f1 int);
1748 create function blockme() returns int as $$
1752 insert into foo values(x);
1755 insert into foo values(x);
1756 -- we assume this will take longer than 2 seconds:
1757 select count(*) into x from tenk1 a, tenk1 b, tenk1 c;
1760 raise notice 'caught others?';
1762 when query_canceled then
1763 raise notice 'nyeah nyeah, can''t stop me';
1766 insert into foo values(x);
1768 end$$ language plpgsql;
1770 set statement_timeout to 2000;
1774 reset statement_timeout;
1780 -- Test for pass-by-ref values being stored in proper context
1781 create function test_variable_storage() returns text as $$
1787 -- force error inside subtransaction SPI context
1788 perform trap_zero_divide(-100);
1794 end$$ language plpgsql;
1796 select test_variable_storage();
1799 -- test foreign key error trapping
1802 create temp table master(f1 int primary key);
1804 create temp table slave(f1 int references master deferrable);
1806 insert into master values(1);
1807 insert into slave values(1);
1808 insert into slave values(2); -- fails
1810 create function trap_foreign_key(int) returns int as $$
1812 begin -- start a subtransaction
1813 insert into slave values($1);
1815 when foreign_key_violation then
1816 raise notice 'caught foreign_key_violation';
1820 end$$ language plpgsql;
1822 create function trap_foreign_key_2() returns int as $$
1824 begin -- start a subtransaction
1825 set constraints all immediate;
1827 when foreign_key_violation then
1828 raise notice 'caught foreign_key_violation';
1832 end$$ language plpgsql;
1834 select trap_foreign_key(1);
1835 select trap_foreign_key(2); -- detects FK violation
1838 set constraints all deferred;
1839 select trap_foreign_key(2); -- should not detect FK violation
1841 set constraints all immediate; -- fails
1843 select trap_foreign_key_2(); -- detects FK violation
1844 commit; -- still fails
1846 drop function trap_foreign_key(int);
1847 drop function trap_foreign_key_2();
1850 -- Test proper snapshot handling in simple expressions
1853 create temp table users(login text, id serial);
1855 create function sp_id_user(a_login text) returns int as $$
1858 select into x id from users where login = a_login;
1859 if found then return x; end if;
1861 end$$ language plpgsql stable;
1863 insert into users values('user1');
1865 select sp_id_user('user1');
1866 select sp_id_user('userx');
1868 create function sp_add_user(a_login text) returns int as $$
1869 declare my_id_user int;
1871 my_id_user = sp_id_user( a_login );
1872 IF my_id_user > 0 THEN
1873 RETURN -1; -- error code for existing user
1875 INSERT INTO users ( login ) VALUES ( a_login );
1876 my_id_user = sp_id_user( a_login );
1877 IF my_id_user = 0 THEN
1878 RETURN -2; -- error code for insertion failure
1881 end$$ language plpgsql;
1883 select sp_add_user('user1');
1884 select sp_add_user('user2');
1885 select sp_add_user('user2');
1886 select sp_add_user('user3');
1887 select sp_add_user('user3');
1889 drop function sp_add_user(text);
1890 drop function sp_id_user(text);
1893 -- tests for refcursors
1895 create table rc_test (a int, b int);
1896 copy rc_test from stdin;
1902 create function return_refcursor(rc refcursor) returns refcursor as $$
1904 open rc for select a from rc_test;
1907 $$ language plpgsql;
1909 create function refcursor_test1(refcursor) returns refcursor as $$
1911 perform return_refcursor($1);
1914 $$ language plpgsql;
1918 select refcursor_test1('test1');
1919 fetch next in test1;
1921 select refcursor_test1('test2');
1922 fetch all from test2;
1927 fetch next from test1;
1929 create function refcursor_test2(int, int) returns boolean as $$
1931 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
1935 fetch c1 into nonsense;
1943 $$ language plpgsql;
1945 select refcursor_test2(20000, 20000) as "Should be false",
1946 refcursor_test2(20, 20) as "Should be true";
1949 -- tests for "raise" processing
1951 create function raise_test1(int) returns int as $$
1953 raise notice 'This message has too many parameters!', $1;
1956 $$ language plpgsql;
1958 select raise_test1(5);
1960 create function raise_test2(int) returns int as $$
1962 raise notice 'This message has too few parameters: %, %, %', $1, $1;
1965 $$ language plpgsql;
1967 select raise_test2(10);
1970 -- reject function definitions that contain malformed SQL queries at
1971 -- compile-time, where possible
1973 create function bad_sql1() returns int as $$
1980 end$$ language plpgsql;
1982 create function bad_sql2() returns int as $$
1985 for r in select I fought the law, the law won LOOP
1986 raise notice 'in loop';
1989 end;$$ language plpgsql;
1991 -- a RETURN expression is mandatory, except for void-returning
1992 -- functions, where it is not allowed
1993 create function missing_return_expr() returns int as $$
1996 end;$$ language plpgsql;
1998 create function void_return_expr() returns void as $$
2001 end;$$ language plpgsql;
2003 -- VOID functions are allowed to omit RETURN
2004 create function void_return_expr() returns void as $$
2007 end;$$ language plpgsql;
2009 select void_return_expr();
2011 -- but ordinary functions are not
2012 create function missing_return_expr() returns int as $$
2015 end;$$ language plpgsql;
2017 select missing_return_expr();
2019 drop function void_return_expr();
2020 drop function missing_return_expr();
2023 -- EXECUTE ... INTO test
2026 create table eifoo (i integer, y integer);
2027 create type eitype as (i integer, y integer);
2029 create or replace function execute_into_test(varchar) returns record as $$
2038 execute 'insert into '||$1||' values(10,15)';
2039 execute 'select (row).* from (select row(10,1)::eifoo) s' into _r;
2040 raise notice '% %', _r.i, _r.y;
2041 execute 'select * from '||$1||' limit 1' into _rt;
2042 raise notice '% %', _rt.i, _rt.y;
2043 execute 'select *, 20 from '||$1||' limit 1' into i, j, k;
2044 raise notice '% % %', i, j, k;
2045 execute 'select 1,2' into _v;
2047 end; $$ language plpgsql;
2049 select execute_into_test('eifoo');
2051 drop table eifoo cascade;
2052 drop type eitype cascade;
2055 -- SQLSTATE and SQLERRM test
2058 create function excpt_test1() returns void as $$
2060 raise notice '% %', sqlstate, sqlerrm;
2061 end; $$ language plpgsql;
2062 -- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
2064 select excpt_test1();
2066 create function excpt_test2() returns void as $$
2070 raise notice '% %', sqlstate, sqlerrm;
2073 end; $$ language plpgsql;
2075 select excpt_test2();
2077 create function excpt_test3() returns void as $$
2080 raise exception 'user exception';
2081 exception when others then
2082 raise notice 'caught exception % %', sqlstate, sqlerrm;
2084 raise notice '% %', sqlstate, sqlerrm;
2087 when substring_error then
2088 -- this exception handler shouldn't be invoked
2089 raise notice 'unexpected exception: % %', sqlstate, sqlerrm;
2090 when division_by_zero then
2091 raise notice 'caught exception % %', sqlstate, sqlerrm;
2093 raise notice '% %', sqlstate, sqlerrm;
2095 end; $$ language plpgsql;
2097 select excpt_test3();
2098 drop function excpt_test1();
2099 drop function excpt_test2();
2100 drop function excpt_test3();
2102 -- parameters of raise stmt can be expressions
2103 create function raise_exprs() returns void as $$
2105 a integer[] = '{10,20,30}';
2110 raise notice '%; %; %; %; %; %', a, a[i], c, (select c || 'abc'), row(10,'aaa',NULL,30), NULL;
2111 end;$$ language plpgsql;
2113 select raise_exprs();
2114 drop function raise_exprs();
2116 -- continue statement
2117 create table conttesttbl(idx serial, v integer);
2118 insert into conttesttbl(v) values(10);
2119 insert into conttesttbl(v) values(20);
2120 insert into conttesttbl(v) values(30);
2121 insert into conttesttbl(v) values(40);
2123 create function continue_test1() returns void as $$
2124 declare _i integer = 0; _r record;
2126 raise notice '---1---';
2129 raise notice '%', _i;
2130 continue when _i < 10;
2134 raise notice '---2---';
2139 raise notice '%', _i;
2140 continue lbl when _i > 0;
2145 raise notice '---3---';
2149 continue the_loop when _i % 2 = 0;
2150 raise notice '%', _i;
2153 raise notice '---4---';
2154 for _i in 1..10 loop
2156 -- applies to outer loop, not the nested begin block
2157 continue when _i < 5;
2158 raise notice '%', _i;
2162 raise notice '---5---';
2163 for _r in select * from conttesttbl loop
2164 continue when _r.v <= 20;
2165 raise notice '%', _r.v;
2168 raise notice '---6---';
2169 for _r in execute 'select * from conttesttbl' loop
2170 continue when _r.v <= 20;
2171 raise notice '%', _r.v;
2174 raise notice '---7---';
2176 raise notice '%', _i;
2177 continue when _i = 3;
2180 raise notice '---8---';
2183 raise notice '%', _i;
2185 continue when _i = 3;
2188 raise notice '---9---';
2189 for _r in select * from conttesttbl order by v limit 1 loop
2190 raise notice '%', _r.v;
2194 raise notice '---10---';
2195 for _r in execute 'select * from conttesttbl order by v limit 1' loop
2196 raise notice '%', _r.v;
2199 end; $$ language plpgsql;
2201 select continue_test1();
2203 -- CONTINUE is only legal inside a loop
2204 create function continue_test2() returns void as $$
2211 $$ language plpgsql;
2214 select continue_test2();
2216 -- CONTINUE can't reference the label of a named block
2217 create function continue_test3() returns void as $$
2222 continue begin_block1;
2226 $$ language plpgsql;
2229 select continue_test3();
2231 drop function continue_test1();
2232 drop function continue_test2();
2233 drop function continue_test3();
2234 drop table conttesttbl;
2236 -- verbose end block and end loop
2237 create function end_label1() returns void as $$
2241 for _i in 1 .. 10 loop
2245 for _i in 1 .. 10 loop
2249 $$ language plpgsql;
2251 select end_label1();
2252 drop function end_label1();
2254 -- should fail: undefined end label
2255 create function end_label2() returns void as $$
2257 for _i in 1 .. 10 loop
2261 $$ language plpgsql;
2263 -- should fail: end label does not match start label
2264 create function end_label3() returns void as $$
2268 for _i in 1 .. 10 loop
2270 end loop outer_label;
2272 $$ language plpgsql;
2274 -- should fail: end label on a block without a start label
2275 create function end_label4() returns void as $$
2278 for _i in 1 .. 10 loop
2280 end loop outer_label;
2282 $$ language plpgsql;
2284 -- using list of scalars in fori and fore stmts
2285 create function for_vect() returns void as $proc$
2286 <<lbl>>declare a integer; b varchar; c varchar; r record;
2289 for i in 1 .. 3 loop
2290 raise notice '%', i;
2292 -- fore with record var
2293 for r in select gs as aa, 'BB' as bb, 'CC' as cc from generate_series(1,4) gs loop
2294 raise notice '% % %', r.aa, r.bb, r.cc;
2296 -- fore with single scalar
2297 for a in select gs from generate_series(1,4) gs loop
2298 raise notice '%', a;
2300 -- fore with multiple scalars
2301 for a,b,c in select gs, 'BB','CC' from generate_series(1,4) gs loop
2302 raise notice '% % %', a, b, c;
2304 -- using qualified names in fors, fore is enabled, disabled only for fori
2305 for lbl.a, lbl.b, lbl.c in execute $$select gs, 'bb','cc' from generate_series(1,4) gs$$ loop
2306 raise notice '% % %', a, b, c;
2309 $proc$ language plpgsql;
2313 -- regression test: verify that multiple uses of same plpgsql datum within
2314 -- a SQL command all get mapped to the same $n parameter. The return value
2315 -- of the SELECT is not important, we only care that it doesn't fail with
2316 -- a complaint about an ungrouped column reference.
2317 create function multi_datum_use(p1 int) returns bool as $$
2322 select into x,y unique1/p1, unique1/$1 from tenk1 group by unique1/p1;
2324 end$$ language plpgsql;
2326 select multi_datum_use(42);
2329 -- Test STRICT limiter in both planned and EXECUTE invocations.
2330 -- Note that a data-modifying query is quasi strict (disallow multi rows)
2331 -- by default in the planned case, but not in EXECUTE.
2334 create temp table foo (f1 int, f2 int);
2336 insert into foo values (1,2), (3,4);
2338 create or replace function footest() returns void as $$
2342 insert into foo values(5,6) returning * into x;
2343 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2344 end$$ language plpgsql;
2348 create or replace function footest() returns void as $$
2351 -- should fail due to implicit strict
2352 insert into foo values(7,8),(9,10) returning * into x;
2353 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2354 end$$ language plpgsql;
2358 create or replace function footest() returns void as $$
2362 execute 'insert into foo values(5,6) returning *' into x;
2363 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2364 end$$ language plpgsql;
2368 create or replace function footest() returns void as $$
2371 -- this should work since EXECUTE isn't as picky
2372 execute 'insert into foo values(7,8),(9,10) returning *' into x;
2373 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2374 end$$ language plpgsql;
2380 create or replace function footest() returns void as $$
2384 select * from foo where f1 = 3 into strict x;
2385 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2386 end$$ language plpgsql;
2390 create or replace function footest() returns void as $$
2393 -- should fail, no rows
2394 select * from foo where f1 = 0 into strict x;
2395 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2396 end$$ language plpgsql;
2400 create or replace function footest() returns void as $$
2403 -- should fail, too many rows
2404 select * from foo where f1 > 3 into strict x;
2405 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2406 end$$ language plpgsql;
2410 create or replace function footest() returns void as $$
2414 execute 'select * from foo where f1 = 3' into strict x;
2415 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2416 end$$ language plpgsql;
2420 create or replace function footest() returns void as $$
2423 -- should fail, no rows
2424 execute 'select * from foo where f1 = 0' into strict x;
2425 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2426 end$$ language plpgsql;
2430 create or replace function footest() returns void as $$
2433 -- should fail, too many rows
2434 execute 'select * from foo where f1 > 3' into strict x;
2435 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2436 end$$ language plpgsql;
2440 drop function footest();
2442 -- test scrollable cursor support
2444 create function sc_test() returns setof integer as $$
2446 c scroll cursor for select f1 from int4_tbl;
2450 fetch last from c into x;
2453 fetch prior from c into x;
2457 $$ language plpgsql;
2459 select * from sc_test();
2461 create or replace function sc_test() returns setof integer as $$
2463 c no scroll cursor for select f1 from int4_tbl;
2467 fetch last from c into x;
2470 fetch prior from c into x;
2474 $$ language plpgsql;
2476 select * from sc_test(); -- fails because of NO SCROLL specification
2478 create or replace function sc_test() returns setof integer as $$
2483 open c scroll for select f1 from int4_tbl;
2484 fetch last from c into x;
2487 fetch prior from c into x;
2491 $$ language plpgsql;
2493 select * from sc_test();
2495 create or replace function sc_test() returns setof integer as $$
2500 open c scroll for execute 'select f1 from int4_tbl';
2501 fetch last from c into x;
2504 fetch relative -2 from c into x;
2508 $$ language plpgsql;
2510 select * from sc_test();
2512 create or replace function sc_test() returns setof integer as $$
2517 open c scroll for execute 'select f1 from int4_tbl';
2518 fetch last from c into x;
2521 move backward 2 from c;
2522 fetch relative -1 from c into x;
2526 $$ language plpgsql;
2528 select * from sc_test();
2530 create or replace function sc_test() returns setof integer as $$
2532 c cursor for select * from generate_series(1, 10);
2537 move relative 2 in c;
2541 fetch next from c into x;
2548 $$ language plpgsql;
2550 select * from sc_test();
2552 create or replace function sc_test() returns setof integer as $$
2554 c cursor for select * from generate_series(1, 10);
2558 move forward all in c;
2559 fetch backward from c into x;
2565 $$ language plpgsql;
2567 select * from sc_test();
2569 drop function sc_test();
2571 -- test qualified variable names
2573 create function pl_qual_names (param1 int) returns void as $$
2582 raise notice 'param1 = %', param1;
2583 raise notice 'pl_qual_names.param1 = %', pl_qual_names.param1;
2584 raise notice 'outerblock.param1 = %', outerblock.param1;
2585 raise notice 'innerblock.param1 = %', innerblock.param1;
2588 $$ language plpgsql;
2590 select pl_qual_names(42);
2592 drop function pl_qual_names(int);
2594 -- tests for RETURN QUERY
2595 create function ret_query1(out int, out int) returns setof record as $$
2600 return query select x + 1, x * 10 from generate_series(0, 10) s (x);
2603 $$ language plpgsql;
2605 select * from ret_query1();
2607 create type record_type as (x text, y int, z boolean);
2609 create or replace function ret_query2(lim int) returns setof record_type as $$
2611 return query select md5(s.x::text), s.x, s.x > 0
2612 from generate_series(-8, lim) s (x) where s.x % 2 = 0;
2614 $$ language plpgsql;
2616 select * from ret_query2(8);
2618 -- test EXECUTE USING
2619 create function exc_using(int, text) returns int as $$
2622 for i in execute 'select * from generate_series(1,$1)' using $1+1 loop
2623 raise notice '%', i;
2625 execute 'select $2 + $2*3 + length($1)' into i using $2,$1;
2628 $$ language plpgsql;
2630 select exc_using(5, 'foobar');
2632 drop function exc_using(int, text);
2634 create or replace function exc_using(int) returns void as $$
2639 open c for execute 'select * from generate_series(1,$1)' using $1+1;
2642 exit when not found;
2643 raise notice '%', i;
2648 $$ language plpgsql;
2650 select exc_using(5);
2652 drop function exc_using(int);
2654 -- test FOR-over-cursor
2656 create or replace function forc01() returns void as $$
2658 c cursor(r1 integer, r2 integer)
2659 for select * from generate_series(r1,r2) i;
2661 for select * from generate_series(41,43) i;
2663 for r in c(5,7) loop
2664 raise notice '% from %', r.i, c;
2666 -- again, to test if cursor was closed properly
2667 for r in c(9,10) loop
2668 raise notice '% from %', r.i, c;
2670 -- and test a parameterless cursor
2672 raise notice '% from %', r.i, c2;
2674 -- and try it with a hand-assigned name
2675 raise notice 'after loop, c2 = %', c2;
2676 c2 := 'special_name';
2678 raise notice '% from %', r.i, c2;
2680 raise notice 'after loop, c2 = %', c2;
2681 -- and try it with a generated name
2682 -- (which we can't show in the output because it's variable)
2685 raise notice '%', r.i;
2687 raise notice 'after loop, c2 = %', c2;
2690 $$ language plpgsql;
2694 -- try updating the cursor's current row
2696 create temp table forc_test as
2697 select n as i, n as j from generate_series(1,10) n;
2699 create or replace function forc01() returns void as $$
2701 c cursor for select * from forc_test;
2704 raise notice '%, %', r.i, r.j;
2705 update forc_test set i = i * 100, j = r.j * 2 where current of c;
2708 $$ language plpgsql;
2712 select * from forc_test;
2714 -- same, with a cursor whose portal name doesn't match variable name
2715 create or replace function forc01() returns void as $$
2717 c refcursor := 'fooled_ya';
2720 open c for select * from forc_test;
2723 exit when not found;
2724 raise notice '%, %', r.i, r.j;
2725 update forc_test set i = i * 100, j = r.j * 2 where current of c;
2728 $$ language plpgsql;
2732 select * from forc_test;
2734 drop function forc01();
2736 -- fail because cursor has no query bound to it
2738 create or replace function forc_bad() returns void as $$
2743 raise notice '%', r.i;
2746 $$ language plpgsql;
2748 -- test RETURN QUERY EXECUTE
2750 create or replace function return_dquery()
2751 returns setof int as $$
2753 return query execute 'select * from (values(10),(20)) f';
2754 return query execute 'select * from (values($1),($2)) f' using 40,50;
2756 $$ language plpgsql;
2758 select * from return_dquery();
2760 drop function return_dquery();
2762 -- test RETURN QUERY with dropped columns
2764 create table tabwithcols(a int, b int, c int, d int);
2765 insert into tabwithcols values(10,20,30,40),(50,60,70,80);
2767 create or replace function returnqueryf()
2768 returns setof tabwithcols as $$
2770 return query select * from tabwithcols;
2771 return query execute 'select * from tabwithcols';
2773 $$ language plpgsql;
2775 select * from returnqueryf();
2777 alter table tabwithcols drop column b;
2779 select * from returnqueryf();
2781 alter table tabwithcols drop column d;
2783 select * from returnqueryf();
2785 alter table tabwithcols add column d int;
2787 select * from returnqueryf();
2789 drop function returnqueryf();
2790 drop table tabwithcols;
2792 -- Tests for 8.4's new RAISE features
2794 create or replace function raise_test() returns void as $$
2796 raise notice '% % %', 1, 2, 3
2797 using errcode = '55001', detail = 'some detail info', hint = 'some hint';
2798 raise '% % %', 1, 2, 3
2799 using errcode = 'division_by_zero', detail = 'some detail info';
2801 $$ language plpgsql;
2803 select raise_test();
2805 -- Since we can't actually see the thrown SQLSTATE in default psql output,
2806 -- test it like this; this also tests re-RAISE
2808 create or replace function raise_test() returns void as $$
2811 using errcode = 'division_by_zero', detail = 'some detail info';
2814 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
2817 $$ language plpgsql;
2819 select raise_test();
2821 create or replace function raise_test() returns void as $$
2824 using errcode = '1234F', detail = 'some detail info';
2827 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
2830 $$ language plpgsql;
2832 select raise_test();
2834 -- SQLSTATE specification in WHEN
2835 create or replace function raise_test() returns void as $$
2838 using errcode = '1234F', detail = 'some detail info';
2840 when sqlstate '1234F' then
2841 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
2844 $$ language plpgsql;
2846 select raise_test();
2848 create or replace function raise_test() returns void as $$
2850 raise division_by_zero using detail = 'some detail info';
2853 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
2856 $$ language plpgsql;
2858 select raise_test();
2860 create or replace function raise_test() returns void as $$
2862 raise division_by_zero;
2864 $$ language plpgsql;
2866 select raise_test();
2868 create or replace function raise_test() returns void as $$
2870 raise sqlstate '1234F';
2872 $$ language plpgsql;
2874 select raise_test();
2876 create or replace function raise_test() returns void as $$
2878 raise division_by_zero using message = 'custom' || ' message';
2880 $$ language plpgsql;
2882 select raise_test();
2884 create or replace function raise_test() returns void as $$
2886 raise using message = 'custom' || ' message', errcode = '22012';
2888 $$ language plpgsql;
2890 select raise_test();
2892 -- conflict on message
2893 create or replace function raise_test() returns void as $$
2895 raise notice 'some message' using message = 'custom' || ' message', errcode = '22012';
2897 $$ language plpgsql;
2899 select raise_test();
2901 -- conflict on errcode
2902 create or replace function raise_test() returns void as $$
2904 raise division_by_zero using message = 'custom' || ' message', errcode = '22012';
2906 $$ language plpgsql;
2908 select raise_test();
2910 -- nothing to re-RAISE
2911 create or replace function raise_test() returns void as $$
2915 $$ language plpgsql;
2917 select raise_test();
2919 -- check cases where implicit SQLSTATE variable could be confused with
2920 -- SQLSTATE as a keyword, cf bug #5524
2921 create or replace function raise_test() returns void as $$
2925 when sqlstate '22012' then
2926 raise notice using message = sqlstate;
2927 raise sqlstate '22012' using message = 'substitute message';
2929 $$ language plpgsql;
2931 select raise_test();
2933 drop function raise_test();
2935 -- test CASE statement
2937 create or replace function case_test(bigint) returns text as $$
2947 return 'three, four or eight';
2950 when a+b, a+b+1 then
2951 return 'eleven, twelve';
2954 $$ language plpgsql immutable;
2956 select case_test(1);
2957 select case_test(2);
2958 select case_test(3);
2959 select case_test(4);
2960 select case_test(5); -- fails
2961 select case_test(8);
2962 select case_test(10);
2963 select case_test(11);
2964 select case_test(12);
2965 select case_test(13); -- fails
2967 create or replace function catch() returns void as $$
2969 raise notice '%', case_test(6);
2971 when case_not_found then
2972 raise notice 'caught case_not_found % %', SQLSTATE, SQLERRM;
2974 $$ language plpgsql;
2978 -- test the searched variant too, as well as ELSE
2979 create or replace function case_test(bigint) returns text as $$
2985 when $1 = a + 2 then
2991 $$ language plpgsql immutable;
2993 select case_test(1);
2994 select case_test(2);
2995 select case_test(12);
2996 select case_test(13);
2998 drop function catch();
2999 drop function case_test(bigint);
3001 -- test variadic functions
3003 create or replace function vari(variadic int[])
3006 for i in array_lower($1,1)..array_upper($1,1) loop
3007 raise notice '%', $1[i];
3009 $$ language plpgsql;
3011 select vari(1,2,3,4,5);
3013 select vari(variadic array[5,6,7]);
3015 drop function vari(int[]);
3018 create or replace function pleast(variadic numeric[])
3019 returns numeric as $$
3020 declare aux numeric = $1[array_lower($1,1)];
3022 for i in array_lower($1,1)+1..array_upper($1,1) loop
3023 if $1[i] < aux then aux := $1[i]; end if;
3027 $$ language plpgsql immutable strict;
3029 select pleast(10,1,2,3,-16);
3030 select pleast(10.2,2.2,-1.1);
3031 select pleast(10.2,10, -20);
3032 select pleast(10,20, -1.0);
3034 -- in case of conflict, non-variadic version is preferred
3035 create or replace function pleast(numeric)
3036 returns numeric as $$
3038 raise notice 'non-variadic function called';
3041 $$ language plpgsql immutable strict;
3045 drop function pleast(numeric[]);
3046 drop function pleast(numeric);
3048 -- test table functions
3050 create function tftest(int) returns table(a int, b int) as $$
3052 return query select $1, $1+i from generate_series(1,5) g(i);
3054 $$ language plpgsql immutable strict;
3056 select * from tftest(10);
3058 create or replace function tftest(a1 int) returns table(a int, b int) as $$
3060 a := a1; b := a1 + 1;
3062 a := a1 * 10; b := a1 * 10 + 1;
3065 $$ language plpgsql immutable strict;
3067 select * from tftest(10);
3069 drop function tftest(int);
3071 create or replace function rttest()
3072 returns setof int as $$
3075 return query values(10),(20);
3076 get diagnostics rc = row_count;
3077 raise notice '% %', found, rc;
3078 return query select * from (values(10),(20)) f(a) where false;
3079 get diagnostics rc = row_count;
3080 raise notice '% %', found, rc;
3081 return query execute 'values(10),(20)';
3082 get diagnostics rc = row_count;
3083 raise notice '% %', found, rc;
3084 return query execute 'select * from (values(10),(20)) f(a) where false';
3085 get diagnostics rc = row_count;
3086 raise notice '% %', found, rc;
3088 $$ language plpgsql;
3090 select * from rttest();
3092 drop function rttest();
3094 -- Test for proper cleanup at subtransaction exit. This example
3095 -- exposed a bug in PG 8.2.
3097 CREATE FUNCTION leaker_1(fail BOOL) RETURNS INTEGER AS $$
3102 v_var := (leaker_2(fail)).error_code;
3104 WHEN others THEN RETURN 0;
3108 $$ LANGUAGE plpgsql;
3110 CREATE FUNCTION leaker_2(fail BOOL, OUT error_code INTEGER, OUT new_id INTEGER)
3111 RETURNS RECORD AS $$
3114 RAISE EXCEPTION 'fail ...';
3120 $$ LANGUAGE plpgsql;
3122 SELECT * FROM leaker_1(false);
3123 SELECT * FROM leaker_1(true);
3125 DROP FUNCTION leaker_1(bool);
3126 DROP FUNCTION leaker_2(bool);
3128 -- Test for appropriate cleanup of non-simple expression evaluations
3129 -- (bug in all versions prior to August 2010)
3131 CREATE FUNCTION nonsimple_expr_test() RETURNS text[] AS $$
3137 arr := array[array['foo','bar'], array['baz', 'quux']];
3140 -- use sub-SELECTs to make expressions non-simple
3141 arr[(SELECT i)][(SELECT i+1)] := (SELECT lr);
3144 $$ LANGUAGE plpgsql;
3146 SELECT nonsimple_expr_test();
3148 DROP FUNCTION nonsimple_expr_test();
3150 CREATE FUNCTION nonsimple_expr_test() RETURNS integer AS $$
3152 i integer NOT NULL := 0;
3155 i := (SELECT NULL::integer); -- should throw error
3158 i := (SELECT 1::integer);
3162 $$ LANGUAGE plpgsql;
3164 SELECT nonsimple_expr_test();
3166 DROP FUNCTION nonsimple_expr_test();
3168 -- Test handling of string literals.
3170 set standard_conforming_strings = off;
3172 create or replace function strtest() returns text as $$
3174 raise notice 'foo\\bar\041baz';
3175 return 'foo\\bar\041baz';
3177 $$ language plpgsql;
3181 create or replace function strtest() returns text as $$
3183 raise notice E'foo\\bar\041baz';
3184 return E'foo\\bar\041baz';
3186 $$ language plpgsql;
3190 set standard_conforming_strings = on;
3192 create or replace function strtest() returns text as $$
3194 raise notice 'foo\\bar\041baz\';
3195 return 'foo\\bar\041baz\';
3197 $$ language plpgsql;
3201 create or replace function strtest() returns text as $$
3203 raise notice E'foo\\bar\041baz';
3204 return E'foo\\bar\041baz';
3206 $$ language plpgsql;
3210 drop function strtest();
3212 -- Test anonymous code blocks.
3217 FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno
3219 RAISE NOTICE '%, %', r.roomno, r.comment;
3223 -- these are to check syntax error reporting
3224 DO LANGUAGE plpgsql $$begin return 1; end$$;
3229 FOR r IN SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno
3231 RAISE NOTICE '%, %', r.roomno, r.comment;
3235 -- Check variable scoping -- a var is not available in its own or prior
3236 -- default expressions.
3238 create function scope_test() returns int as $$
3239 declare x int := 42;
3241 declare y int := x + 1;
3247 $$ language plpgsql;
3249 select scope_test();
3251 drop function scope_test();
3253 -- Check handling of conflicts between plpgsql vars and table columns.
3255 set plpgsql.variable_conflict = error;
3257 create function conflict_test() returns setof int8_tbl as $$
3261 for r in select q1,q2 from int8_tbl loop
3265 $$ language plpgsql;
3267 select * from conflict_test();
3269 create or replace function conflict_test() returns setof int8_tbl as $$
3270 #variable_conflict use_variable
3274 for r in select q1,q2 from int8_tbl loop
3278 $$ language plpgsql;
3280 select * from conflict_test();
3282 create or replace function conflict_test() returns setof int8_tbl as $$
3283 #variable_conflict use_column
3287 for r in select q1,q2 from int8_tbl loop
3291 $$ language plpgsql;
3293 select * from conflict_test();
3295 drop function conflict_test();
3297 -- Check that an unreserved keyword can be used as a variable name
3299 create function unreserved_test() returns int as $$
3303 forward := forward * 2;
3306 $$ language plpgsql;
3308 select unreserved_test();
3310 drop function unreserved_test();