agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedrow number with in cursor
5+ messages / 3 participants
[nested] [flat]
* row number with in cursor
@ 2016-10-07 00:05 Kevin Duffy <kevind0718@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Kevin Duffy @ 2016-10-07 00:05 UTC (permalink / raw)
To: pgsql-sql
Hello All:
I need your kind assistance, to learn if it is possible
within a cursor to know what row you are on.
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 ;
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;
thamks for your attention to this matter
KD
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: row number with in cursor
@ 2016-10-07 00:26 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Kevin Duffy <kevind0718@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Adrian Klaver @ 2016-10-07 00:26 UTC (permalink / raw)
To: Kevin Duffy <kevind0718@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: row number with in cursor
@ 2016-10-07 01:01 Kevin Duffy <kevind0718@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 5+ messages in thread
From: Kevin Duffy @ 2016-10-07 01:01 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-sql
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.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
>
> 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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: row number with in cursor
@ 2016-10-07 01:21 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Kevin Duffy <kevind0718@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Adrian Klaver @ 2016-10-07 01:21 UTC (permalink / raw)
To: Kevin Duffy <kevind0718@gmail.com>; +Cc: 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: row number with in cursor
@ 2016-10-07 05:42 Thomas Kellerer <spam_eater@gmx.net>
parent: Kevin Duffy <kevind0718@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Thomas Kellerer @ 2016-10-07 05:42 UTC (permalink / raw)
To: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2016-10-07 05:42 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-10-07 00:05 row number with in cursor Kevin Duffy <kevind0718@gmail.com>
2016-10-07 00:26 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-10-07 01:01 ` Kevin Duffy <kevind0718@gmail.com>
2016-10-07 01:21 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-10-07 05:42 ` Thomas Kellerer <spam_eater@gmx.net>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox