Re: some random() clarification needed

David Rowley <dgrowleyml@xxxxxxxxx> · Wed, 15 Jul 2020 11:53:21 +1200

On Wed, 15 Jul 2020 at 04:01, Marc Millas <marc.millas@xxxxxxxxxx> wrote:
> your answer helps me understand my first problem.
> so, I rewrote a simple loop so as to avoid the "volatile" behaviour.

Not sure what you're trying to do with the plpgsql, but you can just
escape the multiple evaluations by putting the volatile function in a
sub-select with no FROM clause.

SELECT ... FROM ... WHERE id = (SELECT ceiling(random()*2582));

Or the more traditional way to get a random row is:

SELECT ... FROM ... WHERE id BETWEEN 0 AND 2585 ORDER BY random() LIMIT 1;

David