From: | Rory Campbell-Lange <rory(at)campbell-lange(dot)net> |
---|---|
To: | wsheldah(at)lexmark(dot)com |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Can I do <page> of <pages> in one call? |
Date: | 2003-06-16 23:54:27 |
Message-ID: | 20030616235427.GD4535@campbell-lange.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-06-17 00:04:12 | Re: Postgres performance comments from a MySQL user |
Previous Message | Manfred Koizar | 2003-06-16 23:45:58 | Re: Interesting incosistent query timing |