From: Thomas G. Lockhart <lockhart@alumni.caltech.edu>
To: Jackson, DeJuan <djackson@cpsgroup.com>
Cc: Mauro Bartolomeoli <mbarto@novacomp.it>
Cc: pgsql-sql@postgreSQL.org, PostgreSQL Hackers Mailing List <pgsql-hackers@hub.org>
Subject: Re: [HACKERS] RE: [SQL] random tuple
Date: Fri, 09 Oct 1998 04:43:55 +0000
Message-ID: <361D948B.9612C41C@alumni.caltech.edu> (raw)
References: <F10BB1FAF801D111829B0060971D839F45FE9D@cpsmail>
> > >> Is there any way to select a random row from a table?
> > > I'd look at using cursors and random().
> > Do you (or someone else) know how to use the following
> > PostgreSQL functions: oidrand(oid,int4) ,oidsrand(int4)?
> oidsrand(int4) -- seeds the random number generator for oidrand
> oidrand(oid, int4) -- returns a psudo-random oid
The regression test uses oidrand(), which is where I stumbled across it.
The behavior is that oidrand() returns a boolean true/false with an
inverse probability specified by the second argument. For example, given
a table t with 100 entries, and the query
select * from t where oidrand(oid, 10)
will return, on average, 10 (10%) of the entries at random. The function
is called 100 times in the course of the query, and uses random() or
something similar to decide whether to return true or false for any
particular instance.
select * from t where oidrand(oid, 1)
will, on average, return all entries (1/1 = 100%).
select * from t where oidrand(oid, 100)
will, on average, return 1 entry (1/100 = 1%) so sometimes will return
one, zero, or two entries, and occasionally return more than two
entries.
It's pretty random, probably with a Poisson distribution depending on
what you are asking for.
Presumably oidsrand() allows one to change the seed to keep the
pseudo-random results from repeating from one run to the next. But I
haven't looked into it.
- Tom
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: lockhart@alumni.caltech.edu, djackson@cpsgroup.com, mbarto@novacomp.it, pgsql-hackers@hub.org
Subject: Re: [HACKERS] RE: [SQL] random tuple
In-Reply-To: <361D948B.9612C41C@alumni.caltech.edu>
References: <F10BB1FAF801D111829B0060971D839F45FE9D@cpsmail> <361D948B.9612C41C@alumni.caltech.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox