Re: 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