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
