Re: number of rown in a cursor.

When declaring a cursor is there a way to return the number of rows that
the declared cursor consists of ?

Christoffer Gurell <orbit(at)0x63(dot)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

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

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

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.

> 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?

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