pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ashvinder Singh <ashvindersingh@hfcl.com>
To: pgsql-admin@postgresql.org
Subject: Using Cursor in PGSql ver 7.3
Date: Tue, 20 Apr 2004 11:21:21 +0530
Message-ID: <003c01c4269b$81df54e0$5608a8c0@ashvinder> (raw)

Hi All,
I want to use a Cursor in PGSql function. It should have all the functionalities like MoveNext, Fetch and MoveBackward etc.
Using PGSql Help provided,I have created a function 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 follows:

CREATE FUNCTION public.report_cursor(int4, timestamp, timestamp) RETURNS varchar AS '
declare 
        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_stamp, b.policy_name, a.inbound_byte from abr a,                 customer_policy b where a.class_id = i_id and time_stamp >= t1 and time_stamp <= t2 and 
        a.class_id = 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_stamp, b.policy_name, a.inbound_byte from abr a,                 customer_policy b where a.class_id = i_id and time_stamp >= t1 and time_stamp <= t2 and 
        a.class_id = b.class_id order by a.class_id, a.time_stamp desc;

        t1 := v_time1;
        t2 := v_time2;

        open cursor1;
        open cursor2;

loop
        fetch next from cursor1;
        if found then
                sumtotal :=0;  
                i_oid := cursor1.time_stamp;
                i_checktime :=( i_oid - interval ''5 minutes'');
                if (i_checktime < t1) then
                        exit;
                end if;

                fetch next from cursor2;

                for i in 1..20 loop
                        sumtotal:=(sumtotal + cursor2.inbound_byte);
                        fetch next from cursor2;
                end loop;
  
                sumtotal:=sumtotal/20;    

                insert into rep_test values(cursor1.class_id, cursor1.policy_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

Message-ID: <003c01c4269b$81df54e0$5608a8c0@ashvinder>
Permalink:  ../003c01c4269b$81df54e0$5608a8c0@ashvinder/
Also on:    postgresql.org/message-id/003c01c4269b$81df54e0$5608a8c0@ashvinder

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: ashvindersingh@hfcl.com
  Subject: Re: Using Cursor in PGSql ver 7.3
  In-Reply-To: <003c01c4269b$81df54e0$5608a8c0@ashvinder>
  References: <003c01c4269b$81df54e0$5608a8c0@ashvinder>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox