X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5EB3CD1E1C7; Wed, 7 Jan 2004 17:18:22 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 45900-09; Wed, 7 Jan 2004 13:17:39 -0400 (AST) Received: from rwcrmhc11.comcast.net (rwcrmhc11.comcast.net [204.127.198.35]) by svr1.postgresql.org (Postfix) with ESMTP id C5A3FD1E1FE; Wed, 7 Jan 2004 13:15:03 -0400 (AST) Received: from lorenso.com (c-24-1-26-144.client.comcast.net[24.1.26.144]) by comcast.net (rwcrmhc11) with ESMTP id <2004010716573001300797rke>; Wed, 7 Jan 2004 16:57:30 +0000 Message-ID: <3FFC3A82.4000401@lorenso.com> Date: Wed, 07 Jan 2004 10:57:38 -0600 From: "D. Dante Lorenso" User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6b) Gecko/20031205 Thunderbird/0.4 X-Accept-Language: en-us, en MIME-Version: 1.0 To: "pgsql-general@postgresql.org" , pgsql-performance@postgresql.org Subject: Find original number of rows before applied LIMIT/OFFSET? Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/268 X-Sequence-Number: 55296 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 dante@lorenso.com