Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-admin by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group