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
SELECTuinfo2.id,ratedon,cid       FROM uinfo2,rbs2       WHERE urlinfo.id=ratings_by_serial.id and ratedby=rater
andratedon between start and finish       LOOP       IF rec.id<>svid THEN         IF rec.ratedon>last THEN
IFrec.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
WHEREcheckedby=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
''unreachabledone'';  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@ipinc.com
www.stbernard.com
www.ipinc.com