Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 9DE33476A04 for ; Wed, 8 May 2002 13:13:58 -0400 (EDT) Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (Postfix) with ESMTP id 8E3644766DA for ; Wed, 8 May 2002 11:23:32 -0400 (EDT) Received: from [63.195.55.98] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.9) with HTTP id 1453665; Wed, 08 May 2002 08:23:32 -0700 From: "Josh Berkus" Subject: Re: psql: question To: Charles Hauser Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.5.9 Date: Wed, 08 May 2002 08:23:32 -0700 Message-ID: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200205/51 X-Sequence-Number: 7687 Chuck, Please cc: one of the Postgres lists when you query me. If you're getting Q&A support just from me, I'll have to start charging you! > Trying to work out details of how to let users page back and forth > among a multi-page list of results. > > Problem: I need to know the total number of tuples the query would > return in order to decide if there are more to display. > > 1) Use CURSOR and FETCH > > $res= $con->exec( > "BEGIN WORK; > DECLARE gene_result CURSOR FOR > SELECT blah blah > ORDER BY blah; > FETCH $offset IN gene_result > "); > > $result->ntuple: returns only the number set by $offset, not TOTAL > for query. > > 2) use LIMIT OFFSET > > same problem, > $result->ntuple: returns only the number set by LIMIT, OFFSET, not > TOTAL for query. > > So there has to be a way to glean both the TOTAL and the SUBSET > returned? > > Searching the postgresql archives obviously can deal with this > http://archives.postgresql.org/pgsql-sql/ > 'Displaying documents 11-20 of total 243 found.' Personally, I don't know any way to do this without running the query twice. Least inefficient approach: If your interface allows seperating the SELECT clause from the rest of the query, then run first: SELECT COUNT(*) FROM rest-of-query ... Check that value. If it's 0, then return a "no records found" message to the user. Otherwise, run the query broken into LIMIT and OFFSET blocks. More inefficient approach: if the way the query is being generated does not allow you to break off the SELECT clause, then you need to subselect a count: SELECT COUNT(*) FROM ( SELECT blah FROM rest_of_query) query_count; This will also give you a count, buut be slightly slower than the above. If anybody knows a way to get a count *without* running the query twice, I'd be glad to hear it! -Josh Berkus