diff -Nrc pgsql.orig.1f40b253cd5f/src/test/regress/expected/char_2.out pgsql.orig/src/test/regress/expected/char_2.out *** pgsql.orig.1f40b253cd5f/src/test/regress/expected/char_2.out 1970-01-01 01:00:00.000000000 +0100 --- pgsql.orig/src/test/regress/expected/char_2.out 2009-02-02 20:37:40.204285358 +0100 *************** *** 0 **** --- 1,122 ---- + -- + -- CHAR + -- + -- fixed-length by value + -- internally passed by value if <= 4 bytes in storage + SELECT char 'c' = char 'c' AS true; + true + ------ + t + (1 row) + + -- + -- Build a table for testing + -- + CREATE TABLE CHAR_TBL(f1 char); + INSERT INTO CHAR_TBL (f1) VALUES ('a'); + INSERT INTO CHAR_TBL (f1) VALUES ('A'); + -- any of the following three input formats are acceptable + INSERT INTO CHAR_TBL (f1) VALUES ('1'); + INSERT INTO CHAR_TBL (f1) VALUES (2); + INSERT INTO CHAR_TBL (f1) VALUES ('3'); + -- zero-length char + INSERT INTO CHAR_TBL (f1) VALUES (''); + -- try char's of greater than 1 length + INSERT INTO CHAR_TBL (f1) VALUES ('cd'); + ERROR: value too long for type character(1) + INSERT INTO CHAR_TBL (f1) VALUES ('c '); + SELECT '' AS seven, * FROM CHAR_TBL; + seven | f1 + -------+---- + | a + | A + | 1 + | 2 + | 3 + | + | c + (7 rows) + + SELECT '' AS six, c.* + FROM CHAR_TBL c + WHERE c.f1 <> 'a'; + six | f1 + -----+---- + | A + | 1 + | 2 + | 3 + | + | c + (6 rows) + + SELECT '' AS one, c.* + FROM CHAR_TBL c + WHERE c.f1 = 'a'; + one | f1 + -----+---- + | a + (1 row) + + SELECT '' AS five, c.* + FROM CHAR_TBL c + WHERE c.f1 < 'a'; + five | f1 + ------+---- + | + (1 row) + + SELECT '' AS six, c.* + FROM CHAR_TBL c + WHERE c.f1 <= 'a'; + six | f1 + -----+---- + | a + | + (2 rows) + + SELECT '' AS one, c.* + FROM CHAR_TBL c + WHERE c.f1 > 'a'; + one | f1 + -----+---- + | A + | 1 + | 2 + | 3 + | c + (5 rows) + + SELECT '' AS two, c.* + FROM CHAR_TBL c + WHERE c.f1 >= 'a'; + two | f1 + -----+---- + | a + | A + | 1 + | 2 + | 3 + | c + (6 rows) + + DROP TABLE CHAR_TBL; + -- + -- Now test longer arrays of char + -- + CREATE TABLE CHAR_TBL(f1 char(4)); + INSERT INTO CHAR_TBL (f1) VALUES ('a'); + INSERT INTO CHAR_TBL (f1) VALUES ('ab'); + INSERT INTO CHAR_TBL (f1) VALUES ('abcd'); + INSERT INTO CHAR_TBL (f1) VALUES ('abcde'); + ERROR: value too long for type character(4) + INSERT INTO CHAR_TBL (f1) VALUES ('abcd '); + SELECT '' AS four, * FROM CHAR_TBL; + four | f1 + ------+------ + | a + | ab + | abcd + | abcd + (4 rows) + diff -Nrc pgsql.orig.1f40b253cd5f/src/test/regress/expected/plpgsql_1.out pgsql.orig/src/test/regress/expected/plpgsql_1.out *** pgsql.orig.1f40b253cd5f/src/test/regress/expected/plpgsql_1.out 1970-01-01 01:00:00.000000000 +0100 --- pgsql.orig/src/test/regress/expected/plpgsql_1.out 2009-02-02 20:37:40.205763287 +0100 *************** *** 0 **** --- 1,3668 ---- + -- + -- PLPGSQL + -- + -- Scenario: + -- + -- A building with a modern TP cable installation where any + -- of the wall connectors can be used to plug in phones, + -- ethernet interfaces or local office hubs. The backside + -- of the wall connectors is wired to one of several patch- + -- fields in the building. + -- + -- In the patchfields, there are hubs and all the slots + -- representing the wall connectors. In addition there are + -- slots that can represent a phone line from the central + -- phone system. + -- + -- Triggers ensure consistency of the patching information. + -- + -- Functions are used to build up powerful views that let + -- you look behind the wall when looking at a patchfield + -- or into a room. + -- + create table Room ( + roomno char(8), + comment text + ); + create unique index Room_rno on Room using btree (roomno bpchar_ops); + create table WSlot ( + slotname char(20), + roomno char(8), + slotlink char(20), + backlink char(20) + ); + create unique index WSlot_name on WSlot using btree (slotname bpchar_ops); + create table PField ( + name text, + comment text + ); + create unique index PField_name on PField using btree (name text_ops); + create table PSlot ( + slotname char(20), + pfname text, + slotlink char(20), + backlink char(20) + ); + create unique index PSlot_name on PSlot using btree (slotname bpchar_ops); + create table PLine ( + slotname char(20), + phonenumber char(20), + comment text, + backlink char(20) + ); + create unique index PLine_name on PLine using btree (slotname bpchar_ops); + create table Hub ( + name char(14), + comment text, + nslots integer + ); + create unique index Hub_name on Hub using btree (name bpchar_ops); + create table HSlot ( + slotname char(20), + hubname char(14), + slotno integer, + slotlink char(20) + ); + create unique index HSlot_name on HSlot using btree (slotname bpchar_ops); + create index HSlot_hubname on HSlot using btree (hubname bpchar_ops); + create table System ( + name text, + comment text + ); + create unique index System_name on System using btree (name text_ops); + create table IFace ( + slotname char(20), + sysname text, + ifname text, + slotlink char(20) + ); + create unique index IFace_name on IFace using btree (slotname bpchar_ops); + create table PHone ( + slotname char(20), + comment text, + slotlink char(20) + ); + create unique index PHone_name on PHone using btree (slotname bpchar_ops); + -- ************************************************************ + -- * + -- * Trigger procedures and functions for the patchfield + -- * test of PL/pgSQL + -- * + -- ************************************************************ + -- ************************************************************ + -- * AFTER UPDATE on Room + -- * - If room no changes let wall slots follow + -- ************************************************************ + create function tg_room_au() returns trigger as ' + begin + if new.roomno != old.roomno then + update WSlot set roomno = new.roomno where roomno = old.roomno; + end if; + return new; + end; + ' language plpgsql; + create trigger tg_room_au after update + on Room for each row execute procedure tg_room_au(); + -- ************************************************************ + -- * AFTER DELETE on Room + -- * - delete wall slots in this room + -- ************************************************************ + create function tg_room_ad() returns trigger as ' + begin + delete from WSlot where roomno = old.roomno; + return old; + end; + ' language plpgsql; + create trigger tg_room_ad after delete + on Room for each row execute procedure tg_room_ad(); + -- ************************************************************ + -- * BEFORE INSERT or UPDATE on WSlot + -- * - Check that room exists + -- ************************************************************ + create function tg_wslot_biu() returns trigger as $$ + begin + if count(*) = 0 from Room where roomno = new.roomno then + raise exception 'Room % does not exist', new.roomno; + end if; + return new; + end; + $$ language plpgsql; + create trigger tg_wslot_biu before insert or update + on WSlot for each row execute procedure tg_wslot_biu(); + -- ************************************************************ + -- * AFTER UPDATE on PField + -- * - Let PSlots of this field follow + -- ************************************************************ + create function tg_pfield_au() returns trigger as ' + begin + if new.name != old.name then + update PSlot set pfname = new.name where pfname = old.name; + end if; + return new; + end; + ' language plpgsql; + create trigger tg_pfield_au after update + on PField for each row execute procedure tg_pfield_au(); + -- ************************************************************ + -- * AFTER DELETE on PField + -- * - Remove all slots of this patchfield + -- ************************************************************ + create function tg_pfield_ad() returns trigger as ' + begin + delete from PSlot where pfname = old.name; + return old; + end; + ' language plpgsql; + create trigger tg_pfield_ad after delete + on PField for each row execute procedure tg_pfield_ad(); + -- ************************************************************ + -- * BEFORE INSERT or UPDATE on PSlot + -- * - Ensure that our patchfield does exist + -- ************************************************************ + create function tg_pslot_biu() returns trigger as $proc$ + declare + pfrec record; + rename new to ps; + begin + select into pfrec * from PField where name = ps.pfname; + if not found then + raise exception $$Patchfield "%" does not exist$$, ps.pfname; + end if; + return ps; + end; + $proc$ language plpgsql; + create trigger tg_pslot_biu before insert or update + on PSlot for each row execute procedure tg_pslot_biu(); + -- ************************************************************ + -- * AFTER UPDATE on System + -- * - If system name changes let interfaces follow + -- ************************************************************ + create function tg_system_au() returns trigger as ' + begin + if new.name != old.name then + update IFace set sysname = new.name where sysname = old.name; + end if; + return new; + end; + ' language plpgsql; + create trigger tg_system_au after update + on System for each row execute procedure tg_system_au(); + -- ************************************************************ + -- * BEFORE INSERT or UPDATE on IFace + -- * - set the slotname to IF.sysname.ifname + -- ************************************************************ + create function tg_iface_biu() returns trigger as $$ + declare + sname text; + sysrec record; + begin + select into sysrec * from system where name = new.sysname; + if not found then + raise exception $q$system "%" does not exist$q$, new.sysname; + end if; + sname := 'IF.' || new.sysname; + sname := sname || '.'; + sname := sname || new.ifname; + if length(sname) > 20 then + raise exception 'IFace slotname "%" too long (20 char max)', sname; + end if; + new.slotname := sname; + return new; + end; + $$ language plpgsql; + create trigger tg_iface_biu before insert or update + on IFace for each row execute procedure tg_iface_biu(); + -- ************************************************************ + -- * AFTER INSERT or UPDATE or DELETE on Hub + -- * - insert/delete/rename slots as required + -- ************************************************************ + create function tg_hub_a() returns trigger as ' + declare + hname text; + dummy integer; + begin + if tg_op = ''INSERT'' then + dummy := tg_hub_adjustslots(new.name, 0, new.nslots); + return new; + end if; + if tg_op = ''UPDATE'' then + if new.name != old.name then + update HSlot set hubname = new.name where hubname = old.name; + end if; + dummy := tg_hub_adjustslots(new.name, old.nslots, new.nslots); + return new; + end if; + if tg_op = ''DELETE'' then + dummy := tg_hub_adjustslots(old.name, old.nslots, 0); + return old; + end if; + end; + ' language plpgsql; + create trigger tg_hub_a after insert or update or delete + on Hub for each row execute procedure tg_hub_a(); + -- ************************************************************ + -- * Support function to add/remove slots of Hub + -- ************************************************************ + create function tg_hub_adjustslots(hname bpchar, + oldnslots integer, + newnslots integer) + returns integer as ' + begin + if newnslots = oldnslots then + return 0; + end if; + if newnslots < oldnslots then + delete from HSlot where hubname = hname and slotno > newnslots; + return 0; + end if; + for i in oldnslots + 1 .. newnslots loop + insert into HSlot (slotname, hubname, slotno, slotlink) + values (''HS.dummy'', hname, i, ''''); + end loop; + return 0; + end + ' language plpgsql; + -- Test comments + COMMENT ON FUNCTION tg_hub_adjustslots_wrong(bpchar, integer, integer) IS 'function with args'; + ERROR: function tg_hub_adjustslots_wrong(character, integer, integer) does not exist + COMMENT ON FUNCTION tg_hub_adjustslots(bpchar, integer, integer) IS 'function with args'; + COMMENT ON FUNCTION tg_hub_adjustslots(bpchar, integer, integer) IS NULL; + -- ************************************************************ + -- * BEFORE INSERT or UPDATE on HSlot + -- * - prevent from manual manipulation + -- * - set the slotname to HS.hubname.slotno + -- ************************************************************ + create function tg_hslot_biu() returns trigger as ' + declare + sname text; + xname HSlot.slotname%TYPE; + hubrec record; + begin + select into hubrec * from Hub where name = new.hubname; + if not found then + raise exception ''no manual manipulation of HSlot''; + end if; + if new.slotno < 1 or new.slotno > hubrec.nslots then + raise exception ''no manual manipulation of HSlot''; + end if; + if tg_op = ''UPDATE'' then + if new.hubname != old.hubname then + if count(*) > 0 from Hub where name = old.hubname then + raise exception ''no manual manipulation of HSlot''; + end if; + end if; + end if; + sname := ''HS.'' || trim(new.hubname); + sname := sname || ''.''; + sname := sname || new.slotno::text; + if length(sname) > 20 then + raise exception ''HSlot slotname "%" too long (20 char max)'', sname; + end if; + new.slotname := sname; + return new; + end; + ' language plpgsql; + create trigger tg_hslot_biu before insert or update + on HSlot for each row execute procedure tg_hslot_biu(); + -- ************************************************************ + -- * BEFORE DELETE on HSlot + -- * - prevent from manual manipulation + -- ************************************************************ + create function tg_hslot_bd() returns trigger as ' + declare + hubrec record; + begin + select into hubrec * from Hub where name = old.hubname; + if not found then + return old; + end if; + if old.slotno > hubrec.nslots then + return old; + end if; + raise exception ''no manual manipulation of HSlot''; + end; + ' language plpgsql; + create trigger tg_hslot_bd before delete + on HSlot for each row execute procedure tg_hslot_bd(); + -- ************************************************************ + -- * BEFORE INSERT on all slots + -- * - Check name prefix + -- ************************************************************ + create function tg_chkslotname() returns trigger as ' + begin + if substr(new.slotname, 1, 2) != tg_argv[0] then + raise exception ''slotname must begin with %'', tg_argv[0]; + end if; + return new; + end; + ' language plpgsql; + create trigger tg_chkslotname before insert + on PSlot for each row execute procedure tg_chkslotname('PS'); + create trigger tg_chkslotname before insert + on WSlot for each row execute procedure tg_chkslotname('WS'); + create trigger tg_chkslotname before insert + on PLine for each row execute procedure tg_chkslotname('PL'); + create trigger tg_chkslotname before insert + on IFace for each row execute procedure tg_chkslotname('IF'); + create trigger tg_chkslotname before insert + on PHone for each row execute procedure tg_chkslotname('PH'); + -- ************************************************************ + -- * BEFORE INSERT or UPDATE on all slots with slotlink + -- * - Set slotlink to empty string if NULL value given + -- ************************************************************ + create function tg_chkslotlink() returns trigger as ' + begin + if new.slotlink isnull then + new.slotlink := ''''; + end if; + return new; + end; + ' language plpgsql; + create trigger tg_chkslotlink before insert or update + on PSlot for each row execute procedure tg_chkslotlink(); + create trigger tg_chkslotlink before insert or update + on WSlot for each row execute procedure tg_chkslotlink(); + create trigger tg_chkslotlink before insert or update + on IFace for each row execute procedure tg_chkslotlink(); + create trigger tg_chkslotlink before insert or update + on HSlot for each row execute procedure tg_chkslotlink(); + create trigger tg_chkslotlink before insert or update + on PHone for each row execute procedure tg_chkslotlink(); + -- ************************************************************ + -- * BEFORE INSERT or UPDATE on all slots with backlink + -- * - Set backlink to empty string if NULL value given + -- ************************************************************ + create function tg_chkbacklink() returns trigger as ' + begin + if new.backlink isnull then + new.backlink := ''''; + end if; + return new; + end; + ' language plpgsql; + create trigger tg_chkbacklink before insert or update + on PSlot for each row execute procedure tg_chkbacklink(); + create trigger tg_chkbacklink before insert or update + on WSlot for each row execute procedure tg_chkbacklink(); + create trigger tg_chkbacklink before insert or update + on PLine for each row execute procedure tg_chkbacklink(); + -- ************************************************************ + -- * BEFORE UPDATE on PSlot + -- * - do delete/insert instead of update if name changes + -- ************************************************************ + create function tg_pslot_bu() returns trigger as ' + begin + if new.slotname != old.slotname then + delete from PSlot where slotname = old.slotname; + insert into PSlot ( + slotname, + pfname, + slotlink, + backlink + ) values ( + new.slotname, + new.pfname, + new.slotlink, + new.backlink + ); + return null; + end if; + return new; + end; + ' language plpgsql; + create trigger tg_pslot_bu before update + on PSlot for each row execute procedure tg_pslot_bu(); + -- ************************************************************ + -- * BEFORE UPDATE on WSlot + -- * - do delete/insert instead of update if name changes + -- ************************************************************ + create function tg_wslot_bu() returns trigger as ' + begin + if new.slotname != old.slotname then + delete from WSlot where slotname = old.slotname; + insert into WSlot ( + slotname, + roomno, + slotlink, + backlink + ) values ( + new.slotname, + new.roomno, + new.slotlink, + new.backlink + ); + return null; + end if; + return new; + end; + ' language plpgsql; + create trigger tg_wslot_bu before update + on WSlot for each row execute procedure tg_Wslot_bu(); + -- ************************************************************ + -- * BEFORE UPDATE on PLine + -- * - do delete/insert instead of update if name changes + -- ************************************************************ + create function tg_pline_bu() returns trigger as ' + begin + if new.slotname != old.slotname then + delete from PLine where slotname = old.slotname; + insert into PLine ( + slotname, + phonenumber, + comment, + backlink + ) values ( + new.slotname, + new.phonenumber, + new.comment, + new.backlink + ); + return null; + end if; + return new; + end; + ' language plpgsql; + create trigger tg_pline_bu before update + on PLine for each row execute procedure tg_pline_bu(); + -- ************************************************************ + -- * BEFORE UPDATE on IFace + -- * - do delete/insert instead of update if name changes + -- ************************************************************ + create function tg_iface_bu() returns trigger as ' + begin + if new.slotname != old.slotname then + delete from IFace where slotname = old.slotname; + insert into IFace ( + slotname, + sysname, + ifname, + slotlink + ) values ( + new.slotname, + new.sysname, + new.ifname, + new.slotlink + ); + return null; + end if; + return new; + end; + ' language plpgsql; + create trigger tg_iface_bu before update + on IFace for each row execute procedure tg_iface_bu(); + -- ************************************************************ + -- * BEFORE UPDATE on HSlot + -- * - do delete/insert instead of update if name changes + -- ************************************************************ + create function tg_hslot_bu() returns trigger as ' + begin + if new.slotname != old.slotname or new.hubname != old.hubname then + delete from HSlot where slotname = old.slotname; + insert into HSlot ( + slotname, + hubname, + slotno, + slotlink + ) values ( + new.slotname, + new.hubname, + new.slotno, + new.slotlink + ); + return null; + end if; + return new; + end; + ' language plpgsql; + create trigger tg_hslot_bu before update + on HSlot for each row execute procedure tg_hslot_bu(); + -- ************************************************************ + -- * BEFORE UPDATE on PHone + -- * - do delete/insert instead of update if name changes + -- ************************************************************ + create function tg_phone_bu() returns trigger as ' + begin + if new.slotname != old.slotname then + delete from PHone where slotname = old.slotname; + insert into PHone ( + slotname, + comment, + slotlink + ) values ( + new.slotname, + new.comment, + new.slotlink + ); + return null; + end if; + return new; + end; + ' language plpgsql; + create trigger tg_phone_bu before update + on PHone for each row execute procedure tg_phone_bu(); + -- ************************************************************ + -- * AFTER INSERT or UPDATE or DELETE on slot with backlink + -- * - Ensure that the opponent correctly points back to us + -- ************************************************************ + create function tg_backlink_a() returns trigger as ' + declare + dummy integer; + begin + if tg_op = ''INSERT'' then + if new.backlink != '''' then + dummy := tg_backlink_set(new.backlink, new.slotname); + end if; + return new; + end if; + if tg_op = ''UPDATE'' then + if new.backlink != old.backlink then + if old.backlink != '''' then + dummy := tg_backlink_unset(old.backlink, old.slotname); + end if; + if new.backlink != '''' then + dummy := tg_backlink_set(new.backlink, new.slotname); + end if; + else + if new.slotname != old.slotname and new.backlink != '''' then + dummy := tg_slotlink_set(new.backlink, new.slotname); + end if; + end if; + return new; + end if; + if tg_op = ''DELETE'' then + if old.backlink != '''' then + dummy := tg_backlink_unset(old.backlink, old.slotname); + end if; + return old; + end if; + end; + ' language plpgsql; + create trigger tg_backlink_a after insert or update or delete + on PSlot for each row execute procedure tg_backlink_a('PS'); + create trigger tg_backlink_a after insert or update or delete + on WSlot for each row execute procedure tg_backlink_a('WS'); + create trigger tg_backlink_a after insert or update or delete + on PLine for each row execute procedure tg_backlink_a('PL'); + -- ************************************************************ + -- * Support function to set the opponents backlink field + -- * if it does not already point to the requested slot + -- ************************************************************ + create function tg_backlink_set(myname bpchar, blname bpchar) + returns integer as ' + declare + mytype char(2); + link char(4); + rec record; + begin + mytype := substr(myname, 1, 2); + link := mytype || substr(blname, 1, 2); + if link = ''PLPL'' then + raise exception + ''backlink between two phone lines does not make sense''; + end if; + if link in (''PLWS'', ''WSPL'') then + raise exception + ''direct link of phone line to wall slot not permitted''; + end if; + if mytype = ''PS'' then + select into rec * from PSlot where slotname = myname; + if not found then + raise exception ''% does not exist'', myname; + end if; + if rec.backlink != blname then + update PSlot set backlink = blname where slotname = myname; + end if; + return 0; + end if; + if mytype = ''WS'' then + select into rec * from WSlot where slotname = myname; + if not found then + raise exception ''% does not exist'', myname; + end if; + if rec.backlink != blname then + update WSlot set backlink = blname where slotname = myname; + end if; + return 0; + end if; + if mytype = ''PL'' then + select into rec * from PLine where slotname = myname; + if not found then + raise exception ''% does not exist'', myname; + end if; + if rec.backlink != blname then + update PLine set backlink = blname where slotname = myname; + end if; + return 0; + end if; + raise exception ''illegal backlink beginning with %'', mytype; + end; + ' language plpgsql; + -- ************************************************************ + -- * Support function to clear out the backlink field if + -- * it still points to specific slot + -- ************************************************************ + create function tg_backlink_unset(bpchar, bpchar) + returns integer as ' + declare + myname alias for $1; + blname alias for $2; + mytype char(2); + rec record; + begin + mytype := substr(myname, 1, 2); + if mytype = ''PS'' then + select into rec * from PSlot where slotname = myname; + if not found then + return 0; + end if; + if rec.backlink = blname then + update PSlot set backlink = '''' where slotname = myname; + end if; + return 0; + end if; + if mytype = ''WS'' then + select into rec * from WSlot where slotname = myname; + if not found then + return 0; + end if; + if rec.backlink = blname then + update WSlot set backlink = '''' where slotname = myname; + end if; + return 0; + end if; + if mytype = ''PL'' then + select into rec * from PLine where slotname = myname; + if not found then + return 0; + end if; + if rec.backlink = blname then + update PLine set backlink = '''' where slotname = myname; + end if; + return 0; + end if; + end + ' language plpgsql; + -- ************************************************************ + -- * AFTER INSERT or UPDATE or DELETE on slot with slotlink + -- * - Ensure that the opponent correctly points back to us + -- ************************************************************ + create function tg_slotlink_a() returns trigger as ' + declare + dummy integer; + begin + if tg_op = ''INSERT'' then + if new.slotlink != '''' then + dummy := tg_slotlink_set(new.slotlink, new.slotname); + end if; + return new; + end if; + if tg_op = ''UPDATE'' then + if new.slotlink != old.slotlink then + if old.slotlink != '''' then + dummy := tg_slotlink_unset(old.slotlink, old.slotname); + end if; + if new.slotlink != '''' then + dummy := tg_slotlink_set(new.slotlink, new.slotname); + end if; + else + if new.slotname != old.slotname and new.slotlink != '''' then + dummy := tg_slotlink_set(new.slotlink, new.slotname); + end if; + end if; + return new; + end if; + if tg_op = ''DELETE'' then + if old.slotlink != '''' then + dummy := tg_slotlink_unset(old.slotlink, old.slotname); + end if; + return old; + end if; + end; + ' language plpgsql; + create trigger tg_slotlink_a after insert or update or delete + on PSlot for each row execute procedure tg_slotlink_a('PS'); + create trigger tg_slotlink_a after insert or update or delete + on WSlot for each row execute procedure tg_slotlink_a('WS'); + create trigger tg_slotlink_a after insert or update or delete + on IFace for each row execute procedure tg_slotlink_a('IF'); + create trigger tg_slotlink_a after insert or update or delete + on HSlot for each row execute procedure tg_slotlink_a('HS'); + create trigger tg_slotlink_a after insert or update or delete + on PHone for each row execute procedure tg_slotlink_a('PH'); + -- ************************************************************ + -- * Support function to set the opponents slotlink field + -- * if it does not already point to the requested slot + -- ************************************************************ + create function tg_slotlink_set(bpchar, bpchar) + returns integer as ' + declare + myname alias for $1; + blname alias for $2; + mytype char(2); + link char(4); + rec record; + begin + mytype := substr(myname, 1, 2); + link := mytype || substr(blname, 1, 2); + if link = ''PHPH'' then + raise exception + ''slotlink between two phones does not make sense''; + end if; + if link in (''PHHS'', ''HSPH'') then + raise exception + ''link of phone to hub does not make sense''; + end if; + if link in (''PHIF'', ''IFPH'') then + raise exception + ''link of phone to hub does not make sense''; + end if; + if link in (''PSWS'', ''WSPS'') then + raise exception + ''slotlink from patchslot to wallslot not permitted''; + end if; + if mytype = ''PS'' then + select into rec * from PSlot where slotname = myname; + if not found then + raise exception ''% does not exist'', myname; + end if; + if rec.slotlink != blname then + update PSlot set slotlink = blname where slotname = myname; + end if; + return 0; + end if; + if mytype = ''WS'' then + select into rec * from WSlot where slotname = myname; + if not found then + raise exception ''% does not exist'', myname; + end if; + if rec.slotlink != blname then + update WSlot set slotlink = blname where slotname = myname; + end if; + return 0; + end if; + if mytype = ''IF'' then + select into rec * from IFace where slotname = myname; + if not found then + raise exception ''% does not exist'', myname; + end if; + if rec.slotlink != blname then + update IFace set slotlink = blname where slotname = myname; + end if; + return 0; + end if; + if mytype = ''HS'' then + select into rec * from HSlot where slotname = myname; + if not found then + raise exception ''% does not exist'', myname; + end if; + if rec.slotlink != blname then + update HSlot set slotlink = blname where slotname = myname; + end if; + return 0; + end if; + if mytype = ''PH'' then + select into rec * from PHone where slotname = myname; + if not found then + raise exception ''% does not exist'', myname; + end if; + if rec.slotlink != blname then + update PHone set slotlink = blname where slotname = myname; + end if; + return 0; + end if; + raise exception ''illegal slotlink beginning with %'', mytype; + end; + ' language plpgsql; + -- ************************************************************ + -- * Support function to clear out the slotlink field if + -- * it still points to specific slot + -- ************************************************************ + create function tg_slotlink_unset(bpchar, bpchar) + returns integer as ' + declare + myname alias for $1; + blname alias for $2; + mytype char(2); + rec record; + begin + mytype := substr(myname, 1, 2); + if mytype = ''PS'' then + select into rec * from PSlot where slotname = myname; + if not found then + return 0; + end if; + if rec.slotlink = blname then + update PSlot set slotlink = '''' where slotname = myname; + end if; + return 0; + end if; + if mytype = ''WS'' then + select into rec * from WSlot where slotname = myname; + if not found then + return 0; + end if; + if rec.slotlink = blname then + update WSlot set slotlink = '''' where slotname = myname; + end if; + return 0; + end if; + if mytype = ''IF'' then + select into rec * from IFace where slotname = myname; + if not found then + return 0; + end if; + if rec.slotlink = blname then + update IFace set slotlink = '''' where slotname = myname; + end if; + return 0; + end if; + if mytype = ''HS'' then + select into rec * from HSlot where slotname = myname; + if not found then + return 0; + end if; + if rec.slotlink = blname then + update HSlot set slotlink = '''' where slotname = myname; + end if; + return 0; + end if; + if mytype = ''PH'' then + select into rec * from PHone where slotname = myname; + if not found then + return 0; + end if; + if rec.slotlink = blname then + update PHone set slotlink = '''' where slotname = myname; + end if; + return 0; + end if; + end; + ' language plpgsql; + -- ************************************************************ + -- * Describe the backside of a patchfield slot + -- ************************************************************ + create function pslot_backlink_view(bpchar) + returns text as ' + <> + declare + rec record; + bltype char(2); + retval text; + begin + select into rec * from PSlot where slotname = $1; + if not found then + return ''''; + end if; + if rec.backlink = '''' then + return ''-''; + end if; + bltype := substr(rec.backlink, 1, 2); + if bltype = ''PL'' then + declare + rec record; + begin + select into rec * from PLine where slotname = outer.rec.backlink; + retval := ''Phone line '' || trim(rec.phonenumber); + if rec.comment != '''' then + retval := retval || '' (''; + retval := retval || rec.comment; + retval := retval || '')''; + end if; + return retval; + end; + end if; + if bltype = ''WS'' then + select into rec * from WSlot where slotname = rec.backlink; + retval := trim(rec.slotname) || '' in room ''; + retval := retval || trim(rec.roomno); + retval := retval || '' -> ''; + return retval || wslot_slotlink_view(rec.slotname); + end if; + return rec.backlink; + end; + ' language plpgsql; + -- ************************************************************ + -- * Describe the front of a patchfield slot + -- ************************************************************ + create function pslot_slotlink_view(bpchar) + returns text as ' + declare + psrec record; + sltype char(2); + retval text; + begin + select into psrec * from PSlot where slotname = $1; + if not found then + return ''''; + end if; + if psrec.slotlink = '''' then + return ''-''; + end if; + sltype := substr(psrec.slotlink, 1, 2); + if sltype = ''PS'' then + retval := trim(psrec.slotlink) || '' -> ''; + return retval || pslot_backlink_view(psrec.slotlink); + end if; + if sltype = ''HS'' then + retval := comment from Hub H, HSlot HS + where HS.slotname = psrec.slotlink + and H.name = HS.hubname; + retval := retval || '' slot ''; + retval := retval || slotno::text from HSlot + where slotname = psrec.slotlink; + return retval; + end if; + return psrec.slotlink; + end; + ' language plpgsql; + -- ************************************************************ + -- * Describe the front of a wall connector slot + -- ************************************************************ + create function wslot_slotlink_view(bpchar) + returns text as ' + declare + rec record; + sltype char(2); + retval text; + begin + select into rec * from WSlot where slotname = $1; + if not found then + return ''''; + end if; + if rec.slotlink = '''' then + return ''-''; + end if; + sltype := substr(rec.slotlink, 1, 2); + if sltype = ''PH'' then + select into rec * from PHone where slotname = rec.slotlink; + retval := ''Phone '' || trim(rec.slotname); + if rec.comment != '''' then + retval := retval || '' (''; + retval := retval || rec.comment; + retval := retval || '')''; + end if; + return retval; + end if; + if sltype = ''IF'' then + declare + syrow System%RowType; + ifrow IFace%ROWTYPE; + begin + select into ifrow * from IFace where slotname = rec.slotlink; + select into syrow * from System where name = ifrow.sysname; + retval := syrow.name || '' IF ''; + retval := retval || ifrow.ifname; + if syrow.comment != '''' then + retval := retval || '' (''; + retval := retval || syrow.comment; + retval := retval || '')''; + end if; + return retval; + end; + end if; + return rec.slotlink; + end; + ' language plpgsql; + -- ************************************************************ + -- * View of a patchfield describing backside and patches + -- ************************************************************ + create view Pfield_v1 as select PF.pfname, PF.slotname, + pslot_backlink_view(PF.slotname) as backside, + pslot_slotlink_view(PF.slotname) as patch + from PSlot PF; + -- + -- First we build the house - so we create the rooms + -- + insert into Room values ('001', 'Entrance'); + insert into Room values ('002', 'Office'); + insert into Room values ('003', 'Office'); + insert into Room values ('004', 'Technical'); + insert into Room values ('101', 'Office'); + insert into Room values ('102', 'Conference'); + insert into Room values ('103', 'Restroom'); + insert into Room values ('104', 'Technical'); + insert into Room values ('105', 'Office'); + insert into Room values ('106', 'Office'); + -- + -- Second we install the wall connectors + -- + insert into WSlot values ('WS.001.1a', '001', '', ''); + insert into WSlot values ('WS.001.1b', '001', '', ''); + insert into WSlot values ('WS.001.2a', '001', '', ''); + insert into WSlot values ('WS.001.2b', '001', '', ''); + insert into WSlot values ('WS.001.3a', '001', '', ''); + insert into WSlot values ('WS.001.3b', '001', '', ''); + insert into WSlot values ('WS.002.1a', '002', '', ''); + insert into WSlot values ('WS.002.1b', '002', '', ''); + insert into WSlot values ('WS.002.2a', '002', '', ''); + insert into WSlot values ('WS.002.2b', '002', '', ''); + insert into WSlot values ('WS.002.3a', '002', '', ''); + insert into WSlot values ('WS.002.3b', '002', '', ''); + insert into WSlot values ('WS.003.1a', '003', '', ''); + insert into WSlot values ('WS.003.1b', '003', '', ''); + insert into WSlot values ('WS.003.2a', '003', '', ''); + insert into WSlot values ('WS.003.2b', '003', '', ''); + insert into WSlot values ('WS.003.3a', '003', '', ''); + insert into WSlot values ('WS.003.3b', '003', '', ''); + insert into WSlot values ('WS.101.1a', '101', '', ''); + insert into WSlot values ('WS.101.1b', '101', '', ''); + insert into WSlot values ('WS.101.2a', '101', '', ''); + insert into WSlot values ('WS.101.2b', '101', '', ''); + insert into WSlot values ('WS.101.3a', '101', '', ''); + insert into WSlot values ('WS.101.3b', '101', '', ''); + insert into WSlot values ('WS.102.1a', '102', '', ''); + insert into WSlot values ('WS.102.1b', '102', '', ''); + insert into WSlot values ('WS.102.2a', '102', '', ''); + insert into WSlot values ('WS.102.2b', '102', '', ''); + insert into WSlot values ('WS.102.3a', '102', '', ''); + insert into WSlot values ('WS.102.3b', '102', '', ''); + insert into WSlot values ('WS.105.1a', '105', '', ''); + insert into WSlot values ('WS.105.1b', '105', '', ''); + insert into WSlot values ('WS.105.2a', '105', '', ''); + insert into WSlot values ('WS.105.2b', '105', '', ''); + insert into WSlot values ('WS.105.3a', '105', '', ''); + insert into WSlot values ('WS.105.3b', '105', '', ''); + insert into WSlot values ('WS.106.1a', '106', '', ''); + insert into WSlot values ('WS.106.1b', '106', '', ''); + insert into WSlot values ('WS.106.2a', '106', '', ''); + insert into WSlot values ('WS.106.2b', '106', '', ''); + insert into WSlot values ('WS.106.3a', '106', '', ''); + insert into WSlot values ('WS.106.3b', '106', '', ''); + -- + -- Now create the patch fields and their slots + -- + insert into PField values ('PF0_1', 'Wallslots basement'); + -- + -- The cables for these will be made later, so they are unconnected for now + -- + insert into PSlot values ('PS.base.a1', 'PF0_1', '', ''); + insert into PSlot values ('PS.base.a2', 'PF0_1', '', ''); + insert into PSlot values ('PS.base.a3', 'PF0_1', '', ''); + insert into PSlot values ('PS.base.a4', 'PF0_1', '', ''); + insert into PSlot values ('PS.base.a5', 'PF0_1', '', ''); + insert into PSlot values ('PS.base.a6', 'PF0_1', '', ''); + -- + -- These are already wired to the wall connectors + -- + insert into PSlot values ('PS.base.b1', 'PF0_1', '', 'WS.002.1a'); + insert into PSlot values ('PS.base.b2', 'PF0_1', '', 'WS.002.1b'); + insert into PSlot values ('PS.base.b3', 'PF0_1', '', 'WS.002.2a'); + insert into PSlot values ('PS.base.b4', 'PF0_1', '', 'WS.002.2b'); + insert into PSlot values ('PS.base.b5', 'PF0_1', '', 'WS.002.3a'); + insert into PSlot values ('PS.base.b6', 'PF0_1', '', 'WS.002.3b'); + insert into PSlot values ('PS.base.c1', 'PF0_1', '', 'WS.003.1a'); + insert into PSlot values ('PS.base.c2', 'PF0_1', '', 'WS.003.1b'); + insert into PSlot values ('PS.base.c3', 'PF0_1', '', 'WS.003.2a'); + insert into PSlot values ('PS.base.c4', 'PF0_1', '', 'WS.003.2b'); + insert into PSlot values ('PS.base.c5', 'PF0_1', '', 'WS.003.3a'); + insert into PSlot values ('PS.base.c6', 'PF0_1', '', 'WS.003.3b'); + -- + -- This patchfield will be renamed later into PF0_2 - so its + -- slots references in pfname should follow + -- + insert into PField values ('PF0_X', 'Phonelines basement'); + insert into PSlot values ('PS.base.ta1', 'PF0_X', '', ''); + insert into PSlot values ('PS.base.ta2', 'PF0_X', '', ''); + insert into PSlot values ('PS.base.ta3', 'PF0_X', '', ''); + insert into PSlot values ('PS.base.ta4', 'PF0_X', '', ''); + insert into PSlot values ('PS.base.ta5', 'PF0_X', '', ''); + insert into PSlot values ('PS.base.ta6', 'PF0_X', '', ''); + insert into PSlot values ('PS.base.tb1', 'PF0_X', '', ''); + insert into PSlot values ('PS.base.tb2', 'PF0_X', '', ''); + insert into PSlot values ('PS.base.tb3', 'PF0_X', '', ''); + insert into PSlot values ('PS.base.tb4', 'PF0_X', '', ''); + insert into PSlot values ('PS.base.tb5', 'PF0_X', '', ''); + insert into PSlot values ('PS.base.tb6', 'PF0_X', '', ''); + insert into PField values ('PF1_1', 'Wallslots 1st floor'); + insert into PSlot values ('PS.1st.a1', 'PF1_1', '', 'WS.101.1a'); + insert into PSlot values ('PS.1st.a2', 'PF1_1', '', 'WS.101.1b'); + insert into PSlot values ('PS.1st.a3', 'PF1_1', '', 'WS.101.2a'); + insert into PSlot values ('PS.1st.a4', 'PF1_1', '', 'WS.101.2b'); + insert into PSlot values ('PS.1st.a5', 'PF1_1', '', 'WS.101.3a'); + insert into PSlot values ('PS.1st.a6', 'PF1_1', '', 'WS.101.3b'); + insert into PSlot values ('PS.1st.b1', 'PF1_1', '', 'WS.102.1a'); + insert into PSlot values ('PS.1st.b2', 'PF1_1', '', 'WS.102.1b'); + insert into PSlot values ('PS.1st.b3', 'PF1_1', '', 'WS.102.2a'); + insert into PSlot values ('PS.1st.b4', 'PF1_1', '', 'WS.102.2b'); + insert into PSlot values ('PS.1st.b5', 'PF1_1', '', 'WS.102.3a'); + insert into PSlot values ('PS.1st.b6', 'PF1_1', '', 'WS.102.3b'); + insert into PSlot values ('PS.1st.c1', 'PF1_1', '', 'WS.105.1a'); + insert into PSlot values ('PS.1st.c2', 'PF1_1', '', 'WS.105.1b'); + insert into PSlot values ('PS.1st.c3', 'PF1_1', '', 'WS.105.2a'); + insert into PSlot values ('PS.1st.c4', 'PF1_1', '', 'WS.105.2b'); + insert into PSlot values ('PS.1st.c5', 'PF1_1', '', 'WS.105.3a'); + insert into PSlot values ('PS.1st.c6', 'PF1_1', '', 'WS.105.3b'); + insert into PSlot values ('PS.1st.d1', 'PF1_1', '', 'WS.106.1a'); + insert into PSlot values ('PS.1st.d2', 'PF1_1', '', 'WS.106.1b'); + insert into PSlot values ('PS.1st.d3', 'PF1_1', '', 'WS.106.2a'); + insert into PSlot values ('PS.1st.d4', 'PF1_1', '', 'WS.106.2b'); + insert into PSlot values ('PS.1st.d5', 'PF1_1', '', 'WS.106.3a'); + insert into PSlot values ('PS.1st.d6', 'PF1_1', '', 'WS.106.3b'); + -- + -- Now we wire the wall connectors 1a-2a in room 001 to the + -- patchfield. In the second update we make an error, and + -- correct it after + -- + update PSlot set backlink = 'WS.001.1a' where slotname = 'PS.base.a1'; + update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a3'; + select * from WSlot where roomno = '001' order by slotname; + slotname | roomno | slotlink | backlink + ----------------------+----------+----------------------+---------------------- + WS.001.1a | 001 | | PS.base.a1 + WS.001.1b | 001 | | PS.base.a3 + WS.001.2a | 001 | | + WS.001.2b | 001 | | + WS.001.3a | 001 | | + WS.001.3b | 001 | | + (6 rows) + + select * from PSlot where slotname ~ 'PS.base.a' order by slotname; + slotname | pfname | slotlink | backlink + ----------------------+--------+----------------------+---------------------- + PS.base.a1 | PF0_1 | | WS.001.1a + PS.base.a2 | PF0_1 | | + PS.base.a3 | PF0_1 | | WS.001.1b + PS.base.a4 | PF0_1 | | + PS.base.a5 | PF0_1 | | + PS.base.a6 | PF0_1 | | + (6 rows) + + update PSlot set backlink = 'WS.001.2a' where slotname = 'PS.base.a3'; + select * from WSlot where roomno = '001' order by slotname; + slotname | roomno | slotlink | backlink + ----------------------+----------+----------------------+---------------------- + WS.001.1a | 001 | | PS.base.a1 + WS.001.1b | 001 | | + WS.001.2a | 001 | | PS.base.a3 + WS.001.2b | 001 | | + WS.001.3a | 001 | | + WS.001.3b | 001 | | + (6 rows) + + select * from PSlot where slotname ~ 'PS.base.a' order by slotname; + slotname | pfname | slotlink | backlink + ----------------------+--------+----------------------+---------------------- + PS.base.a1 | PF0_1 | | WS.001.1a + PS.base.a2 | PF0_1 | | + PS.base.a3 | PF0_1 | | WS.001.2a + PS.base.a4 | PF0_1 | | + PS.base.a5 | PF0_1 | | + PS.base.a6 | PF0_1 | | + (6 rows) + + update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a2'; + select * from WSlot where roomno = '001' order by slotname; + slotname | roomno | slotlink | backlink + ----------------------+----------+----------------------+---------------------- + WS.001.1a | 001 | | PS.base.a1 + WS.001.1b | 001 | | PS.base.a2 + WS.001.2a | 001 | | PS.base.a3 + WS.001.2b | 001 | | + WS.001.3a | 001 | | + WS.001.3b | 001 | | + (6 rows) + + select * from PSlot where slotname ~ 'PS.base.a' order by slotname; + slotname | pfname | slotlink | backlink + ----------------------+--------+----------------------+---------------------- + PS.base.a1 | PF0_1 | | WS.001.1a + PS.base.a2 | PF0_1 | | WS.001.1b + PS.base.a3 | PF0_1 | | WS.001.2a + PS.base.a4 | PF0_1 | | + PS.base.a5 | PF0_1 | | + PS.base.a6 | PF0_1 | | + (6 rows) + + -- + -- Same procedure for 2b-3b but this time updating the WSlot instead + -- of the PSlot. Due to the triggers the result is the same: + -- WSlot and corresponding PSlot point to each other. + -- + update WSlot set backlink = 'PS.base.a4' where slotname = 'WS.001.2b'; + update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3a'; + select * from WSlot where roomno = '001' order by slotname; + slotname | roomno | slotlink | backlink + ----------------------+----------+----------------------+---------------------- + WS.001.1a | 001 | | PS.base.a1 + WS.001.1b | 001 | | PS.base.a2 + WS.001.2a | 001 | | PS.base.a3 + WS.001.2b | 001 | | PS.base.a4 + WS.001.3a | 001 | | PS.base.a6 + WS.001.3b | 001 | | + (6 rows) + + select * from PSlot where slotname ~ 'PS.base.a' order by slotname; + slotname | pfname | slotlink | backlink + ----------------------+--------+----------------------+---------------------- + PS.base.a1 | PF0_1 | | WS.001.1a + PS.base.a2 | PF0_1 | | WS.001.1b + PS.base.a3 | PF0_1 | | WS.001.2a + PS.base.a4 | PF0_1 | | WS.001.2b + PS.base.a5 | PF0_1 | | + PS.base.a6 | PF0_1 | | WS.001.3a + (6 rows) + + update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3b'; + select * from WSlot where roomno = '001' order by slotname; + slotname | roomno | slotlink | backlink + ----------------------+----------+----------------------+---------------------- + WS.001.1a | 001 | | PS.base.a1 + WS.001.1b | 001 | | PS.base.a2 + WS.001.2a | 001 | | PS.base.a3 + WS.001.2b | 001 | | PS.base.a4 + WS.001.3a | 001 | | + WS.001.3b | 001 | | PS.base.a6 + (6 rows) + + select * from PSlot where slotname ~ 'PS.base.a' order by slotname; + slotname | pfname | slotlink | backlink + ----------------------+--------+----------------------+---------------------- + PS.base.a1 | PF0_1 | | WS.001.1a + PS.base.a2 | PF0_1 | | WS.001.1b + PS.base.a3 | PF0_1 | | WS.001.2a + PS.base.a4 | PF0_1 | | WS.001.2b + PS.base.a5 | PF0_1 | | + PS.base.a6 | PF0_1 | | WS.001.3b + (6 rows) + + update WSlot set backlink = 'PS.base.a5' where slotname = 'WS.001.3a'; + select * from WSlot where roomno = '001' order by slotname; + slotname | roomno | slotlink | backlink + ----------------------+----------+----------------------+---------------------- + WS.001.1a | 001 | | PS.base.a1 + WS.001.1b | 001 | | PS.base.a2 + WS.001.2a | 001 | | PS.base.a3 + WS.001.2b | 001 | | PS.base.a4 + WS.001.3a | 001 | | PS.base.a5 + WS.001.3b | 001 | | PS.base.a6 + (6 rows) + + select * from PSlot where slotname ~ 'PS.base.a' order by slotname; + slotname | pfname | slotlink | backlink + ----------------------+--------+----------------------+---------------------- + PS.base.a1 | PF0_1 | | WS.001.1a + PS.base.a2 | PF0_1 | | WS.001.1b + PS.base.a3 | PF0_1 | | WS.001.2a + PS.base.a4 | PF0_1 | | WS.001.2b + PS.base.a5 | PF0_1 | | WS.001.3a + PS.base.a6 | PF0_1 | | WS.001.3b + (6 rows) + + insert into PField values ('PF1_2', 'Phonelines 1st floor'); + insert into PSlot values ('PS.1st.ta1', 'PF1_2', '', ''); + insert into PSlot values ('PS.1st.ta2', 'PF1_2', '', ''); + insert into PSlot values ('PS.1st.ta3', 'PF1_2', '', ''); + insert into PSlot values ('PS.1st.ta4', 'PF1_2', '', ''); + insert into PSlot values ('PS.1st.ta5', 'PF1_2', '', ''); + insert into PSlot values ('PS.1st.ta6', 'PF1_2', '', ''); + insert into PSlot values ('PS.1st.tb1', 'PF1_2', '', ''); + insert into PSlot values ('PS.1st.tb2', 'PF1_2', '', ''); + insert into PSlot values ('PS.1st.tb3', 'PF1_2', '', ''); + insert into PSlot values ('PS.1st.tb4', 'PF1_2', '', ''); + insert into PSlot values ('PS.1st.tb5', 'PF1_2', '', ''); + insert into PSlot values ('PS.1st.tb6', 'PF1_2', '', ''); + -- + -- Fix the wrong name for patchfield PF0_2 + -- + update PField set name = 'PF0_2' where name = 'PF0_X'; + select * from PSlot order by slotname; + slotname | pfname | slotlink | backlink + ----------------------+--------+----------------------+---------------------- + PS.base.a1 | PF0_1 | | WS.001.1a + PS.base.a2 | PF0_1 | | WS.001.1b + PS.base.a3 | PF0_1 | | WS.001.2a + PS.base.a4 | PF0_1 | | WS.001.2b + PS.base.a5 | PF0_1 | | WS.001.3a + PS.base.a6 | PF0_1 | | WS.001.3b + PS.base.b1 | PF0_1 | | WS.002.1a + PS.base.b2 | PF0_1 | | WS.002.1b + PS.base.b3 | PF0_1 | | WS.002.2a + PS.base.b4 | PF0_1 | | WS.002.2b + PS.base.b5 | PF0_1 | | WS.002.3a + PS.base.b6 | PF0_1 | | WS.002.3b + PS.base.c1 | PF0_1 | | WS.003.1a + PS.base.c2 | PF0_1 | | WS.003.1b + PS.base.c3 | PF0_1 | | WS.003.2a + PS.base.c4 | PF0_1 | | WS.003.2b + PS.base.c5 | PF0_1 | | WS.003.3a + PS.base.c6 | PF0_1 | | WS.003.3b + PS.base.ta1 | PF0_2 | | + PS.base.ta2 | PF0_2 | | + PS.base.ta3 | PF0_2 | | + PS.base.ta4 | PF0_2 | | + PS.base.ta5 | PF0_2 | | + PS.base.ta6 | PF0_2 | | + PS.base.tb1 | PF0_2 | | + PS.base.tb2 | PF0_2 | | + PS.base.tb3 | PF0_2 | | + PS.base.tb4 | PF0_2 | | + PS.base.tb5 | PF0_2 | | + PS.base.tb6 | PF0_2 | | + PS.1st.a1 | PF1_1 | | WS.101.1a + PS.1st.a2 | PF1_1 | | WS.101.1b + PS.1st.a3 | PF1_1 | | WS.101.2a + PS.1st.a4 | PF1_1 | | WS.101.2b + PS.1st.a5 | PF1_1 | | WS.101.3a + PS.1st.a6 | PF1_1 | | WS.101.3b + PS.1st.b1 | PF1_1 | | WS.102.1a + PS.1st.b2 | PF1_1 | | WS.102.1b + PS.1st.b3 | PF1_1 | | WS.102.2a + PS.1st.b4 | PF1_1 | | WS.102.2b + PS.1st.b5 | PF1_1 | | WS.102.3a + PS.1st.b6 | PF1_1 | | WS.102.3b + PS.1st.c1 | PF1_1 | | WS.105.1a + PS.1st.c2 | PF1_1 | | WS.105.1b + PS.1st.c3 | PF1_1 | | WS.105.2a + PS.1st.c4 | PF1_1 | | WS.105.2b + PS.1st.c5 | PF1_1 | | WS.105.3a + PS.1st.c6 | PF1_1 | | WS.105.3b + PS.1st.d1 | PF1_1 | | WS.106.1a + PS.1st.d2 | PF1_1 | | WS.106.1b + PS.1st.d3 | PF1_1 | | WS.106.2a + PS.1st.d4 | PF1_1 | | WS.106.2b + PS.1st.d5 | PF1_1 | | WS.106.3a + PS.1st.d6 | PF1_1 | | WS.106.3b + PS.1st.ta1 | PF1_2 | | + PS.1st.ta2 | PF1_2 | | + PS.1st.ta3 | PF1_2 | | + PS.1st.ta4 | PF1_2 | | + PS.1st.ta5 | PF1_2 | | + PS.1st.ta6 | PF1_2 | | + PS.1st.tb1 | PF1_2 | | + PS.1st.tb2 | PF1_2 | | + PS.1st.tb3 | PF1_2 | | + PS.1st.tb4 | PF1_2 | | + PS.1st.tb5 | PF1_2 | | + PS.1st.tb6 | PF1_2 | | + (66 rows) + + select * from WSlot order by slotname; + slotname | roomno | slotlink | backlink + ----------------------+----------+----------------------+---------------------- + WS.001.1a | 001 | | PS.base.a1 + WS.001.1b | 001 | | PS.base.a2 + WS.001.2a | 001 | | PS.base.a3 + WS.001.2b | 001 | | PS.base.a4 + WS.001.3a | 001 | | PS.base.a5 + WS.001.3b | 001 | | PS.base.a6 + WS.002.1a | 002 | | PS.base.b1 + WS.002.1b | 002 | | PS.base.b2 + WS.002.2a | 002 | | PS.base.b3 + WS.002.2b | 002 | | PS.base.b4 + WS.002.3a | 002 | | PS.base.b5 + WS.002.3b | 002 | | PS.base.b6 + WS.003.1a | 003 | | PS.base.c1 + WS.003.1b | 003 | | PS.base.c2 + WS.003.2a | 003 | | PS.base.c3 + WS.003.2b | 003 | | PS.base.c4 + WS.003.3a | 003 | | PS.base.c5 + WS.003.3b | 003 | | PS.base.c6 + WS.101.1a | 101 | | PS.1st.a1 + WS.101.1b | 101 | | PS.1st.a2 + WS.101.2a | 101 | | PS.1st.a3 + WS.101.2b | 101 | | PS.1st.a4 + WS.101.3a | 101 | | PS.1st.a5 + WS.101.3b | 101 | | PS.1st.a6 + WS.102.1a | 102 | | PS.1st.b1 + WS.102.1b | 102 | | PS.1st.b2 + WS.102.2a | 102 | | PS.1st.b3 + WS.102.2b | 102 | | PS.1st.b4 + WS.102.3a | 102 | | PS.1st.b5 + WS.102.3b | 102 | | PS.1st.b6 + WS.105.1a | 105 | | PS.1st.c1 + WS.105.1b | 105 | | PS.1st.c2 + WS.105.2a | 105 | | PS.1st.c3 + WS.105.2b | 105 | | PS.1st.c4 + WS.105.3a | 105 | | PS.1st.c5 + WS.105.3b | 105 | | PS.1st.c6 + WS.106.1a | 106 | | PS.1st.d1 + WS.106.1b | 106 | | PS.1st.d2 + WS.106.2a | 106 | | PS.1st.d3 + WS.106.2b | 106 | | PS.1st.d4 + WS.106.3a | 106 | | PS.1st.d5 + WS.106.3b | 106 | | PS.1st.d6 + (42 rows) + + -- + -- Install the central phone system and create the phone numbers. + -- They are weired on insert to the patchfields. Again the + -- triggers automatically tell the PSlots to update their + -- backlink field. + -- + insert into PLine values ('PL.001', '-0', 'Central call', 'PS.base.ta1'); + insert into PLine values ('PL.002', '-101', '', 'PS.base.ta2'); + insert into PLine values ('PL.003', '-102', '', 'PS.base.ta3'); + insert into PLine values ('PL.004', '-103', '', 'PS.base.ta5'); + insert into PLine values ('PL.005', '-104', '', 'PS.base.ta6'); + insert into PLine values ('PL.006', '-106', '', 'PS.base.tb2'); + insert into PLine values ('PL.007', '-108', '', 'PS.base.tb3'); + insert into PLine values ('PL.008', '-109', '', 'PS.base.tb4'); + insert into PLine values ('PL.009', '-121', '', 'PS.base.tb5'); + insert into PLine values ('PL.010', '-122', '', 'PS.base.tb6'); + insert into PLine values ('PL.015', '-134', '', 'PS.1st.ta1'); + insert into PLine values ('PL.016', '-137', '', 'PS.1st.ta3'); + insert into PLine values ('PL.017', '-139', '', 'PS.1st.ta4'); + insert into PLine values ('PL.018', '-362', '', 'PS.1st.tb1'); + insert into PLine values ('PL.019', '-363', '', 'PS.1st.tb2'); + insert into PLine values ('PL.020', '-364', '', 'PS.1st.tb3'); + insert into PLine values ('PL.021', '-365', '', 'PS.1st.tb5'); + insert into PLine values ('PL.022', '-367', '', 'PS.1st.tb6'); + insert into PLine values ('PL.028', '-501', 'Fax entrance', 'PS.base.ta2'); + insert into PLine values ('PL.029', '-502', 'Fax 1st floor', 'PS.1st.ta1'); + -- + -- Buy some phones, plug them into the wall and patch the + -- phone lines to the corresponding patchfield slots. + -- + insert into PHone values ('PH.hc001', 'Hicom standard', 'WS.001.1a'); + update PSlot set slotlink = 'PS.base.ta1' where slotname = 'PS.base.a1'; + insert into PHone values ('PH.hc002', 'Hicom standard', 'WS.002.1a'); + update PSlot set slotlink = 'PS.base.ta5' where slotname = 'PS.base.b1'; + insert into PHone values ('PH.hc003', 'Hicom standard', 'WS.002.2a'); + update PSlot set slotlink = 'PS.base.tb2' where slotname = 'PS.base.b3'; + insert into PHone values ('PH.fax001', 'Canon fax', 'WS.001.2a'); + update PSlot set slotlink = 'PS.base.ta2' where slotname = 'PS.base.a3'; + -- + -- Install a hub at one of the patchfields, plug a computers + -- ethernet interface into the wall and patch it to the hub. + -- + insert into Hub values ('base.hub1', 'Patchfield PF0_1 hub', 16); + insert into System values ('orion', 'PC'); + insert into IFace values ('IF', 'orion', 'eth0', 'WS.002.1b'); + update PSlot set slotlink = 'HS.base.hub1.1' where slotname = 'PS.base.b2'; + -- + -- Now we take a look at the patchfield + -- + select * from PField_v1 where pfname = 'PF0_1' order by slotname; + pfname | slotname | backside | patch + --------+----------------------+----------------------------------------------------------+----------------------------------------------- + PF0_1 | PS.base.a1 | WS.001.1a in room 001 -> Phone PH.hc001 (Hicom standard) | PS.base.ta1 -> Phone line -0 (Central call) + PF0_1 | PS.base.a2 | WS.001.1b in room 001 -> - | - + PF0_1 | PS.base.a3 | WS.001.2a in room 001 -> Phone PH.fax001 (Canon fax) | PS.base.ta2 -> Phone line -501 (Fax entrance) + PF0_1 | PS.base.a4 | WS.001.2b in room 001 -> - | - + PF0_1 | PS.base.a5 | WS.001.3a in room 001 -> - | - + PF0_1 | PS.base.a6 | WS.001.3b in room 001 -> - | - + PF0_1 | PS.base.b1 | WS.002.1a in room 002 -> Phone PH.hc002 (Hicom standard) | PS.base.ta5 -> Phone line -103 + PF0_1 | PS.base.b2 | WS.002.1b in room 002 -> orion IF eth0 (PC) | Patchfield PF0_1 hub slot 1 + PF0_1 | PS.base.b3 | WS.002.2a in room 002 -> Phone PH.hc003 (Hicom standard) | PS.base.tb2 -> Phone line -106 + PF0_1 | PS.base.b4 | WS.002.2b in room 002 -> - | - + PF0_1 | PS.base.b5 | WS.002.3a in room 002 -> - | - + PF0_1 | PS.base.b6 | WS.002.3b in room 002 -> - | - + PF0_1 | PS.base.c1 | WS.003.1a in room 003 -> - | - + PF0_1 | PS.base.c2 | WS.003.1b in room 003 -> - | - + PF0_1 | PS.base.c3 | WS.003.2a in room 003 -> - | - + PF0_1 | PS.base.c4 | WS.003.2b in room 003 -> - | - + PF0_1 | PS.base.c5 | WS.003.3a in room 003 -> - | - + PF0_1 | PS.base.c6 | WS.003.3b in room 003 -> - | - + (18 rows) + + select * from PField_v1 where pfname = 'PF0_2' order by slotname; + pfname | slotname | backside | patch + --------+----------------------+--------------------------------+------------------------------------------------------------------------ + PF0_2 | PS.base.ta1 | Phone line -0 (Central call) | PS.base.a1 -> WS.001.1a in room 001 -> Phone PH.hc001 (Hicom standard) + PF0_2 | PS.base.ta2 | Phone line -501 (Fax entrance) | PS.base.a3 -> WS.001.2a in room 001 -> Phone PH.fax001 (Canon fax) + PF0_2 | PS.base.ta3 | Phone line -102 | - + PF0_2 | PS.base.ta4 | - | - + PF0_2 | PS.base.ta5 | Phone line -103 | PS.base.b1 -> WS.002.1a in room 002 -> Phone PH.hc002 (Hicom standard) + PF0_2 | PS.base.ta6 | Phone line -104 | - + PF0_2 | PS.base.tb1 | - | - + PF0_2 | PS.base.tb2 | Phone line -106 | PS.base.b3 -> WS.002.2a in room 002 -> Phone PH.hc003 (Hicom standard) + PF0_2 | PS.base.tb3 | Phone line -108 | - + PF0_2 | PS.base.tb4 | Phone line -109 | - + PF0_2 | PS.base.tb5 | Phone line -121 | - + PF0_2 | PS.base.tb6 | Phone line -122 | - + (12 rows) + + -- + -- Finally we want errors + -- + insert into PField values ('PF1_1', 'should fail due to unique index'); + ERROR: duplicate key value violates unique constraint "pfield_name" + update PSlot set backlink = 'WS.not.there' where slotname = 'PS.base.a1'; + ERROR: WS.not.there does not exist + CONTEXT: PL/pgSQL function "tg_backlink_a" line 16 at assignment + update PSlot set backlink = 'XX.illegal' where slotname = 'PS.base.a1'; + ERROR: illegal backlink beginning with XX + CONTEXT: PL/pgSQL function "tg_backlink_a" line 16 at assignment + update PSlot set slotlink = 'PS.not.there' where slotname = 'PS.base.a1'; + ERROR: PS.not.there does not exist + CONTEXT: PL/pgSQL function "tg_slotlink_a" line 16 at assignment + update PSlot set slotlink = 'XX.illegal' where slotname = 'PS.base.a1'; + ERROR: illegal slotlink beginning with XX + CONTEXT: PL/pgSQL function "tg_slotlink_a" line 16 at assignment + insert into HSlot values ('HS', 'base.hub1', 1, ''); + ERROR: duplicate key value violates unique constraint "hslot_name" + insert into HSlot values ('HS', 'base.hub1', 20, ''); + ERROR: no manual manipulation of HSlot + delete from HSlot; + ERROR: no manual manipulation of HSlot + insert into IFace values ('IF', 'notthere', 'eth0', ''); + ERROR: system "notthere" does not exist + insert into IFace values ('IF', 'orion', 'ethernet_interface_name_too_long', ''); + ERROR: IFace slotname "IF.orion.ethernet_interface_name_too_long" too long (20 char max) + -- + -- The following tests are unrelated to the scenario outlined above; + -- they merely exercise specific parts of PL/PgSQL + -- + -- + -- Test recursion, per bug report 7-Sep-01 + -- + CREATE FUNCTION recursion_test(int,int) RETURNS text AS ' + DECLARE rslt text; + BEGIN + IF $1 <= 0 THEN + rslt = CAST($2 AS TEXT); + ELSE + rslt = CAST($1 AS TEXT) || '','' || recursion_test($1 - 1, $2); + END IF; + RETURN rslt; + END;' LANGUAGE plpgsql; + SELECT recursion_test(4,3); + recursion_test + ---------------- + 4,3,2,1,3 + (1 row) + + -- + -- Test the FOUND magic variable + -- + CREATE TABLE found_test_tbl (a int); + create function test_found() + returns boolean as ' + declare + begin + insert into found_test_tbl values (1); + if FOUND then + insert into found_test_tbl values (2); + end if; + + update found_test_tbl set a = 100 where a = 1; + if FOUND then + insert into found_test_tbl values (3); + end if; + + delete from found_test_tbl where a = 9999; -- matches no rows + if not FOUND then + insert into found_test_tbl values (4); + end if; + + for i in 1 .. 10 loop + -- no need to do anything + end loop; + if FOUND then + insert into found_test_tbl values (5); + end if; + + -- never executes the loop + for i in 2 .. 1 loop + -- no need to do anything + end loop; + if not FOUND then + insert into found_test_tbl values (6); + end if; + return true; + end;' language plpgsql; + select test_found(); + test_found + ------------ + t + (1 row) + + select * from found_test_tbl; + a + ----- + 2 + 100 + 3 + 4 + 5 + 6 + (6 rows) + + -- + -- Test set-returning functions for PL/pgSQL + -- + create function test_table_func_rec() returns setof found_test_tbl as ' + DECLARE + rec RECORD; + BEGIN + FOR rec IN select * from found_test_tbl LOOP + RETURN NEXT rec; + END LOOP; + RETURN; + END;' language plpgsql; + select * from test_table_func_rec(); + a + ----- + 2 + 100 + 3 + 4 + 5 + 6 + (6 rows) + + create function test_table_func_row() returns setof found_test_tbl as ' + DECLARE + row found_test_tbl%ROWTYPE; + BEGIN + FOR row IN select * from found_test_tbl LOOP + RETURN NEXT row; + END LOOP; + RETURN; + END;' language plpgsql; + select * from test_table_func_row(); + a + ----- + 2 + 100 + 3 + 4 + 5 + 6 + (6 rows) + + create function test_ret_set_scalar(int,int) returns setof int as ' + DECLARE + i int; + BEGIN + FOR i IN $1 .. $2 LOOP + RETURN NEXT i + 1; + END LOOP; + RETURN; + END;' language plpgsql; + select * from test_ret_set_scalar(1,10); + test_ret_set_scalar + --------------------- + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 + 11 + (10 rows) + + create function test_ret_set_rec_dyn(int) returns setof record as ' + DECLARE + retval RECORD; + BEGIN + IF $1 > 10 THEN + SELECT INTO retval 5, 10, 15; + RETURN NEXT retval; + RETURN NEXT retval; + ELSE + SELECT INTO retval 50, 5::numeric, ''xxx''::text; + RETURN NEXT retval; + RETURN NEXT retval; + END IF; + RETURN; + END;' language plpgsql; + SELECT * FROM test_ret_set_rec_dyn(1500) AS (a int, b int, c int); + a | b | c + ---+----+---- + 5 | 10 | 15 + 5 | 10 | 15 + (2 rows) + + SELECT * FROM test_ret_set_rec_dyn(5) AS (a int, b numeric, c text); + a | b | c + ----+---+----- + 50 | 5 | xxx + 50 | 5 | xxx + (2 rows) + + create function test_ret_rec_dyn(int) returns record as ' + DECLARE + retval RECORD; + BEGIN + IF $1 > 10 THEN + SELECT INTO retval 5, 10, 15; + RETURN retval; + ELSE + SELECT INTO retval 50, 5::numeric, ''xxx''::text; + RETURN retval; + END IF; + END;' language plpgsql; + SELECT * FROM test_ret_rec_dyn(1500) AS (a int, b int, c int); + a | b | c + ---+----+---- + 5 | 10 | 15 + (1 row) + + SELECT * FROM test_ret_rec_dyn(5) AS (a int, b numeric, c text); + a | b | c + ----+---+----- + 50 | 5 | xxx + (1 row) + + -- + -- Test handling of OUT parameters, including polymorphic cases. + -- Note that RETURN is optional with OUT params; we try both ways. + -- + -- wrong way to do it: + create function f1(in i int, out j int) returns int as $$ + begin + return i+1; + end$$ language plpgsql; + ERROR: RETURN cannot have a parameter in function with OUT parameters at or near "i" + LINE 3: return i+1; + ^ + create function f1(in i int, out j int) as $$ + begin + j := i+1; + return; + end$$ language plpgsql; + select f1(42); + f1 + ---- + 43 + (1 row) + + select * from f1(42); + j + ---- + 43 + (1 row) + + create or replace function f1(inout i int) as $$ + begin + i := i+1; + end$$ language plpgsql; + select f1(42); + f1 + ---- + 43 + (1 row) + + select * from f1(42); + i + ---- + 43 + (1 row) + + drop function f1(int); + create function f1(in i int, out j int) returns setof int as $$ + begin + j := i+1; + return next; + j := i+2; + return next; + return; + end$$ language plpgsql; + select * from f1(42); + j + ---- + 43 + 44 + (2 rows) + + drop function f1(int); + create function f1(in i int, out j int, out k text) as $$ + begin + j := i; + j := j+1; + k := 'foo'; + end$$ language plpgsql; + select f1(42); + f1 + ---------- + (43,foo) + (1 row) + + select * from f1(42); + j | k + ----+----- + 43 | foo + (1 row) + + drop function f1(int); + create function f1(in i int, out j int, out k text) returns setof record as $$ + begin + j := i+1; + k := 'foo'; + return next; + j := j+1; + k := 'foot'; + return next; + end$$ language plpgsql; + select * from f1(42); + j | k + ----+------ + 43 | foo + 44 | foot + (2 rows) + + drop function f1(int); + create function duplic(in i anyelement, out j anyelement, out k anyarray) as $$ + begin + j := i; + k := array[j,j]; + return; + end$$ language plpgsql; + select * from duplic(42); + j | k + ----+--------- + 42 | {42,42} + (1 row) + + select * from duplic('foo'::text); + j | k + -----+----------- + foo | {foo,foo} + (1 row) + + drop function duplic(anyelement); + -- + -- test PERFORM + -- + create table perform_test ( + a INT, + b INT + ); + create function simple_func(int) returns boolean as ' + BEGIN + IF $1 < 20 THEN + INSERT INTO perform_test VALUES ($1, $1 + 10); + RETURN TRUE; + ELSE + RETURN FALSE; + END IF; + END;' language plpgsql; + create function perform_test_func() returns void as ' + BEGIN + IF FOUND then + INSERT INTO perform_test VALUES (100, 100); + END IF; + + PERFORM simple_func(5); + + IF FOUND then + INSERT INTO perform_test VALUES (100, 100); + END IF; + + PERFORM simple_func(50); + + IF FOUND then + INSERT INTO perform_test VALUES (100, 100); + END IF; + + RETURN; + END;' language plpgsql; + SELECT perform_test_func(); + perform_test_func + ------------------- + + (1 row) + + SELECT * FROM perform_test; + a | b + -----+----- + 5 | 15 + 100 | 100 + 100 | 100 + (3 rows) + + drop table perform_test; + -- + -- Test error trapping + -- + create function trap_zero_divide(int) returns int as $$ + declare x int; + sx smallint; + begin + begin -- start a subtransaction + raise notice 'should see this'; + x := 100 / $1; + raise notice 'should see this only if % <> 0', $1; + sx := $1; + raise notice 'should see this only if % fits in smallint', $1; + if $1 < 0 then + raise exception '% is less than zero', $1; + end if; + exception + when division_by_zero then + raise notice 'caught division_by_zero'; + x := -1; + when NUMERIC_VALUE_OUT_OF_RANGE then + raise notice 'caught numeric_value_out_of_range'; + x := -2; + end; + return x; + end$$ language plpgsql; + select trap_zero_divide(50); + NOTICE: should see this + NOTICE: should see this only if 50 <> 0 + NOTICE: should see this only if 50 fits in smallint + trap_zero_divide + ------------------ + 2 + (1 row) + + select trap_zero_divide(0); + NOTICE: should see this + NOTICE: caught division_by_zero + trap_zero_divide + ------------------ + -1 + (1 row) + + select trap_zero_divide(100000); + NOTICE: should see this + NOTICE: should see this only if 100000 <> 0 + NOTICE: caught numeric_value_out_of_range + trap_zero_divide + ------------------ + -2 + (1 row) + + select trap_zero_divide(-100); + NOTICE: should see this + NOTICE: should see this only if -100 <> 0 + NOTICE: should see this only if -100 fits in smallint + ERROR: -100 is less than zero + create function trap_matching_test(int) returns int as $$ + declare x int; + sx smallint; + y int; + begin + begin -- start a subtransaction + x := 100 / $1; + sx := $1; + select into y unique1 from tenk1 where unique2 = + (select unique2 from tenk1 b where ten = $1); + exception + when data_exception then -- category match + raise notice 'caught data_exception'; + x := -1; + when NUMERIC_VALUE_OUT_OF_RANGE OR CARDINALITY_VIOLATION then + raise notice 'caught numeric_value_out_of_range or cardinality_violation'; + x := -2; + end; + return x; + end$$ language plpgsql; + select trap_matching_test(50); + trap_matching_test + -------------------- + 2 + (1 row) + + select trap_matching_test(0); + NOTICE: caught data_exception + trap_matching_test + -------------------- + -1 + (1 row) + + select trap_matching_test(100000); + NOTICE: caught data_exception + trap_matching_test + -------------------- + -1 + (1 row) + + select trap_matching_test(1); + NOTICE: caught numeric_value_out_of_range or cardinality_violation + trap_matching_test + -------------------- + -2 + (1 row) + + create temp table foo (f1 int); + create function blockme() returns int as $$ + declare x int; + begin + x := 1; + insert into foo values(x); + begin + x := x + 1; + insert into foo values(x); + -- we assume this will take longer than 2 seconds: + select count(*) into x from tenk1 a, tenk1 b, tenk1 c; + exception + when others then + raise notice 'caught others?'; + return -1; + when query_canceled then + raise notice 'nyeah nyeah, can''t stop me'; + x := x * 10; + end; + insert into foo values(x); + return x; + end$$ language plpgsql; + set statement_timeout to 2000; + select blockme(); + NOTICE: nyeah nyeah, can't stop me + blockme + --------- + 20 + (1 row) + + reset statement_timeout; + select * from foo; + f1 + ---- + 1 + 20 + (2 rows) + + drop table foo; + -- Test for pass-by-ref values being stored in proper context + create function test_variable_storage() returns text as $$ + declare x text; + begin + x := '1234'; + begin + x := x || '5678'; + -- force error inside subtransaction SPI context + perform trap_zero_divide(-100); + exception + when others then + x := x || '9012'; + end; + return x; + end$$ language plpgsql; + select test_variable_storage(); + NOTICE: should see this + CONTEXT: SQL statement "SELECT trap_zero_divide(-100)" + PL/pgSQL function "test_variable_storage" line 7 at PERFORM + NOTICE: should see this only if -100 <> 0 + CONTEXT: SQL statement "SELECT trap_zero_divide(-100)" + PL/pgSQL function "test_variable_storage" line 7 at PERFORM + NOTICE: should see this only if -100 fits in smallint + CONTEXT: SQL statement "SELECT trap_zero_divide(-100)" + PL/pgSQL function "test_variable_storage" line 7 at PERFORM + test_variable_storage + ----------------------- + 123456789012 + (1 row) + + -- + -- test foreign key error trapping + -- + create temp table master(f1 int primary key); + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "master_pkey" for table "master" + create temp table slave(f1 int references master deferrable); + insert into master values(1); + insert into slave values(1); + insert into slave values(2); -- fails + ERROR: insert or update on table "slave" violates foreign key constraint "slave_f1_fkey" + DETAIL: Key (f1)=(2) is not present in table "master". + create function trap_foreign_key(int) returns int as $$ + begin + begin -- start a subtransaction + insert into slave values($1); + exception + when foreign_key_violation then + raise notice 'caught foreign_key_violation'; + return 0; + end; + return 1; + end$$ language plpgsql; + create function trap_foreign_key_2() returns int as $$ + begin + begin -- start a subtransaction + set constraints all immediate; + exception + when foreign_key_violation then + raise notice 'caught foreign_key_violation'; + return 0; + end; + return 1; + end$$ language plpgsql; + select trap_foreign_key(1); + trap_foreign_key + ------------------ + 1 + (1 row) + + select trap_foreign_key(2); -- detects FK violation + NOTICE: caught foreign_key_violation + trap_foreign_key + ------------------ + 0 + (1 row) + + begin; + set constraints all deferred; + select trap_foreign_key(2); -- should not detect FK violation + trap_foreign_key + ------------------ + 1 + (1 row) + + savepoint x; + set constraints all immediate; -- fails + ERROR: insert or update on table "slave" violates foreign key constraint "slave_f1_fkey" + DETAIL: Key (f1)=(2) is not present in table "master". + rollback to x; + select trap_foreign_key_2(); -- detects FK violation + NOTICE: caught foreign_key_violation + trap_foreign_key_2 + -------------------- + 0 + (1 row) + + commit; -- still fails + ERROR: insert or update on table "slave" violates foreign key constraint "slave_f1_fkey" + DETAIL: Key (f1)=(2) is not present in table "master". + drop function trap_foreign_key(int); + drop function trap_foreign_key_2(); + -- + -- Test proper snapshot handling in simple expressions + -- + create temp table users(login text, id serial); + NOTICE: CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id" + create function sp_id_user(a_login text) returns int as $$ + declare x int; + begin + select into x id from users where login = a_login; + if found then return x; end if; + return 0; + end$$ language plpgsql stable; + insert into users values('user1'); + select sp_id_user('user1'); + sp_id_user + ------------ + 1 + (1 row) + + select sp_id_user('userx'); + sp_id_user + ------------ + 0 + (1 row) + + create function sp_add_user(a_login text) returns int as $$ + declare my_id_user int; + begin + my_id_user = sp_id_user( a_login ); + IF my_id_user > 0 THEN + RETURN -1; -- error code for existing user + END IF; + INSERT INTO users ( login ) VALUES ( a_login ); + my_id_user = sp_id_user( a_login ); + IF my_id_user = 0 THEN + RETURN -2; -- error code for insertion failure + END IF; + RETURN my_id_user; + end$$ language plpgsql; + select sp_add_user('user1'); + sp_add_user + ------------- + -1 + (1 row) + + select sp_add_user('user2'); + sp_add_user + ------------- + 2 + (1 row) + + select sp_add_user('user2'); + sp_add_user + ------------- + -1 + (1 row) + + select sp_add_user('user3'); + sp_add_user + ------------- + 3 + (1 row) + + select sp_add_user('user3'); + sp_add_user + ------------- + -1 + (1 row) + + drop function sp_add_user(text); + drop function sp_id_user(text); + -- + -- tests for refcursors + -- + create table rc_test (a int, b int); + copy rc_test from stdin; + create function return_refcursor(rc refcursor) returns refcursor as $$ + begin + open rc for select a from rc_test; + return rc; + end + $$ language plpgsql; + create function refcursor_test1(refcursor) returns refcursor as $$ + begin + perform return_refcursor($1); + return $1; + end + $$ language plpgsql; + begin; + select refcursor_test1('test1'); + refcursor_test1 + ----------------- + test1 + (1 row) + + fetch next in test1; + a + --- + 5 + (1 row) + + select refcursor_test1('test2'); + refcursor_test1 + ----------------- + test2 + (1 row) + + fetch all from test2; + a + ----- + 5 + 50 + 500 + (3 rows) + + commit; + -- should fail + fetch next from test1; + ERROR: cursor "test1" does not exist + create function refcursor_test2(int, int) returns boolean as $$ + declare + c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2; + nonsense record; + begin + open c1($1, $2); + fetch c1 into nonsense; + close c1; + if found then + return true; + else + return false; + end if; + end + $$ language plpgsql; + select refcursor_test2(20000, 20000) as "Should be false", + refcursor_test2(20, 20) as "Should be true"; + Should be false | Should be true + -----------------+---------------- + f | t + (1 row) + + -- + -- tests for "raise" processing + -- + create function raise_test1(int) returns int as $$ + begin + raise notice 'This message has too many parameters!', $1; + return $1; + end; + $$ language plpgsql; + select raise_test1(5); + ERROR: too many parameters specified for RAISE + CONTEXT: PL/pgSQL function "raise_test1" line 2 at RAISE + create function raise_test2(int) returns int as $$ + begin + raise notice 'This message has too few parameters: %, %, %', $1, $1; + return $1; + end; + $$ language plpgsql; + select raise_test2(10); + ERROR: too few parameters specified for RAISE + CONTEXT: PL/pgSQL function "raise_test2" line 2 at RAISE + -- + -- reject function definitions that contain malformed SQL queries at + -- compile-time, where possible + -- + create function bad_sql1() returns int as $$ + declare a int; + begin + a := 5; + Johnny Yuma; + a := 10; + return a; + end$$ language plpgsql; + ERROR: syntax error at or near "Johnny" + LINE 1: Johnny Yuma + ^ + QUERY: Johnny Yuma + CONTEXT: SQL statement in PL/PgSQL function "bad_sql1" near line 4 + create function bad_sql2() returns int as $$ + declare r record; + begin + for r in select I fought the law, the law won LOOP + raise notice 'in loop'; + end loop; + return 5; + end;$$ language plpgsql; + ERROR: syntax error at or near "the" + LINE 1: select I fought the law, the law won + ^ + QUERY: select I fought the law, the law won + CONTEXT: SQL statement in PL/PgSQL function "bad_sql2" near line 3 + -- a RETURN expression is mandatory, except for void-returning + -- functions, where it is not allowed + create function missing_return_expr() returns int as $$ + begin + return ; + end;$$ language plpgsql; + ERROR: syntax error at end of input + LINE 1: SELECT + ^ + QUERY: SELECT + CONTEXT: SQL statement in PL/PgSQL function "missing_return_expr" near line 2 + create function void_return_expr() returns void as $$ + begin + return 5; + end;$$ language plpgsql; + ERROR: RETURN cannot have a parameter in function returning void at or near "5" + LINE 3: return 5; + ^ + -- VOID functions are allowed to omit RETURN + create function void_return_expr() returns void as $$ + begin + perform 2+2; + end;$$ language plpgsql; + select void_return_expr(); + void_return_expr + ------------------ + + (1 row) + + -- but ordinary functions are not + create function missing_return_expr() returns int as $$ + begin + perform 2+2; + end;$$ language plpgsql; + select missing_return_expr(); + ERROR: control reached end of function without RETURN + CONTEXT: PL/pgSQL function "missing_return_expr" + drop function void_return_expr(); + drop function missing_return_expr(); + -- + -- EXECUTE ... INTO test + -- + create table eifoo (i integer, y integer); + create type eitype as (i integer, y integer); + create or replace function execute_into_test(varchar) returns record as $$ + declare + _r record; + _rt eifoo%rowtype; + _v eitype; + i int; + j int; + k int; + begin + execute 'insert into '||$1||' values(10,15)'; + execute 'select (row).* from (select row(10,1)::eifoo) s' into _r; + raise notice '% %', _r.i, _r.y; + execute 'select * from '||$1||' limit 1' into _rt; + raise notice '% %', _rt.i, _rt.y; + execute 'select *, 20 from '||$1||' limit 1' into i, j, k; + raise notice '% % %', i, j, k; + execute 'select 1,2' into _v; + return _v; + end; $$ language plpgsql; + select execute_into_test('eifoo'); + NOTICE: 10 1 + NOTICE: 10 15 + NOTICE: 10 15 20 + execute_into_test + ------------------- + (1,2) + (1 row) + + drop table eifoo cascade; + drop type eitype cascade; + -- + -- SQLSTATE and SQLERRM test + -- + create function excpt_test1() returns void as $$ + begin + raise notice '% %', sqlstate, sqlerrm; + end; $$ language plpgsql; + -- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION + -- blocks + select excpt_test1(); + ERROR: column "sqlstate" does not exist + LINE 1: SELECT sqlstate + ^ + QUERY: SELECT sqlstate + CONTEXT: PL/pgSQL function "excpt_test1" line 2 at RAISE + create function excpt_test2() returns void as $$ + begin + begin + begin + raise notice '% %', sqlstate, sqlerrm; + end; + end; + end; $$ language plpgsql; + -- should fail + select excpt_test2(); + ERROR: column "sqlstate" does not exist + LINE 1: SELECT sqlstate + ^ + QUERY: SELECT sqlstate + CONTEXT: PL/pgSQL function "excpt_test2" line 4 at RAISE + create function excpt_test3() returns void as $$ + begin + begin + raise exception 'user exception'; + exception when others then + raise notice 'caught exception % %', sqlstate, sqlerrm; + begin + raise notice '% %', sqlstate, sqlerrm; + perform 10/0; + exception + when substring_error then + -- this exception handler shouldn't be invoked + raise notice 'unexpected exception: % %', sqlstate, sqlerrm; + when division_by_zero then + raise notice 'caught exception % %', sqlstate, sqlerrm; + end; + raise notice '% %', sqlstate, sqlerrm; + end; + end; $$ language plpgsql; + select excpt_test3(); + NOTICE: caught exception P0001 user exception + NOTICE: P0001 user exception + NOTICE: caught exception 22012 division by zero + NOTICE: P0001 user exception + excpt_test3 + ------------- + + (1 row) + + drop function excpt_test1(); + drop function excpt_test2(); + drop function excpt_test3(); + -- parameters of raise stmt can be expressions + create function raise_exprs() returns void as $$ + declare + a integer[] = '{10,20,30}'; + c varchar = 'xyz'; + i integer; + begin + i := 2; + raise notice '%; %; %; %; %; %', a, a[i], c, (select c || 'abc'), row(10,'aaa',NULL,30), NULL; + end;$$ language plpgsql; + select raise_exprs(); + NOTICE: {10,20,30}; 20; xyz; xyzabc; (10,aaa,,30); + raise_exprs + ------------- + + (1 row) + + drop function raise_exprs(); + -- continue statement + create table conttesttbl(idx serial, v integer); + NOTICE: CREATE TABLE will create implicit sequence "conttesttbl_idx_seq" for serial column "conttesttbl.idx" + insert into conttesttbl(v) values(10); + insert into conttesttbl(v) values(20); + insert into conttesttbl(v) values(30); + insert into conttesttbl(v) values(40); + create function continue_test1() returns void as $$ + declare _i integer = 0; _r record; + begin + raise notice '---1---'; + loop + _i := _i + 1; + raise notice '%', _i; + continue when _i < 10; + exit; + end loop; + + raise notice '---2---'; + <> + loop + _i := _i - 1; + loop + raise notice '%', _i; + continue lbl when _i > 0; + exit lbl; + end loop; + end loop; + + raise notice '---3---'; + <> + while _i < 10 loop + _i := _i + 1; + continue the_loop when _i % 2 = 0; + raise notice '%', _i; + end loop; + + raise notice '---4---'; + for _i in 1..10 loop + begin + -- applies to outer loop, not the nested begin block + continue when _i < 5; + raise notice '%', _i; + end; + end loop; + + raise notice '---5---'; + for _r in select * from conttesttbl loop + continue when _r.v <= 20; + raise notice '%', _r.v; + end loop; + + raise notice '---6---'; + for _r in execute 'select * from conttesttbl' loop + continue when _r.v <= 20; + raise notice '%', _r.v; + end loop; + + raise notice '---7---'; + for _i in 1..3 loop + raise notice '%', _i; + continue when _i = 3; + end loop; + + raise notice '---8---'; + _i := 1; + while _i <= 3 loop + raise notice '%', _i; + _i := _i + 1; + continue when _i = 3; + end loop; + + raise notice '---9---'; + for _r in select * from conttesttbl order by v limit 1 loop + raise notice '%', _r.v; + continue; + end loop; + + raise notice '---10---'; + for _r in execute 'select * from conttesttbl order by v limit 1' loop + raise notice '%', _r.v; + continue; + end loop; + end; $$ language plpgsql; + select continue_test1(); + NOTICE: ---1--- + NOTICE: 1 + NOTICE: 2 + NOTICE: 3 + NOTICE: 4 + NOTICE: 5 + NOTICE: 6 + NOTICE: 7 + NOTICE: 8 + NOTICE: 9 + NOTICE: 10 + NOTICE: ---2--- + NOTICE: 9 + NOTICE: 8 + NOTICE: 7 + NOTICE: 6 + NOTICE: 5 + NOTICE: 4 + NOTICE: 3 + NOTICE: 2 + NOTICE: 1 + NOTICE: 0 + NOTICE: ---3--- + NOTICE: 1 + NOTICE: 3 + NOTICE: 5 + NOTICE: 7 + NOTICE: 9 + NOTICE: ---4--- + NOTICE: 5 + NOTICE: 6 + NOTICE: 7 + NOTICE: 8 + NOTICE: 9 + NOTICE: 10 + NOTICE: ---5--- + NOTICE: 30 + NOTICE: 40 + NOTICE: ---6--- + NOTICE: 30 + NOTICE: 40 + NOTICE: ---7--- + NOTICE: 1 + NOTICE: 2 + NOTICE: 3 + NOTICE: ---8--- + NOTICE: 1 + NOTICE: 2 + NOTICE: 3 + NOTICE: ---9--- + NOTICE: 10 + NOTICE: ---10--- + NOTICE: 10 + continue_test1 + ---------------- + + (1 row) + + -- CONTINUE is only legal inside a loop + create function continue_test2() returns void as $$ + begin + begin + continue; + end; + return; + end; + $$ language plpgsql; + -- should fail + select continue_test2(); + ERROR: CONTINUE cannot be used outside a loop + CONTEXT: PL/pgSQL function "continue_test2" + -- CONTINUE can't reference the label of a named block + create function continue_test3() returns void as $$ + begin + <> + begin + loop + continue begin_block1; + end loop; + end; + end; + $$ language plpgsql; + -- should fail + select continue_test3(); + ERROR: CONTINUE cannot be used outside a loop + CONTEXT: PL/pgSQL function "continue_test3" + drop function continue_test1(); + drop function continue_test2(); + drop function continue_test3(); + drop table conttesttbl; + -- verbose end block and end loop + create function end_label1() returns void as $$ + <> + begin + <> + for _i in 1 .. 10 loop + exit flbl1; + end loop flbl1; + <> + for _i in 1 .. 10 loop + exit flbl2; + end loop; + end blbl; + $$ language plpgsql; + select end_label1(); + end_label1 + ------------ + + (1 row) + + drop function end_label1(); + -- should fail: undefined end label + create function end_label2() returns void as $$ + begin + for _i in 1 .. 10 loop + exit; + end loop flbl1; + end; + $$ language plpgsql; + ERROR: no such label at or near "flbl1" + LINE 5: end loop flbl1; + ^ + -- should fail: end label does not match start label + create function end_label3() returns void as $$ + <> + begin + <> + for _i in 1 .. 10 loop + exit; + end loop outer_label; + end; + $$ language plpgsql; + ERROR: end label "outer_label" differs from block's label "inner_label" + CONTEXT: compile of PL/pgSQL function "end_label3" near line 6 + -- should fail: end label on a block without a start label + create function end_label4() returns void as $$ + <> + begin + for _i in 1 .. 10 loop + exit; + end loop outer_label; + end; + $$ language plpgsql; + ERROR: end label "outer_label" specified for unlabelled block + CONTEXT: compile of PL/pgSQL function "end_label4" near line 5 + -- using list of scalars in fori and fore stmts + create function for_vect() returns void as $proc$ + <>declare a integer; b varchar; c varchar; r record; + begin + -- fori + for i in 1 .. 3 loop + raise notice '%', i; + end loop; + -- fore with record var + for r in select gs as aa, 'BB' as bb, 'CC' as cc from generate_series(1,4) gs loop + raise notice '% % %', r.aa, r.bb, r.cc; + end loop; + -- fore with single scalar + for a in select gs from generate_series(1,4) gs loop + raise notice '%', a; + end loop; + -- fore with multiple scalars + for a,b,c in select gs, 'BB','CC' from generate_series(1,4) gs loop + raise notice '% % %', a, b, c; + end loop; + -- using qualified names in fors, fore is enabled, disabled only for fori + for lbl.a, lbl.b, lbl.c in execute $$select gs, 'bb','cc' from generate_series(1,4) gs$$ loop + raise notice '% % %', a, b, c; + end loop; + end; + $proc$ language plpgsql; + select for_vect(); + NOTICE: 1 + NOTICE: 2 + NOTICE: 3 + NOTICE: 1 BB CC + NOTICE: 2 BB CC + NOTICE: 3 BB CC + NOTICE: 4 BB CC + NOTICE: 1 + NOTICE: 2 + NOTICE: 3 + NOTICE: 4 + NOTICE: 1 BB CC + NOTICE: 2 BB CC + NOTICE: 3 BB CC + NOTICE: 4 BB CC + NOTICE: 1 bb cc + NOTICE: 2 bb cc + NOTICE: 3 bb cc + NOTICE: 4 bb cc + for_vect + ---------- + + (1 row) + + -- regression test: verify that multiple uses of same plpgsql datum within + -- a SQL command all get mapped to the same $n parameter. The return value + -- of the SELECT is not important, we only care that it doesn't fail with + -- a complaint about an ungrouped column reference. + create function multi_datum_use(p1 int) returns bool as $$ + declare + x int; + y int; + begin + select into x,y unique1/p1, unique1/$1 from tenk1 group by unique1/p1; + return x = y; + end$$ language plpgsql; + select multi_datum_use(42); + multi_datum_use + ----------------- + t + (1 row) + + -- + -- Test STRICT limiter in both planned and EXECUTE invocations. + -- Note that a data-modifying query is quasi strict (disallow multi rows) + -- by default in the planned case, but not in EXECUTE. + -- + create temp table foo (f1 int, f2 int); + insert into foo values (1,2), (3,4); + create or replace function footest() returns void as $$ + declare x record; + begin + -- should work + insert into foo values(5,6) returning * into x; + raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; + end$$ language plpgsql; + select footest(); + NOTICE: x.f1 = 5, x.f2 = 6 + footest + --------- + + (1 row) + + create or replace function footest() returns void as $$ + declare x record; + begin + -- should fail due to implicit strict + insert into foo values(7,8),(9,10) returning * into x; + raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; + end$$ language plpgsql; + select footest(); + ERROR: query returned more than one row + CONTEXT: PL/pgSQL function "footest" line 4 at SQL statement + create or replace function footest() returns void as $$ + declare x record; + begin + -- should work + execute 'insert into foo values(5,6) returning *' into x; + raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; + end$$ language plpgsql; + select footest(); + NOTICE: x.f1 = 5, x.f2 = 6 + footest + --------- + + (1 row) + + create or replace function footest() returns void as $$ + declare x record; + begin + -- this should work since EXECUTE isn't as picky + execute 'insert into foo values(7,8),(9,10) returning *' into x; + raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; + end$$ language plpgsql; + select footest(); + NOTICE: x.f1 = 7, x.f2 = 8 + footest + --------- + + (1 row) + + select * from foo; + f1 | f2 + ----+---- + 1 | 2 + 3 | 4 + 5 | 6 + 5 | 6 + 7 | 8 + 9 | 10 + (6 rows) + + create or replace function footest() returns void as $$ + declare x record; + begin + -- should work + select * from foo where f1 = 3 into strict x; + raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; + end$$ language plpgsql; + select footest(); + NOTICE: x.f1 = 3, x.f2 = 4 + footest + --------- + + (1 row) + + create or replace function footest() returns void as $$ + declare x record; + begin + -- should fail, no rows + select * from foo where f1 = 0 into strict x; + raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; + end$$ language plpgsql; + select footest(); + ERROR: query returned no rows + CONTEXT: PL/pgSQL function "footest" line 4 at SQL statement + create or replace function footest() returns void as $$ + declare x record; + begin + -- should fail, too many rows + select * from foo where f1 > 3 into strict x; + raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; + end$$ language plpgsql; + select footest(); + ERROR: query returned more than one row + CONTEXT: PL/pgSQL function "footest" line 4 at SQL statement + create or replace function footest() returns void as $$ + declare x record; + begin + -- should work + execute 'select * from foo where f1 = 3' into strict x; + raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; + end$$ language plpgsql; + select footest(); + NOTICE: x.f1 = 3, x.f2 = 4 + footest + --------- + + (1 row) + + create or replace function footest() returns void as $$ + declare x record; + begin + -- should fail, no rows + execute 'select * from foo where f1 = 0' into strict x; + raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; + end$$ language plpgsql; + select footest(); + ERROR: query returned no rows + CONTEXT: PL/pgSQL function "footest" line 4 at EXECUTE statement + create or replace function footest() returns void as $$ + declare x record; + begin + -- should fail, too many rows + execute 'select * from foo where f1 > 3' into strict x; + raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; + end$$ language plpgsql; + select footest(); + ERROR: query returned more than one row + CONTEXT: PL/pgSQL function "footest" line 4 at EXECUTE statement + drop function footest(); + -- test scrollable cursor support + create function sc_test() returns setof integer as $$ + declare + c scroll cursor for select f1 from int4_tbl; + x integer; + begin + open c; + fetch last from c into x; + while found loop + return next x; + fetch prior from c into x; + end loop; + close c; + end; + $$ language plpgsql; + select * from sc_test(); + sc_test + ------------- + -2147483647 + 2147483647 + -123456 + 123456 + 0 + (5 rows) + + create or replace function sc_test() returns setof integer as $$ + declare + c no scroll cursor for select f1 from int4_tbl; + x integer; + begin + open c; + fetch last from c into x; + while found loop + return next x; + fetch prior from c into x; + end loop; + close c; + end; + $$ language plpgsql; + select * from sc_test(); -- fails because of NO SCROLL specification + ERROR: cursor can only scan forward + HINT: Declare it with SCROLL option to enable backward scan. + CONTEXT: PL/pgSQL function "sc_test" line 6 at FETCH + create or replace function sc_test() returns setof integer as $$ + declare + c refcursor; + x integer; + begin + open c scroll for select f1 from int4_tbl; + fetch last from c into x; + while found loop + return next x; + fetch prior from c into x; + end loop; + close c; + end; + $$ language plpgsql; + select * from sc_test(); + sc_test + ------------- + -2147483647 + 2147483647 + -123456 + 123456 + 0 + (5 rows) + + create or replace function sc_test() returns setof integer as $$ + declare + c refcursor; + x integer; + begin + open c scroll for execute 'select f1 from int4_tbl'; + fetch last from c into x; + while found loop + return next x; + fetch relative -2 from c into x; + end loop; + close c; + end; + $$ language plpgsql; + select * from sc_test(); + sc_test + ------------- + -2147483647 + -123456 + 0 + (3 rows) + + create or replace function sc_test() returns setof integer as $$ + declare + c cursor for select * from generate_series(1, 10); + x integer; + begin + open c; + loop + move relative 2 in c; + if not found then + exit; + end if; + fetch next from c into x; + if found then + return next x; + end if; + end loop; + close c; + end; + $$ language plpgsql; + select * from sc_test(); + sc_test + --------- + 3 + 6 + 9 + (3 rows) + + drop function sc_test(); + -- test qualified variable names + create function pl_qual_names (param1 int) returns void as $$ + <> + declare + param1 int := 1; + begin + <> + declare + param1 int := 2; + begin + raise notice 'param1 = %', param1; + raise notice 'pl_qual_names.param1 = %', pl_qual_names.param1; + raise notice 'outerblock.param1 = %', outerblock.param1; + raise notice 'innerblock.param1 = %', innerblock.param1; + end; + end; + $$ language plpgsql; + select pl_qual_names(42); + NOTICE: param1 = 2 + NOTICE: pl_qual_names.param1 = 42 + NOTICE: outerblock.param1 = 1 + NOTICE: innerblock.param1 = 2 + pl_qual_names + --------------- + + (1 row) + + drop function pl_qual_names(int); + -- tests for RETURN QUERY + create function ret_query1(out int, out int) returns setof record as $$ + begin + $1 := -1; + $2 := -2; + return next; + return query select x + 1, x * 10 from generate_series(0, 10) s (x); + return next; + end; + $$ language plpgsql; + select * from ret_query1(); + column1 | column2 + ---------+--------- + -1 | -2 + 1 | 0 + 2 | 10 + 3 | 20 + 4 | 30 + 5 | 40 + 6 | 50 + 7 | 60 + 8 | 70 + 9 | 80 + 10 | 90 + 11 | 100 + -1 | -2 + (13 rows) + + create type record_type as (x text, y int, z boolean); + create or replace function ret_query2(lim int) returns setof record_type as $$ + begin + return query select md5(s.x::text), s.x, s.x > 0 + from generate_series(-8, lim) s (x) where s.x % 2 = 0; + end; + $$ language plpgsql; + select * from ret_query2(8); + x | y | z + ----------------------------------+----+--- + a8d2ec85eaf98407310b72eb73dda247 | -8 | f + 596a3d04481816330f07e4f97510c28f | -6 | f + 0267aaf632e87a63288a08331f22c7c3 | -4 | f + 5d7b9adcbe1c629ec722529dd12e5129 | -2 | f + cfcd208495d565ef66e7dff9f98764da | 0 | f + c81e728d9d4c2f636f067f89cc14862c | 2 | t + a87ff679a2f3e71d9181a67b7542122c | 4 | t + 1679091c5a880faf6fb5e6087eb1b2dc | 6 | t + c9f0f895fb98ab9159f51fd0297e236d | 8 | t + (9 rows) + + -- test EXECUTE USING + create function exc_using(int, text) returns int as $$ + declare i int; + begin + for i in execute 'select * from generate_series(1,$1)' using $1+1 loop + raise notice '%', i; + end loop; + execute 'select $2 + $2*3 + length($1)' into i using $2,$1; + return i; + end + $$ language plpgsql; + select exc_using(5, 'foobar'); + NOTICE: 1 + NOTICE: 2 + NOTICE: 3 + NOTICE: 4 + NOTICE: 5 + NOTICE: 6 + exc_using + ----------- + 26 + (1 row) + + -- test FOR-over-cursor + create or replace function forc01() returns void as $$ + declare + c cursor(r1 integer, r2 integer) + for select * from generate_series(r1,r2) i; + c2 cursor + for select * from generate_series(41,43) i; + begin + for r in c(5,7) loop + raise notice '% from %', r.i, c; + end loop; + -- again, to test if cursor was closed properly + for r in c(9,10) loop + raise notice '% from %', r.i, c; + end loop; + -- and test a parameterless cursor + for r in c2 loop + raise notice '% from %', r.i, c2; + end loop; + -- and try it with a hand-assigned name + raise notice 'after loop, c2 = %', c2; + c2 := 'special_name'; + for r in c2 loop + raise notice '% from %', r.i, c2; + end loop; + raise notice 'after loop, c2 = %', c2; + -- and try it with a generated name + -- (which we can't show in the output because it's variable) + c2 := null; + for r in c2 loop + raise notice '%', r.i; + end loop; + raise notice 'after loop, c2 = %', c2; + return; + end; + $$ language plpgsql; + select forc01(); + NOTICE: 5 from c + NOTICE: 6 from c + NOTICE: 7 from c + NOTICE: 9 from c + NOTICE: 10 from c + NOTICE: 41 from c2 + NOTICE: 42 from c2 + NOTICE: 43 from c2 + NOTICE: after loop, c2 = c2 + NOTICE: 41 from special_name + NOTICE: 42 from special_name + NOTICE: 43 from special_name + NOTICE: after loop, c2 = special_name + NOTICE: 41 + NOTICE: 42 + NOTICE: 43 + NOTICE: after loop, c2 = + forc01 + -------- + + (1 row) + + -- try updating the cursor's current row + create temp table forc_test as + select n as i, n as j from generate_series(1,10) n; + create or replace function forc01() returns void as $$ + declare + c cursor for select * from forc_test; + begin + for r in c loop + raise notice '%, %', r.i, r.j; + update forc_test set i = i * 100, j = r.j * 2 where current of c; + end loop; + end; + $$ language plpgsql; + select forc01(); + NOTICE: 1, 1 + NOTICE: 2, 2 + NOTICE: 3, 3 + NOTICE: 4, 4 + NOTICE: 5, 5 + NOTICE: 6, 6 + NOTICE: 7, 7 + NOTICE: 8, 8 + NOTICE: 9, 9 + NOTICE: 10, 10 + forc01 + -------- + + (1 row) + + select * from forc_test; + i | j + ------+---- + 100 | 2 + 200 | 4 + 300 | 6 + 400 | 8 + 500 | 10 + 600 | 12 + 700 | 14 + 800 | 16 + 900 | 18 + 1000 | 20 + (10 rows) + + drop function forc01(); + -- fail because cursor has no query bound to it + create or replace function forc_bad() returns void as $$ + declare + c refcursor; + begin + for r in c loop + raise notice '%', r.i; + end loop; + end; + $$ language plpgsql; + ERROR: cursor FOR loop must use a bound cursor variable + CONTEXT: compile of PL/pgSQL function "forc_bad" near line 4 + -- test RETURN QUERY EXECUTE + create or replace function return_dquery() + returns setof int as $$ + begin + return query execute 'select * from (values(10),(20)) f'; + return query execute 'select * from (values($1),($2)) f' using 40,50; + end; + $$ language plpgsql; + select * from return_dquery(); + return_dquery + --------------- + 10 + 20 + 40 + 50 + (4 rows) + + drop function return_dquery(); + -- Tests for 8.4's new RAISE features + create or replace function raise_test() returns void as $$ + begin + raise notice '% % %', 1, 2, 3 + using errcode = '55001', detail = 'some detail info', hint = 'some hint'; + raise '% % %', 1, 2, 3 + using errcode = 'division_by_zero', detail = 'some detail info'; + end; + $$ language plpgsql; + select raise_test(); + NOTICE: 1 2 3 + DETAIL: some detail info + HINT: some hint + ERROR: 1 2 3 + DETAIL: some detail info + -- Since we can't actually see the thrown SQLSTATE in default psql output, + -- test it like this; this also tests re-RAISE + create or replace function raise_test() returns void as $$ + begin + raise 'check me' + using errcode = 'division_by_zero', detail = 'some detail info'; + exception + when others then + raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; + raise; + end; + $$ language plpgsql; + select raise_test(); + NOTICE: SQLSTATE: 22012 SQLERRM: check me + ERROR: check me + DETAIL: some detail info + create or replace function raise_test() returns void as $$ + begin + raise 'check me' + using errcode = '1234F', detail = 'some detail info'; + exception + when others then + raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; + raise; + end; + $$ language plpgsql; + select raise_test(); + NOTICE: SQLSTATE: 1234F SQLERRM: check me + ERROR: check me + DETAIL: some detail info + -- SQLSTATE specification in WHEN + create or replace function raise_test() returns void as $$ + begin + raise 'check me' + using errcode = '1234F', detail = 'some detail info'; + exception + when sqlstate '1234F' then + raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; + raise; + end; + $$ language plpgsql; + select raise_test(); + NOTICE: SQLSTATE: 1234F SQLERRM: check me + ERROR: check me + DETAIL: some detail info + create or replace function raise_test() returns void as $$ + begin + raise division_by_zero using detail = 'some detail info'; + exception + when others then + raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm; + raise; + end; + $$ language plpgsql; + select raise_test(); + NOTICE: SQLSTATE: 22012 SQLERRM: division_by_zero + ERROR: division_by_zero + DETAIL: some detail info + create or replace function raise_test() returns void as $$ + begin + raise division_by_zero; + end; + $$ language plpgsql; + select raise_test(); + ERROR: division_by_zero + create or replace function raise_test() returns void as $$ + begin + raise sqlstate '1234F'; + end; + $$ language plpgsql; + select raise_test(); + ERROR: 1234F + create or replace function raise_test() returns void as $$ + begin + raise division_by_zero using message = 'custom' || ' message'; + end; + $$ language plpgsql; + select raise_test(); + ERROR: custom message + create or replace function raise_test() returns void as $$ + begin + raise using message = 'custom' || ' message', errcode = '22012'; + end; + $$ language plpgsql; + select raise_test(); + ERROR: custom message + -- conflict on message + create or replace function raise_test() returns void as $$ + begin + raise notice 'some message' using message = 'custom' || ' message', errcode = '22012'; + end; + $$ language plpgsql; + select raise_test(); + ERROR: RAISE option already specified: MESSAGE + CONTEXT: PL/pgSQL function "raise_test" line 2 at RAISE + -- conflict on errcode + create or replace function raise_test() returns void as $$ + begin + raise division_by_zero using message = 'custom' || ' message', errcode = '22012'; + end; + $$ language plpgsql; + select raise_test(); + ERROR: RAISE option already specified: ERRCODE + CONTEXT: PL/pgSQL function "raise_test" line 2 at RAISE + -- nothing to re-RAISE + create or replace function raise_test() returns void as $$ + begin + raise; + end; + $$ language plpgsql; + select raise_test(); + ERROR: RAISE without parameters cannot be used outside an exception handler + CONTEXT: PL/pgSQL function "raise_test" + drop function raise_test(); + -- test CASE statement + create or replace function case_test(bigint) returns text as $$ + declare a int = 10; + b int = 1; + begin + case $1 + when 1 then + return 'one'; + when 2 then + return 'two'; + when 3,4,3+5 then + return 'three, four or eight'; + when a then + return 'ten'; + when a+b, a+b+1 then + return 'eleven, twelve'; + end case; + end; + $$ language plpgsql immutable; + select case_test(1); + case_test + ----------- + one + (1 row) + + select case_test(2); + case_test + ----------- + two + (1 row) + + select case_test(3); + case_test + ---------------------- + three, four or eight + (1 row) + + select case_test(4); + case_test + ---------------------- + three, four or eight + (1 row) + + select case_test(5); -- fails + ERROR: case not found + HINT: CASE statement is missing ELSE part. + CONTEXT: PL/pgSQL function "case_test" line 4 at CASE + select case_test(8); + case_test + ---------------------- + three, four or eight + (1 row) + + select case_test(10); + case_test + ----------- + ten + (1 row) + + select case_test(11); + case_test + ---------------- + eleven, twelve + (1 row) + + select case_test(12); + case_test + ---------------- + eleven, twelve + (1 row) + + select case_test(13); -- fails + ERROR: case not found + HINT: CASE statement is missing ELSE part. + CONTEXT: PL/pgSQL function "case_test" line 4 at CASE + create or replace function catch() returns void as $$ + begin + raise notice '%', case_test(6); + exception + when case_not_found then + raise notice 'caught case_not_found % %', SQLSTATE, SQLERRM; + end + $$ language plpgsql; + select catch(); + NOTICE: caught case_not_found 20000 case not found + catch + ------- + + (1 row) + + -- test the searched variant too, as well as ELSE + create or replace function case_test(bigint) returns text as $$ + declare a int = 10; + begin + case + when $1 = 1 then + return 'one'; + when $1 = a + 2 then + return 'twelve'; + else + return 'other'; + end case; + end; + $$ language plpgsql immutable; + select case_test(1); + case_test + ----------- + one + (1 row) + + select case_test(2); + case_test + ----------- + other + (1 row) + + select case_test(12); + case_test + ----------- + twelve + (1 row) + + select case_test(13); + case_test + ----------- + other + (1 row) + + drop function catch(); + drop function case_test(bigint); + -- test variadic functions + create or replace function vari(variadic int[]) + returns void as $$ + begin + for i in array_lower($1,1)..array_upper($1,1) loop + raise notice '%', $1[i]; + end loop; end; + $$ language plpgsql; + select vari(1,2,3,4,5); + NOTICE: 1 + NOTICE: 2 + NOTICE: 3 + NOTICE: 4 + NOTICE: 5 + vari + ------ + + (1 row) + + select vari(3,4,5); + NOTICE: 3 + NOTICE: 4 + NOTICE: 5 + vari + ------ + + (1 row) + + select vari(variadic array[5,6,7]); + NOTICE: 5 + NOTICE: 6 + NOTICE: 7 + vari + ------ + + (1 row) + + drop function vari(int[]); + -- coercion test + create or replace function pleast(variadic numeric[]) + returns numeric as $$ + declare aux numeric = $1[array_lower($1,1)]; + begin + for i in array_lower($1,1)+1..array_upper($1,1) loop + if $1[i] < aux then aux := $1[i]; end if; + end loop; + return aux; + end; + $$ language plpgsql immutable strict; + select pleast(10,1,2,3,-16); + pleast + -------- + -16 + (1 row) + + select pleast(10.2,2.2,-1.1); + pleast + -------- + -1.1 + (1 row) + + select pleast(10.2,10, -20); + pleast + -------- + -20 + (1 row) + + select pleast(10,20, -1.0); + pleast + -------- + -1.0 + (1 row) + + -- in case of conflict, non-variadic version is preferred + create or replace function pleast(numeric) + returns numeric as $$ + begin + raise notice 'non-variadic function called'; + return $1; + end; + $$ language plpgsql immutable strict; + select pleast(10); + NOTICE: non-variadic function called + pleast + -------- + 10 + (1 row) + + drop function pleast(numeric[]); + drop function pleast(numeric); + -- test table functions + create function tftest(int) returns table(a int, b int) as $$ + begin + return query select $1, $1+i from generate_series(1,5) g(i); + end; + $$ language plpgsql immutable strict; + select * from tftest(10); + a | b + ----+---- + 10 | 11 + 10 | 12 + 10 | 13 + 10 | 14 + 10 | 15 + (5 rows) + + create or replace function tftest(a1 int) returns table(a int, b int) as $$ + begin + a := a1; b := a1 + 1; + return next; + a := a1 * 10; b := a1 * 10 + 1; + return next; + end; + $$ language plpgsql immutable strict; + select * from tftest(10); + a | b + -----+----- + 10 | 11 + 100 | 101 + (2 rows) + + drop function tftest(int); diff -Nrc pgsql.orig.1f40b253cd5f/src/test/regress/expected/varchar_2.out pgsql.orig/src/test/regress/expected/varchar_2.out *** pgsql.orig.1f40b253cd5f/src/test/regress/expected/varchar_2.out 1970-01-01 01:00:00.000000000 +0100 --- pgsql.orig/src/test/regress/expected/varchar_2.out 2009-02-02 20:37:40.206203470 +0100 *************** *** 0 **** --- 1,111 ---- + -- + -- VARCHAR + -- + CREATE TABLE VARCHAR_TBL(f1 varchar(1)); + INSERT INTO VARCHAR_TBL (f1) VALUES ('a'); + INSERT INTO VARCHAR_TBL (f1) VALUES ('A'); + -- any of the following three input formats are acceptable + INSERT INTO VARCHAR_TBL (f1) VALUES ('1'); + INSERT INTO VARCHAR_TBL (f1) VALUES (2); + INSERT INTO VARCHAR_TBL (f1) VALUES ('3'); + -- zero-length char + INSERT INTO VARCHAR_TBL (f1) VALUES (''); + -- try varchar's of greater than 1 length + INSERT INTO VARCHAR_TBL (f1) VALUES ('cd'); + ERROR: value too long for type character varying(1) + INSERT INTO VARCHAR_TBL (f1) VALUES ('c '); + SELECT '' AS seven, * FROM VARCHAR_TBL; + seven | f1 + -------+---- + | a + | A + | 1 + | 2 + | 3 + | + | c + (7 rows) + + SELECT '' AS six, c.* + FROM VARCHAR_TBL c + WHERE c.f1 <> 'a'; + six | f1 + -----+---- + | A + | 1 + | 2 + | 3 + | + | c + (6 rows) + + SELECT '' AS one, c.* + FROM VARCHAR_TBL c + WHERE c.f1 = 'a'; + one | f1 + -----+---- + | a + (1 row) + + SELECT '' AS five, c.* + FROM VARCHAR_TBL c + WHERE c.f1 < 'a'; + five | f1 + ------+---- + | + (1 row) + + SELECT '' AS six, c.* + FROM VARCHAR_TBL c + WHERE c.f1 <= 'a'; + six | f1 + -----+---- + | a + | + (2 rows) + + SELECT '' AS one, c.* + FROM VARCHAR_TBL c + WHERE c.f1 > 'a'; + one | f1 + -----+---- + | A + | 1 + | 2 + | 3 + | c + (5 rows) + + SELECT '' AS two, c.* + FROM VARCHAR_TBL c + WHERE c.f1 >= 'a'; + two | f1 + -----+---- + | a + | A + | 1 + | 2 + | 3 + | c + (6 rows) + + DROP TABLE VARCHAR_TBL; + -- + -- Now test longer arrays of char + -- + CREATE TABLE VARCHAR_TBL(f1 varchar(4)); + INSERT INTO VARCHAR_TBL (f1) VALUES ('a'); + INSERT INTO VARCHAR_TBL (f1) VALUES ('ab'); + INSERT INTO VARCHAR_TBL (f1) VALUES ('abcd'); + INSERT INTO VARCHAR_TBL (f1) VALUES ('abcde'); + ERROR: value too long for type character varying(4) + INSERT INTO VARCHAR_TBL (f1) VALUES ('abcd '); + SELECT '' AS four, * FROM VARCHAR_TBL; + four | f1 + ------+------ + | a + | ab + | abcd + | abcd + (4 rows) +