plpgsql.out

postgresql8.3.4源码,开源数据库

	return retval || wslot_slotlink_view(rec.slotname);
    end if;
    return rec.backlink;
end;
' language plpgsql;
-- ************************************************************
-- * Describe the front of a patchfield slot
-- ************************************************************
create function pslot_slotlink_view(bpchar)
returns text as '
declare
    psrec	record;
    sltype	char(2);
    retval	text;
begin
    select into psrec * from PSlot where slotname = $1;
    if not found then
        return '''';
    end if;
    if psrec.slotlink = '''' then
        return ''-'';
    end if;
    sltype := substr(psrec.slotlink, 1, 2);
    if sltype = ''PS'' then
	retval := trim(psrec.slotlink) || '' -> '';
	return retval || pslot_backlink_view(psrec.slotlink);
    end if;
    if sltype = ''HS'' then
        retval := comment from Hub H, HSlot HS
			where HS.slotname = psrec.slotlink
			  and H.name = HS.hubname;
        retval := retval || '' slot '';
	retval := retval || slotno::text from HSlot
			where slotname = psrec.slotlink;
	return retval;
    end if;
    return psrec.slotlink;
end;
' language plpgsql;
-- ************************************************************
-- * Describe the front of a wall connector slot
-- ************************************************************
create function wslot_slotlink_view(bpchar)
returns text as '
declare
    rec		record;
    sltype	char(2);
    retval	text;
begin
    select into rec * from WSlot where slotname = $1;
    if not found then
        return '''';
    end if;
    if rec.slotlink = '''' then
        return ''-'';
    end if;
    sltype := substr(rec.slotlink, 1, 2);
    if sltype = ''PH'' then
        select into rec * from PHone where slotname = rec.slotlink;
	retval := ''Phone '' || trim(rec.slotname);
	if rec.comment != '''' then
	    retval := retval || '' ('';
	    retval := retval || rec.comment;
	    retval := retval || '')'';
	end if;
	return retval;
    end if;
    if sltype = ''IF'' then
	declare
	    syrow	System%RowType;
	    ifrow	IFace%ROWTYPE;
        begin
	    select into ifrow * from IFace where slotname = rec.slotlink;
	    select into syrow * from System where name = ifrow.sysname;
	    retval := syrow.name || '' IF '';
	    retval := retval || ifrow.ifname;
	    if syrow.comment != '''' then
	        retval := retval || '' ('';
		retval := retval || syrow.comment;
		retval := retval || '')'';
	    end if;
	    return retval;
	end;
    end if;
    return rec.slotlink;
end;
' language plpgsql;
-- ************************************************************
-- * View of a patchfield describing backside and patches
-- ************************************************************
create view Pfield_v1 as select PF.pfname, PF.slotname,
	pslot_backlink_view(PF.slotname) as backside,
	pslot_slotlink_view(PF.slotname) as patch
    from PSlot PF;
--
-- First we build the house - so we create the rooms
--
insert into Room values ('001', 'Entrance');
insert into Room values ('002', 'Office');
insert into Room values ('003', 'Office');
insert into Room values ('004', 'Technical');
insert into Room values ('101', 'Office');
insert into Room values ('102', 'Conference');
insert into Room values ('103', 'Restroom');
insert into Room values ('104', 'Technical');
insert into Room values ('105', 'Office');
insert into Room values ('106', 'Office');
--
-- Second we install the wall connectors
--
insert into WSlot values ('WS.001.1a', '001', '', '');
insert into WSlot values ('WS.001.1b', '001', '', '');
insert into WSlot values ('WS.001.2a', '001', '', '');
insert into WSlot values ('WS.001.2b', '001', '', '');
insert into WSlot values ('WS.001.3a', '001', '', '');
insert into WSlot values ('WS.001.3b', '001', '', '');
insert into WSlot values ('WS.002.1a', '002', '', '');
insert into WSlot values ('WS.002.1b', '002', '', '');
insert into WSlot values ('WS.002.2a', '002', '', '');
insert into WSlot values ('WS.002.2b', '002', '', '');
insert into WSlot values ('WS.002.3a', '002', '', '');
insert into WSlot values ('WS.002.3b', '002', '', '');
insert into WSlot values ('WS.003.1a', '003', '', '');
insert into WSlot values ('WS.003.1b', '003', '', '');
insert into WSlot values ('WS.003.2a', '003', '', '');
insert into WSlot values ('WS.003.2b', '003', '', '');
insert into WSlot values ('WS.003.3a', '003', '', '');
insert into WSlot values ('WS.003.3b', '003', '', '');
insert into WSlot values ('WS.101.1a', '101', '', '');
insert into WSlot values ('WS.101.1b', '101', '', '');
insert into WSlot values ('WS.101.2a', '101', '', '');
insert into WSlot values ('WS.101.2b', '101', '', '');
insert into WSlot values ('WS.101.3a', '101', '', '');
insert into WSlot values ('WS.101.3b', '101', '', '');
insert into WSlot values ('WS.102.1a', '102', '', '');
insert into WSlot values ('WS.102.1b', '102', '', '');
insert into WSlot values ('WS.102.2a', '102', '', '');
insert into WSlot values ('WS.102.2b', '102', '', '');
insert into WSlot values ('WS.102.3a', '102', '', '');
insert into WSlot values ('WS.102.3b', '102', '', '');
insert into WSlot values ('WS.105.1a', '105', '', '');
insert into WSlot values ('WS.105.1b', '105', '', '');
insert into WSlot values ('WS.105.2a', '105', '', '');
insert into WSlot values ('WS.105.2b', '105', '', '');
insert into WSlot values ('WS.105.3a', '105', '', '');
insert into WSlot values ('WS.105.3b', '105', '', '');
insert into WSlot values ('WS.106.1a', '106', '', '');
insert into WSlot values ('WS.106.1b', '106', '', '');
insert into WSlot values ('WS.106.2a', '106', '', '');
insert into WSlot values ('WS.106.2b', '106', '', '');
insert into WSlot values ('WS.106.3a', '106', '', '');
insert into WSlot values ('WS.106.3b', '106', '', '');
--
-- Now create the patch fields and their slots
--
insert into PField values ('PF0_1', 'Wallslots basement');
--
-- The cables for these will be made later, so they are unconnected for now
--
insert into PSlot values ('PS.base.a1', 'PF0_1', '', '');
insert into PSlot values ('PS.base.a2', 'PF0_1', '', '');
insert into PSlot values ('PS.base.a3', 'PF0_1', '', '');
insert into PSlot values ('PS.base.a4', 'PF0_1', '', '');
insert into PSlot values ('PS.base.a5', 'PF0_1', '', '');
insert into PSlot values ('PS.base.a6', 'PF0_1', '', '');
--
-- These are already wired to the wall connectors
--
insert into PSlot values ('PS.base.b1', 'PF0_1', '', 'WS.002.1a');
insert into PSlot values ('PS.base.b2', 'PF0_1', '', 'WS.002.1b');
insert into PSlot values ('PS.base.b3', 'PF0_1', '', 'WS.002.2a');
insert into PSlot values ('PS.base.b4', 'PF0_1', '', 'WS.002.2b');
insert into PSlot values ('PS.base.b5', 'PF0_1', '', 'WS.002.3a');
insert into PSlot values ('PS.base.b6', 'PF0_1', '', 'WS.002.3b');
insert into PSlot values ('PS.base.c1', 'PF0_1', '', 'WS.003.1a');
insert into PSlot values ('PS.base.c2', 'PF0_1', '', 'WS.003.1b');
insert into PSlot values ('PS.base.c3', 'PF0_1', '', 'WS.003.2a');
insert into PSlot values ('PS.base.c4', 'PF0_1', '', 'WS.003.2b');
insert into PSlot values ('PS.base.c5', 'PF0_1', '', 'WS.003.3a');
insert into PSlot values ('PS.base.c6', 'PF0_1', '', 'WS.003.3b');
--
-- This patchfield will be renamed later into PF0_2 - so its
-- slots references in pfname should follow
--
insert into PField values ('PF0_X', 'Phonelines basement');
insert into PSlot values ('PS.base.ta1', 'PF0_X', '', '');
insert into PSlot values ('PS.base.ta2', 'PF0_X', '', '');
insert into PSlot values ('PS.base.ta3', 'PF0_X', '', '');
insert into PSlot values ('PS.base.ta4', 'PF0_X', '', '');
insert into PSlot values ('PS.base.ta5', 'PF0_X', '', '');
insert into PSlot values ('PS.base.ta6', 'PF0_X', '', '');
insert into PSlot values ('PS.base.tb1', 'PF0_X', '', '');
insert into PSlot values ('PS.base.tb2', 'PF0_X', '', '');
insert into PSlot values ('PS.base.tb3', 'PF0_X', '', '');
insert into PSlot values ('PS.base.tb4', 'PF0_X', '', '');
insert into PSlot values ('PS.base.tb5', 'PF0_X', '', '');
insert into PSlot values ('PS.base.tb6', 'PF0_X', '', '');
insert into PField values ('PF1_1', 'Wallslots 1st floor');
insert into PSlot values ('PS.1st.a1', 'PF1_1', '', 'WS.101.1a');
insert into PSlot values ('PS.1st.a2', 'PF1_1', '', 'WS.101.1b');
insert into PSlot values ('PS.1st.a3', 'PF1_1', '', 'WS.101.2a');
insert into PSlot values ('PS.1st.a4', 'PF1_1', '', 'WS.101.2b');
insert into PSlot values ('PS.1st.a5', 'PF1_1', '', 'WS.101.3a');
insert into PSlot values ('PS.1st.a6', 'PF1_1', '', 'WS.101.3b');
insert into PSlot values ('PS.1st.b1', 'PF1_1', '', 'WS.102.1a');
insert into PSlot values ('PS.1st.b2', 'PF1_1', '', 'WS.102.1b');
insert into PSlot values ('PS.1st.b3', 'PF1_1', '', 'WS.102.2a');
insert into PSlot values ('PS.1st.b4', 'PF1_1', '', 'WS.102.2b');
insert into PSlot values ('PS.1st.b5', 'PF1_1', '', 'WS.102.3a');
insert into PSlot values ('PS.1st.b6', 'PF1_1', '', 'WS.102.3b');
insert into PSlot values ('PS.1st.c1', 'PF1_1', '', 'WS.105.1a');
insert into PSlot values ('PS.1st.c2', 'PF1_1', '', 'WS.105.1b');
insert into PSlot values ('PS.1st.c3', 'PF1_1', '', 'WS.105.2a');
insert into PSlot values ('PS.1st.c4', 'PF1_1', '', 'WS.105.2b');
insert into PSlot values ('PS.1st.c5', 'PF1_1', '', 'WS.105.3a');
insert into PSlot values ('PS.1st.c6', 'PF1_1', '', 'WS.105.3b');
insert into PSlot values ('PS.1st.d1', 'PF1_1', '', 'WS.106.1a');
insert into PSlot values ('PS.1st.d2', 'PF1_1', '', 'WS.106.1b');
insert into PSlot values ('PS.1st.d3', 'PF1_1', '', 'WS.106.2a');
insert into PSlot values ('PS.1st.d4', 'PF1_1', '', 'WS.106.2b');
insert into PSlot values ('PS.1st.d5', 'PF1_1', '', 'WS.106.3a');
insert into PSlot values ('PS.1st.d6', 'PF1_1', '', 'WS.106.3b');
--
-- Now we wire the wall connectors 1a-2a in room 001 to the
-- patchfield. In the second update we make an error, and
-- correct it after
--
update PSlot set backlink = 'WS.001.1a' where slotname = 'PS.base.a1';
update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a3';
select * from WSlot where roomno = '001' order by slotname;
       slotname       |  roomno  |       slotlink       |       backlink       
----------------------+----------+----------------------+----------------------
 WS.001.1a            | 001      |                      | PS.base.a1          
 WS.001.1b            | 001      |                      | PS.base.a3          
 WS.001.2a            | 001      |                      |                     
 WS.001.2b            | 001      |                      |                     
 WS.001.3a            | 001      |                      |                     
 WS.001.3b            | 001      |                      |                     
(6 rows)

select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
       slotname       | pfname |       slotlink       |       backlink       
----------------------+--------+----------------------+----------------------
 PS.base.a1           | PF0_1  |                      | WS.001.1a           
 PS.base.a2           | PF0_1  |                      |                     
 PS.base.a3           | PF0_1  |                      | WS.001.1b           
 PS.base.a4           | PF0_1  |                      |                     
 PS.base.a5           | PF0_1  |                      |                     
 PS.base.a6           | PF0_1  |                      |                     
(6 rows)

update PSlot set backlink = 'WS.001.2a' where slotname = 'PS.base.a3';
select * from WSlot where roomno = '001' order by slotname;
       slotname       |  roomno  |       slotlink       |       backlink       
----------------------+----------+----------------------+----------------------
 WS.001.1a            | 001      |                      | PS.base.a1          
 WS.001.1b            | 001      |                      |                     
 WS.001.2a            | 001      |                      | PS.base.a3          
 WS.001.2b            | 001      |                      |                     
 WS.001.3a            | 001      |                      |                     
 WS.001.3b            | 001      |                      |                     
(6 rows)

select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
       slotname       | pfname |       slotlink       |       backlink       
----------------------+--------+----------------------+----------------------
 PS.base.a1           | PF0_1  |                      | WS.001.1a           
 PS.base.a2           | PF0_1  |                      |                     
 PS.base.a3           | PF0_1  |                      | WS.001.2a           
 PS.base.a4           | PF0_1  |                      |                     
 PS.base.a5           | PF0_1  |                      |                     
 PS.base.a6           | PF0_1  |                      |                     
(6 rows)

update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a2';
select * from WSlot where roomno = '001' order by slotname;
       slotname       |  roomno  |       slotlink       |       backlink       
----------------------+----------+----------------------+----------------------
 WS.001.1a            | 001      |                      | PS.base.a1          
 WS.001.1b            | 001      |                      | PS.base.a2          
 WS.001.2a            | 001      |                      | PS.base.a3          
 WS.001.2b            | 001      |                      |                     
 WS.001.3a            | 001      |                      |                     
 WS.001.3b            | 001      |                      |                     
(6 rows)

select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
       slotname       | pfname |       slotlink       |       backlink       
----------------------+--------+----------------------+----------------------
 PS.base.a1           | PF0_1  |                      | WS.001.1a           
 PS.base.a2           | PF0_1  |                      | WS.001.1b           
 PS.base.a3           | PF0_1  |                      | WS.001.2a           
 PS.base.a4           | PF0_1  |                      |                     
 PS.base.a5           | PF0_1  |                      |                     
 PS.base.a6           | PF0_1  |                      |                     
(6 rows)

--
-- Same procedure for 2b-3b but this time updating the WSlot instead
-- of the PSlot. Due to the triggers the result is the same:
-- WSlot and corresponding PSlot point to each other.
--
update WSlot set backlink = 'PS.base.a4' where slotname = 'WS.001.2b';
update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3a';
select * from WSlot where roomno = '001' order by slotname;
       slotname       |  roomno  |       slotlink       |       backlink