diff options
Diffstat (limited to 'src/test/regress/expected/plpgsql.out')
| -rw-r--r-- | src/test/regress/expected/plpgsql.out | 5781 |
1 files changed, 5781 insertions, 0 deletions
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out new file mode 100644 index 0000000..08e42f1 --- /dev/null +++ b/src/test/regress/expected/plpgsql.out @@ -0,0 +1,5781 @@ +-- +-- 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; + ps alias for new; +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'' and 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; + 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 ' +<<outer>> +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 first floor'); +insert into PSlot values ('PS.first.a1', 'PF1_1', '', 'WS.101.1a'); +insert into PSlot values ('PS.first.a2', 'PF1_1', '', 'WS.101.1b'); +insert into PSlot values ('PS.first.a3', 'PF1_1', '', 'WS.101.2a'); +insert into PSlot values ('PS.first.a4', 'PF1_1', '', 'WS.101.2b'); +insert into PSlot values ('PS.first.a5', 'PF1_1', '', 'WS.101.3a'); +insert into PSlot values ('PS.first.a6', 'PF1_1', '', 'WS.101.3b'); +insert into PSlot values ('PS.first.b1', 'PF1_1', '', 'WS.102.1a'); +insert into PSlot values ('PS.first.b2', 'PF1_1', '', 'WS.102.1b'); +insert into PSlot values ('PS.first.b3', 'PF1_1', '', 'WS.102.2a'); +insert into PSlot values ('PS.first.b4', 'PF1_1', '', 'WS.102.2b'); +insert into PSlot values ('PS.first.b5', 'PF1_1', '', 'WS.102.3a'); +insert into PSlot values ('PS.first.b6', 'PF1_1', '', 'WS.102.3b'); +insert into PSlot values ('PS.first.c1', 'PF1_1', '', 'WS.105.1a'); +insert into PSlot values ('PS.first.c2', 'PF1_1', '', 'WS.105.1b'); +insert into PSlot values ('PS.first.c3', 'PF1_1', '', 'WS.105.2a'); +insert into PSlot values ('PS.first.c4', 'PF1_1', '', 'WS.105.2b'); +insert into PSlot values ('PS.first.c5', 'PF1_1', '', 'WS.105.3a'); +insert into PSlot values ('PS.first.c6', 'PF1_1', '', 'WS.105.3b'); +insert into PSlot values ('PS.first.d1', 'PF1_1', '', 'WS.106.1a'); +insert into PSlot values ('PS.first.d2', 'PF1_1', '', 'WS.106.1b'); +insert into PSlot values ('PS.first.d3', 'PF1_1', '', 'WS.106.2a'); +insert into PSlot values ('PS.first.d4', 'PF1_1', '', 'WS.106.2b'); +insert into PSlot values ('PS.first.d5', 'PF1_1', '', 'WS.106.3a'); +insert into PSlot values ('PS.first.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 first floor'); +insert into PSlot values ('PS.first.ta1', 'PF1_2', '', ''); +insert into PSlot values ('PS.first.ta2', 'PF1_2', '', ''); +insert into PSlot values ('PS.first.ta3', 'PF1_2', '', ''); +insert into PSlot values ('PS.first.ta4', 'PF1_2', '', ''); +insert into PSlot values ('PS.first.ta5', 'PF1_2', '', ''); +insert into PSlot values ('PS.first.ta6', 'PF1_2', '', ''); +insert into PSlot values ('PS.first.tb1', 'PF1_2', '', ''); +insert into PSlot values ('PS.first.tb2', 'PF1_2', '', ''); +insert into PSlot values ('PS.first.tb3', 'PF1_2', '', ''); +insert into PSlot values ('PS.first.tb4', 'PF1_2', '', ''); +insert into PSlot values ('PS.first.tb5', 'PF1_2', '', ''); +insert into PSlot values ('PS.first.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.first.a1 | PF1_1 | | WS.101.1a + PS.first.a2 | PF1_1 | | WS.101.1b + PS.first.a3 | PF1_1 | | WS.101.2a + PS.first.a4 | PF1_1 | | WS.101.2b + PS.first.a5 | PF1_1 | | WS.101.3a + PS.first.a6 | PF1_1 | | WS.101.3b + PS.first.b1 | PF1_1 | | WS.102.1a + PS.first.b2 | PF1_1 | | WS.102.1b + PS.first.b3 | PF1_1 | | WS.102.2a + PS.first.b4 | PF1_1 | | WS.102.2b + PS.first.b5 | PF1_1 | | WS.102.3a + PS.first.b6 | PF1_1 | | WS.102.3b + PS.first.c1 | PF1_1 | | WS.105.1a + PS.first.c2 | PF1_1 | | WS.105.1b + PS.first.c3 | PF1_1 | | WS.105.2a + PS.first.c4 | PF1_1 | | WS.105.2b + PS.first.c5 | PF1_1 | | WS.105.3a + PS.first.c6 | PF1_1 | | WS.105.3b + PS.first.d1 | PF1_1 | | WS.106.1a + PS.first.d2 | PF1_1 | | WS.106.1b + PS.first.d3 | PF1_1 | | WS.106.2a + PS.first.d4 | PF1_1 | | WS.106.2b + PS.first.d5 | PF1_1 | | WS.106.3a + PS.first.d6 | PF1_1 | | WS.106.3b + PS.first.ta1 | PF1_2 | | + PS.first.ta2 | PF1_2 | | + PS.first.ta3 | PF1_2 | | + PS.first.ta4 | PF1_2 | | + PS.first.ta5 | PF1_2 | | + PS.first.ta6 | PF1_2 | | + PS.first.tb1 | PF1_2 | | + PS.first.tb2 | PF1_2 | | + PS.first.tb3 | PF1_2 | | + PS.first.tb4 | PF1_2 | | + PS.first.tb5 | PF1_2 | | + PS.first.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.first.a1 + WS.101.1b | 101 | | PS.first.a2 + WS.101.2a | 101 | | PS.first.a3 + WS.101.2b | 101 | | PS.first.a4 + WS.101.3a | 101 | | PS.first.a5 + WS.101.3b | 101 | | PS.first.a6 + WS.102.1a | 102 | | PS.first.b1 + WS.102.1b | 102 | | PS.first.b2 + WS.102.2a | 102 | | PS.first.b3 + WS.102.2b | 102 | | PS.first.b4 + WS.102.3a | 102 | | PS.first.b5 + WS.102.3b | 102 | | PS.first.b6 + WS.105.1a | 105 | | PS.first.c1 + WS.105.1b | 105 | | PS.first.c2 + WS.105.2a | 105 | | PS.first.c3 + WS.105.2b | 105 | | PS.first.c4 + WS.105.3a | 105 | | PS.first.c5 + WS.105.3b | 105 | | PS.first.c6 + WS.106.1a | 106 | | PS.first.d1 + WS.106.1b | 106 | | PS.first.d2 + WS.106.2a | 106 | | PS.first.d3 + WS.106.2b | 106 | | PS.first.d4 + WS.106.3a | 106 | | PS.first.d5 + WS.106.3b | 106 | | PS.first.d6 +(42 rows) + +-- +-- Install the central phone system and create the phone numbers. +-- They are wired 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.first.ta1'); +insert into PLine values ('PL.016', '-137', '', 'PS.first.ta3'); +insert into PLine values ('PL.017', '-139', '', 'PS.first.ta4'); +insert into PLine values ('PL.018', '-362', '', 'PS.first.tb1'); +insert into PLine values ('PL.019', '-363', '', 'PS.first.tb2'); +insert into PLine values ('PL.020', '-364', '', 'PS.first.tb3'); +insert into PLine values ('PL.021', '-365', '', 'PS.first.tb5'); +insert into PLine values ('PL.022', '-367', '', 'PS.first.tb6'); +insert into PLine values ('PL.028', '-501', 'Fax entrance', 'PS.base.ta2'); +insert into PLine values ('PL.029', '-502', 'Fax first floor', 'PS.first.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" +DETAIL: Key (name)=(PF1_1) already exists. +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_set(character,character) line 30 at RAISE +PL/pgSQL function tg_backlink_a() line 17 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_set(character,character) line 47 at RAISE +PL/pgSQL function tg_backlink_a() line 17 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_set(character,character) line 30 at RAISE +PL/pgSQL function tg_slotlink_a() line 17 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_set(character,character) line 77 at RAISE +PL/pgSQL function tg_slotlink_a() line 17 at assignment +insert into HSlot values ('HS', 'base.hub1', 1, ''); +ERROR: duplicate key value violates unique constraint "hslot_name" +DETAIL: Key (slotname)=(HS.base.hub1.1 ) already exists. +insert into HSlot values ('HS', 'base.hub1', 20, ''); +ERROR: no manual manipulation of HSlot +CONTEXT: PL/pgSQL function tg_hslot_biu() line 12 at RAISE +delete from HSlot; +ERROR: no manual manipulation of HSlot +CONTEXT: PL/pgSQL function tg_hslot_bd() line 12 at RAISE +insert into IFace values ('IF', 'notthere', 'eth0', ''); +ERROR: system "notthere" does not exist +CONTEXT: PL/pgSQL function tg_iface_biu() line 8 at RAISE +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) +CONTEXT: PL/pgSQL function tg_iface_biu() line 14 at RAISE +-- +-- 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 some simple polymorphism cases. +-- +create function f1(x anyelement) returns anyelement as $$ +begin + return x + 1; +end$$ language plpgsql; +select f1(42) as int, f1(4.5) as num; + int | num +-----+----- + 43 | 5.5 +(1 row) + +select f1(point(3,4)); -- fail for lack of + operator +ERROR: operator does not exist: point + integer +LINE 1: x + 1 + ^ +HINT: No operator matches the given name and argument types. You might need to add explicit type casts. +QUERY: x + 1 +CONTEXT: PL/pgSQL function f1(anyelement) line 3 at RETURN +drop function f1(x anyelement); +create function f1(x anyelement) returns anyarray as $$ +begin + return array[x + 1, x + 2]; +end$$ language plpgsql; +select f1(42) as int, f1(4.5) as num; + int | num +---------+----------- + {43,44} | {5.5,6.5} +(1 row) + +drop function f1(x anyelement); +create function f1(x anyarray) returns anyelement as $$ +begin + return x[1]; +end$$ language plpgsql; +select f1(array[2,4]) as int, f1(array[4.5, 7.7]) as num; + int | num +-----+----- + 2 | 4.5 +(1 row) + +select f1(stavalues1) from pg_statistic; -- fail, can't infer element type +ERROR: cannot determine element type of "anyarray" argument +drop function f1(x anyarray); +create function f1(x anyarray) returns anyarray as $$ +begin + return x; +end$$ language plpgsql; +select f1(array[2,4]) as int, f1(array[4.5, 7.7]) as num; + int | num +-------+----------- + {2,4} | {4.5,7.7} +(1 row) + +select f1(stavalues1) from pg_statistic; -- fail, can't infer element type +ERROR: PL/pgSQL functions cannot accept type anyarray +CONTEXT: compilation of PL/pgSQL function "f1" near line 1 +drop function f1(x anyarray); +-- fail, can't infer type: +create function f1(x anyelement) returns anyrange as $$ +begin + return array[x + 1, x + 2]; +end$$ language plpgsql; +ERROR: cannot determine result data type +DETAIL: A result of type anyrange requires at least one input of type anyrange or anymultirange. +create function f1(x anyrange) returns anyarray as $$ +begin + return array[lower(x), upper(x)]; +end$$ language plpgsql; +select f1(int4range(42, 49)) as int, f1(float8range(4.5, 7.8)) as num; + int | num +---------+----------- + {42,49} | {4.5,7.8} +(1 row) + +drop function f1(x anyrange); +create function f1(x anycompatible, y anycompatible) returns anycompatiblearray as $$ +begin + return array[x, y]; +end$$ language plpgsql; +select f1(2, 4) as int, f1(2, 4.5) as num; + int | num +-------+--------- + {2,4} | {2,4.5} +(1 row) + +drop function f1(x anycompatible, y anycompatible); +create function f1(x anycompatiblerange, y anycompatible, z anycompatible) returns anycompatiblearray as $$ +begin + return array[lower(x), upper(x), y, z]; +end$$ language plpgsql; +select f1(int4range(42, 49), 11, 2::smallint) as int, f1(float8range(4.5, 7.8), 7.8, 11::real) as num; + int | num +--------------+------------------ + {42,49,11,2} | {4.5,7.8,7.8,11} +(1 row) + +select f1(int4range(42, 49), 11, 4.5) as fail; -- range type doesn't fit +ERROR: function f1(int4range, integer, numeric) does not exist +LINE 1: select f1(int4range(42, 49), 11, 4.5) as fail; + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +drop function f1(x anycompatiblerange, y anycompatible, z anycompatible); +-- fail, can't infer type: +create function f1(x anycompatible) returns anycompatiblerange as $$ +begin + return array[x + 1, x + 2]; +end$$ language plpgsql; +ERROR: cannot determine result data type +DETAIL: A result of type anycompatiblerange requires at least one input of type anycompatiblerange or anycompatiblemultirange. +create function f1(x anycompatiblerange, y anycompatiblearray) returns anycompatiblerange as $$ +begin + return x; +end$$ language plpgsql; +select f1(int4range(42, 49), array[11]) as int, f1(float8range(4.5, 7.8), array[7]) as num; + int | num +---------+----------- + [42,49) | [4.5,7.8) +(1 row) + +drop function f1(x anycompatiblerange, y anycompatiblearray); +create function f1(a anyelement, b anyarray, + c anycompatible, d anycompatible, + OUT x anyarray, OUT y anycompatiblearray) +as $$ +begin + x := a || b; + y := array[c, d]; +end$$ language plpgsql; +select x, pg_typeof(x), y, pg_typeof(y) + from f1(11, array[1, 2], 42, 34.5); + x | pg_typeof | y | pg_typeof +----------+-----------+-----------+----------- + {11,1,2} | integer[] | {42,34.5} | numeric[] +(1 row) + +select x, pg_typeof(x), y, pg_typeof(y) + from f1(11, array[1, 2], point(1,2), point(3,4)); + x | pg_typeof | y | pg_typeof +----------+-----------+-------------------+----------- + {11,1,2} | integer[] | {"(1,2)","(3,4)"} | point[] +(1 row) + +select x, pg_typeof(x), y, pg_typeof(y) + from f1(11, '{1,2}', point(1,2), '(3,4)'); + x | pg_typeof | y | pg_typeof +----------+-----------+-------------------+----------- + {11,1,2} | integer[] | {"(1,2)","(3,4)"} | point[] +(1 row) + +select x, pg_typeof(x), y, pg_typeof(y) + from f1(11, array[1, 2.2], 42, 34.5); -- fail +ERROR: function f1(integer, numeric[], integer, numeric) does not exist +LINE 2: from f1(11, array[1, 2.2], 42, 34.5); + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +drop function f1(a anyelement, b anyarray, + c anycompatible, d anycompatible); +-- +-- 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 +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); +create function duplic(in i anycompatiblerange, out j anycompatible, out k anycompatiblearray) as $$ +begin + j := lower(i); + k := array[lower(i),upper(i)]; + return; +end$$ language plpgsql; +select * from duplic(int4range(42,49)); + j | k +----+--------- + 42 | {42,49} +(1 row) + +select * from duplic(textrange('aaa', 'bbb')); + j | k +-----+----------- + aaa | {aaa,bbb} +(1 row) + +drop function duplic(anycompatiblerange); +-- +-- test PERFORM +-- +create table perform_test ( + a INT, + b INT +); +create function perform_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 perform_simple_func(5); + + IF FOUND then + INSERT INTO perform_test VALUES (100, 100); + END IF; + + PERFORM 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 proper snapshot handling in simple expressions +-- +create temp table users(login text, id serial); +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_unnamed_refcursor() returns refcursor as $$ +declare + rc refcursor; +begin + open rc for select a from rc_test; + return rc; +end +$$ language plpgsql; +create function use_refcursor(rc refcursor) returns int as $$ +declare + rc refcursor; + x record; +begin + rc := return_unnamed_refcursor(); + fetch next from rc into x; + return x.a; +end +$$ language plpgsql; +select use_refcursor(return_unnamed_refcursor()); + use_refcursor +--------------- + 5 +(1 row) + +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) + +-- should fail +create function constant_refcursor() returns refcursor as $$ +declare + rc constant refcursor; +begin + open rc for select a from rc_test; + return rc; +end +$$ language plpgsql; +select constant_refcursor(); +ERROR: variable "rc" is declared CONSTANT +CONTEXT: PL/pgSQL function constant_refcursor() line 5 at OPEN +-- but it's okay like this +create or replace function constant_refcursor() returns refcursor as $$ +declare + rc constant refcursor := 'my_cursor_name'; +begin + open rc for select a from rc_test; + return rc; +end +$$ language plpgsql; +select constant_refcursor(); + constant_refcursor +-------------------- + my_cursor_name +(1 row) + +-- +-- tests for cursors with named parameter arguments +-- +create function namedparmcursor_test1(int, int) returns boolean as $$ +declare + c1 cursor (param1 int, param12 int) for select * from rc_test where a > param1 and b > param12; + nonsense record; +begin + open c1(param12 := $2, param1 := $1); + fetch c1 into nonsense; + close c1; + if found then + return true; + else + return false; + end if; +end +$$ language plpgsql; +select namedparmcursor_test1(20000, 20000) as "Should be false", + namedparmcursor_test1(20, 20) as "Should be true"; + Should be false | Should be true +-----------------+---------------- + f | t +(1 row) + +-- mixing named and positional argument notations +create function namedparmcursor_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(param1 := $1, $2); + fetch c1 into nonsense; + close c1; + if found then + return true; + else + return false; + end if; +end +$$ language plpgsql; +select namedparmcursor_test2(20, 20); + namedparmcursor_test2 +----------------------- + t +(1 row) + +-- mixing named and positional: param2 is given twice, once in named notation +-- and second time in positional notation. Should throw an error at parse time +create function namedparmcursor_test3() returns void as $$ +declare + c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2; +begin + open c1(param2 := 20, 21); +end +$$ language plpgsql; +ERROR: value for parameter "param2" of cursor "c1" specified more than once +LINE 5: open c1(param2 := 20, 21); + ^ +-- mixing named and positional: same as previous test, but param1 is duplicated +create function namedparmcursor_test4() returns void as $$ +declare + c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2; +begin + open c1(20, param1 := 21); +end +$$ language plpgsql; +ERROR: value for parameter "param1" of cursor "c1" specified more than once +LINE 5: open c1(20, param1 := 21); + ^ +-- duplicate named parameter, should throw an error at parse time +create function namedparmcursor_test5() returns void as $$ +declare + c1 cursor (p1 int, p2 int) for + select * from tenk1 where thousand = p1 and tenthous = p2; +begin + open c1 (p2 := 77, p2 := 42); +end +$$ language plpgsql; +ERROR: value for parameter "p2" of cursor "c1" specified more than once +LINE 6: open c1 (p2 := 77, p2 := 42); + ^ +-- not enough parameters, should throw an error at parse time +create function namedparmcursor_test6() returns void as $$ +declare + c1 cursor (p1 int, p2 int) for + select * from tenk1 where thousand = p1 and tenthous = p2; +begin + open c1 (p2 := 77); +end +$$ language plpgsql; +ERROR: not enough arguments for cursor "c1" +LINE 6: open c1 (p2 := 77); + ^ +-- division by zero runtime error, the context given in the error message +-- should be sensible +create function namedparmcursor_test7() returns void as $$ +declare + c1 cursor (p1 int, p2 int) for + select * from tenk1 where thousand = p1 and tenthous = p2; +begin + open c1 (p2 := 77, p1 := 42/0); +end $$ language plpgsql; +select namedparmcursor_test7(); +ERROR: division by zero +CONTEXT: SQL expression "42/0 AS p1, 77 AS p2" +PL/pgSQL function namedparmcursor_test7() line 6 at OPEN +-- check that line comments work correctly within the argument list (there +-- is some special handling of this case in the code: the newline after the +-- comment must be preserved when the argument-evaluating query is +-- constructed, otherwise the comment effectively comments out the next +-- argument, too) +create function namedparmcursor_test8() returns int4 as $$ +declare + c1 cursor (p1 int, p2 int) for + select count(*) from tenk1 where thousand = p1 and tenthous = p2; + n int4; +begin + open c1 (77 -- test + , 42); + fetch c1 into n; + return n; +end $$ language plpgsql; +select namedparmcursor_test8(); + namedparmcursor_test8 +----------------------- + 0 +(1 row) + +-- cursor parameter name can match plpgsql variable or unreserved keyword +create function namedparmcursor_test9(p1 int) returns int4 as $$ +declare + c1 cursor (p1 int, p2 int, debug int) for + select count(*) from tenk1 where thousand = p1 and tenthous = p2 + and four = debug; + p2 int4 := 1006; + n int4; +begin + open c1 (p1 := p1, p2 := p2, debug := 2); + fetch c1 into n; + return n; +end $$ language plpgsql; +select namedparmcursor_test9(6); + namedparmcursor_test9 +----------------------- + 1 +(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; +ERROR: too many parameters specified for RAISE +CONTEXT: compilation of PL/pgSQL function "raise_test1" near line 3 +create function raise_test2(int) returns int as $$ +begin + raise notice 'This message has too few parameters: %, %, %', $1, $1; + return $1; +end; +$$ language plpgsql; +ERROR: too few parameters specified for RAISE +CONTEXT: compilation of PL/pgSQL function "raise_test2" near line 3 +create function raise_test3(int) returns int as $$ +begin + raise notice 'This message has no parameters (despite having %% signs in it)!'; + return $1; +end; +$$ language plpgsql; +select raise_test3(1); +NOTICE: This message has no parameters (despite having % signs in it)! + raise_test3 +------------- + 1 +(1 row) + +-- Test re-RAISE inside a nested exception block. This case is allowed +-- by Oracle's PL/SQL but was handled differently by PG before 9.1. +CREATE FUNCTION reraise_test() RETURNS void AS $$ +BEGIN + BEGIN + RAISE syntax_error; + EXCEPTION + WHEN syntax_error THEN + BEGIN + raise notice 'exception % thrown in inner block, reraising', sqlerrm; + RAISE; + EXCEPTION + WHEN OTHERS THEN + raise notice 'RIGHT - exception % caught in inner block', sqlerrm; + END; + END; +EXCEPTION + WHEN OTHERS THEN + raise notice 'WRONG - exception % caught in outer block', sqlerrm; +END; +$$ LANGUAGE plpgsql; +SELECT reraise_test(); +NOTICE: exception syntax_error thrown in inner block, reraising +NOTICE: RIGHT - exception syntax_error caught in inner block + reraise_test +-------------- + +(1 row) + +-- +-- 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 5: Johnny Yuma; + ^ +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 4: for r in select I fought the law, the law won LOOP + ^ +-- 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: missing expression at or near ";" +LINE 3: return ; + ^ +create function void_return_expr() returns void as $$ +begin + return 5; +end;$$ language plpgsql; +ERROR: RETURN cannot have a parameter in function returning void +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: sqlstate + ^ +QUERY: sqlstate +CONTEXT: PL/pgSQL function excpt_test1() line 3 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: sqlstate + ^ +QUERY: sqlstate +CONTEXT: PL/pgSQL function excpt_test2() line 5 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) + +create function excpt_test4() returns text as $$ +begin + begin perform 1/0; + exception when others then return sqlerrm; end; +end; $$ language plpgsql; +select excpt_test4(); + excpt_test4 +------------------ + division by zero +(1 row) + +drop function excpt_test1(); +drop function excpt_test2(); +drop function excpt_test3(); +drop function excpt_test4(); +-- 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); <NULL> + raise_exprs +------------- + +(1 row) + +drop function raise_exprs(); +-- 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 stricttest() 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 stricttest(); +NOTICE: x.f1 = 5, x.f2 = 6 + stricttest +------------ + +(1 row) + +create or replace function stricttest() 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 stricttest(); +ERROR: query returned more than one row +HINT: Make sure the query returns a single row, or use LIMIT 1. +CONTEXT: PL/pgSQL function stricttest() line 5 at SQL statement +create or replace function stricttest() 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 stricttest(); +NOTICE: x.f1 = 5, x.f2 = 6 + stricttest +------------ + +(1 row) + +create or replace function stricttest() 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 stricttest(); +NOTICE: x.f1 = 7, x.f2 = 8 + stricttest +------------ + +(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 stricttest() 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 stricttest(); +NOTICE: x.f1 = 3, x.f2 = 4 + stricttest +------------ + +(1 row) + +create or replace function stricttest() 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 stricttest(); +ERROR: query returned no rows +CONTEXT: PL/pgSQL function stricttest() line 5 at SQL statement +create or replace function stricttest() 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 stricttest(); +ERROR: query returned more than one row +HINT: Make sure the query returns a single row, or use LIMIT 1. +CONTEXT: PL/pgSQL function stricttest() line 5 at SQL statement +create or replace function stricttest() 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 stricttest(); +NOTICE: x.f1 = 3, x.f2 = 4 + stricttest +------------ + +(1 row) + +create or replace function stricttest() 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 stricttest(); +ERROR: query returned no rows +CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE +create or replace function stricttest() 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 stricttest(); +ERROR: query returned more than one row +CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE +drop function stricttest(); +-- test printing parameters after failure due to STRICT +set plpgsql.print_strict_params to true; +create or replace function stricttest() returns void as $$ +declare +x record; +p1 int := 2; +p3 text := 'foo'; +begin + -- no rows + select * from foo where f1 = p1 and f1::text = p3 into strict x; + raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; +end$$ language plpgsql; +select stricttest(); +ERROR: query returned no rows +DETAIL: parameters: p1 = '2', p3 = 'foo' +CONTEXT: PL/pgSQL function stricttest() line 8 at SQL statement +create or replace function stricttest() returns void as $$ +declare +x record; +p1 int := 2; +p3 text := $a$'Valame Dios!' dijo Sancho; 'no le dije yo a vuestra merced que mirase bien lo que hacia?'$a$; +begin + -- no rows + select * from foo where f1 = p1 and f1::text = p3 into strict x; + raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; +end$$ language plpgsql; +select stricttest(); +ERROR: query returned no rows +DETAIL: parameters: p1 = '2', p3 = '''Valame Dios!'' dijo Sancho; ''no le dije yo a vuestra merced que mirase bien lo que hacia?''' +CONTEXT: PL/pgSQL function stricttest() line 8 at SQL statement +create or replace function stricttest() returns void as $$ +declare +x record; +p1 int := 2; +p3 text := 'foo'; +begin + -- too many rows + select * from foo where f1 > p1 or f1::text = p3 into strict x; + raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; +end$$ language plpgsql; +select stricttest(); +ERROR: query returned more than one row +DETAIL: parameters: p1 = '2', p3 = 'foo' +HINT: Make sure the query returns a single row, or use LIMIT 1. +CONTEXT: PL/pgSQL function stricttest() line 8 at SQL statement +create or replace function stricttest() returns void as $$ +declare x record; +begin + -- too many rows, no params + select * from foo where f1 > 3 into strict x; + raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; +end$$ language plpgsql; +select stricttest(); +ERROR: query returned more than one row +HINT: Make sure the query returns a single row, or use LIMIT 1. +CONTEXT: PL/pgSQL function stricttest() line 5 at SQL statement +create or replace function stricttest() returns void as $$ +declare x record; +begin + -- no rows + execute 'select * from foo where f1 = $1 or f1::text = $2' using 0, 'foo' into strict x; + raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; +end$$ language plpgsql; +select stricttest(); +ERROR: query returned no rows +DETAIL: parameters: $1 = '0', $2 = 'foo' +CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE +create or replace function stricttest() returns void as $$ +declare x record; +begin + -- too many rows + execute 'select * from foo where f1 > $1' using 1 into strict x; + raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; +end$$ language plpgsql; +select stricttest(); +ERROR: query returned more than one row +DETAIL: parameters: $1 = '1' +CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE +create or replace function stricttest() returns void as $$ +declare x record; +begin + -- too many rows, no parameters + execute 'select * from foo where f1 > 3' into strict x; + raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; +end$$ language plpgsql; +select stricttest(); +ERROR: query returned more than one row +CONTEXT: PL/pgSQL function stricttest() line 5 at EXECUTE +create or replace function stricttest() returns void as $$ +-- override the global +#print_strict_params off +declare +x record; +p1 int := 2; +p3 text := 'foo'; +begin + -- too many rows + select * from foo where f1 > p1 or f1::text = p3 into strict x; + raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; +end$$ language plpgsql; +select stricttest(); +ERROR: query returned more than one row +HINT: Make sure the query returns a single row, or use LIMIT 1. +CONTEXT: PL/pgSQL function stricttest() line 10 at SQL statement +reset plpgsql.print_strict_params; +create or replace function stricttest() returns void as $$ +-- override the global +#print_strict_params on +declare +x record; +p1 int := 2; +p3 text := 'foo'; +begin + -- too many rows + select * from foo where f1 > p1 or f1::text = p3 into strict x; + raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2; +end$$ language plpgsql; +select stricttest(); +ERROR: query returned more than one row +DETAIL: parameters: p1 = '2', p3 = 'foo' +HINT: Make sure the query returns a single row, or use LIMIT 1. +CONTEXT: PL/pgSQL function stricttest() line 10 at SQL statement +-- test warnings and errors +set plpgsql.extra_warnings to 'all'; +set plpgsql.extra_warnings to 'none'; +set plpgsql.extra_errors to 'all'; +set plpgsql.extra_errors to 'none'; +-- test warnings when shadowing a variable +set plpgsql.extra_warnings to 'shadowed_variables'; +-- simple shadowing of input and output parameters +create or replace function shadowtest(in1 int) + returns table (out1 int) as $$ +declare +in1 int; +out1 int; +begin +end +$$ language plpgsql; +WARNING: variable "in1" shadows a previously defined variable +LINE 4: in1 int; + ^ +WARNING: variable "out1" shadows a previously defined variable +LINE 5: out1 int; + ^ +select shadowtest(1); + shadowtest +------------ +(0 rows) + +set plpgsql.extra_warnings to 'shadowed_variables'; +select shadowtest(1); + shadowtest +------------ +(0 rows) + +create or replace function shadowtest(in1 int) + returns table (out1 int) as $$ +declare +in1 int; +out1 int; +begin +end +$$ language plpgsql; +WARNING: variable "in1" shadows a previously defined variable +LINE 4: in1 int; + ^ +WARNING: variable "out1" shadows a previously defined variable +LINE 5: out1 int; + ^ +select shadowtest(1); + shadowtest +------------ +(0 rows) + +drop function shadowtest(int); +-- shadowing in a second DECLARE block +create or replace function shadowtest() + returns void as $$ +declare +f1 int; +begin + declare + f1 int; + begin + end; +end$$ language plpgsql; +WARNING: variable "f1" shadows a previously defined variable +LINE 7: f1 int; + ^ +drop function shadowtest(); +-- several levels of shadowing +create or replace function shadowtest(in1 int) + returns void as $$ +declare +in1 int; +begin + declare + in1 int; + begin + end; +end$$ language plpgsql; +WARNING: variable "in1" shadows a previously defined variable +LINE 4: in1 int; + ^ +WARNING: variable "in1" shadows a previously defined variable +LINE 7: in1 int; + ^ +drop function shadowtest(int); +-- shadowing in cursor definitions +create or replace function shadowtest() + returns void as $$ +declare +f1 int; +c1 cursor (f1 int) for select 1; +begin +end$$ language plpgsql; +WARNING: variable "f1" shadows a previously defined variable +LINE 5: c1 cursor (f1 int) for select 1; + ^ +drop function shadowtest(); +-- test errors when shadowing a variable +set plpgsql.extra_errors to 'shadowed_variables'; +create or replace function shadowtest(f1 int) + returns boolean as $$ +declare f1 int; begin return 1; end $$ language plpgsql; +ERROR: variable "f1" shadows a previously defined variable +LINE 3: declare f1 int; begin return 1; end $$ language plpgsql; + ^ +select shadowtest(1); +ERROR: function shadowtest(integer) does not exist +LINE 1: select shadowtest(1); + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +reset plpgsql.extra_errors; +reset plpgsql.extra_warnings; +create or replace function shadowtest(f1 int) + returns boolean as $$ +declare f1 int; begin return 1; end $$ language plpgsql; +select shadowtest(1); + shadowtest +------------ + t +(1 row) + +-- runtime extra checks +set plpgsql.extra_warnings to 'too_many_rows'; +do $$ +declare x int; +begin + select v from generate_series(1,2) g(v) into x; +end; +$$; +WARNING: query returned more than one row +HINT: Make sure the query returns a single row, or use LIMIT 1. +set plpgsql.extra_errors to 'too_many_rows'; +do $$ +declare x int; +begin + select v from generate_series(1,2) g(v) into x; +end; +$$; +ERROR: query returned more than one row +HINT: Make sure the query returns a single row, or use LIMIT 1. +CONTEXT: PL/pgSQL function inline_code_block line 4 at SQL statement +reset plpgsql.extra_errors; +reset plpgsql.extra_warnings; +set plpgsql.extra_warnings to 'strict_multi_assignment'; +do $$ +declare + x int; + y int; +begin + select 1 into x, y; + select 1,2 into x, y; + select 1,2,3 into x, y; +end +$$; +WARNING: number of source and target fields in assignment does not match +DETAIL: strict_multi_assignment check of extra_warnings is active. +HINT: Make sure the query returns the exact list of columns. +WARNING: number of source and target fields in assignment does not match +DETAIL: strict_multi_assignment check of extra_warnings is active. +HINT: Make sure the query returns the exact list of columns. +set plpgsql.extra_errors to 'strict_multi_assignment'; +do $$ +declare + x int; + y int; +begin + select 1 into x, y; + select 1,2 into x, y; + select 1,2,3 into x, y; +end +$$; +ERROR: number of source and target fields in assignment does not match +DETAIL: strict_multi_assignment check of extra_errors is active. +HINT: Make sure the query returns the exact list of columns. +CONTEXT: PL/pgSQL function inline_code_block line 6 at SQL statement +create table test_01(a int, b int, c int); +alter table test_01 drop column a; +-- the check is active only when source table is not empty +insert into test_01 values(10,20); +do $$ +declare + x int; + y int; +begin + select * from test_01 into x, y; -- should be ok + raise notice 'ok'; + select * from test_01 into x; -- should to fail +end; +$$; +NOTICE: ok +ERROR: number of source and target fields in assignment does not match +DETAIL: strict_multi_assignment check of extra_errors is active. +HINT: Make sure the query returns the exact list of columns. +CONTEXT: PL/pgSQL function inline_code_block line 8 at SQL statement +do $$ +declare + t test_01; +begin + select 1, 2 into t; -- should be ok + raise notice 'ok'; + select 1, 2, 3 into t; -- should fail; +end; +$$; +NOTICE: ok +ERROR: number of source and target fields in assignment does not match +DETAIL: strict_multi_assignment check of extra_errors is active. +HINT: Make sure the query returns the exact list of columns. +CONTEXT: PL/pgSQL function inline_code_block line 7 at SQL statement +do $$ +declare + t test_01; +begin + select 1 into t; -- should fail; +end; +$$; +ERROR: number of source and target fields in assignment does not match +DETAIL: strict_multi_assignment check of extra_errors is active. +HINT: Make sure the query returns the exact list of columns. +CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement +drop table test_01; +reset plpgsql.extra_errors; +reset plpgsql.extra_warnings; +-- 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 7 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 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; + move backward 2 from c; + fetch relative -1 from c into x; + end loop; + close c; +end; +$$ language plpgsql; +select * from sc_test(); + sc_test +------------- + -2147483647 + 123456 +(2 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) + +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; + move forward all in c; + fetch backward from c into x; + if found then + return next x; + end if; + close c; +end; +$$ language plpgsql; +select * from sc_test(); + sc_test +--------- + 10 +(1 row) + +drop function sc_test(); +-- test qualified variable names +create function pl_qual_names (param1 int) returns void as $$ +<<outerblock>> +declare + param1 int := 1; +begin + <<innerblock>> + 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) + +drop function exc_using(int, text); +create or replace function exc_using(int) returns void as $$ +declare + c refcursor; + i int; +begin + open c for execute 'select * from generate_series(1,$1)' using $1+1; + loop + fetch c into i; + exit when not found; + raise notice '%', i; + end loop; + close c; + return; +end; +$$ language plpgsql; +select exc_using(5); +NOTICE: 1 +NOTICE: 2 +NOTICE: 3 +NOTICE: 4 +NOTICE: 5 +NOTICE: 6 + exc_using +----------- + +(1 row) + +drop function exc_using(int); +-- 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 = <NULL> + 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) + +-- same, with a cursor whose portal name doesn't match variable name +create or replace function forc01() returns void as $$ +declare + c refcursor := 'fooled_ya'; + r record; +begin + open c for select * from forc_test; + loop + fetch c into r; + exit when not found; + 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: 100, 2 +NOTICE: 200, 4 +NOTICE: 300, 6 +NOTICE: 400, 8 +NOTICE: 500, 10 +NOTICE: 600, 12 +NOTICE: 700, 14 +NOTICE: 800, 16 +NOTICE: 900, 18 +NOTICE: 1000, 20 + forc01 +-------- + +(1 row) + +select * from forc_test; + i | j +--------+---- + 10000 | 4 + 20000 | 8 + 30000 | 12 + 40000 | 16 + 50000 | 20 + 60000 | 24 + 70000 | 28 + 80000 | 32 + 90000 | 36 + 100000 | 40 +(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 +LINE 5: for r in c loop + ^ +-- 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(); +-- test RETURN QUERY with dropped columns +create table tabwithcols(a int, b int, c int, d int); +insert into tabwithcols values(10,20,30,40),(50,60,70,80); +create or replace function returnqueryf() +returns setof tabwithcols as $$ +begin + return query select * from tabwithcols; + return query execute 'select * from tabwithcols'; +end; +$$ language plpgsql; +select * from returnqueryf(); + a | b | c | d +----+----+----+---- + 10 | 20 | 30 | 40 + 50 | 60 | 70 | 80 + 10 | 20 | 30 | 40 + 50 | 60 | 70 | 80 +(4 rows) + +alter table tabwithcols drop column b; +select * from returnqueryf(); + a | c | d +----+----+---- + 10 | 30 | 40 + 50 | 70 | 80 + 10 | 30 | 40 + 50 | 70 | 80 +(4 rows) + +alter table tabwithcols drop column d; +select * from returnqueryf(); + a | c +----+---- + 10 | 30 + 50 | 70 + 10 | 30 + 50 | 70 +(4 rows) + +alter table tabwithcols add column d int; +select * from returnqueryf(); + a | c | d +----+----+--- + 10 | 30 | + 50 | 70 | + 10 | 30 | + 50 | 70 | +(4 rows) + +drop function returnqueryf(); +drop table tabwithcols; +-- +-- Tests for composite-type results +-- +create type compostype as (x int, y varchar); +-- test: use of variable of composite type in return statement +create or replace function compos() returns compostype as $$ +declare + v compostype; +begin + v := (1, 'hello'); + return v; +end; +$$ language plpgsql; +select compos(); + compos +----------- + (1,hello) +(1 row) + +-- test: use of variable of record type in return statement +create or replace function compos() returns compostype as $$ +declare + v record; +begin + v := (1, 'hello'::varchar); + return v; +end; +$$ language plpgsql; +select compos(); + compos +----------- + (1,hello) +(1 row) + +-- test: use of row expr in return statement +create or replace function compos() returns compostype as $$ +begin + return (1, 'hello'::varchar); +end; +$$ language plpgsql; +select compos(); + compos +----------- + (1,hello) +(1 row) + +-- this does not work currently (no implicit casting) +create or replace function compos() returns compostype as $$ +begin + return (1, 'hello'); +end; +$$ language plpgsql; +select compos(); +ERROR: returned record type does not match expected record type +DETAIL: Returned type unknown does not match expected type character varying in column 2. +CONTEXT: PL/pgSQL function compos() while casting return value to function's return type +-- ... but this does +create or replace function compos() returns compostype as $$ +begin + return (1, 'hello')::compostype; +end; +$$ language plpgsql; +select compos(); + compos +----------- + (1,hello) +(1 row) + +drop function compos(); +-- test: return a row expr as record. +create or replace function composrec() returns record as $$ +declare + v record; +begin + v := (1, 'hello'); + return v; +end; +$$ language plpgsql; +select composrec(); + composrec +----------- + (1,hello) +(1 row) + +-- test: return row expr in return statement. +create or replace function composrec() returns record as $$ +begin + return (1, 'hello'); +end; +$$ language plpgsql; +select composrec(); + composrec +----------- + (1,hello) +(1 row) + +drop function composrec(); +-- test: row expr in RETURN NEXT statement. +create or replace function compos() returns setof compostype as $$ +begin + for i in 1..3 + loop + return next (1, 'hello'::varchar); + end loop; + return next null::compostype; + return next (2, 'goodbye')::compostype; +end; +$$ language plpgsql; +select * from compos(); + x | y +---+--------- + 1 | hello + 1 | hello + 1 | hello + | + 2 | goodbye +(5 rows) + +drop function compos(); +-- test: use invalid expr in return statement. +create or replace function compos() returns compostype as $$ +begin + return 1 + 1; +end; +$$ language plpgsql; +select compos(); +ERROR: cannot return non-composite value from function returning composite type +CONTEXT: PL/pgSQL function compos() line 3 at RETURN +-- RETURN variable is a different code path ... +create or replace function compos() returns compostype as $$ +declare x int := 42; +begin + return x; +end; +$$ language plpgsql; +select * from compos(); +ERROR: cannot return non-composite value from function returning composite type +CONTEXT: PL/pgSQL function compos() line 4 at RETURN +drop function compos(); +-- test: invalid use of composite variable in scalar-returning function +create or replace function compos() returns int as $$ +declare + v compostype; +begin + v := (1, 'hello'); + return v; +end; +$$ language plpgsql; +select compos(); +ERROR: invalid input syntax for type integer: "(1,hello)" +CONTEXT: PL/pgSQL function compos() while casting return value to function's return type +-- test: invalid use of composite expression in scalar-returning function +create or replace function compos() returns int as $$ +begin + return (1, 'hello')::compostype; +end; +$$ language plpgsql; +select compos(); +ERROR: invalid input syntax for type integer: "(1,hello)" +CONTEXT: PL/pgSQL function compos() while casting return value to function's return type +drop function compos(); +drop type compostype; +-- +-- 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 +CONTEXT: PL/pgSQL function raise_test() line 5 at RAISE +-- 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 +CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE +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 +CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE +-- 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 +CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE +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 +CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE +create or replace function raise_test() returns void as $$ +begin + raise division_by_zero; +end; +$$ language plpgsql; +select raise_test(); +ERROR: division_by_zero +CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE +create or replace function raise_test() returns void as $$ +begin + raise sqlstate '1234F'; +end; +$$ language plpgsql; +select raise_test(); +ERROR: 1234F +CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE +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 +CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE +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 +CONTEXT: PL/pgSQL function raise_test() line 3 at RAISE +-- 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 3 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 3 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() line 3 at RAISE +-- test access to exception data +create function zero_divide() returns int as $$ +declare v int := 0; +begin + return 10 / v; +end; +$$ language plpgsql; +create or replace function raise_test() returns void as $$ +begin + raise exception 'custom exception' + using detail = 'some detail of custom exception', + hint = 'some hint related to custom exception'; +end; +$$ language plpgsql; +create function stacked_diagnostics_test() returns void as $$ +declare _sqlstate text; + _message text; + _context text; +begin + perform zero_divide(); +exception when others then + get stacked diagnostics + _sqlstate = returned_sqlstate, + _message = message_text, + _context = pg_exception_context; + raise notice 'sqlstate: %, message: %, context: [%]', + _sqlstate, _message, replace(_context, E'\n', ' <- '); +end; +$$ language plpgsql; +select stacked_diagnostics_test(); +NOTICE: sqlstate: 22012, message: division by zero, context: [PL/pgSQL function zero_divide() line 4 at RETURN <- SQL statement "SELECT zero_divide()" <- PL/pgSQL function stacked_diagnostics_test() line 6 at PERFORM] + stacked_diagnostics_test +-------------------------- + +(1 row) + +create or replace function stacked_diagnostics_test() returns void as $$ +declare _detail text; + _hint text; + _message text; +begin + perform raise_test(); +exception when others then + get stacked diagnostics + _message = message_text, + _detail = pg_exception_detail, + _hint = pg_exception_hint; + raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint; +end; +$$ language plpgsql; +select stacked_diagnostics_test(); +NOTICE: message: custom exception, detail: some detail of custom exception, hint: some hint related to custom exception + stacked_diagnostics_test +-------------------------- + +(1 row) + +-- fail, cannot use stacked diagnostics statement outside handler +create or replace function stacked_diagnostics_test() returns void as $$ +declare _detail text; + _hint text; + _message text; +begin + get stacked diagnostics + _message = message_text, + _detail = pg_exception_detail, + _hint = pg_exception_hint; + raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint; +end; +$$ language plpgsql; +select stacked_diagnostics_test(); +ERROR: GET STACKED DIAGNOSTICS cannot be used outside an exception handler +CONTEXT: PL/pgSQL function stacked_diagnostics_test() line 6 at GET STACKED DIAGNOSTICS +drop function zero_divide(); +drop function stacked_diagnostics_test(); +-- check cases where implicit SQLSTATE variable could be confused with +-- SQLSTATE as a keyword, cf bug #5524 +create or replace function raise_test() returns void as $$ +begin + perform 1/0; +exception + when sqlstate '22012' then + raise notice using message = sqlstate; + raise sqlstate '22012' using message = 'substitute message'; +end; +$$ language plpgsql; +select raise_test(); +NOTICE: 22012 +ERROR: substitute message +CONTEXT: PL/pgSQL function raise_test() line 7 at RAISE +drop function raise_test(); +-- test passing column_name, constraint_name, datatype_name, table_name +-- and schema_name error fields +create or replace function stacked_diagnostics_test() returns void as $$ +declare _column_name text; + _constraint_name text; + _datatype_name text; + _table_name text; + _schema_name text; +begin + raise exception using + column = '>>some column name<<', + constraint = '>>some constraint name<<', + datatype = '>>some datatype name<<', + table = '>>some table name<<', + schema = '>>some schema name<<'; +exception when others then + get stacked diagnostics + _column_name = column_name, + _constraint_name = constraint_name, + _datatype_name = pg_datatype_name, + _table_name = table_name, + _schema_name = schema_name; + raise notice 'column %, constraint %, type %, table %, schema %', + _column_name, _constraint_name, _datatype_name, _table_name, _schema_name; +end; +$$ language plpgsql; +select stacked_diagnostics_test(); +NOTICE: column >>some column name<<, constraint >>some constraint name<<, type >>some datatype name<<, table >>some table name<<, schema >>some schema name<< + stacked_diagnostics_test +-------------------------- + +(1 row) + +drop function stacked_diagnostics_test(); +-- 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); +create function rttest() +returns setof int as $$ +declare rc int; +begin + return query values(10),(20); + get diagnostics rc = row_count; + raise notice '% %', found, rc; + return query select * from (values(10),(20)) f(a) where false; + get diagnostics rc = row_count; + raise notice '% %', found, rc; + return query execute 'values(10),(20)'; + get diagnostics rc = row_count; + raise notice '% %', found, rc; + return query execute 'select * from (values(10),(20)) f(a) where false'; + get diagnostics rc = row_count; + raise notice '% %', found, rc; +end; +$$ language plpgsql; +select * from rttest(); +NOTICE: t 2 +NOTICE: f 0 +NOTICE: t 2 +NOTICE: f 0 + rttest +-------- + 10 + 20 + 10 + 20 +(4 rows) + +-- check some error cases, too +create or replace function rttest() +returns setof int as $$ +begin + return query select 10 into no_such_table; +end; +$$ language plpgsql; +select * from rttest(); +ERROR: SELECT INTO query does not return tuples +CONTEXT: SQL statement "select 10 into no_such_table" +PL/pgSQL function rttest() line 3 at RETURN QUERY +create or replace function rttest() +returns setof int as $$ +begin + return query execute 'select 10 into no_such_table'; +end; +$$ language plpgsql; +select * from rttest(); +ERROR: SELECT INTO query does not return tuples +CONTEXT: SQL statement "select 10 into no_such_table" +PL/pgSQL function rttest() line 3 at RETURN QUERY +select * from no_such_table; +ERROR: relation "no_such_table" does not exist +LINE 1: select * from no_such_table; + ^ +drop function rttest(); +-- Test for proper cleanup at subtransaction exit. This example +-- exposed a bug in PG 8.2. +CREATE FUNCTION leaker_1(fail BOOL) RETURNS INTEGER AS $$ +DECLARE + v_var INTEGER; +BEGIN + BEGIN + v_var := (leaker_2(fail)).error_code; + EXCEPTION + WHEN others THEN RETURN 0; + END; + RETURN 1; +END; +$$ LANGUAGE plpgsql; +CREATE FUNCTION leaker_2(fail BOOL, OUT error_code INTEGER, OUT new_id INTEGER) + RETURNS RECORD AS $$ +BEGIN + IF fail THEN + RAISE EXCEPTION 'fail ...'; + END IF; + error_code := 1; + new_id := 1; + RETURN; +END; +$$ LANGUAGE plpgsql; +SELECT * FROM leaker_1(false); + leaker_1 +---------- + 1 +(1 row) + +SELECT * FROM leaker_1(true); + leaker_1 +---------- + 0 +(1 row) + +DROP FUNCTION leaker_1(bool); +DROP FUNCTION leaker_2(bool); +-- Test for appropriate cleanup of non-simple expression evaluations +-- (bug in all versions prior to August 2010) +CREATE FUNCTION nonsimple_expr_test() RETURNS text[] AS $$ +DECLARE + arr text[]; + lr text; + i integer; +BEGIN + arr := array[array['foo','bar'], array['baz', 'quux']]; + lr := 'fool'; + i := 1; + -- use sub-SELECTs to make expressions non-simple + arr[(SELECT i)][(SELECT i+1)] := (SELECT lr); + RETURN arr; +END; +$$ LANGUAGE plpgsql; +SELECT nonsimple_expr_test(); + nonsimple_expr_test +------------------------- + {{foo,fool},{baz,quux}} +(1 row) + +DROP FUNCTION nonsimple_expr_test(); +CREATE FUNCTION nonsimple_expr_test() RETURNS integer AS $$ +declare + i integer NOT NULL := 0; +begin + begin + i := (SELECT NULL::integer); -- should throw error + exception + WHEN OTHERS THEN + i := (SELECT 1::integer); + end; + return i; +end; +$$ LANGUAGE plpgsql; +SELECT nonsimple_expr_test(); + nonsimple_expr_test +--------------------- + 1 +(1 row) + +DROP FUNCTION nonsimple_expr_test(); +-- +-- Test cases involving recursion and error recovery in simple expressions +-- (bugs in all versions before October 2010). The problems are most +-- easily exposed by mutual recursion between plpgsql and sql functions. +-- +create function recurse(float8) returns float8 as +$$ +begin + if ($1 > 0) then + return sql_recurse($1 - 1); + else + return $1; + end if; +end; +$$ language plpgsql; +-- "limit" is to prevent this from being inlined +create function sql_recurse(float8) returns float8 as +$$ select recurse($1) limit 1; $$ language sql; +select recurse(10); + recurse +--------- + 0 +(1 row) + +create function error1(text) returns text language sql as +$$ SELECT relname::text FROM pg_class c WHERE c.oid = $1::regclass $$; +create function error2(p_name_table text) returns text language plpgsql as $$ +begin + return error1(p_name_table); +end$$; +BEGIN; +create table public.stuffs (stuff text); +SAVEPOINT a; +select error2('nonexistent.stuffs'); +ERROR: schema "nonexistent" does not exist +CONTEXT: SQL function "error1" statement 1 +PL/pgSQL function error2(text) line 3 at RETURN +ROLLBACK TO a; +select error2('public.stuffs'); + error2 +-------- + stuffs +(1 row) + +rollback; +drop function error2(p_name_table text); +drop function error1(text); +-- Test for proper handling of cast-expression caching +create function sql_to_date(integer) returns date as $$ +select $1::text::date +$$ language sql immutable strict; +create cast (integer as date) with function sql_to_date(integer) as assignment; +create function cast_invoker(integer) returns date as $$ +begin + return $1; +end$$ language plpgsql; +select cast_invoker(20150717); + cast_invoker +-------------- + 07-17-2015 +(1 row) + +select cast_invoker(20150718); -- second call crashed in pre-release 9.5 + cast_invoker +-------------- + 07-18-2015 +(1 row) + +begin; +select cast_invoker(20150717); + cast_invoker +-------------- + 07-17-2015 +(1 row) + +select cast_invoker(20150718); + cast_invoker +-------------- + 07-18-2015 +(1 row) + +savepoint s1; +select cast_invoker(20150718); + cast_invoker +-------------- + 07-18-2015 +(1 row) + +select cast_invoker(-1); -- fails +ERROR: invalid input syntax for type date: "-1" +CONTEXT: SQL function "sql_to_date" statement 1 +PL/pgSQL function cast_invoker(integer) while casting return value to function's return type +rollback to savepoint s1; +select cast_invoker(20150719); + cast_invoker +-------------- + 07-19-2015 +(1 row) + +select cast_invoker(20150720); + cast_invoker +-------------- + 07-20-2015 +(1 row) + +commit; +drop function cast_invoker(integer); +drop function sql_to_date(integer) cascade; +NOTICE: drop cascades to cast from integer to date +-- Test handling of cast cache inside DO blocks +-- (to check the original crash case, this must be a cast not previously +-- used in this session) +begin; +do $$ declare x text[]; begin x := '{1.23, 4.56}'::numeric[]; end $$; +do $$ declare x text[]; begin x := '{1.23, 4.56}'::numeric[]; end $$; +end; +-- Test for consistent reporting of error context +create function fail() returns int language plpgsql as $$ +begin + return 1/0; +end +$$; +select fail(); +ERROR: division by zero +CONTEXT: SQL expression "1/0" +PL/pgSQL function fail() line 3 at RETURN +select fail(); +ERROR: division by zero +CONTEXT: SQL expression "1/0" +PL/pgSQL function fail() line 3 at RETURN +drop function fail(); +-- Test handling of string literals. +set standard_conforming_strings = off; +create or replace function strtest() returns text as $$ +begin + raise notice 'foo\\bar\041baz'; + return 'foo\\bar\041baz'; +end +$$ language plpgsql; +WARNING: nonstandard use of \\ in a string literal +LINE 3: raise notice 'foo\\bar\041baz'; + ^ +HINT: Use the escape string syntax for backslashes, e.g., E'\\'. +WARNING: nonstandard use of \\ in a string literal +LINE 4: return 'foo\\bar\041baz'; + ^ +HINT: Use the escape string syntax for backslashes, e.g., E'\\'. +WARNING: nonstandard use of \\ in a string literal +LINE 4: return 'foo\\bar\041baz'; + ^ +HINT: Use the escape string syntax for backslashes, e.g., E'\\'. +select strtest(); +NOTICE: foo\bar!baz +WARNING: nonstandard use of \\ in a string literal +LINE 1: 'foo\\bar\041baz' + ^ +HINT: Use the escape string syntax for backslashes, e.g., E'\\'. +QUERY: 'foo\\bar\041baz' + strtest +------------- + foo\bar!baz +(1 row) + +create or replace function strtest() returns text as $$ +begin + raise notice E'foo\\bar\041baz'; + return E'foo\\bar\041baz'; +end +$$ language plpgsql; +select strtest(); +NOTICE: foo\bar!baz + strtest +------------- + foo\bar!baz +(1 row) + +set standard_conforming_strings = on; +create or replace function strtest() returns text as $$ +begin + raise notice 'foo\\bar\041baz\'; + return 'foo\\bar\041baz\'; +end +$$ language plpgsql; +select strtest(); +NOTICE: foo\\bar\041baz\ + strtest +------------------ + foo\\bar\041baz\ +(1 row) + +create or replace function strtest() returns text as $$ +begin + raise notice E'foo\\bar\041baz'; + return E'foo\\bar\041baz'; +end +$$ language plpgsql; +select strtest(); +NOTICE: foo\bar!baz + strtest +------------- + foo\bar!baz +(1 row) + +drop function strtest(); +-- Test anonymous code blocks. +DO $$ +DECLARE r record; +BEGIN + FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno + LOOP + RAISE NOTICE '%, %', r.roomno, r.comment; + END LOOP; +END$$; +NOTICE: 001, Entrance +NOTICE: 002, Office +NOTICE: 003, Office +NOTICE: 004, Technical +NOTICE: 101, Office +NOTICE: 102, Conference +NOTICE: 103, Restroom +NOTICE: 104, Technical +NOTICE: 105, Office +NOTICE: 106, Office +-- these are to check syntax error reporting +DO LANGUAGE plpgsql $$begin return 1; end$$; +ERROR: RETURN cannot have a parameter in function returning void +LINE 1: DO LANGUAGE plpgsql $$begin return 1; end$$; + ^ +DO $$ +DECLARE r record; +BEGIN + FOR r IN SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno + LOOP + RAISE NOTICE '%, %', r.roomno, r.comment; + END LOOP; +END$$; +ERROR: column "foo" does not exist +LINE 1: SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomn... + ^ +QUERY: SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno +CONTEXT: PL/pgSQL function inline_code_block line 4 at FOR over SELECT rows +-- Check handling of errors thrown from/into anonymous code blocks. +do $outer$ +begin + for i in 1..10 loop + begin + execute $ex$ + do $$ + declare x int = 0; + begin + x := 1 / x; + end; + $$; + $ex$; + exception when division_by_zero then + raise notice 'caught division by zero'; + end; + end loop; +end; +$outer$; +NOTICE: caught division by zero +NOTICE: caught division by zero +NOTICE: caught division by zero +NOTICE: caught division by zero +NOTICE: caught division by zero +NOTICE: caught division by zero +NOTICE: caught division by zero +NOTICE: caught division by zero +NOTICE: caught division by zero +NOTICE: caught division by zero +-- Check variable scoping -- a var is not available in its own or prior +-- default expressions, but it is available in later ones. +do $$ +declare x int := x + 1; -- error +begin + raise notice 'x = %', x; +end; +$$; +ERROR: column "x" does not exist +LINE 1: x + 1 + ^ +QUERY: x + 1 +CONTEXT: PL/pgSQL function inline_code_block line 2 during statement block local variable initialization +do $$ +declare y int := x + 1; -- error + x int := 42; +begin + raise notice 'x = %, y = %', x, y; +end; +$$; +ERROR: column "x" does not exist +LINE 1: x + 1 + ^ +QUERY: x + 1 +CONTEXT: PL/pgSQL function inline_code_block line 2 during statement block local variable initialization +do $$ +declare x int := 42; + y int := x + 1; +begin + raise notice 'x = %, y = %', x, y; +end; +$$; +NOTICE: x = 42, y = 43 +do $$ +declare x int := 42; +begin + declare y int := x + 1; + x int := x + 2; + z int := x * 10; + begin + raise notice 'x = %, y = %, z = %', x, y, z; + end; +end; +$$; +NOTICE: x = 44, y = 43, z = 440 +-- Check handling of conflicts between plpgsql vars and table columns. +set plpgsql.variable_conflict = error; +create function conflict_test() returns setof int8_tbl as $$ +declare r record; + q1 bigint := 42; +begin + for r in select q1,q2 from int8_tbl loop + return next r; + end loop; +end; +$$ language plpgsql; +select * from conflict_test(); +ERROR: column reference "q1" is ambiguous +LINE 1: select q1,q2 from int8_tbl + ^ +DETAIL: It could refer to either a PL/pgSQL variable or a table column. +QUERY: select q1,q2 from int8_tbl +CONTEXT: PL/pgSQL function conflict_test() line 5 at FOR over SELECT rows +create or replace function conflict_test() returns setof int8_tbl as $$ +#variable_conflict use_variable +declare r record; + q1 bigint := 42; +begin + for r in select q1,q2 from int8_tbl loop + return next r; + end loop; +end; +$$ language plpgsql; +select * from conflict_test(); + q1 | q2 +----+------------------- + 42 | 456 + 42 | 4567890123456789 + 42 | 123 + 42 | 4567890123456789 + 42 | -4567890123456789 +(5 rows) + +create or replace function conflict_test() returns setof int8_tbl as $$ +#variable_conflict use_column +declare r record; + q1 bigint := 42; +begin + for r in select q1,q2 from int8_tbl loop + return next r; + end loop; +end; +$$ language plpgsql; +select * from conflict_test(); + q1 | q2 +------------------+------------------- + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 123 + 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 +(5 rows) + +drop function conflict_test(); +-- Check that an unreserved keyword can be used as a variable name +create function unreserved_test() returns int as $$ +declare + forward int := 21; +begin + forward := forward * 2; + return forward; +end +$$ language plpgsql; +select unreserved_test(); + unreserved_test +----------------- + 42 +(1 row) + +create or replace function unreserved_test() returns int as $$ +declare + return int := 42; +begin + return := return + 1; + return return; +end +$$ language plpgsql; +select unreserved_test(); + unreserved_test +----------------- + 43 +(1 row) + +create or replace function unreserved_test() returns int as $$ +declare + comment int := 21; +begin + comment := comment * 2; + comment on function unreserved_test() is 'this is a test'; + return comment; +end +$$ language plpgsql; +select unreserved_test(); + unreserved_test +----------------- + 42 +(1 row) + +select obj_description('unreserved_test()'::regprocedure, 'pg_proc'); + obj_description +----------------- + this is a test +(1 row) + +drop function unreserved_test(); +-- +-- Test FOREACH over arrays +-- +create function foreach_test(anyarray) +returns void as $$ +declare x int; +begin + foreach x in array $1 + loop + raise notice '%', x; + end loop; + end; +$$ language plpgsql; +select foreach_test(ARRAY[1,2,3,4]); +NOTICE: 1 +NOTICE: 2 +NOTICE: 3 +NOTICE: 4 + foreach_test +-------------- + +(1 row) + +select foreach_test(ARRAY[[1,2],[3,4]]); +NOTICE: 1 +NOTICE: 2 +NOTICE: 3 +NOTICE: 4 + foreach_test +-------------- + +(1 row) + +create or replace function foreach_test(anyarray) +returns void as $$ +declare x int; +begin + foreach x slice 1 in array $1 + loop + raise notice '%', x; + end loop; + end; +$$ language plpgsql; +-- should fail +select foreach_test(ARRAY[1,2,3,4]); +ERROR: FOREACH ... SLICE loop variable must be of an array type +CONTEXT: PL/pgSQL function foreach_test(anyarray) line 4 at FOREACH over array +select foreach_test(ARRAY[[1,2],[3,4]]); +ERROR: FOREACH ... SLICE loop variable must be of an array type +CONTEXT: PL/pgSQL function foreach_test(anyarray) line 4 at FOREACH over array +create or replace function foreach_test(anyarray) +returns void as $$ +declare x int[]; +begin + foreach x slice 1 in array $1 + loop + raise notice '%', x; + end loop; + end; +$$ language plpgsql; +select foreach_test(ARRAY[1,2,3,4]); +NOTICE: {1,2,3,4} + foreach_test +-------------- + +(1 row) + +select foreach_test(ARRAY[[1,2],[3,4]]); +NOTICE: {1,2} +NOTICE: {3,4} + foreach_test +-------------- + +(1 row) + +-- higher level of slicing +create or replace function foreach_test(anyarray) +returns void as $$ +declare x int[]; +begin + foreach x slice 2 in array $1 + loop + raise notice '%', x; + end loop; + end; +$$ language plpgsql; +-- should fail +select foreach_test(ARRAY[1,2,3,4]); +ERROR: slice dimension (2) is out of the valid range 0..1 +CONTEXT: PL/pgSQL function foreach_test(anyarray) line 4 at FOREACH over array +-- ok +select foreach_test(ARRAY[[1,2],[3,4]]); +NOTICE: {{1,2},{3,4}} + foreach_test +-------------- + +(1 row) + +select foreach_test(ARRAY[[[1,2]],[[3,4]]]); +NOTICE: {{1,2}} +NOTICE: {{3,4}} + foreach_test +-------------- + +(1 row) + +create type xy_tuple AS (x int, y int); +-- iteration over array of records +create or replace function foreach_test(anyarray) +returns void as $$ +declare r record; +begin + foreach r in array $1 + loop + raise notice '%', r; + end loop; + end; +$$ language plpgsql; +select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]); +NOTICE: (10,20) +NOTICE: (40,69) +NOTICE: (35,78) + foreach_test +-------------- + +(1 row) + +select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]); +NOTICE: (10,20) +NOTICE: (40,69) +NOTICE: (35,78) +NOTICE: (88,76) + foreach_test +-------------- + +(1 row) + +create or replace function foreach_test(anyarray) +returns void as $$ +declare x int; y int; +begin + foreach x, y in array $1 + loop + raise notice 'x = %, y = %', x, y; + end loop; + end; +$$ language plpgsql; +select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]); +NOTICE: x = 10, y = 20 +NOTICE: x = 40, y = 69 +NOTICE: x = 35, y = 78 + foreach_test +-------------- + +(1 row) + +select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]); +NOTICE: x = 10, y = 20 +NOTICE: x = 40, y = 69 +NOTICE: x = 35, y = 78 +NOTICE: x = 88, y = 76 + foreach_test +-------------- + +(1 row) + +-- slicing over array of composite types +create or replace function foreach_test(anyarray) +returns void as $$ +declare x xy_tuple[]; +begin + foreach x slice 1 in array $1 + loop + raise notice '%', x; + end loop; + end; +$$ language plpgsql; +select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]); +NOTICE: {"(10,20)","(40,69)","(35,78)"} + foreach_test +-------------- + +(1 row) + +select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]); +NOTICE: {"(10,20)","(40,69)"} +NOTICE: {"(35,78)","(88,76)"} + foreach_test +-------------- + +(1 row) + +drop function foreach_test(anyarray); +drop type xy_tuple; +-- +-- Assorted tests for array subscript assignment +-- +create temp table rtype (id int, ar text[]); +create function arrayassign1() returns text[] language plpgsql as $$ +declare + r record; +begin + r := row(12, '{foo,bar,baz}')::rtype; + r.ar[2] := 'replace'; + return r.ar; +end$$; +select arrayassign1(); + arrayassign1 +------------------- + {foo,replace,baz} +(1 row) + +select arrayassign1(); -- try again to exercise internal caching + arrayassign1 +------------------- + {foo,replace,baz} +(1 row) + +create domain orderedarray as int[2] + constraint sorted check (value[1] < value[2]); +select '{1,2}'::orderedarray; + orderedarray +-------------- + {1,2} +(1 row) + +select '{2,1}'::orderedarray; -- fail +ERROR: value for domain orderedarray violates check constraint "sorted" +create function testoa(x1 int, x2 int, x3 int) returns orderedarray +language plpgsql as $$ +declare res orderedarray; +begin + res := array[x1, x2]; + res[2] := x3; + return res; +end$$; +select testoa(1,2,3); + testoa +-------- + {1,3} +(1 row) + +select testoa(1,2,3); -- try again to exercise internal caching + testoa +-------- + {1,3} +(1 row) + +select testoa(2,1,3); -- fail at initial assign +ERROR: value for domain orderedarray violates check constraint "sorted" +CONTEXT: PL/pgSQL function testoa(integer,integer,integer) line 4 at assignment +select testoa(1,2,1); -- fail at update +ERROR: value for domain orderedarray violates check constraint "sorted" +CONTEXT: PL/pgSQL function testoa(integer,integer,integer) line 5 at assignment +drop function arrayassign1(); +drop function testoa(x1 int, x2 int, x3 int); +-- +-- Test handling of expanded arrays +-- +create function returns_rw_array(int) returns int[] +language plpgsql as $$ + declare r int[]; + begin r := array[$1, $1]; return r; end; +$$ stable; +create function consumes_rw_array(int[]) returns int +language plpgsql as $$ + begin return $1[1]; end; +$$ stable; +select consumes_rw_array(returns_rw_array(42)); + consumes_rw_array +------------------- + 42 +(1 row) + +-- bug #14174 +explain (verbose, costs off) +select i, a from + (select returns_rw_array(1) as a offset 0) ss, + lateral consumes_rw_array(a) i; + QUERY PLAN +----------------------------------------------------------------- + Nested Loop + Output: i.i, (returns_rw_array(1)) + -> Result + Output: returns_rw_array(1) + -> Function Scan on public.consumes_rw_array i + Output: i.i + Function Call: consumes_rw_array((returns_rw_array(1))) +(7 rows) + +select i, a from + (select returns_rw_array(1) as a offset 0) ss, + lateral consumes_rw_array(a) i; + i | a +---+------- + 1 | {1,1} +(1 row) + +explain (verbose, costs off) +select consumes_rw_array(a), a from returns_rw_array(1) a; + QUERY PLAN +-------------------------------------------- + Function Scan on public.returns_rw_array a + Output: consumes_rw_array(a), a + Function Call: returns_rw_array(1) +(3 rows) + +select consumes_rw_array(a), a from returns_rw_array(1) a; + consumes_rw_array | a +-------------------+------- + 1 | {1,1} +(1 row) + +explain (verbose, costs off) +select consumes_rw_array(a), a from + (values (returns_rw_array(1)), (returns_rw_array(2))) v(a); + QUERY PLAN +--------------------------------------------------------------------- + Values Scan on "*VALUES*" + Output: consumes_rw_array("*VALUES*".column1), "*VALUES*".column1 +(2 rows) + +select consumes_rw_array(a), a from + (values (returns_rw_array(1)), (returns_rw_array(2))) v(a); + consumes_rw_array | a +-------------------+------- + 1 | {1,1} + 2 | {2,2} +(2 rows) + +do $$ +declare a int[] := array[1,2]; +begin + a := a || 3; + raise notice 'a = %', a; +end$$; +NOTICE: a = {1,2,3} +-- +-- Test access to call stack +-- +create function inner_func(int) +returns int as $$ +declare _context text; +begin + get diagnostics _context = pg_context; + raise notice '***%***', _context; + -- lets do it again, just for fun.. + get diagnostics _context = pg_context; + raise notice '***%***', _context; + raise notice 'lets make sure we didnt break anything'; + return 2 * $1; +end; +$$ language plpgsql; +create or replace function outer_func(int) +returns int as $$ +declare + myresult int; +begin + raise notice 'calling down into inner_func()'; + myresult := inner_func($1); + raise notice 'inner_func() done'; + return myresult; +end; +$$ language plpgsql; +create or replace function outer_outer_func(int) +returns int as $$ +declare + myresult int; +begin + raise notice 'calling down into outer_func()'; + myresult := outer_func($1); + raise notice 'outer_func() done'; + return myresult; +end; +$$ language plpgsql; +select outer_outer_func(10); +NOTICE: calling down into outer_func() +NOTICE: calling down into inner_func() +NOTICE: ***PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS +PL/pgSQL function outer_func(integer) line 6 at assignment +PL/pgSQL function outer_outer_func(integer) line 6 at assignment*** +NOTICE: ***PL/pgSQL function inner_func(integer) line 7 at GET DIAGNOSTICS +PL/pgSQL function outer_func(integer) line 6 at assignment +PL/pgSQL function outer_outer_func(integer) line 6 at assignment*** +NOTICE: lets make sure we didnt break anything +NOTICE: inner_func() done +NOTICE: outer_func() done + outer_outer_func +------------------ + 20 +(1 row) + +-- repeated call should to work +select outer_outer_func(20); +NOTICE: calling down into outer_func() +NOTICE: calling down into inner_func() +NOTICE: ***PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS +PL/pgSQL function outer_func(integer) line 6 at assignment +PL/pgSQL function outer_outer_func(integer) line 6 at assignment*** +NOTICE: ***PL/pgSQL function inner_func(integer) line 7 at GET DIAGNOSTICS +PL/pgSQL function outer_func(integer) line 6 at assignment +PL/pgSQL function outer_outer_func(integer) line 6 at assignment*** +NOTICE: lets make sure we didnt break anything +NOTICE: inner_func() done +NOTICE: outer_func() done + outer_outer_func +------------------ + 40 +(1 row) + +drop function outer_outer_func(int); +drop function outer_func(int); +drop function inner_func(int); +-- access to call stack from exception +create function inner_func(int) +returns int as $$ +declare + _context text; + sx int := 5; +begin + begin + perform sx / 0; + exception + when division_by_zero then + get diagnostics _context = pg_context; + raise notice '***%***', _context; + end; + + -- lets do it again, just for fun.. + get diagnostics _context = pg_context; + raise notice '***%***', _context; + raise notice 'lets make sure we didnt break anything'; + return 2 * $1; +end; +$$ language plpgsql; +create or replace function outer_func(int) +returns int as $$ +declare + myresult int; +begin + raise notice 'calling down into inner_func()'; + myresult := inner_func($1); + raise notice 'inner_func() done'; + return myresult; +end; +$$ language plpgsql; +create or replace function outer_outer_func(int) +returns int as $$ +declare + myresult int; +begin + raise notice 'calling down into outer_func()'; + myresult := outer_func($1); + raise notice 'outer_func() done'; + return myresult; +end; +$$ language plpgsql; +select outer_outer_func(10); +NOTICE: calling down into outer_func() +NOTICE: calling down into inner_func() +NOTICE: ***PL/pgSQL function inner_func(integer) line 10 at GET DIAGNOSTICS +PL/pgSQL function outer_func(integer) line 6 at assignment +PL/pgSQL function outer_outer_func(integer) line 6 at assignment*** +NOTICE: ***PL/pgSQL function inner_func(integer) line 15 at GET DIAGNOSTICS +PL/pgSQL function outer_func(integer) line 6 at assignment +PL/pgSQL function outer_outer_func(integer) line 6 at assignment*** +NOTICE: lets make sure we didnt break anything +NOTICE: inner_func() done +NOTICE: outer_func() done + outer_outer_func +------------------ + 20 +(1 row) + +-- repeated call should to work +select outer_outer_func(20); +NOTICE: calling down into outer_func() +NOTICE: calling down into inner_func() +NOTICE: ***PL/pgSQL function inner_func(integer) line 10 at GET DIAGNOSTICS +PL/pgSQL function outer_func(integer) line 6 at assignment +PL/pgSQL function outer_outer_func(integer) line 6 at assignment*** +NOTICE: ***PL/pgSQL function inner_func(integer) line 15 at GET DIAGNOSTICS +PL/pgSQL function outer_func(integer) line 6 at assignment +PL/pgSQL function outer_outer_func(integer) line 6 at assignment*** +NOTICE: lets make sure we didnt break anything +NOTICE: inner_func() done +NOTICE: outer_func() done + outer_outer_func +------------------ + 40 +(1 row) + +drop function outer_outer_func(int); +drop function outer_func(int); +drop function inner_func(int); +-- +-- Test ASSERT +-- +do $$ +begin + assert 1=1; -- should succeed +end; +$$; +do $$ +begin + assert 1=0; -- should fail +end; +$$; +ERROR: assertion failed +CONTEXT: PL/pgSQL function inline_code_block line 3 at ASSERT +do $$ +begin + assert NULL; -- should fail +end; +$$; +ERROR: assertion failed +CONTEXT: PL/pgSQL function inline_code_block line 3 at ASSERT +-- check controlling GUC +set plpgsql.check_asserts = off; +do $$ +begin + assert 1=0; -- won't be tested +end; +$$; +reset plpgsql.check_asserts; +-- test custom message +do $$ +declare var text := 'some value'; +begin + assert 1=0, format('assertion failed, var = "%s"', var); +end; +$$; +ERROR: assertion failed, var = "some value" +CONTEXT: PL/pgSQL function inline_code_block line 4 at ASSERT +-- ensure assertions are not trapped by 'others' +do $$ +begin + assert 1=0, 'unhandled assertion'; +exception when others then + null; -- do nothing +end; +$$; +ERROR: unhandled assertion +CONTEXT: PL/pgSQL function inline_code_block line 3 at ASSERT +-- Test use of plpgsql in a domain check constraint (cf. bug #14414) +create function plpgsql_domain_check(val int) returns boolean as $$ +begin return val > 0; end +$$ language plpgsql immutable; +create domain plpgsql_domain as integer check(plpgsql_domain_check(value)); +do $$ +declare v_test plpgsql_domain; +begin + v_test := 1; +end; +$$; +do $$ +declare v_test plpgsql_domain := 1; +begin + v_test := 0; -- fail +end; +$$; +ERROR: value for domain plpgsql_domain violates check constraint "plpgsql_domain_check" +CONTEXT: PL/pgSQL function inline_code_block line 4 at assignment +-- Test handling of expanded array passed to a domain constraint (bug #14472) +create function plpgsql_arr_domain_check(val int[]) returns boolean as $$ +begin return val[1] > 0; end +$$ language plpgsql immutable; +create domain plpgsql_arr_domain as int[] check(plpgsql_arr_domain_check(value)); +do $$ +declare v_test plpgsql_arr_domain; +begin + v_test := array[1]; + v_test := v_test || 2; +end; +$$; +do $$ +declare v_test plpgsql_arr_domain := array[1]; +begin + v_test := 0 || v_test; -- fail +end; +$$; +ERROR: value for domain plpgsql_arr_domain violates check constraint "plpgsql_arr_domain_check" +CONTEXT: PL/pgSQL function inline_code_block line 4 at assignment +-- +-- test usage of transition tables in AFTER triggers +-- +CREATE TABLE transition_table_base (id int PRIMARY KEY, val text); +CREATE FUNCTION transition_table_base_ins_func() + RETURNS trigger + LANGUAGE plpgsql +AS $$ +DECLARE + t text; + l text; +BEGIN + t = ''; + FOR l IN EXECUTE + $q$ + EXPLAIN (TIMING off, COSTS off, VERBOSE on) + SELECT * FROM newtable + $q$ LOOP + t = t || l || E'\n'; + END LOOP; + + RAISE INFO '%', t; + RETURN new; +END; +$$; +CREATE TRIGGER transition_table_base_ins_trig + AFTER INSERT ON transition_table_base + REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable + FOR EACH STATEMENT + EXECUTE PROCEDURE transition_table_base_ins_func(); +ERROR: OLD TABLE can only be specified for a DELETE or UPDATE trigger +CREATE TRIGGER transition_table_base_ins_trig + AFTER INSERT ON transition_table_base + REFERENCING NEW TABLE AS newtable + FOR EACH STATEMENT + EXECUTE PROCEDURE transition_table_base_ins_func(); +INSERT INTO transition_table_base VALUES (1, 'One'), (2, 'Two'); +INFO: Named Tuplestore Scan + Output: id, val + +INSERT INTO transition_table_base VALUES (3, 'Three'), (4, 'Four'); +INFO: Named Tuplestore Scan + Output: id, val + +CREATE OR REPLACE FUNCTION transition_table_base_upd_func() + RETURNS trigger + LANGUAGE plpgsql +AS $$ +DECLARE + t text; + l text; +BEGIN + t = ''; + FOR l IN EXECUTE + $q$ + EXPLAIN (TIMING off, COSTS off, VERBOSE on) + SELECT * FROM oldtable ot FULL JOIN newtable nt USING (id) + $q$ LOOP + t = t || l || E'\n'; + END LOOP; + + RAISE INFO '%', t; + RETURN new; +END; +$$; +CREATE TRIGGER transition_table_base_upd_trig + AFTER UPDATE ON transition_table_base + REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable + FOR EACH STATEMENT + EXECUTE PROCEDURE transition_table_base_upd_func(); +UPDATE transition_table_base + SET val = '*' || val || '*' + WHERE id BETWEEN 2 AND 3; +INFO: Hash Full Join + Output: COALESCE(ot.id, nt.id), ot.val, nt.val + Hash Cond: (ot.id = nt.id) + -> Named Tuplestore Scan + Output: ot.id, ot.val + -> Hash + Output: nt.id, nt.val + -> Named Tuplestore Scan + Output: nt.id, nt.val + +CREATE TABLE transition_table_level1 +( + level1_no serial NOT NULL , + level1_node_name varchar(255), + PRIMARY KEY (level1_no) +) WITHOUT OIDS; +CREATE TABLE transition_table_level2 +( + level2_no serial NOT NULL , + parent_no int NOT NULL, + level1_node_name varchar(255), + PRIMARY KEY (level2_no) +) WITHOUT OIDS; +CREATE TABLE transition_table_status +( + level int NOT NULL, + node_no int NOT NULL, + status int, + PRIMARY KEY (level, node_no) +) WITHOUT OIDS; +CREATE FUNCTION transition_table_level1_ri_parent_del_func() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ + DECLARE n bigint; + BEGIN + PERFORM FROM p JOIN transition_table_level2 c ON c.parent_no = p.level1_no; + IF FOUND THEN + RAISE EXCEPTION 'RI error'; + END IF; + RETURN NULL; + END; +$$; +CREATE TRIGGER transition_table_level1_ri_parent_del_trigger + AFTER DELETE ON transition_table_level1 + REFERENCING OLD TABLE AS p + FOR EACH STATEMENT EXECUTE PROCEDURE + transition_table_level1_ri_parent_del_func(); +CREATE FUNCTION transition_table_level1_ri_parent_upd_func() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ + DECLARE + x int; + BEGIN + WITH p AS (SELECT level1_no, sum(delta) cnt + FROM (SELECT level1_no, 1 AS delta FROM i + UNION ALL + SELECT level1_no, -1 AS delta FROM d) w + GROUP BY level1_no + HAVING sum(delta) < 0) + SELECT level1_no + FROM p JOIN transition_table_level2 c ON c.parent_no = p.level1_no + INTO x; + IF FOUND THEN + RAISE EXCEPTION 'RI error'; + END IF; + RETURN NULL; + END; +$$; +CREATE TRIGGER transition_table_level1_ri_parent_upd_trigger + AFTER UPDATE ON transition_table_level1 + REFERENCING OLD TABLE AS d NEW TABLE AS i + FOR EACH STATEMENT EXECUTE PROCEDURE + transition_table_level1_ri_parent_upd_func(); +CREATE FUNCTION transition_table_level2_ri_child_insupd_func() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ + BEGIN + PERFORM FROM i + LEFT JOIN transition_table_level1 p + ON p.level1_no IS NOT NULL AND p.level1_no = i.parent_no + WHERE p.level1_no IS NULL; + IF FOUND THEN + RAISE EXCEPTION 'RI error'; + END IF; + RETURN NULL; + END; +$$; +CREATE TRIGGER transition_table_level2_ri_child_ins_trigger + AFTER INSERT ON transition_table_level2 + REFERENCING NEW TABLE AS i + FOR EACH STATEMENT EXECUTE PROCEDURE + transition_table_level2_ri_child_insupd_func(); +CREATE TRIGGER transition_table_level2_ri_child_upd_trigger + AFTER UPDATE ON transition_table_level2 + REFERENCING NEW TABLE AS i + FOR EACH STATEMENT EXECUTE PROCEDURE + transition_table_level2_ri_child_insupd_func(); +-- create initial test data +INSERT INTO transition_table_level1 (level1_no) + SELECT generate_series(1,200); +ANALYZE transition_table_level1; +INSERT INTO transition_table_level2 (level2_no, parent_no) + SELECT level2_no, level2_no / 50 + 1 AS parent_no + FROM generate_series(1,9999) level2_no; +ANALYZE transition_table_level2; +INSERT INTO transition_table_status (level, node_no, status) + SELECT 1, level1_no, 0 FROM transition_table_level1; +INSERT INTO transition_table_status (level, node_no, status) + SELECT 2, level2_no, 0 FROM transition_table_level2; +ANALYZE transition_table_status; +INSERT INTO transition_table_level1(level1_no) + SELECT generate_series(201,1000); +ANALYZE transition_table_level1; +-- behave reasonably if someone tries to modify a transition table +CREATE FUNCTION transition_table_level2_bad_usage_func() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ + BEGIN + INSERT INTO dx VALUES (1000000, 1000000, 'x'); + RETURN NULL; + END; +$$; +CREATE TRIGGER transition_table_level2_bad_usage_trigger + AFTER DELETE ON transition_table_level2 + REFERENCING OLD TABLE AS dx + FOR EACH STATEMENT EXECUTE PROCEDURE + transition_table_level2_bad_usage_func(); +DELETE FROM transition_table_level2 + WHERE level2_no BETWEEN 301 AND 305; +ERROR: relation "dx" cannot be the target of a modifying statement +CONTEXT: SQL statement "INSERT INTO dx VALUES (1000000, 1000000, 'x')" +PL/pgSQL function transition_table_level2_bad_usage_func() line 3 at SQL statement +DROP TRIGGER transition_table_level2_bad_usage_trigger + ON transition_table_level2; +-- attempt modifications which would break RI (should all fail) +DELETE FROM transition_table_level1 + WHERE level1_no = 25; +ERROR: RI error +CONTEXT: PL/pgSQL function transition_table_level1_ri_parent_del_func() line 6 at RAISE +UPDATE transition_table_level1 SET level1_no = -1 + WHERE level1_no = 30; +ERROR: RI error +CONTEXT: PL/pgSQL function transition_table_level1_ri_parent_upd_func() line 15 at RAISE +INSERT INTO transition_table_level2 (level2_no, parent_no) + VALUES (10000, 10000); +ERROR: RI error +CONTEXT: PL/pgSQL function transition_table_level2_ri_child_insupd_func() line 8 at RAISE +UPDATE transition_table_level2 SET parent_no = 2000 + WHERE level2_no = 40; +ERROR: RI error +CONTEXT: PL/pgSQL function transition_table_level2_ri_child_insupd_func() line 8 at RAISE +-- attempt modifications which would not break RI (should all succeed) +DELETE FROM transition_table_level1 + WHERE level1_no BETWEEN 201 AND 1000; +DELETE FROM transition_table_level1 + WHERE level1_no BETWEEN 100000000 AND 100000010; +SELECT count(*) FROM transition_table_level1; + count +------- + 200 +(1 row) + +DELETE FROM transition_table_level2 + WHERE level2_no BETWEEN 211 AND 220; +SELECT count(*) FROM transition_table_level2; + count +------- + 9989 +(1 row) + +CREATE TABLE alter_table_under_transition_tables +( + id int PRIMARY KEY, + name text +); +CREATE FUNCTION alter_table_under_transition_tables_upd_func() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ +BEGIN + RAISE WARNING 'old table = %, new table = %', + (SELECT string_agg(id || '=' || name, ',') FROM d), + (SELECT string_agg(id || '=' || name, ',') FROM i); + RAISE NOTICE 'one = %', (SELECT 1 FROM alter_table_under_transition_tables LIMIT 1); + RETURN NULL; +END; +$$; +-- should fail, TRUNCATE is not compatible with transition tables +CREATE TRIGGER alter_table_under_transition_tables_upd_trigger + AFTER TRUNCATE OR UPDATE ON alter_table_under_transition_tables + REFERENCING OLD TABLE AS d NEW TABLE AS i + FOR EACH STATEMENT EXECUTE PROCEDURE + alter_table_under_transition_tables_upd_func(); +ERROR: TRUNCATE triggers with transition tables are not supported +-- should work +CREATE TRIGGER alter_table_under_transition_tables_upd_trigger + AFTER UPDATE ON alter_table_under_transition_tables + REFERENCING OLD TABLE AS d NEW TABLE AS i + FOR EACH STATEMENT EXECUTE PROCEDURE + alter_table_under_transition_tables_upd_func(); +INSERT INTO alter_table_under_transition_tables + VALUES (1, '1'), (2, '2'), (3, '3'); +UPDATE alter_table_under_transition_tables + SET name = name || name; +WARNING: old table = 1=1,2=2,3=3, new table = 1=11,2=22,3=33 +NOTICE: one = 1 +-- now change 'name' to an integer to see what happens... +ALTER TABLE alter_table_under_transition_tables + ALTER COLUMN name TYPE int USING name::integer; +UPDATE alter_table_under_transition_tables + SET name = (name::text || name::text)::integer; +WARNING: old table = 1=11,2=22,3=33, new table = 1=1111,2=2222,3=3333 +NOTICE: one = 1 +-- now drop column 'name' +ALTER TABLE alter_table_under_transition_tables + DROP column name; +UPDATE alter_table_under_transition_tables + SET id = id; +ERROR: column "name" does not exist +LINE 1: (SELECT string_agg(id || '=' || name, ',') FROM d) + ^ +QUERY: (SELECT string_agg(id || '=' || name, ',') FROM d) +CONTEXT: PL/pgSQL function alter_table_under_transition_tables_upd_func() line 3 at RAISE +-- +-- Test multiple reference to a transition table +-- +CREATE TABLE multi_test (i int); +INSERT INTO multi_test VALUES (1); +CREATE OR REPLACE FUNCTION multi_test_trig() RETURNS trigger +LANGUAGE plpgsql AS $$ +BEGIN + RAISE NOTICE 'count = %', (SELECT COUNT(*) FROM new_test); + RAISE NOTICE 'count union = %', + (SELECT COUNT(*) + FROM (SELECT * FROM new_test UNION ALL SELECT * FROM new_test) ss); + RETURN NULL; +END$$; +CREATE TRIGGER my_trigger AFTER UPDATE ON multi_test + REFERENCING NEW TABLE AS new_test OLD TABLE as old_test + FOR EACH STATEMENT EXECUTE PROCEDURE multi_test_trig(); +UPDATE multi_test SET i = i; +NOTICE: count = 1 +NOTICE: count union = 2 +DROP TABLE multi_test; +DROP FUNCTION multi_test_trig(); +-- +-- Check type parsing and record fetching from partitioned tables +-- +CREATE TABLE partitioned_table (a int, b text) PARTITION BY LIST (a); +CREATE TABLE pt_part1 PARTITION OF partitioned_table FOR VALUES IN (1); +CREATE TABLE pt_part2 PARTITION OF partitioned_table FOR VALUES IN (2); +INSERT INTO partitioned_table VALUES (1, 'Row 1'); +INSERT INTO partitioned_table VALUES (2, 'Row 2'); +CREATE OR REPLACE FUNCTION get_from_partitioned_table(partitioned_table.a%type) +RETURNS partitioned_table AS $$ +DECLARE + a_val partitioned_table.a%TYPE; + result partitioned_table%ROWTYPE; +BEGIN + a_val := $1; + SELECT * INTO result FROM partitioned_table WHERE a = a_val; + RETURN result; +END; $$ LANGUAGE plpgsql; +NOTICE: type reference partitioned_table.a%TYPE converted to integer +SELECT * FROM get_from_partitioned_table(1) AS t; + a | b +---+------- + 1 | Row 1 +(1 row) + +CREATE OR REPLACE FUNCTION list_partitioned_table() +RETURNS SETOF partitioned_table.a%TYPE AS $$ +DECLARE + row partitioned_table%ROWTYPE; + a_val partitioned_table.a%TYPE; +BEGIN + FOR row IN SELECT * FROM partitioned_table ORDER BY a LOOP + a_val := row.a; + RETURN NEXT a_val; + END LOOP; + RETURN; +END; $$ LANGUAGE plpgsql; +NOTICE: type reference partitioned_table.a%TYPE converted to integer +SELECT * FROM list_partitioned_table() AS t; + t +--- + 1 + 2 +(2 rows) + +-- +-- Check argument name is used instead of $n in error message +-- +CREATE FUNCTION fx(x WSlot) RETURNS void AS $$ +BEGIN + GET DIAGNOSTICS x = ROW_COUNT; + RETURN; +END; $$ LANGUAGE plpgsql; +ERROR: "x" is not a scalar variable +LINE 3: GET DIAGNOSTICS x = ROW_COUNT; + ^ |