X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 57A11D1BAF5 for ; Tue, 20 Apr 2004 02:51:32 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 68527-06 for ; Tue, 20 Apr 2004 02:51:31 -0300 (ADT) Received: from netserv.nehfcl.hfcl.com (unknown [203.110.91.190]) by svr1.postgresql.org (Postfix) with ESMTP id 334C1D1BAF8 for ; Tue, 20 Apr 2004 02:51:22 -0300 (ADT) Received: (from root@localhost) by netserv.nehfcl.hfcl.com (8.11.6/8.11.2) id i3K6CAK06435 for pgsql-admin@postgresql.org; Tue, 20 Apr 2004 11:42:10 +0530 Received: from ashvinder ([192.168.8.86]) by netserv.nehfcl.hfcl.com (8.11.6/8.11.2) with SMTP id i3K6C9O06427 for ; Tue, 20 Apr 2004 11:42:09 +0530 Message-ID: <003c01c4269b$81df54e0$5608a8c0@ashvinder> From: "Ashvinder Singh" To: Subject: Using Cursor in PGSql ver 7.3 Date: Tue, 20 Apr 2004 11:21:21 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0039_01C426C9.9B73DC40" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 Xmilter: nehfcl.hfcl.com X-Virus-Scanned: by AMaViS perl-11 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200404/264 X-Sequence-Number: 13237 This is a multi-part message in MIME format. ------=_NextPart_000_0039_01C426C9.9B73DC40 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi All, I want to use a Cursor in PGSql function. It should have all the functional= ities like MoveNext, Fetch and MoveBackward etc. Using PGSql Help provided,I have created a function using a Cursor but it i= s not working and gives errors. I am using PostGres ver 7.3. The code of the function is as follows: CREATE FUNCTION public.report_cursor(int4, timestamp, timestamp) RETURNS va= rchar AS ' declare=20 i_id ALIAS for $1; v_time1 ALIAS for $2; v_time2 ALIAS for $3; sumtotal integer; r record; t1 timestamp; t2 timestamp; i_oid timestamp; i_checktime timestamp; i integer; begin declare cursor1 CURSOR FOR select a.abr_id, a.class_id, a.time_sta= mp, b.policy_name, a.inbound_byte from abr a, customer_poli= cy b where a.class_id =3D i_id and time_stamp >=3D t1 and time_stamp <=3D t= 2 and=20 a.class_id =3D b.class_id order by a.class_id, a.time_stamp desc; declare cursor2 CURSOR FOR select a.abr_id, a.class_id, a.time_st= amp, b.policy_name, a.inbound_byte from abr a, customer_pol= icy b where a.class_id =3D i_id and time_stamp >=3D t1 and time_stamp <=3D = t2 and=20 a.class_id =3D b.class_id order by a.class_id, a.time_stamp desc; t1 :=3D v_time1; t2 :=3D v_time2; open cursor1; open cursor2; loop fetch next from cursor1; if found then sumtotal :=3D0;=20=20 i_oid :=3D cursor1.time_stamp; i_checktime :=3D( i_oid - interval ''5 minutes''); if (i_checktime < t1) then exit; end if; fetch next from cursor2; for i in 1..20 loop sumtotal:=3D(sumtotal + cursor2.inbound_byte); fetch next from cursor2; end loop; =20=20 sumtotal:=3Dsumtotal/20;=20=20=20=20 insert into rep_test values(cursor1.class_id, cursor1.polic= y_name, sumtotal, cursor1.time_stamp); move backward 20 from cursor2; else exit; end if; end loop; close cursor1; close cursor2; return 0; end; Thanks in advance, Regards, Ashvinder ------=_NextPart_000_0039_01C426C9.9B73DC40 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi All,
I want to use a Cursor in PGSql function. = It should=20 have all the functionalities like MoveNext, Fetch and MoveBackward=20 etc.
Using PGSql Help provided,I have created a= function=20 using a Cursor but it is not working and gives errors.
I am using PostGres ver 7.3.
The code of the function is as=20 follows:
 
CREATE FUNCTION public.report_cursor(int4,= =20 timestamp, timestamp) RETURNS varchar AS=20 '
declare 
        i_id ALIAS for= =20 $1;
        v_time1 ALIAS for=20 $2;
        v_time2 ALIAS for $3;
 
        sumt= otal=20 integer;
 
        r=20 record;
        t1=20 timestamp;
        t2=20 timestamp;
        i_oid=20 timestamp;
        i_checktime=20 timestamp;
 
        i=20 integer;
 
begin
 
       = declare=20 cursor1 CURSOR FOR select a.abr_id, a.class_id,  a.time_stamp,=20 b.policy_name, a.inbound_byte from abr a,       &n= bsp;=20         customer_policy b where a.class_= id =3D=20 i_id and time_stamp >=3D t1 and time_stamp <=3D t2 and
 &nbs= p; =20     a.class_id =3D b.class_id order by a.class_id, a.time_st= amp=20 desc;
 
        decl= are=20 cursor2  CURSOR FOR select a.abr_id, a.class_id,  a.time_sta= mp,=20 b.policy_name, a.inbound_byte from abr a,       &n= bsp;=20         customer_policy b where a.class_= id =3D=20 i_id and time_stamp >=3D t1 and time_stamp <=3D t2 and
 &nbs= p; =20     a.class_id =3D b.class_id order by a.class_id, a.time_st= amp=20 desc;
 
       = t1 :=3D=20 v_time1;
        t2 :=3D=20 v_time2;
 
        open= =20 cursor1;
        open cursor2;
 
loop
      &nb= sp; fetch=20 next from cursor1;
        if found=20 then
           =20     sumtotal :=3D0;  
   =20             i_oid :=3D=20 cursor1.time_stamp;
          &n= bsp;=20     i_checktime :=3D( i_oid - interval ''5=20 minutes'');
           =20     if (i_checktime < t1) then
   =20                = =20     exit;
       =20         end if;
 
       =20         fetch next from cursor2;
 
       =20         for i in 1..20 loop
  &n= bsp;=20                = =20     sumtotal:=3D(sumtotal +=20 cursor2.inbound_byte);
       =20                = =20 fetch next from cursor2;
       =20         end=20 loop;
  
       =20        =20 sumtotal:=3Dsumtotal/20;    
 
       =20         insert into rep_test=20 values(cursor1.class_id, cursor1.policy_name, sumtotal,=20 cursor1.time_stamp);
 
       =20         move backward 20 from=20 cursor2;
        else
   = ;=20            =20 exit;
        end if;
end loop;
<= /DIV>
 
       = close=20 cursor1;
        close cursor2;
 
        retu= rn=20 0;
end;
Thanks in advance,
Regards,
Ashvinder
------=_NextPart_000_0039_01C426C9.9B73DC40--