| blob | 6ea169d9add54fc8c97c480065bdce94ee37d2f0 |
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 ps alias for new;
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'' and new.hubname != old.hubname then
289 if count(*) > 0 from Hub where name = old.hubname then
290 raise exception ''no manual manipulation of HSlot'';
291 end if;
292 end if;
293 sname := ''HS.'' || trim(new.hubname);
294 sname := sname || ''.'';
295 sname := sname || new.slotno::text;
296 if length(sname) > 20 then
297 raise exception ''HSlot slotname "%" too long (20 char max)'', sname;
298 end if;
299 new.slotname := sname;
300 return new;
301 end;
302 ' language plpgsql;
303 create trigger tg_hslot_biu before insert or update
304 on HSlot for each row execute procedure tg_hslot_biu();
305 -- ************************************************************
306 -- * BEFORE DELETE on HSlot
307 -- * - prevent from manual manipulation
308 -- ************************************************************
309 create function tg_hslot_bd() returns trigger as '
310 declare
311 hubrec record;
312 begin
313 select into hubrec * from Hub where name = old.hubname;
314 if not found then
315 return old;
316 end if;
317 if old.slotno > hubrec.nslots then
318 return old;
319 end if;
320 raise exception ''no manual manipulation of HSlot'';
321 end;
322 ' language plpgsql;
323 create trigger tg_hslot_bd before delete
324 on HSlot for each row execute procedure tg_hslot_bd();
325 -- ************************************************************
326 -- * BEFORE INSERT on all slots
327 -- * - Check name prefix
328 -- ************************************************************
329 create function tg_chkslotname() returns trigger as '
330 begin
331 if substr(new.slotname, 1, 2) != tg_argv[0] then
332 raise exception ''slotname must begin with %'', tg_argv[0];
333 end if;
334 return new;
335 end;
336 ' language plpgsql;
337 create trigger tg_chkslotname before insert
338 on PSlot for each row execute procedure tg_chkslotname('PS');
339 create trigger tg_chkslotname before insert
340 on WSlot for each row execute procedure tg_chkslotname('WS');
341 create trigger tg_chkslotname before insert
342 on PLine for each row execute procedure tg_chkslotname('PL');
343 create trigger tg_chkslotname before insert
344 on IFace for each row execute procedure tg_chkslotname('IF');
345 create trigger tg_chkslotname before insert
346 on PHone for each row execute procedure tg_chkslotname('PH');
347 -- ************************************************************
348 -- * BEFORE INSERT or UPDATE on all slots with slotlink
349 -- * - Set slotlink to empty string if NULL value given
350 -- ************************************************************
351 create function tg_chkslotlink() returns trigger as '
352 begin
353 if new.slotlink isnull then
354 new.slotlink := '''';
355 end if;
356 return new;
357 end;
358 ' language plpgsql;
359 create trigger tg_chkslotlink before insert or update
360 on PSlot for each row execute procedure tg_chkslotlink();
361 create trigger tg_chkslotlink before insert or update
362 on WSlot for each row execute procedure tg_chkslotlink();
363 create trigger tg_chkslotlink before insert or update
364 on IFace for each row execute procedure tg_chkslotlink();
365 create trigger tg_chkslotlink before insert or update
366 on HSlot for each row execute procedure tg_chkslotlink();
367 create trigger tg_chkslotlink before insert or update
368 on PHone for each row execute procedure tg_chkslotlink();
369 -- ************************************************************
370 -- * BEFORE INSERT or UPDATE on all slots with backlink
371 -- * - Set backlink to empty string if NULL value given
372 -- ************************************************************
373 create function tg_chkbacklink() returns trigger as '
374 begin
375 if new.backlink isnull then
376 new.backlink := '''';
377 end if;
378 return new;
379 end;
380 ' language plpgsql;
381 create trigger tg_chkbacklink before insert or update
382 on PSlot for each row execute procedure tg_chkbacklink();
383 create trigger tg_chkbacklink before insert or update
384 on WSlot for each row execute procedure tg_chkbacklink();
385 create trigger tg_chkbacklink before insert or update
386 on PLine for each row execute procedure tg_chkbacklink();
387 -- ************************************************************
388 -- * BEFORE UPDATE on PSlot
389 -- * - do delete/insert instead of update if name changes
390 -- ************************************************************
391 create function tg_pslot_bu() returns trigger as '
392 begin
393 if new.slotname != old.slotname then
394 delete from PSlot where slotname = old.slotname;
395 insert into PSlot (
396 slotname,
397 pfname,
398 slotlink,
399 backlink
400 ) values (
401 new.slotname,
402 new.pfname,
403 new.slotlink,
404 new.backlink
405 );
406 return null;
407 end if;
408 return new;
409 end;
410 ' language plpgsql;
411 create trigger tg_pslot_bu before update
412 on PSlot for each row execute procedure tg_pslot_bu();
413 -- ************************************************************
414 -- * BEFORE UPDATE on WSlot
415 -- * - do delete/insert instead of update if name changes
416 -- ************************************************************
417 create function tg_wslot_bu() returns trigger as '
418 begin
419 if new.slotname != old.slotname then
420 delete from WSlot where slotname = old.slotname;
421 insert into WSlot (
422 slotname,
423 roomno,
424 slotlink,
425 backlink
426 ) values (
427 new.slotname,
428 new.roomno,
429 new.slotlink,
430 new.backlink
431 );
432 return null;
433 end if;
434 return new;
435 end;
436 ' language plpgsql;
437 create trigger tg_wslot_bu before update
438 on WSlot for each row execute procedure tg_Wslot_bu();
439 -- ************************************************************
440 -- * BEFORE UPDATE on PLine
441 -- * - do delete/insert instead of update if name changes
442 -- ************************************************************
443 create function tg_pline_bu() returns trigger as '
444 begin
445 if new.slotname != old.slotname then
446 delete from PLine where slotname = old.slotname;
447 insert into PLine (
448 slotname,
449 phonenumber,
450 comment,
451 backlink
452 ) values (
453 new.slotname,
454 new.phonenumber,
455 new.comment,
456 new.backlink
457 );
458 return null;
459 end if;
460 return new;
461 end;
462 ' language plpgsql;
463 create trigger tg_pline_bu before update
464 on PLine for each row execute procedure tg_pline_bu();
465 -- ************************************************************
466 -- * BEFORE UPDATE on IFace
467 -- * - do delete/insert instead of update if name changes
468 -- ************************************************************
469 create function tg_iface_bu() returns trigger as '
470 begin
471 if new.slotname != old.slotname then
472 delete from IFace where slotname = old.slotname;
473 insert into IFace (
474 slotname,
475 sysname,
476 ifname,
477 slotlink
478 ) values (
479 new.slotname,
480 new.sysname,
481 new.ifname,
482 new.slotlink
483 );
484 return null;
485 end if;
486 return new;
487 end;
488 ' language plpgsql;
489 create trigger tg_iface_bu before update
490 on IFace for each row execute procedure tg_iface_bu();
491 -- ************************************************************
492 -- * BEFORE UPDATE on HSlot
493 -- * - do delete/insert instead of update if name changes
494 -- ************************************************************
495 create function tg_hslot_bu() returns trigger as '
496 begin
497 if new.slotname != old.slotname or new.hubname != old.hubname then
498 delete from HSlot where slotname = old.slotname;
499 insert into HSlot (
500 slotname,
501 hubname,
502 slotno,
503 slotlink
504 ) values (
505 new.slotname,
506 new.hubname,
507 new.slotno,
508 new.slotlink
509 );
510 return null;
511 end if;
512 return new;
513 end;
514 ' language plpgsql;
515 create trigger tg_hslot_bu before update
516 on HSlot for each row execute procedure tg_hslot_bu();
517 -- ************************************************************
518 -- * BEFORE UPDATE on PHone
519 -- * - do delete/insert instead of update if name changes
520 -- ************************************************************
521 create function tg_phone_bu() returns trigger as '
522 begin
523 if new.slotname != old.slotname then
524 delete from PHone where slotname = old.slotname;
525 insert into PHone (
526 slotname,
527 comment,
528 slotlink
529 ) values (
530 new.slotname,
531 new.comment,
532 new.slotlink
533 );
534 return null;
535 end if;
536 return new;
537 end;
538 ' language plpgsql;
539 create trigger tg_phone_bu before update
540 on PHone for each row execute procedure tg_phone_bu();
541 -- ************************************************************
542 -- * AFTER INSERT or UPDATE or DELETE on slot with backlink
543 -- * - Ensure that the opponent correctly points back to us
544 -- ************************************************************
545 create function tg_backlink_a() returns trigger as '
546 declare
547 dummy integer;
548 begin
549 if tg_op = ''INSERT'' then
550 if new.backlink != '''' then
551 dummy := tg_backlink_set(new.backlink, new.slotname);
552 end if;
553 return new;
554 end if;
555 if tg_op = ''UPDATE'' then
556 if new.backlink != old.backlink then
557 if old.backlink != '''' then
558 dummy := tg_backlink_unset(old.backlink, old.slotname);
559 end if;
560 if new.backlink != '''' then
561 dummy := tg_backlink_set(new.backlink, new.slotname);
562 end if;
563 else
564 if new.slotname != old.slotname and new.backlink != '''' then
565 dummy := tg_slotlink_set(new.backlink, new.slotname);
566 end if;
567 end if;
568 return new;
569 end if;
570 if tg_op = ''DELETE'' then
571 if old.backlink != '''' then
572 dummy := tg_backlink_unset(old.backlink, old.slotname);
573 end if;
574 return old;
575 end if;
576 end;
577 ' language plpgsql;
578 create trigger tg_backlink_a after insert or update or delete
579 on PSlot for each row execute procedure tg_backlink_a('PS');
580 create trigger tg_backlink_a after insert or update or delete
581 on WSlot for each row execute procedure tg_backlink_a('WS');
582 create trigger tg_backlink_a after insert or update or delete
583 on PLine for each row execute procedure tg_backlink_a('PL');
584 -- ************************************************************
585 -- * Support function to set the opponents backlink field
586 -- * if it does not already point to the requested slot
587 -- ************************************************************
588 create function tg_backlink_set(myname bpchar, blname bpchar)
589 returns integer as '
590 declare
591 mytype char(2);
592 link char(4);
593 rec record;
594 begin
595 mytype := substr(myname, 1, 2);
596 link := mytype || substr(blname, 1, 2);
597 if link = ''PLPL'' then
598 raise exception
599 ''backlink between two phone lines does not make sense'';
600 end if;
601 if link in (''PLWS'', ''WSPL'') then
602 raise exception
603 ''direct link of phone line to wall slot not permitted'';
604 end if;
605 if mytype = ''PS'' then
606 select into rec * from PSlot where slotname = myname;
607 if not found then
608 raise exception ''% does not exist'', myname;
609 end if;
610 if rec.backlink != blname then
611 update PSlot set backlink = blname where slotname = myname;
612 end if;
613 return 0;
614 end if;
615 if mytype = ''WS'' then
616 select into rec * from WSlot where slotname = myname;
617 if not found then
618 raise exception ''% does not exist'', myname;
619 end if;
620 if rec.backlink != blname then
621 update WSlot set backlink = blname where slotname = myname;
622 end if;
623 return 0;
624 end if;
625 if mytype = ''PL'' then
626 select into rec * from PLine where slotname = myname;
627 if not found then
628 raise exception ''% does not exist'', myname;
629 end if;
630 if rec.backlink != blname then
631 update PLine set backlink = blname where slotname = myname;
632 end if;
633 return 0;
634 end if;
635 raise exception ''illegal backlink beginning with %'', mytype;
636 end;
637 ' language plpgsql;
638 -- ************************************************************
639 -- * Support function to clear out the backlink field if
640 -- * it still points to specific slot
641 -- ************************************************************
642 create function tg_backlink_unset(bpchar, bpchar)
643 returns integer as '
644 declare
645 myname alias for $1;
646 blname alias for $2;
647 mytype char(2);
648 rec record;
649 begin
650 mytype := substr(myname, 1, 2);
651 if mytype = ''PS'' then
652 select into rec * from PSlot where slotname = myname;
653 if not found then
654 return 0;
655 end if;
656 if rec.backlink = blname then
657 update PSlot set backlink = '''' where slotname = myname;
658 end if;
659 return 0;
660 end if;
661 if mytype = ''WS'' then
662 select into rec * from WSlot where slotname = myname;
663 if not found then
664 return 0;
665 end if;
666 if rec.backlink = blname then
667 update WSlot set backlink = '''' where slotname = myname;
668 end if;
669 return 0;
670 end if;
671 if mytype = ''PL'' then
672 select into rec * from PLine where slotname = myname;
673 if not found then
674 return 0;
675 end if;
676 if rec.backlink = blname then
677 update PLine set backlink = '''' where slotname = myname;
678 end if;
679 return 0;
680 end if;
681 end
682 ' language plpgsql;
683 -- ************************************************************
684 -- * AFTER INSERT or UPDATE or DELETE on slot with slotlink
685 -- * - Ensure that the opponent correctly points back to us
686 -- ************************************************************
687 create function tg_slotlink_a() returns trigger as '
688 declare
689 dummy integer;
690 begin
691 if tg_op = ''INSERT'' then
692 if new.slotlink != '''' then
693 dummy := tg_slotlink_set(new.slotlink, new.slotname);
694 end if;
695 return new;
696 end if;
697 if tg_op = ''UPDATE'' then
698 if new.slotlink != old.slotlink then
699 if old.slotlink != '''' then
700 dummy := tg_slotlink_unset(old.slotlink, old.slotname);
701 end if;
702 if new.slotlink != '''' then
703 dummy := tg_slotlink_set(new.slotlink, new.slotname);
704 end if;
705 else
706 if new.slotname != old.slotname and new.slotlink != '''' then
707 dummy := tg_slotlink_set(new.slotlink, new.slotname);
708 end if;
709 end if;
710 return new;
711 end if;
712 if tg_op = ''DELETE'' then
713 if old.slotlink != '''' then
714 dummy := tg_slotlink_unset(old.slotlink, old.slotname);
715 end if;
716 return old;
717 end if;
718 end;
719 ' language plpgsql;
720 create trigger tg_slotlink_a after insert or update or delete
721 on PSlot for each row execute procedure tg_slotlink_a('PS');
722 create trigger tg_slotlink_a after insert or update or delete
723 on WSlot for each row execute procedure tg_slotlink_a('WS');
724 create trigger tg_slotlink_a after insert or update or delete
725 on IFace for each row execute procedure tg_slotlink_a('IF');
726 create trigger tg_slotlink_a after insert or update or delete
727 on HSlot for each row execute procedure tg_slotlink_a('HS');
728 create trigger tg_slotlink_a after insert or update or delete
729 on PHone for each row execute procedure tg_slotlink_a('PH');
730 -- ************************************************************
731 -- * Support function to set the opponents slotlink field
732 -- * if it does not already point to the requested slot
733 -- ************************************************************
734 create function tg_slotlink_set(bpchar, bpchar)
735 returns integer as '
736 declare
737 myname alias for $1;
738 blname alias for $2;
739 mytype char(2);
740 link char(4);
741 rec record;
742 begin
743 mytype := substr(myname, 1, 2);
744 link := mytype || substr(blname, 1, 2);
745 if link = ''PHPH'' then
746 raise exception
747 ''slotlink between two phones does not make sense'';
748 end if;
749 if link in (''PHHS'', ''HSPH'') then
750 raise exception
751 ''link of phone to hub does not make sense'';
752 end if;
753 if link in (''PHIF'', ''IFPH'') then
754 raise exception
755 ''link of phone to hub does not make sense'';
756 end if;
757 if link in (''PSWS'', ''WSPS'') then
758 raise exception
759 ''slotlink from patchslot to wallslot not permitted'';
760 end if;
761 if mytype = ''PS'' then
762 select into rec * from PSlot where slotname = myname;
763 if not found then
764 raise exception ''% does not exist'', myname;
765 end if;
766 if rec.slotlink != blname then
767 update PSlot set slotlink = blname where slotname = myname;
768 end if;
769 return 0;
770 end if;
771 if mytype = ''WS'' then
772 select into rec * from WSlot where slotname = myname;
773 if not found then
774 raise exception ''% does not exist'', myname;
775 end if;
776 if rec.slotlink != blname then
777 update WSlot set slotlink = blname where slotname = myname;
778 end if;
779 return 0;
780 end if;
781 if mytype = ''IF'' then
782 select into rec * from IFace where slotname = myname;
783 if not found then
784 raise exception ''% does not exist'', myname;
785 end if;
786 if rec.slotlink != blname then
787 update IFace set slotlink = blname where slotname = myname;
788 end if;
789 return 0;
790 end if;
791 if mytype = ''HS'' then
792 select into rec * from HSlot where slotname = myname;
793 if not found then
794 raise exception ''% does not exist'', myname;
795 end if;
796 if rec.slotlink != blname then
797 update HSlot set slotlink = blname where slotname = myname;
798 end if;
799 return 0;
800 end if;
801 if mytype = ''PH'' then
802 select into rec * from PHone where slotname = myname;
803 if not found then
804 raise exception ''% does not exist'', myname;
805 end if;
806 if rec.slotlink != blname then
807 update PHone set slotlink = blname where slotname = myname;
808 end if;
809 return 0;
810 end if;
811 raise exception ''illegal slotlink beginning with %'', mytype;
812 end;
813 ' language plpgsql;
814 -- ************************************************************
815 -- * Support function to clear out the slotlink field if
816 -- * it still points to specific slot
817 -- ************************************************************
818 create function tg_slotlink_unset(bpchar, bpchar)
819 returns integer as '
820 declare
821 myname alias for $1;
822 blname alias for $2;
823 mytype char(2);
824 rec record;
825 begin
826 mytype := substr(myname, 1, 2);
827 if mytype = ''PS'' then
828 select into rec * from PSlot where slotname = myname;
829 if not found then
830 return 0;
831 end if;
832 if rec.slotlink = blname then
833 update PSlot set slotlink = '''' where slotname = myname;
834 end if;
835 return 0;
836 end if;
837 if mytype = ''WS'' then
838 select into rec * from WSlot where slotname = myname;
839 if not found then
840 return 0;
841 end if;
842 if rec.slotlink = blname then
843 update WSlot set slotlink = '''' where slotname = myname;
844 end if;
845 return 0;
846 end if;
847 if mytype = ''IF'' then
848 select into rec * from IFace where slotname = myname;
849 if not found then
850 return 0;
851 end if;
852 if rec.slotlink = blname then
853 update IFace set slotlink = '''' where slotname = myname;
854 end if;
855 return 0;
856 end if;
857 if mytype = ''HS'' then
858 select into rec * from HSlot where slotname = myname;
859 if not found then
860 return 0;
861 end if;
862 if rec.slotlink = blname then
863 update HSlot set slotlink = '''' where slotname = myname;
864 end if;
865 return 0;
866 end if;
867 if mytype = ''PH'' then
868 select into rec * from PHone where slotname = myname;
869 if not found then
870 return 0;
871 end if;
872 if rec.slotlink = blname then
873 update PHone set slotlink = '''' where slotname = myname;
874 end if;
875 return 0;
876 end if;
877 end;
878 ' language plpgsql;
879 -- ************************************************************
880 -- * Describe the backside of a patchfield slot
881 -- ************************************************************
882 create function pslot_backlink_view(bpchar)
883 returns text as '
884 <<outer>>
885 declare
886 rec record;
887 bltype char(2);
888 retval text;
889 begin
890 select into rec * from PSlot where slotname = $1;
891 if not found then
892 return '''';
893 end if;
894 if rec.backlink = '''' then
895 return ''-'';
896 end if;
897 bltype := substr(rec.backlink, 1, 2);
898 if bltype = ''PL'' then
899 declare
900 rec record;
901 begin
902 select into rec * from PLine where slotname = "outer".rec.backlink;
903 retval := ''Phone line '' || trim(rec.phonenumber);
904 if rec.comment != '''' then
905 retval := retval || '' ('';
906 retval := retval || rec.comment;
907 retval := retval || '')'';
908 end if;
909 return retval;
910 end;
911 end if;
912 if bltype = ''WS'' then
913 select into rec * from WSlot where slotname = rec.backlink;
914 retval := trim(rec.slotname) || '' in room '';
915 retval := retval || trim(rec.roomno);
916 retval := retval || '' -> '';
917 return retval || wslot_slotlink_view(rec.slotname);
918 end if;
919 return rec.backlink;
920 end;
921 ' language plpgsql;
922 -- ************************************************************
923 -- * Describe the front of a patchfield slot
924 -- ************************************************************
925 create function pslot_slotlink_view(bpchar)
926 returns text as '
927 declare
928 psrec record;
929 sltype char(2);
930 retval text;
931 begin
932 select into psrec * from PSlot where slotname = $1;
933 if not found then
934 return '''';
935 end if;
936 if psrec.slotlink = '''' then
937 return ''-'';
938 end if;
939 sltype := substr(psrec.slotlink, 1, 2);
940 if sltype = ''PS'' then
941 retval := trim(psrec.slotlink) || '' -> '';
942 return retval || pslot_backlink_view(psrec.slotlink);
943 end if;
944 if sltype = ''HS'' then
945 retval := comment from Hub H, HSlot HS
946 where HS.slotname = psrec.slotlink
947 and H.name = HS.hubname;
948 retval := retval || '' slot '';
949 retval := retval || slotno::text from HSlot
950 where slotname = psrec.slotlink;
951 return retval;
952 end if;
953 return psrec.slotlink;
954 end;
955 ' language plpgsql;
956 -- ************************************************************
957 -- * Describe the front of a wall connector slot
958 -- ************************************************************
959 create function wslot_slotlink_view(bpchar)
960 returns text as '
961 declare
962 rec record;
963 sltype char(2);
964 retval text;
965 begin
966 select into rec * from WSlot where slotname = $1;
967 if not found then
968 return '''';
969 end if;
970 if rec.slotlink = '''' then
971 return ''-'';
972 end if;
973 sltype := substr(rec.slotlink, 1, 2);
974 if sltype = ''PH'' then
975 select into rec * from PHone where slotname = rec.slotlink;
976 retval := ''Phone '' || trim(rec.slotname);
977 if rec.comment != '''' then
978 retval := retval || '' ('';
979 retval := retval || rec.comment;
980 retval := retval || '')'';
981 end if;
982 return retval;
983 end if;
984 if sltype = ''IF'' then
985 declare
986 syrow System%RowType;
987 ifrow IFace%ROWTYPE;
988 begin
989 select into ifrow * from IFace where slotname = rec.slotlink;
990 select into syrow * from System where name = ifrow.sysname;
991 retval := syrow.name || '' IF '';
992 retval := retval || ifrow.ifname;
993 if syrow.comment != '''' then
994 retval := retval || '' ('';
995 retval := retval || syrow.comment;
996 retval := retval || '')'';
997 end if;
998 return retval;
999 end;
1000 end if;
1001 return rec.slotlink;
1002 end;
1003 ' language plpgsql;
1004 -- ************************************************************
1005 -- * View of a patchfield describing backside and patches
1006 -- ************************************************************
1007 create view Pfield_v1 as select PF.pfname, PF.slotname,
1008 pslot_backlink_view(PF.slotname) as backside,
1009 pslot_slotlink_view(PF.slotname) as patch
1010 from PSlot PF;
1011 --
1012 -- First we build the house - so we create the rooms
1013 --
1014 insert into Room values ('001', 'Entrance');
1015 insert into Room values ('002', 'Office');
1016 insert into Room values ('003', 'Office');
1017 insert into Room values ('004', 'Technical');
1018 insert into Room values ('101', 'Office');
1019 insert into Room values ('102', 'Conference');
1020 insert into Room values ('103', 'Restroom');
1021 insert into Room values ('104', 'Technical');
1022 insert into Room values ('105', 'Office');
1023 insert into Room values ('106', 'Office');
1024 --
1025 -- Second we install the wall connectors
1026 --
1027 insert into WSlot values ('WS.001.1a', '001', '', '');
1028 insert into WSlot values ('WS.001.1b', '001', '', '');
1029 insert into WSlot values ('WS.001.2a', '001', '', '');
1030 insert into WSlot values ('WS.001.2b', '001', '', '');
1031 insert into WSlot values ('WS.001.3a', '001', '', '');
1032 insert into WSlot values ('WS.001.3b', '001', '', '');
1033 insert into WSlot values ('WS.002.1a', '002', '', '');
1034 insert into WSlot values ('WS.002.1b', '002', '', '');
1035 insert into WSlot values ('WS.002.2a', '002', '', '');
1036 insert into WSlot values ('WS.002.2b', '002', '', '');
1037 insert into WSlot values ('WS.002.3a', '002', '', '');
1038 insert into WSlot values ('WS.002.3b', '002', '', '');
1039 insert into WSlot values ('WS.003.1a', '003', '', '');
1040 insert into WSlot values ('WS.003.1b', '003', '', '');
1041 insert into WSlot values ('WS.003.2a', '003', '', '');
1042 insert into WSlot values ('WS.003.2b', '003', '', '');
1043 insert into WSlot values ('WS.003.3a', '003', '', '');
1044 insert into WSlot values ('WS.003.3b', '003', '', '');
1045 insert into WSlot values ('WS.101.1a', '101', '', '');
1046 insert into WSlot values ('WS.101.1b', '101', '', '');
1047 insert into WSlot values ('WS.101.2a', '101', '', '');
1048 insert into WSlot values ('WS.101.2b', '101', '', '');
1049 insert into WSlot values ('WS.101.3a', '101', '', '');
1050 insert into WSlot values ('WS.101.3b', '101', '', '');
1051 insert into WSlot values ('WS.102.1a', '102', '', '');
1052 insert into WSlot values ('WS.102.1b', '102', '', '');
1053 insert into WSlot values ('WS.102.2a', '102', '', '');
1054 insert into WSlot values ('WS.102.2b', '102', '', '');
1055 insert into WSlot values ('WS.102.3a', '102', '', '');
1056 insert into WSlot values ('WS.102.3b', '102', '', '');
1057 insert into WSlot values ('WS.105.1a', '105', '', '');
1058 insert into WSlot values ('WS.105.1b', '105', '', '');
1059 insert into WSlot values ('WS.105.2a', '105', '', '');
1060 insert into WSlot values ('WS.105.2b', '105', '', '');
1061 insert into WSlot values ('WS.105.3a', '105', '', '');
1062 insert into WSlot values ('WS.105.3b', '105', '', '');
1063 insert into WSlot values ('WS.106.1a', '106', '', '');
1064 insert into WSlot values ('WS.106.1b', '106', '', '');
1065 insert into WSlot values ('WS.106.2a', '106', '', '');
1066 insert into WSlot values ('WS.106.2b', '106', '', '');
1067 insert into WSlot values ('WS.106.3a', '106', '', '');
1068 insert into WSlot values ('WS.106.3b', '106', '', '');
1069 --
1070 -- Now create the patch fields and their slots
1071 --
1072 insert into PField values ('PF0_1', 'Wallslots basement');
1073 --
1074 -- The cables for these will be made later, so they are unconnected for now
1075 --
1076 insert into PSlot values ('PS.base.a1', 'PF0_1', '', '');
1077 insert into PSlot values ('PS.base.a2', 'PF0_1', '', '');
1078 insert into PSlot values ('PS.base.a3', 'PF0_1', '', '');
1079 insert into PSlot values ('PS.base.a4', 'PF0_1', '', '');
1080 insert into PSlot values ('PS.base.a5', 'PF0_1', '', '');
1081 insert into PSlot values ('PS.base.a6', 'PF0_1', '', '');
1082 --
1083 -- These are already wired to the wall connectors
1084 --
1085 insert into PSlot values ('PS.base.b1', 'PF0_1', '', 'WS.002.1a');
1086 insert into PSlot values ('PS.base.b2', 'PF0_1', '', 'WS.002.1b');
1087 insert into PSlot values ('PS.base.b3', 'PF0_1', '', 'WS.002.2a');
1088 insert into PSlot values ('PS.base.b4', 'PF0_1', '', 'WS.002.2b');
1089 insert into PSlot values ('PS.base.b5', 'PF0_1', '', 'WS.002.3a');
1090 insert into PSlot values ('PS.base.b6', 'PF0_1', '', 'WS.002.3b');
1091 insert into PSlot values ('PS.base.c1', 'PF0_1', '', 'WS.003.1a');
1092 insert into PSlot values ('PS.base.c2', 'PF0_1', '', 'WS.003.1b');
1093 insert into PSlot values ('PS.base.c3', 'PF0_1', '', 'WS.003.2a');
1094 insert into PSlot values ('PS.base.c4', 'PF0_1', '', 'WS.003.2b');
1095 insert into PSlot values ('PS.base.c5', 'PF0_1', '', 'WS.003.3a');
1096 insert into PSlot values ('PS.base.c6', 'PF0_1', '', 'WS.003.3b');
1097 --
1098 -- This patchfield will be renamed later into PF0_2 - so its
1099 -- slots references in pfname should follow
1100 --
1101 insert into PField values ('PF0_X', 'Phonelines basement');
1102 insert into PSlot values ('PS.base.ta1', 'PF0_X', '', '');
1103 insert into PSlot values ('PS.base.ta2', 'PF0_X', '', '');
1104 insert into PSlot values ('PS.base.ta3', 'PF0_X', '', '');
1105 insert into PSlot values ('PS.base.ta4', 'PF0_X', '', '');
1106 insert into PSlot values ('PS.base.ta5', 'PF0_X', '', '');
1107 insert into PSlot values ('PS.base.ta6', 'PF0_X', '', '');
1108 insert into PSlot values ('PS.base.tb1', 'PF0_X', '', '');
1109 insert into PSlot values ('PS.base.tb2', 'PF0_X', '', '');
1110 insert into PSlot values ('PS.base.tb3', 'PF0_X', '', '');
1111 insert into PSlot values ('PS.base.tb4', 'PF0_X', '', '');
1112 insert into PSlot values ('PS.base.tb5', 'PF0_X', '', '');
1113 insert into PSlot values ('PS.base.tb6', 'PF0_X', '', '');
1114 insert into PField values ('PF1_1', 'Wallslots first floor');
1115 insert into PSlot values ('PS.first.a1', 'PF1_1', '', 'WS.101.1a');
1116 insert into PSlot values ('PS.first.a2', 'PF1_1', '', 'WS.101.1b');
1117 insert into PSlot values ('PS.first.a3', 'PF1_1', '', 'WS.101.2a');
1118 insert into PSlot values ('PS.first.a4', 'PF1_1', '', 'WS.101.2b');
1119 insert into PSlot values ('PS.first.a5', 'PF1_1', '', 'WS.101.3a');
1120 insert into PSlot values ('PS.first.a6', 'PF1_1', '', 'WS.101.3b');
1121 insert into PSlot values ('PS.first.b1', 'PF1_1', '', 'WS.102.1a');
1122 insert into PSlot values ('PS.first.b2', 'PF1_1', '', 'WS.102.1b');
1123 insert into PSlot values ('PS.first.b3', 'PF1_1', '', 'WS.102.2a');
1124 insert into PSlot values ('PS.first.b4', 'PF1_1', '', 'WS.102.2b');
1125 insert into PSlot values ('PS.first.b5', 'PF1_1', '', 'WS.102.3a');
1126 insert into PSlot values ('PS.first.b6', 'PF1_1', '', 'WS.102.3b');
1127 insert into PSlot values ('PS.first.c1', 'PF1_1', '', 'WS.105.1a');
1128 insert into PSlot values ('PS.first.c2', 'PF1_1', '', 'WS.105.1b');
1129 insert into PSlot values ('PS.first.c3', 'PF1_1', '', 'WS.105.2a');
1130 insert into PSlot values ('PS.first.c4', 'PF1_1', '', 'WS.105.2b');
1131 insert into PSlot values ('PS.first.c5', 'PF1_1', '', 'WS.105.3a');
1132 insert into PSlot values ('PS.first.c6', 'PF1_1', '', 'WS.105.3b');
1133 insert into PSlot values ('PS.first.d1', 'PF1_1', '', 'WS.106.1a');
1134 insert into PSlot values ('PS.first.d2', 'PF1_1', '', 'WS.106.1b');
1135 insert into PSlot values ('PS.first.d3', 'PF1_1', '', 'WS.106.2a');
1136 insert into PSlot values ('PS.first.d4', 'PF1_1', '', 'WS.106.2b');
1137 insert into PSlot values ('PS.first.d5', 'PF1_1', '', 'WS.106.3a');
1138 insert into PSlot values ('PS.first.d6', 'PF1_1', '', 'WS.106.3b');
1139 --
1140 -- Now we wire the wall connectors 1a-2a in room 001 to the
1141 -- patchfield. In the second update we make an error, and
1142 -- correct it after
1143 --
1144 update PSlot set backlink = 'WS.001.1a' where slotname = 'PS.base.a1';
1145 update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a3';
1146 select * from WSlot where roomno = '001' order by slotname;
1147 slotname | roomno | slotlink | backlink
1148 ----------------------+----------+----------------------+----------------------
1149 WS.001.1a | 001 | | PS.base.a1
1150 WS.001.1b | 001 | | PS.base.a3
1151 WS.001.2a | 001 | |
1152 WS.001.2b | 001 | |
1153 WS.001.3a | 001 | |
1154 WS.001.3b | 001 | |
1155 (6 rows)
1157 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1158 slotname | pfname | slotlink | backlink
1159 ----------------------+--------+----------------------+----------------------
1160 PS.base.a1 | PF0_1 | | WS.001.1a
1161 PS.base.a2 | PF0_1 | |
1162 PS.base.a3 | PF0_1 | | WS.001.1b
1163 PS.base.a4 | PF0_1 | |
1164 PS.base.a5 | PF0_1 | |
1165 PS.base.a6 | PF0_1 | |
1166 (6 rows)
1168 update PSlot set backlink = 'WS.001.2a' where slotname = 'PS.base.a3';
1169 select * from WSlot where roomno = '001' order by slotname;
1170 slotname | roomno | slotlink | backlink
1171 ----------------------+----------+----------------------+----------------------
1172 WS.001.1a | 001 | | PS.base.a1
1173 WS.001.1b | 001 | |
1174 WS.001.2a | 001 | | PS.base.a3
1175 WS.001.2b | 001 | |
1176 WS.001.3a | 001 | |
1177 WS.001.3b | 001 | |
1178 (6 rows)
1180 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1181 slotname | pfname | slotlink | backlink
1182 ----------------------+--------+----------------------+----------------------
1183 PS.base.a1 | PF0_1 | | WS.001.1a
1184 PS.base.a2 | PF0_1 | |
1185 PS.base.a3 | PF0_1 | | WS.001.2a
1186 PS.base.a4 | PF0_1 | |
1187 PS.base.a5 | PF0_1 | |
1188 PS.base.a6 | PF0_1 | |
1189 (6 rows)
1191 update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a2';
1192 select * from WSlot where roomno = '001' order by slotname;
1193 slotname | roomno | slotlink | backlink
1194 ----------------------+----------+----------------------+----------------------
1195 WS.001.1a | 001 | | PS.base.a1
1196 WS.001.1b | 001 | | PS.base.a2
1197 WS.001.2a | 001 | | PS.base.a3
1198 WS.001.2b | 001 | |
1199 WS.001.3a | 001 | |
1200 WS.001.3b | 001 | |
1201 (6 rows)
1203 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1204 slotname | pfname | slotlink | backlink
1205 ----------------------+--------+----------------------+----------------------
1206 PS.base.a1 | PF0_1 | | WS.001.1a
1207 PS.base.a2 | PF0_1 | | WS.001.1b
1208 PS.base.a3 | PF0_1 | | WS.001.2a
1209 PS.base.a4 | PF0_1 | |
1210 PS.base.a5 | PF0_1 | |
1211 PS.base.a6 | PF0_1 | |
1212 (6 rows)
1214 --
1215 -- Same procedure for 2b-3b but this time updating the WSlot instead
1216 -- of the PSlot. Due to the triggers the result is the same:
1217 -- WSlot and corresponding PSlot point to each other.
1218 --
1219 update WSlot set backlink = 'PS.base.a4' where slotname = 'WS.001.2b';
1220 update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3a';
1221 select * from WSlot where roomno = '001' order by slotname;
1222 slotname | roomno | slotlink | backlink
1223 ----------------------+----------+----------------------+----------------------
1224 WS.001.1a | 001 | | PS.base.a1
1225 WS.001.1b | 001 | | PS.base.a2
1226 WS.001.2a | 001 | | PS.base.a3
1227 WS.001.2b | 001 | | PS.base.a4
1228 WS.001.3a | 001 | | PS.base.a6
1229 WS.001.3b | 001 | |
1230 (6 rows)
1232 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1233 slotname | pfname | slotlink | backlink
1234 ----------------------+--------+----------------------+----------------------
1235 PS.base.a1 | PF0_1 | | WS.001.1a
1236 PS.base.a2 | PF0_1 | | WS.001.1b
1237 PS.base.a3 | PF0_1 | | WS.001.2a
1238 PS.base.a4 | PF0_1 | | WS.001.2b
1239 PS.base.a5 | PF0_1 | |
1240 PS.base.a6 | PF0_1 | | WS.001.3a
1241 (6 rows)
1243 update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3b';
1244 select * from WSlot where roomno = '001' order by slotname;
1245 slotname | roomno | slotlink | backlink
1246 ----------------------+----------+----------------------+----------------------
1247 WS.001.1a | 001 | | PS.base.a1
1248 WS.001.1b | 001 | | PS.base.a2
1249 WS.001.2a | 001 | | PS.base.a3
1250 WS.001.2b | 001 | | PS.base.a4
1251 WS.001.3a | 001 | |
1252 WS.001.3b | 001 | | PS.base.a6
1253 (6 rows)
1255 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1256 slotname | pfname | slotlink | backlink
1257 ----------------------+--------+----------------------+----------------------
1258 PS.base.a1 | PF0_1 | | WS.001.1a
1259 PS.base.a2 | PF0_1 | | WS.001.1b
1260 PS.base.a3 | PF0_1 | | WS.001.2a
1261 PS.base.a4 | PF0_1 | | WS.001.2b
1262 PS.base.a5 | PF0_1 | |
1263 PS.base.a6 | PF0_1 | | WS.001.3b
1264 (6 rows)
1266 update WSlot set backlink = 'PS.base.a5' where slotname = 'WS.001.3a';
1267 select * from WSlot where roomno = '001' order by slotname;
1268 slotname | roomno | slotlink | backlink
1269 ----------------------+----------+----------------------+----------------------
1270 WS.001.1a | 001 | | PS.base.a1
1271 WS.001.1b | 001 | | PS.base.a2
1272 WS.001.2a | 001 | | PS.base.a3
1273 WS.001.2b | 001 | | PS.base.a4
1274 WS.001.3a | 001 | | PS.base.a5
1275 WS.001.3b | 001 | | PS.base.a6
1276 (6 rows)
1278 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1279 slotname | pfname | slotlink | backlink
1280 ----------------------+--------+----------------------+----------------------
1281 PS.base.a1 | PF0_1 | | WS.001.1a
1282 PS.base.a2 | PF0_1 | | WS.001.1b
1283 PS.base.a3 | PF0_1 | | WS.001.2a
1284 PS.base.a4 | PF0_1 | | WS.001.2b
1285 PS.base.a5 | PF0_1 | | WS.001.3a
1286 PS.base.a6 | PF0_1 | | WS.001.3b
1287 (6 rows)
1289 insert into PField values ('PF1_2', 'Phonelines first floor');
1290 insert into PSlot values ('PS.first.ta1', 'PF1_2', '', '');
1291 insert into PSlot values ('PS.first.ta2', 'PF1_2', '', '');
1292 insert into PSlot values ('PS.first.ta3', 'PF1_2', '', '');
1293 insert into PSlot values ('PS.first.ta4', 'PF1_2', '', '');
1294 insert into PSlot values ('PS.first.ta5', 'PF1_2', '', '');
1295 insert into PSlot values ('PS.first.ta6', 'PF1_2', '', '');
1296 insert into PSlot values ('PS.first.tb1', 'PF1_2', '', '');
1297 insert into PSlot values ('PS.first.tb2', 'PF1_2', '', '');
1298 insert into PSlot values ('PS.first.tb3', 'PF1_2', '', '');
1299 insert into PSlot values ('PS.first.tb4', 'PF1_2', '', '');
1300 insert into PSlot values ('PS.first.tb5', 'PF1_2', '', '');
1301 insert into PSlot values ('PS.first.tb6', 'PF1_2', '', '');
1302 --
1303 -- Fix the wrong name for patchfield PF0_2
1304 --
1305 update PField set name = 'PF0_2' where name = 'PF0_X';
1306 select * from PSlot order by slotname;
1307 slotname | pfname | slotlink | backlink
1308 ----------------------+--------+----------------------+----------------------
1309 PS.base.a1 | PF0_1 | | WS.001.1a
1310 PS.base.a2 | PF0_1 | | WS.001.1b
1311 PS.base.a3 | PF0_1 | | WS.001.2a
1312 PS.base.a4 | PF0_1 | | WS.001.2b
1313 PS.base.a5 | PF0_1 | | WS.001.3a
1314 PS.base.a6 | PF0_1 | | WS.001.3b
1315 PS.base.b1 | PF0_1 | | WS.002.1a
1316 PS.base.b2 | PF0_1 | | WS.002.1b
1317 PS.base.b3 | PF0_1 | | WS.002.2a
1318 PS.base.b4 | PF0_1 | | WS.002.2b
1319 PS.base.b5 | PF0_1 | | WS.002.3a
1320 PS.base.b6 | PF0_1 | | WS.002.3b
1321 PS.base.c1 | PF0_1 | | WS.003.1a
1322 PS.base.c2 | PF0_1 | | WS.003.1b
1323 PS.base.c3 | PF0_1 | | WS.003.2a
1324 PS.base.c4 | PF0_1 | | WS.003.2b
1325 PS.base.c5 | PF0_1 | | WS.003.3a
1326 PS.base.c6 | PF0_1 | | WS.003.3b
1327 PS.base.ta1 | PF0_2 | |
1328 PS.base.ta2 | PF0_2 | |
1329 PS.base.ta3 | PF0_2 | |
1330 PS.base.ta4 | PF0_2 | |
1331 PS.base.ta5 | PF0_2 | |
1332 PS.base.ta6 | PF0_2 | |
1333 PS.base.tb1 | PF0_2 | |
1334 PS.base.tb2 | PF0_2 | |
1335 PS.base.tb3 | PF0_2 | |
1336 PS.base.tb4 | PF0_2 | |
1337 PS.base.tb5 | PF0_2 | |
1338 PS.base.tb6 | PF0_2 | |
1339 PS.first.a1 | PF1_1 | | WS.101.1a
1340 PS.first.a2 | PF1_1 | | WS.101.1b
1341 PS.first.a3 | PF1_1 | | WS.101.2a
1342 PS.first.a4 | PF1_1 | | WS.101.2b
1343 PS.first.a5 | PF1_1 | | WS.101.3a
1344 PS.first.a6 | PF1_1 | | WS.101.3b
1345 PS.first.b1 | PF1_1 | | WS.102.1a
1346 PS.first.b2 | PF1_1 | | WS.102.1b
1347 PS.first.b3 | PF1_1 | | WS.102.2a
1348 PS.first.b4 | PF1_1 | | WS.102.2b
1349 PS.first.b5 | PF1_1 | | WS.102.3a
1350 PS.first.b6 | PF1_1 | | WS.102.3b
1351 PS.first.c1 | PF1_1 | | WS.105.1a
1352 PS.first.c2 | PF1_1 | | WS.105.1b
1353 PS.first.c3 | PF1_1 | | WS.105.2a
1354 PS.first.c4 | PF1_1 | | WS.105.2b
1355 PS.first.c5 | PF1_1 | | WS.105.3a
1356 PS.first.c6 | PF1_1 | | WS.105.3b
1357 PS.first.d1 | PF1_1 | | WS.106.1a
1358 PS.first.d2 | PF1_1 | | WS.106.1b
1359 PS.first.d3 | PF1_1 | | WS.106.2a
1360 PS.first.d4 | PF1_1 | | WS.106.2b
1361 PS.first.d5 | PF1_1 | | WS.106.3a
1362 PS.first.d6 | PF1_1 | | WS.106.3b
1363 PS.first.ta1 | PF1_2 | |
1364 PS.first.ta2 | PF1_2 | |
1365 PS.first.ta3 | PF1_2 | |
1366 PS.first.ta4 | PF1_2 | |
1367 PS.first.ta5 | PF1_2 | |
1368 PS.first.ta6 | PF1_2 | |
1369 PS.first.tb1 | PF1_2 | |
1370 PS.first.tb2 | PF1_2 | |
1371 PS.first.tb3 | PF1_2 | |
1372 PS.first.tb4 | PF1_2 | |
1373 PS.first.tb5 | PF1_2 | |
1374 PS.first.tb6 | PF1_2 | |
1375 (66 rows)
1377 select * from WSlot order by slotname;
1378 slotname | roomno | slotlink | backlink
1379 ----------------------+----------+----------------------+----------------------
1380 WS.001.1a | 001 | | PS.base.a1
1381 WS.001.1b | 001 | | PS.base.a2
1382 WS.001.2a | 001 | | PS.base.a3
1383 WS.001.2b | 001 | | PS.base.a4
1384 WS.001.3a | 001 | | PS.base.a5
1385 WS.001.3b | 001 | | PS.base.a6
1386 WS.002.1a | 002 | | PS.base.b1
1387 WS.002.1b | 002 | | PS.base.b2
1388 WS.002.2a | 002 | | PS.base.b3
1389 WS.002.2b | 002 | | PS.base.b4
1390 WS.002.3a | 002 | | PS.base.b5
1391 WS.002.3b | 002 | | PS.base.b6
1392 WS.003.1a | 003 | | PS.base.c1
1393 WS.003.1b | 003 | | PS.base.c2
1394 WS.003.2a | 003 | | PS.base.c3
1395 WS.003.2b | 003 | | PS.base.c4
1396 WS.003.3a | 003 | | PS.base.c5
1397 WS.003.3b | 003 | | PS.base.c6
1398 WS.101.1a | 101 | | PS.first.a1
1399 WS.101.1b | 101 | | PS.first.a2
1400 WS.101.2a | 101 | | PS.first.a3
1401 WS.101.2b | 101 | | PS.first.a4
1402 WS.101.3a | 101 | | PS.first.a5
1403 WS.101.3b | 101 | | PS.first.a6
1404 WS.102.1a | 102 | | PS.first.b1
1405 WS.102.1b | 102 | | PS.first.b2
1406 WS.102.2a | 102 | | PS.first.b3
1407 WS.102.2b | 102 | | PS.first.b4
1408 WS.102.3a | 102 | | PS.first.b5
1409 WS.102.3b | 102 | | PS.first.b6
1410 WS.105.1a | 105 | | PS.first.c1
1411 WS.105.1b | 105 | | PS.first.c2
1412 WS.105.2a | 105 | | PS.first.c3
1413 WS.105.2b | 105 | | PS.first.c4
1414 WS.105.3a | 105 | | PS.first.c5
1415 WS.105.3b | 105 | | PS.first.c6
1416 WS.106.1a | 106 | | PS.first.d1
1417 WS.106.1b | 106 | | PS.first.d2
1418 WS.106.2a | 106 | | PS.first.d3
1419 WS.106.2b | 106 | | PS.first.d4
1420 WS.106.3a | 106 | | PS.first.d5
1421 WS.106.3b | 106 | | PS.first.d6
1422 (42 rows)
1424 --
1425 -- Install the central phone system and create the phone numbers.
1426 -- They are wired on insert to the patchfields. Again the
1427 -- triggers automatically tell the PSlots to update their
1428 -- backlink field.
1429 --
1430 insert into PLine values ('PL.001', '-0', 'Central call', 'PS.base.ta1');
1431 insert into PLine values ('PL.002', '-101', '', 'PS.base.ta2');
1432 insert into PLine values ('PL.003', '-102', '', 'PS.base.ta3');
1433 insert into PLine values ('PL.004', '-103', '', 'PS.base.ta5');
1434 insert into PLine values ('PL.005', '-104', '', 'PS.base.ta6');
1435 insert into PLine values ('PL.006', '-106', '', 'PS.base.tb2');
1436 insert into PLine values ('PL.007', '-108', '', 'PS.base.tb3');
1437 insert into PLine values ('PL.008', '-109', '', 'PS.base.tb4');
1438 insert into PLine values ('PL.009', '-121', '', 'PS.base.tb5');
1439 insert into PLine values ('PL.010', '-122', '', 'PS.base.tb6');
1440 insert into PLine values ('PL.015', '-134', '', 'PS.first.ta1');
1441 insert into PLine values ('PL.016', '-137', '', 'PS.first.ta3');
1442 insert into PLine values ('PL.017', '-139', '', 'PS.first.ta4');
1443 insert into PLine values ('PL.018', '-362', '', 'PS.first.tb1');
1444 insert into PLine values ('PL.019', '-363', '', 'PS.first.tb2');
1445 insert into PLine values ('PL.020', '-364', '', 'PS.first.tb3');
1446 insert into PLine values ('PL.021', '-365', '', 'PS.first.tb5');
1447 insert into PLine values ('PL.022', '-367', '', 'PS.first.tb6');
1448 insert into PLine values ('PL.028', '-501', 'Fax entrance', 'PS.base.ta2');
1449 insert into PLine values ('PL.029', '-502', 'Fax first floor', 'PS.first.ta1');
1450 --
1451 -- Buy some phones, plug them into the wall and patch the
1452 -- phone lines to the corresponding patchfield slots.
1453 --
1454 insert into PHone values ('PH.hc001', 'Hicom standard', 'WS.001.1a');
1455 update PSlot set slotlink = 'PS.base.ta1' where slotname = 'PS.base.a1';
1456 insert into PHone values ('PH.hc002', 'Hicom standard', 'WS.002.1a');
1457 update PSlot set slotlink = 'PS.base.ta5' where slotname = 'PS.base.b1';
1458 insert into PHone values ('PH.hc003', 'Hicom standard', 'WS.002.2a');
1459 update PSlot set slotlink = 'PS.base.tb2' where slotname = 'PS.base.b3';
1460 insert into PHone values ('PH.fax001', 'Canon fax', 'WS.001.2a');
1461 update PSlot set slotlink = 'PS.base.ta2' where slotname = 'PS.base.a3';
1462 --
1463 -- Install a hub at one of the patchfields, plug a computers
1464 -- ethernet interface into the wall and patch it to the hub.
1465 --
1466 insert into Hub values ('base.hub1', 'Patchfield PF0_1 hub', 16);
1467 insert into System values ('orion', 'PC');
1468 insert into IFace values ('IF', 'orion', 'eth0', 'WS.002.1b');
1469 update PSlot set slotlink = 'HS.base.hub1.1' where slotname = 'PS.base.b2';
1470 --
1471 -- Now we take a look at the patchfield
1472 --
1473 select * from PField_v1 where pfname = 'PF0_1' order by slotname;
1474 pfname | slotname | backside | patch
1475 --------+----------------------+----------------------------------------------------------+-----------------------------------------------
1476 PF0_1 | PS.base.a1 | WS.001.1a in room 001 -> Phone PH.hc001 (Hicom standard) | PS.base.ta1 -> Phone line -0 (Central call)
1477 PF0_1 | PS.base.a2 | WS.001.1b in room 001 -> - | -
1478 PF0_1 | PS.base.a3 | WS.001.2a in room 001 -> Phone PH.fax001 (Canon fax) | PS.base.ta2 -> Phone line -501 (Fax entrance)
1479 PF0_1 | PS.base.a4 | WS.001.2b in room 001 -> - | -
1480 PF0_1 | PS.base.a5 | WS.001.3a in room 001 -> - | -
1481 PF0_1 | PS.base.a6 | WS.001.3b in room 001 -> - | -
1482 PF0_1 | PS.base.b1 | WS.002.1a in room 002 -> Phone PH.hc002 (Hicom standard) | PS.base.ta5 -> Phone line -103
1483 PF0_1 | PS.base.b2 | WS.002.1b in room 002 -> orion IF eth0 (PC) | Patchfield PF0_1 hub slot 1
1484 PF0_1 | PS.base.b3 | WS.002.2a in room 002 -> Phone PH.hc003 (Hicom standard) | PS.base.tb2 -> Phone line -106
1485 PF0_1 | PS.base.b4 | WS.002.2b in room 002 -> - | -
1486 PF0_1 | PS.base.b5 | WS.002.3a in room 002 -> - | -
1487 PF0_1 | PS.base.b6 | WS.002.3b in room 002 -> - | -
1488 PF0_1 | PS.base.c1 | WS.003.1a in room 003 -> - | -
1489 PF0_1 | PS.base.c2 | WS.003.1b in room 003 -> - | -
1490 PF0_1 | PS.base.c3 | WS.003.2a in room 003 -> - | -
1491 PF0_1 | PS.base.c4 | WS.003.2b in room 003 -> - | -
1492 PF0_1 | PS.base.c5 | WS.003.3a in room 003 -> - | -
1493 PF0_1 | PS.base.c6 | WS.003.3b in room 003 -> - | -
1494 (18 rows)
1496 select * from PField_v1 where pfname = 'PF0_2' order by slotname;
1497 pfname | slotname | backside | patch
1498 --------+----------------------+--------------------------------+------------------------------------------------------------------------
1499 PF0_2 | PS.base.ta1 | Phone line -0 (Central call) | PS.base.a1 -> WS.001.1a in room 001 -> Phone PH.hc001 (Hicom standard)
1500 PF0_2 | PS.base.ta2 | Phone line -501 (Fax entrance) | PS.base.a3 -> WS.001.2a in room 001 -> Phone PH.fax001 (Canon fax)
1501 PF0_2 | PS.base.ta3 | Phone line -102 | -
1502 PF0_2 | PS.base.ta4 | - | -
1503 PF0_2 | PS.base.ta5 | Phone line -103 | PS.base.b1 -> WS.002.1a in room 002 -> Phone PH.hc002 (Hicom standard)
1504 PF0_2 | PS.base.ta6 | Phone line -104 | -
1505 PF0_2 | PS.base.tb1 | - | -
1506 PF0_2 | PS.base.tb2 | Phone line -106 | PS.base.b3 -> WS.002.2a in room 002 -> Phone PH.hc003 (Hicom standard)
1507 PF0_2 | PS.base.tb3 | Phone line -108 | -
1508 PF0_2 | PS.base.tb4 | Phone line -109 | -
1509 PF0_2 | PS.base.tb5 | Phone line -121 | -
1510 PF0_2 | PS.base.tb6 | Phone line -122 | -
1511 (12 rows)
1513 --
1514 -- Finally we want errors
1515 --
1516 insert into PField values ('PF1_1', 'should fail due to unique index');
1517 ERROR: duplicate key value violates unique constraint "pfield_name"
1518 DETAIL: Key (name)=(PF1_1) already exists.
1519 update PSlot set backlink = 'WS.not.there' where slotname = 'PS.base.a1';
1520 ERROR: WS.not.there does not exist
1521 CONTEXT: PL/pgSQL function tg_backlink_set(character,character) line 30 at RAISE
1522 PL/pgSQL function tg_backlink_a() line 17 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_set(character,character) line 47 at RAISE
1526 PL/pgSQL function tg_backlink_a() line 17 at assignment
1527 update PSlot set slotlink = 'PS.not.there' where slotname = 'PS.base.a1';
1528 ERROR: PS.not.there does not exist
1529 CONTEXT: PL/pgSQL function tg_slotlink_set(character,character) line 30 at RAISE
1530 PL/pgSQL function tg_slotlink_a() line 17 at assignment
1531 update PSlot set slotlink = 'XX.illegal' where slotname = 'PS.base.a1';
1532 ERROR: illegal slotlink beginning with XX
1533 CONTEXT: PL/pgSQL function tg_slotlink_set(character,character) line 77 at RAISE
1534 PL/pgSQL function tg_slotlink_a() line 17 at assignment
1535 insert into HSlot values ('HS', 'base.hub1', 1, '');
1536 ERROR: duplicate key value violates unique constraint "hslot_name"
1537 DETAIL: Key (slotname)=(HS.base.hub1.1 ) already exists.
1538 insert into HSlot values ('HS', 'base.hub1', 20, '');
1539 ERROR: no manual manipulation of HSlot
1540 CONTEXT: PL/pgSQL function tg_hslot_biu() line 12 at RAISE
1541 delete from HSlot;
1542 ERROR: no manual manipulation of HSlot
1543 CONTEXT: PL/pgSQL function tg_hslot_bd() line 12 at RAISE
1544 insert into IFace values ('IF', 'notthere', 'eth0', '');
1545 ERROR: system "notthere" does not exist
1546 CONTEXT: PL/pgSQL function tg_iface_biu() line 8 at RAISE
1547 insert into IFace values ('IF', 'orion', 'ethernet_interface_name_too_long', '');
1548 ERROR: IFace slotname "IF.orion.ethernet_interface_name_too_long" too long (20 char max)
1549 CONTEXT: PL/pgSQL function tg_iface_biu() line 14 at RAISE
1550 --
1551 -- The following tests are unrelated to the scenario outlined above;
1552 -- they merely exercise specific parts of PL/pgSQL
1553 --
1554 --
1555 -- Test recursion, per bug report 7-Sep-01
1556 --
1557 CREATE FUNCTION recursion_test(int,int) RETURNS text AS '
1558 DECLARE rslt text;
1559 BEGIN
1560 IF $1 <= 0 THEN
1561 rslt = CAST($2 AS TEXT);
1562 ELSE
1563 rslt = CAST($1 AS TEXT) || '','' || recursion_test($1 - 1, $2);
1564 END IF;
1565 RETURN rslt;
1566 END;' LANGUAGE plpgsql;
1567 SELECT recursion_test(4,3);
1568 recursion_test
1569 ----------------
1570 4,3,2,1,3
1571 (1 row)
1573 --
1574 -- Test the FOUND magic variable
1575 --
1576 CREATE TABLE found_test_tbl (a int);
1577 create function test_found()
1578 returns boolean as '
1579 declare
1580 begin
1581 insert into found_test_tbl values (1);
1582 if FOUND then
1583 insert into found_test_tbl values (2);
1584 end if;
1586 update found_test_tbl set a = 100 where a = 1;
1587 if FOUND then
1588 insert into found_test_tbl values (3);
1589 end if;
1591 delete from found_test_tbl where a = 9999; -- matches no rows
1592 if not FOUND then
1593 insert into found_test_tbl values (4);
1594 end if;
1596 for i in 1 .. 10 loop
1597 -- no need to do anything
1598 end loop;
1599 if FOUND then
1600 insert into found_test_tbl values (5);
1601 end if;
1603 -- never executes the loop
1604 for i in 2 .. 1 loop
1605 -- no need to do anything
1606 end loop;
1607 if not FOUND then
1608 insert into found_test_tbl values (6);
1609 end if;
1610 return true;
1611 end;' language plpgsql;
1612 select test_found();
1613 test_found
1614 ------------
1615 t
1616 (1 row)
1618 select * from found_test_tbl;
1619 a
1620 -----
1621 2
1622 100
1623 3
1624 4
1625 5
1626 6
1627 (6 rows)
1629 --
1630 -- Test set-returning functions for PL/pgSQL
1631 --
1632 create function test_table_func_rec() returns setof found_test_tbl as '
1633 DECLARE
1634 rec RECORD;
1635 BEGIN
1636 FOR rec IN select * from found_test_tbl LOOP
1637 RETURN NEXT rec;
1638 END LOOP;
1639 RETURN;
1640 END;' language plpgsql;
1641 select * from test_table_func_rec();
1642 a
1643 -----
1644 2
1645 100
1646 3
1647 4
1648 5
1649 6
1650 (6 rows)
1652 create function test_table_func_row() returns setof found_test_tbl as '
1653 DECLARE
1654 row found_test_tbl%ROWTYPE;
1655 BEGIN
1656 FOR row IN select * from found_test_tbl LOOP
1657 RETURN NEXT row;
1658 END LOOP;
1659 RETURN;
1660 END;' language plpgsql;
1661 select * from test_table_func_row();
1662 a
1663 -----
1664 2
1665 100
1666 3
1667 4
1668 5
1669 6
1670 (6 rows)
1672 create function test_ret_set_scalar(int,int) returns setof int as '
1673 DECLARE
1674 i int;
1675 BEGIN
1676 FOR i IN $1 .. $2 LOOP
1677 RETURN NEXT i + 1;
1678 END LOOP;
1679 RETURN;
1680 END;' language plpgsql;
1681 select * from test_ret_set_scalar(1,10);
1682 test_ret_set_scalar
1683 ---------------------
1684 2
1685 3
1686 4
1687 5
1688 6
1689 7
1690 8
1691 9
1692 10
1693 11
1694 (10 rows)
1696 create function test_ret_set_rec_dyn(int) returns setof record as '
1697 DECLARE
1698 retval RECORD;
1699 BEGIN
1700 IF $1 > 10 THEN
1701 SELECT INTO retval 5, 10, 15;
1702 RETURN NEXT retval;
1703 RETURN NEXT retval;
1704 ELSE
1705 SELECT INTO retval 50, 5::numeric, ''xxx''::text;
1706 RETURN NEXT retval;
1707 RETURN NEXT retval;
1708 END IF;
1709 RETURN;
1710 END;' language plpgsql;
1711 SELECT * FROM test_ret_set_rec_dyn(1500) AS (a int, b int, c int);
1712 a | b | c
1713 ---+----+----
1714 5 | 10 | 15
1715 5 | 10 | 15
1716 (2 rows)
1718 SELECT * FROM test_ret_set_rec_dyn(5) AS (a int, b numeric, c text);
1719 a | b | c
1720 ----+---+-----
1721 50 | 5 | xxx
1722 50 | 5 | xxx
1723 (2 rows)
1725 create function test_ret_rec_dyn(int) returns record as '
1726 DECLARE
1727 retval RECORD;
1728 BEGIN
1729 IF $1 > 10 THEN
1730 SELECT INTO retval 5, 10, 15;
1731 RETURN retval;
1732 ELSE
1733 SELECT INTO retval 50, 5::numeric, ''xxx''::text;
1734 RETURN retval;
1735 END IF;
1736 END;' language plpgsql;
1737 SELECT * FROM test_ret_rec_dyn(1500) AS (a int, b int, c int);
1738 a | b | c
1739 ---+----+----
1740 5 | 10 | 15
1741 (1 row)
1743 SELECT * FROM test_ret_rec_dyn(5) AS (a int, b numeric, c text);
1744 a | b | c
1745 ----+---+-----
1746 50 | 5 | xxx
1747 (1 row)
1749 --
1750 -- Test some simple polymorphism cases.
1751 --
1752 create function f1(x anyelement) returns anyelement as $$
1753 begin
1754 return x + 1;
1755 end$$ language plpgsql;
1756 select f1(42) as int, f1(4.5) as num;
1757 int | num
1758 -----+-----
1759 43 | 5.5
1760 (1 row)
1762 select f1(point(3,4)); -- fail for lack of + operator
1763 ERROR: operator does not exist: point + integer
1764 LINE 1: x + 1
1765 ^
1766 HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
1767 QUERY: x + 1
1768 CONTEXT: PL/pgSQL function f1(anyelement) line 3 at RETURN
1769 drop function f1(x anyelement);
1770 create function f1(x anyelement) returns anyarray as $$
1771 begin
1772 return array[x + 1, x + 2];
1773 end$$ language plpgsql;
1774 select f1(42) as int, f1(4.5) as num;
1775 int | num
1776 ---------+-----------
1777 {43,44} | {5.5,6.5}
1778 (1 row)
1780 drop function f1(x anyelement);
1781 create function f1(x anyarray) returns anyelement as $$
1782 begin
1783 return x[1];
1784 end$$ language plpgsql;
1785 select f1(array[2,4]) as int, f1(array[4.5, 7.7]) as num;
1786 int | num
1787 -----+-----
1788 2 | 4.5
1789 (1 row)
1791 select f1(stavalues1) from pg_statistic; -- fail, can't infer element type
1792 ERROR: cannot determine element type of "anyarray" argument
1793 drop function f1(x anyarray);
1794 create function f1(x anyarray) returns anyarray as $$
1795 begin
1796 return x;
1797 end$$ language plpgsql;
1798 select f1(array[2,4]) as int, f1(array[4.5, 7.7]) as num;
1799 int | num
1800 -------+-----------
1801 {2,4} | {4.5,7.7}
1802 (1 row)
1804 select f1(stavalues1) from pg_statistic; -- fail, can't infer element type
1805 ERROR: PL/pgSQL functions cannot accept type anyarray
1806 CONTEXT: compilation of PL/pgSQL function "f1" near line 1
1807 drop function f1(x anyarray);
1808 -- fail, can't infer type:
1809 create function f1(x anyelement) returns anyrange as $$
1810 begin
1811 return array[x + 1, x + 2];
1812 end$$ language plpgsql;
1813 ERROR: cannot determine result data type
1814 DETAIL: A result of type anyrange requires at least one input of type anyrange or anymultirange.
1815 create function f1(x anyrange) returns anyarray as $$
1816 begin
1817 return array[lower(x), upper(x)];
1818 end$$ language plpgsql;
1819 select f1(int4range(42, 49)) as int, f1(float8range(4.5, 7.8)) as num;
1820 int | num
1821 ---------+-----------
1822 {42,49} | {4.5,7.8}
1823 (1 row)
1825 drop function f1(x anyrange);
1826 create function f1(x anycompatible, y anycompatible) returns anycompatiblearray as $$
1827 begin
1828 return array[x, y];
1829 end$$ language plpgsql;
1830 select f1(2, 4) as int, f1(2, 4.5) as num;
1831 int | num
1832 -------+---------
1833 {2,4} | {2,4.5}
1834 (1 row)
1836 drop function f1(x anycompatible, y anycompatible);
1837 create function f1(x anycompatiblerange, y anycompatible, z anycompatible) returns anycompatiblearray as $$
1838 begin
1839 return array[lower(x), upper(x), y, z];
1840 end$$ language plpgsql;
1841 select f1(int4range(42, 49), 11, 2::smallint) as int, f1(float8range(4.5, 7.8), 7.8, 11::real) as num;
1842 int | num
1843 --------------+------------------
1844 {42,49,11,2} | {4.5,7.8,7.8,11}
1845 (1 row)
1847 select f1(int4range(42, 49), 11, 4.5) as fail; -- range type doesn't fit
1848 ERROR: function f1(int4range, integer, numeric) does not exist
1849 LINE 1: select f1(int4range(42, 49), 11, 4.5) as fail;
1850 ^
1851 HINT: No function matches the given name and argument types. You might need to add explicit type casts.
1852 drop function f1(x anycompatiblerange, y anycompatible, z anycompatible);
1853 -- fail, can't infer type:
1854 create function f1(x anycompatible) returns anycompatiblerange as $$
1855 begin
1856 return array[x + 1, x + 2];
1857 end$$ language plpgsql;
1858 ERROR: cannot determine result data type
1859 DETAIL: A result of type anycompatiblerange requires at least one input of type anycompatiblerange or anycompatiblemultirange.
1860 create function f1(x anycompatiblerange, y anycompatiblearray) returns anycompatiblerange as $$
1861 begin
1862 return x;
1863 end$$ language plpgsql;
1864 select f1(int4range(42, 49), array[11]) as int, f1(float8range(4.5, 7.8), array[7]) as num;
1865 int | num
1866 ---------+-----------
1867 [42,49) | [4.5,7.8)
1868 (1 row)
1870 drop function f1(x anycompatiblerange, y anycompatiblearray);
1871 create function f1(a anyelement, b anyarray,
1872 c anycompatible, d anycompatible,
1873 OUT x anyarray, OUT y anycompatiblearray)
1874 as $$
1875 begin
1876 x := a || b;
1877 y := array[c, d];
1878 end$$ language plpgsql;
1879 select x, pg_typeof(x), y, pg_typeof(y)
1880 from f1(11, array[1, 2], 42, 34.5);
1881 x | pg_typeof | y | pg_typeof
1882 ----------+-----------+-----------+-----------
1883 {11,1,2} | integer[] | {42,34.5} | numeric[]
1884 (1 row)
1886 select x, pg_typeof(x), y, pg_typeof(y)
1887 from f1(11, array[1, 2], point(1,2), point(3,4));
1888 x | pg_typeof | y | pg_typeof
1889 ----------+-----------+-------------------+-----------
1890 {11,1,2} | integer[] | {"(1,2)","(3,4)"} | point[]
1891 (1 row)
1893 select x, pg_typeof(x), y, pg_typeof(y)
1894 from f1(11, '{1,2}', point(1,2), '(3,4)');
1895 x | pg_typeof | y | pg_typeof
1896 ----------+-----------+-------------------+-----------
1897 {11,1,2} | integer[] | {"(1,2)","(3,4)"} | point[]
1898 (1 row)
1900 select x, pg_typeof(x), y, pg_typeof(y)
1901 from f1(11, array[1, 2.2], 42, 34.5); -- fail
1902 ERROR: function f1(integer, numeric[], integer, numeric) does not exist
1903 LINE 2: from f1(11, array[1, 2.2], 42, 34.5);
1904 ^
1905 HINT: No function matches the given name and argument types. You might need to add explicit type casts.
1906 drop function f1(a anyelement, b anyarray,
1907 c anycompatible, d anycompatible);
1908 --
1909 -- Test handling of OUT parameters, including polymorphic cases.
1910 -- Note that RETURN is optional with OUT params; we try both ways.
1911 --
1912 -- wrong way to do it:
1913 create function f1(in i int, out j int) returns int as $$
1914 begin
1915 return i+1;
1916 end$$ language plpgsql;
1917 ERROR: RETURN cannot have a parameter in function with OUT parameters
1918 LINE 3: return i+1;
1919 ^
1920 create function f1(in i int, out j int) as $$
1921 begin
1922 j := i+1;
1923 return;
1924 end$$ language plpgsql;
1925 select f1(42);
1926 f1
1927 ----
1928 43
1929 (1 row)
1931 select * from f1(42);
1932 j
1933 ----
1934 43
1935 (1 row)
1937 create or replace function f1(inout i int) as $$
1938 begin
1939 i := i+1;
1940 end$$ language plpgsql;
1941 select f1(42);
1942 f1
1943 ----
1944 43
1945 (1 row)
1947 select * from f1(42);
1948 i
1949 ----
1950 43
1951 (1 row)
1953 drop function f1(int);
1954 create function f1(in i int, out j int) returns setof int as $$
1955 begin
1956 j := i+1;
1957 return next;
1958 j := i+2;
1959 return next;
1960 return;
1961 end$$ language plpgsql;
1962 select * from f1(42);
1963 j
1964 ----
1965 43
1966 44
1967 (2 rows)
1969 drop function f1(int);
1970 create function f1(in i int, out j int, out k text) as $$
1971 begin
1972 j := i;
1973 j := j+1;
1974 k := 'foo';
1975 end$$ language plpgsql;
1976 select f1(42);
1977 f1
1978 ----------
1979 (43,foo)
1980 (1 row)
1982 select * from f1(42);
1983 j | k
1984 ----+-----
1985 43 | foo
1986 (1 row)
1988 drop function f1(int);
1989 create function f1(in i int, out j int, out k text) returns setof record as $$
1990 begin
1991 j := i+1;
1992 k := 'foo';
1993 return next;
1994 j := j+1;
1995 k := 'foot';
1996 return next;
1997 end$$ language plpgsql;
1998 select * from f1(42);
1999 j | k
2000 ----+------
2001 43 | foo
2002 44 | foot
2003 (2 rows)
2005 drop function f1(int);
2006 create function duplic(in i anyelement, out j anyelement, out k anyarray) as $$
2007 begin
2008 j := i;
2009 k := array[j,j];
2010 return;
2011 end$$ language plpgsql;
2012 select * from duplic(42);
2013 j | k
2014 ----+---------
2015 42 | {42,42}
2016 (1 row)
2018 select * from duplic('foo'::text);
2019 j | k
2020 -----+-----------
2021 foo | {foo,foo}
2022 (1 row)
2024 drop function duplic(anyelement);
2025 create function duplic(in i anycompatiblerange, out j anycompatible, out k anycompatiblearray) as $$
2026 begin
2027 j := lower(i);
2028 k := array[lower(i),upper(i)];
2029 return;
2030 end$$ language plpgsql;
2031 select * from duplic(int4range(42,49));
2032 j | k
2033 ----+---------
2034 42 | {42,49}
2035 (1 row)
2037 select * from duplic(textrange('aaa', 'bbb'));
2038 j | k
2039 -----+-----------
2040 aaa | {aaa,bbb}
2041 (1 row)
2043 drop function duplic(anycompatiblerange);
2044 --
2045 -- test PERFORM
2046 --
2047 create table perform_test (
2048 a INT,
2049 b INT
2050 );
2051 create function perform_simple_func(int) returns boolean as '
2052 BEGIN
2053 IF $1 < 20 THEN
2054 INSERT INTO perform_test VALUES ($1, $1 + 10);
2055 RETURN TRUE;
2056 ELSE
2057 RETURN FALSE;
2058 END IF;
2059 END;' language plpgsql;
2060 create function perform_test_func() returns void as '
2061 BEGIN
2062 IF FOUND then
2063 INSERT INTO perform_test VALUES (100, 100);
2064 END IF;
2066 PERFORM perform_simple_func(5);
2068 IF FOUND then
2069 INSERT INTO perform_test VALUES (100, 100);
2070 END IF;
2072 PERFORM perform_simple_func(50);
2074 IF FOUND then
2075 INSERT INTO perform_test VALUES (100, 100);
2076 END IF;
2078 RETURN;
2079 END;' language plpgsql;
2080 SELECT perform_test_func();
2081 perform_test_func
2082 -------------------
2084 (1 row)
2086 SELECT * FROM perform_test;
2087 a | b
2088 -----+-----
2089 5 | 15
2090 100 | 100
2091 100 | 100
2092 (3 rows)
2094 drop table perform_test;
2095 --
2096 -- Test proper snapshot handling in simple expressions
2097 --
2098 create temp table users(login text, id serial);
2099 create function sp_id_user(a_login text) returns int as $$
2100 declare x int;
2101 begin
2102 select into x id from users where login = a_login;
2103 if found then return x; end if;
2104 return 0;
2105 end$$ language plpgsql stable;
2106 insert into users values('user1');
2107 select sp_id_user('user1');
2108 sp_id_user
2109 ------------
2110 1
2111 (1 row)
2113 select sp_id_user('userx');
2114 sp_id_user
2115 ------------
2116 0
2117 (1 row)
2119 create function sp_add_user(a_login text) returns int as $$
2120 declare my_id_user int;
2121 begin
2122 my_id_user = sp_id_user( a_login );
2123 IF my_id_user > 0 THEN
2124 RETURN -1; -- error code for existing user
2125 END IF;
2126 INSERT INTO users ( login ) VALUES ( a_login );
2127 my_id_user = sp_id_user( a_login );
2128 IF my_id_user = 0 THEN
2129 RETURN -2; -- error code for insertion failure
2130 END IF;
2131 RETURN my_id_user;
2132 end$$ language plpgsql;
2133 select sp_add_user('user1');
2134 sp_add_user
2135 -------------
2136 -1
2137 (1 row)
2139 select sp_add_user('user2');
2140 sp_add_user
2141 -------------
2142 2
2143 (1 row)
2145 select sp_add_user('user2');
2146 sp_add_user
2147 -------------
2148 -1
2149 (1 row)
2151 select sp_add_user('user3');
2152 sp_add_user
2153 -------------
2154 3
2155 (1 row)
2157 select sp_add_user('user3');
2158 sp_add_user
2159 -------------
2160 -1
2161 (1 row)
2163 drop function sp_add_user(text);
2164 drop function sp_id_user(text);
2165 --
2166 -- tests for refcursors
2167 --
2168 create table rc_test (a int, b int);
2169 copy rc_test from stdin;
2170 create function return_unnamed_refcursor() returns refcursor as $$
2171 declare
2172 rc refcursor;
2173 begin
2174 open rc for select a from rc_test;
2175 return rc;
2176 end
2177 $$ language plpgsql;
2178 create function use_refcursor(rc refcursor) returns int as $$
2179 declare
2180 rc refcursor;
2181 x record;
2182 begin
2183 rc := return_unnamed_refcursor();
2184 fetch next from rc into x;
2185 return x.a;
2186 end
2187 $$ language plpgsql;
2188 select use_refcursor(return_unnamed_refcursor());
2189 use_refcursor
2190 ---------------
2191 5
2192 (1 row)
2194 create function return_refcursor(rc refcursor) returns refcursor as $$
2195 begin
2196 open rc for select a from rc_test;
2197 return rc;
2198 end
2199 $$ language plpgsql;
2200 create function refcursor_test1(refcursor) returns refcursor as $$
2201 begin
2202 perform return_refcursor($1);
2203 return $1;
2204 end
2205 $$ language plpgsql;
2206 begin;
2207 select refcursor_test1('test1');
2208 refcursor_test1
2209 -----------------
2210 test1
2211 (1 row)
2213 fetch next in test1;
2214 a
2215 ---
2216 5
2217 (1 row)
2219 select refcursor_test1('test2');
2220 refcursor_test1
2221 -----------------
2222 test2
2223 (1 row)
2225 fetch all from test2;
2226 a
2227 -----
2228 5
2229 50
2230 500
2231 (3 rows)
2233 commit;
2234 -- should fail
2235 fetch next from test1;
2236 ERROR: cursor "test1" does not exist
2237 create function refcursor_test2(int, int) returns boolean as $$
2238 declare
2239 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
2240 nonsense record;
2241 begin
2242 open c1($1, $2);
2243 fetch c1 into nonsense;
2244 close c1;
2245 if found then
2246 return true;
2247 else
2248 return false;
2249 end if;
2250 end
2251 $$ language plpgsql;
2252 select refcursor_test2(20000, 20000) as "Should be false",
2253 refcursor_test2(20, 20) as "Should be true";
2254 Should be false | Should be true
2255 -----------------+----------------
2256 f | t
2257 (1 row)
2259 --
2260 -- tests for cursors with named parameter arguments
2261 --
2262 create function namedparmcursor_test1(int, int) returns boolean as $$
2263 declare
2264 c1 cursor (param1 int, param12 int) for select * from rc_test where a > param1 and b > param12;
2265 nonsense record;
2266 begin
2267 open c1(param12 := $2, param1 := $1);
2268 fetch c1 into nonsense;
2269 close c1;
2270 if found then
2271 return true;
2272 else
2273 return false;
2274 end if;
2275 end
2276 $$ language plpgsql;
2277 select namedparmcursor_test1(20000, 20000) as "Should be false",
2278 namedparmcursor_test1(20, 20) as "Should be true";
2279 Should be false | Should be true
2280 -----------------+----------------
2281 f | t
2282 (1 row)
2284 -- mixing named and positional argument notations
2285 create function namedparmcursor_test2(int, int) returns boolean as $$
2286 declare
2287 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
2288 nonsense record;
2289 begin
2290 open c1(param1 := $1, $2);
2291 fetch c1 into nonsense;
2292 close c1;
2293 if found then
2294 return true;
2295 else
2296 return false;
2297 end if;
2298 end
2299 $$ language plpgsql;
2300 select namedparmcursor_test2(20, 20);
2301 namedparmcursor_test2
2302 -----------------------
2303 t
2304 (1 row)
2306 -- mixing named and positional: param2 is given twice, once in named notation
2307 -- and second time in positional notation. Should throw an error at parse time
2308 create function namedparmcursor_test3() returns void as $$
2309 declare
2310 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
2311 begin
2312 open c1(param2 := 20, 21);
2313 end
2314 $$ language plpgsql;
2315 ERROR: value for parameter "param2" of cursor "c1" specified more than once
2316 LINE 5: open c1(param2 := 20, 21);
2317 ^
2318 -- mixing named and positional: same as previous test, but param1 is duplicated
2319 create function namedparmcursor_test4() returns void as $$
2320 declare
2321 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
2322 begin
2323 open c1(20, param1 := 21);
2324 end
2325 $$ language plpgsql;
2326 ERROR: value for parameter "param1" of cursor "c1" specified more than once
2327 LINE 5: open c1(20, param1 := 21);
2328 ^
2329 -- duplicate named parameter, should throw an error at parse time
2330 create function namedparmcursor_test5() returns void as $$
2331 declare
2332 c1 cursor (p1 int, p2 int) for
2333 select * from tenk1 where thousand = p1 and tenthous = p2;
2334 begin
2335 open c1 (p2 := 77, p2 := 42);
2336 end
2337 $$ language plpgsql;
2338 ERROR: value for parameter "p2" of cursor "c1" specified more than once
2339 LINE 6: open c1 (p2 := 77, p2 := 42);
2340 ^
2341 -- not enough parameters, should throw an error at parse time
2342 create function namedparmcursor_test6() returns void as $$
2343 declare
2344 c1 cursor (p1 int, p2 int) for
2345 select * from tenk1 where thousand = p1 and tenthous = p2;
2346 begin
2347 open c1 (p2 := 77);
2348 end
2349 $$ language plpgsql;
2350 ERROR: not enough arguments for cursor "c1"
2351 LINE 6: open c1 (p2 := 77);
2352 ^
2353 -- division by zero runtime error, the context given in the error message
2354 -- should be sensible
2355 create function namedparmcursor_test7() returns void as $$
2356 declare
2357 c1 cursor (p1 int, p2 int) for
2358 select * from tenk1 where thousand = p1 and tenthous = p2;
2359 begin
2360 open c1 (p2 := 77, p1 := 42/0);
2361 end $$ language plpgsql;
2362 select namedparmcursor_test7();
2363 ERROR: division by zero
2364 CONTEXT: SQL expression "42/0 AS p1, 77 AS p2"
2365 PL/pgSQL function namedparmcursor_test7() line 6 at OPEN
2366 -- check that line comments work correctly within the argument list (there
2367 -- is some special handling of this case in the code: the newline after the
2368 -- comment must be preserved when the argument-evaluating query is
2369 -- constructed, otherwise the comment effectively comments out the next
2370 -- argument, too)
2371 create function namedparmcursor_test8() returns int4 as $$
2372 declare
2373 c1 cursor (p1 int, p2 int) for
2374 select count(*) from tenk1 where thousand = p1 and tenthous = p2;
2375 n int4;
2376 begin
2377 open c1 (77 -- test
2378 , 42);
2379 fetch c1 into n;
2380 return n;
2381 end $$ language plpgsql;
2382 select namedparmcursor_test8();
2383 namedparmcursor_test8
2384 -----------------------
2385 0
2386 (1 row)
2388 -- cursor parameter name can match plpgsql variable or unreserved keyword
2389 create function namedparmcursor_test9(p1 int) returns int4 as $$
2390 declare
2391 c1 cursor (p1 int, p2 int, debug int) for
2392 select count(*) from tenk1 where thousand = p1 and tenthous = p2
2393 and four = debug;
2394 p2 int4 := 1006;
2395 n int4;
2396 begin
2397 open c1 (p1 := p1, p2 := p2, debug := 2);
2398 fetch c1 into n;
2399 return n;
2400 end $$ language plpgsql;
2401 select namedparmcursor_test9(6);
2402 namedparmcursor_test9
2403 -----------------------
2404 1
2405 (1 row)
2407 --
2408 -- tests for "raise" processing
2409 --
2410 create function raise_test1(int) returns int as $$
2411 begin
2412 raise notice 'This message has too many parameters!', $1;
2413 return $1;
2414 end;
2415 $$ language plpgsql;
2416 ERROR: too many parameters specified for RAISE
2417 CONTEXT: compilation of PL/pgSQL function "raise_test1" near line 3
2418 create function raise_test2(int) returns int as $$
2419 begin
2420 raise notice 'This message has too few parameters: %, %, %', $1, $1;
2421 return $1;
2422 end;
2423 $$ language plpgsql;
2424 ERROR: too few parameters specified for RAISE
2425 CONTEXT: compilation of PL/pgSQL function "raise_test2" near line 3
2426 create function raise_test3(int) returns int as $$
2427 begin
2428 raise notice 'This message has no parameters (despite having %% signs in it)!';
2429 return $1;
2430 end;
2431 $$ language plpgsql;
2432 select raise_test3(1);
2433 NOTICE: This message has no parameters (despite having % signs in it)!
2434 raise_test3
2435 -------------
2436 1
2437 (1 row)
2439 -- Test re-RAISE inside a nested exception block. This case is allowed
2440 -- by Oracle's PL/SQL but was handled differently by PG before 9.1.
2441 CREATE FUNCTION reraise_test() RETURNS void AS $$
2442 BEGIN
2443 BEGIN
2444 RAISE syntax_error;
2445 EXCEPTION
2446 WHEN syntax_error THEN
2447 BEGIN
2448 raise notice 'exception % thrown in inner block, reraising', sqlerrm;
2449 RAISE;
2450 EXCEPTION
2451 WHEN OTHERS THEN
2452 raise notice 'RIGHT - exception % caught in inner block', sqlerrm;
2453 END;
2454 END;
2455 EXCEPTION
2456 WHEN OTHERS THEN
2457 raise notice 'WRONG - exception % caught in outer block', sqlerrm;
2458 END;
2459 $$ LANGUAGE plpgsql;
2460 SELECT reraise_test();
2461 NOTICE: exception syntax_error thrown in inner block, reraising
2462 NOTICE: RIGHT - exception syntax_error caught in inner block
2463 reraise_test
2464 --------------
2466 (1 row)
2468 --
2469 -- reject function definitions that contain malformed SQL queries at
2470 -- compile-time, where possible
2471 --
2472 create function bad_sql1() returns int as $$
2473 declare a int;
2474 begin
2475 a := 5;
2476 Johnny Yuma;
2477 a := 10;
2478 return a;
2479 end$$ language plpgsql;
2480 ERROR: syntax error at or near "Johnny"
2481 LINE 5: Johnny Yuma;
2482 ^
2483 create function bad_sql2() returns int as $$
2484 declare r record;
2485 begin
2486 for r in select I fought the law, the law won LOOP
2487 raise notice 'in loop';
2488 end loop;
2489 return 5;
2490 end;$$ language plpgsql;
2491 ERROR: syntax error at or near "the"
2492 LINE 4: for r in select I fought the law, the law won LOOP
2493 ^
2494 -- a RETURN expression is mandatory, except for void-returning
2495 -- functions, where it is not allowed
2496 create function missing_return_expr() returns int as $$
2497 begin
2498 return ;
2499 end;$$ language plpgsql;
2500 ERROR: missing expression at or near ";"
2501 LINE 3: return ;
2502 ^
2503 create function void_return_expr() returns void as $$
2504 begin
2505 return 5;
2506 end;$$ language plpgsql;
2507 ERROR: RETURN cannot have a parameter in function returning void
2508 LINE 3: return 5;
2509 ^
2510 -- VOID functions are allowed to omit RETURN
2511 create function void_return_expr() returns void as $$
2512 begin
2513 perform 2+2;
2514 end;$$ language plpgsql;
2515 select void_return_expr();
2516 void_return_expr
2517 ------------------
2519 (1 row)
2521 -- but ordinary functions are not
2522 create function missing_return_expr() returns int as $$
2523 begin
2524 perform 2+2;
2525 end;$$ language plpgsql;
2526 select missing_return_expr();
2527 ERROR: control reached end of function without RETURN
2528 CONTEXT: PL/pgSQL function missing_return_expr()
2529 drop function void_return_expr();
2530 drop function missing_return_expr();
2531 --
2532 -- EXECUTE ... INTO test
2533 --
2534 create table eifoo (i integer, y integer);
2535 create type eitype as (i integer, y integer);
2536 create or replace function execute_into_test(varchar) returns record as $$
2537 declare
2538 _r record;
2539 _rt eifoo%rowtype;
2540 _v eitype;
2541 i int;
2542 j int;
2543 k int;
2544 begin
2545 execute 'insert into '||$1||' values(10,15)';
2546 execute 'select (row).* from (select row(10,1)::eifoo) s' into _r;
2547 raise notice '% %', _r.i, _r.y;
2548 execute 'select * from '||$1||' limit 1' into _rt;
2549 raise notice '% %', _rt.i, _rt.y;
2550 execute 'select *, 20 from '||$1||' limit 1' into i, j, k;
2551 raise notice '% % %', i, j, k;
2552 execute 'select 1,2' into _v;
2553 return _v;
2554 end; $$ language plpgsql;
2555 select execute_into_test('eifoo');
2556 NOTICE: 10 1
2557 NOTICE: 10 15
2558 NOTICE: 10 15 20
2559 execute_into_test
2560 -------------------
2561 (1,2)
2562 (1 row)
2564 drop table eifoo cascade;
2565 drop type eitype cascade;
2566 --
2567 -- SQLSTATE and SQLERRM test
2568 --
2569 create function excpt_test1() returns void as $$
2570 begin
2571 raise notice '% %', sqlstate, sqlerrm;
2572 end; $$ language plpgsql;
2573 -- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
2574 -- blocks
2575 select excpt_test1();
2576 ERROR: column "sqlstate" does not exist
2577 LINE 1: sqlstate
2578 ^
2579 QUERY: sqlstate
2580 CONTEXT: PL/pgSQL function excpt_test1() line 3 at RAISE
2581 create function excpt_test2() returns void as $$
2582 begin
2583 begin
2584 begin
2585 raise notice '% %', sqlstate, sqlerrm;
2586 end;
2587 end;
2588 end; $$ language plpgsql;
2589 -- should fail
2590 select excpt_test2();
2591 ERROR: column "sqlstate" does not exist
2592 LINE 1: sqlstate
2593 ^
2594 QUERY: sqlstate
2595 CONTEXT: PL/pgSQL function excpt_test2() line 5 at RAISE
2596 create function excpt_test3() returns void as $$
2597 begin
2598 begin
2599 raise exception 'user exception';
2600 exception when others then
2601 raise notice 'caught exception % %', sqlstate, sqlerrm;
2602 begin
2603 raise notice '% %', sqlstate, sqlerrm;
2604 perform 10/0;
2605 exception
2606 when substring_error then
2607 -- this exception handler shouldn't be invoked
2608 raise notice 'unexpected exception: % %', sqlstate, sqlerrm;
2609 when division_by_zero then
2610 raise notice 'caught exception % %', sqlstate, sqlerrm;
2611 end;
2612 raise notice '% %', sqlstate, sqlerrm;
2613 end;
2614 end; $$ language plpgsql;
2615 select excpt_test3();
2616 NOTICE: caught exception P0001 user exception
2617 NOTICE: P0001 user exception
2618 NOTICE: caught exception 22012 division by zero
2619 NOTICE: P0001 user exception
2620 excpt_test3
2621 -------------
2623 (1 row)
2625 create function excpt_test4() returns text as $$
2626 begin
2627 begin perform 1/0;
2628 exception when others then return sqlerrm; end;
2629 end; $$ language plpgsql;
2630 select excpt_test4();
2631 excpt_test4
2632 ------------------
2633 division by zero
2634 (1 row)
2636 drop function excpt_test1();
2637 drop function excpt_test2();
2638 drop function excpt_test3();
2639 drop function excpt_test4();
2640 -- parameters of raise stmt can be expressions
2641 create function raise_exprs() returns void as $$
2642 declare
2643 a integer[] = '{10,20,30}';
2644 c varchar = 'xyz';
2645 i integer;
2646 begin
2647 i := 2;
2648 raise notice '%; %; %; %; %; %', a, a[i], c, (select c || 'abc'), row(10,'aaa',NULL,30), NULL;
2649 end;$$ language plpgsql;
2650 select raise_exprs();
2651 NOTICE: {10,20,30}; 20; xyz; xyzabc; (10,aaa,,30); <NULL>
2652 raise_exprs
2653 -------------
2655 (1 row)
2657 drop function raise_exprs();
2658 -- regression test: verify that multiple uses of same plpgsql datum within
2659 -- a SQL command all get mapped to the same $n parameter. The return value
2660 -- of the SELECT is not important, we only care that it doesn't fail with
2661 -- a complaint about an ungrouped column reference.
2662 create function multi_datum_use(p1 int) returns bool as $$
2663 declare
2664 x int;
2665 y int;
2666 begin
2667 select into x,y unique1/p1, unique1/$1 from tenk1 group by unique1/p1;
2668 return x = y;
2669 end$$ language plpgsql;
2670 select multi_datum_use(42);
2671 multi_datum_use
2672 -----------------
2673 t
2674 (1 row)
2676 --
2677 -- Test STRICT limiter in both planned and EXECUTE invocations.
2678 -- Note that a data-modifying query is quasi strict (disallow multi rows)
2679 -- by default in the planned case, but not in EXECUTE.
2680 --
2681 create temp table foo (f1 int, f2 int);
2682 insert into foo values (1,2), (3,4);
2683 create or replace function stricttest() returns void as $$
2684 declare x record;
2685 begin
2686 -- should work
2687 insert into foo values(5,6) returning * into x;
2688 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2689 end$$ language plpgsql;
2690 select stricttest();
2691 NOTICE: x.f1 = 5, x.f2 = 6
2692 stricttest
2693 ------------
2695 (1 row)
2697 create or replace function stricttest() returns void as $$
2698 declare x record;
2699 begin
2700 -- should fail due to implicit strict
2701 insert into foo values(7,8),(9,10) returning * into x;
2702 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2703 end$$ language plpgsql;
2704 select stricttest();
2705 ERROR: query returned more than one row
2706 HINT: Make sure the query returns a single row, or use LIMIT 1.
2707 CONTEXT: PL/pgSQL function stricttest() line 5 at SQL statement
2708 create or replace function stricttest() returns void as $$
2709 declare x record;
2710 begin
2711 -- should work
2712 execute 'insert into foo values(5,6) returning *' into x;
2713 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2714 end$$ language plpgsql;
2715 select stricttest();
2716 NOTICE: x.f1 = 5, x.f2 = 6
2717 stricttest
2718 ------------
2720 (1 row)
2722 create or replace function stricttest() returns void as $$
2723 declare x record;
2724 begin
2725 -- this should work since EXECUTE isn't as picky
2726 execute 'insert into foo values(7,8),(9,10) returning *' into x;
2727 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2728 end$$ language plpgsql;
2729 select stricttest();
2730 NOTICE: x.f1 = 7, x.f2 = 8
2731 stricttest
2732 ------------
2734 (1 row)
2736 select * from foo;
2737 f1 | f2
2738 ----+----
2739 1 | 2
2740 3 | 4
2741 5 | 6
2742 5 | 6
2743 7 | 8
2744 9 | 10
2745 (6 rows)
2747 create or replace function stricttest() returns void as $$
2748 declare x record;
2749 begin
2750 -- should work
2751 select * from foo where f1 = 3 into strict x;
2752 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2753 end$$ language plpgsql;
2754 select stricttest();
2755 NOTICE: x.f1 = 3, x.f2 = 4
2756 stricttest
2757 ------------
2759 (1 row)
2761 create or replace function stricttest() returns void as $$
2762 declare x record;
2763 begin
2764 -- should fail, no rows
2765 select * from foo where f1 = 0 into strict x;
2766 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2767 end$$ language plpgsql;
2768 select stricttest();
2769 ERROR: query returned no rows
2770 CONTEXT: PL/pgSQL function stricttest() line 5 at SQL statement
2771 create or replace function stricttest() returns void as $$
2772 declare x record;
2773 begin
2774 -- should fail, too many rows
2775 select * from foo where f1 > 3 into strict x;
2776 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2777 end$$ language plpgsql;
2778 select stricttest();
2779 ERROR: query returned more than one row
2780 HINT: Make sure the query returns a single row, or use LIMIT 1.
2781 CONTEXT: PL/pgSQL function stricttest() line 5 at SQL statement
2782 create or replace function stricttest() returns void as $$
2783 declare x record;
2784 begin
2785 -- should work
2786 execute 'select * from foo where f1 = 3' into strict x;
2787 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2788 end$$ language plpgsql;
2789 select stricttest();
2790 NOTICE: x.f1 = 3, x.f2 = 4
2791 stricttest
2792 ------------
2794 (1 row)
2796 create or replace function stricttest() returns void as $$
2797 declare x record;
2798 begin
2799 -- should fail, no rows
2800 execute 'select * from foo where f1 = 0' into strict x;
2801 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2802 end$$ language plpgsql;
2803 select stricttest();
2804 ERROR: query returned no rows
2805 CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE
2806 create or replace function stricttest() returns void as $$
2807 declare x record;
2808 begin
2809 -- should fail, too many rows
2810 execute 'select * from foo where f1 > 3' into strict x;
2811 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2812 end$$ language plpgsql;
2813 select stricttest();
2814 ERROR: query returned more than one row
2815 CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE
2816 drop function stricttest();
2817 -- test printing parameters after failure due to STRICT
2818 set plpgsql.print_strict_params to true;
2819 create or replace function stricttest() returns void as $$
2820 declare
2821 x record;
2822 p1 int := 2;
2823 p3 text := 'foo';
2824 begin
2825 -- no rows
2826 select * from foo where f1 = p1 and f1::text = p3 into strict x;
2827 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2828 end$$ language plpgsql;
2829 select stricttest();
2830 ERROR: query returned no rows
2831 DETAIL: parameters: p1 = '2', p3 = 'foo'
2832 CONTEXT: PL/pgSQL function stricttest() line 8 at SQL statement
2833 create or replace function stricttest() returns void as $$
2834 declare
2835 x record;
2836 p1 int := 2;
2837 p3 text := $a$'Valame Dios!' dijo Sancho; 'no le dije yo a vuestra merced que mirase bien lo que hacia?'$a$;
2838 begin
2839 -- no rows
2840 select * from foo where f1 = p1 and f1::text = p3 into strict x;
2841 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2842 end$$ language plpgsql;
2843 select stricttest();
2844 ERROR: query returned no rows
2845 DETAIL: parameters: p1 = '2', p3 = '''Valame Dios!'' dijo Sancho; ''no le dije yo a vuestra merced que mirase bien lo que hacia?'''
2846 CONTEXT: PL/pgSQL function stricttest() line 8 at SQL statement
2847 create or replace function stricttest() returns void as $$
2848 declare
2849 x record;
2850 p1 int := 2;
2851 p3 text := 'foo';
2852 begin
2853 -- too many rows
2854 select * from foo where f1 > p1 or f1::text = p3 into strict x;
2855 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2856 end$$ language plpgsql;
2857 select stricttest();
2858 ERROR: query returned more than one row
2859 DETAIL: parameters: p1 = '2', p3 = 'foo'
2860 HINT: Make sure the query returns a single row, or use LIMIT 1.
2861 CONTEXT: PL/pgSQL function stricttest() line 8 at SQL statement
2862 create or replace function stricttest() returns void as $$
2863 declare x record;
2864 begin
2865 -- too many rows, no params
2866 select * from foo where f1 > 3 into strict x;
2867 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2868 end$$ language plpgsql;
2869 select stricttest();
2870 ERROR: query returned more than one row
2871 HINT: Make sure the query returns a single row, or use LIMIT 1.
2872 CONTEXT: PL/pgSQL function stricttest() line 5 at SQL statement
2873 create or replace function stricttest() returns void as $$
2874 declare x record;
2875 begin
2876 -- no rows
2877 execute 'select * from foo where f1 = $1 or f1::text = $2' using 0, 'foo' into strict x;
2878 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2879 end$$ language plpgsql;
2880 select stricttest();
2881 ERROR: query returned no rows
2882 DETAIL: parameters: $1 = '0', $2 = 'foo'
2883 CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE
2884 create or replace function stricttest() returns void as $$
2885 declare x record;
2886 begin
2887 -- too many rows
2888 execute 'select * from foo where f1 > $1' using 1 into strict x;
2889 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2890 end$$ language plpgsql;
2891 select stricttest();
2892 ERROR: query returned more than one row
2893 DETAIL: parameters: $1 = '1'
2894 CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE
2895 create or replace function stricttest() returns void as $$
2896 declare x record;
2897 begin
2898 -- too many rows, no parameters
2899 execute 'select * from foo where f1 > 3' into strict x;
2900 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2901 end$$ language plpgsql;
2902 select stricttest();
2903 ERROR: query returned more than one row
2904 CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE
2905 create or replace function stricttest() returns void as $$
2906 -- override the global
2907 #print_strict_params off
2908 declare
2909 x record;
2910 p1 int := 2;
2911 p3 text := 'foo';
2912 begin
2913 -- too many rows
2914 select * from foo where f1 > p1 or f1::text = p3 into strict x;
2915 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2916 end$$ language plpgsql;
2917 select stricttest();
2918 ERROR: query returned more than one row
2919 HINT: Make sure the query returns a single row, or use LIMIT 1.
2920 CONTEXT: PL/pgSQL function stricttest() line 10 at SQL statement
2921 reset plpgsql.print_strict_params;
2922 create or replace function stricttest() returns void as $$
2923 -- override the global
2924 #print_strict_params on
2925 declare
2926 x record;
2927 p1 int := 2;
2928 p3 text := 'foo';
2929 begin
2930 -- too many rows
2931 select * from foo where f1 > p1 or f1::text = p3 into strict x;
2932 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2933 end$$ language plpgsql;
2934 select stricttest();
2935 ERROR: query returned more than one row
2936 DETAIL: parameters: p1 = '2', p3 = 'foo'
2937 HINT: Make sure the query returns a single row, or use LIMIT 1.
2938 CONTEXT: PL/pgSQL function stricttest() line 10 at SQL statement
2939 -- test warnings and errors
2940 set plpgsql.extra_warnings to 'all';
2941 set plpgsql.extra_warnings to 'none';
2942 set plpgsql.extra_errors to 'all';
2943 set plpgsql.extra_errors to 'none';
2944 -- test warnings when shadowing a variable
2945 set plpgsql.extra_warnings to 'shadowed_variables';
2946 -- simple shadowing of input and output parameters
2947 create or replace function shadowtest(in1 int)
2948 returns table (out1 int) as $$
2949 declare
2950 in1 int;
2951 out1 int;
2952 begin
2953 end
2954 $$ language plpgsql;
2955 WARNING: variable "in1" shadows a previously defined variable
2956 LINE 4: in1 int;
2957 ^
2958 WARNING: variable "out1" shadows a previously defined variable
2959 LINE 5: out1 int;
2960 ^
2961 select shadowtest(1);
2962 shadowtest
2963 ------------
2964 (0 rows)
2966 set plpgsql.extra_warnings to 'shadowed_variables';
2967 select shadowtest(1);
2968 shadowtest
2969 ------------
2970 (0 rows)
2972 create or replace function shadowtest(in1 int)
2973 returns table (out1 int) as $$
2974 declare
2975 in1 int;
2976 out1 int;
2977 begin
2978 end
2979 $$ language plpgsql;
2980 WARNING: variable "in1" shadows a previously defined variable
2981 LINE 4: in1 int;
2982 ^
2983 WARNING: variable "out1" shadows a previously defined variable
2984 LINE 5: out1 int;
2985 ^
2986 select shadowtest(1);
2987 shadowtest
2988 ------------
2989 (0 rows)
2991 drop function shadowtest(int);
2992 -- shadowing in a second DECLARE block
2993 create or replace function shadowtest()
2994 returns void as $$
2995 declare
2996 f1 int;
2997 begin
2998 declare
2999 f1 int;
3000 begin
3001 end;
3002 end$$ language plpgsql;
3003 WARNING: variable "f1" shadows a previously defined variable
3004 LINE 7: f1 int;
3005 ^
3006 drop function shadowtest();
3007 -- several levels of shadowing
3008 create or replace function shadowtest(in1 int)
3009 returns void as $$
3010 declare
3011 in1 int;
3012 begin
3013 declare
3014 in1 int;
3015 begin
3016 end;
3017 end$$ language plpgsql;
3018 WARNING: variable "in1" shadows a previously defined variable
3019 LINE 4: in1 int;
3020 ^
3021 WARNING: variable "in1" shadows a previously defined variable
3022 LINE 7: in1 int;
3023 ^
3024 drop function shadowtest(int);
3025 -- shadowing in cursor definitions
3026 create or replace function shadowtest()
3027 returns void as $$
3028 declare
3029 f1 int;
3030 c1 cursor (f1 int) for select 1;
3031 begin
3032 end$$ language plpgsql;
3033 WARNING: variable "f1" shadows a previously defined variable
3034 LINE 5: c1 cursor (f1 int) for select 1;
3035 ^
3036 drop function shadowtest();
3037 -- test errors when shadowing a variable
3038 set plpgsql.extra_errors to 'shadowed_variables';
3039 create or replace function shadowtest(f1 int)
3040 returns boolean as $$
3041 declare f1 int; begin return 1; end $$ language plpgsql;
3042 ERROR: variable "f1" shadows a previously defined variable
3043 LINE 3: declare f1 int; begin return 1; end $$ language plpgsql;
3044 ^
3045 select shadowtest(1);
3046 ERROR: function shadowtest(integer) does not exist
3047 LINE 1: select shadowtest(1);
3048 ^
3049 HINT: No function matches the given name and argument types. You might need to add explicit type casts.
3050 reset plpgsql.extra_errors;
3051 reset plpgsql.extra_warnings;
3052 create or replace function shadowtest(f1 int)
3053 returns boolean as $$
3054 declare f1 int; begin return 1; end $$ language plpgsql;
3055 select shadowtest(1);
3056 shadowtest
3057 ------------
3058 t
3059 (1 row)
3061 -- runtime extra checks
3062 set plpgsql.extra_warnings to 'too_many_rows';
3063 do $$
3064 declare x int;
3065 begin
3066 select v from generate_series(1,2) g(v) into x;
3067 end;
3068 $$;
3069 WARNING: query returned more than one row
3070 HINT: Make sure the query returns a single row, or use LIMIT 1.
3071 set plpgsql.extra_errors to 'too_many_rows';
3072 do $$
3073 declare x int;
3074 begin
3075 select v from generate_series(1,2) g(v) into x;
3076 end;
3077 $$;
3078 ERROR: query returned more than one row
3079 HINT: Make sure the query returns a single row, or use LIMIT 1.
3080 CONTEXT: PL/pgSQL function inline_code_block line 4 at SQL statement
3081 reset plpgsql.extra_errors;
3082 reset plpgsql.extra_warnings;
3083 set plpgsql.extra_warnings to 'strict_multi_assignment';
3084 do $$
3085 declare
3086 x int;
3087 y int;
3088 begin
3089 select 1 into x, y;
3090 select 1,2 into x, y;
3091 select 1,2,3 into x, y;
3092 end
3093 $$;
3094 WARNING: number of source and target fields in assignment does not match
3095 DETAIL: strict_multi_assignment check of extra_warnings is active.
3096 HINT: Make sure the query returns the exact list of columns.
3097 WARNING: number of source and target fields in assignment does not match
3098 DETAIL: strict_multi_assignment check of extra_warnings is active.
3099 HINT: Make sure the query returns the exact list of columns.
3100 set plpgsql.extra_errors to 'strict_multi_assignment';
3101 do $$
3102 declare
3103 x int;
3104 y int;
3105 begin
3106 select 1 into x, y;
3107 select 1,2 into x, y;
3108 select 1,2,3 into x, y;
3109 end
3110 $$;
3111 ERROR: number of source and target fields in assignment does not match
3112 DETAIL: strict_multi_assignment check of extra_errors is active.
3113 HINT: Make sure the query returns the exact list of columns.
3114 CONTEXT: PL/pgSQL function inline_code_block line 6 at SQL statement
3115 create table test_01(a int, b int, c int);
3116 alter table test_01 drop column a;
3117 -- the check is active only when source table is not empty
3118 insert into test_01 values(10,20);
3119 do $$
3120 declare
3121 x int;
3122 y int;
3123 begin
3124 select * from test_01 into x, y; -- should be ok
3125 raise notice 'ok';
3126 select * from test_01 into x; -- should to fail
3127 end;
3128 $$;
3129 NOTICE: ok
3130 ERROR: number of source and target fields in assignment does not match
3131 DETAIL: strict_multi_assignment check of extra_errors is active.
3132 HINT: Make sure the query returns the exact list of columns.
3133 CONTEXT: PL/pgSQL function inline_code_block line 8 at SQL statement
3134 do $$
3135 declare
3136 t test_01;
3137 begin
3138 select 1, 2 into t; -- should be ok
3139 raise notice 'ok';
3140 select 1, 2, 3 into t; -- should fail;
3141 end;
3142 $$;
3143 NOTICE: ok
3144 ERROR: number of source and target fields in assignment does not match
3145 DETAIL: strict_multi_assignment check of extra_errors is active.
3146 HINT: Make sure the query returns the exact list of columns.
3147 CONTEXT: PL/pgSQL function inline_code_block line 7 at SQL statement
3148 do $$
3149 declare
3150 t test_01;
3151 begin
3152 select 1 into t; -- should fail;
3153 end;
3154 $$;
3155 ERROR: number of source and target fields in assignment does not match
3156 DETAIL: strict_multi_assignment check of extra_errors is active.
3157 HINT: Make sure the query returns the exact list of columns.
3158 CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement
3159 drop table test_01;
3160 reset plpgsql.extra_errors;
3161 reset plpgsql.extra_warnings;
3162 -- test scrollable cursor support
3163 create function sc_test() returns setof integer as $$
3164 declare
3165 c scroll cursor for select f1 from int4_tbl;
3166 x integer;
3167 begin
3168 open c;
3169 fetch last from c into x;
3170 while found loop
3171 return next x;
3172 fetch prior from c into x;
3173 end loop;
3174 close c;
3175 end;
3176 $$ language plpgsql;
3177 select * from sc_test();
3178 sc_test
3179 -------------
3180 -2147483647
3181 2147483647
3182 -123456
3183 123456
3184 0
3185 (5 rows)
3187 create or replace function sc_test() returns setof integer as $$
3188 declare
3189 c no scroll cursor for select f1 from int4_tbl;
3190 x integer;
3191 begin
3192 open c;
3193 fetch last from c into x;
3194 while found loop
3195 return next x;
3196 fetch prior from c into x;
3197 end loop;
3198 close c;
3199 end;
3200 $$ language plpgsql;
3201 select * from sc_test(); -- fails because of NO SCROLL specification
3202 ERROR: cursor can only scan forward
3203 HINT: Declare it with SCROLL option to enable backward scan.
3204 CONTEXT: PL/pgSQL function sc_test() line 7 at FETCH
3205 create or replace function sc_test() returns setof integer as $$
3206 declare
3207 c refcursor;
3208 x integer;
3209 begin
3210 open c scroll for select f1 from int4_tbl;
3211 fetch last from c into x;
3212 while found loop
3213 return next x;
3214 fetch prior from c into x;
3215 end loop;
3216 close c;
3217 end;
3218 $$ language plpgsql;
3219 select * from sc_test();
3220 sc_test
3221 -------------
3222 -2147483647
3223 2147483647
3224 -123456
3225 123456
3226 0
3227 (5 rows)
3229 create or replace function sc_test() returns setof integer as $$
3230 declare
3231 c refcursor;
3232 x integer;
3233 begin
3234 open c scroll for execute 'select f1 from int4_tbl';
3235 fetch last from c into x;
3236 while found loop
3237 return next x;
3238 fetch relative -2 from c into x;
3239 end loop;
3240 close c;
3241 end;
3242 $$ language plpgsql;
3243 select * from sc_test();
3244 sc_test
3245 -------------
3246 -2147483647
3247 -123456
3248 0
3249 (3 rows)
3251 create or replace function sc_test() returns setof integer as $$
3252 declare
3253 c refcursor;
3254 x integer;
3255 begin
3256 open c scroll for execute 'select f1 from int4_tbl';
3257 fetch last from c into x;
3258 while found loop
3259 return next x;
3260 move backward 2 from c;
3261 fetch relative -1 from c into x;
3262 end loop;
3263 close c;
3264 end;
3265 $$ language plpgsql;
3266 select * from sc_test();
3267 sc_test
3268 -------------
3269 -2147483647
3270 123456
3271 (2 rows)
3273 create or replace function sc_test() returns setof integer as $$
3274 declare
3275 c cursor for select * from generate_series(1, 10);
3276 x integer;
3277 begin
3278 open c;
3279 loop
3280 move relative 2 in c;
3281 if not found then
3282 exit;
3283 end if;
3284 fetch next from c into x;
3285 if found then
3286 return next x;
3287 end if;
3288 end loop;
3289 close c;
3290 end;
3291 $$ language plpgsql;
3292 select * from sc_test();
3293 sc_test
3294 ---------
3295 3
3296 6
3297 9
3298 (3 rows)
3300 create or replace function sc_test() returns setof integer as $$
3301 declare
3302 c cursor for select * from generate_series(1, 10);
3303 x integer;
3304 begin
3305 open c;
3306 move forward all in c;
3307 fetch backward from c into x;
3308 if found then
3309 return next x;
3310 end if;
3311 close c;
3312 end;
3313 $$ language plpgsql;
3314 select * from sc_test();
3315 sc_test
3316 ---------
3317 10
3318 (1 row)
3320 drop function sc_test();
3321 -- test qualified variable names
3322 create function pl_qual_names (param1 int) returns void as $$
3323 <<outerblock>>
3324 declare
3325 param1 int := 1;
3326 begin
3327 <<innerblock>>
3328 declare
3329 param1 int := 2;
3330 begin
3331 raise notice 'param1 = %', param1;
3332 raise notice 'pl_qual_names.param1 = %', pl_qual_names.param1;
3333 raise notice 'outerblock.param1 = %', outerblock.param1;
3334 raise notice 'innerblock.param1 = %', innerblock.param1;
3335 end;
3336 end;
3337 $$ language plpgsql;
3338 select pl_qual_names(42);
3339 NOTICE: param1 = 2
3340 NOTICE: pl_qual_names.param1 = 42
3341 NOTICE: outerblock.param1 = 1
3342 NOTICE: innerblock.param1 = 2
3343 pl_qual_names
3344 ---------------
3346 (1 row)
3348 drop function pl_qual_names(int);
3349 -- tests for RETURN QUERY
3350 create function ret_query1(out int, out int) returns setof record as $$
3351 begin
3352 $1 := -1;
3353 $2 := -2;
3354 return next;
3355 return query select x + 1, x * 10 from generate_series(0, 10) s (x);
3356 return next;
3357 end;
3358 $$ language plpgsql;
3359 select * from ret_query1();
3360 column1 | column2
3361 ---------+---------
3362 -1 | -2
3363 1 | 0
3364 2 | 10
3365 3 | 20
3366 4 | 30
3367 5 | 40
3368 6 | 50
3369 7 | 60
3370 8 | 70
3371 9 | 80
3372 10 | 90
3373 11 | 100
3374 -1 | -2
3375 (13 rows)
3377 create type record_type as (x text, y int, z boolean);
3378 create or replace function ret_query2(lim int) returns setof record_type as $$
3379 begin
3380 return query select md5(s.x::text), s.x, s.x > 0
3381 from generate_series(-8, lim) s (x) where s.x % 2 = 0;
3382 end;
3383 $$ language plpgsql;
3384 select * from ret_query2(8);
3385 x | y | z
3386 ----------------------------------+----+---
3387 a8d2ec85eaf98407310b72eb73dda247 | -8 | f
3388 596a3d04481816330f07e4f97510c28f | -6 | f
3389 0267aaf632e87a63288a08331f22c7c3 | -4 | f
3390 5d7b9adcbe1c629ec722529dd12e5129 | -2 | f
3391 cfcd208495d565ef66e7dff9f98764da | 0 | f
3392 c81e728d9d4c2f636f067f89cc14862c | 2 | t
3393 a87ff679a2f3e71d9181a67b7542122c | 4 | t
3394 1679091c5a880faf6fb5e6087eb1b2dc | 6 | t
3395 c9f0f895fb98ab9159f51fd0297e236d | 8 | t
3396 (9 rows)
3398 -- test EXECUTE USING
3399 create function exc_using(int, text) returns int as $$
3400 declare i int;
3401 begin
3402 for i in execute 'select * from generate_series(1,$1)' using $1+1 loop
3403 raise notice '%', i;
3404 end loop;
3405 execute 'select $2 + $2*3 + length($1)' into i using $2,$1;
3406 return i;
3407 end
3408 $$ language plpgsql;
3409 select exc_using(5, 'foobar');
3410 NOTICE: 1
3411 NOTICE: 2
3412 NOTICE: 3
3413 NOTICE: 4
3414 NOTICE: 5
3415 NOTICE: 6
3416 exc_using
3417 -----------
3418 26
3419 (1 row)
3421 drop function exc_using(int, text);
3422 create or replace function exc_using(int) returns void as $$
3423 declare
3424 c refcursor;
3425 i int;
3426 begin
3427 open c for execute 'select * from generate_series(1,$1)' using $1+1;
3428 loop
3429 fetch c into i;
3430 exit when not found;
3431 raise notice '%', i;
3432 end loop;
3433 close c;
3434 return;
3435 end;
3436 $$ language plpgsql;
3437 select exc_using(5);
3438 NOTICE: 1
3439 NOTICE: 2
3440 NOTICE: 3
3441 NOTICE: 4
3442 NOTICE: 5
3443 NOTICE: 6
3444 exc_using
3445 -----------
3447 (1 row)
3449 drop function exc_using(int);
3450 -- test FOR-over-cursor
3451 create or replace function forc01() returns void as $$
3452 declare
3453 c cursor(r1 integer, r2 integer)
3454 for select * from generate_series(r1,r2) i;
3455 c2 cursor
3456 for select * from generate_series(41,43) i;
3457 begin
3458 for r in c(5,7) loop
3459 raise notice '% from %', r.i, c;
3460 end loop;
3461 -- again, to test if cursor was closed properly
3462 for r in c(9,10) loop
3463 raise notice '% from %', r.i, c;
3464 end loop;
3465 -- and test a parameterless cursor
3466 for r in c2 loop
3467 raise notice '% from %', r.i, c2;
3468 end loop;
3469 -- and try it with a hand-assigned name
3470 raise notice 'after loop, c2 = %', c2;
3471 c2 := 'special_name';
3472 for r in c2 loop
3473 raise notice '% from %', r.i, c2;
3474 end loop;
3475 raise notice 'after loop, c2 = %', c2;
3476 -- and try it with a generated name
3477 -- (which we can't show in the output because it's variable)
3478 c2 := null;
3479 for r in c2 loop
3480 raise notice '%', r.i;
3481 end loop;
3482 raise notice 'after loop, c2 = %', c2;
3483 return;
3484 end;
3485 $$ language plpgsql;
3486 select forc01();
3487 NOTICE: 5 from c
3488 NOTICE: 6 from c
3489 NOTICE: 7 from c
3490 NOTICE: 9 from c
3491 NOTICE: 10 from c
3492 NOTICE: 41 from c2
3493 NOTICE: 42 from c2
3494 NOTICE: 43 from c2
3495 NOTICE: after loop, c2 = c2
3496 NOTICE: 41 from special_name
3497 NOTICE: 42 from special_name
3498 NOTICE: 43 from special_name
3499 NOTICE: after loop, c2 = special_name
3500 NOTICE: 41
3501 NOTICE: 42
3502 NOTICE: 43
3503 NOTICE: after loop, c2 = <NULL>
3504 forc01
3505 --------
3507 (1 row)
3509 -- try updating the cursor's current row
3510 create temp table forc_test as
3511 select n as i, n as j from generate_series(1,10) n;
3512 create or replace function forc01() returns void as $$
3513 declare
3514 c cursor for select * from forc_test;
3515 begin
3516 for r in c loop
3517 raise notice '%, %', r.i, r.j;
3518 update forc_test set i = i * 100, j = r.j * 2 where current of c;
3519 end loop;
3520 end;
3521 $$ language plpgsql;
3522 select forc01();
3523 NOTICE: 1, 1
3524 NOTICE: 2, 2
3525 NOTICE: 3, 3
3526 NOTICE: 4, 4
3527 NOTICE: 5, 5
3528 NOTICE: 6, 6
3529 NOTICE: 7, 7
3530 NOTICE: 8, 8
3531 NOTICE: 9, 9
3532 NOTICE: 10, 10
3533 forc01
3534 --------
3536 (1 row)
3538 select * from forc_test;
3539 i | j
3540 ------+----
3541 100 | 2
3542 200 | 4
3543 300 | 6
3544 400 | 8
3545 500 | 10
3546 600 | 12
3547 700 | 14
3548 800 | 16
3549 900 | 18
3550 1000 | 20
3551 (10 rows)
3553 -- same, with a cursor whose portal name doesn't match variable name
3554 create or replace function forc01() returns void as $$
3555 declare
3556 c refcursor := 'fooled_ya';
3557 r record;
3558 begin
3559 open c for select * from forc_test;
3560 loop
3561 fetch c into r;
3562 exit when not found;
3563 raise notice '%, %', r.i, r.j;
3564 update forc_test set i = i * 100, j = r.j * 2 where current of c;
3565 end loop;
3566 end;
3567 $$ language plpgsql;
3568 select forc01();
3569 NOTICE: 100, 2
3570 NOTICE: 200, 4
3571 NOTICE: 300, 6
3572 NOTICE: 400, 8
3573 NOTICE: 500, 10
3574 NOTICE: 600, 12
3575 NOTICE: 700, 14
3576 NOTICE: 800, 16
3577 NOTICE: 900, 18
3578 NOTICE: 1000, 20
3579 forc01
3580 --------
3582 (1 row)
3584 select * from forc_test;
3585 i | j
3586 --------+----
3587 10000 | 4
3588 20000 | 8
3589 30000 | 12
3590 40000 | 16
3591 50000 | 20
3592 60000 | 24
3593 70000 | 28
3594 80000 | 32
3595 90000 | 36
3596 100000 | 40
3597 (10 rows)
3599 drop function forc01();
3600 -- fail because cursor has no query bound to it
3601 create or replace function forc_bad() returns void as $$
3602 declare
3603 c refcursor;
3604 begin
3605 for r in c loop
3606 raise notice '%', r.i;
3607 end loop;
3608 end;
3609 $$ language plpgsql;
3610 ERROR: cursor FOR loop must use a bound cursor variable
3611 LINE 5: for r in c loop
3612 ^
3613 -- test RETURN QUERY EXECUTE
3614 create or replace function return_dquery()
3615 returns setof int as $$
3616 begin
3617 return query execute 'select * from (values(10),(20)) f';
3618 return query execute 'select * from (values($1),($2)) f' using 40,50;
3619 end;
3620 $$ language plpgsql;
3621 select * from return_dquery();
3622 return_dquery
3623 ---------------
3624 10
3625 20
3626 40
3627 50
3628 (4 rows)
3630 drop function return_dquery();
3631 -- test RETURN QUERY with dropped columns
3632 create table tabwithcols(a int, b int, c int, d int);
3633 insert into tabwithcols values(10,20,30,40),(50,60,70,80);
3634 create or replace function returnqueryf()
3635 returns setof tabwithcols as $$
3636 begin
3637 return query select * from tabwithcols;
3638 return query execute 'select * from tabwithcols';
3639 end;
3640 $$ language plpgsql;
3641 select * from returnqueryf();
3642 a | b | c | d
3643 ----+----+----+----
3644 10 | 20 | 30 | 40
3645 50 | 60 | 70 | 80
3646 10 | 20 | 30 | 40
3647 50 | 60 | 70 | 80
3648 (4 rows)
3650 alter table tabwithcols drop column b;
3651 select * from returnqueryf();
3652 a | c | d
3653 ----+----+----
3654 10 | 30 | 40
3655 50 | 70 | 80
3656 10 | 30 | 40
3657 50 | 70 | 80
3658 (4 rows)
3660 alter table tabwithcols drop column d;
3661 select * from returnqueryf();
3662 a | c
3663 ----+----
3664 10 | 30
3665 50 | 70
3666 10 | 30
3667 50 | 70
3668 (4 rows)
3670 alter table tabwithcols add column d int;
3671 select * from returnqueryf();
3672 a | c | d
3673 ----+----+---
3674 10 | 30 |
3675 50 | 70 |
3676 10 | 30 |
3677 50 | 70 |
3678 (4 rows)
3680 drop function returnqueryf();
3681 drop table tabwithcols;
3682 --
3683 -- Tests for composite-type results
3684 --
3685 create type compostype as (x int, y varchar);
3686 -- test: use of variable of composite type in return statement
3687 create or replace function compos() returns compostype as $$
3688 declare
3689 v compostype;
3690 begin
3691 v := (1, 'hello');
3692 return v;
3693 end;
3694 $$ language plpgsql;
3695 select compos();
3696 compos
3697 -----------
3698 (1,hello)
3699 (1 row)
3701 -- test: use of variable of record type in return statement
3702 create or replace function compos() returns compostype as $$
3703 declare
3704 v record;
3705 begin
3706 v := (1, 'hello'::varchar);
3707 return v;
3708 end;
3709 $$ language plpgsql;
3710 select compos();
3711 compos
3712 -----------
3713 (1,hello)
3714 (1 row)
3716 -- test: use of row expr in return statement
3717 create or replace function compos() returns compostype as $$
3718 begin
3719 return (1, 'hello'::varchar);
3720 end;
3721 $$ language plpgsql;
3722 select compos();
3723 compos
3724 -----------
3725 (1,hello)
3726 (1 row)
3728 -- this does not work currently (no implicit casting)
3729 create or replace function compos() returns compostype as $$
3730 begin
3731 return (1, 'hello');
3732 end;
3733 $$ language plpgsql;
3734 select compos();
3735 ERROR: returned record type does not match expected record type
3736 DETAIL: Returned type unknown does not match expected type character varying in column 2.
3737 CONTEXT: PL/pgSQL function compos() while casting return value to function's return type
3738 -- ... but this does
3739 create or replace function compos() returns compostype as $$
3740 begin
3741 return (1, 'hello')::compostype;
3742 end;
3743 $$ language plpgsql;
3744 select compos();
3745 compos
3746 -----------
3747 (1,hello)
3748 (1 row)
3750 drop function compos();
3751 -- test: return a row expr as record.
3752 create or replace function composrec() returns record as $$
3753 declare
3754 v record;
3755 begin
3756 v := (1, 'hello');
3757 return v;
3758 end;
3759 $$ language plpgsql;
3760 select composrec();
3761 composrec
3762 -----------
3763 (1,hello)
3764 (1 row)
3766 -- test: return row expr in return statement.
3767 create or replace function composrec() returns record as $$
3768 begin
3769 return (1, 'hello');
3770 end;
3771 $$ language plpgsql;
3772 select composrec();
3773 composrec
3774 -----------
3775 (1,hello)
3776 (1 row)
3778 drop function composrec();
3779 -- test: row expr in RETURN NEXT statement.
3780 create or replace function compos() returns setof compostype as $$
3781 begin
3782 for i in 1..3
3783 loop
3784 return next (1, 'hello'::varchar);
3785 end loop;
3786 return next null::compostype;
3787 return next (2, 'goodbye')::compostype;
3788 end;
3789 $$ language plpgsql;
3790 select * from compos();
3791 x | y
3792 ---+---------
3793 1 | hello
3794 1 | hello
3795 1 | hello
3796 |
3797 2 | goodbye
3798 (5 rows)
3800 drop function compos();
3801 -- test: use invalid expr in return statement.
3802 create or replace function compos() returns compostype as $$
3803 begin
3804 return 1 + 1;
3805 end;
3806 $$ language plpgsql;
3807 select compos();
3808 ERROR: cannot return non-composite value from function returning composite type
3809 CONTEXT: PL/pgSQL function compos() line 3 at RETURN
3810 -- RETURN variable is a different code path ...
3811 create or replace function compos() returns compostype as $$
3812 declare x int := 42;
3813 begin
3814 return x;
3815 end;
3816 $$ language plpgsql;
3817 select * from compos();
3818 ERROR: cannot return non-composite value from function returning composite type
3819 CONTEXT: PL/pgSQL function compos() line 4 at RETURN
3820 drop function compos();
3821 -- test: invalid use of composite variable in scalar-returning function
3822 create or replace function compos() returns int as $$
3823 declare
3824 v compostype;
3825 begin
3826 v := (1, 'hello');
3827 return v;
3828 end;
3829 $$ language plpgsql;
3830 select compos();
3831 ERROR: invalid input syntax for type integer: "(1,hello)"
3832 CONTEXT: PL/pgSQL function compos() while casting return value to function's return type
3833 -- test: invalid use of composite expression in scalar-returning function
3834 create or replace function compos() returns int as $$
3835 begin
3836 return (1, 'hello')::compostype;
3837 end;
3838 $$ language plpgsql;
3839 select compos();
3840 ERROR: invalid input syntax for type integer: "(1,hello)"
3841 CONTEXT: PL/pgSQL function compos() while casting return value to function's return type
3842 drop function compos();
3843 drop type compostype;
3844 --
3845 -- Tests for 8.4's new RAISE features
3846 --
3847 create or replace function raise_test() returns void as $$
3848 begin
3849 raise notice '% % %', 1, 2, 3
3850 using errcode = '55001', detail = 'some detail info', hint = 'some hint';
3851 raise '% % %', 1, 2, 3
3852 using errcode = 'division_by_zero', detail = 'some detail info';
3853 end;
3854 $$ language plpgsql;
3855 select raise_test();
3856 NOTICE: 1 2 3
3857 DETAIL: some detail info
3858 HINT: some hint
3859 ERROR: 1 2 3
3860 DETAIL: some detail info
3861 CONTEXT: PL/pgSQL function raise_test() line 5 at RAISE
3862 -- Since we can't actually see the thrown SQLSTATE in default psql output,
3863 -- test it like this; this also tests re-RAISE
3864 create or replace function raise_test() returns void as $$
3865 begin
3866 raise 'check me'
3867 using errcode = 'division_by_zero', detail = 'some detail info';
3868 exception
3869 when others then
3870 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3871 raise;
3872 end;
3873 $$ language plpgsql;
3874 select raise_test();
3875 NOTICE: SQLSTATE: 22012 SQLERRM: check me
3876 ERROR: check me
3877 DETAIL: some detail info
3878 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3879 create or replace function raise_test() returns void as $$
3880 begin
3881 raise 'check me'
3882 using errcode = '1234F', detail = 'some detail info';
3883 exception
3884 when others then
3885 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3886 raise;
3887 end;
3888 $$ language plpgsql;
3889 select raise_test();
3890 NOTICE: SQLSTATE: 1234F SQLERRM: check me
3891 ERROR: check me
3892 DETAIL: some detail info
3893 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3894 -- SQLSTATE specification in WHEN
3895 create or replace function raise_test() returns void as $$
3896 begin
3897 raise 'check me'
3898 using errcode = '1234F', detail = 'some detail info';
3899 exception
3900 when sqlstate '1234F' then
3901 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3902 raise;
3903 end;
3904 $$ language plpgsql;
3905 select raise_test();
3906 NOTICE: SQLSTATE: 1234F SQLERRM: check me
3907 ERROR: check me
3908 DETAIL: some detail info
3909 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3910 create or replace function raise_test() returns void as $$
3911 begin
3912 raise division_by_zero using detail = 'some detail info';
3913 exception
3914 when others then
3915 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3916 raise;
3917 end;
3918 $$ language plpgsql;
3919 select raise_test();
3920 NOTICE: SQLSTATE: 22012 SQLERRM: division_by_zero
3921 ERROR: division_by_zero
3922 DETAIL: some detail info
3923 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3924 create or replace function raise_test() returns void as $$
3925 begin
3926 raise division_by_zero;
3927 end;
3928 $$ language plpgsql;
3929 select raise_test();
3930 ERROR: division_by_zero
3931 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3932 create or replace function raise_test() returns void as $$
3933 begin
3934 raise sqlstate '1234F';
3935 end;
3936 $$ language plpgsql;
3937 select raise_test();
3938 ERROR: 1234F
3939 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3940 create or replace function raise_test() returns void as $$
3941 begin
3942 raise division_by_zero using message = 'custom' || ' message';
3943 end;
3944 $$ language plpgsql;
3945 select raise_test();
3946 ERROR: custom message
3947 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3948 create or replace function raise_test() returns void as $$
3949 begin
3950 raise using message = 'custom' || ' message', errcode = '22012';
3951 end;
3952 $$ language plpgsql;
3953 select raise_test();
3954 ERROR: custom message
3955 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3956 -- conflict on message
3957 create or replace function raise_test() returns void as $$
3958 begin
3959 raise notice 'some message' using message = 'custom' || ' message', errcode = '22012';
3960 end;
3961 $$ language plpgsql;
3962 select raise_test();
3963 ERROR: RAISE option already specified: MESSAGE
3964 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3965 -- conflict on errcode
3966 create or replace function raise_test() returns void as $$
3967 begin
3968 raise division_by_zero using message = 'custom' || ' message', errcode = '22012';
3969 end;
3970 $$ language plpgsql;
3971 select raise_test();
3972 ERROR: RAISE option already specified: ERRCODE
3973 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3974 -- nothing to re-RAISE
3975 create or replace function raise_test() returns void as $$
3976 begin
3977 raise;
3978 end;
3979 $$ language plpgsql;
3980 select raise_test();
3981 ERROR: RAISE without parameters cannot be used outside an exception handler
3982 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3983 -- test access to exception data
3984 create function zero_divide() returns int as $$
3985 declare v int := 0;
3986 begin
3987 return 10 / v;
3988 end;
3989 $$ language plpgsql;
3990 create or replace function raise_test() returns void as $$
3991 begin
3992 raise exception 'custom exception'
3993 using detail = 'some detail of custom exception',
3994 hint = 'some hint related to custom exception';
3995 end;
3996 $$ language plpgsql;
3997 create function stacked_diagnostics_test() returns void as $$
3998 declare _sqlstate text;
3999 _message text;
4000 _context text;
4001 begin
4002 perform zero_divide();
4003 exception when others then
4004 get stacked diagnostics
4005 _sqlstate = returned_sqlstate,
4006 _message = message_text,
4007 _context = pg_exception_context;
4008 raise notice 'sqlstate: %, message: %, context: [%]',
4009 _sqlstate, _message, replace(_context, E'\n', ' <- ');
4010 end;
4011 $$ language plpgsql;
4012 select stacked_diagnostics_test();
4013 NOTICE: sqlstate: 22012, message: division by zero, context: [PL/pgSQL function zero_divide() line 4 at RETURN <- SQL statement "SELECT zero_divide()" <- PL/pgSQL function stacked_diagnostics_test() line 6 at PERFORM]
4014 stacked_diagnostics_test
4015 --------------------------
4017 (1 row)
4019 create or replace function stacked_diagnostics_test() returns void as $$
4020 declare _detail text;
4021 _hint text;
4022 _message text;
4023 begin
4024 perform raise_test();
4025 exception when others then
4026 get stacked diagnostics
4027 _message = message_text,
4028 _detail = pg_exception_detail,
4029 _hint = pg_exception_hint;
4030 raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
4031 end;
4032 $$ language plpgsql;
4033 select stacked_diagnostics_test();
4034 NOTICE: message: custom exception, detail: some detail of custom exception, hint: some hint related to custom exception
4035 stacked_diagnostics_test
4036 --------------------------
4038 (1 row)
4040 -- fail, cannot use stacked diagnostics statement outside handler
4041 create or replace function stacked_diagnostics_test() returns void as $$
4042 declare _detail text;
4043 _hint text;
4044 _message text;
4045 begin
4046 get stacked diagnostics
4047 _message = message_text,
4048 _detail = pg_exception_detail,
4049 _hint = pg_exception_hint;
4050 raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
4051 end;
4052 $$ language plpgsql;
4053 select stacked_diagnostics_test();
4054 ERROR: GET STACKED DIAGNOSTICS cannot be used outside an exception handler
4055 CONTEXT: PL/pgSQL function stacked_diagnostics_test() line 6 at GET STACKED DIAGNOSTICS
4056 drop function zero_divide();
4057 drop function stacked_diagnostics_test();
4058 -- check cases where implicit SQLSTATE variable could be confused with
4059 -- SQLSTATE as a keyword, cf bug #5524
4060 create or replace function raise_test() returns void as $$
4061 begin
4062 perform 1/0;
4063 exception
4064 when sqlstate '22012' then
4065 raise notice using message = sqlstate;
4066 raise sqlstate '22012' using message = 'substitute message';
4067 end;
4068 $$ language plpgsql;
4069 select raise_test();
4070 NOTICE: 22012
4071 ERROR: substitute message
4072 CONTEXT: PL/pgSQL function raise_test() line 7 at RAISE
4073 drop function raise_test();
4074 -- test passing column_name, constraint_name, datatype_name, table_name
4075 -- and schema_name error fields
4076 create or replace function stacked_diagnostics_test() returns void as $$
4077 declare _column_name text;
4078 _constraint_name text;
4079 _datatype_name text;
4080 _table_name text;
4081 _schema_name text;
4082 begin
4083 raise exception using
4084 column = '>>some column name<<',
4085 constraint = '>>some constraint name<<',
4086 datatype = '>>some datatype name<<',
4087 table = '>>some table name<<',
4088 schema = '>>some schema name<<';
4089 exception when others then
4090 get stacked diagnostics
4091 _column_name = column_name,
4092 _constraint_name = constraint_name,
4093 _datatype_name = pg_datatype_name,
4094 _table_name = table_name,
4095 _schema_name = schema_name;
4096 raise notice 'column %, constraint %, type %, table %, schema %',
4097 _column_name, _constraint_name, _datatype_name, _table_name, _schema_name;
4098 end;
4099 $$ language plpgsql;
4100 select stacked_diagnostics_test();
4101 NOTICE: column >>some column name<<, constraint >>some constraint name<<, type >>some datatype name<<, table >>some table name<<, schema >>some schema name<<
4102 stacked_diagnostics_test
4103 --------------------------
4105 (1 row)
4107 drop function stacked_diagnostics_test();
4108 -- test variadic functions
4109 create or replace function vari(variadic int[])
4110 returns void as $$
4111 begin
4112 for i in array_lower($1,1)..array_upper($1,1) loop
4113 raise notice '%', $1[i];
4114 end loop; end;
4115 $$ language plpgsql;
4116 select vari(1,2,3,4,5);
4117 NOTICE: 1
4118 NOTICE: 2
4119 NOTICE: 3
4120 NOTICE: 4
4121 NOTICE: 5
4122 vari
4123 ------
4125 (1 row)
4127 select vari(3,4,5);
4128 NOTICE: 3
4129 NOTICE: 4
4130 NOTICE: 5
4131 vari
4132 ------
4134 (1 row)
4136 select vari(variadic array[5,6,7]);
4137 NOTICE: 5
4138 NOTICE: 6
4139 NOTICE: 7
4140 vari
4141 ------
4143 (1 row)
4145 drop function vari(int[]);
4146 -- coercion test
4147 create or replace function pleast(variadic numeric[])
4148 returns numeric as $$
4149 declare aux numeric = $1[array_lower($1,1)];
4150 begin
4151 for i in array_lower($1,1)+1..array_upper($1,1) loop
4152 if $1[i] < aux then aux := $1[i]; end if;
4153 end loop;
4154 return aux;
4155 end;
4156 $$ language plpgsql immutable strict;
4157 select pleast(10,1,2,3,-16);
4158 pleast
4159 --------
4160 -16
4161 (1 row)
4163 select pleast(10.2,2.2,-1.1);
4164 pleast
4165 --------
4166 -1.1
4167 (1 row)
4169 select pleast(10.2,10, -20);
4170 pleast
4171 --------
4172 -20
4173 (1 row)
4175 select pleast(10,20, -1.0);
4176 pleast
4177 --------
4178 -1.0
4179 (1 row)
4181 -- in case of conflict, non-variadic version is preferred
4182 create or replace function pleast(numeric)
4183 returns numeric as $$
4184 begin
4185 raise notice 'non-variadic function called';
4186 return $1;
4187 end;
4188 $$ language plpgsql immutable strict;
4189 select pleast(10);
4190 NOTICE: non-variadic function called
4191 pleast
4192 --------
4193 10
4194 (1 row)
4196 drop function pleast(numeric[]);
4197 drop function pleast(numeric);
4198 -- test table functions
4199 create function tftest(int) returns table(a int, b int) as $$
4200 begin
4201 return query select $1, $1+i from generate_series(1,5) g(i);
4202 end;
4203 $$ language plpgsql immutable strict;
4204 select * from tftest(10);
4205 a | b
4206 ----+----
4207 10 | 11
4208 10 | 12
4209 10 | 13
4210 10 | 14
4211 10 | 15
4212 (5 rows)
4214 create or replace function tftest(a1 int) returns table(a int, b int) as $$
4215 begin
4216 a := a1; b := a1 + 1;
4217 return next;
4218 a := a1 * 10; b := a1 * 10 + 1;
4219 return next;
4220 end;
4221 $$ language plpgsql immutable strict;
4222 select * from tftest(10);
4223 a | b
4224 -----+-----
4225 10 | 11
4226 100 | 101
4227 (2 rows)
4229 drop function tftest(int);
4230 create or replace function rttest()
4231 returns setof int as $$
4232 declare rc int;
4233 begin
4234 return query values(10),(20);
4235 get diagnostics rc = row_count;
4236 raise notice '% %', found, rc;
4237 return query select * from (values(10),(20)) f(a) where false;
4238 get diagnostics rc = row_count;
4239 raise notice '% %', found, rc;
4240 return query execute 'values(10),(20)';
4241 get diagnostics rc = row_count;
4242 raise notice '% %', found, rc;
4243 return query execute 'select * from (values(10),(20)) f(a) where false';
4244 get diagnostics rc = row_count;
4245 raise notice '% %', found, rc;
4246 end;
4247 $$ language plpgsql;
4248 select * from rttest();
4249 NOTICE: t 2
4250 NOTICE: f 0
4251 NOTICE: t 2
4252 NOTICE: f 0
4253 rttest
4254 --------
4255 10
4256 20
4257 10
4258 20
4259 (4 rows)
4261 drop function rttest();
4262 -- Test for proper cleanup at subtransaction exit. This example
4263 -- exposed a bug in PG 8.2.
4264 CREATE FUNCTION leaker_1(fail BOOL) RETURNS INTEGER AS $$
4265 DECLARE
4266 v_var INTEGER;
4267 BEGIN
4268 BEGIN
4269 v_var := (leaker_2(fail)).error_code;
4270 EXCEPTION
4271 WHEN others THEN RETURN 0;
4272 END;
4273 RETURN 1;
4274 END;
4275 $$ LANGUAGE plpgsql;
4276 CREATE FUNCTION leaker_2(fail BOOL, OUT error_code INTEGER, OUT new_id INTEGER)
4277 RETURNS RECORD AS $$
4278 BEGIN
4279 IF fail THEN
4280 RAISE EXCEPTION 'fail ...';
4281 END IF;
4282 error_code := 1;
4283 new_id := 1;
4284 RETURN;
4285 END;
4286 $$ LANGUAGE plpgsql;
4287 SELECT * FROM leaker_1(false);
4288 leaker_1
4289 ----------
4290 1
4291 (1 row)
4293 SELECT * FROM leaker_1(true);
4294 leaker_1
4295 ----------
4296 0
4297 (1 row)
4299 DROP FUNCTION leaker_1(bool);
4300 DROP FUNCTION leaker_2(bool);
4301 -- Test for appropriate cleanup of non-simple expression evaluations
4302 -- (bug in all versions prior to August 2010)
4303 CREATE FUNCTION nonsimple_expr_test() RETURNS text[] AS $$
4304 DECLARE
4305 arr text[];
4306 lr text;
4307 i integer;
4308 BEGIN
4309 arr := array[array['foo','bar'], array['baz', 'quux']];
4310 lr := 'fool';
4311 i := 1;
4312 -- use sub-SELECTs to make expressions non-simple
4313 arr[(SELECT i)][(SELECT i+1)] := (SELECT lr);
4314 RETURN arr;
4315 END;
4316 $$ LANGUAGE plpgsql;
4317 SELECT nonsimple_expr_test();
4318 nonsimple_expr_test
4319 -------------------------
4320 {{foo,fool},{baz,quux}}
4321 (1 row)
4323 DROP FUNCTION nonsimple_expr_test();
4324 CREATE FUNCTION nonsimple_expr_test() RETURNS integer AS $$
4325 declare
4326 i integer NOT NULL := 0;
4327 begin
4328 begin
4329 i := (SELECT NULL::integer); -- should throw error
4330 exception
4331 WHEN OTHERS THEN
4332 i := (SELECT 1::integer);
4333 end;
4334 return i;
4335 end;
4336 $$ LANGUAGE plpgsql;
4337 SELECT nonsimple_expr_test();
4338 nonsimple_expr_test
4339 ---------------------
4340 1
4341 (1 row)
4343 DROP FUNCTION nonsimple_expr_test();
4344 --
4345 -- Test cases involving recursion and error recovery in simple expressions
4346 -- (bugs in all versions before October 2010). The problems are most
4347 -- easily exposed by mutual recursion between plpgsql and sql functions.
4348 --
4349 create function recurse(float8) returns float8 as
4350 $$
4351 begin
4352 if ($1 > 0) then
4353 return sql_recurse($1 - 1);
4354 else
4355 return $1;
4356 end if;
4357 end;
4358 $$ language plpgsql;
4359 -- "limit" is to prevent this from being inlined
4360 create function sql_recurse(float8) returns float8 as
4361 $$ select recurse($1) limit 1; $$ language sql;
4362 select recurse(10);
4363 recurse
4364 ---------
4365 0
4366 (1 row)
4368 create function error1(text) returns text language sql as
4369 $$ SELECT relname::text FROM pg_class c WHERE c.oid = $1::regclass $$;
4370 create function error2(p_name_table text) returns text language plpgsql as $$
4371 begin
4372 return error1(p_name_table);
4373 end$$;
4374 BEGIN;
4375 create table public.stuffs (stuff text);
4376 SAVEPOINT a;
4377 select error2('nonexistent.stuffs');
4378 ERROR: schema "nonexistent" does not exist
4379 CONTEXT: SQL function "error1" statement 1
4380 PL/pgSQL function error2(text) line 3 at RETURN
4381 ROLLBACK TO a;
4382 select error2('public.stuffs');
4383 error2
4384 --------
4385 stuffs
4386 (1 row)
4388 rollback;
4389 drop function error2(p_name_table text);
4390 drop function error1(text);
4391 -- Test for proper handling of cast-expression caching
4392 create function sql_to_date(integer) returns date as $$
4393 select $1::text::date
4394 $$ language sql immutable strict;
4395 create cast (integer as date) with function sql_to_date(integer) as assignment;
4396 create function cast_invoker(integer) returns date as $$
4397 begin
4398 return $1;
4399 end$$ language plpgsql;
4400 select cast_invoker(20150717);
4401 cast_invoker
4402 --------------
4403 07-17-2015
4404 (1 row)
4406 select cast_invoker(20150718); -- second call crashed in pre-release 9.5
4407 cast_invoker
4408 --------------
4409 07-18-2015
4410 (1 row)
4412 begin;
4413 select cast_invoker(20150717);
4414 cast_invoker
4415 --------------
4416 07-17-2015
4417 (1 row)
4419 select cast_invoker(20150718);
4420 cast_invoker
4421 --------------
4422 07-18-2015
4423 (1 row)
4425 savepoint s1;
4426 select cast_invoker(20150718);
4427 cast_invoker
4428 --------------
4429 07-18-2015
4430 (1 row)
4432 select cast_invoker(-1); -- fails
4433 ERROR: invalid input syntax for type date: "-1"
4434 CONTEXT: SQL function "sql_to_date" statement 1
4435 PL/pgSQL function cast_invoker(integer) while casting return value to function's return type
4436 rollback to savepoint s1;
4437 select cast_invoker(20150719);
4438 cast_invoker
4439 --------------
4440 07-19-2015
4441 (1 row)
4443 select cast_invoker(20150720);
4444 cast_invoker
4445 --------------
4446 07-20-2015
4447 (1 row)
4449 commit;
4450 drop function cast_invoker(integer);
4451 drop function sql_to_date(integer) cascade;
4452 NOTICE: drop cascades to cast from integer to date
4453 -- Test handling of cast cache inside DO blocks
4454 -- (to check the original crash case, this must be a cast not previously
4455 -- used in this session)
4456 begin;
4457 do $$ declare x text[]; begin x := '{1.23, 4.56}'::numeric[]; end $$;
4458 do $$ declare x text[]; begin x := '{1.23, 4.56}'::numeric[]; end $$;
4459 end;
4460 -- Test for consistent reporting of error context
4461 create function fail() returns int language plpgsql as $$
4462 begin
4463 return 1/0;
4464 end
4465 $$;
4466 select fail();
4467 ERROR: division by zero
4468 CONTEXT: SQL expression "1/0"
4469 PL/pgSQL function fail() line 3 at RETURN
4470 select fail();
4471 ERROR: division by zero
4472 CONTEXT: SQL expression "1/0"
4473 PL/pgSQL function fail() line 3 at RETURN
4474 drop function fail();
4475 -- Test handling of string literals.
4476 set standard_conforming_strings = off;
4477 create or replace function strtest() returns text as $$
4478 begin
4479 raise notice 'foo\\bar\041baz';
4480 return 'foo\\bar\041baz';
4481 end
4482 $$ language plpgsql;
4483 WARNING: nonstandard use of \\ in a string literal
4484 LINE 3: raise notice 'foo\\bar\041baz';
4485 ^
4486 HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
4487 WARNING: nonstandard use of \\ in a string literal
4488 LINE 4: return 'foo\\bar\041baz';
4489 ^
4490 HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
4491 WARNING: nonstandard use of \\ in a string literal
4492 LINE 4: return 'foo\\bar\041baz';
4493 ^
4494 HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
4495 select strtest();
4496 NOTICE: foo\bar!baz
4497 WARNING: nonstandard use of \\ in a string literal
4498 LINE 1: 'foo\\bar\041baz'
4499 ^
4500 HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
4501 QUERY: 'foo\\bar\041baz'
4502 strtest
4503 -------------
4504 foo\bar!baz
4505 (1 row)
4507 create or replace function strtest() returns text as $$
4508 begin
4509 raise notice E'foo\\bar\041baz';
4510 return E'foo\\bar\041baz';
4511 end
4512 $$ language plpgsql;
4513 select strtest();
4514 NOTICE: foo\bar!baz
4515 strtest
4516 -------------
4517 foo\bar!baz
4518 (1 row)
4520 set standard_conforming_strings = on;
4521 create or replace function strtest() returns text as $$
4522 begin
4523 raise notice 'foo\\bar\041baz\';
4524 return 'foo\\bar\041baz\';
4525 end
4526 $$ language plpgsql;
4527 select strtest();
4528 NOTICE: foo\\bar\041baz\
4529 strtest
4530 ------------------
4531 foo\\bar\041baz\
4532 (1 row)
4534 create or replace function strtest() returns text as $$
4535 begin
4536 raise notice E'foo\\bar\041baz';
4537 return E'foo\\bar\041baz';
4538 end
4539 $$ language plpgsql;
4540 select strtest();
4541 NOTICE: foo\bar!baz
4542 strtest
4543 -------------
4544 foo\bar!baz
4545 (1 row)
4547 drop function strtest();
4548 -- Test anonymous code blocks.
4549 DO $$
4550 DECLARE r record;
4551 BEGIN
4552 FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno
4553 LOOP
4554 RAISE NOTICE '%, %', r.roomno, r.comment;
4555 END LOOP;
4556 END$$;
4557 NOTICE: 001, Entrance
4558 NOTICE: 002, Office
4559 NOTICE: 003, Office
4560 NOTICE: 004, Technical
4561 NOTICE: 101, Office
4562 NOTICE: 102, Conference
4563 NOTICE: 103, Restroom
4564 NOTICE: 104, Technical
4565 NOTICE: 105, Office
4566 NOTICE: 106, Office
4567 -- these are to check syntax error reporting
4568 DO LANGUAGE plpgsql $$begin return 1; end$$;
4569 ERROR: RETURN cannot have a parameter in function returning void
4570 LINE 1: DO LANGUAGE plpgsql $$begin return 1; end$$;
4571 ^
4572 DO $$
4573 DECLARE r record;
4574 BEGIN
4575 FOR r IN SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno
4576 LOOP
4577 RAISE NOTICE '%, %', r.roomno, r.comment;
4578 END LOOP;
4579 END$$;
4580 ERROR: column "foo" does not exist
4581 LINE 1: SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomn...
4582 ^
4583 QUERY: SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno
4584 CONTEXT: PL/pgSQL function inline_code_block line 4 at FOR over SELECT rows
4585 -- Check handling of errors thrown from/into anonymous code blocks.
4586 do $outer$
4587 begin
4588 for i in 1..10 loop
4589 begin
4590 execute $ex$
4591 do $$
4592 declare x int = 0;
4593 begin
4594 x := 1 / x;
4595 end;
4596 $$;
4597 $ex$;
4598 exception when division_by_zero then
4599 raise notice 'caught division by zero';
4600 end;
4601 end loop;
4602 end;
4603 $outer$;
4604 NOTICE: caught division by zero
4605 NOTICE: caught division by zero
4606 NOTICE: caught division by zero
4607 NOTICE: caught division by zero
4608 NOTICE: caught division by zero
4609 NOTICE: caught division by zero
4610 NOTICE: caught division by zero
4611 NOTICE: caught division by zero
4612 NOTICE: caught division by zero
4613 NOTICE: caught division by zero
4614 -- Check variable scoping -- a var is not available in its own or prior
4615 -- default expressions.
4616 create function scope_test() returns int as $$
4617 declare x int := 42;
4618 begin
4619 declare y int := x + 1;
4620 x int := x + 2;
4621 begin
4622 return x * 100 + y;
4623 end;
4624 end;
4625 $$ language plpgsql;
4626 select scope_test();
4627 scope_test
4628 ------------
4629 4443
4630 (1 row)
4632 drop function scope_test();
4633 -- Check handling of conflicts between plpgsql vars and table columns.
4634 set plpgsql.variable_conflict = error;
4635 create function conflict_test() returns setof int8_tbl as $$
4636 declare r record;
4637 q1 bigint := 42;
4638 begin
4639 for r in select q1,q2 from int8_tbl loop
4640 return next r;
4641 end loop;
4642 end;
4643 $$ language plpgsql;
4644 select * from conflict_test();
4645 ERROR: column reference "q1" is ambiguous
4646 LINE 1: select q1,q2 from int8_tbl
4647 ^
4648 DETAIL: It could refer to either a PL/pgSQL variable or a table column.
4649 QUERY: select q1,q2 from int8_tbl
4650 CONTEXT: PL/pgSQL function conflict_test() line 5 at FOR over SELECT rows
4651 create or replace function conflict_test() returns setof int8_tbl as $$
4652 #variable_conflict use_variable
4653 declare r record;
4654 q1 bigint := 42;
4655 begin
4656 for r in select q1,q2 from int8_tbl loop
4657 return next r;
4658 end loop;
4659 end;
4660 $$ language plpgsql;
4661 select * from conflict_test();
4662 q1 | q2
4663 ----+-------------------
4664 42 | 456
4665 42 | 4567890123456789
4666 42 | 123
4667 42 | 4567890123456789
4668 42 | -4567890123456789
4669 (5 rows)
4671 create or replace function conflict_test() returns setof int8_tbl as $$
4672 #variable_conflict use_column
4673 declare r record;
4674 q1 bigint := 42;
4675 begin
4676 for r in select q1,q2 from int8_tbl loop
4677 return next r;
4678 end loop;
4679 end;
4680 $$ language plpgsql;
4681 select * from conflict_test();
4682 q1 | q2
4683 ------------------+-------------------
4684 123 | 456
4685 123 | 4567890123456789
4686 4567890123456789 | 123
4687 4567890123456789 | 4567890123456789
4688 4567890123456789 | -4567890123456789
4689 (5 rows)
4691 drop function conflict_test();
4692 -- Check that an unreserved keyword can be used as a variable name
4693 create function unreserved_test() returns int as $$
4694 declare
4695 forward int := 21;
4696 begin
4697 forward := forward * 2;
4698 return forward;
4699 end
4700 $$ language plpgsql;
4701 select unreserved_test();
4702 unreserved_test
4703 -----------------
4704 42
4705 (1 row)
4707 create or replace function unreserved_test() returns int as $$
4708 declare
4709 return int := 42;
4710 begin
4711 return := return + 1;
4712 return return;
4713 end
4714 $$ language plpgsql;
4715 select unreserved_test();
4716 unreserved_test
4717 -----------------
4718 43
4719 (1 row)
4721 create or replace function unreserved_test() returns int as $$
4722 declare
4723 comment int := 21;
4724 begin
4725 comment := comment * 2;
4726 comment on function unreserved_test() is 'this is a test';
4727 return comment;
4728 end
4729 $$ language plpgsql;
4730 select unreserved_test();
4731 unreserved_test
4732 -----------------
4733 42
4734 (1 row)
4736 select obj_description('unreserved_test()'::regprocedure, 'pg_proc');
4737 obj_description
4738 -----------------
4739 this is a test
4740 (1 row)
4742 drop function unreserved_test();
4743 --
4744 -- Test FOREACH over arrays
4745 --
4746 create function foreach_test(anyarray)
4747 returns void as $$
4748 declare x int;
4749 begin
4750 foreach x in array $1
4751 loop
4752 raise notice '%', x;
4753 end loop;
4754 end;
4755 $$ language plpgsql;
4756 select foreach_test(ARRAY[1,2,3,4]);
4757 NOTICE: 1
4758 NOTICE: 2
4759 NOTICE: 3
4760 NOTICE: 4
4761 foreach_test
4762 --------------
4764 (1 row)
4766 select foreach_test(ARRAY[[1,2],[3,4]]);
4767 NOTICE: 1
4768 NOTICE: 2
4769 NOTICE: 3
4770 NOTICE: 4
4771 foreach_test
4772 --------------
4774 (1 row)
4776 create or replace function foreach_test(anyarray)
4777 returns void as $$
4778 declare x int;
4779 begin
4780 foreach x slice 1 in array $1
4781 loop
4782 raise notice '%', x;
4783 end loop;
4784 end;
4785 $$ language plpgsql;
4786 -- should fail
4787 select foreach_test(ARRAY[1,2,3,4]);
4788 ERROR: FOREACH ... SLICE loop variable must be of an array type
4789 CONTEXT: PL/pgSQL function foreach_test(anyarray) line 4 at FOREACH over array
4790 select foreach_test(ARRAY[[1,2],[3,4]]);
4791 ERROR: FOREACH ... SLICE loop variable must be of an array type
4792 CONTEXT: PL/pgSQL function foreach_test(anyarray) line 4 at FOREACH over array
4793 create or replace function foreach_test(anyarray)
4794 returns void as $$
4795 declare x int[];
4796 begin
4797 foreach x slice 1 in array $1
4798 loop
4799 raise notice '%', x;
4800 end loop;
4801 end;
4802 $$ language plpgsql;
4803 select foreach_test(ARRAY[1,2,3,4]);
4804 NOTICE: {1,2,3,4}
4805 foreach_test
4806 --------------
4808 (1 row)
4810 select foreach_test(ARRAY[[1,2],[3,4]]);
4811 NOTICE: {1,2}
4812 NOTICE: {3,4}
4813 foreach_test
4814 --------------
4816 (1 row)
4818 -- higher level of slicing
4819 create or replace function foreach_test(anyarray)
4820 returns void as $$
4821 declare x int[];
4822 begin
4823 foreach x slice 2 in array $1
4824 loop
4825 raise notice '%', x;
4826 end loop;
4827 end;
4828 $$ language plpgsql;
4829 -- should fail
4830 select foreach_test(ARRAY[1,2,3,4]);
4831 ERROR: slice dimension (2) is out of the valid range 0..1
4832 CONTEXT: PL/pgSQL function foreach_test(anyarray) line 4 at FOREACH over array
4833 -- ok
4834 select foreach_test(ARRAY[[1,2],[3,4]]);
4835 NOTICE: {{1,2},{3,4}}
4836 foreach_test
4837 --------------
4839 (1 row)
4841 select foreach_test(ARRAY[[[1,2]],[[3,4]]]);
4842 NOTICE: {{1,2}}
4843 NOTICE: {{3,4}}
4844 foreach_test
4845 --------------
4847 (1 row)
4849 create type xy_tuple AS (x int, y int);
4850 -- iteration over array of records
4851 create or replace function foreach_test(anyarray)
4852 returns void as $$
4853 declare r record;
4854 begin
4855 foreach r in array $1
4856 loop
4857 raise notice '%', r;
4858 end loop;
4859 end;
4860 $$ language plpgsql;
4861 select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]);
4862 NOTICE: (10,20)
4863 NOTICE: (40,69)
4864 NOTICE: (35,78)
4865 foreach_test
4866 --------------
4868 (1 row)
4870 select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]);
4871 NOTICE: (10,20)
4872 NOTICE: (40,69)
4873 NOTICE: (35,78)
4874 NOTICE: (88,76)
4875 foreach_test
4876 --------------
4878 (1 row)
4880 create or replace function foreach_test(anyarray)
4881 returns void as $$
4882 declare x int; y int;
4883 begin
4884 foreach x, y in array $1
4885 loop
4886 raise notice 'x = %, y = %', x, y;
4887 end loop;
4888 end;
4889 $$ language plpgsql;
4890 select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]);
4891 NOTICE: x = 10, y = 20
4892 NOTICE: x = 40, y = 69
4893 NOTICE: x = 35, y = 78
4894 foreach_test
4895 --------------
4897 (1 row)
4899 select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]);
4900 NOTICE: x = 10, y = 20
4901 NOTICE: x = 40, y = 69
4902 NOTICE: x = 35, y = 78
4903 NOTICE: x = 88, y = 76
4904 foreach_test
4905 --------------
4907 (1 row)
4909 -- slicing over array of composite types
4910 create or replace function foreach_test(anyarray)
4911 returns void as $$
4912 declare x xy_tuple[];
4913 begin
4914 foreach x slice 1 in array $1
4915 loop
4916 raise notice '%', x;
4917 end loop;
4918 end;
4919 $$ language plpgsql;
4920 select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]);
4921 NOTICE: {"(10,20)","(40,69)","(35,78)"}
4922 foreach_test
4923 --------------
4925 (1 row)
4927 select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]);
4928 NOTICE: {"(10,20)","(40,69)"}
4929 NOTICE: {"(35,78)","(88,76)"}
4930 foreach_test
4931 --------------
4933 (1 row)
4935 drop function foreach_test(anyarray);
4936 drop type xy_tuple;
4937 --
4938 -- Assorted tests for array subscript assignment
4939 --
4940 create temp table rtype (id int, ar text[]);
4941 create function arrayassign1() returns text[] language plpgsql as $$
4942 declare
4943 r record;
4944 begin
4945 r := row(12, '{foo,bar,baz}')::rtype;
4946 r.ar[2] := 'replace';
4947 return r.ar;
4948 end$$;
4949 select arrayassign1();
4950 arrayassign1
4951 -------------------
4952 {foo,replace,baz}
4953 (1 row)
4955 select arrayassign1(); -- try again to exercise internal caching
4956 arrayassign1
4957 -------------------
4958 {foo,replace,baz}
4959 (1 row)
4961 create domain orderedarray as int[2]
4962 constraint sorted check (value[1] < value[2]);
4963 select '{1,2}'::orderedarray;
4964 orderedarray
4965 --------------
4966 {1,2}
4967 (1 row)
4969 select '{2,1}'::orderedarray; -- fail
4970 ERROR: value for domain orderedarray violates check constraint "sorted"
4971 create function testoa(x1 int, x2 int, x3 int) returns orderedarray
4972 language plpgsql as $$
4973 declare res orderedarray;
4974 begin
4975 res := array[x1, x2];
4976 res[2] := x3;
4977 return res;
4978 end$$;
4979 select testoa(1,2,3);
4980 testoa
4981 --------
4982 {1,3}
4983 (1 row)
4985 select testoa(1,2,3); -- try again to exercise internal caching
4986 testoa
4987 --------
4988 {1,3}
4989 (1 row)
4991 select testoa(2,1,3); -- fail at initial assign
4992 ERROR: value for domain orderedarray violates check constraint "sorted"
4993 CONTEXT: PL/pgSQL function testoa(integer,integer,integer) line 4 at assignment
4994 select testoa(1,2,1); -- fail at update
4995 ERROR: value for domain orderedarray violates check constraint "sorted"
4996 CONTEXT: PL/pgSQL function testoa(integer,integer,integer) line 5 at assignment
4997 drop function arrayassign1();
4998 drop function testoa(x1 int, x2 int, x3 int);
4999 --
5000 -- Test handling of expanded arrays
5001 --
5002 create function returns_rw_array(int) returns int[]
5003 language plpgsql as $$
5004 declare r int[];
5005 begin r := array[$1, $1]; return r; end;
5006 $$ stable;
5007 create function consumes_rw_array(int[]) returns int
5008 language plpgsql as $$
5009 begin return $1[1]; end;
5010 $$ stable;
5011 select consumes_rw_array(returns_rw_array(42));
5012 consumes_rw_array
5013 -------------------
5014 42
5015 (1 row)
5017 -- bug #14174
5018 explain (verbose, costs off)
5019 select i, a from
5020 (select returns_rw_array(1) as a offset 0) ss,
5021 lateral consumes_rw_array(a) i;
5022 QUERY PLAN
5023 -----------------------------------------------------------------
5024 Nested Loop
5025 Output: i.i, (returns_rw_array(1))
5026 -> Result
5027 Output: returns_rw_array(1)
5028 -> Function Scan on public.consumes_rw_array i
5029 Output: i.i
5030 Function Call: consumes_rw_array((returns_rw_array(1)))
5031 (7 rows)
5033 select i, a from
5034 (select returns_rw_array(1) as a offset 0) ss,
5035 lateral consumes_rw_array(a) i;
5036 i | a
5037 ---+-------
5038 1 | {1,1}
5039 (1 row)
5041 explain (verbose, costs off)
5042 select consumes_rw_array(a), a from returns_rw_array(1) a;
5043 QUERY PLAN
5044 --------------------------------------------
5045 Function Scan on public.returns_rw_array a
5046 Output: consumes_rw_array(a), a
5047 Function Call: returns_rw_array(1)
5048 (3 rows)
5050 select consumes_rw_array(a), a from returns_rw_array(1) a;
5051 consumes_rw_array | a
5052 -------------------+-------
5053 1 | {1,1}
5054 (1 row)
5056 explain (verbose, costs off)
5057 select consumes_rw_array(a), a from
5058 (values (returns_rw_array(1)), (returns_rw_array(2))) v(a);
5059 QUERY PLAN
5060 ---------------------------------------------------------------------
5061 Values Scan on "*VALUES*"
5062 Output: consumes_rw_array("*VALUES*".column1), "*VALUES*".column1
5063 (2 rows)
5065 select consumes_rw_array(a), a from
5066 (values (returns_rw_array(1)), (returns_rw_array(2))) v(a);
5067 consumes_rw_array | a
5068 -------------------+-------
5069 1 | {1,1}
5070 2 | {2,2}
5071 (2 rows)
5073 do $$
5074 declare a int[] := array[1,2];
5075 begin
5076 a := a || 3;
5077 raise notice 'a = %', a;
5078 end$$;
5079 NOTICE: a = {1,2,3}
5080 --
5081 -- Test access to call stack
5082 --
5083 create function inner_func(int)
5084 returns int as $$
5085 declare _context text;
5086 begin
5087 get diagnostics _context = pg_context;
5088 raise notice '***%***', _context;
5089 -- lets do it again, just for fun..
5090 get diagnostics _context = pg_context;
5091 raise notice '***%***', _context;
5092 raise notice 'lets make sure we didnt break anything';
5093 return 2 * $1;
5094 end;
5095 $$ language plpgsql;
5096 create or replace function outer_func(int)
5097 returns int as $$
5098 declare
5099 myresult int;
5100 begin
5101 raise notice 'calling down into inner_func()';
5102 myresult := inner_func($1);
5103 raise notice 'inner_func() done';
5104 return myresult;
5105 end;
5106 $$ language plpgsql;
5107 create or replace function outer_outer_func(int)
5108 returns int as $$
5109 declare
5110 myresult int;
5111 begin
5112 raise notice 'calling down into outer_func()';
5113 myresult := outer_func($1);
5114 raise notice 'outer_func() done';
5115 return myresult;
5116 end;
5117 $$ language plpgsql;
5118 select outer_outer_func(10);
5119 NOTICE: calling down into outer_func()
5120 NOTICE: calling down into inner_func()
5121 NOTICE: ***PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS
5122 PL/pgSQL function outer_func(integer) line 6 at assignment
5123 PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
5124 NOTICE: ***PL/pgSQL function inner_func(integer) line 7 at GET DIAGNOSTICS
5125 PL/pgSQL function outer_func(integer) line 6 at assignment
5126 PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
5127 NOTICE: lets make sure we didnt break anything
5128 NOTICE: inner_func() done
5129 NOTICE: outer_func() done
5130 outer_outer_func
5131 ------------------
5132 20
5133 (1 row)
5135 -- repeated call should to work
5136 select outer_outer_func(20);
5137 NOTICE: calling down into outer_func()
5138 NOTICE: calling down into inner_func()
5139 NOTICE: ***PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS
5140 PL/pgSQL function outer_func(integer) line 6 at assignment
5141 PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
5142 NOTICE: ***PL/pgSQL function inner_func(integer) line 7 at GET DIAGNOSTICS
5143 PL/pgSQL function outer_func(integer) line 6 at assignment
5144 PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
5145 NOTICE: lets make sure we didnt break anything
5146 NOTICE: inner_func() done
5147 NOTICE: outer_func() done
5148 outer_outer_func
5149 ------------------
5150 40
5151 (1 row)
5153 drop function outer_outer_func(int);
5154 drop function outer_func(int);
5155 drop function inner_func(int);
5156 -- access to call stack from exception
5157 create function inner_func(int)
5158 returns int as $$
5159 declare
5160 _context text;
5161 sx int := 5;
5162 begin
5163 begin
5164 perform sx / 0;
5165 exception
5166 when division_by_zero then
5167 get diagnostics _context = pg_context;
5168 raise notice '***%***', _context;
5169 end;
5171 -- lets do it again, just for fun..
5172 get diagnostics _context = pg_context;
5173 raise notice '***%***', _context;
5174 raise notice 'lets make sure we didnt break anything';
5175 return 2 * $1;
5176 end;
5177 $$ language plpgsql;
5178 create or replace function outer_func(int)
5179 returns int as $$
5180 declare
5181 myresult int;
5182 begin
5183 raise notice 'calling down into inner_func()';
5184 myresult := inner_func($1);
5185 raise notice 'inner_func() done';
5186 return myresult;
5187 end;
5188 $$ language plpgsql;
5189 create or replace function outer_outer_func(int)
5190 returns int as $$
5191 declare
5192 myresult int;
5193 begin
5194 raise notice 'calling down into outer_func()';
5195 myresult := outer_func($1);
5196 raise notice 'outer_func() done';
5197 return myresult;
5198 end;
5199 $$ language plpgsql;
5200 select outer_outer_func(10);
5201 NOTICE: calling down into outer_func()
5202 NOTICE: calling down into inner_func()
5203 NOTICE: ***PL/pgSQL function inner_func(integer) line 10 at GET DIAGNOSTICS
5204 PL/pgSQL function outer_func(integer) line 6 at assignment
5205 PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
5206 NOTICE: ***PL/pgSQL function inner_func(integer) line 15 at GET DIAGNOSTICS
5207 PL/pgSQL function outer_func(integer) line 6 at assignment
5208 PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
5209 NOTICE: lets make sure we didnt break anything
5210 NOTICE: inner_func() done
5211 NOTICE: outer_func() done
5212 outer_outer_func
5213 ------------------
5214 20
5215 (1 row)
5217 -- repeated call should to work
5218 select outer_outer_func(20);
5219 NOTICE: calling down into outer_func()
5220 NOTICE: calling down into inner_func()
5221 NOTICE: ***PL/pgSQL function inner_func(integer) line 10 at GET DIAGNOSTICS
5222 PL/pgSQL function outer_func(integer) line 6 at assignment
5223 PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
5224 NOTICE: ***PL/pgSQL function inner_func(integer) line 15 at GET DIAGNOSTICS
5225 PL/pgSQL function outer_func(integer) line 6 at assignment
5226 PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
5227 NOTICE: lets make sure we didnt break anything
5228 NOTICE: inner_func() done
5229 NOTICE: outer_func() done
5230 outer_outer_func
5231 ------------------
5232 40
5233 (1 row)
5235 drop function outer_outer_func(int);
5236 drop function outer_func(int);
5237 drop function inner_func(int);
5238 --
5239 -- Test ASSERT
5240 --
5241 do $$
5242 begin
5243 assert 1=1; -- should succeed
5244 end;
5245 $$;
5246 do $$
5247 begin
5248 assert 1=0; -- should fail
5249 end;
5250 $$;
5251 ERROR: assertion failed
5252 CONTEXT: PL/pgSQL function inline_code_block line 3 at ASSERT
5253 do $$
5254 begin
5255 assert NULL; -- should fail
5256 end;
5257 $$;
5258 ERROR: assertion failed
5259 CONTEXT: PL/pgSQL function inline_code_block line 3 at ASSERT
5260 -- check controlling GUC
5261 set plpgsql.check_asserts = off;
5262 do $$
5263 begin
5264 assert 1=0; -- won't be tested
5265 end;
5266 $$;
5267 reset plpgsql.check_asserts;
5268 -- test custom message
5269 do $$
5270 declare var text := 'some value';
5271 begin
5272 assert 1=0, format('assertion failed, var = "%s"', var);
5273 end;
5274 $$;
5275 ERROR: assertion failed, var = "some value"
5276 CONTEXT: PL/pgSQL function inline_code_block line 4 at ASSERT
5277 -- ensure assertions are not trapped by 'others'
5278 do $$
5279 begin
5280 assert 1=0, 'unhandled assertion';
5281 exception when others then
5282 null; -- do nothing
5283 end;
5284 $$;
5285 ERROR: unhandled assertion
5286 CONTEXT: PL/pgSQL function inline_code_block line 3 at ASSERT
5287 -- Test use of plpgsql in a domain check constraint (cf. bug #14414)
5288 create function plpgsql_domain_check(val int) returns boolean as $$
5289 begin return val > 0; end
5290 $$ language plpgsql immutable;
5291 create domain plpgsql_domain as integer check(plpgsql_domain_check(value));
5292 do $$
5293 declare v_test plpgsql_domain;
5294 begin
5295 v_test := 1;
5296 end;
5297 $$;
5298 do $$
5299 declare v_test plpgsql_domain := 1;
5300 begin
5301 v_test := 0; -- fail
5302 end;
5303 $$;
5304 ERROR: value for domain plpgsql_domain violates check constraint "plpgsql_domain_check"
5305 CONTEXT: PL/pgSQL function inline_code_block line 4 at assignment
5306 -- Test handling of expanded array passed to a domain constraint (bug #14472)
5307 create function plpgsql_arr_domain_check(val int[]) returns boolean as $$
5308 begin return val[1] > 0; end
5309 $$ language plpgsql immutable;
5310 create domain plpgsql_arr_domain as int[] check(plpgsql_arr_domain_check(value));
5311 do $$
5312 declare v_test plpgsql_arr_domain;
5313 begin
5314 v_test := array[1];
5315 v_test := v_test || 2;
5316 end;
5317 $$;
5318 do $$
5319 declare v_test plpgsql_arr_domain := array[1];
5320 begin
5321 v_test := 0 || v_test; -- fail
5322 end;
5323 $$;
5324 ERROR: value for domain plpgsql_arr_domain violates check constraint "plpgsql_arr_domain_check"
5325 CONTEXT: PL/pgSQL function inline_code_block line 4 at assignment
5326 --
5327 -- test usage of transition tables in AFTER triggers
5328 --
5329 CREATE TABLE transition_table_base (id int PRIMARY KEY, val text);
5330 CREATE FUNCTION transition_table_base_ins_func()
5331 RETURNS trigger
5332 LANGUAGE plpgsql
5333 AS $$
5334 DECLARE
5335 t text;
5336 l text;
5337 BEGIN
5338 t = '';
5339 FOR l IN EXECUTE
5340 $q$
5341 EXPLAIN (TIMING off, COSTS off, VERBOSE on)
5342 SELECT * FROM newtable
5343 $q$ LOOP
5344 t = t || l || E'\n';
5345 END LOOP;
5347 RAISE INFO '%', t;
5348 RETURN new;
5349 END;
5350 $$;
5351 CREATE TRIGGER transition_table_base_ins_trig
5352 AFTER INSERT ON transition_table_base
5353 REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable
5354 FOR EACH STATEMENT
5355 EXECUTE PROCEDURE transition_table_base_ins_func();
5356 ERROR: OLD TABLE can only be specified for a DELETE or UPDATE trigger
5357 CREATE TRIGGER transition_table_base_ins_trig
5358 AFTER INSERT ON transition_table_base
5359 REFERENCING NEW TABLE AS newtable
5360 FOR EACH STATEMENT
5361 EXECUTE PROCEDURE transition_table_base_ins_func();
5362 INSERT INTO transition_table_base VALUES (1, 'One'), (2, 'Two');
5363 INFO: Named Tuplestore Scan
5364 Output: id, val
5366 INSERT INTO transition_table_base VALUES (3, 'Three'), (4, 'Four');
5367 INFO: Named Tuplestore Scan
5368 Output: id, val
5370 CREATE OR REPLACE FUNCTION transition_table_base_upd_func()
5371 RETURNS trigger
5372 LANGUAGE plpgsql
5373 AS $$
5374 DECLARE
5375 t text;
5376 l text;
5377 BEGIN
5378 t = '';
5379 FOR l IN EXECUTE
5380 $q$
5381 EXPLAIN (TIMING off, COSTS off, VERBOSE on)
5382 SELECT * FROM oldtable ot FULL JOIN newtable nt USING (id)
5383 $q$ LOOP
5384 t = t || l || E'\n';
5385 END LOOP;
5387 RAISE INFO '%', t;
5388 RETURN new;
5389 END;
5390 $$;
5391 CREATE TRIGGER transition_table_base_upd_trig
5392 AFTER UPDATE ON transition_table_base
5393 REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable
5394 FOR EACH STATEMENT
5395 EXECUTE PROCEDURE transition_table_base_upd_func();
5396 UPDATE transition_table_base
5397 SET val = '*' || val || '*'
5398 WHERE id BETWEEN 2 AND 3;
5399 INFO: Hash Full Join
5400 Output: COALESCE(ot.id, nt.id), ot.val, nt.val
5401 Hash Cond: (ot.id = nt.id)
5402 -> Named Tuplestore Scan
5403 Output: ot.id, ot.val
5404 -> Hash
5405 Output: nt.id, nt.val
5406 -> Named Tuplestore Scan
5407 Output: nt.id, nt.val
5409 CREATE TABLE transition_table_level1
5410 (
5411 level1_no serial NOT NULL ,
5412 level1_node_name varchar(255),
5413 PRIMARY KEY (level1_no)
5414 ) WITHOUT OIDS;
5415 CREATE TABLE transition_table_level2
5416 (
5417 level2_no serial NOT NULL ,
5418 parent_no int NOT NULL,
5419 level1_node_name varchar(255),
5420 PRIMARY KEY (level2_no)
5421 ) WITHOUT OIDS;
5422 CREATE TABLE transition_table_status
5423 (
5424 level int NOT NULL,
5425 node_no int NOT NULL,
5426 status int,
5427 PRIMARY KEY (level, node_no)
5428 ) WITHOUT OIDS;
5429 CREATE FUNCTION transition_table_level1_ri_parent_del_func()
5430 RETURNS TRIGGER
5431 LANGUAGE plpgsql
5432 AS $$
5433 DECLARE n bigint;
5434 BEGIN
5435 PERFORM FROM p JOIN transition_table_level2 c ON c.parent_no = p.level1_no;
5436 IF FOUND THEN
5437 RAISE EXCEPTION 'RI error';
5438 END IF;
5439 RETURN NULL;
5440 END;
5441 $$;
5442 CREATE TRIGGER transition_table_level1_ri_parent_del_trigger
5443 AFTER DELETE ON transition_table_level1
5444 REFERENCING OLD TABLE AS p
5445 FOR EACH STATEMENT EXECUTE PROCEDURE
5446 transition_table_level1_ri_parent_del_func();
5447 CREATE FUNCTION transition_table_level1_ri_parent_upd_func()
5448 RETURNS TRIGGER
5449 LANGUAGE plpgsql
5450 AS $$
5451 DECLARE
5452 x int;
5453 BEGIN
5454 WITH p AS (SELECT level1_no, sum(delta) cnt
5455 FROM (SELECT level1_no, 1 AS delta FROM i
5456 UNION ALL
5457 SELECT level1_no, -1 AS delta FROM d) w
5458 GROUP BY level1_no
5459 HAVING sum(delta) < 0)
5460 SELECT level1_no
5461 FROM p JOIN transition_table_level2 c ON c.parent_no = p.level1_no
5462 INTO x;
5463 IF FOUND THEN
5464 RAISE EXCEPTION 'RI error';
5465 END IF;
5466 RETURN NULL;
5467 END;
5468 $$;
5469 CREATE TRIGGER transition_table_level1_ri_parent_upd_trigger
5470 AFTER UPDATE ON transition_table_level1
5471 REFERENCING OLD TABLE AS d NEW TABLE AS i
5472 FOR EACH STATEMENT EXECUTE PROCEDURE
5473 transition_table_level1_ri_parent_upd_func();
5474 CREATE FUNCTION transition_table_level2_ri_child_insupd_func()
5475 RETURNS TRIGGER
5476 LANGUAGE plpgsql
5477 AS $$
5478 BEGIN
5479 PERFORM FROM i
5480 LEFT JOIN transition_table_level1 p
5481 ON p.level1_no IS NOT NULL AND p.level1_no = i.parent_no
5482 WHERE p.level1_no IS NULL;
5483 IF FOUND THEN
5484 RAISE EXCEPTION 'RI error';
5485 END IF;
5486 RETURN NULL;
5487 END;
5488 $$;
5489 CREATE TRIGGER transition_table_level2_ri_child_ins_trigger
5490 AFTER INSERT ON transition_table_level2
5491 REFERENCING NEW TABLE AS i
5492 FOR EACH STATEMENT EXECUTE PROCEDURE
5493 transition_table_level2_ri_child_insupd_func();
5494 CREATE TRIGGER transition_table_level2_ri_child_upd_trigger
5495 AFTER UPDATE ON transition_table_level2
5496 REFERENCING NEW TABLE AS i
5497 FOR EACH STATEMENT EXECUTE PROCEDURE
5498 transition_table_level2_ri_child_insupd_func();
5499 -- create initial test data
5500 INSERT INTO transition_table_level1 (level1_no)
5501 SELECT generate_series(1,200);
5502 ANALYZE transition_table_level1;
5503 INSERT INTO transition_table_level2 (level2_no, parent_no)
5504 SELECT level2_no, level2_no / 50 + 1 AS parent_no
5505 FROM generate_series(1,9999) level2_no;
5506 ANALYZE transition_table_level2;
5507 INSERT INTO transition_table_status (level, node_no, status)
5508 SELECT 1, level1_no, 0 FROM transition_table_level1;
5509 INSERT INTO transition_table_status (level, node_no, status)
5510 SELECT 2, level2_no, 0 FROM transition_table_level2;
5511 ANALYZE transition_table_status;
5512 INSERT INTO transition_table_level1(level1_no)
5513 SELECT generate_series(201,1000);
5514 ANALYZE transition_table_level1;
5515 -- behave reasonably if someone tries to modify a transition table
5516 CREATE FUNCTION transition_table_level2_bad_usage_func()
5517 RETURNS TRIGGER
5518 LANGUAGE plpgsql
5519 AS $$
5520 BEGIN
5521 INSERT INTO dx VALUES (1000000, 1000000, 'x');
5522 RETURN NULL;
5523 END;
5524 $$;
5525 CREATE TRIGGER transition_table_level2_bad_usage_trigger
5526 AFTER DELETE ON transition_table_level2
5527 REFERENCING OLD TABLE AS dx
5528 FOR EACH STATEMENT EXECUTE PROCEDURE
5529 transition_table_level2_bad_usage_func();
5530 DELETE FROM transition_table_level2
5531 WHERE level2_no BETWEEN 301 AND 305;
5532 ERROR: relation "dx" cannot be the target of a modifying statement
5533 CONTEXT: SQL statement "INSERT INTO dx VALUES (1000000, 1000000, 'x')"
5534 PL/pgSQL function transition_table_level2_bad_usage_func() line 3 at SQL statement
5535 DROP TRIGGER transition_table_level2_bad_usage_trigger
5536 ON transition_table_level2;
5537 -- attempt modifications which would break RI (should all fail)
5538 DELETE FROM transition_table_level1
5539 WHERE level1_no = 25;
5540 ERROR: RI error
5541 CONTEXT: PL/pgSQL function transition_table_level1_ri_parent_del_func() line 6 at RAISE
5542 UPDATE transition_table_level1 SET level1_no = -1
5543 WHERE level1_no = 30;
5544 ERROR: RI error
5545 CONTEXT: PL/pgSQL function transition_table_level1_ri_parent_upd_func() line 15 at RAISE
5546 INSERT INTO transition_table_level2 (level2_no, parent_no)
5547 VALUES (10000, 10000);
5548 ERROR: RI error
5549 CONTEXT: PL/pgSQL function transition_table_level2_ri_child_insupd_func() line 8 at RAISE
5550 UPDATE transition_table_level2 SET parent_no = 2000
5551 WHERE level2_no = 40;
5552 ERROR: RI error
5553 CONTEXT: PL/pgSQL function transition_table_level2_ri_child_insupd_func() line 8 at RAISE
5554 -- attempt modifications which would not break RI (should all succeed)
5555 DELETE FROM transition_table_level1
5556 WHERE level1_no BETWEEN 201 AND 1000;
5557 DELETE FROM transition_table_level1
5558 WHERE level1_no BETWEEN 100000000 AND 100000010;
5559 SELECT count(*) FROM transition_table_level1;
5560 count
5561 -------
5562 200
5563 (1 row)
5565 DELETE FROM transition_table_level2
5566 WHERE level2_no BETWEEN 211 AND 220;
5567 SELECT count(*) FROM transition_table_level2;
5568 count
5569 -------
5570 9989
5571 (1 row)
5573 CREATE TABLE alter_table_under_transition_tables
5574 (
5575 id int PRIMARY KEY,
5576 name text
5577 );
5578 CREATE FUNCTION alter_table_under_transition_tables_upd_func()
5579 RETURNS TRIGGER
5580 LANGUAGE plpgsql
5581 AS $$
5582 BEGIN
5583 RAISE WARNING 'old table = %, new table = %',
5584 (SELECT string_agg(id || '=' || name, ',') FROM d),
5585 (SELECT string_agg(id || '=' || name, ',') FROM i);
5586 RAISE NOTICE 'one = %', (SELECT 1 FROM alter_table_under_transition_tables LIMIT 1);
5587 RETURN NULL;
5588 END;
5589 $$;
5590 -- should fail, TRUNCATE is not compatible with transition tables
5591 CREATE TRIGGER alter_table_under_transition_tables_upd_trigger
5592 AFTER TRUNCATE OR UPDATE ON alter_table_under_transition_tables
5593 REFERENCING OLD TABLE AS d NEW TABLE AS i
5594 FOR EACH STATEMENT EXECUTE PROCEDURE
5595 alter_table_under_transition_tables_upd_func();
5596 ERROR: TRUNCATE triggers with transition tables are not supported
5597 -- should work
5598 CREATE TRIGGER alter_table_under_transition_tables_upd_trigger
5599 AFTER UPDATE ON alter_table_under_transition_tables
5600 REFERENCING OLD TABLE AS d NEW TABLE AS i
5601 FOR EACH STATEMENT EXECUTE PROCEDURE
5602 alter_table_under_transition_tables_upd_func();
5603 INSERT INTO alter_table_under_transition_tables
5604 VALUES (1, '1'), (2, '2'), (3, '3');
5605 UPDATE alter_table_under_transition_tables
5606 SET name = name || name;
5607 WARNING: old table = 1=1,2=2,3=3, new table = 1=11,2=22,3=33
5608 NOTICE: one = 1
5609 -- now change 'name' to an integer to see what happens...
5610 ALTER TABLE alter_table_under_transition_tables
5611 ALTER COLUMN name TYPE int USING name::integer;
5612 UPDATE alter_table_under_transition_tables
5613 SET name = (name::text || name::text)::integer;
5614 WARNING: old table = 1=11,2=22,3=33, new table = 1=1111,2=2222,3=3333
5615 NOTICE: one = 1
5616 -- now drop column 'name'
5617 ALTER TABLE alter_table_under_transition_tables
5618 DROP column name;
5619 UPDATE alter_table_under_transition_tables
5620 SET id = id;
5621 ERROR: column "name" does not exist
5622 LINE 1: (SELECT string_agg(id || '=' || name, ',') FROM d)
5623 ^
5624 QUERY: (SELECT string_agg(id || '=' || name, ',') FROM d)
5625 CONTEXT: PL/pgSQL function alter_table_under_transition_tables_upd_func() line 3 at RAISE
5626 --
5627 -- Test multiple reference to a transition table
5628 --
5629 CREATE TABLE multi_test (i int);
5630 INSERT INTO multi_test VALUES (1);
5631 CREATE OR REPLACE FUNCTION multi_test_trig() RETURNS trigger
5632 LANGUAGE plpgsql AS $$
5633 BEGIN
5634 RAISE NOTICE 'count = %', (SELECT COUNT(*) FROM new_test);
5635 RAISE NOTICE 'count union = %',
5636 (SELECT COUNT(*)
5637 FROM (SELECT * FROM new_test UNION ALL SELECT * FROM new_test) ss);
5638 RETURN NULL;
5639 END$$;
5640 CREATE TRIGGER my_trigger AFTER UPDATE ON multi_test
5641 REFERENCING NEW TABLE AS new_test OLD TABLE as old_test
5642 FOR EACH STATEMENT EXECUTE PROCEDURE multi_test_trig();
5643 UPDATE multi_test SET i = i;
5644 NOTICE: count = 1
5645 NOTICE: count union = 2
5646 DROP TABLE multi_test;
5647 DROP FUNCTION multi_test_trig();
5648 --
5649 -- Check type parsing and record fetching from partitioned tables
5650 --
5651 CREATE TABLE partitioned_table (a int, b text) PARTITION BY LIST (a);
5652 CREATE TABLE pt_part1 PARTITION OF partitioned_table FOR VALUES IN (1);
5653 CREATE TABLE pt_part2 PARTITION OF partitioned_table FOR VALUES IN (2);
5654 INSERT INTO partitioned_table VALUES (1, 'Row 1');
5655 INSERT INTO partitioned_table VALUES (2, 'Row 2');
5656 CREATE OR REPLACE FUNCTION get_from_partitioned_table(partitioned_table.a%type)
5657 RETURNS partitioned_table AS $$
5658 DECLARE
5659 a_val partitioned_table.a%TYPE;
5660 result partitioned_table%ROWTYPE;
5661 BEGIN
5662 a_val := $1;
5663 SELECT * INTO result FROM partitioned_table WHERE a = a_val;
5664 RETURN result;
5665 END; $$ LANGUAGE plpgsql;
5666 NOTICE: type reference partitioned_table.a%TYPE converted to integer
5667 SELECT * FROM get_from_partitioned_table(1) AS t;
5668 a | b
5669 ---+-------
5670 1 | Row 1
5671 (1 row)
5673 CREATE OR REPLACE FUNCTION list_partitioned_table()
5674 RETURNS SETOF partitioned_table.a%TYPE AS $$
5675 DECLARE
5676 row partitioned_table%ROWTYPE;
5677 a_val partitioned_table.a%TYPE;
5678 BEGIN
5679 FOR row IN SELECT * FROM partitioned_table ORDER BY a LOOP
5680 a_val := row.a;
5681 RETURN NEXT a_val;
5682 END LOOP;
5683 RETURN;
5684 END; $$ LANGUAGE plpgsql;
5685 NOTICE: type reference partitioned_table.a%TYPE converted to integer
5686 SELECT * FROM list_partitioned_table() AS t;
5687 t
5688 ---
5689 1
5690 2
5691 (2 rows)
5693 --
5694 -- Check argument name is used instead of $n in error message
5695 --
5696 CREATE FUNCTION fx(x WSlot) RETURNS void AS $$
5697 BEGIN
5698 GET DIAGNOSTICS x = ROW_COUNT;
5699 RETURN;
5700 END; $$ LANGUAGE plpgsql;
5701 ERROR: "x" is not a scalar variable
5702 LINE 3: GET DIAGNOSTICS x = ROW_COUNT;
5703 ^