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 17 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 17 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 17 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 17 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 8 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 8 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 8 at PERFORM
2077
test_variable_storage
2078
-----------------------
2083
-- test foreign key error trapping
2085
create temp table master(f1 int primary key);
2086
create temp table slave(f1 int references master deferrable);
2087
insert into master values(1);
2088
insert into slave values(1);
2089
insert into slave values(2); -- fails
2090
ERROR: insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
2091
DETAIL: Key (f1)=(2) is not present in table "master".
2092
create function trap_foreign_key(int) returns int as $$
2094
begin -- start a subtransaction
2095
insert into slave values($1);
2097
when foreign_key_violation then
2098
raise notice 'caught foreign_key_violation';
2102
end$$ language plpgsql;
2103
create function trap_foreign_key_2() returns int as $$
2105
begin -- start a subtransaction
2106
set constraints all immediate;
2108
when foreign_key_violation then
2109
raise notice 'caught foreign_key_violation';
2113
end$$ language plpgsql;
2114
select trap_foreign_key(1);
2120
select trap_foreign_key(2); -- detects FK violation
2121
NOTICE: caught foreign_key_violation
2128
set constraints all deferred;
2129
select trap_foreign_key(2); -- should not detect FK violation
2136
set constraints all immediate; -- fails
2137
ERROR: insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
2138
DETAIL: Key (f1)=(2) is not present in table "master".
2140
select trap_foreign_key_2(); -- detects FK violation
2141
NOTICE: caught foreign_key_violation
2143
--------------------
2147
commit; -- still fails
2148
ERROR: insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
2149
DETAIL: Key (f1)=(2) is not present in table "master".
2150
drop function trap_foreign_key(int);
2151
drop function trap_foreign_key_2();
2153
-- Test proper snapshot handling in simple expressions
2155
create temp table users(login text, id serial);
2156
create function sp_id_user(a_login text) returns int as $$
2159
select into x id from users where login = a_login;
2160
if found then return x; end if;
2162
end$$ language plpgsql stable;
2163
insert into users values('user1');
2164
select sp_id_user('user1');
2170
select sp_id_user('userx');
2176
create function sp_add_user(a_login text) returns int as $$
2177
declare my_id_user int;
2179
my_id_user = sp_id_user( a_login );
2180
IF my_id_user > 0 THEN
2181
RETURN -1; -- error code for existing user
2183
INSERT INTO users ( login ) VALUES ( a_login );
2184
my_id_user = sp_id_user( a_login );
2185
IF my_id_user = 0 THEN
2186
RETURN -2; -- error code for insertion failure
2189
end$$ language plpgsql;
2190
select sp_add_user('user1');
2196
select sp_add_user('user2');
2202
select sp_add_user('user2');
2208
select sp_add_user('user3');
2214
select sp_add_user('user3');
2220
drop function sp_add_user(text);
2221
drop function sp_id_user(text);
2223
-- tests for refcursors
2225
create table rc_test (a int, b int);
2226
copy rc_test from stdin;
2227
create function return_refcursor(rc refcursor) returns refcursor as $$
2229
open rc for select a from rc_test;
2232
$$ language plpgsql;
2233
create function refcursor_test1(refcursor) returns refcursor as $$
2235
perform return_refcursor($1);
2238
$$ language plpgsql;
2240
select refcursor_test1('test1');
2246
fetch next in test1;
2252
select refcursor_test1('test2');
2258
fetch all from test2;
2268
fetch next from test1;
2269
ERROR: cursor "test1" does not exist
2270
create function refcursor_test2(int, int) returns boolean as $$
2272
c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
2276
fetch c1 into nonsense;
2284
$$ language plpgsql;
2285
select refcursor_test2(20000, 20000) as "Should be false",
2286
refcursor_test2(20, 20) as "Should be true";
2287
Should be false | Should be true
2288
-----------------+----------------
2293
-- tests for cursors with named parameter arguments
2295
create function namedparmcursor_test1(int, int) returns boolean as $$
2297
c1 cursor (param1 int, param12 int) for select * from rc_test where a > param1 and b > param12;
2300
open c1(param12 := $2, param1 := $1);
2301
fetch c1 into nonsense;
2309
$$ language plpgsql;
2310
select namedparmcursor_test1(20000, 20000) as "Should be false",
2311
namedparmcursor_test1(20, 20) as "Should be true";
2312
Should be false | Should be true
2313
-----------------+----------------
2317
-- mixing named and positional argument notations
2318
create function namedparmcursor_test2(int, int) returns boolean as $$
2320
c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
2323
open c1(param1 := $1, $2);
2324
fetch c1 into nonsense;
2332
$$ language plpgsql;
2333
select namedparmcursor_test2(20, 20);
2334
namedparmcursor_test2
2335
-----------------------
2339
-- mixing named and positional: param2 is given twice, once in named notation
2340
-- and second time in positional notation. Should throw an error at parse time
2341
create function namedparmcursor_test3() returns void as $$
2343
c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
2345
open c1(param2 := 20, 21);
2347
$$ language plpgsql;
2348
ERROR: value for parameter "param2" of cursor "c1" specified more than once
2349
LINE 5: open c1(param2 := 20, 21);
2351
-- mixing named and positional: same as previous test, but param1 is duplicated
2352
create function namedparmcursor_test4() returns void as $$
2354
c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
2356
open c1(20, param1 := 21);
2358
$$ language plpgsql;
2359
ERROR: value for parameter "param1" of cursor "c1" specified more than once
2360
LINE 5: open c1(20, param1 := 21);
2362
-- duplicate named parameter, should throw an error at parse time
2363
create function namedparmcursor_test5() returns void as $$
2365
c1 cursor (p1 int, p2 int) for
2366
select * from tenk1 where thousand = p1 and tenthous = p2;
2368
open c1 (p2 := 77, p2 := 42);
2370
$$ language plpgsql;
2371
ERROR: value for parameter "p2" of cursor "c1" specified more than once
2372
LINE 6: open c1 (p2 := 77, p2 := 42);
2374
-- not enough parameters, should throw an error at parse time
2375
create function namedparmcursor_test6() returns void as $$
2377
c1 cursor (p1 int, p2 int) for
2378
select * from tenk1 where thousand = p1 and tenthous = p2;
2382
$$ language plpgsql;
2383
ERROR: not enough arguments for cursor "c1"
2384
LINE 6: open c1 (p2 := 77);
2386
-- division by zero runtime error, the context given in the error message
2387
-- should be sensible
2388
create function namedparmcursor_test7() returns void as $$
2390
c1 cursor (p1 int, p2 int) for
2391
select * from tenk1 where thousand = p1 and tenthous = p2;
2393
open c1 (p2 := 77, p1 := 42/0);
2394
end $$ language plpgsql;
2395
select namedparmcursor_test7();
2396
ERROR: division by zero
2397
CONTEXT: SQL statement "SELECT 42/0 AS p1, 77 AS p2;"
2398
PL/pgSQL function namedparmcursor_test7() line 6 at OPEN
2399
-- check that line comments work correctly within the argument list (there
2400
-- is some special handling of this case in the code: the newline after the
2401
-- comment must be preserved when the argument-evaluating query is
2402
-- constructed, otherwise the comment effectively comments out the next
2404
create function namedparmcursor_test8() returns int4 as $$
2406
c1 cursor (p1 int, p2 int) for
2407
select count(*) from tenk1 where thousand = p1 and tenthous = p2;
2414
end $$ language plpgsql;
2415
select namedparmcursor_test8();
2416
namedparmcursor_test8
2417
-----------------------
2421
-- cursor parameter name can match plpgsql variable or unreserved keyword
2422
create function namedparmcursor_test9(p1 int) returns int4 as $$
2424
c1 cursor (p1 int, p2 int, debug int) for
2425
select count(*) from tenk1 where thousand = p1 and tenthous = p2
2430
open c1 (p1 := p1, p2 := p2, debug := 2);
2433
end $$ language plpgsql;
2434
select namedparmcursor_test9(6);
2435
namedparmcursor_test9
2436
-----------------------
2441
-- tests for "raise" processing
2443
create function raise_test1(int) returns int as $$
2445
raise notice 'This message has too many parameters!', $1;
2448
$$ language plpgsql;
2449
select raise_test1(5);
2450
ERROR: too many parameters specified for RAISE
2451
CONTEXT: PL/pgSQL function raise_test1(integer) line 3 at RAISE
2452
create function raise_test2(int) returns int as $$
2454
raise notice 'This message has too few parameters: %, %, %', $1, $1;
2457
$$ language plpgsql;
2458
select raise_test2(10);
2459
ERROR: too few parameters specified for RAISE
2460
CONTEXT: PL/pgSQL function raise_test2(integer) line 3 at RAISE
2461
-- Test re-RAISE inside a nested exception block. This case is allowed
2462
-- by Oracle's PL/SQL but was handled differently by PG before 9.1.
2463
CREATE FUNCTION reraise_test() RETURNS void AS $$
2468
WHEN syntax_error THEN
2470
raise notice 'exception % thrown in inner block, reraising', sqlerrm;
2474
raise notice 'RIGHT - exception % caught in inner block', sqlerrm;
2479
raise notice 'WRONG - exception % caught in outer block', sqlerrm;
2481
$$ LANGUAGE plpgsql;
2482
SELECT reraise_test();
2483
NOTICE: exception syntax_error thrown in inner block, reraising
2484
NOTICE: RIGHT - exception syntax_error caught in inner block
2491
-- reject function definitions that contain malformed SQL queries at
2492
-- compile-time, where possible
2494
create function bad_sql1() returns int as $$
2501
end$$ language plpgsql;
2502
ERROR: syntax error at or near "Johnny"
2503
LINE 5: Johnny Yuma;
2505
create function bad_sql2() returns int as $$
2508
for r in select I fought the law, the law won LOOP
2509
raise notice 'in loop';
2512
end;$$ language plpgsql;
2513
ERROR: syntax error at or near "the"
2514
LINE 4: for r in select I fought the law, the law won LOOP
2516
-- a RETURN expression is mandatory, except for void-returning
2517
-- functions, where it is not allowed
2518
create function missing_return_expr() returns int as $$
2521
end;$$ language plpgsql;
2522
ERROR: missing expression at or near ";"
2525
create function void_return_expr() returns void as $$
2528
end;$$ language plpgsql;
2529
ERROR: RETURN cannot have a parameter in function returning void
2532
-- VOID functions are allowed to omit RETURN
2533
create function void_return_expr() returns void as $$
2536
end;$$ language plpgsql;
2537
select void_return_expr();
2543
-- but ordinary functions are not
2544
create function missing_return_expr() returns int as $$
2547
end;$$ language plpgsql;
2548
select missing_return_expr();
2549
ERROR: control reached end of function without RETURN
2550
CONTEXT: PL/pgSQL function missing_return_expr()
2551
drop function void_return_expr();
2552
drop function missing_return_expr();
2554
-- EXECUTE ... INTO test
2556
create table eifoo (i integer, y integer);
2557
create type eitype as (i integer, y integer);
2558
create or replace function execute_into_test(varchar) returns record as $$
2567
execute 'insert into '||$1||' values(10,15)';
2568
execute 'select (row).* from (select row(10,1)::eifoo) s' into _r;
2569
raise notice '% %', _r.i, _r.y;
2570
execute 'select * from '||$1||' limit 1' into _rt;
2571
raise notice '% %', _rt.i, _rt.y;
2572
execute 'select *, 20 from '||$1||' limit 1' into i, j, k;
2573
raise notice '% % %', i, j, k;
2574
execute 'select 1,2' into _v;
2576
end; $$ language plpgsql;
2577
select execute_into_test('eifoo');
2586
drop table eifoo cascade;
2587
drop type eitype cascade;
2589
-- SQLSTATE and SQLERRM test
2591
create function excpt_test1() returns void as $$
2593
raise notice '% %', sqlstate, sqlerrm;
2594
end; $$ language plpgsql;
2595
-- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
2597
select excpt_test1();
2598
ERROR: column "sqlstate" does not exist
2599
LINE 1: SELECT sqlstate
2601
QUERY: SELECT sqlstate
2602
CONTEXT: PL/pgSQL function excpt_test1() line 3 at RAISE
2603
create function excpt_test2() returns void as $$
2607
raise notice '% %', sqlstate, sqlerrm;
2610
end; $$ language plpgsql;
2612
select excpt_test2();
2613
ERROR: column "sqlstate" does not exist
2614
LINE 1: SELECT sqlstate
2616
QUERY: SELECT sqlstate
2617
CONTEXT: PL/pgSQL function excpt_test2() line 5 at RAISE
2618
create function excpt_test3() returns void as $$
2621
raise exception 'user exception';
2622
exception when others then
2623
raise notice 'caught exception % %', sqlstate, sqlerrm;
2625
raise notice '% %', sqlstate, sqlerrm;
2628
when substring_error then
2629
-- this exception handler shouldn't be invoked
2630
raise notice 'unexpected exception: % %', sqlstate, sqlerrm;
2631
when division_by_zero then
2632
raise notice 'caught exception % %', sqlstate, sqlerrm;
2634
raise notice '% %', sqlstate, sqlerrm;
2636
end; $$ language plpgsql;
2637
select excpt_test3();
2638
NOTICE: caught exception P0001 user exception
2639
NOTICE: P0001 user exception
2640
NOTICE: caught exception 22012 division by zero
2641
NOTICE: P0001 user exception
2647
drop function excpt_test1();
2648
drop function excpt_test2();
2649
drop function excpt_test3();
2650
-- parameters of raise stmt can be expressions
2651
create function raise_exprs() returns void as $$
2653
a integer[] = '{10,20,30}';
2658
raise notice '%; %; %; %; %; %', a, a[i], c, (select c || 'abc'), row(10,'aaa',NULL,30), NULL;
2659
end;$$ language plpgsql;
2660
select raise_exprs();
2661
NOTICE: {10,20,30}; 20; xyz; xyzabc; (10,aaa,,30); <NULL>
2667
drop function raise_exprs();
2668
-- continue statement
2669
create table conttesttbl(idx serial, v integer);
2670
insert into conttesttbl(v) values(10);
2671
insert into conttesttbl(v) values(20);
2672
insert into conttesttbl(v) values(30);
2673
insert into conttesttbl(v) values(40);
2674
create function continue_test1() returns void as $$
2675
declare _i integer = 0; _r record;
2677
raise notice '---1---';
2680
raise notice '%', _i;
2681
continue when _i < 10;
2685
raise notice '---2---';
2690
raise notice '%', _i;
2691
continue lbl when _i > 0;
2696
raise notice '---3---';
2700
continue the_loop when _i % 2 = 0;
2701
raise notice '%', _i;
2704
raise notice '---4---';
2705
for _i in 1..10 loop
2707
-- applies to outer loop, not the nested begin block
2708
continue when _i < 5;
2709
raise notice '%', _i;
2713
raise notice '---5---';
2714
for _r in select * from conttesttbl loop
2715
continue when _r.v <= 20;
2716
raise notice '%', _r.v;
2719
raise notice '---6---';
2720
for _r in execute 'select * from conttesttbl' loop
2721
continue when _r.v <= 20;
2722
raise notice '%', _r.v;
2725
raise notice '---7---';
2727
raise notice '%', _i;
2728
continue when _i = 3;
2731
raise notice '---8---';
2734
raise notice '%', _i;
2736
continue when _i = 3;
2739
raise notice '---9---';
2740
for _r in select * from conttesttbl order by v limit 1 loop
2741
raise notice '%', _r.v;
2745
raise notice '---10---';
2746
for _r in execute 'select * from conttesttbl order by v limit 1' loop
2747
raise notice '%', _r.v;
2750
end; $$ language plpgsql;
2751
select continue_test1();
2810
-- CONTINUE is only legal inside a loop
2811
create function continue_test2() returns void as $$
2818
$$ language plpgsql;
2820
select continue_test2();
2821
ERROR: CONTINUE cannot be used outside a loop
2822
CONTEXT: PL/pgSQL function continue_test2()
2823
-- CONTINUE can't reference the label of a named block
2824
create function continue_test3() returns void as $$
2829
continue begin_block1;
2833
$$ language plpgsql;
2835
select continue_test3();
2836
ERROR: CONTINUE cannot be used outside a loop
2837
CONTEXT: PL/pgSQL function continue_test3()
2838
drop function continue_test1();
2839
drop function continue_test2();
2840
drop function continue_test3();
2841
drop table conttesttbl;
2842
-- verbose end block and end loop
2843
create function end_label1() returns void as $$
2847
for _i in 1 .. 10 loop
2851
for _i in 1 .. 10 loop
2855
$$ language plpgsql;
2856
select end_label1();
2862
drop function end_label1();
2863
-- should fail: undefined end label
2864
create function end_label2() returns void as $$
2866
for _i in 1 .. 10 loop
2870
$$ language plpgsql;
2871
ERROR: label does not exist at or near "flbl1"
2872
LINE 5: end loop flbl1;
2874
-- should fail: end label does not match start label
2875
create function end_label3() returns void as $$
2879
for _i in 1 .. 10 loop
2881
end loop outer_label;
2883
$$ language plpgsql;
2884
ERROR: end label "outer_label" differs from block's label "inner_label"
2885
LINE 7: end loop outer_label;
2887
-- should fail: end label on a block without a start label
2888
create function end_label4() returns void as $$
2891
for _i in 1 .. 10 loop
2893
end loop outer_label;
2895
$$ language plpgsql;
2896
ERROR: end label "outer_label" specified for unlabelled block
2897
LINE 6: end loop outer_label;
2899
-- using list of scalars in fori and fore stmts
2900
create function for_vect() returns void as $proc$
2901
<<lbl>>declare a integer; b varchar; c varchar; r record;
2904
for i in 1 .. 3 loop
2905
raise notice '%', i;
2907
-- fore with record var
2908
for r in select gs as aa, 'BB' as bb, 'CC' as cc from generate_series(1,4) gs loop
2909
raise notice '% % %', r.aa, r.bb, r.cc;
2911
-- fore with single scalar
2912
for a in select gs from generate_series(1,4) gs loop
2913
raise notice '%', a;
2915
-- fore with multiple scalars
2916
for a,b,c in select gs, 'BB','CC' from generate_series(1,4) gs loop
2917
raise notice '% % %', a, b, c;
2919
-- using qualified names in fors, fore is enabled, disabled only for fori
2920
for lbl.a, lbl.b, lbl.c in execute $$select gs, 'bb','cc' from generate_series(1,4) gs$$ loop
2921
raise notice '% % %', a, b, c;
2924
$proc$ language plpgsql;
2950
-- regression test: verify that multiple uses of same plpgsql datum within
2951
-- a SQL command all get mapped to the same $n parameter. The return value
2952
-- of the SELECT is not important, we only care that it doesn't fail with
2953
-- a complaint about an ungrouped column reference.
2954
create function multi_datum_use(p1 int) returns bool as $$
2959
select into x,y unique1/p1, unique1/$1 from tenk1 group by unique1/p1;
2961
end$$ language plpgsql;
2962
select multi_datum_use(42);
2969
-- Test STRICT limiter in both planned and EXECUTE invocations.
2970
-- Note that a data-modifying query is quasi strict (disallow multi rows)
2971
-- by default in the planned case, but not in EXECUTE.
2973
create temp table foo (f1 int, f2 int);
2974
insert into foo values (1,2), (3,4);
2975
create or replace function footest() returns void as $$
2979
insert into foo values(5,6) returning * into x;
2980
raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2981
end$$ language plpgsql;
2983
NOTICE: x.f1 = 5, x.f2 = 6
2989
create or replace function footest() returns void as $$
2992
-- should fail due to implicit strict
2993
insert into foo values(7,8),(9,10) returning * into x;
2994
raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2995
end$$ language plpgsql;
2997
ERROR: query returned more than one row
2998
CONTEXT: PL/pgSQL function footest() line 5 at SQL statement
2999
create or replace function footest() returns void as $$
3003
execute 'insert into foo values(5,6) returning *' into x;
3004
raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
3005
end$$ language plpgsql;
3007
NOTICE: x.f1 = 5, x.f2 = 6
3013
create or replace function footest() returns void as $$
3016
-- this should work since EXECUTE isn't as picky
3017
execute 'insert into foo values(7,8),(9,10) returning *' into x;
3018
raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
3019
end$$ language plpgsql;
3021
NOTICE: x.f1 = 7, x.f2 = 8
3038
create or replace function footest() returns void as $$
3042
select * from foo where f1 = 3 into strict x;
3043
raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
3044
end$$ language plpgsql;
3046
NOTICE: x.f1 = 3, x.f2 = 4
3052
create or replace function footest() returns void as $$
3055
-- should fail, no rows
3056
select * from foo where f1 = 0 into strict x;
3057
raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
3058
end$$ language plpgsql;
3060
ERROR: query returned no rows
3061
CONTEXT: PL/pgSQL function footest() line 5 at SQL statement
3062
create or replace function footest() returns void as $$
3065
-- should fail, too many rows
3066
select * from foo where f1 > 3 into strict x;
3067
raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
3068
end$$ language plpgsql;
3070
ERROR: query returned more than one row
3071
CONTEXT: PL/pgSQL function footest() line 5 at SQL statement
3072
create or replace function footest() returns void as $$
3076
execute 'select * from foo where f1 = 3' into strict x;
3077
raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
3078
end$$ language plpgsql;
3080
NOTICE: x.f1 = 3, x.f2 = 4
3086
create or replace function footest() returns void as $$
3089
-- should fail, no rows
3090
execute 'select * from foo where f1 = 0' into strict x;
3091
raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
3092
end$$ language plpgsql;
3094
ERROR: query returned no rows
3095
CONTEXT: PL/pgSQL function footest() line 5 at EXECUTE statement
3096
create or replace function footest() returns void as $$
3099
-- should fail, too many rows
3100
execute 'select * from foo where f1 > 3' into strict x;
3101
raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
3102
end$$ language plpgsql;
3104
ERROR: query returned more than one row
3105
CONTEXT: PL/pgSQL function footest() line 5 at EXECUTE statement
3106
drop function footest();
3107
-- test scrollable cursor support
3108
create function sc_test() returns setof integer as $$
3110
c scroll cursor for select f1 from int4_tbl;
3114
fetch last from c into x;
3117
fetch prior from c into x;
3121
$$ language plpgsql;
3122
select * from sc_test();
3132
create or replace function sc_test() returns setof integer as $$
3134
c no scroll cursor for select f1 from int4_tbl;
3138
fetch last from c into x;
3141
fetch prior from c into x;
3145
$$ language plpgsql;
3146
select * from sc_test(); -- fails because of NO SCROLL specification
3147
ERROR: cursor can only scan forward
3148
HINT: Declare it with SCROLL option to enable backward scan.
3149
CONTEXT: PL/pgSQL function sc_test() line 7 at FETCH
3150
create or replace function sc_test() returns setof integer as $$
3155
open c scroll for select f1 from int4_tbl;
3156
fetch last from c into x;
3159
fetch prior from c into x;
3163
$$ language plpgsql;
3164
select * from sc_test();
3174
create or replace function sc_test() returns setof integer as $$
3179
open c scroll for execute 'select f1 from int4_tbl';
3180
fetch last from c into x;
3183
fetch relative -2 from c into x;
3187
$$ language plpgsql;
3188
select * from sc_test();
3196
create or replace function sc_test() returns setof integer as $$
3201
open c scroll for execute 'select f1 from int4_tbl';
3202
fetch last from c into x;
3205
move backward 2 from c;
3206
fetch relative -1 from c into x;
3210
$$ language plpgsql;
3211
select * from sc_test();
3218
create or replace function sc_test() returns setof integer as $$
3220
c cursor for select * from generate_series(1, 10);
3225
move relative 2 in c;
3229
fetch next from c into x;
3236
$$ language plpgsql;
3237
select * from sc_test();
3245
create or replace function sc_test() returns setof integer as $$
3247
c cursor for select * from generate_series(1, 10);
3251
move forward all in c;
3252
fetch backward from c into x;
3258
$$ language plpgsql;
3259
select * from sc_test();
3265
drop function sc_test();
3266
-- test qualified variable names
3267
create function pl_qual_names (param1 int) returns void as $$
3276
raise notice 'param1 = %', param1;
3277
raise notice 'pl_qual_names.param1 = %', pl_qual_names.param1;
3278
raise notice 'outerblock.param1 = %', outerblock.param1;
3279
raise notice 'innerblock.param1 = %', innerblock.param1;
3282
$$ language plpgsql;
3283
select pl_qual_names(42);
3285
NOTICE: pl_qual_names.param1 = 42
3286
NOTICE: outerblock.param1 = 1
3287
NOTICE: innerblock.param1 = 2
3293
drop function pl_qual_names(int);
3294
-- tests for RETURN QUERY
3295
create function ret_query1(out int, out int) returns setof record as $$
3300
return query select x + 1, x * 10 from generate_series(0, 10) s (x);
3303
$$ language plpgsql;
3304
select * from ret_query1();
3322
create type record_type as (x text, y int, z boolean);
3323
create or replace function ret_query2(lim int) returns setof record_type as $$
3325
return query select md5(s.x::text), s.x, s.x > 0
3326
from generate_series(-8, lim) s (x) where s.x % 2 = 0;
3328
$$ language plpgsql;
3329
select * from ret_query2(8);
3331
----------------------------------+----+---
3332
a8d2ec85eaf98407310b72eb73dda247 | -8 | f
3333
596a3d04481816330f07e4f97510c28f | -6 | f
3334
0267aaf632e87a63288a08331f22c7c3 | -4 | f
3335
5d7b9adcbe1c629ec722529dd12e5129 | -2 | f
3336
cfcd208495d565ef66e7dff9f98764da | 0 | f
3337
c81e728d9d4c2f636f067f89cc14862c | 2 | t
3338
a87ff679a2f3e71d9181a67b7542122c | 4 | t
3339
1679091c5a880faf6fb5e6087eb1b2dc | 6 | t
3340
c9f0f895fb98ab9159f51fd0297e236d | 8 | t
3343
-- test EXECUTE USING
3344
create function exc_using(int, text) returns int as $$
3347
for i in execute 'select * from generate_series(1,$1)' using $1+1 loop
3348
raise notice '%', i;
3350
execute 'select $2 + $2*3 + length($1)' into i using $2,$1;
3353
$$ language plpgsql;
3354
select exc_using(5, 'foobar');
3366
drop function exc_using(int, text);
3367
create or replace function exc_using(int) returns void as $$
3372
open c for execute 'select * from generate_series(1,$1)' using $1+1;
3375
exit when not found;
3376
raise notice '%', i;
3381
$$ language plpgsql;
3382
select exc_using(5);
3394
drop function exc_using(int);
3395
-- test FOR-over-cursor
3396
create or replace function forc01() returns void as $$
3398
c cursor(r1 integer, r2 integer)
3399
for select * from generate_series(r1,r2) i;
3401
for select * from generate_series(41,43) i;
3403
for r in c(5,7) loop
3404
raise notice '% from %', r.i, c;
3406
-- again, to test if cursor was closed properly
3407
for r in c(9,10) loop
3408
raise notice '% from %', r.i, c;
3410
-- and test a parameterless cursor
3412
raise notice '% from %', r.i, c2;
3414
-- and try it with a hand-assigned name
3415
raise notice 'after loop, c2 = %', c2;
3416
c2 := 'special_name';
3418
raise notice '% from %', r.i, c2;
3420
raise notice 'after loop, c2 = %', c2;
3421
-- and try it with a generated name
3422
-- (which we can't show in the output because it's variable)
3425
raise notice '%', r.i;
3427
raise notice 'after loop, c2 = %', c2;
3430
$$ language plpgsql;
3440
NOTICE: after loop, c2 = c2
3441
NOTICE: 41 from special_name
3442
NOTICE: 42 from special_name
3443
NOTICE: 43 from special_name
3444
NOTICE: after loop, c2 = special_name
3448
NOTICE: after loop, c2 = <NULL>
3454
-- try updating the cursor's current row
3455
create temp table forc_test as
3456
select n as i, n as j from generate_series(1,10) n;
3457
create or replace function forc01() returns void as $$
3459
c cursor for select * from forc_test;
3462
raise notice '%, %', r.i, r.j;
3463
update forc_test set i = i * 100, j = r.j * 2 where current of c;
3466
$$ language plpgsql;
3483
select * from forc_test;
3498
-- same, with a cursor whose portal name doesn't match variable name
3499
create or replace function forc01() returns void as $$
3501
c refcursor := 'fooled_ya';
3504
open c for select * from forc_test;
3507
exit when not found;
3508
raise notice '%, %', r.i, r.j;
3509
update forc_test set i = i * 100, j = r.j * 2 where current of c;
3512
$$ language plpgsql;
3529
select * from forc_test;
3544
drop function forc01();
3545
-- fail because cursor has no query bound to it
3546
create or replace function forc_bad() returns void as $$
3551
raise notice '%', r.i;
3554
$$ language plpgsql;
3555
ERROR: cursor FOR loop must use a bound cursor variable
3556
LINE 5: for r in c loop
3558
-- test RETURN QUERY EXECUTE
3559
create or replace function return_dquery()
3560
returns setof int as $$
3562
return query execute 'select * from (values(10),(20)) f';
3563
return query execute 'select * from (values($1),($2)) f' using 40,50;
3565
$$ language plpgsql;
3566
select * from return_dquery();
3575
drop function return_dquery();
3576
-- test RETURN QUERY with dropped columns
3577
create table tabwithcols(a int, b int, c int, d int);
3578
insert into tabwithcols values(10,20,30,40),(50,60,70,80);
3579
create or replace function returnqueryf()
3580
returns setof tabwithcols as $$
3582
return query select * from tabwithcols;
3583
return query execute 'select * from tabwithcols';
3585
$$ language plpgsql;
3586
select * from returnqueryf();
3595
alter table tabwithcols drop column b;
3596
select * from returnqueryf();
3605
alter table tabwithcols drop column d;
3606
select * from returnqueryf();
3615
alter table tabwithcols add column d int;
3616
select * from returnqueryf();
3625
drop function returnqueryf();
3626
drop table tabwithcols;
3628
-- Tests for composite-type results
3630
create type footype as (x int, y varchar);
3631
-- test: use of variable of composite type in return statement
3632
create or replace function foo() returns footype as $$
3639
$$ language plpgsql;
3646
-- test: use of variable of record type in return statement
3647
create or replace function foo() returns footype as $$
3651
v := (1, 'hello'::varchar);
3654
$$ language plpgsql;
3661
-- test: use of row expr in return statement
3662
create or replace function foo() returns footype as $$
3664
return (1, 'hello'::varchar);
3666
$$ language plpgsql;
3673
-- this does not work currently (no implicit casting)
3674
create or replace function foo() returns footype as $$
3676
return (1, 'hello');
3678
$$ language plpgsql;
3680
ERROR: returned record type does not match expected record type
3681
DETAIL: Returned type unknown does not match expected type character varying in column 2.
3682
CONTEXT: PL/pgSQL function foo() while casting return value to function's return type
3683
-- ... but this does
3684
create or replace function foo() returns footype as $$
3686
return (1, 'hello')::footype;
3688
$$ language plpgsql;
3695
drop function foo();
3696
-- test: return a row expr as record.
3697
create or replace function foorec() returns record as $$
3704
$$ language plpgsql;
3711
-- test: return row expr in return statement.
3712
create or replace function foorec() returns record as $$
3714
return (1, 'hello');
3716
$$ language plpgsql;
3723
drop function foorec();
3724
-- test: row expr in RETURN NEXT statement.
3725
create or replace function foo() returns setof footype as $$
3729
return next (1, 'hello'::varchar);
3731
return next null::footype;
3732
return next (2, 'goodbye')::footype;
3734
$$ language plpgsql;
3735
select * from foo();
3745
drop function foo();
3746
-- test: use invalid expr in return statement.
3747
create or replace function foo() returns footype as $$
3751
$$ language plpgsql;
3753
ERROR: cannot return non-composite value from function returning composite type
3754
CONTEXT: PL/pgSQL function foo() line 3 at RETURN
3755
drop function foo();
3758
-- Tests for 8.4's new RAISE features
3760
create or replace function raise_test() returns void as $$
3762
raise notice '% % %', 1, 2, 3
3763
using errcode = '55001', detail = 'some detail info', hint = 'some hint';
3764
raise '% % %', 1, 2, 3
3765
using errcode = 'division_by_zero', detail = 'some detail info';
3767
$$ language plpgsql;
3768
select raise_test();
3770
DETAIL: some detail info
3773
DETAIL: some detail info
3774
-- Since we can't actually see the thrown SQLSTATE in default psql output,
3775
-- test it like this; this also tests re-RAISE
3776
create or replace function raise_test() returns void as $$
3779
using errcode = 'division_by_zero', detail = 'some detail info';
3782
raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3785
$$ language plpgsql;
3786
select raise_test();
3787
NOTICE: SQLSTATE: 22012 SQLERRM: check me
3789
DETAIL: some detail info
3790
create or replace function raise_test() returns void as $$
3793
using errcode = '1234F', detail = 'some detail info';
3796
raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3799
$$ language plpgsql;
3800
select raise_test();
3801
NOTICE: SQLSTATE: 1234F SQLERRM: check me
3803
DETAIL: some detail info
3804
-- SQLSTATE specification in WHEN
3805
create or replace function raise_test() returns void as $$
3808
using errcode = '1234F', detail = 'some detail info';
3810
when sqlstate '1234F' then
3811
raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3814
$$ language plpgsql;
3815
select raise_test();
3816
NOTICE: SQLSTATE: 1234F SQLERRM: check me
3818
DETAIL: some detail info
3819
create or replace function raise_test() returns void as $$
3821
raise division_by_zero using detail = 'some detail info';
3824
raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3827
$$ language plpgsql;
3828
select raise_test();
3829
NOTICE: SQLSTATE: 22012 SQLERRM: division_by_zero
3830
ERROR: division_by_zero
3831
DETAIL: some detail info
3832
create or replace function raise_test() returns void as $$
3834
raise division_by_zero;
3836
$$ language plpgsql;
3837
select raise_test();
3838
ERROR: division_by_zero
3839
create or replace function raise_test() returns void as $$
3841
raise sqlstate '1234F';
3843
$$ language plpgsql;
3844
select raise_test();
3846
create or replace function raise_test() returns void as $$
3848
raise division_by_zero using message = 'custom' || ' message';
3850
$$ language plpgsql;
3851
select raise_test();
3852
ERROR: custom message
3853
create or replace function raise_test() returns void as $$
3855
raise using message = 'custom' || ' message', errcode = '22012';
3857
$$ language plpgsql;
3858
select raise_test();
3859
ERROR: custom message
3860
-- conflict on message
3861
create or replace function raise_test() returns void as $$
3863
raise notice 'some message' using message = 'custom' || ' message', errcode = '22012';
3865
$$ language plpgsql;
3866
select raise_test();
3867
ERROR: RAISE option already specified: MESSAGE
3868
CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3869
-- conflict on errcode
3870
create or replace function raise_test() returns void as $$
3872
raise division_by_zero using message = 'custom' || ' message', errcode = '22012';
3874
$$ language plpgsql;
3875
select raise_test();
3876
ERROR: RAISE option already specified: ERRCODE
3877
CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3878
-- nothing to re-RAISE
3879
create or replace function raise_test() returns void as $$
3883
$$ language plpgsql;
3884
select raise_test();
3885
ERROR: RAISE without parameters cannot be used outside an exception handler
3886
CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3887
-- test access to exception data
3888
create function zero_divide() returns int as $$
3893
$$ language plpgsql;
3894
create or replace function raise_test() returns void as $$
3896
raise exception 'custom exception'
3897
using detail = 'some detail of custom exception',
3898
hint = 'some hint related to custom exception';
3900
$$ language plpgsql;
3901
create function stacked_diagnostics_test() returns void as $$
3902
declare _sqlstate text;
3906
perform zero_divide();
3907
exception when others then
3908
get stacked diagnostics
3909
_sqlstate = returned_sqlstate,
3910
_message = message_text,
3911
_context = pg_exception_context;
3912
raise notice 'sqlstate: %, message: %, context: [%]',
3913
_sqlstate, _message, replace(_context, E'\n', ' <- ');
3915
$$ language plpgsql;
3916
select stacked_diagnostics_test();
3917
NOTICE: sqlstate: 22012, message: division by zero, context: [PL/pgSQL function zero_divide() line 4 at RETURN <- SQL statement "SELECT zero_divide()" <- PL/pgSQL function stacked_diagnostics_test() line 6 at PERFORM]
3918
stacked_diagnostics_test
3919
--------------------------
3923
create or replace function stacked_diagnostics_test() returns void as $$
3924
declare _detail text;
3928
perform raise_test();
3929
exception when others then
3930
get stacked diagnostics
3931
_message = message_text,
3932
_detail = pg_exception_detail,
3933
_hint = pg_exception_hint;
3934
raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
3936
$$ language plpgsql;
3937
select stacked_diagnostics_test();
3938
NOTICE: message: custom exception, detail: some detail of custom exception, hint: some hint related to custom exception
3939
stacked_diagnostics_test
3940
--------------------------
3944
-- fail, cannot use stacked diagnostics statement outside handler
3945
create or replace function stacked_diagnostics_test() returns void as $$
3946
declare _detail text;
3950
get stacked diagnostics
3951
_message = message_text,
3952
_detail = pg_exception_detail,
3953
_hint = pg_exception_hint;
3954
raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
3956
$$ language plpgsql;
3957
select stacked_diagnostics_test();
3958
ERROR: GET STACKED DIAGNOSTICS cannot be used outside an exception handler
3959
CONTEXT: PL/pgSQL function stacked_diagnostics_test() line 6 at GET DIAGNOSTICS
3960
drop function zero_divide();
3961
drop function stacked_diagnostics_test();
3962
-- check cases where implicit SQLSTATE variable could be confused with
3963
-- SQLSTATE as a keyword, cf bug #5524
3964
create or replace function raise_test() returns void as $$
3968
when sqlstate '22012' then
3969
raise notice using message = sqlstate;
3970
raise sqlstate '22012' using message = 'substitute message';
3972
$$ language plpgsql;
3973
select raise_test();
3975
ERROR: substitute message
3976
drop function raise_test();
3977
-- test CASE statement
3978
create or replace function case_test(bigint) returns text as $$
3988
return 'three, four or eight';
3991
when a+b, a+b+1 then
3992
return 'eleven, twelve';
3995
$$ language plpgsql immutable;
3996
select case_test(1);
4002
select case_test(2);
4008
select case_test(3);
4010
----------------------
4011
three, four or eight
4014
select case_test(4);
4016
----------------------
4017
three, four or eight
4020
select case_test(5); -- fails
4021
ERROR: case not found
4022
HINT: CASE statement is missing ELSE part.
4023
CONTEXT: PL/pgSQL function case_test(bigint) line 5 at CASE
4024
select case_test(8);
4026
----------------------
4027
three, four or eight
4030
select case_test(10);
4036
select case_test(11);
4042
select case_test(12);
4048
select case_test(13); -- fails
4049
ERROR: case not found
4050
HINT: CASE statement is missing ELSE part.
4051
CONTEXT: PL/pgSQL function case_test(bigint) line 5 at CASE
4052
create or replace function catch() returns void as $$
4054
raise notice '%', case_test(6);
4056
when case_not_found then
4057
raise notice 'caught case_not_found % %', SQLSTATE, SQLERRM;
4059
$$ language plpgsql;
4061
NOTICE: caught case_not_found 20000 case not found
4067
-- test the searched variant too, as well as ELSE
4068
create or replace function case_test(bigint) returns text as $$
4074
when $1 = a + 2 then
4080
$$ language plpgsql immutable;
4081
select case_test(1);
4087
select case_test(2);
4093
select case_test(12);
4099
select case_test(13);
4105
drop function catch();
4106
drop function case_test(bigint);
4107
-- test variadic functions
4108
create or replace function vari(variadic int[])
4111
for i in array_lower($1,1)..array_upper($1,1) loop
4112
raise notice '%', $1[i];
4114
$$ language plpgsql;
4115
select vari(1,2,3,4,5);
4135
select vari(variadic array[5,6,7]);
4144
drop function vari(int[]);
4146
create or replace function pleast(variadic numeric[])
4147
returns numeric as $$
4148
declare aux numeric = $1[array_lower($1,1)];
4150
for i in array_lower($1,1)+1..array_upper($1,1) loop
4151
if $1[i] < aux then aux := $1[i]; end if;
4155
$$ language plpgsql immutable strict;
4156
select pleast(10,1,2,3,-16);
4162
select pleast(10.2,2.2,-1.1);
4168
select pleast(10.2,10, -20);
4174
select pleast(10,20, -1.0);
4180
-- in case of conflict, non-variadic version is preferred
4181
create or replace function pleast(numeric)
4182
returns numeric as $$
4184
raise notice 'non-variadic function called';
4187
$$ language plpgsql immutable strict;
4189
NOTICE: non-variadic function called
4195
drop function pleast(numeric[]);
4196
drop function pleast(numeric);
4197
-- test table functions
4198
create function tftest(int) returns table(a int, b int) as $$
4200
return query select $1, $1+i from generate_series(1,5) g(i);
4202
$$ language plpgsql immutable strict;
4203
select * from tftest(10);
4213
create or replace function tftest(a1 int) returns table(a int, b int) as $$
4215
a := a1; b := a1 + 1;
4217
a := a1 * 10; b := a1 * 10 + 1;
4220
$$ language plpgsql immutable strict;
4221
select * from tftest(10);
4228
drop function tftest(int);
4229
create or replace function rttest()
4230
returns setof int as $$
4233
return query values(10),(20);
4234
get diagnostics rc = row_count;
4235
raise notice '% %', found, rc;
4236
return query select * from (values(10),(20)) f(a) where false;
4237
get diagnostics rc = row_count;
4238
raise notice '% %', found, rc;
4239
return query execute 'values(10),(20)';
4240
get diagnostics rc = row_count;
4241
raise notice '% %', found, rc;
4242
return query execute 'select * from (values(10),(20)) f(a) where false';
4243
get diagnostics rc = row_count;
4244
raise notice '% %', found, rc;
4246
$$ language plpgsql;
4247
select * from rttest();
4260
drop function rttest();
4261
-- Test for proper cleanup at subtransaction exit. This example
4262
-- exposed a bug in PG 8.2.
4263
CREATE FUNCTION leaker_1(fail BOOL) RETURNS INTEGER AS $$
4268
v_var := (leaker_2(fail)).error_code;
4270
WHEN others THEN RETURN 0;
4274
$$ LANGUAGE plpgsql;
4275
CREATE FUNCTION leaker_2(fail BOOL, OUT error_code INTEGER, OUT new_id INTEGER)
4276
RETURNS RECORD AS $$
4279
RAISE EXCEPTION 'fail ...';
4285
$$ LANGUAGE plpgsql;
4286
SELECT * FROM leaker_1(false);
4292
SELECT * FROM leaker_1(true);
4298
DROP FUNCTION leaker_1(bool);
4299
DROP FUNCTION leaker_2(bool);
4300
-- Test for appropriate cleanup of non-simple expression evaluations
4301
-- (bug in all versions prior to August 2010)
4302
CREATE FUNCTION nonsimple_expr_test() RETURNS text[] AS $$
4308
arr := array[array['foo','bar'], array['baz', 'quux']];
4311
-- use sub-SELECTs to make expressions non-simple
4312
arr[(SELECT i)][(SELECT i+1)] := (SELECT lr);
4315
$$ LANGUAGE plpgsql;
4316
SELECT nonsimple_expr_test();
4318
-------------------------
4319
{{foo,fool},{baz,quux}}
4322
DROP FUNCTION nonsimple_expr_test();
4323
CREATE FUNCTION nonsimple_expr_test() RETURNS integer AS $$
4325
i integer NOT NULL := 0;
4328
i := (SELECT NULL::integer); -- should throw error
4331
i := (SELECT 1::integer);
4335
$$ LANGUAGE plpgsql;
4336
SELECT nonsimple_expr_test();
4338
---------------------
4342
DROP FUNCTION nonsimple_expr_test();
4344
-- Test cases involving recursion and error recovery in simple expressions
4345
-- (bugs in all versions before October 2010). The problems are most
4346
-- easily exposed by mutual recursion between plpgsql and sql functions.
4348
create function recurse(float8) returns float8 as
4352
return sql_recurse($1 - 1);
4357
$$ language plpgsql;
4358
-- "limit" is to prevent this from being inlined
4359
create function sql_recurse(float8) returns float8 as
4360
$$ select recurse($1) limit 1; $$ language sql;
4367
create function error1(text) returns text language sql as
4368
$$ SELECT relname::text FROM pg_class c WHERE c.oid = $1::regclass $$;
4369
create function error2(p_name_table text) returns text language plpgsql as $$
4371
return error1(p_name_table);
4374
create table public.stuffs (stuff text);
4376
select error2('nonexistent.stuffs');
4377
ERROR: schema "nonexistent" does not exist
4378
CONTEXT: SQL function "error1" statement 1
4379
PL/pgSQL function error2(text) line 3 at RETURN
4381
select error2('public.stuffs');
4388
drop function error2(p_name_table text);
4389
drop function error1(text);
4390
-- Test for consistent reporting of error context
4391
create function fail() returns int language plpgsql as $$
4397
ERROR: division by zero
4398
CONTEXT: SQL statement "SELECT 1/0"
4399
PL/pgSQL function fail() line 3 at RETURN
4401
ERROR: division by zero
4402
CONTEXT: SQL statement "SELECT 1/0"
4403
PL/pgSQL function fail() line 3 at RETURN
4404
drop function fail();
4405
-- Test handling of string literals.
4406
set standard_conforming_strings = off;
4407
create or replace function strtest() returns text as $$
4409
raise notice 'foo\\bar\041baz';
4410
return 'foo\\bar\041baz';
4412
$$ language plpgsql;
4413
WARNING: nonstandard use of \\ in a string literal
4414
LINE 3: raise notice 'foo\\bar\041baz';
4416
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
4417
WARNING: nonstandard use of \\ in a string literal
4418
LINE 4: return 'foo\\bar\041baz';
4420
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
4421
WARNING: nonstandard use of \\ in a string literal
4422
LINE 4: return 'foo\\bar\041baz';
4424
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
4427
WARNING: nonstandard use of \\ in a string literal
4428
LINE 1: SELECT 'foo\\bar\041baz'
4430
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
4431
QUERY: SELECT 'foo\\bar\041baz'
4432
CONTEXT: PL/pgSQL function strtest() line 4 at RETURN
4438
create or replace function strtest() returns text as $$
4440
raise notice E'foo\\bar\041baz';
4441
return E'foo\\bar\041baz';
4443
$$ language plpgsql;
4451
set standard_conforming_strings = on;
4452
create or replace function strtest() returns text as $$
4454
raise notice 'foo\\bar\041baz\';
4455
return 'foo\\bar\041baz\';
4457
$$ language plpgsql;
4459
NOTICE: foo\\bar\041baz\
4465
create or replace function strtest() returns text as $$
4467
raise notice E'foo\\bar\041baz';
4468
return E'foo\\bar\041baz';
4470
$$ language plpgsql;
4478
drop function strtest();
4479
-- Test anonymous code blocks.
4483
FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno
4485
RAISE NOTICE '%, %', r.roomno, r.comment;
4488
NOTICE: 001, Entrance
4491
NOTICE: 004, Technical
4493
NOTICE: 102, Conference
4494
NOTICE: 103, Restroom
4495
NOTICE: 104, Technical
4498
-- these are to check syntax error reporting
4499
DO LANGUAGE plpgsql $$begin return 1; end$$;
4500
ERROR: RETURN cannot have a parameter in function returning void
4501
LINE 1: DO LANGUAGE plpgsql $$begin return 1; end$$;
4506
FOR r IN SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno
4508
RAISE NOTICE '%, %', r.roomno, r.comment;
4511
ERROR: column "foo" does not exist
4512
LINE 1: SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomn...
4514
QUERY: SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno
4515
CONTEXT: PL/pgSQL function inline_code_block line 4 at FOR over SELECT rows
4516
-- Check variable scoping -- a var is not available in its own or prior
4517
-- default expressions.
4518
create function scope_test() returns int as $$
4519
declare x int := 42;
4521
declare y int := x + 1;
4527
$$ language plpgsql;
4528
select scope_test();
4534
drop function scope_test();
4535
-- Check handling of conflicts between plpgsql vars and table columns.
4536
set plpgsql.variable_conflict = error;
4537
create function conflict_test() returns setof int8_tbl as $$
4541
for r in select q1,q2 from int8_tbl loop
4545
$$ language plpgsql;
4546
select * from conflict_test();
4547
ERROR: column reference "q1" is ambiguous
4548
LINE 1: select q1,q2 from int8_tbl
4550
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
4551
QUERY: select q1,q2 from int8_tbl
4552
CONTEXT: PL/pgSQL function conflict_test() line 5 at FOR over SELECT rows
4553
create or replace function conflict_test() returns setof int8_tbl as $$
4554
#variable_conflict use_variable
4558
for r in select q1,q2 from int8_tbl loop
4562
$$ language plpgsql;
4563
select * from conflict_test();
4565
----+-------------------
4567
42 | 4567890123456789
4569
42 | 4567890123456789
4570
42 | -4567890123456789
4573
create or replace function conflict_test() returns setof int8_tbl as $$
4574
#variable_conflict use_column
4578
for r in select q1,q2 from int8_tbl loop
4582
$$ language plpgsql;
4583
select * from conflict_test();
4585
------------------+-------------------
4587
123 | 4567890123456789
4588
4567890123456789 | 123
4589
4567890123456789 | 4567890123456789
4590
4567890123456789 | -4567890123456789
4593
drop function conflict_test();
4594
-- Check that an unreserved keyword can be used as a variable name
4595
create function unreserved_test() returns int as $$
4599
forward := forward * 2;
4602
$$ language plpgsql;
4603
select unreserved_test();
4609
drop function unreserved_test();
4611
-- Test FOREACH over arrays
4613
create function foreach_test(anyarray)
4617
foreach x in array $1
4619
raise notice '%', x;
4622
$$ language plpgsql;
4623
select foreach_test(ARRAY[1,2,3,4]);
4633
select foreach_test(ARRAY[[1,2],[3,4]]);
4643
create or replace function foreach_test(anyarray)
4647
foreach x slice 1 in array $1
4649
raise notice '%', x;
4652
$$ language plpgsql;
4654
select foreach_test(ARRAY[1,2,3,4]);
4655
ERROR: FOREACH ... SLICE loop variable must be of an array type
4656
CONTEXT: PL/pgSQL function foreach_test(anyarray) line 4 at FOREACH over array
4657
select foreach_test(ARRAY[[1,2],[3,4]]);
4658
ERROR: FOREACH ... SLICE loop variable must be of an array type
4659
CONTEXT: PL/pgSQL function foreach_test(anyarray) line 4 at FOREACH over array
4660
create or replace function foreach_test(anyarray)
4664
foreach x slice 1 in array $1
4666
raise notice '%', x;
4669
$$ language plpgsql;
4670
select foreach_test(ARRAY[1,2,3,4]);
4677
select foreach_test(ARRAY[[1,2],[3,4]]);
4685
-- higher level of slicing
4686
create or replace function foreach_test(anyarray)
4690
foreach x slice 2 in array $1
4692
raise notice '%', x;
4695
$$ language plpgsql;
4697
select foreach_test(ARRAY[1,2,3,4]);
4698
ERROR: slice dimension (2) is out of the valid range 0..1
4699
CONTEXT: PL/pgSQL function foreach_test(anyarray) line 4 at FOREACH over array
4701
select foreach_test(ARRAY[[1,2],[3,4]]);
4702
NOTICE: {{1,2},{3,4}}
4708
select foreach_test(ARRAY[[[1,2]],[[3,4]]]);
4716
create type xy_tuple AS (x int, y int);
4717
-- iteration over array of records
4718
create or replace function foreach_test(anyarray)
4722
foreach r in array $1
4724
raise notice '%', r;
4727
$$ language plpgsql;
4728
select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]);
4737
select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]);
4747
create or replace function foreach_test(anyarray)
4749
declare x int; y int;
4751
foreach x, y in array $1
4753
raise notice 'x = %, y = %', x, y;
4756
$$ language plpgsql;
4757
select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]);
4758
NOTICE: x = 10, y = 20
4759
NOTICE: x = 40, y = 69
4760
NOTICE: x = 35, y = 78
4766
select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]);
4767
NOTICE: x = 10, y = 20
4768
NOTICE: x = 40, y = 69
4769
NOTICE: x = 35, y = 78
4770
NOTICE: x = 88, y = 76
4776
-- slicing over array of composite types
4777
create or replace function foreach_test(anyarray)
4779
declare x xy_tuple[];
4781
foreach x slice 1 in array $1
4783
raise notice '%', x;
4786
$$ language plpgsql;
4787
select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]);
4788
NOTICE: {"(10,20)","(40,69)","(35,78)"}
4794
select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]);
4795
NOTICE: {"(10,20)","(40,69)"}
4796
NOTICE: {"(35,78)","(88,76)"}
4802
drop function foreach_test(anyarray);
4805
-- Assorted tests for array subscript assignment
4807
create temp table rtype (id int, ar text[]);
4808
create function arrayassign1() returns text[] language plpgsql as $$
4812
r := row(12, '{foo,bar,baz}')::rtype;
4813
r.ar[2] := 'replace';
4816
select arrayassign1();
4822
select arrayassign1(); -- try again to exercise internal caching
4828
create domain orderedarray as int[2]
4829
constraint sorted check (value[1] < value[2]);
4830
select '{1,2}'::orderedarray;
4836
select '{2,1}'::orderedarray; -- fail
4837
ERROR: value for domain orderedarray violates check constraint "sorted"
4838
create function testoa(x1 int, x2 int, x3 int) returns orderedarray
4839
language plpgsql as $$
4840
declare res orderedarray;
4842
res := array[x1, x2];
4846
select testoa(1,2,3);
4852
select testoa(1,2,3); -- try again to exercise internal caching
4858
select testoa(2,1,3); -- fail at initial assign
4859
ERROR: value for domain orderedarray violates check constraint "sorted"
4860
CONTEXT: PL/pgSQL function testoa(integer,integer,integer) line 4 at assignment
4861
select testoa(1,2,1); -- fail at update
4862
ERROR: value for domain orderedarray violates check constraint "sorted"
4863
CONTEXT: PL/pgSQL function testoa(integer,integer,integer) line 5 at assignment
4864
drop function arrayassign1();
4865
drop function testoa(x1 int, x2 int, x3 int);
added
removed
src/test/regress/expected/plpgsql.out
