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