Thread: Using Cursor in PGSql ver 7.3

Using Cursor in PGSql ver 7.3

From

"Ashvinder Singh"

Date:

20 April 2004, 13:39:55

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