plsql in 7.1

Hi,

Does plpgsql in 7.1.2 has some change in implementation??
Since I have a plplsql function which works in pg-7.0,
however, in pg-7.1.2, sometimes doesn't work, I found sometimes,
I generate more then 800 pg_sorttemp???.??? file each has ~430k.
which causes our disk full so fast(when we have mutilpel client
call it).

Here it is:

CREATE FUNCTION getstats12(text,date) RETURNS text AS '
declare
rater alias for $1;
string text:='''';
c char(1):='','';
svid int4:=0;
rec record;
dnorat int4:=0;
drat int4:=0;
ddel int4:=0;
dunrch int4:=0;
wnorat int4:=0;
wrat int4:=0;
wdel int4:=0;
wunrch int4:=0;
dhours float4;
whours float4;
drph int2:=0;
wrph int2:=0;
nthday int2:=0;
start timestamp;
last timestamp;
finish timestamp;
begin
SELECT dayofweek($2) INTO nthday;
finish:=$2+''23:59:59''::time;
last:=finish-''1day''::timespan;
start:=($2-nthday)::timestamp;
FOR rec IN
SELECT urlinfo.id,ratedon,cid
FROM urlinfo,ratings_by_serial
WHERE urlinfo.id=ratings_by_serial.id and ratedby=rater
and ratedon between start and finish
UNION
SELECT uinfo2.id,ratedon,cid
FROM uinfo2,rbs2
WHERE urlinfo.id=ratings_by_serial.id and ratedby=rater
and ratedon between start and finish
LOOP
IF rec.id<>svid THEN
IF rec.ratedon>last THEN
IF rec.cid= -1 THEN
dnorat:=dnorat+1;
ELSE
drat:=drat+1;
END IF;
ELSE
IF rec.cid= -1 THEN
wnorat:=wnorat+1;
ELSE
wrat:=wrat+1;
END IF;
END IF;
END IF;
svid:=rec.id;
END LOOP;
--raise notice ''urlinfo done'';
FOR rec IN
SELECT deleteddate
FROM deleted
WHERE allocatedto=rater
and deleteddate between start and finish
LOOP
IF rec.deleteddate>last THEN
ddel:=ddel+1;
ELSE
wdel:=wdel+1;
END IF;
END LOOP;
--raise notice ''deleted done'';
FOR rec IN
SELECT checkdate
FROM unreachable
WHERE checkedby=rater
and checkdate between start and finish
LOOP
IF rec.checkdate>last THEN
dunrch:=dunrch+1;
ELSE
wunrch:=wunrch+1;
END IF;
END LOOP;
--raise notice ''unreachable done'';
wnorat:=wnorat+dnorat;
wrat:=wrat+drat;
wdel:=wdel+ddel;
wunrch:=wunrch+dunrch;
SELECT getratinghour(rater,$2,$2) INTO dhours;
SELECT getratinghour(rater,start::date,$2) INTO whours;
IF dhours>0 THEN
drph:=round(drat/dhours);
END IF;
IF whours>0 THEN
wrph:=round(wrat/whours);
END IF;
string:=string||drat||c||wrat||c||dnorat||c||wnorat||c||
ddel||c||wdel||c||dunrch||c||wunrch||c||
dhours||c||whours||c||drph||c||wrph;
return string;
end;
' LANGUAGE 'plpgsql';

Thanks.

Jie LIANG

St. Bernard Software

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang(at)ipinc(dot)com
www.stbernard.com
www.ipinc.com