Re: Yet Another COUNT(*)...WHERE...question

"Rainer Bauer" <usenet(at)munnin(dot)com> writes:

> Anyway, what Phoenix is trying to say is that 2 queries are required: One to
> get the total count and one to get the tuples for the current page. I reckon
> it would help, if the query returning the result set could also report the
> total no. of tuples found. Somthing like
> SELECT COUNT(*), * FROM <table> WHERE <cond> OFFSET <o> LIMIT <l>
>
> Or is there a way to do that?

Well anything like the above would just report l as the count.

The only way to do it in Postgres currently is to create a temporary table.
Then you can populate it once, then select the count from the temporary table
in one query and the required page from it in the second query.

But temporary tables in Postgres are not really designed for this. In
particular they count as DDL so you have to grant privileges to create tables
to the application and it has to create and delete entries in pg_class for
every use.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com