| blob | 94a485f46b30aad38f57c6106e3b23cbaa1d00d6 |
1 --
2 -- PLPGSQL
3 --
4 -- Scenario:
5 --
6 -- A building with a modern TP cable installation where any
7 -- of the wall connectors can be used to plug in phones,
8 -- ethernet interfaces or local office hubs. The backside
9 -- of the wall connectors is wired to one of several patch-
10 -- fields in the building.
11 --
12 -- In the patchfields, there are hubs and all the slots
13 -- representing the wall connectors. In addition there are
14 -- slots that can represent a phone line from the central
15 -- phone system.
16 --
17 -- Triggers ensure consistency of the patching information.
18 --
19 -- Functions are used to build up powerful views that let
20 -- you look behind the wall when looking at a patchfield
21 -- or into a room.
22 --
23 create table Room (
24 roomno char(8),
25 comment text
26 );
27 create unique index Room_rno on Room using btree (roomno bpchar_ops);
28 create table WSlot (
29 slotname char(20),
30 roomno char(8),
31 slotlink char(20),
32 backlink char(20)
33 );
34 create unique index WSlot_name on WSlot using btree (slotname bpchar_ops);
35 create table PField (
36 name text,
37 comment text
38 );
39 create unique index PField_name on PField using btree (name text_ops);
40 create table PSlot (
41 slotname char(20),
42 pfname text,
43 slotlink char(20),
44 backlink char(20)
45 );
46 create unique index PSlot_name on PSlot using btree (slotname bpchar_ops);
47 create table PLine (
48 slotname char(20),
49 phonenumber char(20),
50 comment text,
51 backlink char(20)
52 );
53 create unique index PLine_name on PLine using btree (slotname bpchar_ops);
54 create table Hub (
55 name char(14),
56 comment text,
57 nslots integer
58 );
59 create unique index Hub_name on Hub using btree (name bpchar_ops);
60 create table HSlot (
61 slotname char(20),
62 hubname char(14),
63 slotno integer,
64 slotlink char(20)
65 );
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);
68 create table System (
69 name text,
70 comment text
71 );
72 create unique index System_name on System using btree (name text_ops);
73 create table IFace (
74 slotname char(20),
75 sysname text,
76 ifname text,
77 slotlink char(20)
78 );
79 create unique index IFace_name on IFace using btree (slotname bpchar_ops);
80 create table PHone (
81 slotname char(20),
82 comment text,
83 slotlink char(20)
84 );
85 create unique index PHone_name on PHone using btree (slotname bpchar_ops);
86 -- ************************************************************
87 -- *
88 -- * Trigger procedures and functions for the patchfield
89 -- * test of PL/pgSQL
90 -- *
91 -- ************************************************************
92 -- ************************************************************
93 -- * AFTER UPDATE on Room
94 -- * - If room no changes let wall slots follow
95 -- ************************************************************
96 create function tg_room_au() returns trigger as '
97 begin
98 if new.roomno != old.roomno then
99 update WSlot set roomno = new.roomno where roomno = old.roomno;
100 end if;
101 return new;
102 end;
103 ' language plpgsql;
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 '
111 begin
112 delete from WSlot where roomno = old.roomno;
113 return old;
114 end;
115 ' language plpgsql;
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 $$
123 begin
124 if count(*) = 0 from Room where roomno = new.roomno then
125 raise exception 'Room % does not exist', new.roomno;
126 end if;
127 return new;
128 end;
129 $$ language plpgsql;
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 '
137 begin
138 if new.name != old.name then
139 update PSlot set pfname = new.name where pfname = old.name;
140 end if;
141 return new;
142 end;
143 ' language plpgsql;
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 '
151 begin
152 delete from PSlot where pfname = old.name;
153 return old;
154 end;
155 ' language plpgsql;
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$
163 declare
164 pfrec record;
165 rename new to ps;
166 begin
167 select into pfrec * from PField where name = ps.pfname;
168 if not found then
169 raise exception $$Patchfield "%" does not exist$$, ps.pfname;
170 end if;
171 return ps;
172 end;
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 '
181 begin
182 if new.name != old.name then
183 update IFace set sysname = new.name where sysname = old.name;
184 end if;
185 return new;
186 end;
187 ' language plpgsql;
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 $$
195 declare
196 sname text;
197 sysrec record;
198 begin
199 select into sysrec * from system where name = new.sysname;
200 if not found then
201 raise exception $q$system "%" does not exist$q$, new.sysname;
202 end if;
203 sname := 'IF.' || new.sysname;
204 sname := sname || '.';
205 sname := sname || new.ifname;
206 if length(sname) > 20 then
207 raise exception 'IFace slotname "%" too long (20 char max)', sname;
208 end if;
209 new.slotname := sname;
210 return new;
211 end;
212 $$ language plpgsql;
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 '
220 declare
221 hname text;
222 dummy integer;
223 begin
224 if tg_op = ''INSERT'' then
225 dummy := tg_hub_adjustslots(new.name, 0, new.nslots);
226 return new;
227 end if;
228 if tg_op = ''UPDATE'' then
229 if new.name != old.name then
230 update HSlot set hubname = new.name where hubname = old.name;
231 end if;
232 dummy := tg_hub_adjustslots(new.name, old.nslots, new.nslots);
233 return new;
234 end if;
235 if tg_op = ''DELETE'' then
236 dummy := tg_hub_adjustslots(old.name, old.nslots, 0);
237 return old;
238 end if;
239 end;
240 ' language plpgsql;
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,
247 oldnslots integer,
248 newnslots integer)
249 returns integer as '
250 begin
251 if newnslots = oldnslots then
252 return 0;
253 end if;
254 if newnslots < oldnslots then
255 delete from HSlot where hubname = hname and slotno > newnslots;
256 return 0;
257 end if;
258 for i in oldnslots + 1 .. newnslots loop
259 insert into HSlot (slotname, hubname, slotno, slotlink)
260 values (''HS.dummy'', hname, i, '''');
261 end loop;
262 return 0;
263 end
264 ' language plpgsql;
265 -- Test comments
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 '
276 declare
277 sname text;
278 xname HSlot.slotname%TYPE;
279 hubrec record;
280 begin
281 select into hubrec * from Hub where name = new.hubname;
282 if not found then
283 raise exception ''no manual manipulation of HSlot'';
284 end if;
285 if new.slotno < 1 or new.slotno > hubrec.nslots then
286 raise exception ''no manual manipulation of HSlot'';
287 end if;
288 if tg_op = ''UPDATE'' then
289 if new.hubname != old.hubname then
290 if count(*) > 0 from Hub where name = old.hubname then
291 raise exception ''no manual manipulation of HSlot'';
292 end if;
293 end if;
294 end if;
295 sname := ''HS.'' || trim(new.hubname);
296 sname := sname || ''.'';
297 sname := sname || new.slotno::text;
298 if length(sname) > 20 then
299 raise exception ''HSlot slotname "%" too long (20 char max)'', sname;
300 end if;
301 new.slotname := sname;
302 return new;
303 end;
304 ' language plpgsql;
305 create trigger tg_hslot_biu before insert or update
306 on HSlot for each row execute procedure tg_hslot_biu();
307 -- ************************************************************
308 -- * BEFORE DELETE on HSlot
309 -- * - prevent from manual manipulation
310 -- ************************************************************
311 create function tg_hslot_bd() returns trigger as '
312 declare
313 hubrec record;
314 begin
315 select into hubrec * from Hub where name = old.hubname;
316 if not found then
317 return old;
318 end if;
319 if old.slotno > hubrec.nslots then
320 return old;
321 end if;
322 raise exception ''no manual manipulation of HSlot'';
323 end;
324 ' language plpgsql;
325 create trigger tg_hslot_bd before delete
326 on HSlot for each row execute procedure tg_hslot_bd();
327 -- ************************************************************
328 -- * BEFORE INSERT on all slots
329 -- * - Check name prefix
330 -- ************************************************************
331 create function tg_chkslotname() returns trigger as '
332 begin
333 if substr(new.slotname, 1, 2) != tg_argv[0] then
334 raise exception ''slotname must begin with %'', tg_argv[0];
335 end if;
336 return new;
337 end;
338 ' language plpgsql;
339 create trigger tg_chkslotname before insert
340 on PSlot for each row execute procedure tg_chkslotname('PS');
341 create trigger tg_chkslotname before insert
342 on WSlot for each row execute procedure tg_chkslotname('WS');
343 create trigger tg_chkslotname before insert
344 on PLine for each row execute procedure tg_chkslotname('PL');
345 create trigger tg_chkslotname before insert
346 on IFace for each row execute procedure tg_chkslotname('IF');
347 create trigger tg_chkslotname before insert
348 on PHone for each row execute procedure tg_chkslotname('PH');
349 -- ************************************************************
350 -- * BEFORE INSERT or UPDATE on all slots with slotlink
351 -- * - Set slotlink to empty string if NULL value given
352 -- ************************************************************
353 create function tg_chkslotlink() returns trigger as '
354 begin
355 if new.slotlink isnull then
356 new.slotlink := '''';
357 end if;
358 return new;
359 end;
360 ' language plpgsql;
361 create trigger tg_chkslotlink before insert or update
362 on PSlot for each row execute procedure tg_chkslotlink();
363 create trigger tg_chkslotlink before insert or update
364 on WSlot for each row execute procedure tg_chkslotlink();
365 create trigger tg_chkslotlink before insert or update
366 on IFace for each row execute procedure tg_chkslotlink();
367 create trigger tg_chkslotlink before insert or update
368 on HSlot for each row execute procedure tg_chkslotlink();
369 create trigger tg_chkslotlink before insert or update
370 on PHone for each row execute procedure tg_chkslotlink();
371 -- ************************************************************
372 -- * BEFORE INSERT or UPDATE on all slots with backlink
373 -- * - Set backlink to empty string if NULL value given
374 -- ************************************************************
375 create function tg_chkbacklink() returns trigger as '
376 begin
377 if new.backlink isnull then
378 new.backlink := '''';
379 end if;
380 return new;
381 end;
382 ' language plpgsql;
383 create trigger tg_chkbacklink before insert or update
384 on PSlot for each row execute procedure tg_chkbacklink();
385 create trigger tg_chkbacklink before insert or update
386 on WSlot for each row execute procedure tg_chkbacklink();
387 create trigger tg_chkbacklink before insert or update
388 on PLine for each row execute procedure tg_chkbacklink();
389 -- ************************************************************
390 -- * BEFORE UPDATE on PSlot
391 -- * - do delete/insert instead of update if name changes
392 -- ************************************************************
393 create function tg_pslot_bu() returns trigger as '
394 begin
395 if new.slotname != old.slotname then
396 delete from PSlot where slotname = old.slotname;
397 insert into PSlot (
398 slotname,
399 pfname,
400 slotlink,
401 backlink
402 ) values (
403 new.slotname,
404 new.pfname,
405 new.slotlink,
406 new.backlink
407 );
408 return null;
409 end if;
410 return new;
411 end;
412 ' language plpgsql;
413 create trigger tg_pslot_bu before update
414 on PSlot for each row execute procedure tg_pslot_bu();
415 -- ************************************************************
416 -- * BEFORE UPDATE on WSlot
417 -- * - do delete/insert instead of update if name changes
418 -- ************************************************************
419 create function tg_wslot_bu() returns trigger as '
420 begin
421 if new.slotname != old.slotname then
422 delete from WSlot where slotname = old.slotname;
423 insert into WSlot (
424 slotname,
425 roomno,
426 slotlink,
427 backlink
428 ) values (
429 new.slotname,
430 new.roomno,
431 new.slotlink,
432 new.backlink
433 );
434 return null;
435 end if;
436 return new;
437 end;
438 ' language plpgsql;
439 create trigger tg_wslot_bu before update
440 on WSlot for each row execute procedure tg_Wslot_bu();
441 -- ************************************************************
442 -- * BEFORE UPDATE on PLine
443 -- * - do delete/insert instead of update if name changes
444 -- ************************************************************
445 create function tg_pline_bu() returns trigger as '
446 begin
447 if new.slotname != old.slotname then
448 delete from PLine where slotname = old.slotname;
449 insert into PLine (
450 slotname,
451 phonenumber,
452 comment,
453 backlink
454 ) values (
455 new.slotname,
456 new.phonenumber,
457 new.comment,
458 new.backlink
459 );
460 return null;
461 end if;
462 return new;
463 end;
464 ' language plpgsql;
465 create trigger tg_pline_bu before update
466 on PLine for each row execute procedure tg_pline_bu();
467 -- ************************************************************
468 -- * BEFORE UPDATE on IFace
469 -- * - do delete/insert instead of update if name changes
470 -- ************************************************************
471 create function tg_iface_bu() returns trigger as '
472 begin
473 if new.slotname != old.slotname then
474 delete from IFace where slotname = old.slotname;
475 insert into IFace (
476 slotname,
477 sysname,
478 ifname,
479 slotlink
480 ) values (
481 new.slotname,
482 new.sysname,
483 new.ifname,
484 new.slotlink
485 );
486 return null;
487 end if;
488 return new;
489 end;
490 ' language plpgsql;
491 create trigger tg_iface_bu before update
492 on IFace for each row execute procedure tg_iface_bu();
493 -- ************************************************************
494 -- * BEFORE UPDATE on HSlot
495 -- * - do delete/insert instead of update if name changes
496 -- ************************************************************
497 create function tg_hslot_bu() returns trigger as '
498 begin
499 if new.slotname != old.slotname or new.hubname != old.hubname then
500 delete from HSlot where slotname = old.slotname;
501 insert into HSlot (
502 slotname,
503 hubname,
504 slotno,
505 slotlink
506 ) values (
507 new.slotname,
508 new.hubname,
509 new.slotno,
510 new.slotlink
511 );
512 return null;
513 end if;
514 return new;
515 end;
516 ' language plpgsql;
517 create trigger tg_hslot_bu before update
518 on HSlot for each row execute procedure tg_hslot_bu();
519 -- ************************************************************
520 -- * BEFORE UPDATE on PHone
521 -- * - do delete/insert instead of update if name changes
522 -- ************************************************************
523 create function tg_phone_bu() returns trigger as '
524 begin
525 if new.slotname != old.slotname then
526 delete from PHone where slotname = old.slotname;
527 insert into PHone (
528 slotname,
529 comment,
530 slotlink
531 ) values (
532 new.slotname,
533 new.comment,
534 new.slotlink
535 );
536 return null;
537 end if;
538 return new;
539 end;
540 ' language plpgsql;
541 create trigger tg_phone_bu before update
542 on PHone for each row execute procedure tg_phone_bu();
543 -- ************************************************************
544 -- * AFTER INSERT or UPDATE or DELETE on slot with backlink
545 -- * - Ensure that the opponent correctly points back to us
546 -- ************************************************************
547 create function tg_backlink_a() returns trigger as '
548 declare
549 dummy integer;
550 begin
551 if tg_op = ''INSERT'' then
552 if new.backlink != '''' then
553 dummy := tg_backlink_set(new.backlink, new.slotname);
554 end if;
555 return new;
556 end if;
557 if tg_op = ''UPDATE'' then
558 if new.backlink != old.backlink then
559 if old.backlink != '''' then
560 dummy := tg_backlink_unset(old.backlink, old.slotname);
561 end if;
562 if new.backlink != '''' then
563 dummy := tg_backlink_set(new.backlink, new.slotname);
564 end if;
565 else
566 if new.slotname != old.slotname and new.backlink != '''' then
567 dummy := tg_slotlink_set(new.backlink, new.slotname);
568 end if;
569 end if;
570 return new;
571 end if;
572 if tg_op = ''DELETE'' then
573 if old.backlink != '''' then
574 dummy := tg_backlink_unset(old.backlink, old.slotname);
575 end if;
576 return old;
577 end if;
578 end;
579 ' language plpgsql;
580 create trigger tg_backlink_a after insert or update or delete
581 on PSlot for each row execute procedure tg_backlink_a('PS');
582 create trigger tg_backlink_a after insert or update or delete
583 on WSlot for each row execute procedure tg_backlink_a('WS');
584 create trigger tg_backlink_a after insert or update or delete
585 on PLine for each row execute procedure tg_backlink_a('PL');
586 -- ************************************************************
587 -- * Support function to set the opponents backlink field
588 -- * if it does not already point to the requested slot
589 -- ************************************************************
590 create function tg_backlink_set(myname bpchar, blname bpchar)
591 returns integer as '
592 declare
593 mytype char(2);
594 link char(4);
595 rec record;
596 begin
597 mytype := substr(myname, 1, 2);
598 link := mytype || substr(blname, 1, 2);
599 if link = ''PLPL'' then
600 raise exception
601 ''backlink between two phone lines does not make sense'';
602 end if;
603 if link in (''PLWS'', ''WSPL'') then
604 raise exception
605 ''direct link of phone line to wall slot not permitted'';
606 end if;
607 if mytype = ''PS'' then
608 select into rec * from PSlot where slotname = myname;
609 if not found then
610 raise exception ''% does not exist'', myname;
611 end if;
612 if rec.backlink != blname then
613 update PSlot set backlink = blname where slotname = myname;
614 end if;
615 return 0;
616 end if;
617 if mytype = ''WS'' then
618 select into rec * from WSlot where slotname = myname;
619 if not found then
620 raise exception ''% does not exist'', myname;
621 end if;
622 if rec.backlink != blname then
623 update WSlot set backlink = blname where slotname = myname;
624 end if;
625 return 0;
626 end if;
627 if mytype = ''PL'' then
628 select into rec * from PLine where slotname = myname;
629 if not found then
630 raise exception ''% does not exist'', myname;
631 end if;
632 if rec.backlink != blname then
633 update PLine set backlink = blname where slotname = myname;
634 end if;
635 return 0;
636 end if;
637 raise exception ''illegal backlink beginning with %'', mytype;
638 end;
639 ' language plpgsql;
640 -- ************************************************************
641 -- * Support function to clear out the backlink field if
642 -- * it still points to specific slot
643 -- ************************************************************
644 create function tg_backlink_unset(bpchar, bpchar)
645 returns integer as '
646 declare
647 myname alias for $1;
648 blname alias for $2;
649 mytype char(2);
650 rec record;
651 begin
652 mytype := substr(myname, 1, 2);
653 if mytype = ''PS'' then
654 select into rec * from PSlot where slotname = myname;
655 if not found then
656 return 0;
657 end if;
658 if rec.backlink = blname then
659 update PSlot set backlink = '''' where slotname = myname;
660 end if;
661 return 0;
662 end if;
663 if mytype = ''WS'' then
664 select into rec * from WSlot where slotname = myname;
665 if not found then
666 return 0;
667 end if;
668 if rec.backlink = blname then
669 update WSlot set backlink = '''' where slotname = myname;
670 end if;
671 return 0;
672 end if;
673 if mytype = ''PL'' then
674 select into rec * from PLine where slotname = myname;
675 if not found then
676 return 0;
677 end if;
678 if rec.backlink = blname then
679 update PLine set backlink = '''' where slotname = myname;
680 end if;
681 return 0;
682 end if;
683 end
684 ' language plpgsql;
685 -- ************************************************************
686 -- * AFTER INSERT or UPDATE or DELETE on slot with slotlink
687 -- * - Ensure that the opponent correctly points back to us
688 -- ************************************************************
689 create function tg_slotlink_a() returns trigger as '
690 declare
691 dummy integer;
692 begin
693 if tg_op = ''INSERT'' then
694 if new.slotlink != '''' then
695 dummy := tg_slotlink_set(new.slotlink, new.slotname);
696 end if;
697 return new;
698 end if;
699 if tg_op = ''UPDATE'' then
700 if new.slotlink != old.slotlink then
701 if old.slotlink != '''' then
702 dummy := tg_slotlink_unset(old.slotlink, old.slotname);
703 end if;
704 if new.slotlink != '''' then
705 dummy := tg_slotlink_set(new.slotlink, new.slotname);
706 end if;
707 else
708 if new.slotname != old.slotname and new.slotlink != '''' then
709 dummy := tg_slotlink_set(new.slotlink, new.slotname);
710 end if;
711 end if;
712 return new;
713 end if;
714 if tg_op = ''DELETE'' then
715 if old.slotlink != '''' then
716 dummy := tg_slotlink_unset(old.slotlink, old.slotname);
717 end if;
718 return old;
719 end if;
720 end;
721 ' language plpgsql;
722 create trigger tg_slotlink_a after insert or update or delete
723 on PSlot for each row execute procedure tg_slotlink_a('PS');
724 create trigger tg_slotlink_a after insert or update or delete
725 on WSlot for each row execute procedure tg_slotlink_a('WS');
726 create trigger tg_slotlink_a after insert or update or delete
727 on IFace for each row execute procedure tg_slotlink_a('IF');
728 create trigger tg_slotlink_a after insert or update or delete
729 on HSlot for each row execute procedure tg_slotlink_a('HS');
730 create trigger tg_slotlink_a after insert or update or delete
731 on PHone for each row execute procedure tg_slotlink_a('PH');
732 -- ************************************************************
733 -- * Support function to set the opponents slotlink field
734 -- * if it does not already point to the requested slot
735 -- ************************************************************
736 create function tg_slotlink_set(bpchar, bpchar)
737 returns integer as '
738 declare
739 myname alias for $1;
740 blname alias for $2;
741 mytype char(2);
742 link char(4);
743 rec record;
744 begin
745 mytype := substr(myname, 1, 2);
746 link := mytype || substr(blname, 1, 2);
747 if link = ''PHPH'' then
748 raise exception
749 ''slotlink between two phones does not make sense'';
750 end if;
751 if link in (''PHHS'', ''HSPH'') then
752 raise exception
753 ''link of phone to hub does not make sense'';
754 end if;
755 if link in (''PHIF'', ''IFPH'') then
756 raise exception
757 ''link of phone to hub does not make sense'';
758 end if;
759 if link in (''PSWS'', ''WSPS'') then
760 raise exception
761 ''slotlink from patchslot to wallslot not permitted'';
762 end if;
763 if mytype = ''PS'' then
764 select into rec * from PSlot where slotname = myname;
765 if not found then
766 raise exception ''% does not exist'', myname;
767 end if;
768 if rec.slotlink != blname then
769 update PSlot set slotlink = blname where slotname = myname;
770 end if;
771 return 0;
772 end if;
773 if mytype = ''WS'' then
774 select into rec * from WSlot where slotname = myname;
775 if not found then
776 raise exception ''% does not exist'', myname;
777 end if;
778 if rec.slotlink != blname then
779 update WSlot set slotlink = blname where slotname = myname;
780 end if;
781 return 0;
782 end if;
783 if mytype = ''IF'' then
784 select into rec * from IFace where slotname = myname;
785 if not found then
786 raise exception ''% does not exist'', myname;
787 end if;
788 if rec.slotlink != blname then
789 update IFace set slotlink = blname where slotname = myname;
790 end if;
791 return 0;
792 end if;
793 if mytype = ''HS'' then
794 select into rec * from HSlot where slotname = myname;
795 if not found then
796 raise exception ''% does not exist'', myname;
797 end if;
798 if rec.slotlink != blname then
799 update HSlot set slotlink = blname where slotname = myname;
800 end if;
801 return 0;
802 end if;
803 if mytype = ''PH'' then
804 select into rec * from PHone where slotname = myname;
805 if not found then
806 raise exception ''% does not exist'', myname;
807 end if;
808 if rec.slotlink != blname then
809 update PHone set slotlink = blname where slotname = myname;
810 end if;
811 return 0;
812 end if;
813 raise exception ''illegal slotlink beginning with %'', mytype;
814 end;
815 ' language plpgsql;
816 -- ************************************************************
817 -- * Support function to clear out the slotlink field if
818 -- * it still points to specific slot
819 -- ************************************************************
820 create function tg_slotlink_unset(bpchar, bpchar)
821 returns integer as '
822 declare
823 myname alias for $1;
824 blname alias for $2;
825 mytype char(2);
826 rec record;
827 begin
828 mytype := substr(myname, 1, 2);
829 if mytype = ''PS'' then
830 select into rec * from PSlot where slotname = myname;
831 if not found then
832 return 0;
833 end if;
834 if rec.slotlink = blname then
835 update PSlot set slotlink = '''' where slotname = myname;
836 end if;
837 return 0;
838 end if;
839 if mytype = ''WS'' then
840 select into rec * from WSlot where slotname = myname;
841 if not found then
842 return 0;
843 end if;
844 if rec.slotlink = blname then
845 update WSlot set slotlink = '''' where slotname = myname;
846 end if;
847 return 0;
848 end if;
849 if mytype = ''IF'' then
850 select into rec * from IFace where slotname = myname;
851 if not found then
852 return 0;
853 end if;
854 if rec.slotlink = blname then
855 update IFace set slotlink = '''' where slotname = myname;
856 end if;
857 return 0;
858 end if;
859 if mytype = ''HS'' then
860 select into rec * from HSlot where slotname = myname;
861 if not found then
862 return 0;
863 end if;
864 if rec.slotlink = blname then
865 update HSlot set slotlink = '''' where slotname = myname;
866 end if;
867 return 0;
868 end if;
869 if mytype = ''PH'' then
870 select into rec * from PHone where slotname = myname;
871 if not found then
872 return 0;
873 end if;
874 if rec.slotlink = blname then
875 update PHone set slotlink = '''' where slotname = myname;
876 end if;
877 return 0;
878 end if;
879 end;
880 ' language plpgsql;
881 -- ************************************************************
882 -- * Describe the backside of a patchfield slot
883 -- ************************************************************
884 create function pslot_backlink_view(bpchar)
885 returns text as '
886 <<outer>>
887 declare
888 rec record;
889 bltype char(2);
890 retval text;
891 begin
892 select into rec * from PSlot where slotname = $1;
893 if not found then
894 return '''';
895 end if;
896 if rec.backlink = '''' then
897 return ''-'';
898 end if;
899 bltype := substr(rec.backlink, 1, 2);
900 if bltype = ''PL'' then
901 declare
902 rec record;
903 begin
904 select into rec * from PLine where slotname = outer.rec.backlink;
905 retval := ''Phone line '' || trim(rec.phonenumber);
906 if rec.comment != '''' then
907 retval := retval || '' ('';
908 retval := retval || rec.comment;
909 retval := retval || '')'';
910 end if;
911 return retval;
912 end;
913 end if;
914 if bltype = ''WS'' then
915 select into rec * from WSlot where slotname = rec.backlink;
916 retval := trim(rec.slotname) || '' in room '';
917 retval := retval || trim(rec.roomno);
918 retval := retval || '' -> '';
919 return retval || wslot_slotlink_view(rec.slotname);
920 end if;
921 return rec.backlink;
922 end;
923 ' language plpgsql;
924 -- ************************************************************
925 -- * Describe the front of a patchfield slot
926 -- ************************************************************
927 create function pslot_slotlink_view(bpchar)
928 returns text as '
929 declare
930 psrec record;
931 sltype char(2);
932 retval text;
933 begin
934 select into psrec * from PSlot where slotname = $1;
935 if not found then
936 return '''';
937 end if;
938 if psrec.slotlink = '''' then
939 return ''-'';
940 end if;
941 sltype := substr(psrec.slotlink, 1, 2);
942 if sltype = ''PS'' then
943 retval := trim(psrec.slotlink) || '' -> '';
944 return retval || pslot_backlink_view(psrec.slotlink);
945 end if;
946 if sltype = ''HS'' then
947 retval := comment from Hub H, HSlot HS
948 where HS.slotname = psrec.slotlink
949 and H.name = HS.hubname;
950 retval := retval || '' slot '';
951 retval := retval || slotno::text from HSlot
952 where slotname = psrec.slotlink;
953 return retval;
954 end if;
955 return psrec.slotlink;
956 end;
957 ' language plpgsql;
958 -- ************************************************************
959 -- * Describe the front of a wall connector slot
960 -- ************************************************************
961 create function wslot_slotlink_view(bpchar)
962 returns text as '
963 declare
964 rec record;
965 sltype char(2);
966 retval text;
967 begin
968 select into rec * from WSlot where slotname = $1;
969 if not found then
970 return '''';
971 end if;
972 if rec.slotlink = '''' then
973 return ''-'';
974 end if;
975 sltype := substr(rec.slotlink, 1, 2);
976 if sltype = ''PH'' then
977 select into rec * from PHone where slotname = rec.slotlink;
978 retval := ''Phone '' || trim(rec.slotname);
979 if rec.comment != '''' then
980 retval := retval || '' ('';
981 retval := retval || rec.comment;
982 retval := retval || '')'';
983 end if;
984 return retval;
985 end if;
986 if sltype = ''IF'' then
987 declare
988 syrow System%RowType;
989 ifrow IFace%ROWTYPE;
990 begin
991 select into ifrow * from IFace where slotname = rec.slotlink;
992 select into syrow * from System where name = ifrow.sysname;
993 retval := syrow.name || '' IF '';
994 retval := retval || ifrow.ifname;
995 if syrow.comment != '''' then
996 retval := retval || '' ('';
997 retval := retval || syrow.comment;
998 retval := retval || '')'';
999 end if;
1000 return retval;
1001 end;
1002 end if;
1003 return rec.slotlink;
1004 end;
1005 ' language plpgsql;
1006 -- ************************************************************
1007 -- * View of a patchfield describing backside and patches
1008 -- ************************************************************
1009 create view Pfield_v1 as select PF.pfname, PF.slotname,
1010 pslot_backlink_view(PF.slotname) as backside,
1011 pslot_slotlink_view(PF.slotname) as patch
1012 from PSlot PF;
1013 --
1014 -- First we build the house - so we create the rooms
1015 --
1016 insert into Room values ('001', 'Entrance');
1017 insert into Room values ('002', 'Office');
1018 insert into Room values ('003', 'Office');
1019 insert into Room values ('004', 'Technical');
1020 insert into Room values ('101', 'Office');
1021 insert into Room values ('102', 'Conference');
1022 insert into Room values ('103', 'Restroom');
1023 insert into Room values ('104', 'Technical');
1024 insert into Room values ('105', 'Office');
1025 insert into Room values ('106', 'Office');
1026 --
1027 -- Second we install the wall connectors
1028 --
1029 insert into WSlot values ('WS.001.1a', '001', '', '');
1030 insert into WSlot values ('WS.001.1b', '001', '', '');
1031 insert into WSlot values ('WS.001.2a', '001', '', '');
1032 insert into WSlot values ('WS.001.2b', '001', '', '');
1033 insert into WSlot values ('WS.001.3a', '001', '', '');
1034 insert into WSlot values ('WS.001.3b', '001', '', '');
1035 insert into WSlot values ('WS.002.1a', '002', '', '');
1036 insert into WSlot values ('WS.002.1b', '002', '', '');
1037 insert into WSlot values ('WS.002.2a', '002', '', '');
1038 insert into WSlot values ('WS.002.2b', '002', '', '');
1039 insert into WSlot values ('WS.002.3a', '002', '', '');
1040 insert into WSlot values ('WS.002.3b', '002', '', '');
1041 insert into WSlot values ('WS.003.1a', '003', '', '');
1042 insert into WSlot values ('WS.003.1b', '003', '', '');
1043 insert into WSlot values ('WS.003.2a', '003', '', '');
1044 insert into WSlot values ('WS.003.2b', '003', '', '');
1045 insert into WSlot values ('WS.003.3a', '003', '', '');
1046 insert into WSlot values ('WS.003.3b', '003', '', '');
1047 insert into WSlot values ('WS.101.1a', '101', '', '');
1048 insert into WSlot values ('WS.101.1b', '101', '', '');
1049 insert into WSlot values ('WS.101.2a', '101', '', '');
1050 insert into WSlot values ('WS.101.2b', '101', '', '');
1051 insert into WSlot values ('WS.101.3a', '101', '', '');
1052 insert into WSlot values ('WS.101.3b', '101', '', '');
1053 insert into WSlot values ('WS.102.1a', '102', '', '');
1054 insert into WSlot values ('WS.102.1b', '102', '', '');
1055 insert into WSlot values ('WS.102.2a', '102', '', '');
1056 insert into WSlot values ('WS.102.2b', '102', '', '');
1057 insert into WSlot values ('WS.102.3a', '102', '', '');
1058 insert into WSlot values ('WS.102.3b', '102', '', '');
1059 insert into WSlot values ('WS.105.1a', '105', '', '');
1060 insert into WSlot values ('WS.105.1b', '105', '', '');
1061 insert into WSlot values ('WS.105.2a', '105', '', '');
1062 insert into WSlot values ('WS.105.2b', '105', '', '');
1063 insert into WSlot values ('WS.105.3a', '105', '', '');
1064 insert into WSlot values ('WS.105.3b', '105', '', '');
1065 insert into WSlot values ('WS.106.1a', '106', '', '');
1066 insert into WSlot values ('WS.106.1b', '106', '', '');
1067 insert into WSlot values ('WS.106.2a', '106', '', '');
1068 insert into WSlot values ('WS.106.2b', '106', '', '');
1069 insert into WSlot values ('WS.106.3a', '106', '', '');
1070 insert into WSlot values ('WS.106.3b', '106', '', '');
1071 --
1072 -- Now create the patch fields and their slots
1073 --
1074 insert into PField values ('PF0_1', 'Wallslots basement');
1075 --
1076 -- The cables for these will be made later, so they are unconnected for now
1077 --
1078 insert into PSlot values ('PS.base.a1', 'PF0_1', '', '');
1079 insert into PSlot values ('PS.base.a2', 'PF0_1', '', '');
1080 insert into PSlot values ('PS.base.a3', 'PF0_1', '', '');
1081 insert into PSlot values ('PS.base.a4', 'PF0_1', '', '');
1082 insert into PSlot values ('PS.base.a5', 'PF0_1', '', '');
1083 insert into PSlot values ('PS.base.a6', 'PF0_1', '', '');
1084 --
1085 -- These are already wired to the wall connectors
1086 --
1087 insert into PSlot values ('PS.base.b1', 'PF0_1', '', 'WS.002.1a');
1088 insert into PSlot values ('PS.base.b2', 'PF0_1', '', 'WS.002.1b');
1089 insert into PSlot values ('PS.base.b3', 'PF0_1', '', 'WS.002.2a');
1090 insert into PSlot values ('PS.base.b4', 'PF0_1', '', 'WS.002.2b');
1091 insert into PSlot values ('PS.base.b5', 'PF0_1', '', 'WS.002.3a');
1092 insert into PSlot values ('PS.base.b6', 'PF0_1', '', 'WS.002.3b');
1093 insert into PSlot values ('PS.base.c1', 'PF0_1', '', 'WS.003.1a');
1094 insert into PSlot values ('PS.base.c2', 'PF0_1', '', 'WS.003.1b');
1095 insert into PSlot values ('PS.base.c3', 'PF0_1', '', 'WS.003.2a');
1096 insert into PSlot values ('PS.base.c4', 'PF0_1', '', 'WS.003.2b');
1097 insert into PSlot values ('PS.base.c5', 'PF0_1', '', 'WS.003.3a');
1098 insert into PSlot values ('PS.base.c6', 'PF0_1', '', 'WS.003.3b');
1099 --
1100 -- This patchfield will be renamed later into PF0_2 - so its
1101 -- slots references in pfname should follow
1102 --
1103 insert into PField values ('PF0_X', 'Phonelines basement');
1104 insert into PSlot values ('PS.base.ta1', 'PF0_X', '', '');
1105 insert into PSlot values ('PS.base.ta2', 'PF0_X', '', '');
1106 insert into PSlot values ('PS.base.ta3', 'PF0_X', '', '');
1107 insert into PSlot values ('PS.base.ta4', 'PF0_X', '', '');
1108 insert into PSlot values ('PS.base.ta5', 'PF0_X', '', '');
1109 insert into PSlot values ('PS.base.ta6', 'PF0_X', '', '');
1110 insert into PSlot values ('PS.base.tb1', 'PF0_X', '', '');
1111 insert into PSlot values ('PS.base.tb2', 'PF0_X', '', '');
1112 insert into PSlot values ('PS.base.tb3', 'PF0_X', '', '');
1113 insert into PSlot values ('PS.base.tb4', 'PF0_X', '', '');
1114 insert into PSlot values ('PS.base.tb5', 'PF0_X', '', '');
1115 insert into PSlot values ('PS.base.tb6', 'PF0_X', '', '');
1116 insert into PField values ('PF1_1', 'Wallslots 1st floor');
1117 insert into PSlot values ('PS.1st.a1', 'PF1_1', '', 'WS.101.1a');
1118 insert into PSlot values ('PS.1st.a2', 'PF1_1', '', 'WS.101.1b');
1119 insert into PSlot values ('PS.1st.a3', 'PF1_1', '', 'WS.101.2a');
1120 insert into PSlot values ('PS.1st.a4', 'PF1_1', '', 'WS.101.2b');
1121 insert into PSlot values ('PS.1st.a5', 'PF1_1', '', 'WS.101.3a');
1122 insert into PSlot values ('PS.1st.a6', 'PF1_1', '', 'WS.101.3b');
1123 insert into PSlot values ('PS.1st.b1', 'PF1_1', '', 'WS.102.1a');
1124 insert into PSlot values ('PS.1st.b2', 'PF1_1', '', 'WS.102.1b');
1125 insert into PSlot values ('PS.1st.b3', 'PF1_1', '', 'WS.102.2a');
1126 insert into PSlot values ('PS.1st.b4', 'PF1_1', '', 'WS.102.2b');
1127 insert into PSlot values ('PS.1st.b5', 'PF1_1', '', 'WS.102.3a');
1128 insert into PSlot values ('PS.1st.b6', 'PF1_1', '', 'WS.102.3b');
1129 insert into PSlot values ('PS.1st.c1', 'PF1_1', '', 'WS.105.1a');
1130 insert into PSlot values ('PS.1st.c2', 'PF1_1', '', 'WS.105.1b');
1131 insert into PSlot values ('PS.1st.c3', 'PF1_1', '', 'WS.105.2a');
1132 insert into PSlot values ('PS.1st.c4', 'PF1_1', '', 'WS.105.2b');
1133 insert into PSlot values ('PS.1st.c5', 'PF1_1', '', 'WS.105.3a');
1134 insert into PSlot values ('PS.1st.c6', 'PF1_1', '', 'WS.105.3b');
1135 insert into PSlot values ('PS.1st.d1', 'PF1_1', '', 'WS.106.1a');
1136 insert into PSlot values ('PS.1st.d2', 'PF1_1', '', 'WS.106.1b');
1137 insert into PSlot values ('PS.1st.d3', 'PF1_1', '', 'WS.106.2a');
1138 insert into PSlot values ('PS.1st.d4', 'PF1_1', '', 'WS.106.2b');
1139 insert into PSlot values ('PS.1st.d5', 'PF1_1', '', 'WS.106.3a');
1140 insert into PSlot values ('PS.1st.d6', 'PF1_1', '', 'WS.106.3b');
1141 --
1142 -- Now we wire the wall connectors 1a-2a in room 001 to the
1143 -- patchfield. In the second update we make an error, and
1144 -- correct it after
1145 --
1146 update PSlot set backlink = 'WS.001.1a' where slotname = 'PS.base.a1';
1147 update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a3';
1148 select * from WSlot where roomno = '001' order by slotname;
1149 slotname | roomno | slotlink | backlink
1150 ----------------------+----------+----------------------+----------------------
1151 WS.001.1a | 001 | | PS.base.a1
1152 WS.001.1b | 001 | | PS.base.a3
1153 WS.001.2a | 001 | |
1154 WS.001.2b | 001 | |
1155 WS.001.3a | 001 | |
1156 WS.001.3b | 001 | |
1157 (6 rows)
1159 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1160 slotname | pfname | slotlink | backlink
1161 ----------------------+--------+----------------------+----------------------
1162 PS.base.a1 | PF0_1 | | WS.001.1a
1163 PS.base.a2 | PF0_1 | |
1164 PS.base.a3 | PF0_1 | | WS.001.1b
1165 PS.base.a4 | PF0_1 | |
1166 PS.base.a5 | PF0_1 | |
1167 PS.base.a6 | PF0_1 | |
1168 (6 rows)
1170 update PSlot set backlink = 'WS.001.2a' where slotname = 'PS.base.a3';
1171 select * from WSlot where roomno = '001' order by slotname;
1172 slotname | roomno | slotlink | backlink
1173 ----------------------+----------+----------------------+----------------------
1174 WS.001.1a | 001 | | PS.base.a1
1175 WS.001.1b | 001 | |
1176 WS.001.2a | 001 | | PS.base.a3
1177 WS.001.2b | 001 | |
1178 WS.001.3a | 001 | |
1179 WS.001.3b | 001 | |
1180 (6 rows)
1182 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1183 slotname | pfname | slotlink | backlink
1184 ----------------------+--------+----------------------+----------------------
1185 PS.base.a1 | PF0_1 | | WS.001.1a
1186 PS.base.a2 | PF0_1 | |
1187 PS.base.a3 | PF0_1 | | WS.001.2a
1188 PS.base.a4 | PF0_1 | |
1189 PS.base.a5 | PF0_1 | |
1190 PS.base.a6 | PF0_1 | |
1191 (6 rows)
1193 update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a2';
1194 select * from WSlot where roomno = '001' order by slotname;
1195 slotname | roomno | slotlink | backlink
1196 ----------------------+----------+----------------------+----------------------
1197 WS.001.1a | 001 | | PS.base.a1
1198 WS.001.1b | 001 | | PS.base.a2
1199 WS.001.2a | 001 | | PS.base.a3
1200 WS.001.2b | 001 | |
1201 WS.001.3a | 001 | |
1202 WS.001.3b | 001 | |
1203 (6 rows)
1205 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1206 slotname | pfname | slotlink | backlink
1207 ----------------------+--------+----------------------+----------------------
1208 PS.base.a1 | PF0_1 | | WS.001.1a
1209 PS.base.a2 | PF0_1 | | WS.001.1b
1210 PS.base.a3 | PF0_1 | | WS.001.2a
1211 PS.base.a4 | PF0_1 | |
1212 PS.base.a5 | PF0_1 | |
1213 PS.base.a6 | PF0_1 | |
1214 (6 rows)
1216 --
1217 -- Same procedure for 2b-3b but this time updating the WSlot instead
1218 -- of the PSlot. Due to the triggers the result is the same:
1219 -- WSlot and corresponding PSlot point to each other.
1220 --
1221 update WSlot set backlink = 'PS.base.a4' where slotname = 'WS.001.2b';
1222 update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3a';
1223 select * from WSlot where roomno = '001' order by slotname;
1224 slotname | roomno | slotlink | backlink
1225 ----------------------+----------+----------------------+----------------------
1226 WS.001.1a | 001 | | PS.base.a1
1227 WS.001.1b | 001 | | PS.base.a2
1228 WS.001.2a | 001 | | PS.base.a3
1229 WS.001.2b | 001 | | PS.base.a4
1230 WS.001.3a | 001 | | PS.base.a6
1231 WS.001.3b | 001 | |
1232 (6 rows)
1234 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1235 slotname | pfname | slotlink | backlink
1236 ----------------------+--------+----------------------+----------------------
1237 PS.base.a1 | PF0_1 | | WS.001.1a
1238 PS.base.a2 | PF0_1 | | WS.001.1b
1239 PS.base.a3 | PF0_1 | | WS.001.2a
1240 PS.base.a4 | PF0_1 | | WS.001.2b
1241 PS.base.a5 | PF0_1 | |
1242 PS.base.a6 | PF0_1 | | WS.001.3a
1243 (6 rows)
1245 update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3b';
1246 select * from WSlot where roomno = '001' order by slotname;
1247 slotname | roomno | slotlink | backlink
1248 ----------------------+----------+----------------------+----------------------
1249 WS.001.1a | 001 | | PS.base.a1
1250 WS.001.1b | 001 | | PS.base.a2
1251 WS.001.2a | 001 | | PS.base.a3
1252 WS.001.2b | 001 | | PS.base.a4
1253 WS.001.3a | 001 | |
1254 WS.001.3b | 001 | | PS.base.a6
1255 (6 rows)
1257 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1258 slotname | pfname | slotlink | backlink
1259 ----------------------+--------+----------------------+----------------------
1260 PS.base.a1 | PF0_1 | | WS.001.1a
1261 PS.base.a2 | PF0_1 | | WS.001.1b
1262 PS.base.a3 | PF0_1 | | WS.001.2a
1263 PS.base.a4 | PF0_1 | | WS.001.2b
1264 PS.base.a5 | PF0_1 | |
1265 PS.base.a6 | PF0_1 | | WS.001.3b
1266 (6 rows)
1268 update WSlot set backlink = 'PS.base.a5' where slotname = 'WS.001.3a';
1269 select * from WSlot where roomno = '001' order by slotname;
1270 slotname | roomno | slotlink | backlink
1271 ----------------------+----------+----------------------+----------------------
1272 WS.001.1a | 001 | | PS.base.a1
1273 WS.001.1b | 001 | | PS.base.a2
1274 WS.001.2a | 001 | | PS.base.a3
1275 WS.001.2b | 001 | | PS.base.a4
1276 WS.001.3a | 001 | | PS.base.a5
1277 WS.001.3b | 001 | | PS.base.a6
1278 (6 rows)
1280 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1281 slotname | pfname | slotlink | backlink
1282 ----------------------+--------+----------------------+----------------------
1283 PS.base.a1 | PF0_1 | | WS.001.1a
1284 PS.base.a2 | PF0_1 | | WS.001.1b
1285 PS.base.a3 | PF0_1 | | WS.001.2a
1286 PS.base.a4 | PF0_1 | | WS.001.2b
1287 PS.base.a5 | PF0_1 | | WS.001.3a
1288 PS.base.a6 | PF0_1 | | WS.001.3b
1289 (6 rows)
1291 insert into PField values ('PF1_2', 'Phonelines 1st floor');
1292 insert into PSlot values ('PS.1st.ta1', 'PF1_2', '', '');
1293 insert into PSlot values ('PS.1st.ta2', 'PF1_2', '', '');
1294 insert into PSlot values ('PS.1st.ta3', 'PF1_2', '', '');
1295 insert into PSlot values ('PS.1st.ta4', 'PF1_2', '', '');
1296 insert into PSlot values ('PS.1st.ta5', 'PF1_2', '', '');
1297 insert into PSlot values ('PS.1st.ta6', 'PF1_2', '', '');
1298 insert into PSlot values ('PS.1st.tb1', 'PF1_2', '', '');
1299 insert into PSlot values ('PS.1st.tb2', 'PF1_2', '', '');
1300 insert into PSlot values ('PS.1st.tb3', 'PF1_2', '', '');
1301 insert into PSlot values ('PS.1st.tb4', 'PF1_2', '', '');
1302 insert into PSlot values ('PS.1st.tb5', 'PF1_2', '', '');
1303 insert into PSlot values ('PS.1st.tb6', 'PF1_2', '', '');
1304 --
1305 -- Fix the wrong name for patchfield PF0_2
1306 --
1307 update PField set name = 'PF0_2' where name = 'PF0_X';
1308 select * from PSlot order by slotname;
1309 slotname | pfname | slotlink | backlink
1310 ----------------------+--------+----------------------+----------------------
1311 PS.1st.a1 | PF1_1 | | WS.101.1a
1312 PS.1st.a2 | PF1_1 | | WS.101.1b
1313 PS.1st.a3 | PF1_1 | | WS.101.2a
1314 PS.1st.a4 | PF1_1 | | WS.101.2b
1315 PS.1st.a5 | PF1_1 | | WS.101.3a
1316 PS.1st.a6 | PF1_1 | | WS.101.3b
1317 PS.1st.b1 | PF1_1 | | WS.102.1a
1318 PS.1st.b2 | PF1_1 | | WS.102.1b
1319 PS.1st.b3 | PF1_1 | | WS.102.2a
1320 PS.1st.b4 | PF1_1 | | WS.102.2b
1321 PS.1st.b5 | PF1_1 | | WS.102.3a
1322 PS.1st.b6 | PF1_1 | | WS.102.3b
1323 PS.1st.c1 | PF1_1 | | WS.105.1a
1324 PS.1st.c2 | PF1_1 | | WS.105.1b
1325 PS.1st.c3 | PF1_1 | | WS.105.2a
1326 PS.1st.c4 | PF1_1 | | WS.105.2b
1327 PS.1st.c5 | PF1_1 | | WS.105.3a
1328 PS.1st.c6 | PF1_1 | | WS.105.3b
1329 PS.1st.d1 | PF1_1 | | WS.106.1a
1330 PS.1st.d2 | PF1_1 | | WS.106.1b
1331 PS.1st.d3 | PF1_1 | | WS.106.2a
1332 PS.1st.d4 | PF1_1 | | WS.106.2b
1333 PS.1st.d5 | PF1_1 | | WS.106.3a
1334 PS.1st.d6 | PF1_1 | | WS.106.3b
1335 PS.1st.ta1 | PF1_2 | |
1336 PS.1st.ta2 | PF1_2 | |
1337 PS.1st.ta3 | PF1_2 | |
1338 PS.1st.ta4 | PF1_2 | |
1339 PS.1st.ta5 | PF1_2 | |
1340 PS.1st.ta6 | PF1_2 | |
1341 PS.1st.tb1 | PF1_2 | |
1342 PS.1st.tb2 | PF1_2 | |
1343 PS.1st.tb3 | PF1_2 | |
1344 PS.1st.tb4 | PF1_2 | |
1345 PS.1st.tb5 | PF1_2 | |
1346 PS.1st.tb6 | PF1_2 | |
1347 PS.base.a1 | PF0_1 | | WS.001.1a
1348 PS.base.a2 | PF0_1 | | WS.001.1b
1349 PS.base.a3 | PF0_1 | | WS.001.2a
1350 PS.base.a4 | PF0_1 | | WS.001.2b
1351 PS.base.a5 | PF0_1 | | WS.001.3a
1352 PS.base.a6 | PF0_1 | | WS.001.3b
1353 PS.base.b1 | PF0_1 | | WS.002.1a
1354 PS.base.b2 | PF0_1 | | WS.002.1b
1355 PS.base.b3 | PF0_1 | | WS.002.2a
1356 PS.base.b4 | PF0_1 | | WS.002.2b
1357 PS.base.b5 | PF0_1 | | WS.002.3a
1358 PS.base.b6 | PF0_1 | | WS.002.3b
1359 PS.base.c1 | PF0_1 | | WS.003.1a
1360 PS.base.c2 | PF0_1 | | WS.003.1b
1361 PS.base.c3 | PF0_1 | | WS.003.2a
1362 PS.base.c4 | PF0_1 | | WS.003.2b
1363 PS.base.c5 | PF0_1 | | WS.003.3a
1364 PS.base.c6 | PF0_1 | | WS.003.3b
1365 PS.base.ta1 | PF0_2 | |
1366 PS.base.ta2 | PF0_2 | |
1367 PS.base.ta3 | PF0_2 | |
1368 PS.base.ta4 | PF0_2 | |
1369 PS.base.ta5 | PF0_2 | |
1370 PS.base.ta6 | PF0_2 | |
1371 PS.base.tb1 | PF0_2 | |
1372 PS.base.tb2 | PF0_2 | |
1373 PS.base.tb3 | PF0_2 | |
1374 PS.base.tb4 | PF0_2 | |
1375 PS.base.tb5 | PF0_2 | |
1376 PS.base.tb6 | PF0_2 | |
1377 (66 rows)
1379 select * from WSlot order by slotname;
1380 slotname | roomno | slotlink | backlink
1381 ----------------------+----------+----------------------+----------------------
1382 WS.001.1a | 001 | | PS.base.a1
1383 WS.001.1b | 001 | | PS.base.a2
1384 WS.001.2a | 001 | | PS.base.a3
1385 WS.001.2b | 001 | | PS.base.a4
1386 WS.001.3a | 001 | | PS.base.a5
1387 WS.001.3b | 001 | | PS.base.a6
1388 WS.002.1a | 002 | | PS.base.b1
1389 WS.002.1b | 002 | | PS.base.b2
1390 WS.002.2a | 002 | | PS.base.b3
1391 WS.002.2b | 002 | | PS.base.b4
1392 WS.002.3a | 002 | | PS.base.b5
1393 WS.002.3b | 002 | | PS.base.b6
1394 WS.003.1a | 003 | | PS.base.c1
1395 WS.003.1b | 003 | | PS.base.c2
1396 WS.003.2a | 003 | | PS.base.c3
1397 WS.003.2b | 003 | | PS.base.c4
1398 WS.003.3a | 003 | | PS.base.c5
1399 WS.003.3b | 003 | | PS.base.c6
1400 WS.101.1a | 101 | | PS.1st.a1
1401 WS.101.1b | 101 | | PS.1st.a2
1402 WS.101.2a | 101 | | PS.1st.a3
1403 WS.101.2b | 101 | | PS.1st.a4
1404 WS.101.3a | 101 | | PS.1st.a5
1405 WS.101.3b | 101 | | PS.1st.a6
1406 WS.102.1a | 102 | | PS.1st.b1
1407 WS.102.1b | 102 | | PS.1st.b2
1408 WS.102.2a | 102 | | PS.1st.b3
1409 WS.102.2b | 102 | | PS.1st.b4
1410 WS.102.3a | 102 | | PS.1st.b5
1411 WS.102.3b | 102 | | PS.1st.b6
1412 WS.105.1a | 105 | | PS.1st.c1
1413 WS.105.1b | 105 | | PS.1st.c2
1414 WS.105.2a | 105 | | PS.1st.c3
1415 WS.105.2b | 105 | | PS.1st.c4
1416 WS.105.3a | 105 | | PS.1st.c5
1417 WS.105.3b | 105 | | PS.1st.c6
1418 WS.106.1a | 106 | | PS.1st.d1
1419 WS.106.1b | 106 | | PS.1st.d2
1420 WS.106.2a | 106 | | PS.1st.d3
1421 WS.106.2b | 106 | | PS.1st.d4
1422 WS.106.3a | 106 | | PS.1st.d5
1423 WS.106.3b | 106 | | PS.1st.d6
1424 (42 rows)
1426 --
1427 -- Install the central phone system and create the phone numbers.
1428 -- They are weired on insert to the patchfields. Again the
1429 -- triggers automatically tell the PSlots to update their
1430 -- backlink field.
1431 --
1432 insert into PLine values ('PL.001', '-0', 'Central call', 'PS.base.ta1');
1433 insert into PLine values ('PL.002', '-101', '', 'PS.base.ta2');
1434 insert into PLine values ('PL.003', '-102', '', 'PS.base.ta3');
1435 insert into PLine values ('PL.004', '-103', '', 'PS.base.ta5');
1436 insert into PLine values ('PL.005', '-104', '', 'PS.base.ta6');
1437 insert into PLine values ('PL.006', '-106', '', 'PS.base.tb2');
1438 insert into PLine values ('PL.007', '-108', '', 'PS.base.tb3');
1439 insert into PLine values ('PL.008', '-109', '', 'PS.base.tb4');
1440 insert into PLine values ('PL.009', '-121', '', 'PS.base.tb5');
1441 insert into PLine values ('PL.010', '-122', '', 'PS.base.tb6');
1442 insert into PLine values ('PL.015', '-134', '', 'PS.1st.ta1');
1443 insert into PLine values ('PL.016', '-137', '', 'PS.1st.ta3');
1444 insert into PLine values ('PL.017', '-139', '', 'PS.1st.ta4');
1445 insert into PLine values ('PL.018', '-362', '', 'PS.1st.tb1');
1446 insert into PLine values ('PL.019', '-363', '', 'PS.1st.tb2');
1447 insert into PLine values ('PL.020', '-364', '', 'PS.1st.tb3');
1448 insert into PLine values ('PL.021', '-365', '', 'PS.1st.tb5');
1449 insert into PLine values ('PL.022', '-367', '', 'PS.1st.tb6');
1450 insert into PLine values ('PL.028', '-501', 'Fax entrance', 'PS.base.ta2');
1451 insert into PLine values ('PL.029', '-502', 'Fax 1st floor', 'PS.1st.ta1');
1452 --
1453 -- Buy some phones, plug them into the wall and patch the
1454 -- phone lines to the corresponding patchfield slots.
1455 --
1456 insert into PHone values ('PH.hc001', 'Hicom standard', 'WS.001.1a');
1457 update PSlot set slotlink = 'PS.base.ta1' where slotname = 'PS.base.a1';
1458 insert into PHone values ('PH.hc002', 'Hicom standard', 'WS.002.1a');
1459 update PSlot set slotlink = 'PS.base.ta5' where slotname = 'PS.base.b1';
1460 insert into PHone values ('PH.hc003', 'Hicom standard', 'WS.002.2a');
1461 update PSlot set slotlink = 'PS.base.tb2' where slotname = 'PS.base.b3';
1462 insert into PHone values ('PH.fax001', 'Canon fax', 'WS.001.2a');
1463 update PSlot set slotlink = 'PS.base.ta2' where slotname = 'PS.base.a3';
1464 --
1465 -- Install a hub at one of the patchfields, plug a computers
1466 -- ethernet interface into the wall and patch it to the hub.
1467 --
1468 insert into Hub values ('base.hub1', 'Patchfield PF0_1 hub', 16);
1469 insert into System values ('orion', 'PC');
1470 insert into IFace values ('IF', 'orion', 'eth0', 'WS.002.1b');
1471 update PSlot set slotlink = 'HS.base.hub1.1' where slotname = 'PS.base.b2';
1472 --
1473 -- Now we take a look at the patchfield
1474 --
1475 select * from PField_v1 where pfname = 'PF0_1' order by slotname;
1476 pfname | slotname | backside | patch
1477 --------+----------------------+----------------------------------------------------------+-----------------------------------------------
1478 PF0_1 | PS.base.a1 | WS.001.1a in room 001 -> Phone PH.hc001 (Hicom standard) | PS.base.ta1 -> Phone line -0 (Central call)
1479 PF0_1 | PS.base.a2 | WS.001.1b in room 001 -> - | -
1480 PF0_1 | PS.base.a3 | WS.001.2a in room 001 -> Phone PH.fax001 (Canon fax) | PS.base.ta2 -> Phone line -501 (Fax entrance)
1481 PF0_1 | PS.base.a4 | WS.001.2b in room 001 -> - | -
1482 PF0_1 | PS.base.a5 | WS.001.3a in room 001 -> - | -
1483 PF0_1 | PS.base.a6 | WS.001.3b in room 001 -> - | -
1484 PF0_1 | PS.base.b1 | WS.002.1a in room 002 -> Phone PH.hc002 (Hicom standard) | PS.base.ta5 -> Phone line -103
1485 PF0_1 | PS.base.b2 | WS.002.1b in room 002 -> orion IF eth0 (PC) | Patchfield PF0_1 hub slot 1
1486 PF0_1 | PS.base.b3 | WS.002.2a in room 002 -> Phone PH.hc003 (Hicom standard) | PS.base.tb2 -> Phone line -106
1487 PF0_1 | PS.base.b4 | WS.002.2b in room 002 -> - | -
1488 PF0_1 | PS.base.b5 | WS.002.3a in room 002 -> - | -
1489 PF0_1 | PS.base.b6 | WS.002.3b in room 002 -> - | -
1490 PF0_1 | PS.base.c1 | WS.003.1a in room 003 -> - | -
1491 PF0_1 | PS.base.c2 | WS.003.1b in room 003 -> - | -
1492 PF0_1 | PS.base.c3 | WS.003.2a in room 003 -> - | -
1493 PF0_1 | PS.base.c4 | WS.003.2b in room 003 -> - | -
1494 PF0_1 | PS.base.c5 | WS.003.3a in room 003 -> - | -
1495 PF0_1 | PS.base.c6 | WS.003.3b in room 003 -> - | -
1496 (18 rows)
1498 select * from PField_v1 where pfname = 'PF0_2' order by slotname;
1499 pfname | slotname | backside | patch
1500 --------+----------------------+--------------------------------+------------------------------------------------------------------------
1501 PF0_2 | PS.base.ta1 | Phone line -0 (Central call) | PS.base.a1 -> WS.001.1a in room 001 -> Phone PH.hc001 (Hicom standard)
1502 PF0_2 | PS.base.ta2 | Phone line -501 (Fax entrance) | PS.base.a3 -> WS.001.2a in room 001 -> Phone PH.fax001 (Canon fax)
1503 PF0_2 | PS.base.ta3 | Phone line -102 | -
1504 PF0_2 | PS.base.ta4 | - | -
1505 PF0_2 | PS.base.ta5 | Phone line -103 | PS.base.b1 -> WS.002.1a in room 002 -> Phone PH.hc002 (Hicom standard)
1506 PF0_2 | PS.base.ta6 | Phone line -104 | -
1507 PF0_2 | PS.base.tb1 | - | -
1508 PF0_2 | PS.base.tb2 | Phone line -106 | PS.base.b3 -> WS.002.2a in room 002 -> Phone PH.hc003 (Hicom standard)
1509 PF0_2 | PS.base.tb3 | Phone line -108 | -
1510 PF0_2 | PS.base.tb4 | Phone line -109 | -
1511 PF0_2 | PS.base.tb5 | Phone line -121 | -
1512 PF0_2 | PS.base.tb6 | Phone line -122 | -
1513 (12 rows)
1515 --
1516 -- Finally we want errors
1517 --
1518 insert into PField values ('PF1_1', 'should fail due to unique index');
1519 ERROR: duplicate key value violates unique constraint "pfield_name"
1520 update PSlot set backlink = 'WS.not.there' where slotname = 'PS.base.a1';
1521 ERROR: WS.not.there does not exist
1522 CONTEXT: PL/pgSQL function "tg_backlink_a" line 16 at assignment
1523 update PSlot set backlink = 'XX.illegal' where slotname = 'PS.base.a1';
1524 ERROR: illegal backlink beginning with XX
1525 CONTEXT: PL/pgSQL function "tg_backlink_a" line 16 at assignment
1526 update PSlot set slotlink = 'PS.not.there' where slotname = 'PS.base.a1';
1527 ERROR: PS.not.there does not exist
1528 CONTEXT: PL/pgSQL function "tg_slotlink_a" line 16 at assignment
1529 update PSlot set slotlink = 'XX.illegal' where slotname = 'PS.base.a1';
1530 ERROR: illegal slotlink beginning with XX
1531 CONTEXT: PL/pgSQL function "tg_slotlink_a" line 16 at assignment
1532 insert into HSlot values ('HS', 'base.hub1', 1, '');
1533 ERROR: duplicate key value violates unique constraint "hslot_name"
1534 insert into HSlot values ('HS', 'base.hub1', 20, '');
1535 ERROR: no manual manipulation of HSlot
1536 delete from 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)
1542 --
1543 -- The following tests are unrelated to the scenario outlined above;
1544 -- they merely exercise specific parts of PL/PgSQL
1545 --
1546 --
1547 -- Test recursion, per bug report 7-Sep-01
1548 --
1549 CREATE FUNCTION recursion_test(int,int) RETURNS text AS '
1550 DECLARE rslt text;
1551 BEGIN
1552 IF $1 <= 0 THEN
1553 rslt = CAST($2 AS TEXT);
1554 ELSE
1555 rslt = CAST($1 AS TEXT) || '','' || recursion_test($1 - 1, $2);
1556 END IF;
1557 RETURN rslt;
1558 END;' LANGUAGE plpgsql;
1559 SELECT recursion_test(4,3);
1560 recursion_test
1561 ----------------
1562 4,3,2,1,3
1563 (1 row)
1565 --
1566 -- Test the FOUND magic variable
1567 --
1568 CREATE TABLE found_test_tbl (a int);
1569 create function test_found()
1570 returns boolean as '
1571 declare
1572 begin
1573 insert into found_test_tbl values (1);
1574 if FOUND then
1575 insert into found_test_tbl values (2);
1576 end if;
1578 update found_test_tbl set a = 100 where a = 1;
1579 if FOUND then
1580 insert into found_test_tbl values (3);
1581 end if;
1583 delete from found_test_tbl where a = 9999; -- matches no rows
1584 if not FOUND then
1585 insert into found_test_tbl values (4);
1586 end if;
1588 for i in 1 .. 10 loop
1589 -- no need to do anything
1590 end loop;
1591 if FOUND then
1592 insert into found_test_tbl values (5);
1593 end if;
1595 -- never executes the loop
1596 for i in 2 .. 1 loop
1597 -- no need to do anything
1598 end loop;
1599 if not FOUND then
1600 insert into found_test_tbl values (6);
1601 end if;
1602 return true;
1603 end;' language plpgsql;
1604 select test_found();
1605 test_found
1606 ------------
1607 t
1608 (1 row)
1610 select * from found_test_tbl;
1611 a
1612 -----
1613 2
1614 100
1615 3
1616 4
1617 5
1618 6
1619 (6 rows)
1621 --
1622 -- Test set-returning functions for PL/pgSQL
1623 --
1624 create function test_table_func_rec() returns setof found_test_tbl as '
1625 DECLARE
1626 rec RECORD;
1627 BEGIN
1628 FOR rec IN select * from found_test_tbl LOOP
1629 RETURN NEXT rec;
1630 END LOOP;
1631 RETURN;
1632 END;' language plpgsql;
1633 select * from test_table_func_rec();
1634 a
1635 -----
1636 2
1637 100
1638 3
1639 4
1640 5
1641 6
1642 (6 rows)
1644 create function test_table_func_row() returns setof found_test_tbl as '
1645 DECLARE
1646 row found_test_tbl%ROWTYPE;
1647 BEGIN
1648 FOR row IN select * from found_test_tbl LOOP
1649 RETURN NEXT row;
1650 END LOOP;
1651 RETURN;
1652 END;' language plpgsql;
1653 select * from test_table_func_row();
1654 a
1655 -----
1656 2
1657 100
1658 3
1659 4
1660 5
1661 6
1662 (6 rows)
1664 create function test_ret_set_scalar(int,int) returns setof int as '
1665 DECLARE
1666 i int;
1667 BEGIN
1668 FOR i IN $1 .. $2 LOOP
1669 RETURN NEXT i + 1;
1670 END LOOP;
1671 RETURN;
1672 END;' language plpgsql;
1673 select * from test_ret_set_scalar(1,10);
1674 test_ret_set_scalar
1675 ---------------------
1676 2
1677 3
1678 4
1679 5
1680 6
1681 7
1682 8
1683 9
1684 10
1685 11
1686 (10 rows)
1688 create function test_ret_set_rec_dyn(int) returns setof record as '
1689 DECLARE
1690 retval RECORD;
1691 BEGIN
1692 IF $1 > 10 THEN
1693 SELECT INTO retval 5, 10, 15;
1694 RETURN NEXT retval;
1695 RETURN NEXT retval;
1696 ELSE
1697 SELECT INTO retval 50, 5::numeric, ''xxx''::text;
1698 RETURN NEXT retval;
1699 RETURN NEXT retval;
1700 END IF;
1701 RETURN;
1702 END;' language plpgsql;
1703 SELECT * FROM test_ret_set_rec_dyn(1500) AS (a int, b int, c int);
1704 a | b | c
1705 ---+----+----
1706 5 | 10 | 15
1707 5 | 10 | 15
1708 (2 rows)
1710 SELECT * FROM test_ret_set_rec_dyn(5) AS (a int, b numeric, c text);
1711 a | b | c
1712 ----+---+-----
1713 50 | 5 | xxx
1714 50 | 5 | xxx
1715 (2 rows)
1717 create function test_ret_rec_dyn(int) returns record as '
1718 DECLARE
1719 retval RECORD;
1720 BEGIN
1721 IF $1 > 10 THEN
1722 SELECT INTO retval 5, 10, 15;
1723 RETURN retval;
1724 ELSE
1725 SELECT INTO retval 50, 5::numeric, ''xxx''::text;
1726 RETURN retval;
1727 END IF;
1728 END;' language plpgsql;
1729 SELECT * FROM test_ret_rec_dyn(1500) AS (a int, b int, c int);
1730 a | b | c
1731 ---+----+----
1732 5 | 10 | 15
1733 (1 row)
1735 SELECT * FROM test_ret_rec_dyn(5) AS (a int, b numeric, c text);
1736 a | b | c
1737 ----+---+-----
1738 50 | 5 | xxx
1739 (1 row)
1741 --
1742 -- Test handling of OUT parameters, including polymorphic cases.
1743 -- Note that RETURN is optional with OUT params; we try both ways.
1744 --
1745 -- wrong way to do it:
1746 create function f1(in i int, out j int) returns int as $$
1747 begin
1748 return i+1;
1749 end$$ language plpgsql;
1750 ERROR: RETURN cannot have a parameter in function with OUT parameters at or near "i"
1751 LINE 3: return i+1;
1752 ^
1753 create function f1(in i int, out j int) as $$
1754 begin
1755 j := i+1;
1756 return;
1757 end$$ language plpgsql;
1758 select f1(42);
1759 f1
1760 ----
1761 43
1762 (1 row)
1764 select * from f1(42);
1765 j
1766 ----
1767 43
1768 (1 row)
1770 create or replace function f1(inout i int) as $$
1771 begin
1772 i := i+1;
1773 end$$ language plpgsql;
1774 select f1(42);
1775 f1
1776 ----
1777 43
1778 (1 row)
1780 select * from f1(42);
1781 i
1782 ----
1783 43
1784 (1 row)
1786 drop function f1(int);
1787 create function f1(in i int, out j int) returns setof int as $$
1788 begin
1789 j := i+1;
1790 return next;
1791 j := i+2;
1792 return next;
1793 return;
1794 end$$ language plpgsql;
1795 select * from f1(42);
1796 j
1797 ----
1798 43
1799 44
1800 (2 rows)
1802 drop function f1(int);
1803 create function f1(in i int, out j int, out k text) as $$
1804 begin
1805 j := i;
1806 j := j+1;
1807 k := 'foo';
1808 end$$ language plpgsql;
1809 select f1(42);
1810 f1
1811 ----------
1812 (43,foo)
1813 (1 row)
1815 select * from f1(42);
1816 j | k
1817 ----+-----
1818 43 | foo
1819 (1 row)
1821 drop function f1(int);
1822 create function f1(in i int, out j int, out k text) returns setof record as $$
1823 begin
1824 j := i+1;
1825 k := 'foo';
1826 return next;
1827 j := j+1;
1828 k := 'foot';
1829 return next;
1830 end$$ language plpgsql;
1831 select * from f1(42);
1832 j | k
1833 ----+------
1834 43 | foo
1835 44 | foot
1836 (2 rows)
1838 drop function f1(int);
1839 create function duplic(in i anyelement, out j anyelement, out k anyarray) as $$
1840 begin
1841 j := i;
1842 k := array[j,j];
1843 return;
1844 end$$ language plpgsql;
1845 select * from duplic(42);
1846 j | k
1847 ----+---------
1848 42 | {42,42}
1849 (1 row)
1851 select * from duplic('foo'::text);
1852 j | k
1853 -----+-----------
1854 foo | {foo,foo}
1855 (1 row)
1857 drop function duplic(anyelement);
1858 --
1859 -- test PERFORM
1860 --
1861 create table perform_test (
1862 a INT,
1863 b INT
1864 );
1865 create function simple_func(int) returns boolean as '
1866 BEGIN
1867 IF $1 < 20 THEN
1868 INSERT INTO perform_test VALUES ($1, $1 + 10);
1869 RETURN TRUE;
1870 ELSE
1871 RETURN FALSE;
1872 END IF;
1873 END;' language plpgsql;
1874 create function perform_test_func() returns void as '
1875 BEGIN
1876 IF FOUND then
1877 INSERT INTO perform_test VALUES (100, 100);
1878 END IF;
1880 PERFORM simple_func(5);
1882 IF FOUND then
1883 INSERT INTO perform_test VALUES (100, 100);
1884 END IF;
1886 PERFORM simple_func(50);
1888 IF FOUND then
1889 INSERT INTO perform_test VALUES (100, 100);
1890 END IF;
1892 RETURN;
1893 END;' language plpgsql;
1894 SELECT perform_test_func();
1895 perform_test_func
1896 -------------------
1898 (1 row)
1900 SELECT * FROM perform_test;
1901 a | b
1902 -----+-----
1903 5 | 15
1904 100 | 100
1905 100 | 100
1906 (3 rows)
1908 drop table perform_test;
1909 --
1910 -- Test error trapping
1911 --
1912 create function trap_zero_divide(int) returns int as $$
1913 declare x int;
1914 sx smallint;
1915 begin
1916 begin -- start a subtransaction
1917 raise notice 'should see this';
1918 x := 100 / $1;
1919 raise notice 'should see this only if % <> 0', $1;
1920 sx := $1;
1921 raise notice 'should see this only if % fits in smallint', $1;
1922 if $1 < 0 then
1923 raise exception '% is less than zero', $1;
1924 end if;
1925 exception
1926 when division_by_zero then
1927 raise notice 'caught division_by_zero';
1928 x := -1;
1929 when NUMERIC_VALUE_OUT_OF_RANGE then
1930 raise notice 'caught numeric_value_out_of_range';
1931 x := -2;
1932 end;
1933 return x;
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
1939 trap_zero_divide
1940 ------------------
1941 2
1942 (1 row)
1944 select trap_zero_divide(0);
1945 NOTICE: should see this
1946 NOTICE: caught division_by_zero
1947 trap_zero_divide
1948 ------------------
1949 -1
1950 (1 row)
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
1956 trap_zero_divide
1957 ------------------
1958 -2
1959 (1 row)
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 $$
1967 declare x int;
1968 sx smallint;
1969 y int;
1970 begin
1971 begin -- start a subtransaction
1972 x := 100 / $1;
1973 sx := $1;
1974 select into y unique1 from tenk1 where unique2 =
1975 (select unique2 from tenk1 b where ten = $1);
1976 exception
1977 when data_exception then -- category match
1978 raise notice 'caught data_exception';
1979 x := -1;
1980 when NUMERIC_VALUE_OUT_OF_RANGE OR CARDINALITY_VIOLATION then
1981 raise notice 'caught numeric_value_out_of_range or cardinality_violation';
1982 x := -2;
1983 end;
1984 return x;
1985 end$$ language plpgsql;
1986 select trap_matching_test(50);
1987 trap_matching_test
1988 --------------------
1989 2
1990 (1 row)
1992 select trap_matching_test(0);
1993 NOTICE: caught data_exception
1994 trap_matching_test
1995 --------------------
1996 -1
1997 (1 row)
1999 select trap_matching_test(100000);
2000 NOTICE: caught data_exception
2001 trap_matching_test
2002 --------------------
2003 -1
2004 (1 row)
2006 select trap_matching_test(1);
2007 NOTICE: caught numeric_value_out_of_range or cardinality_violation
2008 trap_matching_test
2009 --------------------
2010 -2
2011 (1 row)
2013 create temp table foo (f1 int);
2014 create function blockme() returns int as $$
2015 declare x int;
2016 begin
2017 x := 1;
2018 insert into foo values(x);
2019 begin
2020 x := x + 1;
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;
2024 exception
2025 when others then
2026 raise notice 'caught others?';
2027 return -1;
2028 when query_canceled then
2029 raise notice 'nyeah nyeah, can''t stop me';
2030 x := x * 10;
2031 end;
2032 insert into foo values(x);
2033 return x;
2034 end$$ language plpgsql;
2035 set statement_timeout to 2000;
2036 select blockme();
2037 NOTICE: nyeah nyeah, can't stop me
2038 blockme
2039 ---------
2040 20
2041 (1 row)
2043 reset statement_timeout;
2044 select * from foo;
2045 f1
2046 ----
2047 1
2048 20
2049 (2 rows)
2051 drop table foo;
2052 -- Test for pass-by-ref values being stored in proper context
2053 create function test_variable_storage() returns text as $$
2054 declare x text;
2055 begin
2056 x := '1234';
2057 begin
2058 x := x || '5678';
2059 -- force error inside subtransaction SPI context
2060 perform trap_zero_divide(-100);
2061 exception
2062 when others then
2063 x := x || '9012';
2064 end;
2065 return x;
2066 end$$ language plpgsql;
2067 select test_variable_storage();
2068 NOTICE: should see this
2069 CONTEXT: SQL statement "SELECT trap_zero_divide(-100)"
2070 PL/pgSQL function "test_variable_storage" line 7 at PERFORM
2071 NOTICE: should see this only if -100 <> 0
2072 CONTEXT: SQL statement "SELECT trap_zero_divide(-100)"
2073 PL/pgSQL function "test_variable_storage" line 7 at PERFORM
2074 NOTICE: should see this only if -100 fits in smallint
2075 CONTEXT: SQL statement "SELECT trap_zero_divide(-100)"
2076 PL/pgSQL function "test_variable_storage" line 7 at PERFORM
2077 test_variable_storage
2078 -----------------------
2079 123456789012
2080 (1 row)
2082 --
2083 -- test foreign key error trapping
2084 --
2085 create temp table master(f1 int primary key);
2086 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "master_pkey" for table "master"
2087 create temp table slave(f1 int references master deferrable);
2088 insert into master values(1);
2089 insert into slave values(1);
2090 insert into slave values(2); -- fails
2091 ERROR: insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
2092 DETAIL: Key (f1)=(2) is not present in table "master".
2093 create function trap_foreign_key(int) returns int as $$
2094 begin
2095 begin -- start a subtransaction
2096 insert into slave values($1);
2097 exception
2098 when foreign_key_violation then
2099 raise notice 'caught foreign_key_violation';
2100 return 0;
2101 end;
2102 return 1;
2103 end$$ language plpgsql;
2104 create function trap_foreign_key_2() returns int as $$
2105 begin
2106 begin -- start a subtransaction
2107 set constraints all immediate;
2108 exception
2109 when foreign_key_violation then
2110 raise notice 'caught foreign_key_violation';
2111 return 0;
2112 end;
2113 return 1;
2114 end$$ language plpgsql;
2115 select trap_foreign_key(1);
2116 trap_foreign_key
2117 ------------------
2118 1
2119 (1 row)
2121 select trap_foreign_key(2); -- detects FK violation
2122 NOTICE: caught foreign_key_violation
2123 trap_foreign_key
2124 ------------------
2125 0
2126 (1 row)
2128 begin;
2129 set constraints all deferred;
2130 select trap_foreign_key(2); -- should not detect FK violation
2131 trap_foreign_key
2132 ------------------
2133 1
2134 (1 row)
2136 savepoint x;
2137 set constraints all immediate; -- fails
2138 ERROR: insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
2139 DETAIL: Key (f1)=(2) is not present in table "master".
2140 rollback to x;
2141 select trap_foreign_key_2(); -- detects FK violation
2142 NOTICE: caught foreign_key_violation
2143 trap_foreign_key_2
2144 --------------------
2145 0
2146 (1 row)
2148 commit; -- still fails
2149 ERROR: insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
2150 DETAIL: Key (f1)=(2) is not present in table "master".
2151 drop function trap_foreign_key(int);
2152 drop function trap_foreign_key_2();
2153 --
2154 -- Test proper snapshot handling in simple expressions
2155 --
2156 create temp table users(login text, id serial);
2157 NOTICE: CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id"
2158 create function sp_id_user(a_login text) returns int as $$
2159 declare x int;
2160 begin
2161 select into x id from users where login = a_login;
2162 if found then return x; end if;
2163 return 0;
2164 end$$ language plpgsql stable;
2165 insert into users values('user1');
2166 select sp_id_user('user1');
2167 sp_id_user
2168 ------------
2169 1
2170 (1 row)
2172 select sp_id_user('userx');
2173 sp_id_user
2174 ------------
2175 0
2176 (1 row)
2178 create function sp_add_user(a_login text) returns int as $$
2179 declare my_id_user int;
2180 begin
2181 my_id_user = sp_id_user( a_login );
2182 IF my_id_user > 0 THEN
2183 RETURN -1; -- error code for existing user
2184 END IF;
2185 INSERT INTO users ( login ) VALUES ( a_login );
2186 my_id_user = sp_id_user( a_login );
2187 IF my_id_user = 0 THEN
2188 RETURN -2; -- error code for insertion failure
2189 END IF;
2190 RETURN my_id_user;
2191 end$$ language plpgsql;
2192 select sp_add_user('user1');
2193 sp_add_user
2194 -------------
2195 -1
2196 (1 row)
2198 select sp_add_user('user2');
2199 sp_add_user
2200 -------------
2201 2
2202 (1 row)
2204 select sp_add_user('user2');
2205 sp_add_user
2206 -------------
2207 -1
2208 (1 row)
2210 select sp_add_user('user3');
2211 sp_add_user
2212 -------------
2213 3
2214 (1 row)
2216 select sp_add_user('user3');
2217 sp_add_user
2218 -------------
2219 -1
2220 (1 row)
2222 drop function sp_add_user(text);
2223 drop function sp_id_user(text);
2224 --
2225 -- tests for refcursors
2226 --
2227 create table rc_test (a int, b int);
2228 copy rc_test from stdin;
2229 create function return_refcursor(rc refcursor) returns refcursor as $$
2230 begin
2231 open rc for select a from rc_test;
2232 return rc;
2233 end
2234 $$ language plpgsql;
2235 create function refcursor_test1(refcursor) returns refcursor as $$
2236 begin
2237 perform return_refcursor($1);
2238 return $1;
2239 end
2240 $$ language plpgsql;
2241 begin;
2242 select refcursor_test1('test1');
2243 refcursor_test1
2244 -----------------
2245 test1
2246 (1 row)
2248 fetch next in test1;
2249 a
2250 ---
2251 5
2252 (1 row)
2254 select refcursor_test1('test2');
2255 refcursor_test1
2256 -----------------
2257 test2
2258 (1 row)
2260 fetch all from test2;
2261 a
2262 -----
2263 5
2264 50
2265 500
2266 (3 rows)
2268 commit;
2269 -- should fail
2270 fetch next from test1;
2271 ERROR: cursor "test1" does not exist
2272 create function refcursor_test2(int, int) returns boolean as $$
2273 declare
2274 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
2275 nonsense record;
2276 begin
2277 open c1($1, $2);
2278 fetch c1 into nonsense;
2279 close c1;
2280 if found then
2281 return true;
2282 else
2283 return false;
2284 end if;
2285 end
2286 $$ language plpgsql;
2287 select refcursor_test2(20000, 20000) as "Should be false",
2288 refcursor_test2(20, 20) as "Should be true";
2289 Should be false | Should be true
2290 -----------------+----------------
2291 f | t
2292 (1 row)
2294 --
2295 -- tests for "raise" processing
2296 --
2297 create function raise_test1(int) returns int as $$
2298 begin
2299 raise notice 'This message has too many parameters!', $1;
2300 return $1;
2301 end;
2302 $$ language plpgsql;
2303 select raise_test1(5);
2304 ERROR: too many parameters specified for RAISE
2305 CONTEXT: PL/pgSQL function "raise_test1" line 2 at RAISE
2306 create function raise_test2(int) returns int as $$
2307 begin
2308 raise notice 'This message has too few parameters: %, %, %', $1, $1;
2309 return $1;
2310 end;
2311 $$ language plpgsql;
2312 select raise_test2(10);
2313 ERROR: too few parameters specified for RAISE
2314 CONTEXT: PL/pgSQL function "raise_test2" line 2 at RAISE
2315 --
2316 -- reject function definitions that contain malformed SQL queries at
2317 -- compile-time, where possible
2318 --
2319 create function bad_sql1() returns int as $$
2320 declare a int;
2321 begin
2322 a := 5;
2323 Johnny Yuma;
2324 a := 10;
2325 return a;
2326 end$$ language plpgsql;
2327 ERROR: syntax error at or near "Johnny"
2328 LINE 1: Johnny Yuma
2329 ^
2330 QUERY: Johnny Yuma
2331 CONTEXT: SQL statement in PL/PgSQL function "bad_sql1" near line 4
2332 create function bad_sql2() returns int as $$
2333 declare r record;
2334 begin
2335 for r in select I fought the law, the law won LOOP
2336 raise notice 'in loop';
2337 end loop;
2338 return 5;
2339 end;$$ language plpgsql;
2340 ERROR: syntax error at or near "the"
2341 LINE 1: select I fought the law, the law won
2342 ^
2343 QUERY: select I fought the law, the law won
2344 CONTEXT: SQL statement in PL/PgSQL function "bad_sql2" near line 3
2345 -- a RETURN expression is mandatory, except for void-returning
2346 -- functions, where it is not allowed
2347 create function missing_return_expr() returns int as $$
2348 begin
2349 return ;
2350 end;$$ language plpgsql;
2351 ERROR: syntax error at end of input
2352 LINE 1: SELECT
2353 ^
2354 QUERY: SELECT
2355 CONTEXT: SQL statement in PL/PgSQL function "missing_return_expr" near line 2
2356 create function void_return_expr() returns void as $$
2357 begin
2358 return 5;
2359 end;$$ language plpgsql;
2360 ERROR: RETURN cannot have a parameter in function returning void at or near "5"
2361 LINE 3: return 5;
2362 ^
2363 -- VOID functions are allowed to omit RETURN
2364 create function void_return_expr() returns void as $$
2365 begin
2366 perform 2+2;
2367 end;$$ language plpgsql;
2368 select void_return_expr();
2369 void_return_expr
2370 ------------------
2372 (1 row)
2374 -- but ordinary functions are not
2375 create function missing_return_expr() returns int as $$
2376 begin
2377 perform 2+2;
2378 end;$$ language plpgsql;
2379 select missing_return_expr();
2380 ERROR: control reached end of function without RETURN
2381 CONTEXT: PL/pgSQL function "missing_return_expr"
2382 drop function void_return_expr();
2383 drop function missing_return_expr();
2384 --
2385 -- EXECUTE ... INTO test
2386 --
2387 create table eifoo (i integer, y integer);
2388 create type eitype as (i integer, y integer);
2389 create or replace function execute_into_test(varchar) returns record as $$
2390 declare
2391 _r record;
2392 _rt eifoo%rowtype;
2393 _v eitype;
2394 i int;
2395 j int;
2396 k int;
2397 begin
2398 execute 'insert into '||$1||' values(10,15)';
2399 execute 'select (row).* from (select row(10,1)::eifoo) s' into _r;
2400 raise notice '% %', _r.i, _r.y;
2401 execute 'select * from '||$1||' limit 1' into _rt;
2402 raise notice '% %', _rt.i, _rt.y;
2403 execute 'select *, 20 from '||$1||' limit 1' into i, j, k;
2404 raise notice '% % %', i, j, k;
2405 execute 'select 1,2' into _v;
2406 return _v;
2407 end; $$ language plpgsql;
2408 select execute_into_test('eifoo');
2409 NOTICE: 10 1
2410 NOTICE: 10 15
2411 NOTICE: 10 15 20
2412 execute_into_test
2413 -------------------
2414 (1,2)
2415 (1 row)
2417 drop table eifoo cascade;
2418 drop type eitype cascade;
2419 --
2420 -- SQLSTATE and SQLERRM test
2421 --
2422 create function excpt_test1() returns void as $$
2423 begin
2424 raise notice '% %', sqlstate, sqlerrm;
2425 end; $$ language plpgsql;
2426 -- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
2427 -- blocks
2428 select excpt_test1();
2429 ERROR: column "sqlstate" does not exist
2430 LINE 1: SELECT sqlstate
2431 ^
2432 QUERY: SELECT sqlstate
2433 CONTEXT: PL/pgSQL function "excpt_test1" line 2 at RAISE
2434 create function excpt_test2() returns void as $$
2435 begin
2436 begin
2437 begin
2438 raise notice '% %', sqlstate, sqlerrm;
2439 end;
2440 end;
2441 end; $$ language plpgsql;
2442 -- should fail
2443 select excpt_test2();
2444 ERROR: column "sqlstate" does not exist
2445 LINE 1: SELECT sqlstate
2446 ^
2447 QUERY: SELECT sqlstate
2448 CONTEXT: PL/pgSQL function "excpt_test2" line 4 at RAISE
2449 create function excpt_test3() returns void as $$
2450 begin
2451 begin
2452 raise exception 'user exception';
2453 exception when others then
2454 raise notice 'caught exception % %', sqlstate, sqlerrm;
2455 begin
2456 raise notice '% %', sqlstate, sqlerrm;
2457 perform 10/0;
2458 exception
2459 when substring_error then
2460 -- this exception handler shouldn't be invoked
2461 raise notice 'unexpected exception: % %', sqlstate, sqlerrm;
2462 when division_by_zero then
2463 raise notice 'caught exception % %', sqlstate, sqlerrm;
2464 end;
2465 raise notice '% %', sqlstate, sqlerrm;
2466 end;
2467 end; $$ language plpgsql;
2468 select excpt_test3();
2469 NOTICE: caught exception P0001 user exception
2470 NOTICE: P0001 user exception
2471 NOTICE: caught exception 22012 division by zero
2472 NOTICE: P0001 user exception
2473 excpt_test3
2474 -------------
2476 (1 row)
2478 drop function excpt_test1();
2479 drop function excpt_test2();
2480 drop function excpt_test3();
2481 -- parameters of raise stmt can be expressions
2482 create function raise_exprs() returns void as $$
2483 declare
2484 a integer[] = '{10,20,30}';
2485 c varchar = 'xyz';
2486 i integer;
2487 begin
2488 i := 2;
2489 raise notice '%; %; %; %; %; %', a, a[i], c, (select c || 'abc'), row(10,'aaa',NULL,30), NULL;
2490 end;$$ language plpgsql;
2491 select raise_exprs();
2492 NOTICE: {10,20,30}; 20; xyz; xyzabc; (10,aaa,,30); <NULL>
2493 raise_exprs
2494 -------------
2496 (1 row)
2498 drop function raise_exprs();
2499 -- continue statement
2500 create table conttesttbl(idx serial, v integer);
2501 NOTICE: CREATE TABLE will create implicit sequence "conttesttbl_idx_seq" for serial column "conttesttbl.idx"
2502 insert into conttesttbl(v) values(10);
2503 insert into conttesttbl(v) values(20);
2504 insert into conttesttbl(v) values(30);
2505 insert into conttesttbl(v) values(40);
2506 create function continue_test1() returns void as $$
2507 declare _i integer = 0; _r record;
2508 begin
2509 raise notice '---1---';
2510 loop
2511 _i := _i + 1;
2512 raise notice '%', _i;
2513 continue when _i < 10;
2514 exit;
2515 end loop;
2517 raise notice '---2---';
2518 <<lbl>>
2519 loop
2520 _i := _i - 1;
2521 loop
2522 raise notice '%', _i;
2523 continue lbl when _i > 0;
2524 exit lbl;
2525 end loop;
2526 end loop;
2528 raise notice '---3---';
2529 <<the_loop>>
2530 while _i < 10 loop
2531 _i := _i + 1;
2532 continue the_loop when _i % 2 = 0;
2533 raise notice '%', _i;
2534 end loop;
2536 raise notice '---4---';
2537 for _i in 1..10 loop
2538 begin
2539 -- applies to outer loop, not the nested begin block
2540 continue when _i < 5;
2541 raise notice '%', _i;
2542 end;
2543 end loop;
2545 raise notice '---5---';
2546 for _r in select * from conttesttbl loop
2547 continue when _r.v <= 20;
2548 raise notice '%', _r.v;
2549 end loop;
2551 raise notice '---6---';
2552 for _r in execute 'select * from conttesttbl' loop
2553 continue when _r.v <= 20;
2554 raise notice '%', _r.v;
2555 end loop;
2557 raise notice '---7---';
2558 for _i in 1..3 loop
2559 raise notice '%', _i;
2560 continue when _i = 3;
2561 end loop;
2563 raise notice '---8---';
2564 _i := 1;
2565 while _i <= 3 loop
2566 raise notice '%', _i;
2567 _i := _i + 1;
2568 continue when _i = 3;
2569 end loop;
2571 raise notice '---9---';
2572 for _r in select * from conttesttbl order by v limit 1 loop
2573 raise notice '%', _r.v;
2574 continue;
2575 end loop;
2577 raise notice '---10---';
2578 for _r in execute 'select * from conttesttbl order by v limit 1' loop
2579 raise notice '%', _r.v;
2580 continue;
2581 end loop;
2582 end; $$ language plpgsql;
2583 select continue_test1();
2584 NOTICE: ---1---
2585 NOTICE: 1
2586 NOTICE: 2
2587 NOTICE: 3
2588 NOTICE: 4
2589 NOTICE: 5
2590 NOTICE: 6
2591 NOTICE: 7
2592 NOTICE: 8
2593 NOTICE: 9
2594 NOTICE: 10
2595 NOTICE: ---2---
2596 NOTICE: 9
2597 NOTICE: 8
2598 NOTICE: 7
2599 NOTICE: 6
2600 NOTICE: 5
2601 NOTICE: 4
2602 NOTICE: 3
2603 NOTICE: 2
2604 NOTICE: 1
2605 NOTICE: 0
2606 NOTICE: ---3---
2607 NOTICE: 1
2608 NOTICE: 3
2609 NOTICE: 5
2610 NOTICE: 7
2611 NOTICE: 9
2612 NOTICE: ---4---
2613 NOTICE: 5
2614 NOTICE: 6
2615 NOTICE: 7
2616 NOTICE: 8
2617 NOTICE: 9
2618 NOTICE: 10
2619 NOTICE: ---5---
2620 NOTICE: 30
2621 NOTICE: 40
2622 NOTICE: ---6---
2623 NOTICE: 30
2624 NOTICE: 40
2625 NOTICE: ---7---
2626 NOTICE: 1
2627 NOTICE: 2
2628 NOTICE: 3
2629 NOTICE: ---8---
2630 NOTICE: 1
2631 NOTICE: 2
2632 NOTICE: 3
2633 NOTICE: ---9---
2634 NOTICE: 10
2635 NOTICE: ---10---
2636 NOTICE: 10
2637 continue_test1
2638 ----------------
2640 (1 row)
2642 -- CONTINUE is only legal inside a loop
2643 create function continue_test2() returns void as $$
2644 begin
2645 begin
2646 continue;
2647 end;
2648 return;
2649 end;
2650 $$ language plpgsql;
2651 -- should fail
2652 select continue_test2();
2653 ERROR: CONTINUE cannot be used outside a loop
2654 CONTEXT: PL/pgSQL function "continue_test2"
2655 -- CONTINUE can't reference the label of a named block
2656 create function continue_test3() returns void as $$
2657 begin
2658 <<begin_block1>>
2659 begin
2660 loop
2661 continue begin_block1;
2662 end loop;
2663 end;
2664 end;
2665 $$ language plpgsql;
2666 -- should fail
2667 select continue_test3();
2668 ERROR: CONTINUE cannot be used outside a loop
2669 CONTEXT: PL/pgSQL function "continue_test3"
2670 drop function continue_test1();
2671 drop function continue_test2();
2672 drop function continue_test3();
2673 drop table conttesttbl;
2674 -- verbose end block and end loop
2675 create function end_label1() returns void as $$
2676 <<blbl>>
2677 begin
2678 <<flbl1>>
2679 for _i in 1 .. 10 loop
2680 exit flbl1;
2681 end loop flbl1;
2682 <<flbl2>>
2683 for _i in 1 .. 10 loop
2684 exit flbl2;
2685 end loop;
2686 end blbl;
2687 $$ language plpgsql;
2688 select end_label1();
2689 end_label1
2690 ------------
2692 (1 row)
2694 drop function end_label1();
2695 -- should fail: undefined end label
2696 create function end_label2() returns void as $$
2697 begin
2698 for _i in 1 .. 10 loop
2699 exit;
2700 end loop flbl1;
2701 end;
2702 $$ language plpgsql;
2703 ERROR: no such label at or near "flbl1"
2704 LINE 5: end loop flbl1;
2705 ^
2706 -- should fail: end label does not match start label
2707 create function end_label3() returns void as $$
2708 <<outer_label>>
2709 begin
2710 <<inner_label>>
2711 for _i in 1 .. 10 loop
2712 exit;
2713 end loop outer_label;
2714 end;
2715 $$ language plpgsql;
2716 ERROR: end label "outer_label" differs from block's label "inner_label"
2717 CONTEXT: compile of PL/pgSQL function "end_label3" near line 6
2718 -- should fail: end label on a block without a start label
2719 create function end_label4() returns void as $$
2720 <<outer_label>>
2721 begin
2722 for _i in 1 .. 10 loop
2723 exit;
2724 end loop outer_label;
2725 end;
2726 $$ language plpgsql;
2727 ERROR: end label "outer_label" specified for unlabelled block
2728 CONTEXT: compile of PL/pgSQL function "end_label4" near line 5
2729 -- using list of scalars in fori and fore stmts
2730 create function for_vect() returns void as $proc$
2731 <<lbl>>declare a integer; b varchar; c varchar; r record;
2732 begin
2733 -- fori
2734 for i in 1 .. 3 loop
2735 raise notice '%', i;
2736 end loop;
2737 -- fore with record var
2738 for r in select gs as aa, 'BB' as bb, 'CC' as cc from generate_series(1,4) gs loop
2739 raise notice '% % %', r.aa, r.bb, r.cc;
2740 end loop;
2741 -- fore with single scalar
2742 for a in select gs from generate_series(1,4) gs loop
2743 raise notice '%', a;
2744 end loop;
2745 -- fore with multiple scalars
2746 for a,b,c in select gs, 'BB','CC' from generate_series(1,4) gs loop
2747 raise notice '% % %', a, b, c;
2748 end loop;
2749 -- using qualified names in fors, fore is enabled, disabled only for fori
2750 for lbl.a, lbl.b, lbl.c in execute $$select gs, 'bb','cc' from generate_series(1,4) gs$$ loop
2751 raise notice '% % %', a, b, c;
2752 end loop;
2753 end;
2754 $proc$ language plpgsql;
2755 select for_vect();
2756 NOTICE: 1
2757 NOTICE: 2
2758 NOTICE: 3
2759 NOTICE: 1 BB CC
2760 NOTICE: 2 BB CC
2761 NOTICE: 3 BB CC
2762 NOTICE: 4 BB CC
2763 NOTICE: 1
2764 NOTICE: 2
2765 NOTICE: 3
2766 NOTICE: 4
2767 NOTICE: 1 BB CC
2768 NOTICE: 2 BB CC
2769 NOTICE: 3 BB CC
2770 NOTICE: 4 BB CC
2771 NOTICE: 1 bb cc
2772 NOTICE: 2 bb cc
2773 NOTICE: 3 bb cc
2774 NOTICE: 4 bb cc
2775 for_vect
2776 ----------
2778 (1 row)
2780 -- regression test: verify that multiple uses of same plpgsql datum within
2781 -- a SQL command all get mapped to the same $n parameter. The return value
2782 -- of the SELECT is not important, we only care that it doesn't fail with
2783 -- a complaint about an ungrouped column reference.
2784 create function multi_datum_use(p1 int) returns bool as $$
2785 declare
2786 x int;
2787 y int;
2788 begin
2789 select into x,y unique1/p1, unique1/$1 from tenk1 group by unique1/p1;
2790 return x = y;
2791 end$$ language plpgsql;
2792 select multi_datum_use(42);
2793 multi_datum_use
2794 -----------------
2795 t
2796 (1 row)
2798 --
2799 -- Test STRICT limiter in both planned and EXECUTE invocations.
2800 -- Note that a data-modifying query is quasi strict (disallow multi rows)
2801 -- by default in the planned case, but not in EXECUTE.
2802 --
2803 create temp table foo (f1 int, f2 int);
2804 insert into foo values (1,2), (3,4);
2805 create or replace function footest() returns void as $$
2806 declare x record;
2807 begin
2808 -- should work
2809 insert into foo values(5,6) returning * into x;
2810 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2811 end$$ language plpgsql;
2812 select footest();
2813 NOTICE: x.f1 = 5, x.f2 = 6
2814 footest
2815 ---------
2817 (1 row)
2819 create or replace function footest() returns void as $$
2820 declare x record;
2821 begin
2822 -- should fail due to implicit strict
2823 insert into foo values(7,8),(9,10) returning * into x;
2824 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2825 end$$ language plpgsql;
2826 select footest();
2827 ERROR: query returned more than one row
2828 CONTEXT: PL/pgSQL function "footest" line 4 at SQL statement
2829 create or replace function footest() returns void as $$
2830 declare x record;
2831 begin
2832 -- should work
2833 execute 'insert into foo values(5,6) returning *' into x;
2834 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2835 end$$ language plpgsql;
2836 select footest();
2837 NOTICE: x.f1 = 5, x.f2 = 6
2838 footest
2839 ---------
2841 (1 row)
2843 create or replace function footest() returns void as $$
2844 declare x record;
2845 begin
2846 -- this should work since EXECUTE isn't as picky
2847 execute 'insert into foo values(7,8),(9,10) returning *' into x;
2848 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2849 end$$ language plpgsql;
2850 select footest();
2851 NOTICE: x.f1 = 7, x.f2 = 8
2852 footest
2853 ---------
2855 (1 row)
2857 select * from foo;
2858 f1 | f2
2859 ----+----
2860 1 | 2
2861 3 | 4
2862 5 | 6
2863 5 | 6
2864 7 | 8
2865 9 | 10
2866 (6 rows)
2868 create or replace function footest() returns void as $$
2869 declare x record;
2870 begin
2871 -- should work
2872 select * from foo where f1 = 3 into strict x;
2873 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2874 end$$ language plpgsql;
2875 select footest();
2876 NOTICE: x.f1 = 3, x.f2 = 4
2877 footest
2878 ---------
2880 (1 row)
2882 create or replace function footest() returns void as $$
2883 declare x record;
2884 begin
2885 -- should fail, no rows
2886 select * from foo where f1 = 0 into strict x;
2887 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2888 end$$ language plpgsql;
2889 select footest();
2890 ERROR: query returned no rows
2891 CONTEXT: PL/pgSQL function "footest" line 4 at SQL statement
2892 create or replace function footest() returns void as $$
2893 declare x record;
2894 begin
2895 -- should fail, too many rows
2896 select * from foo where f1 > 3 into strict x;
2897 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2898 end$$ language plpgsql;
2899 select footest();
2900 ERROR: query returned more than one row
2901 CONTEXT: PL/pgSQL function "footest" line 4 at SQL statement
2902 create or replace function footest() returns void as $$
2903 declare x record;
2904 begin
2905 -- should work
2906 execute 'select * from foo where f1 = 3' into strict x;
2907 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2908 end$$ language plpgsql;
2909 select footest();
2910 NOTICE: x.f1 = 3, x.f2 = 4
2911 footest
2912 ---------
2914 (1 row)
2916 create or replace function footest() returns void as $$
2917 declare x record;
2918 begin
2919 -- should fail, no rows
2920 execute 'select * from foo where f1 = 0' into strict x;
2921 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2922 end$$ language plpgsql;
2923 select footest();
2924 ERROR: query returned no rows
2925 CONTEXT: PL/pgSQL function "footest" line 4 at EXECUTE statement
2926 create or replace function footest() returns void as $$
2927 declare x record;
2928 begin
2929 -- should fail, too many rows
2930 execute 'select * from foo where f1 > 3' into strict x;
2931 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2932 end$$ language plpgsql;
2933 select footest();
2934 ERROR: query returned more than one row
2935 CONTEXT: PL/pgSQL function "footest" line 4 at EXECUTE statement
2936 drop function footest();
2937 -- test scrollable cursor support
2938 create function sc_test() returns setof integer as $$
2939 declare
2940 c scroll cursor for select f1 from int4_tbl;
2941 x integer;
2942 begin
2943 open c;
2944 fetch last from c into x;
2945 while found loop
2946 return next x;
2947 fetch prior from c into x;
2948 end loop;
2949 close c;
2950 end;
2951 $$ language plpgsql;
2952 select * from sc_test();
2953 sc_test
2954 -------------
2955 -2147483647
2956 2147483647
2957 -123456
2958 123456
2959 0
2960 (5 rows)
2962 create or replace function sc_test() returns setof integer as $$
2963 declare
2964 c no scroll cursor for select f1 from int4_tbl;
2965 x integer;
2966 begin
2967 open c;
2968 fetch last from c into x;
2969 while found loop
2970 return next x;
2971 fetch prior from c into x;
2972 end loop;
2973 close c;
2974 end;
2975 $$ language plpgsql;
2976 select * from sc_test(); -- fails because of NO SCROLL specification
2977 ERROR: cursor can only scan forward
2978 HINT: Declare it with SCROLL option to enable backward scan.
2979 CONTEXT: PL/pgSQL function "sc_test" line 6 at FETCH
2980 create or replace function sc_test() returns setof integer as $$
2981 declare
2982 c refcursor;
2983 x integer;
2984 begin
2985 open c scroll for select f1 from int4_tbl;
2986 fetch last from c into x;
2987 while found loop
2988 return next x;
2989 fetch prior from c into x;
2990 end loop;
2991 close c;
2992 end;
2993 $$ language plpgsql;
2994 select * from sc_test();
2995 sc_test
2996 -------------
2997 -2147483647
2998 2147483647
2999 -123456
3000 123456
3001 0
3002 (5 rows)
3004 create or replace function sc_test() returns setof integer as $$
3005 declare
3006 c refcursor;
3007 x integer;
3008 begin
3009 open c scroll for execute 'select f1 from int4_tbl';
3010 fetch last from c into x;
3011 while found loop
3012 return next x;
3013 fetch relative -2 from c into x;
3014 end loop;
3015 close c;
3016 end;
3017 $$ language plpgsql;
3018 select * from sc_test();
3019 sc_test
3020 -------------
3021 -2147483647
3022 -123456
3023 0
3024 (3 rows)
3026 create or replace function sc_test() returns setof integer as $$
3027 declare
3028 c cursor for select * from generate_series(1, 10);
3029 x integer;
3030 begin
3031 open c;
3032 loop
3033 move relative 2 in c;
3034 if not found then
3035 exit;
3036 end if;
3037 fetch next from c into x;
3038 if found then
3039 return next x;
3040 end if;
3041 end loop;
3042 close c;
3043 end;
3044 $$ language plpgsql;
3045 select * from sc_test();
3046 sc_test
3047 ---------
3048 3
3049 6
3050 9
3051 (3 rows)
3053 drop function sc_test();
3054 -- test qualified variable names
3055 create function pl_qual_names (param1 int) returns void as $$
3056 <<outerblock>>
3057 declare
3058 param1 int := 1;
3059 begin
3060 <<innerblock>>
3061 declare
3062 param1 int := 2;
3063 begin
3064 raise notice 'param1 = %', param1;
3065 raise notice 'pl_qual_names.param1 = %', pl_qual_names.param1;
3066 raise notice 'outerblock.param1 = %', outerblock.param1;
3067 raise notice 'innerblock.param1 = %', innerblock.param1;
3068 end;
3069 end;
3070 $$ language plpgsql;
3071 select pl_qual_names(42);
3072 NOTICE: param1 = 2
3073 NOTICE: pl_qual_names.param1 = 42
3074 NOTICE: outerblock.param1 = 1
3075 NOTICE: innerblock.param1 = 2
3076 pl_qual_names
3077 ---------------
3079 (1 row)
3081 drop function pl_qual_names(int);
3082 -- tests for RETURN QUERY
3083 create function ret_query1(out int, out int) returns setof record as $$
3084 begin
3085 $1 := -1;
3086 $2 := -2;
3087 return next;
3088 return query select x + 1, x * 10 from generate_series(0, 10) s (x);
3089 return next;
3090 end;
3091 $$ language plpgsql;
3092 select * from ret_query1();
3093 column1 | column2
3094 ---------+---------
3095 -1 | -2
3096 1 | 0
3097 2 | 10
3098 3 | 20
3099 4 | 30
3100 5 | 40
3101 6 | 50
3102 7 | 60
3103 8 | 70
3104 9 | 80
3105 10 | 90
3106 11 | 100
3107 -1 | -2
3108 (13 rows)
3110 create type record_type as (x text, y int, z boolean);
3111 create or replace function ret_query2(lim int) returns setof record_type as $$
3112 begin
3113 return query select md5(s.x::text), s.x, s.x > 0
3114 from generate_series(-8, lim) s (x) where s.x % 2 = 0;
3115 end;
3116 $$ language plpgsql;
3117 select * from ret_query2(8);
3118 x | y | z
3119 ----------------------------------+----+---
3120 a8d2ec85eaf98407310b72eb73dda247 | -8 | f
3121 596a3d04481816330f07e4f97510c28f | -6 | f
3122 0267aaf632e87a63288a08331f22c7c3 | -4 | f
3123 5d7b9adcbe1c629ec722529dd12e5129 | -2 | f
3124 cfcd208495d565ef66e7dff9f98764da | 0 | f
3125 c81e728d9d4c2f636f067f89cc14862c | 2 | t
3126 a87ff679a2f3e71d9181a67b7542122c | 4 | t
3127 1679091c5a880faf6fb5e6087eb1b2dc | 6 | t
3128 c9f0f895fb98ab9159f51fd0297e236d | 8 | t
3129 (9 rows)
3131 -- test EXECUTE USING
3132 create function exc_using(int, text) returns int as $$
3133 declare i int;
3134 begin
3135 for i in execute 'select * from generate_series(1,$1)' using $1+1 loop
3136 raise notice '%', i;
3137 end loop;
3138 execute 'select $2 + $2*3 + length($1)' into i using $2,$1;
3139 return i;
3140 end
3141 $$ language plpgsql;
3142 select exc_using(5, 'foobar');
3143 NOTICE: 1
3144 NOTICE: 2
3145 NOTICE: 3
3146 NOTICE: 4
3147 NOTICE: 5
3148 NOTICE: 6
3149 exc_using
3150 -----------
3151 26
3152 (1 row)
3154 -- test FOR-over-cursor
3155 create or replace function forc01() returns void as $$
3156 declare
3157 c cursor(r1 integer, r2 integer)
3158 for select * from generate_series(r1,r2) i;
3159 c2 cursor
3160 for select * from generate_series(41,43) i;
3161 begin
3162 for r in c(5,7) loop
3163 raise notice '% from %', r.i, c;
3164 end loop;
3165 -- again, to test if cursor was closed properly
3166 for r in c(9,10) loop
3167 raise notice '% from %', r.i, c;
3168 end loop;
3169 -- and test a parameterless cursor
3170 for r in c2 loop
3171 raise notice '% from %', r.i, c2;
3172 end loop;
3173 -- and try it with a hand-assigned name
3174 raise notice 'after loop, c2 = %', c2;
3175 c2 := 'special_name';
3176 for r in c2 loop
3177 raise notice '% from %', r.i, c2;
3178 end loop;
3179 raise notice 'after loop, c2 = %', c2;
3180 -- and try it with a generated name
3181 -- (which we can't show in the output because it's variable)
3182 c2 := null;
3183 for r in c2 loop
3184 raise notice '%', r.i;
3185 end loop;
3186 raise notice 'after loop, c2 = %', c2;
3187 return;
3188 end;
3189 $$ language plpgsql;
3190 select forc01();
3191 NOTICE: 5 from c
3192 NOTICE: 6 from c
3193 NOTICE: 7 from c
3194 NOTICE: 9 from c
3195 NOTICE: 10 from c
3196 NOTICE: 41 from c2
3197 NOTICE: 42 from c2
3198 NOTICE: 43 from c2
3199 NOTICE: after loop, c2 = c2
3200 NOTICE: 41 from special_name
3201 NOTICE: 42 from special_name
3202 NOTICE: 43 from special_name
3203 NOTICE: after loop, c2 = special_name
3204 NOTICE: 41
3205 NOTICE: 42
3206 NOTICE: 43
3207 NOTICE: after loop, c2 = <NULL>
3208 forc01
3209 --------
3211 (1 row)
3213 -- try updating the cursor's current row
3214 create temp table forc_test as
3215 select n as i, n as j from generate_series(1,10) n;
3216 create or replace function forc01() returns void as $$
3217 declare
3218 c cursor for select * from forc_test;
3219 begin
3220 for r in c loop
3221 raise notice '%, %', r.i, r.j;
3222 update forc_test set i = i * 100, j = r.j * 2 where current of c;
3223 end loop;
3224 end;
3225 $$ language plpgsql;
3226 select forc01();
3227 NOTICE: 1, 1
3228 NOTICE: 2, 2
3229 NOTICE: 3, 3
3230 NOTICE: 4, 4
3231 NOTICE: 5, 5
3232 NOTICE: 6, 6
3233 NOTICE: 7, 7
3234 NOTICE: 8, 8
3235 NOTICE: 9, 9
3236 NOTICE: 10, 10
3237 forc01
3238 --------
3240 (1 row)
3242 select * from forc_test;
3243 i | j
3244 ------+----
3245 100 | 2
3246 200 | 4
3247 300 | 6
3248 400 | 8
3249 500 | 10
3250 600 | 12
3251 700 | 14
3252 800 | 16
3253 900 | 18
3254 1000 | 20
3255 (10 rows)
3257 drop function forc01();
3258 -- fail because cursor has no query bound to it
3259 create or replace function forc_bad() returns void as $$
3260 declare
3261 c refcursor;
3262 begin
3263 for r in c loop
3264 raise notice '%', r.i;
3265 end loop;
3266 end;
3267 $$ language plpgsql;
3268 ERROR: cursor FOR loop must use a bound cursor variable
3269 CONTEXT: compile of PL/pgSQL function "forc_bad" near line 4
3270 -- test RETURN QUERY EXECUTE
3271 create or replace function return_dquery()
3272 returns setof int as $$
3273 begin
3274 return query execute 'select * from (values(10),(20)) f';
3275 return query execute 'select * from (values($1),($2)) f' using 40,50;
3276 end;
3277 $$ language plpgsql;
3278 select * from return_dquery();
3279 return_dquery
3280 ---------------
3281 10
3282 20
3283 40
3284 50
3285 (4 rows)
3287 drop function return_dquery();
3288 -- Tests for 8.4's new RAISE features
3289 create or replace function raise_test() returns void as $$
3290 begin
3291 raise notice '% % %', 1, 2, 3
3292 using errcode = '55001', detail = 'some detail info', hint = 'some hint';
3293 raise '% % %', 1, 2, 3
3294 using errcode = 'division_by_zero', detail = 'some detail info';
3295 end;
3296 $$ language plpgsql;
3297 select raise_test();
3298 NOTICE: 1 2 3
3299 DETAIL: some detail info
3300 HINT: some hint
3301 ERROR: 1 2 3
3302 DETAIL: some detail info
3303 -- Since we can't actually see the thrown SQLSTATE in default psql output,
3304 -- test it like this; this also tests re-RAISE
3305 create or replace function raise_test() returns void as $$
3306 begin
3307 raise 'check me'
3308 using errcode = 'division_by_zero', detail = 'some detail info';
3309 exception
3310 when others then
3311 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3312 raise;
3313 end;
3314 $$ language plpgsql;
3315 select raise_test();
3316 NOTICE: SQLSTATE: 22012 SQLERRM: check me
3317 ERROR: check me
3318 DETAIL: some detail info
3319 create or replace function raise_test() returns void as $$
3320 begin
3321 raise 'check me'
3322 using errcode = '1234F', detail = 'some detail info';
3323 exception
3324 when others then
3325 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3326 raise;
3327 end;
3328 $$ language plpgsql;
3329 select raise_test();
3330 NOTICE: SQLSTATE: 1234F SQLERRM: check me
3331 ERROR: check me
3332 DETAIL: some detail info
3333 -- SQLSTATE specification in WHEN
3334 create or replace function raise_test() returns void as $$
3335 begin
3336 raise 'check me'
3337 using errcode = '1234F', detail = 'some detail info';
3338 exception
3339 when sqlstate '1234F' then
3340 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3341 raise;
3342 end;
3343 $$ language plpgsql;
3344 select raise_test();
3345 NOTICE: SQLSTATE: 1234F SQLERRM: check me
3346 ERROR: check me
3347 DETAIL: some detail info
3348 create or replace function raise_test() returns void as $$
3349 begin
3350 raise division_by_zero using detail = 'some detail info';
3351 exception
3352 when others then
3353 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3354 raise;
3355 end;
3356 $$ language plpgsql;
3357 select raise_test();
3358 NOTICE: SQLSTATE: 22012 SQLERRM: division_by_zero
3359 ERROR: division_by_zero
3360 DETAIL: some detail info
3361 create or replace function raise_test() returns void as $$
3362 begin
3363 raise division_by_zero;
3364 end;
3365 $$ language plpgsql;
3366 select raise_test();
3367 ERROR: division_by_zero
3368 create or replace function raise_test() returns void as $$
3369 begin
3370 raise sqlstate '1234F';
3371 end;
3372 $$ language plpgsql;
3373 select raise_test();
3374 ERROR: 1234F
3375 create or replace function raise_test() returns void as $$
3376 begin
3377 raise division_by_zero using message = 'custom' || ' message';
3378 end;
3379 $$ language plpgsql;
3380 select raise_test();
3381 ERROR: custom message
3382 create or replace function raise_test() returns void as $$
3383 begin
3384 raise using message = 'custom' || ' message', errcode = '22012';
3385 end;
3386 $$ language plpgsql;
3387 select raise_test();
3388 ERROR: custom message
3389 -- conflict on message
3390 create or replace function raise_test() returns void as $$
3391 begin
3392 raise notice 'some message' using message = 'custom' || ' message', errcode = '22012';
3393 end;
3394 $$ language plpgsql;
3395 select raise_test();
3396 ERROR: RAISE option already specified: MESSAGE
3397 CONTEXT: PL/pgSQL function "raise_test" line 2 at RAISE
3398 -- conflict on errcode
3399 create or replace function raise_test() returns void as $$
3400 begin
3401 raise division_by_zero using message = 'custom' || ' message', errcode = '22012';
3402 end;
3403 $$ language plpgsql;
3404 select raise_test();
3405 ERROR: RAISE option already specified: ERRCODE
3406 CONTEXT: PL/pgSQL function "raise_test" line 2 at RAISE
3407 -- nothing to re-RAISE
3408 create or replace function raise_test() returns void as $$
3409 begin
3410 raise;
3411 end;
3412 $$ language plpgsql;
3413 select raise_test();
3414 ERROR: RAISE without parameters cannot be used outside an exception handler
3415 CONTEXT: PL/pgSQL function "raise_test"
3416 drop function raise_test();
3417 -- test CASE statement
3418 create or replace function case_test(bigint) returns text as $$
3419 declare a int = 10;
3420 b int = 1;
3421 begin
3422 case $1
3423 when 1 then
3424 return 'one';
3425 when 2 then
3426 return 'two';
3427 when 3,4,3+5 then
3428 return 'three, four or eight';
3429 when a then
3430 return 'ten';
3431 when a+b, a+b+1 then
3432 return 'eleven, twelve';
3433 end case;
3434 end;
3435 $$ language plpgsql immutable;
3436 select case_test(1);
3437 case_test
3438 -----------
3439 one
3440 (1 row)
3442 select case_test(2);
3443 case_test
3444 -----------
3445 two
3446 (1 row)
3448 select case_test(3);
3449 case_test
3450 ----------------------
3451 three, four or eight
3452 (1 row)
3454 select case_test(4);
3455 case_test
3456 ----------------------
3457 three, four or eight
3458 (1 row)
3460 select case_test(5); -- fails
3461 ERROR: case not found
3462 HINT: CASE statement is missing ELSE part.
3463 CONTEXT: PL/pgSQL function "case_test" line 4 at CASE
3464 select case_test(8);
3465 case_test
3466 ----------------------
3467 three, four or eight
3468 (1 row)
3470 select case_test(10);
3471 case_test
3472 -----------
3473 ten
3474 (1 row)
3476 select case_test(11);
3477 case_test
3478 ----------------
3479 eleven, twelve
3480 (1 row)
3482 select case_test(12);
3483 case_test
3484 ----------------
3485 eleven, twelve
3486 (1 row)
3488 select case_test(13); -- fails
3489 ERROR: case not found
3490 HINT: CASE statement is missing ELSE part.
3491 CONTEXT: PL/pgSQL function "case_test" line 4 at CASE
3492 create or replace function catch() returns void as $$
3493 begin
3494 raise notice '%', case_test(6);
3495 exception
3496 when case_not_found then
3497 raise notice 'caught case_not_found % %', SQLSTATE, SQLERRM;
3498 end
3499 $$ language plpgsql;
3500 select catch();
3501 NOTICE: caught case_not_found 20000 case not found
3502 catch
3503 -------
3505 (1 row)
3507 -- test the searched variant too, as well as ELSE
3508 create or replace function case_test(bigint) returns text as $$
3509 declare a int = 10;
3510 begin
3511 case
3512 when $1 = 1 then
3513 return 'one';
3514 when $1 = a + 2 then
3515 return 'twelve';
3516 else
3517 return 'other';
3518 end case;
3519 end;
3520 $$ language plpgsql immutable;
3521 select case_test(1);
3522 case_test
3523 -----------
3524 one
3525 (1 row)
3527 select case_test(2);
3528 case_test
3529 -----------
3530 other
3531 (1 row)
3533 select case_test(12);
3534 case_test
3535 -----------
3536 twelve
3537 (1 row)
3539 select case_test(13);
3540 case_test
3541 -----------
3542 other
3543 (1 row)
3545 drop function catch();
3546 drop function case_test(bigint);
3547 -- test variadic functions
3548 create or replace function vari(variadic int[])
3549 returns void as $$
3550 begin
3551 for i in array_lower($1,1)..array_upper($1,1) loop
3552 raise notice '%', $1[i];
3553 end loop; end;
3554 $$ language plpgsql;
3555 select vari(1,2,3,4,5);
3556 NOTICE: 1
3557 NOTICE: 2
3558 NOTICE: 3
3559 NOTICE: 4
3560 NOTICE: 5
3561 vari
3562 ------
3564 (1 row)
3566 select vari(3,4,5);
3567 NOTICE: 3
3568 NOTICE: 4
3569 NOTICE: 5
3570 vari
3571 ------
3573 (1 row)
3575 select vari(variadic array[5,6,7]);
3576 NOTICE: 5
3577 NOTICE: 6
3578 NOTICE: 7
3579 vari
3580 ------
3582 (1 row)
3584 drop function vari(int[]);
3585 -- coercion test
3586 create or replace function pleast(variadic numeric[])
3587 returns numeric as $$
3588 declare aux numeric = $1[array_lower($1,1)];
3589 begin
3590 for i in array_lower($1,1)+1..array_upper($1,1) loop
3591 if $1[i] < aux then aux := $1[i]; end if;
3592 end loop;
3593 return aux;
3594 end;
3595 $$ language plpgsql immutable strict;
3596 select pleast(10,1,2,3,-16);
3597 pleast
3598 --------
3599 -16
3600 (1 row)
3602 select pleast(10.2,2.2,-1.1);
3603 pleast
3604 --------
3605 -1.1
3606 (1 row)
3608 select pleast(10.2,10, -20);
3609 pleast
3610 --------
3611 -20
3612 (1 row)
3614 select pleast(10,20, -1.0);
3615 pleast
3616 --------
3617 -1.0
3618 (1 row)
3620 -- in case of conflict, non-variadic version is preferred
3621 create or replace function pleast(numeric)
3622 returns numeric as $$
3623 begin
3624 raise notice 'non-variadic function called';
3625 return $1;
3626 end;
3627 $$ language plpgsql immutable strict;
3628 select pleast(10);
3629 NOTICE: non-variadic function called
3630 pleast
3631 --------
3632 10
3633 (1 row)
3635 drop function pleast(numeric[]);
3636 drop function pleast(numeric);
3637 -- test table functions
3638 create function tftest(int) returns table(a int, b int) as $$
3639 begin
3640 return query select $1, $1+i from generate_series(1,5) g(i);
3641 end;
3642 $$ language plpgsql immutable strict;
3643 select * from tftest(10);
3644 a | b
3645 ----+----
3646 10 | 11
3647 10 | 12
3648 10 | 13
3649 10 | 14
3650 10 | 15
3651 (5 rows)
3653 create or replace function tftest(a1 int) returns table(a int, b int) as $$
3654 begin
3655 a := a1; b := a1 + 1;
3656 return next;
3657 a := a1 * 10; b := a1 * 10 + 1;
3658 return next;
3659 end;
3660 $$ language plpgsql immutable strict;
3661 select * from tftest(10);
3662 a | b
3663 -----+-----
3664 10 | 11
3665 100 | 101
3666 (2 rows)
3668 drop function tftest(int);