Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 39A8C4760FC for ; Thu, 9 May 2002 01:22:23 -0400 (EDT) Received: from mail-relay.trade-india.com (unknown [203.196.129.235]) by postgresql.org (Postfix) with SMTP id 1D3BB475F29 for ; Thu, 9 May 2002 01:22:15 -0400 (EDT) Received: (qmail 26379 invoked from network); 9 May 2002 05:14:56 -0000 Received: from system29.trade-india-local.com (HELO server) (192.168.0.29) by ptil-236-129-del.primus-india.net with SMTP; 9 May 2002 05:14:56 -0000 Content-Type: text/plain; charset="iso-8859-1" From: "Rajesh Kumar Mallah." Organization: Infocom Network Limited. To: "Josh Berkus" , Charles Hauser Subject: Re: psql: question Date: Thu, 9 May 2002 10:51:17 +0530 X-Mailer: KMail [version 1.4] Cc: pgsql-sql@postgresql.org References: In-Reply-To: MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200205091051.17419.mallah@trade-india.com> X-Archive-Number: 200205/66 X-Sequence-Number: 7702 Hi I run the query twice only the first time the query is requested. In a web context i can keep passing the total number of record as a query parameter for displaying the subsequent pages for the first request is 50% efficient but the remaining are 100% but the above works under the assumption that you do not add=20 record while the end user is browsing the pages which is mostly safe. regds mallah. On Wednesday 08 May 2002 08:53 pm, Josh Berkus wrote: > 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=3D $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 > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org --=20 Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.