Subject: Re: row number with in cursor
To: Kevin Duffy <kevind0718@gmail.com>, pgsql-sql@postgresql.org
References: 
 <CAHCyeW1VwGD5JYX4GhHj-Yr3Tmupnrb_onm2BX+Vopb84=Tf1w@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <81027780-1d91-4d1a-a1ef-7a6cfc77b37b@aklaver.com>
Date: Thu, 6 Oct 2016 17:26:55 -0700
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:45.0) Gecko/20100101
 Thunderbird/45.4.0
MIME-Version: 1.0
In-Reply-To: 
 <CAHCyeW1VwGD5JYX4GhHj-Yr3Tmupnrb_onm2BX+Vopb84=Tf1w@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On 10/06/2016 05:05 PM, Kevin Duffy wrote:
> Hello All:
>
> I need your kind assistance, to learn if it is possible
> within a cursor to know what row you are on.

What version of Postgres?

Are you actually using a CURSOR as defined by plpgsql?:

https://www.postgresql.org/docs/9.6/static/plpgsql-cursors.html

Looks like you are LOOPing over the results of a function?

> Something like this:
>
> for currDateRate  IN  currDR( rate_in,  start_date )  LOOP
>
>   raise notice ' currDateRate.rate_date: %', currDateRate.rate_date ;
>   raise notice ' currDateRate.lag_r_value: %', currDateRate.lag_r_value ;
>   raise notice ' currDateRate.rate_value: %', currDateRate.rate_value ;
>   raise notice ' currDateRate.overnight_r: %',
> currDateRate.overnight_rate_return ;
>   tr_index  :=  tr_index  *( 1+ currDateRate.overnight_rate_return  )  ;
>   raise notice ' tr_index: %',tr_index ;
>  -- this does NOT work
>   raise notice ' row number %', currDateRate%ROWNUMBER ;

There is no ROWNUMBER in plpgsql(I am assuming you are using that?).

>
> End LOOP;
>
> for testing purposes would like to break out after twenty records.
> Yes I know I could do a simple counter like this:
>
>   rtn_cnt :=  rtn_cnt +1;
>   if rtn_cnt  >= 20  then
>       return  rtn_cnt;
>   END IF;

I think you will need to use some variation of the above.
Or add an argument to currDR(assuming it is a function) that sets a LIMIT.

>
>
> thamks for your attention to this matter
>
> KD


-- 
Adrian Klaver
adrian.klaver@aklaver.com


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql