selecting random rows

Here is Josh Berkus' solution for randomly picking a single row from a
query.  I think the FAQ (www.postgresql.org/docs/faqs.FAQ.html#item4.1)
could be updated with a link to this solution, which is more practical
for large queries.

    www.powerpostgresql.com/Random_Aggregate

Here is a discussion by Greg Sabino Mullane about getting random subsets
of table rows.  Greg's approach involves modifying the table you want to
query from and is not very general-purpose (although it is a nice read).


people.planetpostgresql.org/greg/index.php?/archives/40-Getting-random-rows-from-a-database-table.html

I've seen Josh's approach extended to the multiple-row-subset case.  I
think the perl pseudo-code would look like this:

# $n is the desired number of rows
while(<>)
{
  if(rand($.)<$n) # This is the probability that the current line should
be in the output if it were the last line of the
input
          

  {
     # Remove (at random) one of the current
selections
                                

      splice(@lines,rand(@lines),1) if @lines==$n;
     # and add the latest selection at the
end
                                 

      push(@lines,$_);
  }
}

Would it be possible to implement this as a function in PG?

Aside: I'm fantasizing about a postgresql archive of user-submitted
functions. Is the pgfoundry the closest thing to this?

-Kevin Murphy