Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id D3B99475917 for ; Wed, 8 May 2002 17:10:47 -0400 (EDT) Received: from teak.adhesivemedia.com (teak.adhesivemedia.com [207.202.159.79]) by postgresql.org (Postfix) with ESMTP id C62BB475DA7 for ; Wed, 8 May 2002 16:31:59 -0400 (EDT) Received: from localhost (philip@localhost) by teak.adhesivemedia.com (8.11.6/8.11.6) with ESMTP id g48KVdg25703; Wed, 8 May 2002 13:31:39 -0700 (PDT) (envelope-from philip@adhesivemedia.com) Date: Wed, 8 May 2002 13:31:39 -0700 (PDT) From: Philip Hallstrom To: Charles Hauser Cc: pgsql-sql@postgresql.org Subject: Re: CURSOR/FETCH vs LIMIT/OFFSET In-Reply-To: Message-ID: <20020508133050.A20473-100000@teak.adhesivemedia.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200205/57 X-Sequence-Number: 7693 Why not at the very beginning do something like "SELECT COUNT(*)..." and store that as $totalTuples... then use limit/offset... -philip On Wed, 8 May 2002, Charles Hauser wrote: > Hi, > > re: displaying results of query. > > 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.' > > -- > Regards, > > Chuck > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >