=?iso-8859-1?Q?SZUCS_G=E1bor?= <surrano@mailbox.hu> writes:
>> CREATE TABLE poetry ( rand SERIAL, ... );
>>
>> SELECT * FROM poetry WHERE rand = (
>> SELECT int8( curval( 'poetry_rand_seq') * random()));
> Mmmm... It usually doesn't work for me.
Yeah ... better would be
>> SELECT * FROM poetry WHERE rand = (
>> SELECT int8( (select last_value from poetry_rand_seq) * random()));
Personally though, I'd skip the sequence entirely and do
create table poetry (...,
rand float8 default random());
create index on poetry.rand
select * from poetry where rand > random() order by rand limit 1;
A difficulty with either of these approaches is that the system won't
optimize comparisons involving random() into indexscans. To get around
that, you'd have to hide the random() call inside a user-defined
function that is (bogusly) marked cachable (or in 7.3, "stable" would be
the best choice). At the moment I think it'd also work to stick the
random() call inside a subselect, but the UDF approach is less likely to
get broken by future changes.
regards, tom lane
