Re: Functions to return random numbers in a given range

Hi

čt 21. 12. 2023 v 18:06 odesílatel Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
napsal:

> Attached is a patch that adds 3 SQL-callable functions to return
> random integer/numeric values chosen uniformly from a given range:
>
> random(min int, max int) returns int
> random(min bigint, max bigint) returns bigint
> random(min numeric, max numeric) returns numeric
>
The return value is in the range [min, max], and in the numeric case,
> the result scale equals Max(scale(min), scale(max)), so it can be used
> to generate large random integers, as well as decimals.
>
> The goal is to provide simple, easy-to-use functions that operate
> correctly over arbitrary ranges, which is trickier than it might seem
> using the existing random() function. The main advantages are:
>
> 1. Support for arbitrary bounds (provided that max >= min). A SQL or
> PL/pgSQL implementation based on the existing random() function can
> suffer from integer overflow if the difference max-min is too large.
>
> 2. Uniform results over the full range. It's easy to overlook the fact
> that in a naive implementation doing something like
> "((max-min)*random()+min)::int", the endpoint values will be half as
> likely as any other value, since casting to integer rounds to nearest.
>
> 3. Makes better use of the underlying PRNG, not limited to the 52-bits
> of double precision values.
>
> 4. Simpler and more efficient generation of random numeric values.
> This is something I have commonly wanted in the past, and have usually
> resorted to hacks involving multiple calls to random() to build
> strings of digits, which is horribly slow, and messy.
>
> The implementation moves the existing random functions to a new source
> file, so the new functions all share a common PRNG state with the
> existing random functions, and that state is kept private to that
> file.
>

+1

Regards

Pavel

> Regards,
> Dean
>