-- ************************************************************ -- * Describe the backside of a patchfield slot -- ************************************************************ create function pslot_backlink_view(bpchar) returns text as ' <> declare rec record; bltype char(2); retval text; begin select into rec * from PSlot where slotname = $1; if not found then return ''''; end if; if rec.backlink = '''' then return ''-''; end if; bltype := substr(rec.backlink, 1, 2); if bltype = ''PL'' then declare rec record; begin select into rec * from PLine where slotname = outer.rec.backlink; retval := ''Phone line '' || trim(rec.phonenumber); if rec.comment != '''' then retval := retval || '' (''; retval := retval || rec.comment; retval := retval || '')''; end if; return retval; end; end if; if bltype = ''WS'' then select into rec * from WSlot where slotname = rec.backlink; retval := trim(rec.slotname) || '' in room ''; retval := retval || trim(rec.roomno); retval := retval || '' -> ''; return retval || wslot_slotlink_view(rec.slotname); end if; return rec.backlink; end; ' language 'plpgsql'; -- ************************************************************ -- * Describe the front of a patchfield slot -- ************************************************************ create function pslot_slotlink_view(bpchar) returns text as ' declare psrec record; sltype char(2); retval text; begin select into psrec * from PSlot where slotname = $1; if not found then return ''''; end if; if psrec.slotlink = '''' then return ''-''; end if; sltype := substr(psrec.slotlink, 1, 2); if sltype = ''PS'' then retval := trim(psrec.slotlink) || '' -> ''; return retval || pslot_backlink_view(psrec.slotlink); end if; if sltype = ''HS'' then retval := comment from Hub H, HSlot HS where HS.slotname = psrec.slotlink and H.name = HS.hubname; retval := retval || '' slot ''; retval := retval || slotno::text from HSlot where slotname = psrec.slotlink; return retval; end if; return psrec.slotlink; end; ' language 'plpgsql'; -- ************************************************************ -- * Describe the front of a wall connector slot -- ************************************************************ create function wslot_slotlink_view(bpchar) returns text as ' declare rec record; sltype char(2); retval text; begin select into rec * from WSlot where slotname = $1; if not found then return ''''; end if; if rec.slotlink = '''' then return ''-''; end if; sltype := substr(rec.slotlink, 1, 2); if sltype = ''PH'' then select into rec * from PHone where slotname = rec.slotlink; retval := ''Phone '' || trim(rec.slotname); if rec.comment != '''' then retval := retval || '' (''; retval := retval || rec.comment; retval := retval || '')''; end if; return retval; end if; if sltype = ''IF'' then declare syrow System%RowType; ifrow IFace%ROWTYPE; begin select into ifrow * from IFace where slotname = rec.slotlink; select into syrow * from System where name = ifrow.sysname; retval := syrow.name || '' IF ''; retval := retval || ifrow.ifname; if syrow.comment != '''' then retval := retval || '' (''; retval := retval || syrow.comment; retval := retval || '')''; end if; return retval; end; end if; return rec.slotlink; end; ' language 'plpgsql'; -- ************************************************************ -- * View of a patchfield describing backside and patches -- ************************************************************ create view Pfield_v1 as select PF.pfname, PF.slotname, pslot_backlink_view(PF.slotname) as backside, pslot_slotlink_view(PF.slotname) as patch from PSlot PF;