Re: psql: question - Mailing list pgsql-sql

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
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= $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

--
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.