| Quellcodebibliothek Statistik Leitseite products/Sources/formale Sprachen/C/Postgres/src/test/regress/expected/ (Postgres Database Version 18.4©) Datei vom 11.4.2026 mit Größe 174 kB |
|
Impressum plpgsql.out Sprache: unbekannt |
|
|
Spracherkennung für: .out vermutete Sprache: Unknown {[0] [0] [0]} [Methode: Schwerpunktbildung, einfache Gewichte, sechs Dimensionen] --
-- 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 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 -> Pho 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 li 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 -> Pho 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 -> Pho 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 PF0_2 | PS.base.ta2 | Phone line -501 (Fax entrance) | PS.base.a3 -> WS.001.2a in room 001 -> Phone 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 (Hico 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 (Hico 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 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 anycom 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::r 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 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 anycompatible create function f1(x anycompatiblerange, y anycompatiblearray) returns anycompatibleran 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 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 anycompatiblear 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: PL/pgSQL 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 -- (this used to require a special hack in the code; it no longer does, -- but let's keep the test anyway) 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 -- use both supported syntaxes for named arguments 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 ha 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 merce 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 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 fipshash(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 ----------------------------------+----+--- e91592205d3881e3ea35d66973bb4898 | -8 | f 03b26944890929ff751653acb2f2af79 | -6 | f e5e0093f285a4fb94c3fcc2ad7fd04ed | -4 | f cf3bae39dd692048a8bf961182e6a34d | -2 | f 5feceb66ffc86f38d952786c6d696c79 | 0 | f d4735e3a265e16eee03f59718b9b5d03 | 2 | t 4b227777d4dd1fc61c6f884f48641d02 | 4 | t e7f6c011776e8db7cd330b54174fd76f | 6 | t 2c624232cdd221771294dfbb310aca00 | 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 -- assign portal names to cursors to get stable output c := 'c'; c2 := 'c2'; for r in c(5,7) loop raise notice '% from %', r.i, c; end loop; -- again, to test if cursor was closed properly -- (and while we're at it, test named-parameter notation) for r in c(r2 := 10, r1 => 9) 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(); -- it's okay to re-use a cursor variable name, even when bound do $$ declare cnt int := 0; c1 cursor for select * from forc_test; begin for r1 in c1 loop declare c1 cursor for select * from forc_test; begin for r2 in c1 loop cnt := cnt + 1; end loop; end; end loop; raise notice 'cnt = %', cnt; end $$; NOTICE: cnt = 100 -- 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 "y" (po 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 parallel safe; 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_divid 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 r 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 stacked_diagnostics_test(); -- Test that an error recovery subtransaction is parallel safe create function error_trap_test() returns text as $$ begin perform zero_divide(); return 'no error detected!'; exception when division_by_zero then return 'division_by_zero detected'; end; $$ language plpgsql parallel safe; set debug_parallel_query to on; explain (verbose, costs off) select error_trap_test(); QUERY PLAN ----------------------------------- Gather Output: (error_trap_test()) Workers Planned: 1 Single Copy: true -> Result Output: error_trap_test() (6 rows) select error_trap_test(); error_trap_test --------------------------- division_by_zero detected (1 row) reset debug_parallel_query; drop function error_trap_test(); drop function zero_divide(); -- 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<<, ta 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 ty 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: PL/pgSQL expression "1/0" PL/pgSQL function fail() line 3 at RETURN select fail(); ERROR: division by zero CONTEXT: PL/pgSQL 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 i 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 i 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 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 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 pg_routine_oid create function current_function(text) returns regprocedure as $$ declare fn_oid regprocedure; begin get diagnostics fn_oid = pg_routine_oid; return fn_oid; end; $$ language plpgsql; select current_function('foo'); current_function ------------------------ current_function(text) (1 row) drop function current_function(text); -- shouldn't fail in DO, even though there's no useful data do $$ declare fn_oid oid; begin get diagnostics fn_oid = pg_routine_oid; raise notice 'pg_routine_oid = %', fn_oid; end; $$; NOTICE: pg_routine_oid = 0 -- -- 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 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 RAIS 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 RAI 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 RAI -- 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 RAI -- -- 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 public.partitioned_table.a%TYPE AS $$ DECLARE row public.partitioned_table%ROWTYPE; a_val public.partitioned_table.a%TYPE; BEGIN FOR row IN SELECT * FROM public.partitioned_table ORDER BY a LOOP a_val := row.a; RETURN NEXT a_val; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; NOTICE: type reference public.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; ^ [Verzeichnis aufwärts0.220unsichere VerbindungÜbersetzung europäischer Sprachen durch Browser2026-08-08] |
2026-08-25