Re: row number with in cursor

The cursor is defined as follows:

currDR CURSOR (r character(15), sD date ) IS
select rate_key, rate_date, rate_value,
LAG(rate_date, 1 , null) OVER w as lag_r_date ,
LAG(rate_value, 1, null) Over w as lag_r_value ,
( LAG(rate_value, 1, null) Over w /100 * ( rate_date -
LAG(rate_date, 1 , null) OVER w ) ) / 360 as overnight_rate_return
from rate_quote
where rate_key = r and rate_date >= sD
WINDOW w as (PARTITION BY rate_key order by rate_date ROWS 1
PRECEDING )
order by 2 ;

Cursor works fine
Could I add a row number to the above?

KD

On Thu, Oct 6, 2016 at 8:26 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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(dot)klaver(at)aklaver(dot)com
>