| blob | 0a6945581bd8e27b92499a656a6752d272f901ca |
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 -- should fail
2260 create function constant_refcursor() returns refcursor as $$
2261 declare
2262 rc constant refcursor;
2263 begin
2264 open rc for select a from rc_test;
2265 return rc;
2266 end
2267 $$ language plpgsql;
2268 select constant_refcursor();
2269 ERROR: variable "rc" is declared CONSTANT
2270 CONTEXT: PL/pgSQL function constant_refcursor() line 5 at OPEN
2271 -- but it's okay like this
2272 create or replace function constant_refcursor() returns refcursor as $$
2273 declare
2274 rc constant refcursor := 'my_cursor_name';
2275 begin
2276 open rc for select a from rc_test;
2277 return rc;
2278 end
2279 $$ language plpgsql;
2280 select constant_refcursor();
2281 constant_refcursor
2282 --------------------
2283 my_cursor_name
2284 (1 row)
2286 --
2287 -- tests for cursors with named parameter arguments
2288 --
2289 create function namedparmcursor_test1(int, int) returns boolean as $$
2290 declare
2291 c1 cursor (param1 int, param12 int) for select * from rc_test where a > param1 and b > param12;
2292 nonsense record;
2293 begin
2294 open c1(param12 := $2, param1 := $1);
2295 fetch c1 into nonsense;
2296 close c1;
2297 if found then
2298 return true;
2299 else
2300 return false;
2301 end if;
2302 end
2303 $$ language plpgsql;
2304 select namedparmcursor_test1(20000, 20000) as "Should be false",
2305 namedparmcursor_test1(20, 20) as "Should be true";
2306 Should be false | Should be true
2307 -----------------+----------------
2308 f | t
2309 (1 row)
2311 -- mixing named and positional argument notations
2312 create function namedparmcursor_test2(int, int) returns boolean as $$
2313 declare
2314 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
2315 nonsense record;
2316 begin
2317 open c1(param1 := $1, $2);
2318 fetch c1 into nonsense;
2319 close c1;
2320 if found then
2321 return true;
2322 else
2323 return false;
2324 end if;
2325 end
2326 $$ language plpgsql;
2327 select namedparmcursor_test2(20, 20);
2328 namedparmcursor_test2
2329 -----------------------
2330 t
2331 (1 row)
2333 -- mixing named and positional: param2 is given twice, once in named notation
2334 -- and second time in positional notation. Should throw an error at parse time
2335 create function namedparmcursor_test3() returns void as $$
2336 declare
2337 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
2338 begin
2339 open c1(param2 := 20, 21);
2340 end
2341 $$ language plpgsql;
2342 ERROR: value for parameter "param2" of cursor "c1" specified more than once
2343 LINE 5: open c1(param2 := 20, 21);
2344 ^
2345 -- mixing named and positional: same as previous test, but param1 is duplicated
2346 create function namedparmcursor_test4() returns void as $$
2347 declare
2348 c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
2349 begin
2350 open c1(20, param1 := 21);
2351 end
2352 $$ language plpgsql;
2353 ERROR: value for parameter "param1" of cursor "c1" specified more than once
2354 LINE 5: open c1(20, param1 := 21);
2355 ^
2356 -- duplicate named parameter, should throw an error at parse time
2357 create function namedparmcursor_test5() returns void as $$
2358 declare
2359 c1 cursor (p1 int, p2 int) for
2360 select * from tenk1 where thousand = p1 and tenthous = p2;
2361 begin
2362 open c1 (p2 := 77, p2 := 42);
2363 end
2364 $$ language plpgsql;
2365 ERROR: value for parameter "p2" of cursor "c1" specified more than once
2366 LINE 6: open c1 (p2 := 77, p2 := 42);
2367 ^
2368 -- not enough parameters, should throw an error at parse time
2369 create function namedparmcursor_test6() returns void as $$
2370 declare
2371 c1 cursor (p1 int, p2 int) for
2372 select * from tenk1 where thousand = p1 and tenthous = p2;
2373 begin
2374 open c1 (p2 := 77);
2375 end
2376 $$ language plpgsql;
2377 ERROR: not enough arguments for cursor "c1"
2378 LINE 6: open c1 (p2 := 77);
2379 ^
2380 -- division by zero runtime error, the context given in the error message
2381 -- should be sensible
2382 create function namedparmcursor_test7() returns void as $$
2383 declare
2384 c1 cursor (p1 int, p2 int) for
2385 select * from tenk1 where thousand = p1 and tenthous = p2;
2386 begin
2387 open c1 (p2 := 77, p1 := 42/0);
2388 end $$ language plpgsql;
2389 select namedparmcursor_test7();
2390 ERROR: division by zero
2391 CONTEXT: PL/pgSQL expression "42/0 AS p1, 77 AS p2"
2392 PL/pgSQL function namedparmcursor_test7() line 6 at OPEN
2393 -- check that line comments work correctly within the argument list
2394 -- (this used to require a special hack in the code; it no longer does,
2395 -- but let's keep the test anyway)
2396 create function namedparmcursor_test8() returns int4 as $$
2397 declare
2398 c1 cursor (p1 int, p2 int) for
2399 select count(*) from tenk1 where thousand = p1 and tenthous = p2;
2400 n int4;
2401 begin
2402 open c1 (77 -- test
2403 , 42);
2404 fetch c1 into n;
2405 return n;
2406 end $$ language plpgsql;
2407 select namedparmcursor_test8();
2408 namedparmcursor_test8
2409 -----------------------
2410 0
2411 (1 row)
2413 -- cursor parameter name can match plpgsql variable or unreserved keyword
2414 create function namedparmcursor_test9(p1 int) returns int4 as $$
2415 declare
2416 c1 cursor (p1 int, p2 int, debug int) for
2417 select count(*) from tenk1 where thousand = p1 and tenthous = p2
2418 and four = debug;
2419 p2 int4 := 1006;
2420 n int4;
2421 begin
2422 open c1 (p1 := p1, p2 := p2, debug := 2);
2423 fetch c1 into n;
2424 return n;
2425 end $$ language plpgsql;
2426 select namedparmcursor_test9(6);
2427 namedparmcursor_test9
2428 -----------------------
2429 1
2430 (1 row)
2432 --
2433 -- tests for "raise" processing
2434 --
2435 create function raise_test1(int) returns int as $$
2436 begin
2437 raise notice 'This message has too many parameters!', $1;
2438 return $1;
2439 end;
2440 $$ language plpgsql;
2441 ERROR: too many parameters specified for RAISE
2442 CONTEXT: compilation of PL/pgSQL function "raise_test1" near line 3
2443 create function raise_test2(int) returns int as $$
2444 begin
2445 raise notice 'This message has too few parameters: %, %, %', $1, $1;
2446 return $1;
2447 end;
2448 $$ language plpgsql;
2449 ERROR: too few parameters specified for RAISE
2450 CONTEXT: compilation of PL/pgSQL function "raise_test2" near line 3
2451 create function raise_test3(int) returns int as $$
2452 begin
2453 raise notice 'This message has no parameters (despite having %% signs in it)!';
2454 return $1;
2455 end;
2456 $$ language plpgsql;
2457 select raise_test3(1);
2458 NOTICE: This message has no parameters (despite having % signs in it)!
2459 raise_test3
2460 -------------
2461 1
2462 (1 row)
2464 -- Test re-RAISE inside a nested exception block. This case is allowed
2465 -- by Oracle's PL/SQL but was handled differently by PG before 9.1.
2466 CREATE FUNCTION reraise_test() RETURNS void AS $$
2467 BEGIN
2468 BEGIN
2469 RAISE syntax_error;
2470 EXCEPTION
2471 WHEN syntax_error THEN
2472 BEGIN
2473 raise notice 'exception % thrown in inner block, reraising', sqlerrm;
2474 RAISE;
2475 EXCEPTION
2476 WHEN OTHERS THEN
2477 raise notice 'RIGHT - exception % caught in inner block', sqlerrm;
2478 END;
2479 END;
2480 EXCEPTION
2481 WHEN OTHERS THEN
2482 raise notice 'WRONG - exception % caught in outer block', sqlerrm;
2483 END;
2484 $$ LANGUAGE plpgsql;
2485 SELECT reraise_test();
2486 NOTICE: exception syntax_error thrown in inner block, reraising
2487 NOTICE: RIGHT - exception syntax_error caught in inner block
2488 reraise_test
2489 --------------
2491 (1 row)
2493 --
2494 -- reject function definitions that contain malformed SQL queries at
2495 -- compile-time, where possible
2496 --
2497 create function bad_sql1() returns int as $$
2498 declare a int;
2499 begin
2500 a := 5;
2501 Johnny Yuma;
2502 a := 10;
2503 return a;
2504 end$$ language plpgsql;
2505 ERROR: syntax error at or near "Johnny"
2506 LINE 5: Johnny Yuma;
2507 ^
2508 create function bad_sql2() returns int as $$
2509 declare r record;
2510 begin
2511 for r in select I fought the law, the law won LOOP
2512 raise notice 'in loop';
2513 end loop;
2514 return 5;
2515 end;$$ language plpgsql;
2516 ERROR: syntax error at or near "the"
2517 LINE 4: for r in select I fought the law, the law won LOOP
2518 ^
2519 -- a RETURN expression is mandatory, except for void-returning
2520 -- functions, where it is not allowed
2521 create function missing_return_expr() returns int as $$
2522 begin
2523 return ;
2524 end;$$ language plpgsql;
2525 ERROR: missing expression at or near ";"
2526 LINE 3: return ;
2527 ^
2528 create function void_return_expr() returns void as $$
2529 begin
2530 return 5;
2531 end;$$ language plpgsql;
2532 ERROR: RETURN cannot have a parameter in function returning void
2533 LINE 3: return 5;
2534 ^
2535 -- VOID functions are allowed to omit RETURN
2536 create function void_return_expr() returns void as $$
2537 begin
2538 perform 2+2;
2539 end;$$ language plpgsql;
2540 select void_return_expr();
2541 void_return_expr
2542 ------------------
2544 (1 row)
2546 -- but ordinary functions are not
2547 create function missing_return_expr() returns int as $$
2548 begin
2549 perform 2+2;
2550 end;$$ language plpgsql;
2551 select missing_return_expr();
2552 ERROR: control reached end of function without RETURN
2553 CONTEXT: PL/pgSQL function missing_return_expr()
2554 drop function void_return_expr();
2555 drop function missing_return_expr();
2556 --
2557 -- EXECUTE ... INTO test
2558 --
2559 create table eifoo (i integer, y integer);
2560 create type eitype as (i integer, y integer);
2561 create or replace function execute_into_test(varchar) returns record as $$
2562 declare
2563 _r record;
2564 _rt eifoo%rowtype;
2565 _v eitype;
2566 i int;
2567 j int;
2568 k int;
2569 begin
2570 execute 'insert into '||$1||' values(10,15)';
2571 execute 'select (row).* from (select row(10,1)::eifoo) s' into _r;
2572 raise notice '% %', _r.i, _r.y;
2573 execute 'select * from '||$1||' limit 1' into _rt;
2574 raise notice '% %', _rt.i, _rt.y;
2575 execute 'select *, 20 from '||$1||' limit 1' into i, j, k;
2576 raise notice '% % %', i, j, k;
2577 execute 'select 1,2' into _v;
2578 return _v;
2579 end; $$ language plpgsql;
2580 select execute_into_test('eifoo');
2581 NOTICE: 10 1
2582 NOTICE: 10 15
2583 NOTICE: 10 15 20
2584 execute_into_test
2585 -------------------
2586 (1,2)
2587 (1 row)
2589 drop table eifoo cascade;
2590 drop type eitype cascade;
2591 --
2592 -- SQLSTATE and SQLERRM test
2593 --
2594 create function excpt_test1() returns void as $$
2595 begin
2596 raise notice '% %', sqlstate, sqlerrm;
2597 end; $$ language plpgsql;
2598 -- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
2599 -- blocks
2600 select excpt_test1();
2601 ERROR: column "sqlstate" does not exist
2602 LINE 1: sqlstate
2603 ^
2604 QUERY: sqlstate
2605 CONTEXT: PL/pgSQL function excpt_test1() line 3 at RAISE
2606 create function excpt_test2() returns void as $$
2607 begin
2608 begin
2609 begin
2610 raise notice '% %', sqlstate, sqlerrm;
2611 end;
2612 end;
2613 end; $$ language plpgsql;
2614 -- should fail
2615 select excpt_test2();
2616 ERROR: column "sqlstate" does not exist
2617 LINE 1: sqlstate
2618 ^
2619 QUERY: sqlstate
2620 CONTEXT: PL/pgSQL function excpt_test2() line 5 at RAISE
2621 create function excpt_test3() returns void as $$
2622 begin
2623 begin
2624 raise exception 'user exception';
2625 exception when others then
2626 raise notice 'caught exception % %', sqlstate, sqlerrm;
2627 begin
2628 raise notice '% %', sqlstate, sqlerrm;
2629 perform 10/0;
2630 exception
2631 when substring_error then
2632 -- this exception handler shouldn't be invoked
2633 raise notice 'unexpected exception: % %', sqlstate, sqlerrm;
2634 when division_by_zero then
2635 raise notice 'caught exception % %', sqlstate, sqlerrm;
2636 end;
2637 raise notice '% %', sqlstate, sqlerrm;
2638 end;
2639 end; $$ language plpgsql;
2640 select excpt_test3();
2641 NOTICE: caught exception P0001 user exception
2642 NOTICE: P0001 user exception
2643 NOTICE: caught exception 22012 division by zero
2644 NOTICE: P0001 user exception
2645 excpt_test3
2646 -------------
2648 (1 row)
2650 create function excpt_test4() returns text as $$
2651 begin
2652 begin perform 1/0;
2653 exception when others then return sqlerrm; end;
2654 end; $$ language plpgsql;
2655 select excpt_test4();
2656 excpt_test4
2657 ------------------
2658 division by zero
2659 (1 row)
2661 drop function excpt_test1();
2662 drop function excpt_test2();
2663 drop function excpt_test3();
2664 drop function excpt_test4();
2665 -- parameters of raise stmt can be expressions
2666 create function raise_exprs() returns void as $$
2667 declare
2668 a integer[] = '{10,20,30}';
2669 c varchar = 'xyz';
2670 i integer;
2671 begin
2672 i := 2;
2673 raise notice '%; %; %; %; %; %', a, a[i], c, (select c || 'abc'), row(10,'aaa',NULL,30), NULL;
2674 end;$$ language plpgsql;
2675 select raise_exprs();
2676 NOTICE: {10,20,30}; 20; xyz; xyzabc; (10,aaa,,30); <NULL>
2677 raise_exprs
2678 -------------
2680 (1 row)
2682 drop function raise_exprs();
2683 -- regression test: verify that multiple uses of same plpgsql datum within
2684 -- a SQL command all get mapped to the same $n parameter. The return value
2685 -- of the SELECT is not important, we only care that it doesn't fail with
2686 -- a complaint about an ungrouped column reference.
2687 create function multi_datum_use(p1 int) returns bool as $$
2688 declare
2689 x int;
2690 y int;
2691 begin
2692 select into x,y unique1/p1, unique1/$1 from tenk1 group by unique1/p1;
2693 return x = y;
2694 end$$ language plpgsql;
2695 select multi_datum_use(42);
2696 multi_datum_use
2697 -----------------
2698 t
2699 (1 row)
2701 --
2702 -- Test STRICT limiter in both planned and EXECUTE invocations.
2703 -- Note that a data-modifying query is quasi strict (disallow multi rows)
2704 -- by default in the planned case, but not in EXECUTE.
2705 --
2706 create temp table foo (f1 int, f2 int);
2707 insert into foo values (1,2), (3,4);
2708 create or replace function stricttest() returns void as $$
2709 declare x record;
2710 begin
2711 -- should work
2712 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 -- should fail due to implicit strict
2726 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 ERROR: query returned more than one row
2731 HINT: Make sure the query returns a single row, or use LIMIT 1.
2732 CONTEXT: PL/pgSQL function stricttest() line 5 at SQL statement
2733 create or replace function stricttest() returns void as $$
2734 declare x record;
2735 begin
2736 -- should work
2737 execute 'insert into foo values(5,6) returning *' into x;
2738 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2739 end$$ language plpgsql;
2740 select stricttest();
2741 NOTICE: x.f1 = 5, x.f2 = 6
2742 stricttest
2743 ------------
2745 (1 row)
2747 create or replace function stricttest() returns void as $$
2748 declare x record;
2749 begin
2750 -- this should work since EXECUTE isn't as picky
2751 execute 'insert into foo values(7,8),(9,10) returning *' into x;
2752 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2753 end$$ language plpgsql;
2754 select stricttest();
2755 NOTICE: x.f1 = 7, x.f2 = 8
2756 stricttest
2757 ------------
2759 (1 row)
2761 select * from foo;
2762 f1 | f2
2763 ----+----
2764 1 | 2
2765 3 | 4
2766 5 | 6
2767 5 | 6
2768 7 | 8
2769 9 | 10
2770 (6 rows)
2772 create or replace function stricttest() returns void as $$
2773 declare x record;
2774 begin
2775 -- should work
2776 select * from foo where f1 = 3 into strict x;
2777 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2778 end$$ language plpgsql;
2779 select stricttest();
2780 NOTICE: x.f1 = 3, x.f2 = 4
2781 stricttest
2782 ------------
2784 (1 row)
2786 create or replace function stricttest() returns void as $$
2787 declare x record;
2788 begin
2789 -- should fail, no rows
2790 select * from foo where f1 = 0 into strict x;
2791 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2792 end$$ language plpgsql;
2793 select stricttest();
2794 ERROR: query returned no rows
2795 CONTEXT: PL/pgSQL function stricttest() line 5 at SQL statement
2796 create or replace function stricttest() returns void as $$
2797 declare x record;
2798 begin
2799 -- should fail, too many rows
2800 select * from foo where f1 > 3 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 more than one row
2805 HINT: Make sure the query returns a single row, or use LIMIT 1.
2806 CONTEXT: PL/pgSQL function stricttest() line 5 at SQL statement
2807 create or replace function stricttest() returns void as $$
2808 declare x record;
2809 begin
2810 -- should work
2811 execute 'select * from foo where f1 = 3' into strict x;
2812 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2813 end$$ language plpgsql;
2814 select stricttest();
2815 NOTICE: x.f1 = 3, x.f2 = 4
2816 stricttest
2817 ------------
2819 (1 row)
2821 create or replace function stricttest() returns void as $$
2822 declare x record;
2823 begin
2824 -- should fail, no rows
2825 execute 'select * from foo where f1 = 0' into strict x;
2826 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2827 end$$ language plpgsql;
2828 select stricttest();
2829 ERROR: query returned no rows
2830 CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE
2831 create or replace function stricttest() returns void as $$
2832 declare x record;
2833 begin
2834 -- should fail, too many rows
2835 execute 'select * from foo where f1 > 3' into strict x;
2836 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2837 end$$ language plpgsql;
2838 select stricttest();
2839 ERROR: query returned more than one row
2840 CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE
2841 drop function stricttest();
2842 -- test printing parameters after failure due to STRICT
2843 set plpgsql.print_strict_params to true;
2844 create or replace function stricttest() returns void as $$
2845 declare
2846 x record;
2847 p1 int := 2;
2848 p3 text := 'foo';
2849 begin
2850 -- no rows
2851 select * from foo where f1 = p1 and f1::text = p3 into strict x;
2852 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2853 end$$ language plpgsql;
2854 select stricttest();
2855 ERROR: query returned no rows
2856 DETAIL: parameters: p1 = '2', p3 = 'foo'
2857 CONTEXT: PL/pgSQL function stricttest() line 8 at SQL statement
2858 create or replace function stricttest() returns void as $$
2859 declare
2860 x record;
2861 p1 int := 2;
2862 p3 text := $a$'Valame Dios!' dijo Sancho; 'no le dije yo a vuestra merced que mirase bien lo que hacia?'$a$;
2863 begin
2864 -- no rows
2865 select * from foo where f1 = p1 and f1::text = p3 into strict x;
2866 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2867 end$$ language plpgsql;
2868 select stricttest();
2869 ERROR: query returned no rows
2870 DETAIL: parameters: p1 = '2', p3 = '''Valame Dios!'' dijo Sancho; ''no le dije yo a vuestra merced que mirase bien lo que hacia?'''
2871 CONTEXT: PL/pgSQL function stricttest() line 8 at SQL statement
2872 create or replace function stricttest() returns void as $$
2873 declare
2874 x record;
2875 p1 int := 2;
2876 p3 text := 'foo';
2877 begin
2878 -- too many rows
2879 select * from foo where f1 > p1 or f1::text = p3 into strict x;
2880 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2881 end$$ language plpgsql;
2882 select stricttest();
2883 ERROR: query returned more than one row
2884 DETAIL: parameters: p1 = '2', p3 = 'foo'
2885 HINT: Make sure the query returns a single row, or use LIMIT 1.
2886 CONTEXT: PL/pgSQL function stricttest() line 8 at SQL statement
2887 create or replace function stricttest() returns void as $$
2888 declare x record;
2889 begin
2890 -- too many rows, no params
2891 select * from foo where f1 > 3 into strict x;
2892 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2893 end$$ language plpgsql;
2894 select stricttest();
2895 ERROR: query returned more than one row
2896 HINT: Make sure the query returns a single row, or use LIMIT 1.
2897 CONTEXT: PL/pgSQL function stricttest() line 5 at SQL statement
2898 create or replace function stricttest() returns void as $$
2899 declare x record;
2900 begin
2901 -- no rows
2902 execute 'select * from foo where f1 = $1 or f1::text = $2' using 0, 'foo' into strict x;
2903 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2904 end$$ language plpgsql;
2905 select stricttest();
2906 ERROR: query returned no rows
2907 DETAIL: parameters: $1 = '0', $2 = 'foo'
2908 CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE
2909 create or replace function stricttest() returns void as $$
2910 declare x record;
2911 begin
2912 -- too many rows
2913 execute 'select * from foo where f1 > $1' using 1 into strict x;
2914 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2915 end$$ language plpgsql;
2916 select stricttest();
2917 ERROR: query returned more than one row
2918 DETAIL: parameters: $1 = '1'
2919 CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE
2920 create or replace function stricttest() returns void as $$
2921 declare x record;
2922 begin
2923 -- too many rows, no parameters
2924 execute 'select * from foo where f1 > 3' into strict x;
2925 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2926 end$$ language plpgsql;
2927 select stricttest();
2928 ERROR: query returned more than one row
2929 CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE
2930 create or replace function stricttest() returns void as $$
2931 -- override the global
2932 #print_strict_params off
2933 declare
2934 x record;
2935 p1 int := 2;
2936 p3 text := 'foo';
2937 begin
2938 -- too many rows
2939 select * from foo where f1 > p1 or f1::text = p3 into strict x;
2940 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2941 end$$ language plpgsql;
2942 select stricttest();
2943 ERROR: query returned more than one row
2944 HINT: Make sure the query returns a single row, or use LIMIT 1.
2945 CONTEXT: PL/pgSQL function stricttest() line 10 at SQL statement
2946 reset plpgsql.print_strict_params;
2947 create or replace function stricttest() returns void as $$
2948 -- override the global
2949 #print_strict_params on
2950 declare
2951 x record;
2952 p1 int := 2;
2953 p3 text := 'foo';
2954 begin
2955 -- too many rows
2956 select * from foo where f1 > p1 or f1::text = p3 into strict x;
2957 raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
2958 end$$ language plpgsql;
2959 select stricttest();
2960 ERROR: query returned more than one row
2961 DETAIL: parameters: p1 = '2', p3 = 'foo'
2962 HINT: Make sure the query returns a single row, or use LIMIT 1.
2963 CONTEXT: PL/pgSQL function stricttest() line 10 at SQL statement
2964 -- test warnings and errors
2965 set plpgsql.extra_warnings to 'all';
2966 set plpgsql.extra_warnings to 'none';
2967 set plpgsql.extra_errors to 'all';
2968 set plpgsql.extra_errors to 'none';
2969 -- test warnings when shadowing a variable
2970 set plpgsql.extra_warnings to 'shadowed_variables';
2971 -- simple shadowing of input and output parameters
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 set plpgsql.extra_warnings to 'shadowed_variables';
2992 select shadowtest(1);
2993 shadowtest
2994 ------------
2995 (0 rows)
2997 create or replace function shadowtest(in1 int)
2998 returns table (out1 int) as $$
2999 declare
3000 in1 int;
3001 out1 int;
3002 begin
3003 end
3004 $$ language plpgsql;
3005 WARNING: variable "in1" shadows a previously defined variable
3006 LINE 4: in1 int;
3007 ^
3008 WARNING: variable "out1" shadows a previously defined variable
3009 LINE 5: out1 int;
3010 ^
3011 select shadowtest(1);
3012 shadowtest
3013 ------------
3014 (0 rows)
3016 drop function shadowtest(int);
3017 -- shadowing in a second DECLARE block
3018 create or replace function shadowtest()
3019 returns void as $$
3020 declare
3021 f1 int;
3022 begin
3023 declare
3024 f1 int;
3025 begin
3026 end;
3027 end$$ language plpgsql;
3028 WARNING: variable "f1" shadows a previously defined variable
3029 LINE 7: f1 int;
3030 ^
3031 drop function shadowtest();
3032 -- several levels of shadowing
3033 create or replace function shadowtest(in1 int)
3034 returns void as $$
3035 declare
3036 in1 int;
3037 begin
3038 declare
3039 in1 int;
3040 begin
3041 end;
3042 end$$ language plpgsql;
3043 WARNING: variable "in1" shadows a previously defined variable
3044 LINE 4: in1 int;
3045 ^
3046 WARNING: variable "in1" shadows a previously defined variable
3047 LINE 7: in1 int;
3048 ^
3049 drop function shadowtest(int);
3050 -- shadowing in cursor definitions
3051 create or replace function shadowtest()
3052 returns void as $$
3053 declare
3054 f1 int;
3055 c1 cursor (f1 int) for select 1;
3056 begin
3057 end$$ language plpgsql;
3058 WARNING: variable "f1" shadows a previously defined variable
3059 LINE 5: c1 cursor (f1 int) for select 1;
3060 ^
3061 drop function shadowtest();
3062 -- test errors when shadowing a variable
3063 set plpgsql.extra_errors to 'shadowed_variables';
3064 create or replace function shadowtest(f1 int)
3065 returns boolean as $$
3066 declare f1 int; begin return 1; end $$ language plpgsql;
3067 ERROR: variable "f1" shadows a previously defined variable
3068 LINE 3: declare f1 int; begin return 1; end $$ language plpgsql;
3069 ^
3070 select shadowtest(1);
3071 ERROR: function shadowtest(integer) does not exist
3072 LINE 1: select shadowtest(1);
3073 ^
3074 HINT: No function matches the given name and argument types. You might need to add explicit type casts.
3075 reset plpgsql.extra_errors;
3076 reset plpgsql.extra_warnings;
3077 create or replace function shadowtest(f1 int)
3078 returns boolean as $$
3079 declare f1 int; begin return 1; end $$ language plpgsql;
3080 select shadowtest(1);
3081 shadowtest
3082 ------------
3083 t
3084 (1 row)
3086 -- runtime extra checks
3087 set plpgsql.extra_warnings to 'too_many_rows';
3088 do $$
3089 declare x int;
3090 begin
3091 select v from generate_series(1,2) g(v) into x;
3092 end;
3093 $$;
3094 WARNING: query returned more than one row
3095 HINT: Make sure the query returns a single row, or use LIMIT 1.
3096 set plpgsql.extra_errors to 'too_many_rows';
3097 do $$
3098 declare x int;
3099 begin
3100 select v from generate_series(1,2) g(v) into x;
3101 end;
3102 $$;
3103 ERROR: query returned more than one row
3104 HINT: Make sure the query returns a single row, or use LIMIT 1.
3105 CONTEXT: PL/pgSQL function inline_code_block line 4 at SQL statement
3106 reset plpgsql.extra_errors;
3107 reset plpgsql.extra_warnings;
3108 set plpgsql.extra_warnings to 'strict_multi_assignment';
3109 do $$
3110 declare
3111 x int;
3112 y int;
3113 begin
3114 select 1 into x, y;
3115 select 1,2 into x, y;
3116 select 1,2,3 into x, y;
3117 end
3118 $$;
3119 WARNING: number of source and target fields in assignment does not match
3120 DETAIL: strict_multi_assignment check of extra_warnings is active.
3121 HINT: Make sure the query returns the exact list of columns.
3122 WARNING: number of source and target fields in assignment does not match
3123 DETAIL: strict_multi_assignment check of extra_warnings is active.
3124 HINT: Make sure the query returns the exact list of columns.
3125 set plpgsql.extra_errors to 'strict_multi_assignment';
3126 do $$
3127 declare
3128 x int;
3129 y int;
3130 begin
3131 select 1 into x, y;
3132 select 1,2 into x, y;
3133 select 1,2,3 into x, y;
3134 end
3135 $$;
3136 ERROR: number of source and target fields in assignment does not match
3137 DETAIL: strict_multi_assignment check of extra_errors is active.
3138 HINT: Make sure the query returns the exact list of columns.
3139 CONTEXT: PL/pgSQL function inline_code_block line 6 at SQL statement
3140 create table test_01(a int, b int, c int);
3141 alter table test_01 drop column a;
3142 -- the check is active only when source table is not empty
3143 insert into test_01 values(10,20);
3144 do $$
3145 declare
3146 x int;
3147 y int;
3148 begin
3149 select * from test_01 into x, y; -- should be ok
3150 raise notice 'ok';
3151 select * from test_01 into x; -- should to fail
3152 end;
3153 $$;
3154 NOTICE: ok
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 8 at SQL statement
3159 do $$
3160 declare
3161 t test_01;
3162 begin
3163 select 1, 2 into t; -- should be ok
3164 raise notice 'ok';
3165 select 1, 2, 3 into t; -- should fail;
3166 end;
3167 $$;
3168 NOTICE: ok
3169 ERROR: number of source and target fields in assignment does not match
3170 DETAIL: strict_multi_assignment check of extra_errors is active.
3171 HINT: Make sure the query returns the exact list of columns.
3172 CONTEXT: PL/pgSQL function inline_code_block line 7 at SQL statement
3173 do $$
3174 declare
3175 t test_01;
3176 begin
3177 select 1 into t; -- should fail;
3178 end;
3179 $$;
3180 ERROR: number of source and target fields in assignment does not match
3181 DETAIL: strict_multi_assignment check of extra_errors is active.
3182 HINT: Make sure the query returns the exact list of columns.
3183 CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement
3184 drop table test_01;
3185 reset plpgsql.extra_errors;
3186 reset plpgsql.extra_warnings;
3187 -- test scrollable cursor support
3188 create function sc_test() returns setof integer as $$
3189 declare
3190 c scroll cursor for select f1 from int4_tbl;
3191 x integer;
3192 begin
3193 open c;
3194 fetch last from c into x;
3195 while found loop
3196 return next x;
3197 fetch prior from c into x;
3198 end loop;
3199 close c;
3200 end;
3201 $$ language plpgsql;
3202 select * from sc_test();
3203 sc_test
3204 -------------
3205 -2147483647
3206 2147483647
3207 -123456
3208 123456
3209 0
3210 (5 rows)
3212 create or replace function sc_test() returns setof integer as $$
3213 declare
3214 c no scroll cursor for select f1 from int4_tbl;
3215 x integer;
3216 begin
3217 open c;
3218 fetch last from c into x;
3219 while found loop
3220 return next x;
3221 fetch prior from c into x;
3222 end loop;
3223 close c;
3224 end;
3225 $$ language plpgsql;
3226 select * from sc_test(); -- fails because of NO SCROLL specification
3227 ERROR: cursor can only scan forward
3228 HINT: Declare it with SCROLL option to enable backward scan.
3229 CONTEXT: PL/pgSQL function sc_test() line 7 at FETCH
3230 create or replace function sc_test() returns setof integer as $$
3231 declare
3232 c refcursor;
3233 x integer;
3234 begin
3235 open c scroll for select f1 from int4_tbl;
3236 fetch last from c into x;
3237 while found loop
3238 return next x;
3239 fetch prior from c into x;
3240 end loop;
3241 close c;
3242 end;
3243 $$ language plpgsql;
3244 select * from sc_test();
3245 sc_test
3246 -------------
3247 -2147483647
3248 2147483647
3249 -123456
3250 123456
3251 0
3252 (5 rows)
3254 create or replace function sc_test() returns setof integer as $$
3255 declare
3256 c refcursor;
3257 x integer;
3258 begin
3259 open c scroll for execute 'select f1 from int4_tbl';
3260 fetch last from c into x;
3261 while found loop
3262 return next x;
3263 fetch relative -2 from c into x;
3264 end loop;
3265 close c;
3266 end;
3267 $$ language plpgsql;
3268 select * from sc_test();
3269 sc_test
3270 -------------
3271 -2147483647
3272 -123456
3273 0
3274 (3 rows)
3276 create or replace function sc_test() returns setof integer as $$
3277 declare
3278 c refcursor;
3279 x integer;
3280 begin
3281 open c scroll for execute 'select f1 from int4_tbl';
3282 fetch last from c into x;
3283 while found loop
3284 return next x;
3285 move backward 2 from c;
3286 fetch relative -1 from c into x;
3287 end loop;
3288 close c;
3289 end;
3290 $$ language plpgsql;
3291 select * from sc_test();
3292 sc_test
3293 -------------
3294 -2147483647
3295 123456
3296 (2 rows)
3298 create or replace function sc_test() returns setof integer as $$
3299 declare
3300 c cursor for select * from generate_series(1, 10);
3301 x integer;
3302 begin
3303 open c;
3304 loop
3305 move relative 2 in c;
3306 if not found then
3307 exit;
3308 end if;
3309 fetch next from c into x;
3310 if found then
3311 return next x;
3312 end if;
3313 end loop;
3314 close c;
3315 end;
3316 $$ language plpgsql;
3317 select * from sc_test();
3318 sc_test
3319 ---------
3320 3
3321 6
3322 9
3323 (3 rows)
3325 create or replace function sc_test() returns setof integer as $$
3326 declare
3327 c cursor for select * from generate_series(1, 10);
3328 x integer;
3329 begin
3330 open c;
3331 move forward all in c;
3332 fetch backward from c into x;
3333 if found then
3334 return next x;
3335 end if;
3336 close c;
3337 end;
3338 $$ language plpgsql;
3339 select * from sc_test();
3340 sc_test
3341 ---------
3342 10
3343 (1 row)
3345 drop function sc_test();
3346 -- test qualified variable names
3347 create function pl_qual_names (param1 int) returns void as $$
3348 <<outerblock>>
3349 declare
3350 param1 int := 1;
3351 begin
3352 <<innerblock>>
3353 declare
3354 param1 int := 2;
3355 begin
3356 raise notice 'param1 = %', param1;
3357 raise notice 'pl_qual_names.param1 = %', pl_qual_names.param1;
3358 raise notice 'outerblock.param1 = %', outerblock.param1;
3359 raise notice 'innerblock.param1 = %', innerblock.param1;
3360 end;
3361 end;
3362 $$ language plpgsql;
3363 select pl_qual_names(42);
3364 NOTICE: param1 = 2
3365 NOTICE: pl_qual_names.param1 = 42
3366 NOTICE: outerblock.param1 = 1
3367 NOTICE: innerblock.param1 = 2
3368 pl_qual_names
3369 ---------------
3371 (1 row)
3373 drop function pl_qual_names(int);
3374 -- tests for RETURN QUERY
3375 create function ret_query1(out int, out int) returns setof record as $$
3376 begin
3377 $1 := -1;
3378 $2 := -2;
3379 return next;
3380 return query select x + 1, x * 10 from generate_series(0, 10) s (x);
3381 return next;
3382 end;
3383 $$ language plpgsql;
3384 select * from ret_query1();
3385 column1 | column2
3386 ---------+---------
3387 -1 | -2
3388 1 | 0
3389 2 | 10
3390 3 | 20
3391 4 | 30
3392 5 | 40
3393 6 | 50
3394 7 | 60
3395 8 | 70
3396 9 | 80
3397 10 | 90
3398 11 | 100
3399 -1 | -2
3400 (13 rows)
3402 create type record_type as (x text, y int, z boolean);
3403 create or replace function ret_query2(lim int) returns setof record_type as $$
3404 begin
3405 return query select fipshash(s.x::text), s.x, s.x > 0
3406 from generate_series(-8, lim) s (x) where s.x % 2 = 0;
3407 end;
3408 $$ language plpgsql;
3409 select * from ret_query2(8);
3410 x | y | z
3411 ----------------------------------+----+---
3412 e91592205d3881e3ea35d66973bb4898 | -8 | f
3413 03b26944890929ff751653acb2f2af79 | -6 | f
3414 e5e0093f285a4fb94c3fcc2ad7fd04ed | -4 | f
3415 cf3bae39dd692048a8bf961182e6a34d | -2 | f
3416 5feceb66ffc86f38d952786c6d696c79 | 0 | f
3417 d4735e3a265e16eee03f59718b9b5d03 | 2 | t
3418 4b227777d4dd1fc61c6f884f48641d02 | 4 | t
3419 e7f6c011776e8db7cd330b54174fd76f | 6 | t
3420 2c624232cdd221771294dfbb310aca00 | 8 | t
3421 (9 rows)
3423 -- test EXECUTE USING
3424 create function exc_using(int, text) returns int as $$
3425 declare i int;
3426 begin
3427 for i in execute 'select * from generate_series(1,$1)' using $1+1 loop
3428 raise notice '%', i;
3429 end loop;
3430 execute 'select $2 + $2*3 + length($1)' into i using $2,$1;
3431 return i;
3432 end
3433 $$ language plpgsql;
3434 select exc_using(5, 'foobar');
3435 NOTICE: 1
3436 NOTICE: 2
3437 NOTICE: 3
3438 NOTICE: 4
3439 NOTICE: 5
3440 NOTICE: 6
3441 exc_using
3442 -----------
3443 26
3444 (1 row)
3446 drop function exc_using(int, text);
3447 create or replace function exc_using(int) returns void as $$
3448 declare
3449 c refcursor;
3450 i int;
3451 begin
3452 open c for execute 'select * from generate_series(1,$1)' using $1+1;
3453 loop
3454 fetch c into i;
3455 exit when not found;
3456 raise notice '%', i;
3457 end loop;
3458 close c;
3459 return;
3460 end;
3461 $$ language plpgsql;
3462 select exc_using(5);
3463 NOTICE: 1
3464 NOTICE: 2
3465 NOTICE: 3
3466 NOTICE: 4
3467 NOTICE: 5
3468 NOTICE: 6
3469 exc_using
3470 -----------
3472 (1 row)
3474 drop function exc_using(int);
3475 -- test FOR-over-cursor
3476 create or replace function forc01() returns void as $$
3477 declare
3478 c cursor(r1 integer, r2 integer)
3479 for select * from generate_series(r1,r2) i;
3480 c2 cursor
3481 for select * from generate_series(41,43) i;
3482 begin
3483 -- assign portal names to cursors to get stable output
3484 c := 'c';
3485 c2 := 'c2';
3486 for r in c(5,7) loop
3487 raise notice '% from %', r.i, c;
3488 end loop;
3489 -- again, to test if cursor was closed properly
3490 for r in c(9,10) loop
3491 raise notice '% from %', r.i, c;
3492 end loop;
3493 -- and test a parameterless cursor
3494 for r in c2 loop
3495 raise notice '% from %', r.i, c2;
3496 end loop;
3497 -- and try it with a hand-assigned name
3498 raise notice 'after loop, c2 = %', c2;
3499 c2 := 'special_name';
3500 for r in c2 loop
3501 raise notice '% from %', r.i, c2;
3502 end loop;
3503 raise notice 'after loop, c2 = %', c2;
3504 -- and try it with a generated name
3505 -- (which we can't show in the output because it's variable)
3506 c2 := null;
3507 for r in c2 loop
3508 raise notice '%', r.i;
3509 end loop;
3510 raise notice 'after loop, c2 = %', c2;
3511 return;
3512 end;
3513 $$ language plpgsql;
3514 select forc01();
3515 NOTICE: 5 from c
3516 NOTICE: 6 from c
3517 NOTICE: 7 from c
3518 NOTICE: 9 from c
3519 NOTICE: 10 from c
3520 NOTICE: 41 from c2
3521 NOTICE: 42 from c2
3522 NOTICE: 43 from c2
3523 NOTICE: after loop, c2 = c2
3524 NOTICE: 41 from special_name
3525 NOTICE: 42 from special_name
3526 NOTICE: 43 from special_name
3527 NOTICE: after loop, c2 = special_name
3528 NOTICE: 41
3529 NOTICE: 42
3530 NOTICE: 43
3531 NOTICE: after loop, c2 = <NULL>
3532 forc01
3533 --------
3535 (1 row)
3537 -- try updating the cursor's current row
3538 create temp table forc_test as
3539 select n as i, n as j from generate_series(1,10) n;
3540 create or replace function forc01() returns void as $$
3541 declare
3542 c cursor for select * from forc_test;
3543 begin
3544 for r in c loop
3545 raise notice '%, %', r.i, r.j;
3546 update forc_test set i = i * 100, j = r.j * 2 where current of c;
3547 end loop;
3548 end;
3549 $$ language plpgsql;
3550 select forc01();
3551 NOTICE: 1, 1
3552 NOTICE: 2, 2
3553 NOTICE: 3, 3
3554 NOTICE: 4, 4
3555 NOTICE: 5, 5
3556 NOTICE: 6, 6
3557 NOTICE: 7, 7
3558 NOTICE: 8, 8
3559 NOTICE: 9, 9
3560 NOTICE: 10, 10
3561 forc01
3562 --------
3564 (1 row)
3566 select * from forc_test;
3567 i | j
3568 ------+----
3569 100 | 2
3570 200 | 4
3571 300 | 6
3572 400 | 8
3573 500 | 10
3574 600 | 12
3575 700 | 14
3576 800 | 16
3577 900 | 18
3578 1000 | 20
3579 (10 rows)
3581 -- same, with a cursor whose portal name doesn't match variable name
3582 create or replace function forc01() returns void as $$
3583 declare
3584 c refcursor := 'fooled_ya';
3585 r record;
3586 begin
3587 open c for select * from forc_test;
3588 loop
3589 fetch c into r;
3590 exit when not found;
3591 raise notice '%, %', r.i, r.j;
3592 update forc_test set i = i * 100, j = r.j * 2 where current of c;
3593 end loop;
3594 end;
3595 $$ language plpgsql;
3596 select forc01();
3597 NOTICE: 100, 2
3598 NOTICE: 200, 4
3599 NOTICE: 300, 6
3600 NOTICE: 400, 8
3601 NOTICE: 500, 10
3602 NOTICE: 600, 12
3603 NOTICE: 700, 14
3604 NOTICE: 800, 16
3605 NOTICE: 900, 18
3606 NOTICE: 1000, 20
3607 forc01
3608 --------
3610 (1 row)
3612 select * from forc_test;
3613 i | j
3614 --------+----
3615 10000 | 4
3616 20000 | 8
3617 30000 | 12
3618 40000 | 16
3619 50000 | 20
3620 60000 | 24
3621 70000 | 28
3622 80000 | 32
3623 90000 | 36
3624 100000 | 40
3625 (10 rows)
3627 drop function forc01();
3628 -- it's okay to re-use a cursor variable name, even when bound
3629 do $$
3630 declare cnt int := 0;
3631 c1 cursor for select * from forc_test;
3632 begin
3633 for r1 in c1 loop
3634 declare c1 cursor for select * from forc_test;
3635 begin
3636 for r2 in c1 loop
3637 cnt := cnt + 1;
3638 end loop;
3639 end;
3640 end loop;
3641 raise notice 'cnt = %', cnt;
3642 end $$;
3643 NOTICE: cnt = 100
3644 -- fail because cursor has no query bound to it
3645 create or replace function forc_bad() returns void as $$
3646 declare
3647 c refcursor;
3648 begin
3649 for r in c loop
3650 raise notice '%', r.i;
3651 end loop;
3652 end;
3653 $$ language plpgsql;
3654 ERROR: cursor FOR loop must use a bound cursor variable
3655 LINE 5: for r in c loop
3656 ^
3657 -- test RETURN QUERY EXECUTE
3658 create or replace function return_dquery()
3659 returns setof int as $$
3660 begin
3661 return query execute 'select * from (values(10),(20)) f';
3662 return query execute 'select * from (values($1),($2)) f' using 40,50;
3663 end;
3664 $$ language plpgsql;
3665 select * from return_dquery();
3666 return_dquery
3667 ---------------
3668 10
3669 20
3670 40
3671 50
3672 (4 rows)
3674 drop function return_dquery();
3675 -- test RETURN QUERY with dropped columns
3676 create table tabwithcols(a int, b int, c int, d int);
3677 insert into tabwithcols values(10,20,30,40),(50,60,70,80);
3678 create or replace function returnqueryf()
3679 returns setof tabwithcols as $$
3680 begin
3681 return query select * from tabwithcols;
3682 return query execute 'select * from tabwithcols';
3683 end;
3684 $$ language plpgsql;
3685 select * from returnqueryf();
3686 a | b | c | d
3687 ----+----+----+----
3688 10 | 20 | 30 | 40
3689 50 | 60 | 70 | 80
3690 10 | 20 | 30 | 40
3691 50 | 60 | 70 | 80
3692 (4 rows)
3694 alter table tabwithcols drop column b;
3695 select * from returnqueryf();
3696 a | c | d
3697 ----+----+----
3698 10 | 30 | 40
3699 50 | 70 | 80
3700 10 | 30 | 40
3701 50 | 70 | 80
3702 (4 rows)
3704 alter table tabwithcols drop column d;
3705 select * from returnqueryf();
3706 a | c
3707 ----+----
3708 10 | 30
3709 50 | 70
3710 10 | 30
3711 50 | 70
3712 (4 rows)
3714 alter table tabwithcols add column d int;
3715 select * from returnqueryf();
3716 a | c | d
3717 ----+----+---
3718 10 | 30 |
3719 50 | 70 |
3720 10 | 30 |
3721 50 | 70 |
3722 (4 rows)
3724 drop function returnqueryf();
3725 drop table tabwithcols;
3726 --
3727 -- Tests for composite-type results
3728 --
3729 create type compostype as (x int, y varchar);
3730 -- test: use of variable of composite type in return statement
3731 create or replace function compos() returns compostype as $$
3732 declare
3733 v compostype;
3734 begin
3735 v := (1, 'hello');
3736 return v;
3737 end;
3738 $$ language plpgsql;
3739 select compos();
3740 compos
3741 -----------
3742 (1,hello)
3743 (1 row)
3745 -- test: use of variable of record type in return statement
3746 create or replace function compos() returns compostype as $$
3747 declare
3748 v record;
3749 begin
3750 v := (1, 'hello'::varchar);
3751 return v;
3752 end;
3753 $$ language plpgsql;
3754 select compos();
3755 compos
3756 -----------
3757 (1,hello)
3758 (1 row)
3760 -- test: use of row expr in return statement
3761 create or replace function compos() returns compostype as $$
3762 begin
3763 return (1, 'hello'::varchar);
3764 end;
3765 $$ language plpgsql;
3766 select compos();
3767 compos
3768 -----------
3769 (1,hello)
3770 (1 row)
3772 -- this does not work currently (no implicit casting)
3773 create or replace function compos() returns compostype as $$
3774 begin
3775 return (1, 'hello');
3776 end;
3777 $$ language plpgsql;
3778 select compos();
3779 ERROR: returned record type does not match expected record type
3780 DETAIL: Returned type unknown does not match expected type character varying in column 2.
3781 CONTEXT: PL/pgSQL function compos() while casting return value to function's return type
3782 -- ... but this does
3783 create or replace function compos() returns compostype as $$
3784 begin
3785 return (1, 'hello')::compostype;
3786 end;
3787 $$ language plpgsql;
3788 select compos();
3789 compos
3790 -----------
3791 (1,hello)
3792 (1 row)
3794 drop function compos();
3795 -- test: return a row expr as record.
3796 create or replace function composrec() returns record as $$
3797 declare
3798 v record;
3799 begin
3800 v := (1, 'hello');
3801 return v;
3802 end;
3803 $$ language plpgsql;
3804 select composrec();
3805 composrec
3806 -----------
3807 (1,hello)
3808 (1 row)
3810 -- test: return row expr in return statement.
3811 create or replace function composrec() returns record as $$
3812 begin
3813 return (1, 'hello');
3814 end;
3815 $$ language plpgsql;
3816 select composrec();
3817 composrec
3818 -----------
3819 (1,hello)
3820 (1 row)
3822 drop function composrec();
3823 -- test: row expr in RETURN NEXT statement.
3824 create or replace function compos() returns setof compostype as $$
3825 begin
3826 for i in 1..3
3827 loop
3828 return next (1, 'hello'::varchar);
3829 end loop;
3830 return next null::compostype;
3831 return next (2, 'goodbye')::compostype;
3832 end;
3833 $$ language plpgsql;
3834 select * from compos();
3835 x | y
3836 ---+---------
3837 1 | hello
3838 1 | hello
3839 1 | hello
3840 |
3841 2 | goodbye
3842 (5 rows)
3844 drop function compos();
3845 -- test: use invalid expr in return statement.
3846 create or replace function compos() returns compostype as $$
3847 begin
3848 return 1 + 1;
3849 end;
3850 $$ language plpgsql;
3851 select compos();
3852 ERROR: cannot return non-composite value from function returning composite type
3853 CONTEXT: PL/pgSQL function compos() line 3 at RETURN
3854 -- RETURN variable is a different code path ...
3855 create or replace function compos() returns compostype as $$
3856 declare x int := 42;
3857 begin
3858 return x;
3859 end;
3860 $$ language plpgsql;
3861 select * from compos();
3862 ERROR: cannot return non-composite value from function returning composite type
3863 CONTEXT: PL/pgSQL function compos() line 4 at RETURN
3864 drop function compos();
3865 -- test: invalid use of composite variable in scalar-returning function
3866 create or replace function compos() returns int as $$
3867 declare
3868 v compostype;
3869 begin
3870 v := (1, 'hello');
3871 return v;
3872 end;
3873 $$ language plpgsql;
3874 select compos();
3875 ERROR: invalid input syntax for type integer: "(1,hello)"
3876 CONTEXT: PL/pgSQL function compos() while casting return value to function's return type
3877 -- test: invalid use of composite expression in scalar-returning function
3878 create or replace function compos() returns int as $$
3879 begin
3880 return (1, 'hello')::compostype;
3881 end;
3882 $$ language plpgsql;
3883 select compos();
3884 ERROR: invalid input syntax for type integer: "(1,hello)"
3885 CONTEXT: PL/pgSQL function compos() while casting return value to function's return type
3886 drop function compos();
3887 drop type compostype;
3888 --
3889 -- Tests for 8.4's new RAISE features
3890 --
3891 create or replace function raise_test() returns void as $$
3892 begin
3893 raise notice '% % %', 1, 2, 3
3894 using errcode = '55001', detail = 'some detail info', hint = 'some hint';
3895 raise '% % %', 1, 2, 3
3896 using errcode = 'division_by_zero', detail = 'some detail info';
3897 end;
3898 $$ language plpgsql;
3899 select raise_test();
3900 NOTICE: 1 2 3
3901 DETAIL: some detail info
3902 HINT: some hint
3903 ERROR: 1 2 3
3904 DETAIL: some detail info
3905 CONTEXT: PL/pgSQL function raise_test() line 5 at RAISE
3906 -- Since we can't actually see the thrown SQLSTATE in default psql output,
3907 -- test it like this; this also tests re-RAISE
3908 create or replace function raise_test() returns void as $$
3909 begin
3910 raise 'check me'
3911 using errcode = 'division_by_zero', detail = 'some detail info';
3912 exception
3913 when others then
3914 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3915 raise;
3916 end;
3917 $$ language plpgsql;
3918 select raise_test();
3919 NOTICE: SQLSTATE: 22012 SQLERRM: check me
3920 ERROR: check me
3921 DETAIL: some detail info
3922 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3923 create or replace function raise_test() returns void as $$
3924 begin
3925 raise 'check me'
3926 using errcode = '1234F', detail = 'some detail info';
3927 exception
3928 when others then
3929 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3930 raise;
3931 end;
3932 $$ language plpgsql;
3933 select raise_test();
3934 NOTICE: SQLSTATE: 1234F SQLERRM: check me
3935 ERROR: check me
3936 DETAIL: some detail info
3937 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3938 -- SQLSTATE specification in WHEN
3939 create or replace function raise_test() returns void as $$
3940 begin
3941 raise 'check me'
3942 using errcode = '1234F', detail = 'some detail info';
3943 exception
3944 when sqlstate '1234F' then
3945 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3946 raise;
3947 end;
3948 $$ language plpgsql;
3949 select raise_test();
3950 NOTICE: SQLSTATE: 1234F SQLERRM: check me
3951 ERROR: check me
3952 DETAIL: some detail info
3953 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3954 create or replace function raise_test() returns void as $$
3955 begin
3956 raise division_by_zero using detail = 'some detail info';
3957 exception
3958 when others then
3959 raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
3960 raise;
3961 end;
3962 $$ language plpgsql;
3963 select raise_test();
3964 NOTICE: SQLSTATE: 22012 SQLERRM: division_by_zero
3965 ERROR: division_by_zero
3966 DETAIL: some detail info
3967 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3968 create or replace function raise_test() returns void as $$
3969 begin
3970 raise division_by_zero;
3971 end;
3972 $$ language plpgsql;
3973 select raise_test();
3974 ERROR: division_by_zero
3975 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3976 create or replace function raise_test() returns void as $$
3977 begin
3978 raise sqlstate '1234F';
3979 end;
3980 $$ language plpgsql;
3981 select raise_test();
3982 ERROR: 1234F
3983 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3984 create or replace function raise_test() returns void as $$
3985 begin
3986 raise division_by_zero using message = 'custom' || ' message';
3987 end;
3988 $$ language plpgsql;
3989 select raise_test();
3990 ERROR: custom message
3991 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
3992 create or replace function raise_test() returns void as $$
3993 begin
3994 raise using message = 'custom' || ' message', errcode = '22012';
3995 end;
3996 $$ language plpgsql;
3997 select raise_test();
3998 ERROR: custom message
3999 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
4000 -- conflict on message
4001 create or replace function raise_test() returns void as $$
4002 begin
4003 raise notice 'some message' using message = 'custom' || ' message', errcode = '22012';
4004 end;
4005 $$ language plpgsql;
4006 select raise_test();
4007 ERROR: RAISE option already specified: MESSAGE
4008 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
4009 -- conflict on errcode
4010 create or replace function raise_test() returns void as $$
4011 begin
4012 raise division_by_zero using message = 'custom' || ' message', errcode = '22012';
4013 end;
4014 $$ language plpgsql;
4015 select raise_test();
4016 ERROR: RAISE option already specified: ERRCODE
4017 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
4018 -- nothing to re-RAISE
4019 create or replace function raise_test() returns void as $$
4020 begin
4021 raise;
4022 end;
4023 $$ language plpgsql;
4024 select raise_test();
4025 ERROR: RAISE without parameters cannot be used outside an exception handler
4026 CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE
4027 -- test access to exception data
4028 create function zero_divide() returns int as $$
4029 declare v int := 0;
4030 begin
4031 return 10 / v;
4032 end;
4033 $$ language plpgsql parallel safe;
4034 create or replace function raise_test() returns void as $$
4035 begin
4036 raise exception 'custom exception'
4037 using detail = 'some detail of custom exception',
4038 hint = 'some hint related to custom exception';
4039 end;
4040 $$ language plpgsql;
4041 create function stacked_diagnostics_test() returns void as $$
4042 declare _sqlstate text;
4043 _message text;
4044 _context text;
4045 begin
4046 perform zero_divide();
4047 exception when others then
4048 get stacked diagnostics
4049 _sqlstate = returned_sqlstate,
4050 _message = message_text,
4051 _context = pg_exception_context;
4052 raise notice 'sqlstate: %, message: %, context: [%]',
4053 _sqlstate, _message, replace(_context, E'\n', ' <- ');
4054 end;
4055 $$ language plpgsql;
4056 select stacked_diagnostics_test();
4057 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]
4058 stacked_diagnostics_test
4059 --------------------------
4061 (1 row)
4063 create or replace function stacked_diagnostics_test() returns void as $$
4064 declare _detail text;
4065 _hint text;
4066 _message text;
4067 begin
4068 perform raise_test();
4069 exception when others then
4070 get stacked diagnostics
4071 _message = message_text,
4072 _detail = pg_exception_detail,
4073 _hint = pg_exception_hint;
4074 raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
4075 end;
4076 $$ language plpgsql;
4077 select stacked_diagnostics_test();
4078 NOTICE: message: custom exception, detail: some detail of custom exception, hint: some hint related to custom exception
4079 stacked_diagnostics_test
4080 --------------------------
4082 (1 row)
4084 -- fail, cannot use stacked diagnostics statement outside handler
4085 create or replace function stacked_diagnostics_test() returns void as $$
4086 declare _detail text;
4087 _hint text;
4088 _message text;
4089 begin
4090 get stacked diagnostics
4091 _message = message_text,
4092 _detail = pg_exception_detail,
4093 _hint = pg_exception_hint;
4094 raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
4095 end;
4096 $$ language plpgsql;
4097 select stacked_diagnostics_test();
4098 ERROR: GET STACKED DIAGNOSTICS cannot be used outside an exception handler
4099 CONTEXT: PL/pgSQL function stacked_diagnostics_test() line 6 at GET STACKED DIAGNOSTICS
4100 drop function stacked_diagnostics_test();
4101 -- Test that an error recovery subtransaction is parallel safe
4102 create function error_trap_test() returns text as $$
4103 begin
4104 perform zero_divide();
4105 return 'no error detected!';
4106 exception when division_by_zero then
4107 return 'division_by_zero detected';
4108 end;
4109 $$ language plpgsql parallel safe;
4110 set debug_parallel_query to on;
4111 explain (verbose, costs off) select error_trap_test();
4112 QUERY PLAN
4113 -----------------------------------
4114 Gather
4115 Output: (error_trap_test())
4116 Workers Planned: 1
4117 Single Copy: true
4118 -> Result
4119 Output: error_trap_test()
4120 (6 rows)
4122 select error_trap_test();
4123 error_trap_test
4124 ---------------------------
4125 division_by_zero detected
4126 (1 row)
4128 reset debug_parallel_query;
4129 drop function error_trap_test();
4130 drop function zero_divide();
4131 -- check cases where implicit SQLSTATE variable could be confused with
4132 -- SQLSTATE as a keyword, cf bug #5524
4133 create or replace function raise_test() returns void as $$
4134 begin
4135 perform 1/0;
4136 exception
4137 when sqlstate '22012' then
4138 raise notice using message = sqlstate;
4139 raise sqlstate '22012' using message = 'substitute message';
4140 end;
4141 $$ language plpgsql;
4142 select raise_test();
4143 NOTICE: 22012
4144 ERROR: substitute message
4145 CONTEXT: PL/pgSQL function raise_test() line 7 at RAISE
4146 drop function raise_test();
4147 -- test passing column_name, constraint_name, datatype_name, table_name
4148 -- and schema_name error fields
4149 create or replace function stacked_diagnostics_test() returns void as $$
4150 declare _column_name text;
4151 _constraint_name text;
4152 _datatype_name text;
4153 _table_name text;
4154 _schema_name text;
4155 begin
4156 raise exception using
4157 column = '>>some column name<<',
4158 constraint = '>>some constraint name<<',
4159 datatype = '>>some datatype name<<',
4160 table = '>>some table name<<',
4161 schema = '>>some schema name<<';
4162 exception when others then
4163 get stacked diagnostics
4164 _column_name = column_name,
4165 _constraint_name = constraint_name,
4166 _datatype_name = pg_datatype_name,
4167 _table_name = table_name,
4168 _schema_name = schema_name;
4169 raise notice 'column %, constraint %, type %, table %, schema %',
4170 _column_name, _constraint_name, _datatype_name, _table_name, _schema_name;
4171 end;
4172 $$ language plpgsql;
4173 select stacked_diagnostics_test();
4174 NOTICE: column >>some column name<<, constraint >>some constraint name<<, type >>some datatype name<<, table >>some table name<<, schema >>some schema name<<
4175 stacked_diagnostics_test
4176 --------------------------
4178 (1 row)
4180 drop function stacked_diagnostics_test();
4181 -- test variadic functions
4182 create or replace function vari(variadic int[])
4183 returns void as $$
4184 begin
4185 for i in array_lower($1,1)..array_upper($1,1) loop
4186 raise notice '%', $1[i];
4187 end loop; end;
4188 $$ language plpgsql;
4189 select vari(1,2,3,4,5);
4190 NOTICE: 1
4191 NOTICE: 2
4192 NOTICE: 3
4193 NOTICE: 4
4194 NOTICE: 5
4195 vari
4196 ------
4198 (1 row)
4200 select vari(3,4,5);
4201 NOTICE: 3
4202 NOTICE: 4
4203 NOTICE: 5
4204 vari
4205 ------
4207 (1 row)
4209 select vari(variadic array[5,6,7]);
4210 NOTICE: 5
4211 NOTICE: 6
4212 NOTICE: 7
4213 vari
4214 ------
4216 (1 row)
4218 drop function vari(int[]);
4219 -- coercion test
4220 create or replace function pleast(variadic numeric[])
4221 returns numeric as $$
4222 declare aux numeric = $1[array_lower($1,1)];
4223 begin
4224 for i in array_lower($1,1)+1..array_upper($1,1) loop
4225 if $1[i] < aux then aux := $1[i]; end if;
4226 end loop;
4227 return aux;
4228 end;
4229 $$ language plpgsql immutable strict;
4230 select pleast(10,1,2,3,-16);
4231 pleast
4232 --------
4233 -16
4234 (1 row)
4236 select pleast(10.2,2.2,-1.1);
4237 pleast
4238 --------
4239 -1.1
4240 (1 row)
4242 select pleast(10.2,10, -20);
4243 pleast
4244 --------
4245 -20
4246 (1 row)
4248 select pleast(10,20, -1.0);
4249 pleast
4250 --------
4251 -1.0
4252 (1 row)
4254 -- in case of conflict, non-variadic version is preferred
4255 create or replace function pleast(numeric)
4256 returns numeric as $$
4257 begin
4258 raise notice 'non-variadic function called';
4259 return $1;
4260 end;
4261 $$ language plpgsql immutable strict;
4262 select pleast(10);
4263 NOTICE: non-variadic function called
4264 pleast
4265 --------
4266 10
4267 (1 row)
4269 drop function pleast(numeric[]);
4270 drop function pleast(numeric);
4271 -- test table functions
4272 create function tftest(int) returns table(a int, b int) as $$
4273 begin
4274 return query select $1, $1+i from generate_series(1,5) g(i);
4275 end;
4276 $$ language plpgsql immutable strict;
4277 select * from tftest(10);
4278 a | b
4279 ----+----
4280 10 | 11
4281 10 | 12
4282 10 | 13
4283 10 | 14
4284 10 | 15
4285 (5 rows)
4287 create or replace function tftest(a1 int) returns table(a int, b int) as $$
4288 begin
4289 a := a1; b := a1 + 1;
4290 return next;
4291 a := a1 * 10; b := a1 * 10 + 1;
4292 return next;
4293 end;
4294 $$ language plpgsql immutable strict;
4295 select * from tftest(10);
4296 a | b
4297 -----+-----
4298 10 | 11
4299 100 | 101
4300 (2 rows)
4302 drop function tftest(int);
4303 create function rttest()
4304 returns setof int as $$
4305 declare rc int;
4306 begin
4307 return query values(10),(20);
4308 get diagnostics rc = row_count;
4309 raise notice '% %', found, rc;
4310 return query select * from (values(10),(20)) f(a) where false;
4311 get diagnostics rc = row_count;
4312 raise notice '% %', found, rc;
4313 return query execute 'values(10),(20)';
4314 get diagnostics rc = row_count;
4315 raise notice '% %', found, rc;
4316 return query execute 'select * from (values(10),(20)) f(a) where false';
4317 get diagnostics rc = row_count;
4318 raise notice '% %', found, rc;
4319 end;
4320 $$ language plpgsql;
4321 select * from rttest();
4322 NOTICE: t 2
4323 NOTICE: f 0
4324 NOTICE: t 2
4325 NOTICE: f 0
4326 rttest
4327 --------
4328 10
4329 20
4330 10
4331 20
4332 (4 rows)
4334 -- check some error cases, too
4335 create or replace function rttest()
4336 returns setof int as $$
4337 begin
4338 return query select 10 into no_such_table;
4339 end;
4340 $$ language plpgsql;
4341 select * from rttest();
4342 ERROR: SELECT INTO query does not return tuples
4343 CONTEXT: SQL statement "select 10 into no_such_table"
4344 PL/pgSQL function rttest() line 3 at RETURN QUERY
4345 create or replace function rttest()
4346 returns setof int as $$
4347 begin
4348 return query execute 'select 10 into no_such_table';
4349 end;
4350 $$ language plpgsql;
4351 select * from rttest();
4352 ERROR: SELECT INTO query does not return tuples
4353 CONTEXT: SQL statement "select 10 into no_such_table"
4354 PL/pgSQL function rttest() line 3 at RETURN QUERY
4355 select * from no_such_table;
4356 ERROR: relation "no_such_table" does not exist
4357 LINE 1: select * from no_such_table;
4358 ^
4359 drop function rttest();
4360 -- Test for proper cleanup at subtransaction exit. This example
4361 -- exposed a bug in PG 8.2.
4362 CREATE FUNCTION leaker_1(fail BOOL) RETURNS INTEGER AS $$
4363 DECLARE
4364 v_var INTEGER;
4365 BEGIN
4366 BEGIN
4367 v_var := (leaker_2(fail)).error_code;
4368 EXCEPTION
4369 WHEN others THEN RETURN 0;
4370 END;
4371 RETURN 1;
4372 END;
4373 $$ LANGUAGE plpgsql;
4374 CREATE FUNCTION leaker_2(fail BOOL, OUT error_code INTEGER, OUT new_id INTEGER)
4375 RETURNS RECORD AS $$
4376 BEGIN
4377 IF fail THEN
4378 RAISE EXCEPTION 'fail ...';
4379 END IF;
4380 error_code := 1;
4381 new_id := 1;
4382 RETURN;
4383 END;
4384 $$ LANGUAGE plpgsql;
4385 SELECT * FROM leaker_1(false);
4386 leaker_1
4387 ----------
4388 1
4389 (1 row)
4391 SELECT * FROM leaker_1(true);
4392 leaker_1
4393 ----------
4394 0
4395 (1 row)
4397 DROP FUNCTION leaker_1(bool);
4398 DROP FUNCTION leaker_2(bool);
4399 -- Test for appropriate cleanup of non-simple expression evaluations
4400 -- (bug in all versions prior to August 2010)
4401 CREATE FUNCTION nonsimple_expr_test() RETURNS text[] AS $$
4402 DECLARE
4403 arr text[];
4404 lr text;
4405 i integer;
4406 BEGIN
4407 arr := array[array['foo','bar'], array['baz', 'quux']];
4408 lr := 'fool';
4409 i := 1;
4410 -- use sub-SELECTs to make expressions non-simple
4411 arr[(SELECT i)][(SELECT i+1)] := (SELECT lr);
4412 RETURN arr;
4413 END;
4414 $$ LANGUAGE plpgsql;
4415 SELECT nonsimple_expr_test();
4416 nonsimple_expr_test
4417 -------------------------
4418 {{foo,fool},{baz,quux}}
4419 (1 row)
4421 DROP FUNCTION nonsimple_expr_test();
4422 CREATE FUNCTION nonsimple_expr_test() RETURNS integer AS $$
4423 declare
4424 i integer NOT NULL := 0;
4425 begin
4426 begin
4427 i := (SELECT NULL::integer); -- should throw error
4428 exception
4429 WHEN OTHERS THEN
4430 i := (SELECT 1::integer);
4431 end;
4432 return i;
4433 end;
4434 $$ LANGUAGE plpgsql;
4435 SELECT nonsimple_expr_test();
4436 nonsimple_expr_test
4437 ---------------------
4438 1
4439 (1 row)
4441 DROP FUNCTION nonsimple_expr_test();
4442 --
4443 -- Test cases involving recursion and error recovery in simple expressions
4444 -- (bugs in all versions before October 2010). The problems are most
4445 -- easily exposed by mutual recursion between plpgsql and sql functions.
4446 --
4447 create function recurse(float8) returns float8 as
4448 $$
4449 begin
4450 if ($1 > 0) then
4451 return sql_recurse($1 - 1);
4452 else
4453 return $1;
4454 end if;
4455 end;
4456 $$ language plpgsql;
4457 -- "limit" is to prevent this from being inlined
4458 create function sql_recurse(float8) returns float8 as
4459 $$ select recurse($1) limit 1; $$ language sql;
4460 select recurse(10);
4461 recurse
4462 ---------
4463 0
4464 (1 row)
4466 create function error1(text) returns text language sql as
4467 $$ SELECT relname::text FROM pg_class c WHERE c.oid = $1::regclass $$;
4468 create function error2(p_name_table text) returns text language plpgsql as $$
4469 begin
4470 return error1(p_name_table);
4471 end$$;
4472 BEGIN;
4473 create table public.stuffs (stuff text);
4474 SAVEPOINT a;
4475 select error2('nonexistent.stuffs');
4476 ERROR: schema "nonexistent" does not exist
4477 CONTEXT: SQL function "error1" statement 1
4478 PL/pgSQL function error2(text) line 3 at RETURN
4479 ROLLBACK TO a;
4480 select error2('public.stuffs');
4481 error2
4482 --------
4483 stuffs
4484 (1 row)
4486 rollback;
4487 drop function error2(p_name_table text);
4488 drop function error1(text);
4489 -- Test for proper handling of cast-expression caching
4490 create function sql_to_date(integer) returns date as $$
4491 select $1::text::date
4492 $$ language sql immutable strict;
4493 create cast (integer as date) with function sql_to_date(integer) as assignment;
4494 create function cast_invoker(integer) returns date as $$
4495 begin
4496 return $1;
4497 end$$ language plpgsql;
4498 select cast_invoker(20150717);
4499 cast_invoker
4500 --------------
4501 07-17-2015
4502 (1 row)
4504 select cast_invoker(20150718); -- second call crashed in pre-release 9.5
4505 cast_invoker
4506 --------------
4507 07-18-2015
4508 (1 row)
4510 begin;
4511 select cast_invoker(20150717);
4512 cast_invoker
4513 --------------
4514 07-17-2015
4515 (1 row)
4517 select cast_invoker(20150718);
4518 cast_invoker
4519 --------------
4520 07-18-2015
4521 (1 row)
4523 savepoint s1;
4524 select cast_invoker(20150718);
4525 cast_invoker
4526 --------------
4527 07-18-2015
4528 (1 row)
4530 select cast_invoker(-1); -- fails
4531 ERROR: invalid input syntax for type date: "-1"
4532 CONTEXT: SQL function "sql_to_date" statement 1
4533 PL/pgSQL function cast_invoker(integer) while casting return value to function's return type
4534 rollback to savepoint s1;
4535 select cast_invoker(20150719);
4536 cast_invoker
4537 --------------
4538 07-19-2015
4539 (1 row)
4541 select cast_invoker(20150720);
4542 cast_invoker
4543 --------------
4544 07-20-2015
4545 (1 row)
4547 commit;
4548 drop function cast_invoker(integer);
4549 drop function sql_to_date(integer) cascade;
4550 NOTICE: drop cascades to cast from integer to date
4551 -- Test handling of cast cache inside DO blocks
4552 -- (to check the original crash case, this must be a cast not previously
4553 -- used in this session)
4554 begin;
4555 do $$ declare x text[]; begin x := '{1.23, 4.56}'::numeric[]; end $$;
4556 do $$ declare x text[]; begin x := '{1.23, 4.56}'::numeric[]; end $$;
4557 end;
4558 -- Test for consistent reporting of error context
4559 create function fail() returns int language plpgsql as $$
4560 begin
4561 return 1/0;
4562 end
4563 $$;
4564 select fail();
4565 ERROR: division by zero
4566 CONTEXT: PL/pgSQL expression "1/0"
4567 PL/pgSQL function fail() line 3 at RETURN
4568 select fail();
4569 ERROR: division by zero
4570 CONTEXT: PL/pgSQL expression "1/0"
4571 PL/pgSQL function fail() line 3 at RETURN
4572 drop function fail();
4573 -- Test handling of string literals.
4574 set standard_conforming_strings = off;
4575 create or replace function strtest() returns text as $$
4576 begin
4577 raise notice 'foo\\bar\041baz';
4578 return 'foo\\bar\041baz';
4579 end
4580 $$ language plpgsql;
4581 WARNING: nonstandard use of \\ in a string literal
4582 LINE 3: raise notice 'foo\\bar\041baz';
4583 ^
4584 HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
4585 WARNING: nonstandard use of \\ in a string literal
4586 LINE 4: return 'foo\\bar\041baz';
4587 ^
4588 HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
4589 WARNING: nonstandard use of \\ in a string literal
4590 LINE 4: return 'foo\\bar\041baz';
4591 ^
4592 HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
4593 select strtest();
4594 NOTICE: foo\bar!baz
4595 WARNING: nonstandard use of \\ in a string literal
4596 LINE 1: 'foo\\bar\041baz'
4597 ^
4598 HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
4599 QUERY: 'foo\\bar\041baz'
4600 strtest
4601 -------------
4602 foo\bar!baz
4603 (1 row)
4605 create or replace function strtest() returns text as $$
4606 begin
4607 raise notice E'foo\\bar\041baz';
4608 return E'foo\\bar\041baz';
4609 end
4610 $$ language plpgsql;
4611 select strtest();
4612 NOTICE: foo\bar!baz
4613 strtest
4614 -------------
4615 foo\bar!baz
4616 (1 row)
4618 set standard_conforming_strings = on;
4619 create or replace function strtest() returns text as $$
4620 begin
4621 raise notice 'foo\\bar\041baz\';
4622 return 'foo\\bar\041baz\';
4623 end
4624 $$ language plpgsql;
4625 select strtest();
4626 NOTICE: foo\\bar\041baz\
4627 strtest
4628 ------------------
4629 foo\\bar\041baz\
4630 (1 row)
4632 create or replace function strtest() returns text as $$
4633 begin
4634 raise notice E'foo\\bar\041baz';
4635 return E'foo\\bar\041baz';
4636 end
4637 $$ language plpgsql;
4638 select strtest();
4639 NOTICE: foo\bar!baz
4640 strtest
4641 -------------
4642 foo\bar!baz
4643 (1 row)
4645 drop function strtest();
4646 -- Test anonymous code blocks.
4647 DO $$
4648 DECLARE r record;
4649 BEGIN
4650 FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno
4651 LOOP
4652 RAISE NOTICE '%, %', r.roomno, r.comment;
4653 END LOOP;
4654 END$$;
4655 NOTICE: 001, Entrance
4656 NOTICE: 002, Office
4657 NOTICE: 003, Office
4658 NOTICE: 004, Technical
4659 NOTICE: 101, Office
4660 NOTICE: 102, Conference
4661 NOTICE: 103, Restroom
4662 NOTICE: 104, Technical
4663 NOTICE: 105, Office
4664 NOTICE: 106, Office
4665 -- these are to check syntax error reporting
4666 DO LANGUAGE plpgsql $$begin return 1; end$$;
4667 ERROR: RETURN cannot have a parameter in function returning void
4668 LINE 1: DO LANGUAGE plpgsql $$begin return 1; end$$;
4669 ^
4670 DO $$
4671 DECLARE r record;
4672 BEGIN
4673 FOR r IN SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno
4674 LOOP
4675 RAISE NOTICE '%, %', r.roomno, r.comment;
4676 END LOOP;
4677 END$$;
4678 ERROR: column "foo" does not exist
4679 LINE 1: SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomn...
4680 ^
4681 QUERY: SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno
4682 CONTEXT: PL/pgSQL function inline_code_block line 4 at FOR over SELECT rows
4683 -- Check handling of errors thrown from/into anonymous code blocks.
4684 do $outer$
4685 begin
4686 for i in 1..10 loop
4687 begin
4688 execute $ex$
4689 do $$
4690 declare x int = 0;
4691 begin
4692 x := 1 / x;
4693 end;
4694 $$;
4695 $ex$;
4696 exception when division_by_zero then
4697 raise notice 'caught division by zero';
4698 end;
4699 end loop;
4700 end;
4701 $outer$;
4702 NOTICE: caught division by zero
4703 NOTICE: caught division by zero
4704 NOTICE: caught division by zero
4705 NOTICE: caught division by zero
4706 NOTICE: caught division by zero
4707 NOTICE: caught division by zero
4708 NOTICE: caught division by zero
4709 NOTICE: caught division by zero
4710 NOTICE: caught division by zero
4711 NOTICE: caught division by zero
4712 -- Check variable scoping -- a var is not available in its own or prior
4713 -- default expressions, but it is available in later ones.
4714 do $$
4715 declare x int := x + 1; -- error
4716 begin
4717 raise notice 'x = %', x;
4718 end;
4719 $$;
4720 ERROR: column "x" does not exist
4721 LINE 1: x + 1
4722 ^
4723 QUERY: x + 1
4724 CONTEXT: PL/pgSQL function inline_code_block line 2 during statement block local variable initialization
4725 do $$
4726 declare y int := x + 1; -- error
4727 x int := 42;
4728 begin
4729 raise notice 'x = %, y = %', x, y;
4730 end;
4731 $$;
4732 ERROR: column "x" does not exist
4733 LINE 1: x + 1
4734 ^
4735 QUERY: x + 1
4736 CONTEXT: PL/pgSQL function inline_code_block line 2 during statement block local variable initialization
4737 do $$
4738 declare x int := 42;
4739 y int := x + 1;
4740 begin
4741 raise notice 'x = %, y = %', x, y;
4742 end;
4743 $$;
4744 NOTICE: x = 42, y = 43
4745 do $$
4746 declare x int := 42;
4747 begin
4748 declare y int := x + 1;
4749 x int := x + 2;
4750 z int := x * 10;
4751 begin
4752 raise notice 'x = %, y = %, z = %', x, y, z;
4753 end;
4754 end;
4755 $$;
4756 NOTICE: x = 44, y = 43, z = 440
4757 -- Check handling of conflicts between plpgsql vars and table columns.
4758 set plpgsql.variable_conflict = error;
4759 create function conflict_test() returns setof int8_tbl as $$
4760 declare r record;
4761 q1 bigint := 42;
4762 begin
4763 for r in select q1,q2 from int8_tbl loop
4764 return next r;
4765 end loop;
4766 end;
4767 $$ language plpgsql;
4768 select * from conflict_test();
4769 ERROR: column reference "q1" is ambiguous
4770 LINE 1: select q1,q2 from int8_tbl
4771 ^
4772 DETAIL: It could refer to either a PL/pgSQL variable or a table column.
4773 QUERY: select q1,q2 from int8_tbl
4774 CONTEXT: PL/pgSQL function conflict_test() line 5 at FOR over SELECT rows
4775 create or replace function conflict_test() returns setof int8_tbl as $$
4776 #variable_conflict use_variable
4777 declare r record;
4778 q1 bigint := 42;
4779 begin
4780 for r in select q1,q2 from int8_tbl loop
4781 return next r;
4782 end loop;
4783 end;
4784 $$ language plpgsql;
4785 select * from conflict_test();
4786 q1 | q2
4787 ----+-------------------
4788 42 | 456
4789 42 | 4567890123456789
4790 42 | 123
4791 42 | 4567890123456789
4792 42 | -4567890123456789
4793 (5 rows)
4795 create or replace function conflict_test() returns setof int8_tbl as $$
4796 #variable_conflict use_column
4797 declare r record;
4798 q1 bigint := 42;
4799 begin
4800 for r in select q1,q2 from int8_tbl loop
4801 return next r;
4802 end loop;
4803 end;
4804 $$ language plpgsql;
4805 select * from conflict_test();
4806 q1 | q2
4807 ------------------+-------------------
4808 123 | 456
4809 123 | 4567890123456789
4810 4567890123456789 | 123
4811 4567890123456789 | 4567890123456789
4812 4567890123456789 | -4567890123456789
4813 (5 rows)
4815 drop function conflict_test();
4816 -- Check that an unreserved keyword can be used as a variable name
4817 create function unreserved_test() returns int as $$
4818 declare
4819 forward int := 21;
4820 begin
4821 forward := forward * 2;
4822 return forward;
4823 end
4824 $$ language plpgsql;
4825 select unreserved_test();
4826 unreserved_test
4827 -----------------
4828 42
4829 (1 row)
4831 create or replace function unreserved_test() returns int as $$
4832 declare
4833 return int := 42;
4834 begin
4835 return := return + 1;
4836 return return;
4837 end
4838 $$ language plpgsql;
4839 select unreserved_test();
4840 unreserved_test
4841 -----------------
4842 43
4843 (1 row)
4845 create or replace function unreserved_test() returns int as $$
4846 declare
4847 comment int := 21;
4848 begin
4849 comment := comment * 2;
4850 comment on function unreserved_test() is 'this is a test';
4851 return comment;
4852 end
4853 $$ language plpgsql;
4854 select unreserved_test();
4855 unreserved_test
4856 -----------------
4857 42
4858 (1 row)
4860 select obj_description('unreserved_test()'::regprocedure, 'pg_proc');
4861 obj_description
4862 -----------------
4863 this is a test
4864 (1 row)
4866 drop function unreserved_test();
4867 --
4868 -- Test FOREACH over arrays
4869 --
4870 create function foreach_test(anyarray)
4871 returns void as $$
4872 declare x int;
4873 begin
4874 foreach x in array $1
4875 loop
4876 raise notice '%', x;
4877 end loop;
4878 end;
4879 $$ language plpgsql;
4880 select foreach_test(ARRAY[1,2,3,4]);
4881 NOTICE: 1
4882 NOTICE: 2
4883 NOTICE: 3
4884 NOTICE: 4
4885 foreach_test
4886 --------------
4888 (1 row)
4890 select foreach_test(ARRAY[[1,2],[3,4]]);
4891 NOTICE: 1
4892 NOTICE: 2
4893 NOTICE: 3
4894 NOTICE: 4
4895 foreach_test
4896 --------------
4898 (1 row)
4900 create or replace function foreach_test(anyarray)
4901 returns void as $$
4902 declare x int;
4903 begin
4904 foreach x slice 1 in array $1
4905 loop
4906 raise notice '%', x;
4907 end loop;
4908 end;
4909 $$ language plpgsql;
4910 -- should fail
4911 select foreach_test(ARRAY[1,2,3,4]);
4912 ERROR: FOREACH ... SLICE loop variable must be of an array type
4913 CONTEXT: PL/pgSQL function foreach_test(anyarray) line 4 at FOREACH over array
4914 select foreach_test(ARRAY[[1,2],[3,4]]);
4915 ERROR: FOREACH ... SLICE loop variable must be of an array type
4916 CONTEXT: PL/pgSQL function foreach_test(anyarray) line 4 at FOREACH over array
4917 create or replace function foreach_test(anyarray)
4918 returns void as $$
4919 declare x int[];
4920 begin
4921 foreach x slice 1 in array $1
4922 loop
4923 raise notice '%', x;
4924 end loop;
4925 end;
4926 $$ language plpgsql;
4927 select foreach_test(ARRAY[1,2,3,4]);
4928 NOTICE: {1,2,3,4}
4929 foreach_test
4930 --------------
4932 (1 row)
4934 select foreach_test(ARRAY[[1,2],[3,4]]);
4935 NOTICE: {1,2}
4936 NOTICE: {3,4}
4937 foreach_test
4938 --------------
4940 (1 row)
4942 -- higher level of slicing
4943 create or replace function foreach_test(anyarray)
4944 returns void as $$
4945 declare x int[];
4946 begin
4947 foreach x slice 2 in array $1
4948 loop
4949 raise notice '%', x;
4950 end loop;
4951 end;
4952 $$ language plpgsql;
4953 -- should fail
4954 select foreach_test(ARRAY[1,2,3,4]);
4955 ERROR: slice dimension (2) is out of the valid range 0..1
4956 CONTEXT: PL/pgSQL function foreach_test(anyarray) line 4 at FOREACH over array
4957 -- ok
4958 select foreach_test(ARRAY[[1,2],[3,4]]);
4959 NOTICE: {{1,2},{3,4}}
4960 foreach_test
4961 --------------
4963 (1 row)
4965 select foreach_test(ARRAY[[[1,2]],[[3,4]]]);
4966 NOTICE: {{1,2}}
4967 NOTICE: {{3,4}}
4968 foreach_test
4969 --------------
4971 (1 row)
4973 create type xy_tuple AS (x int, y int);
4974 -- iteration over array of records
4975 create or replace function foreach_test(anyarray)
4976 returns void as $$
4977 declare r record;
4978 begin
4979 foreach r in array $1
4980 loop
4981 raise notice '%', r;
4982 end loop;
4983 end;
4984 $$ language plpgsql;
4985 select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]);
4986 NOTICE: (10,20)
4987 NOTICE: (40,69)
4988 NOTICE: (35,78)
4989 foreach_test
4990 --------------
4992 (1 row)
4994 select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]);
4995 NOTICE: (10,20)
4996 NOTICE: (40,69)
4997 NOTICE: (35,78)
4998 NOTICE: (88,76)
4999 foreach_test
5000 --------------
5002 (1 row)
5004 create or replace function foreach_test(anyarray)
5005 returns void as $$
5006 declare x int; y int;
5007 begin
5008 foreach x, y in array $1
5009 loop
5010 raise notice 'x = %, y = %', x, y;
5011 end loop;
5012 end;
5013 $$ language plpgsql;
5014 select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]);
5015 NOTICE: x = 10, y = 20
5016 NOTICE: x = 40, y = 69
5017 NOTICE: x = 35, y = 78
5018 foreach_test
5019 --------------
5021 (1 row)
5023 select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]);
5024 NOTICE: x = 10, y = 20
5025 NOTICE: x = 40, y = 69
5026 NOTICE: x = 35, y = 78
5027 NOTICE: x = 88, y = 76
5028 foreach_test
5029 --------------
5031 (1 row)
5033 -- slicing over array of composite types
5034 create or replace function foreach_test(anyarray)
5035 returns void as $$
5036 declare x xy_tuple[];
5037 begin
5038 foreach x slice 1 in array $1
5039 loop
5040 raise notice '%', x;
5041 end loop;
5042 end;
5043 $$ language plpgsql;
5044 select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]);
5045 NOTICE: {"(10,20)","(40,69)","(35,78)"}
5046 foreach_test
5047 --------------
5049 (1 row)
5051 select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]);
5052 NOTICE: {"(10,20)","(40,69)"}
5053 NOTICE: {"(35,78)","(88,76)"}
5054 foreach_test
5055 --------------
5057 (1 row)
5059 drop function foreach_test(anyarray);
5060 drop type xy_tuple;
5061 --
5062 -- Assorted tests for array subscript assignment
5063 --
5064 create temp table rtype (id int, ar text[]);
5065 create function arrayassign1() returns text[] language plpgsql as $$
5066 declare
5067 r record;
5068 begin
5069 r := row(12, '{foo,bar,baz}')::rtype;
5070 r.ar[2] := 'replace';
5071 return r.ar;
5072 end$$;
5073 select arrayassign1();
5074 arrayassign1
5075 -------------------
5076 {foo,replace,baz}
5077 (1 row)
5079 select arrayassign1(); -- try again to exercise internal caching
5080 arrayassign1
5081 -------------------
5082 {foo,replace,baz}
5083 (1 row)
5085 create domain orderedarray as int[2]
5086 constraint sorted check (value[1] < value[2]);
5087 select '{1,2}'::orderedarray;
5088 orderedarray
5089 --------------
5090 {1,2}
5091 (1 row)
5093 select '{2,1}'::orderedarray; -- fail
5094 ERROR: value for domain orderedarray violates check constraint "sorted"
5095 create function testoa(x1 int, x2 int, x3 int) returns orderedarray
5096 language plpgsql as $$
5097 declare res orderedarray;
5098 begin
5099 res := array[x1, x2];
5100 res[2] := x3;
5101 return res;
5102 end$$;
5103 select testoa(1,2,3);
5104 testoa
5105 --------
5106 {1,3}
5107 (1 row)
5109 select testoa(1,2,3); -- try again to exercise internal caching
5110 testoa
5111 --------
5112 {1,3}
5113 (1 row)
5115 select testoa(2,1,3); -- fail at initial assign
5116 ERROR: value for domain orderedarray violates check constraint "sorted"
5117 CONTEXT: PL/pgSQL function testoa(integer,integer,integer) line 4 at assignment
5118 select testoa(1,2,1); -- fail at update
5119 ERROR: value for domain orderedarray violates check constraint "sorted"
5120 CONTEXT: PL/pgSQL function testoa(integer,integer,integer) line 5 at assignment
5121 drop function arrayassign1();
5122 drop function testoa(x1 int, x2 int, x3 int);
5123 --
5124 -- Test handling of expanded arrays
5125 --
5126 create function returns_rw_array(int) returns int[]
5127 language plpgsql as $$
5128 declare r int[];
5129 begin r := array[$1, $1]; return r; end;
5130 $$ stable;
5131 create function consumes_rw_array(int[]) returns int
5132 language plpgsql as $$
5133 begin return $1[1]; end;
5134 $$ stable;
5135 select consumes_rw_array(returns_rw_array(42));
5136 consumes_rw_array
5137 -------------------
5138 42
5139 (1 row)
5141 -- bug #14174
5142 explain (verbose, costs off)
5143 select i, a from
5144 (select returns_rw_array(1) as a offset 0) ss,
5145 lateral consumes_rw_array(a) i;
5146 QUERY PLAN
5147 -----------------------------------------------------------------
5148 Nested Loop
5149 Output: i.i, (returns_rw_array(1))
5150 -> Result
5151 Output: returns_rw_array(1)
5152 -> Function Scan on public.consumes_rw_array i
5153 Output: i.i
5154 Function Call: consumes_rw_array((returns_rw_array(1)))
5155 (7 rows)
5157 select i, a from
5158 (select returns_rw_array(1) as a offset 0) ss,
5159 lateral consumes_rw_array(a) i;
5160 i | a
5161 ---+-------
5162 1 | {1,1}
5163 (1 row)
5165 explain (verbose, costs off)
5166 select consumes_rw_array(a), a from returns_rw_array(1) a;
5167 QUERY PLAN
5168 --------------------------------------------
5169 Function Scan on public.returns_rw_array a
5170 Output: consumes_rw_array(a), a
5171 Function Call: returns_rw_array(1)
5172 (3 rows)
5174 select consumes_rw_array(a), a from returns_rw_array(1) a;
5175 consumes_rw_array | a
5176 -------------------+-------
5177 1 | {1,1}
5178 (1 row)
5180 explain (verbose, costs off)
5181 select consumes_rw_array(a), a from
5182 (values (returns_rw_array(1)), (returns_rw_array(2))) v(a);
5183 QUERY PLAN
5184 ---------------------------------------------------------------------
5185 Values Scan on "*VALUES*"
5186 Output: consumes_rw_array("*VALUES*".column1), "*VALUES*".column1
5187 (2 rows)
5189 select consumes_rw_array(a), a from
5190 (values (returns_rw_array(1)), (returns_rw_array(2))) v(a);
5191 consumes_rw_array | a
5192 -------------------+-------
5193 1 | {1,1}
5194 2 | {2,2}
5195 (2 rows)
5197 do $$
5198 declare a int[] := array[1,2];
5199 begin
5200 a := a || 3;
5201 raise notice 'a = %', a;
5202 end$$;
5203 NOTICE: a = {1,2,3}
5204 --
5205 -- Test access to call stack
5206 --
5207 create function inner_func(int)
5208 returns int as $$
5209 declare _context text;
5210 begin
5211 get diagnostics _context = pg_context;
5212 raise notice '***%***', _context;
5213 -- lets do it again, just for fun..
5214 get diagnostics _context = pg_context;
5215 raise notice '***%***', _context;
5216 raise notice 'lets make sure we didnt break anything';
5217 return 2 * $1;
5218 end;
5219 $$ language plpgsql;
5220 create or replace function outer_func(int)
5221 returns int as $$
5222 declare
5223 myresult int;
5224 begin
5225 raise notice 'calling down into inner_func()';
5226 myresult := inner_func($1);
5227 raise notice 'inner_func() done';
5228 return myresult;
5229 end;
5230 $$ language plpgsql;
5231 create or replace function outer_outer_func(int)
5232 returns int as $$
5233 declare
5234 myresult int;
5235 begin
5236 raise notice 'calling down into outer_func()';
5237 myresult := outer_func($1);
5238 raise notice 'outer_func() done';
5239 return myresult;
5240 end;
5241 $$ language plpgsql;
5242 select outer_outer_func(10);
5243 NOTICE: calling down into outer_func()
5244 NOTICE: calling down into inner_func()
5245 NOTICE: ***PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS
5246 PL/pgSQL function outer_func(integer) line 6 at assignment
5247 PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
5248 NOTICE: ***PL/pgSQL function inner_func(integer) line 7 at GET DIAGNOSTICS
5249 PL/pgSQL function outer_func(integer) line 6 at assignment
5250 PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
5251 NOTICE: lets make sure we didnt break anything
5252 NOTICE: inner_func() done
5253 NOTICE: outer_func() done
5254 outer_outer_func
5255 ------------------
5256 20
5257 (1 row)
5259 -- repeated call should work
5260 select outer_outer_func(20);
5261 NOTICE: calling down into outer_func()
5262 NOTICE: calling down into inner_func()
5263 NOTICE: ***PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS
5264 PL/pgSQL function outer_func(integer) line 6 at assignment
5265 PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
5266 NOTICE: ***PL/pgSQL function inner_func(integer) line 7 at GET DIAGNOSTICS
5267 PL/pgSQL function outer_func(integer) line 6 at assignment
5268 PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
5269 NOTICE: lets make sure we didnt break anything
5270 NOTICE: inner_func() done
5271 NOTICE: outer_func() done
5272 outer_outer_func
5273 ------------------
5274 40
5275 (1 row)
5277 drop function outer_outer_func(int);
5278 drop function outer_func(int);
5279 drop function inner_func(int);
5280 -- access to call stack from exception
5281 create function inner_func(int)
5282 returns int as $$
5283 declare
5284 _context text;
5285 sx int := 5;
5286 begin
5287 begin
5288 perform sx / 0;
5289 exception
5290 when division_by_zero then
5291 get diagnostics _context = pg_context;
5292 raise notice '***%***', _context;
5293 end;
5295 -- lets do it again, just for fun..
5296 get diagnostics _context = pg_context;
5297 raise notice '***%***', _context;
5298 raise notice 'lets make sure we didnt break anything';
5299 return 2 * $1;
5300 end;
5301 $$ language plpgsql;
5302 create or replace function outer_func(int)
5303 returns int as $$
5304 declare
5305 myresult int;
5306 begin
5307 raise notice 'calling down into inner_func()';
5308 myresult := inner_func($1);
5309 raise notice 'inner_func() done';
5310 return myresult;
5311 end;
5312 $$ language plpgsql;
5313 create or replace function outer_outer_func(int)
5314 returns int as $$
5315 declare
5316 myresult int;
5317 begin
5318 raise notice 'calling down into outer_func()';
5319 myresult := outer_func($1);
5320 raise notice 'outer_func() done';
5321 return myresult;
5322 end;
5323 $$ language plpgsql;
5324 select outer_outer_func(10);
5325 NOTICE: calling down into outer_func()
5326 NOTICE: calling down into inner_func()
5327 NOTICE: ***PL/pgSQL function inner_func(integer) line 10 at GET DIAGNOSTICS
5328 PL/pgSQL function outer_func(integer) line 6 at assignment
5329 PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
5330 NOTICE: ***PL/pgSQL function inner_func(integer) line 15 at GET DIAGNOSTICS
5331 PL/pgSQL function outer_func(integer) line 6 at assignment
5332 PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
5333 NOTICE: lets make sure we didnt break anything
5334 NOTICE: inner_func() done
5335 NOTICE: outer_func() done
5336 outer_outer_func
5337 ------------------
5338 20
5339 (1 row)
5341 -- repeated call should work
5342 select outer_outer_func(20);
5343 NOTICE: calling down into outer_func()
5344 NOTICE: calling down into inner_func()
5345 NOTICE: ***PL/pgSQL function inner_func(integer) line 10 at GET DIAGNOSTICS
5346 PL/pgSQL function outer_func(integer) line 6 at assignment
5347 PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
5348 NOTICE: ***PL/pgSQL function inner_func(integer) line 15 at GET DIAGNOSTICS
5349 PL/pgSQL function outer_func(integer) line 6 at assignment
5350 PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
5351 NOTICE: lets make sure we didnt break anything
5352 NOTICE: inner_func() done
5353 NOTICE: outer_func() done
5354 outer_outer_func
5355 ------------------
5356 40
5357 (1 row)
5359 drop function outer_outer_func(int);
5360 drop function outer_func(int);
5361 drop function inner_func(int);
5362 -- Test pg_routine_oid
5363 create function current_function(text)
5364 returns regprocedure as $$
5365 declare
5366 fn_oid regprocedure;
5367 begin
5368 get diagnostics fn_oid = pg_routine_oid;
5369 return fn_oid;
5370 end;
5371 $$ language plpgsql;
5372 select current_function('foo');
5373 current_function
5374 ------------------------
5375 current_function(text)
5376 (1 row)
5378 drop function current_function(text);
5379 -- shouldn't fail in DO, even though there's no useful data
5380 do $$
5381 declare
5382 fn_oid oid;
5383 begin
5384 get diagnostics fn_oid = pg_routine_oid;
5385 raise notice 'pg_routine_oid = %', fn_oid;
5386 end;
5387 $$;
5388 NOTICE: pg_routine_oid = 0
5389 --
5390 -- Test ASSERT
5391 --
5392 do $$
5393 begin
5394 assert 1=1; -- should succeed
5395 end;
5396 $$;
5397 do $$
5398 begin
5399 assert 1=0; -- should fail
5400 end;
5401 $$;
5402 ERROR: assertion failed
5403 CONTEXT: PL/pgSQL function inline_code_block line 3 at ASSERT
5404 do $$
5405 begin
5406 assert NULL; -- should fail
5407 end;
5408 $$;
5409 ERROR: assertion failed
5410 CONTEXT: PL/pgSQL function inline_code_block line 3 at ASSERT
5411 -- check controlling GUC
5412 set plpgsql.check_asserts = off;
5413 do $$
5414 begin
5415 assert 1=0; -- won't be tested
5416 end;
5417 $$;
5418 reset plpgsql.check_asserts;
5419 -- test custom message
5420 do $$
5421 declare var text := 'some value';
5422 begin
5423 assert 1=0, format('assertion failed, var = "%s"', var);
5424 end;
5425 $$;
5426 ERROR: assertion failed, var = "some value"
5427 CONTEXT: PL/pgSQL function inline_code_block line 4 at ASSERT
5428 -- ensure assertions are not trapped by 'others'
5429 do $$
5430 begin
5431 assert 1=0, 'unhandled assertion';
5432 exception when others then
5433 null; -- do nothing
5434 end;
5435 $$;
5436 ERROR: unhandled assertion
5437 CONTEXT: PL/pgSQL function inline_code_block line 3 at ASSERT
5438 -- Test use of plpgsql in a domain check constraint (cf. bug #14414)
5439 create function plpgsql_domain_check(val int) returns boolean as $$
5440 begin return val > 0; end
5441 $$ language plpgsql immutable;
5442 create domain plpgsql_domain as integer check(plpgsql_domain_check(value));
5443 do $$
5444 declare v_test plpgsql_domain;
5445 begin
5446 v_test := 1;
5447 end;
5448 $$;
5449 do $$
5450 declare v_test plpgsql_domain := 1;
5451 begin
5452 v_test := 0; -- fail
5453 end;
5454 $$;
5455 ERROR: value for domain plpgsql_domain violates check constraint "plpgsql_domain_check"
5456 CONTEXT: PL/pgSQL function inline_code_block line 4 at assignment
5457 -- Test handling of expanded array passed to a domain constraint (bug #14472)
5458 create function plpgsql_arr_domain_check(val int[]) returns boolean as $$
5459 begin return val[1] > 0; end
5460 $$ language plpgsql immutable;
5461 create domain plpgsql_arr_domain as int[] check(plpgsql_arr_domain_check(value));
5462 do $$
5463 declare v_test plpgsql_arr_domain;
5464 begin
5465 v_test := array[1];
5466 v_test := v_test || 2;
5467 end;
5468 $$;
5469 do $$
5470 declare v_test plpgsql_arr_domain := array[1];
5471 begin
5472 v_test := 0 || v_test; -- fail
5473 end;
5474 $$;
5475 ERROR: value for domain plpgsql_arr_domain violates check constraint "plpgsql_arr_domain_check"
5476 CONTEXT: PL/pgSQL function inline_code_block line 4 at assignment
5477 --
5478 -- test usage of transition tables in AFTER triggers
5479 --
5480 CREATE TABLE transition_table_base (id int PRIMARY KEY, val text);
5481 CREATE FUNCTION transition_table_base_ins_func()
5482 RETURNS trigger
5483 LANGUAGE plpgsql
5484 AS $$
5485 DECLARE
5486 t text;
5487 l text;
5488 BEGIN
5489 t = '';
5490 FOR l IN EXECUTE
5491 $q$
5492 EXPLAIN (TIMING off, COSTS off, VERBOSE on)
5493 SELECT * FROM newtable
5494 $q$ LOOP
5495 t = t || l || E'\n';
5496 END LOOP;
5498 RAISE INFO '%', t;
5499 RETURN new;
5500 END;
5501 $$;
5502 CREATE TRIGGER transition_table_base_ins_trig
5503 AFTER INSERT ON transition_table_base
5504 REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable
5505 FOR EACH STATEMENT
5506 EXECUTE PROCEDURE transition_table_base_ins_func();
5507 ERROR: OLD TABLE can only be specified for a DELETE or UPDATE trigger
5508 CREATE TRIGGER transition_table_base_ins_trig
5509 AFTER INSERT ON transition_table_base
5510 REFERENCING NEW TABLE AS newtable
5511 FOR EACH STATEMENT
5512 EXECUTE PROCEDURE transition_table_base_ins_func();
5513 INSERT INTO transition_table_base VALUES (1, 'One'), (2, 'Two');
5514 INFO: Named Tuplestore Scan
5515 Output: id, val
5517 INSERT INTO transition_table_base VALUES (3, 'Three'), (4, 'Four');
5518 INFO: Named Tuplestore Scan
5519 Output: id, val
5521 CREATE OR REPLACE FUNCTION transition_table_base_upd_func()
5522 RETURNS trigger
5523 LANGUAGE plpgsql
5524 AS $$
5525 DECLARE
5526 t text;
5527 l text;
5528 BEGIN
5529 t = '';
5530 FOR l IN EXECUTE
5531 $q$
5532 EXPLAIN (TIMING off, COSTS off, VERBOSE on)
5533 SELECT * FROM oldtable ot FULL JOIN newtable nt USING (id)
5534 $q$ LOOP
5535 t = t || l || E'\n';
5536 END LOOP;
5538 RAISE INFO '%', t;
5539 RETURN new;
5540 END;
5541 $$;
5542 CREATE TRIGGER transition_table_base_upd_trig
5543 AFTER UPDATE ON transition_table_base
5544 REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable
5545 FOR EACH STATEMENT
5546 EXECUTE PROCEDURE transition_table_base_upd_func();
5547 UPDATE transition_table_base
5548 SET val = '*' || val || '*'
5549 WHERE id BETWEEN 2 AND 3;
5550 INFO: Hash Full Join
5551 Output: COALESCE(ot.id, nt.id), ot.val, nt.val
5552 Hash Cond: (ot.id = nt.id)
5553 -> Named Tuplestore Scan
5554 Output: ot.id, ot.val
5555 -> Hash
5556 Output: nt.id, nt.val
5557 -> Named Tuplestore Scan
5558 Output: nt.id, nt.val
5560 CREATE TABLE transition_table_level1
5561 (
5562 level1_no serial NOT NULL ,
5563 level1_node_name varchar(255),
5564 PRIMARY KEY (level1_no)
5565 ) WITHOUT OIDS;
5566 CREATE TABLE transition_table_level2
5567 (
5568 level2_no serial NOT NULL ,
5569 parent_no int NOT NULL,
5570 level1_node_name varchar(255),
5571 PRIMARY KEY (level2_no)
5572 ) WITHOUT OIDS;
5573 CREATE TABLE transition_table_status
5574 (
5575 level int NOT NULL,
5576 node_no int NOT NULL,
5577 status int,
5578 PRIMARY KEY (level, node_no)
5579 ) WITHOUT OIDS;
5580 CREATE FUNCTION transition_table_level1_ri_parent_del_func()
5581 RETURNS TRIGGER
5582 LANGUAGE plpgsql
5583 AS $$
5584 DECLARE n bigint;
5585 BEGIN
5586 PERFORM FROM p JOIN transition_table_level2 c ON c.parent_no = p.level1_no;
5587 IF FOUND THEN
5588 RAISE EXCEPTION 'RI error';
5589 END IF;
5590 RETURN NULL;
5591 END;
5592 $$;
5593 CREATE TRIGGER transition_table_level1_ri_parent_del_trigger
5594 AFTER DELETE ON transition_table_level1
5595 REFERENCING OLD TABLE AS p
5596 FOR EACH STATEMENT EXECUTE PROCEDURE
5597 transition_table_level1_ri_parent_del_func();
5598 CREATE FUNCTION transition_table_level1_ri_parent_upd_func()
5599 RETURNS TRIGGER
5600 LANGUAGE plpgsql
5601 AS $$
5602 DECLARE
5603 x int;
5604 BEGIN
5605 WITH p AS (SELECT level1_no, sum(delta) cnt
5606 FROM (SELECT level1_no, 1 AS delta FROM i
5607 UNION ALL
5608 SELECT level1_no, -1 AS delta FROM d) w
5609 GROUP BY level1_no
5610 HAVING sum(delta) < 0)
5611 SELECT level1_no
5612 FROM p JOIN transition_table_level2 c ON c.parent_no = p.level1_no
5613 INTO x;
5614 IF FOUND THEN
5615 RAISE EXCEPTION 'RI error';
5616 END IF;
5617 RETURN NULL;
5618 END;
5619 $$;
5620 CREATE TRIGGER transition_table_level1_ri_parent_upd_trigger
5621 AFTER UPDATE ON transition_table_level1
5622 REFERENCING OLD TABLE AS d NEW TABLE AS i
5623 FOR EACH STATEMENT EXECUTE PROCEDURE
5624 transition_table_level1_ri_parent_upd_func();
5625 CREATE FUNCTION transition_table_level2_ri_child_insupd_func()
5626 RETURNS TRIGGER
5627 LANGUAGE plpgsql
5628 AS $$
5629 BEGIN
5630 PERFORM FROM i
5631 LEFT JOIN transition_table_level1 p
5632 ON p.level1_no IS NOT NULL AND p.level1_no = i.parent_no
5633 WHERE p.level1_no IS NULL;
5634 IF FOUND THEN
5635 RAISE EXCEPTION 'RI error';
5636 END IF;
5637 RETURN NULL;
5638 END;
5639 $$;
5640 CREATE TRIGGER transition_table_level2_ri_child_ins_trigger
5641 AFTER INSERT ON transition_table_level2
5642 REFERENCING NEW TABLE AS i
5643 FOR EACH STATEMENT EXECUTE PROCEDURE
5644 transition_table_level2_ri_child_insupd_func();
5645 CREATE TRIGGER transition_table_level2_ri_child_upd_trigger
5646 AFTER UPDATE ON transition_table_level2
5647 REFERENCING NEW TABLE AS i
5648 FOR EACH STATEMENT EXECUTE PROCEDURE
5649 transition_table_level2_ri_child_insupd_func();
5650 -- create initial test data
5651 INSERT INTO transition_table_level1 (level1_no)
5652 SELECT generate_series(1,200);
5653 ANALYZE transition_table_level1;
5654 INSERT INTO transition_table_level2 (level2_no, parent_no)
5655 SELECT level2_no, level2_no / 50 + 1 AS parent_no
5656 FROM generate_series(1,9999) level2_no;
5657 ANALYZE transition_table_level2;
5658 INSERT INTO transition_table_status (level, node_no, status)
5659 SELECT 1, level1_no, 0 FROM transition_table_level1;
5660 INSERT INTO transition_table_status (level, node_no, status)
5661 SELECT 2, level2_no, 0 FROM transition_table_level2;
5662 ANALYZE transition_table_status;
5663 INSERT INTO transition_table_level1(level1_no)
5664 SELECT generate_series(201,1000);
5665 ANALYZE transition_table_level1;
5666 -- behave reasonably if someone tries to modify a transition table
5667 CREATE FUNCTION transition_table_level2_bad_usage_func()
5668 RETURNS TRIGGER
5669 LANGUAGE plpgsql
5670 AS $$
5671 BEGIN
5672 INSERT INTO dx VALUES (1000000, 1000000, 'x');
5673 RETURN NULL;
5674 END;
5675 $$;
5676 CREATE TRIGGER transition_table_level2_bad_usage_trigger
5677 AFTER DELETE ON transition_table_level2
5678 REFERENCING OLD TABLE AS dx
5679 FOR EACH STATEMENT EXECUTE PROCEDURE
5680 transition_table_level2_bad_usage_func();
5681 DELETE FROM transition_table_level2
5682 WHERE level2_no BETWEEN 301 AND 305;
5683 ERROR: relation "dx" cannot be the target of a modifying statement
5684 CONTEXT: SQL statement "INSERT INTO dx VALUES (1000000, 1000000, 'x')"
5685 PL/pgSQL function transition_table_level2_bad_usage_func() line 3 at SQL statement
5686 DROP TRIGGER transition_table_level2_bad_usage_trigger
5687 ON transition_table_level2;
5688 -- attempt modifications which would break RI (should all fail)
5689 DELETE FROM transition_table_level1
5690 WHERE level1_no = 25;
5691 ERROR: RI error
5692 CONTEXT: PL/pgSQL function transition_table_level1_ri_parent_del_func() line 6 at RAISE
5693 UPDATE transition_table_level1 SET level1_no = -1
5694 WHERE level1_no = 30;
5695 ERROR: RI error
5696 CONTEXT: PL/pgSQL function transition_table_level1_ri_parent_upd_func() line 15 at RAISE
5697 INSERT INTO transition_table_level2 (level2_no, parent_no)
5698 VALUES (10000, 10000);
5699 ERROR: RI error
5700 CONTEXT: PL/pgSQL function transition_table_level2_ri_child_insupd_func() line 8 at RAISE
5701 UPDATE transition_table_level2 SET parent_no = 2000
5702 WHERE level2_no = 40;
5703 ERROR: RI error
5704 CONTEXT: PL/pgSQL function transition_table_level2_ri_child_insupd_func() line 8 at RAISE
5705 -- attempt modifications which would not break RI (should all succeed)
5706 DELETE FROM transition_table_level1
5707 WHERE level1_no BETWEEN 201 AND 1000;
5708 DELETE FROM transition_table_level1
5709 WHERE level1_no BETWEEN 100000000 AND 100000010;
5710 SELECT count(*) FROM transition_table_level1;
5711 count
5712 -------
5713 200
5714 (1 row)
5716 DELETE FROM transition_table_level2
5717 WHERE level2_no BETWEEN 211 AND 220;
5718 SELECT count(*) FROM transition_table_level2;
5719 count
5720 -------
5721 9989
5722 (1 row)
5724 CREATE TABLE alter_table_under_transition_tables
5725 (
5726 id int PRIMARY KEY,
5727 name text
5728 );
5729 CREATE FUNCTION alter_table_under_transition_tables_upd_func()
5730 RETURNS TRIGGER
5731 LANGUAGE plpgsql
5732 AS $$
5733 BEGIN
5734 RAISE WARNING 'old table = %, new table = %',
5735 (SELECT string_agg(id || '=' || name, ',') FROM d),
5736 (SELECT string_agg(id || '=' || name, ',') FROM i);
5737 RAISE NOTICE 'one = %', (SELECT 1 FROM alter_table_under_transition_tables LIMIT 1);
5738 RETURN NULL;
5739 END;
5740 $$;
5741 -- should fail, TRUNCATE is not compatible with transition tables
5742 CREATE TRIGGER alter_table_under_transition_tables_upd_trigger
5743 AFTER TRUNCATE OR UPDATE ON alter_table_under_transition_tables
5744 REFERENCING OLD TABLE AS d NEW TABLE AS i
5745 FOR EACH STATEMENT EXECUTE PROCEDURE
5746 alter_table_under_transition_tables_upd_func();
5747 ERROR: TRUNCATE triggers with transition tables are not supported
5748 -- should work
5749 CREATE TRIGGER alter_table_under_transition_tables_upd_trigger
5750 AFTER UPDATE ON alter_table_under_transition_tables
5751 REFERENCING OLD TABLE AS d NEW TABLE AS i
5752 FOR EACH STATEMENT EXECUTE PROCEDURE
5753 alter_table_under_transition_tables_upd_func();
5754 INSERT INTO alter_table_under_transition_tables
5755 VALUES (1, '1'), (2, '2'), (3, '3');
5756 UPDATE alter_table_under_transition_tables
5757 SET name = name || name;
5758 WARNING: old table = 1=1,2=2,3=3, new table = 1=11,2=22,3=33
5759 NOTICE: one = 1
5760 -- now change 'name' to an integer to see what happens...
5761 ALTER TABLE alter_table_under_transition_tables
5762 ALTER COLUMN name TYPE int USING name::integer;
5763 UPDATE alter_table_under_transition_tables
5764 SET name = (name::text || name::text)::integer;
5765 WARNING: old table = 1=11,2=22,3=33, new table = 1=1111,2=2222,3=3333
5766 NOTICE: one = 1
5767 -- now drop column 'name'
5768 ALTER TABLE alter_table_under_transition_tables
5769 DROP column name;
5770 UPDATE alter_table_under_transition_tables
5771 SET id = id;
5772 ERROR: column "name" does not exist
5773 LINE 1: (SELECT string_agg(id || '=' || name, ',') FROM d)
5774 ^
5775 QUERY: (SELECT string_agg(id || '=' || name, ',') FROM d)
5776 CONTEXT: PL/pgSQL function alter_table_under_transition_tables_upd_func() line 3 at RAISE
5777 --
5778 -- Test multiple reference to a transition table
5779 --
5780 CREATE TABLE multi_test (i int);
5781 INSERT INTO multi_test VALUES (1);
5782 CREATE OR REPLACE FUNCTION multi_test_trig() RETURNS trigger
5783 LANGUAGE plpgsql AS $$
5784 BEGIN
5785 RAISE NOTICE 'count = %', (SELECT COUNT(*) FROM new_test);
5786 RAISE NOTICE 'count union = %',
5787 (SELECT COUNT(*)
5788 FROM (SELECT * FROM new_test UNION ALL SELECT * FROM new_test) ss);
5789 RETURN NULL;
5790 END$$;
5791 CREATE TRIGGER my_trigger AFTER UPDATE ON multi_test
5792 REFERENCING NEW TABLE AS new_test OLD TABLE as old_test
5793 FOR EACH STATEMENT EXECUTE PROCEDURE multi_test_trig();
5794 UPDATE multi_test SET i = i;
5795 NOTICE: count = 1
5796 NOTICE: count union = 2
5797 DROP TABLE multi_test;
5798 DROP FUNCTION multi_test_trig();
5799 --
5800 -- Check type parsing and record fetching from partitioned tables
5801 --
5802 CREATE TABLE partitioned_table (a int, b text) PARTITION BY LIST (a);
5803 CREATE TABLE pt_part1 PARTITION OF partitioned_table FOR VALUES IN (1);
5804 CREATE TABLE pt_part2 PARTITION OF partitioned_table FOR VALUES IN (2);
5805 INSERT INTO partitioned_table VALUES (1, 'Row 1');
5806 INSERT INTO partitioned_table VALUES (2, 'Row 2');
5807 CREATE OR REPLACE FUNCTION get_from_partitioned_table(partitioned_table.a%type)
5808 RETURNS partitioned_table AS $$
5809 DECLARE
5810 a_val partitioned_table.a%TYPE;
5811 result partitioned_table%ROWTYPE;
5812 BEGIN
5813 a_val := $1;
5814 SELECT * INTO result FROM partitioned_table WHERE a = a_val;
5815 RETURN result;
5816 END; $$ LANGUAGE plpgsql;
5817 NOTICE: type reference partitioned_table.a%TYPE converted to integer
5818 SELECT * FROM get_from_partitioned_table(1) AS t;
5819 a | b
5820 ---+-------
5821 1 | Row 1
5822 (1 row)
5824 CREATE OR REPLACE FUNCTION list_partitioned_table()
5825 RETURNS SETOF public.partitioned_table.a%TYPE AS $$
5826 DECLARE
5827 row public.partitioned_table%ROWTYPE;
5828 a_val public.partitioned_table.a%TYPE;
5829 BEGIN
5830 FOR row IN SELECT * FROM public.partitioned_table ORDER BY a LOOP
5831 a_val := row.a;
5832 RETURN NEXT a_val;
5833 END LOOP;
5834 RETURN;
5835 END; $$ LANGUAGE plpgsql;
5836 NOTICE: type reference public.partitioned_table.a%TYPE converted to integer
5837 SELECT * FROM list_partitioned_table() AS t;
5838 t
5839 ---
5840 1
5841 2
5842 (2 rows)
5844 --
5845 -- Check argument name is used instead of $n in error message
5846 --
5847 CREATE FUNCTION fx(x WSlot) RETURNS void AS $$
5848 BEGIN
5849 GET DIAGNOSTICS x = ROW_COUNT;
5850 RETURN;
5851 END; $$ LANGUAGE plpgsql;
5852 ERROR: "x" is not a scalar variable
5853 LINE 3: GET DIAGNOSTICS x = ROW_COUNT;
5854 ^