Re: Can I do <page> of <pages> in one call?

Hi Wes

I'm doing this work in a single function call, or perhaps between one
controlling function that returns the row count and a cursor.

ROW_COUNT is a special pgsql variable, is it not?

I think I need advice on cursors, as your example encapsulates the
essence of my problem. I need the total row count, not the WHERE row
count, although I need the actual data in the rows from WHERE.

Rory

On 16/06/03, wsheldah(at)lexmark(dot)com (wsheldah(at)lexmark(dot)com) wrote:
> I don't have a direct answer to your question, but I think it would be
> better to use
>
> SELECT count(*) FROM mytable WHERE [where-clause]
>
> instead of getting the ROW_COUNTon the client side; this will make
> postgresql count the rows, which it can probably do faster,
> and only return the answer to your client, thus saving a little bandwidth.

> Rory Campbell-Lange <rory(at)campbell-lange(dot)net>@postgresql.org on 06/16/2003
> 12:43:54 PM

> > I am writing a function to interrogate the database and get a total row
> > count of rows matching the critera of a search, and then return a subset
> > of those rows. This is to satisfy the familiar web <page> of <pages>
> > situation.
> >
> > At the moment I call the query twice, one with limit and offset
> > statements, the other without these. I use ROW_COUNT after the second.
> >
> > Is it possible to do a query, get the ROW_COUNT, and then use a cursor
> > or something similar to get the subset into a setof result%rowtype?

--
Rory Campbell-Lange
<rory(at)campbell-lange(dot)net>
<www.campbell-lange.net>