Hi čt 21. 12. 2023 v 18:06 odesílatel Dean Rasheed <dean.a.rash...@gmail.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 >
