summaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/utils/adt/pseudorandomfuncs.c104
-rw-r--r--src/include/catalog/catversion.h2
-rw-r--r--src/include/catalog/pg_proc.dat12
-rw-r--r--src/test/regress/expected/random.out87
-rw-r--r--src/test/regress/sql/random.sql26
5 files changed, 222 insertions, 9 deletions
diff --git a/src/backend/utils/adt/pseudorandomfuncs.c b/src/backend/utils/adt/pseudorandomfuncs.c
index e7b8045f925..1d2a981491b 100644
--- a/src/backend/utils/adt/pseudorandomfuncs.c
+++ b/src/backend/utils/adt/pseudorandomfuncs.c
@@ -17,6 +17,7 @@
#include "common/pg_prng.h"
#include "miscadmin.h"
+#include "utils/date.h"
#include "utils/fmgrprotos.h"
#include "utils/numeric.h"
#include "utils/timestamp.h"
@@ -26,6 +27,18 @@ static pg_prng_state prng_state;
static bool prng_seed_set = false;
/*
+ * Macro for checking the range bounds of random(min, max) functions. Throws
+ * an error if they're the wrong way round.
+ */
+#define CHECK_RANGE_BOUNDS(rmin, rmax) \
+ do { \
+ if ((rmin) > (rmax)) \
+ ereport(ERROR, \
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE), \
+ errmsg("lower bound must be less than or equal to upper bound")); \
+ } while (0)
+
+/*
* initialize_prng() -
*
* Initialize (seed) the PRNG, if not done yet in this process.
@@ -129,10 +142,7 @@ int4random(PG_FUNCTION_ARGS)
int32 rmax = PG_GETARG_INT32(1);
int32 result;
- if (rmin > rmax)
- ereport(ERROR,
- errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("lower bound must be less than or equal to upper bound"));
+ CHECK_RANGE_BOUNDS(rmin, rmax);
initialize_prng();
@@ -153,10 +163,7 @@ int8random(PG_FUNCTION_ARGS)
int64 rmax = PG_GETARG_INT64(1);
int64 result;
- if (rmin > rmax)
- ereport(ERROR,
- errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("lower bound must be less than or equal to upper bound"));
+ CHECK_RANGE_BOUNDS(rmin, rmax);
initialize_prng();
@@ -177,9 +184,90 @@ numeric_random(PG_FUNCTION_ARGS)
Numeric rmax = PG_GETARG_NUMERIC(1);
Numeric result;
+ /* Leave range bound checking to random_numeric() */
+
initialize_prng();
result = random_numeric(&prng_state, rmin, rmax);
PG_RETURN_NUMERIC(result);
}
+
+
+/*
+ * date_random() -
+ *
+ * Returns a random date chosen uniformly in the specified range.
+ */
+Datum
+date_random(PG_FUNCTION_ARGS)
+{
+ int32 rmin = (int32) PG_GETARG_DATEADT(0);
+ int32 rmax = (int32) PG_GETARG_DATEADT(1);
+ DateADT result;
+
+ CHECK_RANGE_BOUNDS(rmin, rmax);
+
+ if (DATE_IS_NOBEGIN(rmin) || DATE_IS_NOEND(rmax))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower and upper bounds must be finite"));
+
+ initialize_prng();
+
+ result = (DateADT) pg_prng_int64_range(&prng_state, rmin, rmax);
+
+ PG_RETURN_DATEADT(result);
+}
+
+/*
+ * timestamp_random() -
+ *
+ * Returns a random timestamp chosen uniformly in the specified range.
+ */
+Datum
+timestamp_random(PG_FUNCTION_ARGS)
+{
+ int64 rmin = (int64) PG_GETARG_TIMESTAMP(0);
+ int64 rmax = (int64) PG_GETARG_TIMESTAMP(1);
+ Timestamp result;
+
+ CHECK_RANGE_BOUNDS(rmin, rmax);
+
+ if (TIMESTAMP_IS_NOBEGIN(rmin) || TIMESTAMP_IS_NOEND(rmax))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower and upper bounds must be finite"));
+
+ initialize_prng();
+
+ result = (Timestamp) pg_prng_int64_range(&prng_state, rmin, rmax);
+
+ PG_RETURN_TIMESTAMP(result);
+}
+
+/*
+ * timestamptz_random() -
+ *
+ * Returns a random timestamptz chosen uniformly in the specified range.
+ */
+Datum
+timestamptz_random(PG_FUNCTION_ARGS)
+{
+ int64 rmin = (int64) PG_GETARG_TIMESTAMPTZ(0);
+ int64 rmax = (int64) PG_GETARG_TIMESTAMPTZ(1);
+ TimestampTz result;
+
+ CHECK_RANGE_BOUNDS(rmin, rmax);
+
+ if (TIMESTAMP_IS_NOBEGIN(rmin) || TIMESTAMP_IS_NOEND(rmax))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower and upper bounds must be finite"));
+
+ initialize_prng();
+
+ result = (TimestampTz) pg_prng_int64_range(&prng_state, rmin, rmax);
+
+ PG_RETURN_TIMESTAMPTZ(result);
+}
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 836369f163e..ef0d0f92165 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202509021
+#define CATALOG_VERSION_NO 202509091
#endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 118d6da1ace..03e82d28c87 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3503,6 +3503,18 @@
proname => 'random', provolatile => 'v', proparallel => 'r',
prorettype => 'numeric', proargtypes => 'numeric numeric',
proargnames => '{min,max}', prosrc => 'numeric_random' },
+{ oid => '6431', descr => 'random date in range',
+ proname => 'random', provolatile => 'v', proparallel => 'r',
+ prorettype => 'date', proargtypes => 'date date',
+ proargnames => '{min,max}', prosrc => 'date_random' },
+{ oid => '6432', descr => 'random timestamp in range',
+ proname => 'random', provolatile => 'v', proparallel => 'r',
+ prorettype => 'timestamp', proargtypes => 'timestamp timestamp',
+ proargnames => '{min,max}', prosrc => 'timestamp_random' },
+{ oid => '6433', descr => 'random timestamptz in range',
+ proname => 'random', provolatile => 'v', proparallel => 'r',
+ prorettype => 'timestamptz', proargtypes => 'timestamptz timestamptz',
+ proargnames => '{min,max}', prosrc => 'timestamptz_random' },
{ oid => '1599', descr => 'set random seed',
proname => 'setseed', provolatile => 'v', proparallel => 'r',
prorettype => 'void', proargtypes => 'float8', prosrc => 'setseed' },
diff --git a/src/test/regress/expected/random.out b/src/test/regress/expected/random.out
index 43cf88a3634..7f17b2a1b12 100644
--- a/src/test/regress/expected/random.out
+++ b/src/test/regress/expected/random.out
@@ -536,3 +536,90 @@ SELECT n, random(0, trim_scale(abs(1 - 10.0^(-n)))) FROM generate_series(-20, 20
20 | 0.60795101234744211935
(41 rows)
+-- random dates
+SELECT random('1979-02-08'::date,'2025-07-03'::date) AS random_date_multiple_years;
+ random_date_multiple_years
+----------------------------
+ 04-09-1986
+(1 row)
+
+SELECT random('4714-11-24 BC'::date,'5874897-12-31 AD'::date) AS random_date_maximum_range;
+ random_date_maximum_range
+---------------------------
+ 10-02-2898131
+(1 row)
+
+SELECT random('1979-02-08'::date,'1979-02-08'::date) AS random_date_empty_range;
+ random_date_empty_range
+-------------------------
+ 02-08-1979
+(1 row)
+
+SELECT random('2024-12-31'::date, '2024-01-01'::date); -- fail
+ERROR: lower bound must be less than or equal to upper bound
+SELECT random('-infinity'::date, '2024-01-01'::date); -- fail
+ERROR: lower and upper bounds must be finite
+SELECT random('2024-12-31'::date, 'infinity'::date); -- fail
+ERROR: lower and upper bounds must be finite
+-- random timestamps
+SELECT random('1979-02-08'::timestamp,'2025-07-03'::timestamp) AS random_timestamp_multiple_years;
+ random_timestamp_multiple_years
+---------------------------------
+ Fri Jan 27 18:52:05.366009 2017
+(1 row)
+
+SELECT random('4714-11-24 BC'::timestamp,'294276-12-31 23:59:59.999999'::timestamp) AS random_timestamp_maximum_range;
+ random_timestamp_maximum_range
+-----------------------------------
+ Wed Mar 28 00:45:36.180395 226694
+(1 row)
+
+SELECT random('2024-07-01 12:00:00.000001'::timestamp, '2024-07-01 12:00:00.999999'::timestamp) AS random_narrow_range;
+ random_narrow_range
+---------------------------------
+ Mon Jul 01 12:00:00.999286 2024
+(1 row)
+
+SELECT random('1979-02-08'::timestamp,'1979-02-08'::timestamp) AS random_timestamp_empty_range;
+ random_timestamp_empty_range
+------------------------------
+ Thu Feb 08 00:00:00 1979
+(1 row)
+
+SELECT random('2024-12-31'::timestamp, '2024-01-01'::timestamp); -- fail
+ERROR: lower bound must be less than or equal to upper bound
+SELECT random('-infinity'::timestamp, '2024-01-01'::timestamp); -- fail
+ERROR: lower and upper bounds must be finite
+SELECT random('2024-12-31'::timestamp, 'infinity'::timestamp); -- fail
+ERROR: lower and upper bounds must be finite
+-- random timestamps with timezone
+SELECT random('1979-02-08 +01'::timestamptz,'2025-07-03 +02'::timestamptz) AS random_timestamptz_multiple_years;
+ random_timestamptz_multiple_years
+-------------------------------------
+ Tue Jun 14 04:41:16.652896 2016 PDT
+(1 row)
+
+SELECT random('4714-11-24 BC +00'::timestamptz,'294276-12-31 23:59:59.999999 +00'::timestamptz) AS random_timestamptz_maximum_range;
+ random_timestamptz_maximum_range
+--------------------------------------
+ Wed Mar 26 14:07:16.980265 31603 PDT
+(1 row)
+
+SELECT random('2024-07-01 12:00:00.000001 +04'::timestamptz, '2024-07-01 12:00:00.999999 +04'::timestamptz) AS random_timestamptz_narrow_range;
+ random_timestamptz_narrow_range
+-------------------------------------
+ Mon Jul 01 01:00:00.835808 2024 PDT
+(1 row)
+
+SELECT random('1979-02-08 +05'::timestamptz,'1979-02-08 +05'::timestamptz) AS random_timestamptz_empty_range;
+ random_timestamptz_empty_range
+--------------------------------
+ Wed Feb 07 11:00:00 1979 PST
+(1 row)
+
+SELECT random('2024-01-01 +06'::timestamptz, '2024-01-01 +07'::timestamptz); -- fail
+ERROR: lower bound must be less than or equal to upper bound
+SELECT random('-infinity'::timestamptz, '2024-01-01 +07'::timestamptz); -- fail
+ERROR: lower and upper bounds must be finite
+SELECT random('2024-01-01 +06'::timestamptz, 'infinity'::timestamptz); -- fail
+ERROR: lower and upper bounds must be finite
diff --git a/src/test/regress/sql/random.sql b/src/test/regress/sql/random.sql
index ebfa7539ede..890f14687ef 100644
--- a/src/test/regress/sql/random.sql
+++ b/src/test/regress/sql/random.sql
@@ -277,3 +277,29 @@ SELECT random(-1e30, 1e30) FROM generate_series(1, 10);
SELECT random(-0.4, 0.4) FROM generate_series(1, 10);
SELECT random(0, 1 - 1e-30) FROM generate_series(1, 10);
SELECT n, random(0, trim_scale(abs(1 - 10.0^(-n)))) FROM generate_series(-20, 20) n;
+
+-- random dates
+SELECT random('1979-02-08'::date,'2025-07-03'::date) AS random_date_multiple_years;
+SELECT random('4714-11-24 BC'::date,'5874897-12-31 AD'::date) AS random_date_maximum_range;
+SELECT random('1979-02-08'::date,'1979-02-08'::date) AS random_date_empty_range;
+SELECT random('2024-12-31'::date, '2024-01-01'::date); -- fail
+SELECT random('-infinity'::date, '2024-01-01'::date); -- fail
+SELECT random('2024-12-31'::date, 'infinity'::date); -- fail
+
+-- random timestamps
+SELECT random('1979-02-08'::timestamp,'2025-07-03'::timestamp) AS random_timestamp_multiple_years;
+SELECT random('4714-11-24 BC'::timestamp,'294276-12-31 23:59:59.999999'::timestamp) AS random_timestamp_maximum_range;
+SELECT random('2024-07-01 12:00:00.000001'::timestamp, '2024-07-01 12:00:00.999999'::timestamp) AS random_narrow_range;
+SELECT random('1979-02-08'::timestamp,'1979-02-08'::timestamp) AS random_timestamp_empty_range;
+SELECT random('2024-12-31'::timestamp, '2024-01-01'::timestamp); -- fail
+SELECT random('-infinity'::timestamp, '2024-01-01'::timestamp); -- fail
+SELECT random('2024-12-31'::timestamp, 'infinity'::timestamp); -- fail
+
+-- random timestamps with timezone
+SELECT random('1979-02-08 +01'::timestamptz,'2025-07-03 +02'::timestamptz) AS random_timestamptz_multiple_years;
+SELECT random('4714-11-24 BC +00'::timestamptz,'294276-12-31 23:59:59.999999 +00'::timestamptz) AS random_timestamptz_maximum_range;
+SELECT random('2024-07-01 12:00:00.000001 +04'::timestamptz, '2024-07-01 12:00:00.999999 +04'::timestamptz) AS random_timestamptz_narrow_range;
+SELECT random('1979-02-08 +05'::timestamptz,'1979-02-08 +05'::timestamptz) AS random_timestamptz_empty_range;
+SELECT random('2024-01-01 +06'::timestamptz, '2024-01-01 +07'::timestamptz); -- fail
+SELECT random('-infinity'::timestamptz, '2024-01-01 +07'::timestamptz); -- fail
+SELECT random('2024-01-01 +06'::timestamptz, 'infinity'::timestamptz); -- fail
generated by cgit v1.2.3 (git 2.46.0) at 2025-09-12 11:14:11 +0000