On Jan 15, 2008, at 3:03 PM, Ivan Sergio Borgonovo wrote:
On Tue, 15 Jan 2008 14:43:35 +0100
Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
You need to scroll to the last row to find the size of the result
set, but after that it's pretty easy to return random rows by
scrolling to them (and marking them 'read' in some way to prevent
accidentally returning the same row again).
Could you post a snippet of code or something giving a more detailed
idea of it?
BTW since cursors support offset if you're not interested if the
order of the retrieved rows is random too you don't even have to
remember which one you read I think.
I posted it on this list a while ago when I came up with this
solution. I had some trouble finding my old post in the pgsql-general
archives though - I could find the thread, just not my final posting,
and searching didn't even turn up the thread.
I did find it here: http://www.mail-archive.com/pgsql-
general@xxxxxxxxxxxxxx/msg103670.html
The thread contains several other approaches to the problem, it
really depends on your problem domain which one fits your bill.
I think the function in my original posting could do with clearer
comments though, so here's the function again:
/*
* Return $limit random rows from the result set of SQL query $query
*/
function randomSet(
$query, // The query to execute
$limit // The (max) number of random rows required
) {
// SQL to declare the cursor
query("DECLARE _cur SCROLL CURSOR WITHOUT HOLD FOR $query");
/* Get the range for random(1, n)
*
* Determined by scrolling the cursor to the last row.
* Equivalent to select count(*), but without a separate query.
*/
query("MOVE FORWARD ALL IN _cur");
$count = pg_affected_rows();
$uniques = array(); // A list of used cursor offsets
$resultSet = array();
// Fetch random rows until we have enough or there are no more
while ($limit > 0 && count($uniques) < $count) {
// Determine random scroll offset
$idx = random(1, $count);
// Skip records with an index we already used
if (in_array($idx, $uniques))
continue;
//Fetch the random row
$record = query("FETCH ABSOLUTE $idx FROM _cur");
// Add the row offset to the list of used offsets
$uniques[] = $idx;
$resultSet[] = $record;
$limit--;
}
// query
query("CLOSE _cur");
return $resultSet;
}
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,478f32e59497683469944!
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
