Using Cursor in PGSql ver 7.3

From: "Ashvinder Singh" <ashvindersingh(at)hfcl(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Using Cursor in PGSql ver 7.3
Date: 2004-04-20 05:51:21
Message-ID: 003c01c4269b$81df54e0$5608a8c0@ashvinder
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Browse pgsql-admin by date

  From Date Subject
Next Message Enrico Ortmann 2004-04-20 07:56:43 self defined counter function
Previous Message Frank Finner 2004-04-20 05:05:51 Re: Going from a DB using SQL_ASCII to UNICODE