Thread: CURSOR/FETCH vs LIMIT/OFFSET
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 FORSELECT blah blahORDER 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
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) >
Hi all, Hi Tom, I was exploring ways to improve the time required to update a large table from the join of two others as in: UPDATE a FROM b, c; I found that whatever index I create, compound or not, PG insist on performing the cartesian product first. Now, if "b" and "c" are relatively small, that make sense, but when the cartesian product of "b" and "c" is and order of magnitude larger than "a" it makes no sense at all. Shouldn't the number of rows in "b" and "c" be reduced to the matching the criterias on "a" first? If "b" is meant to be a many to one relation to "c", shouldn't "a" be joined to "b" first? Is there a way I can force the "a" join "b" first? I also tried to do it in a PLPGSQL FOR LOOP, but because the script is processed as a transaction, I do not get much better performance. It would sure be nice to be able to commit the change on a row per row basis. THX jll