Thread: Methods for stepping through results?

Greetings,

Is there any way to do the following:

1.  Return a query in groups of x (i.e. 10 or 20) and progressively return
result groups of x.  Just think of search engine navigation.

2.  I have a client who wants to be able to select certain rows (tuples) of
a result and then "re-submit" the query and have only those rows selected
in the new results.  For example, if a query returns 10 rows and rows
2,4,7,9 are checked, when re-submitted only rows 2,4,7,9 would be returned.
 I know it is kind of stupid, but I have to please the customer :(

The interface I'm using is libpq, apache server, and Netscape/IE.

Thanks,
Matthew

Matthew Hagerty <matthew@venux.net> writes:
> Is there any way to do the following:
> 1.  Return a query in groups of x (i.e. 10 or 20) and progressively return
> result groups of x.  Just think of search engine navigation.

DECLARE CURSOR ... FETCH ...

There has been some talk of providing a facility for this inside libpq,
where it could be used for handling the results of an ordinary SELECT.
But it doesn't seem real high priority, given that you can use a cursor.

> 2.  I have a client who wants to be able to select certain rows (tuples) of
> a result and then "re-submit" the query and have only those rows selected
> in the new results.  For example, if a query returns 10 rows and rows
> 2,4,7,9 are checked, when re-submitted only rows 2,4,7,9 would be returned.
>  I know it is kind of stupid, but I have to please the customer :(

Seems like something you should be handling inside your application.
Why would you "re-submit" the same query in the first place, if you
already have the results?

            regards, tom lane

At 10:47 AM 2/16/99 -0500, Tom Lane wrote:
>Matthew Hagerty <matthew@venux.net> writes:
>> Is there any way to do the following:
>> 1.  Return a query in groups of x (i.e. 10 or 20) and progressively return
>> result groups of x.  Just think of search engine navigation.
>
>DECLARE CURSOR ... FETCH ...
>
>There has been some talk of providing a facility for this inside libpq,
>where it could be used for handling the results of an ordinary SELECT.
>But it doesn't seem real high priority, given that you can use a cursor.
>

Just a quick question about cursors.  Say you have a query that returns
1000 rows (tuples), if I use libpq and PQexec, I am under the impression
that the whole result set is returned in the PQrestult structure, Yes?  So
more big queries mean more memory used for each user accessing the database?

Now how does a cursor differ?  Does the backend still have to run the whole
query and store the results somewhere?  Or does a cursor return only one
row (tuple) at a time, which means less memory usage, but more work for the
backend?

I'm really unclear on why and when I would use a cursor instead of just
stepping through the PQrestult set.  Afterall you can specify a row (tuple)
in PQfetchrow.  All the books I have on SQL only tell how to use cursors,
none tell why or how they make anything better.


>> 2.  I have a client who wants to be able to select certain rows (tuples) of
>> a result and then "re-submit" the query and have only those rows selected
>> in the new results.  For example, if a query returns 10 rows and rows
>> 2,4,7,9 are checked, when re-submitted only rows 2,4,7,9 would be returned.
>>  I know it is kind of stupid, but I have to please the customer :(
>
>Seems like something you should be handling inside your application.
>Why would you "re-submit" the same query in the first place, if you
>already have the results?
>

I know, I was thinking the same thing, why would I re-submit?  But the
client software is a browser and I'm not sure how I would do it.


Thanks,
Matthew

On Tue, 16 Feb 1999, Tom Lane wrote:

> Matthew Hagerty <matthew@venux.net> writes:
> > Is there any way to do the following:
> > 1.  Return a query in groups of x (i.e. 10 or 20) and progressively return
> > result groups of x.  Just think of search engine navigation.
>
> DECLARE CURSOR ... FETCH ...
>
> There has been some talk of providing a facility for this inside libpq,
> where it could be used for handling the results of an ordinary SELECT.
> But it doesn't seem real high priority, given that you can use a cursor.

Part of the JDBC2 spec has the option of this sort of handling of
select's.

Peter

--
       Peter T Mount peter@retep.org.uk
      Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
 Java PDF Generator: http://www.retep.org.uk/pdf