plpgsql.out

关系型数据库 Postgresql 6.5.2

        select into rec * from PHone where slotname = myname;
	if not found then
	    raise exception ''% does not exists'', 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';
QUERY: 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';
QUERY: 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';
QUERY: 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';
QUERY: 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';
QUERY: 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;
QUERY: insert into Room values ('001', 'Entrance');
QUERY: insert into Room values ('002', 'Office');
QUERY: insert into Room values ('003', 'Office');
QUERY: insert into Room values ('004', 'Technical');
QUERY: insert into Room values ('101', 'Office');
QUERY: insert into Room values ('102', 'Conference');
QUERY: insert into Room values ('103', 'Restroom');
QUERY: insert into Room values ('104', 'Technical');
QUERY: insert into Room values ('105', 'Office');
QUERY: insert into Room values ('106', 'Office');
QUERY: insert into WSlot values ('WS.001.1a', '001', '', '');
QUERY: insert into WSlot values ('WS.001.1b', '001', '', '');
QUERY: insert into WSlot values ('WS.001.2a', '001', '', '');
QUERY: insert into WSlot values ('WS.001.2b', '001', '', '');
QUERY: insert into WSlot values ('WS.001.3a', '001', '', '');
QUERY: insert into WSlot values ('WS.001.3b', '001', '', '');
QUERY: insert into WSlot values ('WS.002.1a', '002', '', '');
QUERY: insert into WSlot values ('WS.002.1b', '002', '', '');
QUERY: insert into WSlot values ('WS.002.2a', '002', '', '');
QUERY: insert into WSlot values ('WS.002.2b', '002', '', '');
QUERY: insert into WSlot values ('WS.002.3a', '002', '', '');
QUERY: insert into WSlot values ('WS.002.3b', '002', '', '');
QUERY: insert into WSlot values ('WS.003.1a', '003', '', '');
QUERY: insert into WSlot values ('WS.003.1b', '003', '', '');
QUERY: insert into WSlot values ('WS.003.2a', '003', '', '');
QUERY: insert into WSlot values ('WS.003.2b', '003', '', '');
QUERY: insert into WSlot values ('WS.003.3a', '003', '', '');
QUERY: insert into WSlot values ('WS.003.3b', '003', '', '');
QUERY: insert into WSlot values ('WS.101.1a', '101', '', '');
QUERY: insert into WSlot values ('WS.101.1b', '101', '', '');
QUERY: insert into WSlot values ('WS.101.2a', '101', '', '');
QUERY: insert into WSlot values ('WS.101.2b', '101', '', '');
QUERY: insert into WSlot values ('WS.101.3a', '101', '', '');
QUERY: insert into WSlot values ('WS.101.3b', '101', '', '');
QUERY: insert into WSlot values ('WS.102.1a', '102', '', '');
QUERY: insert into WSlot values ('WS.102.1b', '102', '', '');
QUERY: insert into WSlot values ('WS.102.2a', '102', '', '');
QUERY: insert into WSlot values ('WS.102.2b', '102', '', '');
QUERY: insert into WSlot values ('WS.102.3a', '102', '', '');
QUERY: insert into WSlot values ('WS.102.3b', '102', '', '');
QUERY: insert into WSlot values ('WS.105.1a', '105', '', '');
QUERY: insert into WSlot values ('WS.105.1b', '105', '', '');
QUERY: insert into WSlot values ('WS.105.2a', '105', '', '');
QUERY: insert into WSlot values ('WS.105.2b', '105', '', '');
QUERY: insert into WSlot values ('WS.105.3a', '105', '', '');
QUERY: insert into WSlot values ('WS.105.3b', '105', '', '');
QUERY: insert into WSlot values ('WS.106.1a', '106', '', '');
QUERY: insert into WSlot values ('WS.106.1b', '106', '', '');
QUERY: insert into WSlot values ('WS.106.2a', '106', '', '');
QUERY: insert into WSlot values ('WS.106.2b', '106', '', '');
QUERY: insert into WSlot values ('WS.106.3a', '106', '', '');
QUERY: insert into WSlot values ('WS.106.3b', '106', '', '');
QUERY: insert into PField values ('PF0_1', 'Wallslots basement');
QUERY: insert into PSlot values ('PS.base.a1', 'PF0_1', '', '');
QUERY: insert into PSlot values ('PS.base.a2', 'PF0_1', '', '');
QUERY: insert into PSlot values ('PS.base.a3', 'PF0_1', '', '');
QUERY: insert into PSlot values ('PS.base.a4', 'PF0_1', '', '');
QUERY: insert into PSlot values ('PS.base.a5', 'PF0_1', '', '');
QUERY: insert into PSlot values ('PS.base.a6', 'PF0_1', '', '');
QUERY: insert into PSlot values ('PS.base.b1', 'PF0_1', '', 'WS.002.1a');
QUERY: insert into PSlot values ('PS.base.b2', 'PF0_1', '', 'WS.002.1b');
QUERY: insert into PSlot values ('PS.base.b3', 'PF0_1', '', 'WS.002.2a');
QUERY: insert into PSlot values ('PS.base.b4', 'PF0_1', '', 'WS.002.2b');
QUERY: insert into PSlot values ('PS.base.b5', 'PF0_1', '', 'WS.002.3a');
QUERY: insert into PSlot values ('PS.base.b6', 'PF0_1', '', 'WS.002.3b');
QUERY: insert into PSlot values ('PS.base.c1', 'PF0_1', '', 'WS.003.1a');
QUERY: insert into PSlot values ('PS.base.c2', 'PF0_1', '', 'WS.003.1b');
QUERY: insert into PSlot values ('PS.base.c3', 'PF0_1', '', 'WS.003.2a');
QUERY: insert into PSlot values ('PS.base.c4', 'PF0_1', '', 'WS.003.2b');
QUERY: insert into PSlot values ('PS.base.c5', 'PF0_1', '', 'WS.003.3a');
QUERY: insert into PSlot values ('PS.base.c6', 'PF0_1', '', 'WS.003.3b');
QUERY: insert into PField values ('PF0_X', 'Phonelines basement');
QUERY: insert into PSlot values ('PS.base.ta1', 'PF0_X', '', '');
QUERY: insert into PSlot values ('PS.base.ta2', 'PF0_X', '', '');
QUERY: insert into PSlot values ('PS.base.ta3', 'PF0_X', '', '');
QUERY: insert into PSlot values ('PS.base.ta4', 'PF0_X', '', '');
QUERY: insert into PSlot values ('PS.base.ta5', 'PF0_X', '', '');
QUERY: insert into PSlot values ('PS.base.ta6', 'PF0_X', '', '');
QUERY: insert into PSlot values ('PS.base.tb1', 'PF0_X', '', '');
QUERY: insert into PSlot values ('PS.base.tb2', 'PF0_X', '', '');
QUERY: insert into PSlot values ('PS.base.tb3', 'PF0_X', '', '');
QUERY: insert into PSlot values ('PS.base.tb4', 'PF0_X', '', '');
QUERY: insert into PSlot values ('PS.base.tb5', 'PF0_X', '', '');
QUERY: insert into PSlot values ('PS.base.tb6', 'PF0_X', '', '');
QUERY: insert into PField values ('PF1_1', 'Wallslots 1st floor');
QUERY: insert into PSlot values ('PS.1st.a1', 'PF1_1', '', 'WS.101.1a');
QUERY: insert into PSlot values ('PS.1st.a2', 'PF1_1', '', 'WS.101.1b');
QUERY: insert into PSlot values ('PS.1st.a3', 'PF1_1', '', 'WS.101.2a');
QUERY: insert into PSlot values ('PS.1st.a4', 'PF1_1', '', 'WS.101.2b');
QUERY: insert into PSlot values ('PS.1st.a5', 'PF1_1', '', 'WS.101.3a');
QUERY: insert into PSlot values ('PS.1st.a6', 'PF1_1', '', 'WS.101.3b');
QUERY: insert into PSlot values ('PS.1st.b1', 'PF1_1', '', 'WS.102.1a');
QUERY: insert into PSlot values ('PS.1st.b2', 'PF1_1', '', 'WS.102.1b');
QUERY: insert into PSlot values ('PS.1st.b3', 'PF1_1', '', 'WS.102.2a');
QUERY: insert into PSlot values ('PS.1st.b4', 'PF1_1', '', 'WS.102.2b');
QUERY: insert into PSlot values ('PS.1st.b5', 'PF1_1', '', 'WS.102.3a');
QUERY: insert into PSlot values ('PS.1st.b6', 'PF1_1', '', 'WS.102.3b');
QUERY: insert into PSlot values ('PS.1st.c1', 'PF1_1', '', 'WS.105.1a');
QUERY: insert into PSlot values ('PS.1st.c2', 'PF1_1', '', 'WS.105.1b');
QUERY: insert into PSlot values ('PS.1st.c3', 'PF1_1', '', 'WS.105.2a');
QUERY: insert into PSlot values ('PS.1st.c4', 'PF1_1', '', 'WS.105.2b');
QUERY: insert into PSlot values ('PS.1st.c5', 'PF1_1', '', 'WS.105.3a');
QUERY: insert into PSlot values ('PS.1st.c6', 'PF1_1', '', 'WS.105.3b');
QUERY: insert into PSlot values ('PS.1st.d1', 'PF1_1', '', 'WS.106.1a');
QUERY: insert into PSlot values ('PS.1st.d2', 'PF1_1', '', 'WS.106.1b');
QUERY: insert into PSlot values ('PS.1st.d3', 'PF1_1', '', 'WS.106.2a');
QUERY: insert into PSlot values ('PS.1st.d4', 'PF1_1', '', 'WS.106.2b');
QUERY: insert into PSlot values ('PS.1st.d5', 'PF1_1', '', 'WS.106.3a');
QUERY: insert into PSlot values ('PS.1st.d6', 'PF1_1', '', 'WS.106.3b');
QUERY: update PSlot set backlink = 'WS.001.1a' where slotname = 'PS.base.a1';
QUERY: update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a3';
QUERY: 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)

QUERY: 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)

QUERY: update PSlot set backlink = 'WS.001.2a' where slotname = 'PS.base.a3';
QUERY: 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     |                    |