agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@aklaver.com>
To: Kevin Duffy <kevind0718@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: row number with in cursor
Date: Thu, 6 Oct 2016 17:26:55 -0700
Message-ID: <81027780-1d91-4d1a-a1ef-7a6cfc77b37b@aklaver.com> (raw)
In-Reply-To: <CAHCyeW1VwGD5JYX4GhHj-Yr3Tmupnrb_onm2BX+Vopb84=Tf1w@mail.gmail.com>
References: <CAHCyeW1VwGD5JYX4GhHj-Yr3Tmupnrb_onm2BX+Vopb84=Tf1w@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
view thread (5+ messages) latest in thread
Message-ID: <81027780-1d91-4d1a-a1ef-7a6cfc77b37b@aklaver.com>
Permalink: ../81027780-1d91-4d1a-a1ef-7a6cfc77b37b@aklaver.com/
Also on: postgresql.org/message-id/81027780-1d91-4d1a-a1ef-7a6cfc77b37b@aklaver.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@aklaver.com, kevind0718@gmail.com
Subject: Re: row number with in cursor
In-Reply-To: <81027780-1d91-4d1a-a1ef-7a6cfc77b37b@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox