Postgres Pro
Downloads
Home   >   mailing lists

Thread: number of rown in a cursor.

number of rown in a cursor.

From
Christoffer Gurell
Date:
When declaring a cursor is there a way to return the number of rows that
the declared cursor consists of ?

Re: number of rown in a cursor.

From
Tom Lane
Date:
Christoffer Gurell <orbit@0x63.nu> writes:
> When declaring a cursor is there a way to return the number of rows that
> the declared cursor consists of ?

Not without actually scanning the result, if that's what you meant.

            regards, tom lane

Re: number of rown in a cursor.

From
Christoffer Gurell
Date:
> Not without actually scanning the result, if that's what you meant.

so basically i have to do a move to the end ?

Re: number of rown in a cursor.

From
Scott Marlowe
Date:
On Mon, 2005-01-24 at 10:58, Christoffer Gurell wrote:
> > Not without actually scanning the result, if that's what you meant.
>
> so basically i have to do a move to the end ?

yep.  This is because one of the advantages of a cursor is that it only
runs partially and returns the first X rows for the fetch.  This keeps
load down so that many cursors hitting the machine at once don't all
materialize all their rows and chew up all that I/O, cpu, and memory.
Unfortunately, one of the side effects of this methodology is that no
one knows how many rows there really are until they've been fetched.

Re: number of rown in a cursor.

From
Christoffer Gurell
Date:
> yep.  This is because one of the advantages of a cursor is that it only
> runs partially and returns the first X rows for the fetch.  This keeps
> load down so that many cursors hitting the machine at once don't all
> materialize all their rows and chew up all that I/O, cpu, and memory.
> Unfortunately, one of the side effects of this methodology is that no
> one knows how many rows there really are until they've been fetched.

the thing is that i want to create a gui-widget that has the possibility
to show a large amount of data over a slow connection. My idea was that
i create a cursor and create a srollbar with the number of rows in the
cursor so the user can scroll and only fetch the rows displayed from the
cursor as the user releases the scrollbar.

If i understand right then the way to do this is: create the cursor,
move to the end to get the number of rows, move to the front. get data.
am i right? or is there a better way to achieve this ? perhaps with a
local view?

Re: number of rown in a cursor.

From
John DeSoi
Date:
On Jan 24, 2005, at 1:38 PM, Christoffer Gurell wrote:

> the thing is that i want to create a gui-widget that has the
> possibility
> to show a large amount of data over a slow connection. My idea was that
> i create a cursor and create a srollbar with the number of rows in the
> cursor so the user can scroll and only fetch the rows displayed from
> the
> cursor as the user releases the scrollbar.
>
> If i understand right then the way to do this is: create the cursor,
> move to the end to get the number of rows, move to the front. get data.
> am i right? or is there a better way to achieve this ? perhaps with a
> local view?


Yes, you are correct -- see long discussions in the archives on
count(*). There is no shortcut to determine the number of rows a query
(or cursor) will have. One option might be to set some fairly high
limit in your query (say 10,000) and then have your GUI include some
little warning if it is reached. Perhaps have an easy way to increase
it if user really wants to scroll more records. It is definitely a bit
messy to deal with this in a GUI, but scrolling a million records won't
be very useful anyway.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL