Re: row number with in cursor
От | Adrian Klaver |
---|---|
Тема | Re: row number with in cursor |
Дата | |
Msg-id | 5040cfab-e430-ba0f-80e3-40f61623d1cf@aklaver.com обсуждение исходный текст |
Ответ на | Re: row number with in cursor (Kevin Duffy <kevind0718@gmail.com>) |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: