|
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
|
