[PERFORM] Find original number of rows before applied LIMIT/OFFSET?

[D. Dante Lorenso]

I need to know that original number of rows that WOULD have been returned
by a SELECT statement if the LIMIT / OFFSET where not present in the 
statement.
Is there a way to get this data from PG ?

   SELECT
   ... ;
   ----> returns 100,000 rows

but,

   SELECT
   ...
   LIMIT x
   OFFSET y;
   ----> returns at most x rows

In order to build a list pager on a web site, I want to select 'pages' of a
result set at a time.  However, I need to know the original select 
result set
size because I still have to draw the 'page numbers' to display what 
pages are
available.

I've done this TWO ways in the past:

   1) TWO queries.  The first query will perform a SELECT COUNT(*) ...; and
   the second query performs the actualy SELECT ... LIMIT x OFFSET y;
   2) Using PHP row seek and only selecting the number of rows I need.

Here is an example of method number 2 in PHP:

   //----------------------------------------------------------------------
   function query_assoc_paged ($sql, $limit=0, $offset=0) {
       $this->num_rows = false;
       // open a result set for this query...
       $result = $this->query($sql);
       if (! $result) return (false);
       // save the number of rows we are working with
       $this->num_rows = @pg_num_rows($result);
       // moves the internal row pointer of the result to point to our
       // desired offset. The next call to pg_fetch_assoc() would return
       // that row.
       if (! empty($offset)) {
           if (! @pg_result_seek($result, $offset)) {
               return (array());
           };
       }
       // gather the results together in an array of arrays...
       $data = array();
       while (($row = pg_fetch_assoc($result)) !== false) {
           $data[] = $row;
          
           // After reading N rows from this result set, free our memory
           // and return the rows we fetched...
           if (! empty($limit) && count($data) >= $limit) {
               pg_free_result($result);
               return ($data);
           }
       }

       pg_free_result($result);
       return($data);
   }
   //----------------------------------------------------------------------

In this approach, I am 'emulating' the LIMIT / OFFSET features in PostgreSQL
by just seeking forward in the result set (offset) and only fetching the
number of rows that match my needs (LIMIT).
QUESTION: Is this the best way to do this, or is there a more efficient way
to get at the data I want?  Is there a variable set in PG that tells me the
original number of rows in the query?  Something like:
   SELECT ORIG_RESULT_SIZE, ...
   ...
   LIMIT x
   OFFSET y;
Or can I run another select right afterwards...like:

   SELECT ...
   ...
   LIMIT x
   OFFSET y;
   SELECT unfiltered_size_of_last_query();

Any thoughts?  Sure, the PHP function I'm using above 'works', but is it
the most efficient?  I hope I'm not actually pulling all 100,000 records
across the wire when I only intend to show 10 at a time.  See what I'm
getting at?
TIA,

Dante

---------
D. Dante Lorenso
[EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org