| 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: | Whole Thread | Raw Message | 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
| 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 |