Обсуждение: row number with in cursor

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

On 10/06/2016 06:01 PM, Kevin Duffy wrote:
> 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?

Heading out the door, but here is something:

https://www.postgresql.org/docs/9.6/static/functions-window.html

and a quick search using 'postgres row number':

https://www.google.com/search?q=postgres+row+number&ie=utf-8&oe=utf-8

>
> KD
>
> On Thu, Oct 6, 2016 at 8:26 PM, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.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
>     <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 <mailto:adrian.klaver@aklaver.com>
>
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Kevin Duffy schrieb am 07.10.2016 um 03:01:
> 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?


Sure, just add:
   ROW_NUMBER() OVER W AS RN 

As the overall result is ordered by the same expression as the window w, this should work without much overhead.

Thomas