random_normal_02.patch

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e57ffce971..c467e73c2f 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -1815,6 +1815,27 @@ repeat('Pg', 4) <returnvalue>PgPgPgPg</returnvalue>
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>random_normal</primary>
+        </indexterm>
+
+         <function>random_normal</function> (
+         <optional> <parameter>mean</parameter> <type>double precision</type>
+         <optional>, <parameter>stddev</parameter> <type>double precision</type></optional></optional> )
+         <returnvalue>double precision</returnvalue>
+       </para>
+       <para>
+        Returns a random value from a normal distribution, with default
+        <literal>mean</literal> of 0.0 and default <literal>stddev</literal> of 1.0.
+       </para>
+       <para>
+        <literal>random_normal(0.0, 1.0)</literal>
+        <returnvalue>0.051285419</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -1824,7 +1845,8 @@ repeat('Pg', 4) <returnvalue>PgPgPgPg</returnvalue>
         <returnvalue>void</returnvalue>
        </para>
        <para>
-        Sets the seed for subsequent <literal>random()</literal> calls;
+        Sets the seed for subsequent <literal>random()</literal> and
+        <literal>random_normal()</literal> calls;
         argument must be between -1.0 and 1.0, inclusive
        </para>
        <para>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 52517a6531..c800fdea90 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -620,6 +620,13 @@ CREATE OR REPLACE FUNCTION
  STABLE PARALLEL SAFE
  AS 'sql_localtimestamp';
 
+CREATE OR REPLACE FUNCTION
+  random_normal(mean float8 DEFAULT 0.0, stddev float8 DEFAULT 1.0)
+RETURNS float8
+LANGUAGE INTERNAL
+STRICT VOLATILE PARALLEL SAFE
+AS 'drandom_normal';
+
 --
 -- The default permissions for functions mean that anyone can execute them.
 -- A number of functions shouldn't be executable by just anyone, but rather
diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c
index da97538ebe..bf72825a54 100644
--- a/src/backend/utils/adt/float.c
+++ b/src/backend/utils/adt/float.c
@@ -2743,14 +2743,9 @@ datanh(PG_FUNCTION_ARGS)
 }
 
 
-/*
- *		drandom		- returns a random number
- */
-Datum
-drandom(PG_FUNCTION_ARGS)
+static void
+drandom_check_default_seed()
 {
-	float8		result;
-
 	/* Initialize random seed, if not done yet in this process */
 	if (unlikely(!drandom_seed_set))
 	{
@@ -2770,6 +2765,17 @@ drandom(PG_FUNCTION_ARGS)
 		}
 		drandom_seed_set = true;
 	}
+}
+
+/*
+ *		drandom		- returns a random number
+ */
+Datum
+drandom(PG_FUNCTION_ARGS)
+{
+	float8		result;
+
+	drandom_check_default_seed();
 
 	/* pg_prng_double produces desired result range [0.0 - 1.0) */
 	result = pg_prng_double(&drandom_seed);
@@ -2777,6 +2783,33 @@ drandom(PG_FUNCTION_ARGS)
 	PG_RETURN_FLOAT8(result);
 }
 
+/*
+ *		random_normal		- returns a random number from the a normal distribution
+ *
+ */
+Datum
+drandom_normal(PG_FUNCTION_ARGS)
+{
+	float8		result;
+	float8		mean = 0.0;
+	float8		stddev = 1.0;
+
+	/* Read optional stddev */
+	if (PG_NARGS() >= 2)
+		stddev = PG_GETARG_FLOAT8(1);
+
+	/* Read optional mean */
+	if (PG_NARGS() >= 1)
+		mean = PG_GETARG_FLOAT8(0);
+
+	drandom_check_default_seed();
+
+	/* Get random value from normal(mean = 0.0, stddev = 1.0) */
+	result = pg_prng_double_normal(&drandom_seed);
+	result = (stddev * result) + mean;
+
+	PG_RETURN_FLOAT8(result);
+}
 
 /*
  *		setseed		- set seed for the random number generator
diff --git a/src/common/pg_prng.c b/src/common/pg_prng.c
index 3d2f42724e..bebdae1ca0 100644
--- a/src/common/pg_prng.c
+++ b/src/common/pg_prng.c
@@ -20,6 +20,7 @@
 #include "c.h"
 
 #include <math.h>				/* for ldexp() */
+#include <float.h>				/* for DBL_EPSILON */
 
 #include "common/pg_prng.h"
 #include "port/pg_bitutils.h"
@@ -245,3 +246,30 @@ pg_prng_bool(pg_prng_state *state)
 
 	return (bool) (v >> 63);
 }
+
+
+/*
+ * Select a random double from the normal distribution with
+ * mean = 0.0 and stddev = 1.0.
+ *
+ * To get a result for a different normal distribution use
+ *   STDDEV * pg_prng_double_normal + MEAN
+ *
+ * Using https://en.wikipedia.org/wiki/Box%E2%80%93Muller_transform
+ */
+double
+pg_prng_double_normal(pg_prng_state *state)
+{
+	double u1, u2, z0;
+	/* Ensure u1 is at least as big as epsilon */
+	do
+	{
+		u1 = pg_prng_double(state);
+	}
+	while (u1 <= DBL_EPSILON);
+	u2 = pg_prng_double(state);
+
+	/* Apply Box-Muller calculation for one normal-valued output */
+	z0 = sqrt(-2.0 * log(u1)) * cos(2.0 * M_PI * u2);
+	return z0;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index f9301b2627..f1633d476c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3362,6 +3362,10 @@
 { oid => '1599', descr => 'set random seed',
   proname => 'setseed', provolatile => 'v', proparallel => 'r',
   prorettype => 'void', proargtypes => 'float8', prosrc => 'setseed' },
+{ oid => '5151', descr => 'random value from normal distribution',
+  proname => 'random_normal', provolatile => 'v', proparallel => 'r',
+  prorettype => 'float8', proargtypes => 'float8 float8',
+  proargnames => '{mean,stddev}', prosrc => 'drandom_normal' },
 
 # OIDS 1600 - 1699
 
diff --git a/src/include/common/pg_prng.h b/src/include/common/pg_prng.h
index d9895b495c..5b3ef7cd83 100644
--- a/src/include/common/pg_prng.h
+++ b/src/include/common/pg_prng.h
@@ -55,6 +55,7 @@ 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);
 extern double pg_prng_double(pg_prng_state *state);
+extern double pg_prng_double_normal(pg_prng_state *state);
 extern bool pg_prng_bool(pg_prng_state *state);
 
 #endif							/* PG_PRNG_H */