Add functions to generate random numbers in a specified range.

This adds 3 new variants of the random() function:

    random(min integer, max integer) returns integer
    random(min bigint, max bigint) returns bigint
    random(min numeric, max numeric) returns numeric

Each returns a random number x in the range min <= x <= max.

For the numeric function, the number of digits after the decimal point
is equal to the number of digits that "min" or "max" has after the
decimal point, whichever has more.

The main entry points for these functions are in a new C source file.
The existing random(), random_normal(), and setseed() functions are
moved there too, so that they can all share the same PRNG state, which
is kept private to that file.

Dean Rasheed, reviewed by Jian He, David Zhang, Aleksander Alekseev,
and Tomas Vondra.

Discussion: https://postgr.es/m/CAEZATCV89Vxuq93xQdmc0t-0Y2zeeNQTdsjbmV7dyFBPykbV4Q@mail.gmail.com

4 files changed, 18 insertions, 1 deletions

diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index a00ed3075c7..fd4a482d90b 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202403271
+#define CATALOG_VERSION_NO	202403272
 
 #endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 2f7cfc02c6d..07023ee61d9 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3381,6 +3381,18 @@
   proname => 'random_normal', provolatile => 'v', proparallel => 'r',
   prorettype => 'float8', proargtypes => 'float8 float8',
   prosrc => 'drandom_normal' },
+{ oid => '9719', descr => 'random integer in range',
+  proname => 'random', provolatile => 'v', proparallel => 'r',
+  prorettype => 'int4', proargtypes => 'int4 int4',
+  proargnames => '{min,max}', prosrc => 'int4random' },
+{ oid => '9720', descr => 'random bigint in range',
+  proname => 'random', provolatile => 'v', proparallel => 'r',
+  prorettype => 'int8', proargtypes => 'int8 int8',
+  proargnames => '{min,max}', prosrc => 'int8random' },
+{ oid => '9721', descr => 'random numeric in range',
+  proname => 'random', provolatile => 'v', proparallel => 'r',
+  prorettype => 'numeric', proargtypes => 'numeric numeric',
+  proargnames => '{min,max}', prosrc => 'numeric_random' },
 { oid => '1599', descr => 'set random seed',
   proname => 'setseed', provolatile => 'v', proparallel => 'r',
   prorettype => 'void', proargtypes => 'float8', prosrc => 'setseed' },
diff --git a/src/include/common/pg_prng.h b/src/include/common/pg_prng.h
index e201b95686c..c114c6419d0 100644
--- a/src/include/common/pg_prng.h
+++ b/src/include/common/pg_prng.h
@@ -51,6 +51,7 @@ extern uint64 pg_prng_uint64(pg_prng_state *state);
 extern uint64 pg_prng_uint64_range(pg_prng_state *state, uint64 rmin, uint64 rmax);
 extern int64 pg_prng_int64(pg_prng_state *state);
 extern int64 pg_prng_int64p(pg_prng_state *state);
+extern int64 pg_prng_int64_range(pg_prng_state *state, int64 rmin, int64 rmax);
 extern uint32 pg_prng_uint32(pg_prng_state *state);
 extern int32 pg_prng_int32(pg_prng_state *state);
 extern int32 pg_prng_int32p(pg_prng_state *state);
diff --git a/src/include/utils/numeric.h b/src/include/utils/numeric.h
index 2f7184e299b..43c75c436fe 100644
--- a/src/include/utils/numeric.h
+++ b/src/include/utils/numeric.h
@@ -14,6 +14,7 @@
 #ifndef _PG_NUMERIC_H_
 #define _PG_NUMERIC_H_
 
+#include "common/pg_prng.h"
 #include "fmgr.h"
 
 /*
@@ -103,4 +104,7 @@ extern Numeric numeric_mod_opt_error(Numeric num1, Numeric num2,
 extern int32 numeric_int4_opt_error(Numeric num, bool *have_error);
 extern int64 numeric_int8_opt_error(Numeric num, bool *have_error);
 
+extern Numeric random_numeric(pg_prng_state *state,
+							  Numeric rmin, Numeric rmax);
+
 #endif							/* _PG_NUMERIC_H_ */