Implement 4 new aggregate functions from SQL2003. Specifically: var_pop(),

var_samp(), stddev_pop(), and stddev_samp(). var_samp() and stddev_samp()
are just renamings of the historical Postgres aggregates variance() and
stddev() -- the latter names have been kept for backward compatibility.
This patch includes updates for the documentation and regression tests.
The catversion has been bumped.

NB: SQL2003 requires that DISTINCT not be specified for any of these
aggregates. Per discussion on -patches, I have NOT implemented this
restriction: if the user asks for stddev(DISTINCT x), presumably they
know what they are doing.

8 files changed, 331 insertions, 126 deletions

diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c
index 0a906854f58..29a2124e5f3 100644
--- a/src/backend/utils/adt/float.c
+++ b/src/backend/utils/adt/float.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/adt/float.c,v 1.121 2006/03/05 15:58:41 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/adt/float.c,v 1.122 2006/03/10 20:15:25 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1861,11 +1861,13 @@ setseed(PG_FUNCTION_ARGS)
  *		FLOAT AGGREGATE OPERATORS
  *		=========================
  *
- *		float8_accum	- accumulate for AVG(), STDDEV(), etc
- *		float4_accum	- same, but input data is float4
- *		float8_avg		- produce final result for float AVG()
- *		float8_variance - produce final result for float VARIANCE()
- *		float8_stddev	- produce final result for float STDDEV()
+ *		float8_accum		- accumulate for AVG(), variance aggregates, etc.
+ *		float4_accum		- same, but input data is float4
+ *		float8_avg			- produce final result for float AVG()
+ *		float8_var_samp		- produce final result for float VAR_SAMP()
+ *		float8_var_pop		- produce final result for float VAR_POP()
+ *		float8_stddev_samp	- produce final result for float STDDEV_SAMP()
+ *		float8_stddev_pop	- produce final result for float STDDEV_POP()
  *
  * The transition datatype for all these aggregates is a 3-element array
  * of float8, holding the values N, sum(X), sum(X*X) in that order.
@@ -2015,7 +2017,7 @@ float8_avg(PG_FUNCTION_ARGS)
 }
 
 Datum
-float8_variance(PG_FUNCTION_ARGS)
+float8_var_pop(PG_FUNCTION_ARGS)
 {
 	ArrayType  *transarray = PG_GETARG_ARRAYTYPE_P(0);
 	float8	   *transvalues;
@@ -2024,7 +2026,35 @@ float8_variance(PG_FUNCTION_ARGS)
 				sumX2,
 				numerator;
 
-	transvalues = check_float8_array(transarray, "float8_variance");
+	transvalues = check_float8_array(transarray, "float8_var_pop");
+	N = transvalues[0];
+	sumX = transvalues[1];
+	sumX2 = transvalues[2];
+
+	/* Population variance is undefined when N is 0, so return NULL */
+	if (N == 0.0)
+		PG_RETURN_NULL();
+
+	numerator = N * sumX2 - sumX * sumX;
+
+	/* Watch out for roundoff error producing a negative numerator */
+	if (numerator <= 0.0)
+		PG_RETURN_FLOAT8(0.0);
+
+	PG_RETURN_FLOAT8(numerator / (N * N));
+}
+
+Datum
+float8_var_samp(PG_FUNCTION_ARGS)
+{
+	ArrayType  *transarray = PG_GETARG_ARRAYTYPE_P(0);
+	float8	   *transvalues;
+	float8		N,
+				sumX,
+				sumX2,
+				numerator;
+
+	transvalues = check_float8_array(transarray, "float8_var_samp");
 	N = transvalues[0];
 	sumX = transvalues[1];
 	sumX2 = transvalues[2];
@@ -2043,7 +2073,35 @@ float8_variance(PG_FUNCTION_ARGS)
 }
 
 Datum
-float8_stddev(PG_FUNCTION_ARGS)
+float8_stddev_pop(PG_FUNCTION_ARGS)
+{
+	ArrayType  *transarray = PG_GETARG_ARRAYTYPE_P(0);
+	float8	   *transvalues;
+	float8		N,
+				sumX,
+				sumX2,
+				numerator;
+
+	transvalues = check_float8_array(transarray, "float8_stddev_pop");
+	N = transvalues[0];
+	sumX = transvalues[1];
+	sumX2 = transvalues[2];
+
+	/* Population stddev is undefined when N is 0, so return NULL */
+	if (N == 0.0)
+		PG_RETURN_NULL();
+
+	numerator = N * sumX2 - sumX * sumX;
+
+	/* Watch out for roundoff error producing a negative numerator */
+	if (numerator <= 0.0)
+		PG_RETURN_FLOAT8(0.0);
+
+	PG_RETURN_FLOAT8(sqrt(numerator / (N * N)));
+}
+
+Datum
+float8_stddev_samp(PG_FUNCTION_ARGS)
 {
 	ArrayType  *transarray = PG_GETARG_ARRAYTYPE_P(0);
 	float8	   *transvalues;
@@ -2052,7 +2110,7 @@ float8_stddev(PG_FUNCTION_ARGS)
 				sumX2,
 				numerator;
 
-	transvalues = check_float8_array(transarray, "float8_stddev");
+	transvalues = check_float8_array(transarray, "float8_stddev_samp");
 	N = transvalues[0];
 	sumX = transvalues[1];
 	sumX2 = transvalues[2];
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index c3f498fb8ca..1407ad86629 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -14,7 +14,7 @@
  * Copyright (c) 1998-2006, PostgreSQL Global Development Group
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/adt/numeric.c,v 1.92 2006/03/05 15:58:43 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/adt/numeric.c,v 1.93 2006/03/10 20:15:26 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -2181,10 +2181,22 @@ numeric_avg(PG_FUNCTION_ARGS)
 										NumericGetDatum(N)));
 }
 
-Datum
-numeric_variance(PG_FUNCTION_ARGS)
+/*
+ * Workhorse routine for the standard deviance and variance
+ * aggregates. 'transarray' is the aggregate's transition
+ * array. 'variance' specifies whether we should calculate the
+ * variance or the standard deviation. 'sample' indicates whether the
+ * caller is interested in the sample or the population
+ * variance/stddev.
+ *
+ * If appropriate variance statistic is undefined for the input,
+ * *is_null is set to true and NULL is returned.
+ */
+static Numeric
+numeric_stddev_internal(ArrayType *transarray,
+						bool variance, bool sample,
+						bool *is_null)
 {
-	ArrayType  *transarray = PG_GETARG_ARRAYTYPE_P(0);
 	Datum	   *transdatums;
 	int			ndatums;
 	Numeric		N,
@@ -2195,8 +2207,11 @@ numeric_variance(PG_FUNCTION_ARGS)
 				vsumX,
 				vsumX2,
 				vNminus1;
+	NumericVar *comp;
 	int			rscale;
 
+	*is_null = false;
+
 	/* We assume the input is array of numeric */
 	deconstruct_array(transarray,
 					  NUMERICOID, -1, false, 'i',
@@ -2208,16 +2223,26 @@ numeric_variance(PG_FUNCTION_ARGS)
 	sumX2 = DatumGetNumeric(transdatums[2]);
 
 	if (NUMERIC_IS_NAN(N) || NUMERIC_IS_NAN(sumX) || NUMERIC_IS_NAN(sumX2))
-		PG_RETURN_NUMERIC(make_result(&const_nan));
+		return make_result(&const_nan);
 
-	/* Sample variance is undefined when N is 0 or 1, so return NULL */
 	init_var(&vN);
 	set_var_from_num(N, &vN);
 
-	if (cmp_var(&vN, &const_one) <= 0)
+	/*
+	 * Sample stddev and variance are undefined when N <= 1;
+	 * population stddev is undefined when N == 0. Return NULL in
+	 * either case.
+	 */
+	if (sample)
+		comp = &const_one;
+	else
+		comp = &const_zero;
+
+	if (cmp_var(&vN, comp) <= 0)
 	{
 		free_var(&vN);
-		PG_RETURN_NULL();
+		*is_null = true;
+		return NULL;
 	}
 
 	init_var(&vNminus1);
@@ -2233,7 +2258,7 @@ numeric_variance(PG_FUNCTION_ARGS)
 
 	mul_var(&vsumX, &vsumX, &vsumX, rscale);	/* vsumX = sumX * sumX */
 	mul_var(&vN, &vsumX2, &vsumX2, rscale);		/* vsumX2 = N * sumX2 */
-	sub_var(&vsumX2, &vsumX, &vsumX2);	/* N * sumX2 - sumX * sumX */
+	sub_var(&vsumX2, &vsumX, &vsumX2);			/* N * sumX2 - sumX * sumX */
 
 	if (cmp_var(&vsumX2, &const_zero) <= 0)
 	{
@@ -2242,9 +2267,11 @@ numeric_variance(PG_FUNCTION_ARGS)
 	}
 	else
 	{
-		mul_var(&vN, &vNminus1, &vNminus1, 0);	/* N * (N - 1) */
+		mul_var(&vN, &vNminus1, &vNminus1, 0);				/* N * (N - 1) */
 		rscale = select_div_scale(&vsumX2, &vNminus1);
-		div_var(&vsumX2, &vNminus1, &vsumX, rscale, true);		/* variance */
+		div_var(&vsumX2, &vNminus1, &vsumX, rscale, true);	/* variance */
+		if (!variance)
+			sqrt_var(&vsumX, &vsumX, rscale);				/* stddev */
 
 		res = make_result(&vsumX);
 	}
@@ -2254,87 +2281,69 @@ numeric_variance(PG_FUNCTION_ARGS)
 	free_var(&vsumX);
 	free_var(&vsumX2);
 
-	PG_RETURN_NUMERIC(res);
+	return res;
 }
 
 Datum
-numeric_stddev(PG_FUNCTION_ARGS)
+numeric_var_samp(PG_FUNCTION_ARGS)
 {
-	ArrayType  *transarray = PG_GETARG_ARRAYTYPE_P(0);
-	Datum	   *transdatums;
-	int			ndatums;
-	Numeric		N,
-				sumX,
-				sumX2,
-				res;
-	NumericVar	vN,
-				vsumX,
-				vsumX2,
-				vNminus1;
-	int			rscale;
-
-	/* We assume the input is array of numeric */
-	deconstruct_array(transarray,
-					  NUMERICOID, -1, false, 'i',
-					  &transdatums, NULL, &ndatums);
-	if (ndatums != 3)
-		elog(ERROR, "expected 3-element numeric array");
-	N = DatumGetNumeric(transdatums[0]);
-	sumX = DatumGetNumeric(transdatums[1]);
-	sumX2 = DatumGetNumeric(transdatums[2]);
+	Numeric res;
+	bool is_null;
 
-	if (NUMERIC_IS_NAN(N) || NUMERIC_IS_NAN(sumX) || NUMERIC_IS_NAN(sumX2))
-		PG_RETURN_NUMERIC(make_result(&const_nan));
+	res = numeric_stddev_internal(PG_GETARG_ARRAYTYPE_P(0),
+								  true, true, &is_null);
 
-	/* Sample stddev is undefined when N is 0 or 1, so return NULL */
-	init_var(&vN);
-	set_var_from_num(N, &vN);
-
-	if (cmp_var(&vN, &const_one) <= 0)
-	{
-		free_var(&vN);
+	if (is_null)
 		PG_RETURN_NULL();
-	}
+	else
+		PG_RETURN_NUMERIC(res);
+}
 
-	init_var(&vNminus1);
-	sub_var(&vN, &const_one, &vNminus1);
+Datum
+numeric_stddev_samp(PG_FUNCTION_ARGS)
+{
+	Numeric res;
+	bool is_null;
 
-	init_var(&vsumX);
-	set_var_from_num(sumX, &vsumX);
-	init_var(&vsumX2);
-	set_var_from_num(sumX2, &vsumX2);
+	res = numeric_stddev_internal(PG_GETARG_ARRAYTYPE_P(0),
+								  false, true, &is_null);
 
-	/* compute rscale for mul_var calls */
-	rscale = vsumX.dscale * 2;
+	if (is_null)
+		PG_RETURN_NULL();
+	else
+		PG_RETURN_NUMERIC(res);
+}
 
-	mul_var(&vsumX, &vsumX, &vsumX, rscale);	/* vsumX = sumX * sumX */
-	mul_var(&vN, &vsumX2, &vsumX2, rscale);		/* vsumX2 = N * sumX2 */
-	sub_var(&vsumX2, &vsumX, &vsumX2);	/* N * sumX2 - sumX * sumX */
+Datum
+numeric_var_pop(PG_FUNCTION_ARGS)
+{
+	Numeric res;
+	bool is_null;
 
-	if (cmp_var(&vsumX2, &const_zero) <= 0)
-	{
-		/* Watch out for roundoff error producing a negative numerator */
-		res = make_result(&const_zero);
-	}
+	res = numeric_stddev_internal(PG_GETARG_ARRAYTYPE_P(0),
+								  true, false, &is_null);
+
+	if (is_null)
+		PG_RETURN_NULL();
 	else
-	{
-		mul_var(&vN, &vNminus1, &vNminus1, 0);	/* N * (N - 1) */
-		rscale = select_div_scale(&vsumX2, &vNminus1);
-		div_var(&vsumX2, &vNminus1, &vsumX, rscale, true);		/* variance */
-		sqrt_var(&vsumX, &vsumX, rscale);		/* stddev */
+		PG_RETURN_NUMERIC(res);
+}
 
-		res = make_result(&vsumX);
-	}
+Datum
+numeric_stddev_pop(PG_FUNCTION_ARGS)
+{
+	Numeric res;
+	bool is_null;
 
-	free_var(&vN);
-	free_var(&vNminus1);
-	free_var(&vsumX);
-	free_var(&vsumX2);
+	res = numeric_stddev_internal(PG_GETARG_ARRAYTYPE_P(0),
+								  false, false, &is_null);
 
-	PG_RETURN_NUMERIC(res);
+	if (is_null)
+		PG_RETURN_NULL();
+	else
+		PG_RETURN_NUMERIC(res);
 }
 
-
 /*
  * SUM transition functions for integer datatypes.
  *
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 978e35af8e0..4b541531f84 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -37,7 +37,7 @@
  * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.319 2006/03/06 22:49:16 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.320 2006/03/10 20:15:26 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	200603061
+#define CATALOG_VERSION_NO	200603101
 
 #endif
diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h
index 2585e41ee5d..9f3c1ed9c34 100644
--- a/src/include/catalog/pg_aggregate.h
+++ b/src/include/catalog/pg_aggregate.h
@@ -8,7 +8,7 @@
  * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/pg_aggregate.h,v 1.53 2006/03/05 15:58:54 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/pg_aggregate.h,v 1.54 2006/03/10 20:15:26 neilc Exp $
  *
  * NOTES
  *	  the genbki.sh script reads this file and generates .bki
@@ -144,21 +144,53 @@ DATA(insert ( 2245	bpchar_smaller	-				1058	1042	_null_ ));
  */
 DATA(insert ( 2147	int8inc		-					0	20		0 ));
 
-/* variance */
-DATA(insert ( 2148	int8_accum	numeric_variance	0	1231	"{0,0,0}" ));
-DATA(insert ( 2149	int4_accum	numeric_variance	0	1231	"{0,0,0}" ));
-DATA(insert ( 2150	int2_accum	numeric_variance	0	1231	"{0,0,0}" ));
-DATA(insert ( 2151	float4_accum	float8_variance 0	1022	"{0,0,0}" ));
-DATA(insert ( 2152	float8_accum	float8_variance 0	1022	"{0,0,0}" ));
-DATA(insert ( 2153	numeric_accum  numeric_variance 0	1231	"{0,0,0}" ));
-
-/* stddev */
-DATA(insert ( 2154	int8_accum	numeric_stddev		0	1231	"{0,0,0}" ));
-DATA(insert ( 2155	int4_accum	numeric_stddev		0	1231	"{0,0,0}" ));
-DATA(insert ( 2156	int2_accum	numeric_stddev		0	1231	"{0,0,0}" ));
-DATA(insert ( 2157	float4_accum	float8_stddev	0	1022	"{0,0,0}" ));
-DATA(insert ( 2158	float8_accum	float8_stddev	0	1022	"{0,0,0}" ));
-DATA(insert ( 2159	numeric_accum	numeric_stddev	0	1231	"{0,0,0}" ));
+/* var_pop */
+DATA(insert ( 2718	int8_accum	numeric_var_pop	0	1231	"{0,0,0}" ));
+DATA(insert ( 2719	int4_accum	numeric_var_pop	0	1231	"{0,0,0}" ));
+DATA(insert ( 2720	int2_accum	numeric_var_pop	0	1231	"{0,0,0}" ));
+DATA(insert ( 2721	float4_accum	float8_var_pop 0	1022	"{0,0,0}" ));
+DATA(insert ( 2722	float8_accum	float8_var_pop 0	1022	"{0,0,0}" ));
+DATA(insert ( 2723	numeric_accum  numeric_var_pop 0	1231	"{0,0,0}" ));
+
+/* var_samp */
+DATA(insert ( 2641	int8_accum	numeric_var_samp	0	1231	"{0,0,0}" ));
+DATA(insert ( 2642	int4_accum	numeric_var_samp	0	1231	"{0,0,0}" ));
+DATA(insert ( 2643	int2_accum	numeric_var_samp	0	1231	"{0,0,0}" ));
+DATA(insert ( 2644	float4_accum	float8_var_samp 0	1022	"{0,0,0}" ));
+DATA(insert ( 2645	float8_accum	float8_var_samp 0	1022	"{0,0,0}" ));
+DATA(insert ( 2646	numeric_accum  numeric_var_samp 0	1231	"{0,0,0}" ));
+
+/* variance: historical Postgres syntax for var_samp */
+DATA(insert ( 2148	int8_accum	numeric_var_samp	0	1231	"{0,0,0}" ));
+DATA(insert ( 2149	int4_accum	numeric_var_samp	0	1231	"{0,0,0}" ));
+DATA(insert ( 2150	int2_accum	numeric_var_samp	0	1231	"{0,0,0}" ));
+DATA(insert ( 2151	float4_accum	float8_var_samp 0	1022	"{0,0,0}" ));
+DATA(insert ( 2152	float8_accum	float8_var_samp 0	1022	"{0,0,0}" ));
+DATA(insert ( 2153	numeric_accum  numeric_var_samp 0	1231	"{0,0,0}" ));
+
+/* stddev_pop */
+DATA(insert ( 2724	int8_accum	numeric_stddev_pop		0	1231	"{0,0,0}" ));
+DATA(insert ( 2725	int4_accum	numeric_stddev_pop		0	1231	"{0,0,0}" ));
+DATA(insert ( 2726	int2_accum	numeric_stddev_pop		0	1231	"{0,0,0}" ));
+DATA(insert ( 2727	float4_accum	float8_stddev_pop	0	1022	"{0,0,0}" ));
+DATA(insert ( 2728	float8_accum	float8_stddev_pop	0	1022	"{0,0,0}" ));
+DATA(insert ( 2729	numeric_accum	numeric_stddev_pop	0	1231	"{0,0,0}" ));
+
+/* stddev_samp */
+DATA(insert ( 2712	int8_accum	numeric_stddev_samp		0	1231	"{0,0,0}" ));
+DATA(insert ( 2713	int4_accum	numeric_stddev_samp		0	1231	"{0,0,0}" ));
+DATA(insert ( 2714	int2_accum	numeric_stddev_samp		0	1231	"{0,0,0}" ));
+DATA(insert ( 2715	float4_accum	float8_stddev_samp	0	1022	"{0,0,0}" ));
+DATA(insert ( 2716	float8_accum	float8_stddev_samp	0	1022	"{0,0,0}" ));
+DATA(insert ( 2717	numeric_accum	numeric_stddev_samp	0	1231	"{0,0,0}" ));
+
+/* stddev: historical Postgres syntax for stddev_samp */
+DATA(insert ( 2154	int8_accum	numeric_stddev_samp		0	1231	"{0,0,0}" ));
+DATA(insert ( 2155	int4_accum	numeric_stddev_samp		0	1231	"{0,0,0}" ));
+DATA(insert ( 2156	int2_accum	numeric_stddev_samp		0	1231	"{0,0,0}" ));
+DATA(insert ( 2157	float4_accum	float8_stddev_samp	0	1022	"{0,0,0}" ));
+DATA(insert ( 2158	float8_accum	float8_stddev_samp	0	1022	"{0,0,0}" ));
+DATA(insert ( 2159	numeric_accum	numeric_stddev_samp	0	1231	"{0,0,0}" ));
 
 /* boolean-and and boolean-or */
 DATA(insert ( 2517	booland_statefunc	-			0	16		_null_ ));
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 45ac07493db..d8fda0b8c3a 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.403 2006/03/10 19:12:51 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.404 2006/03/10 20:15:26 neilc Exp $
  *
  * NOTES
  *	  The script catalog/genbki.sh reads this file and generates .bki
@@ -2668,10 +2668,14 @@ DESCR("join selectivity of case-insensitive regex non-match");
 /* Aggregate-related functions */
 DATA(insert OID = 1830 (  float8_avg	   PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_avg - _null_ ));
 DESCR("AVG aggregate final function");
-DATA(insert OID = 1831 (  float8_variance  PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_variance - _null_ ));
-DESCR("VARIANCE aggregate final function");
-DATA(insert OID = 1832 (  float8_stddev    PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_stddev - _null_ ));
-DESCR("STDDEV aggregate final function");
+DATA(insert OID = 2512 (  float8_var_pop   PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_var_pop - _null_ ));
+DESCR("VAR_POP aggregate final function");
+DATA(insert OID = 1831 (  float8_var_samp  PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_var_samp - _null_ ));
+DESCR("VAR_SAMP aggregate final function");
+DATA(insert OID = 2513 (  float8_stddev_pop PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_stddev_pop - _null_ ));
+DESCR("STDDEV_POP aggregate final function");
+DATA(insert OID = 1832 (  float8_stddev_samp    PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_stddev_samp - _null_ ));
+DESCR("STDDEV_SAMP aggregate final function");
 DATA(insert OID = 1833 (  numeric_accum    PGNSP PGUID 12 f f t f i 2 1231 "1231 1700" _null_ _null_ _null_ numeric_accum - _null_ ));
 DESCR("aggregate transition function");
 DATA(insert OID = 1834 (  int2_accum	   PGNSP PGUID 12 f f t f i 2 1231 "1231 21" _null_ _null_ _null_ int2_accum - _null_ ));
@@ -2682,10 +2686,14 @@ DATA(insert OID = 1836 (  int8_accum	   PGNSP PGUID 12 f f t f i 2 1231 "1231 20
 DESCR("aggregate transition function");
 DATA(insert OID = 1837 (  numeric_avg	   PGNSP PGUID 12 f f t f i 1 1700 "1231" _null_ _null_ _null_	numeric_avg - _null_ ));
 DESCR("AVG aggregate final function");
-DATA(insert OID = 1838 (  numeric_variance PGNSP PGUID 12 f f t f i 1 1700 "1231" _null_ _null_ _null_	numeric_variance - _null_ ));
-DESCR("VARIANCE aggregate final function");
-DATA(insert OID = 1839 (  numeric_stddev   PGNSP PGUID 12 f f t f i 1 1700 "1231" _null_ _null_ _null_	numeric_stddev - _null_ ));
-DESCR("STDDEV aggregate final function");
+DATA(insert OID = 2514 (  numeric_var_pop  PGNSP PGUID 12 f f t f i 1 1700 "1231" _null_ _null_ _null_	numeric_var_pop - _null_ ));
+DESCR("VAR_POP aggregate final function");
+DATA(insert OID = 1838 (  numeric_var_samp PGNSP PGUID 12 f f t f i 1 1700 "1231" _null_ _null_ _null_	numeric_var_samp - _null_ ));
+DESCR("VAR_SAMP aggregate final function");
+DATA(insert OID = 2596 (  numeric_stddev_pop PGNSP PGUID 12 f f t f i 1 1700 "1231" _null_ _null_ _null_	numeric_stddev_pop - _null_ ));
+DESCR("STDDEV_POP aggregate final function");
+DATA(insert OID = 1839 (  numeric_stddev_samp   PGNSP PGUID 12 f f t f i 1 1700 "1231" _null_ _null_ _null_	numeric_stddev_samp - _null_ ));
+DESCR("STDDEV_SAMP aggregate final function");
 DATA(insert OID = 1840 (  int2_sum		   PGNSP PGUID 12 f f f f i 2 20 "20 21" _null_ _null_ _null_ int2_sum - _null_ ));
 DESCR("SUM(int2) transition function");
 DATA(insert OID = 1841 (  int4_sum		   PGNSP PGUID 12 f f f f i 2 20 "20 23" _null_ _null_ _null_ int4_sum - _null_ ));
@@ -3115,6 +3123,20 @@ DATA(insert OID = 2245 (  min				PGNSP PGUID 12 t f f f i 1 1042 "1042" _null_ _
 
 DATA(insert OID = 2147 (  count				PGNSP PGUID 12 t f f f i 1 20 "2276" _null_ _null_ _null_  aggregate_dummy - _null_ ));
 
+DATA(insert OID = 2718 (  var_pop			PGNSP PGUID 12 t f f f i 1 1700 "20" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2719 (  var_pop			PGNSP PGUID 12 t f f f i 1 1700 "23" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2720 (  var_pop			PGNSP PGUID 12 t f f f i 1 1700 "21" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2721 (  var_pop			PGNSP PGUID 12 t f f f i 1 701 "700" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2722 (  var_pop			PGNSP PGUID 12 t f f f i 1 701 "701" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2723 (  var_pop			PGNSP PGUID 12 t f f f i 1 1700 "1700" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+
+DATA(insert OID = 2641 (  var_samp			PGNSP PGUID 12 t f f f i 1 1700 "20" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2642 (  var_samp			PGNSP PGUID 12 t f f f i 1 1700 "23" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2643 (  var_samp			PGNSP PGUID 12 t f f f i 1 1700 "21" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2644 (  var_samp			PGNSP PGUID 12 t f f f i 1 701 "700" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2645 (  var_samp			PGNSP PGUID 12 t f f f i 1 701 "701" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2646 (  var_samp			PGNSP PGUID 12 t f f f i 1 1700 "1700" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+
 DATA(insert OID = 2148 (  variance			PGNSP PGUID 12 t f f f i 1 1700 "20" _null_ _null_ _null_  aggregate_dummy - _null_ ));
 DATA(insert OID = 2149 (  variance			PGNSP PGUID 12 t f f f i 1 1700 "23" _null_ _null_ _null_  aggregate_dummy - _null_ ));
 DATA(insert OID = 2150 (  variance			PGNSP PGUID 12 t f f f i 1 1700 "21" _null_ _null_ _null_  aggregate_dummy - _null_ ));
@@ -3122,6 +3144,20 @@ DATA(insert OID = 2151 (  variance			PGNSP PGUID 12 t f f f i 1 701 "700" _null_
 DATA(insert OID = 2152 (  variance			PGNSP PGUID 12 t f f f i 1 701 "701" _null_ _null_ _null_  aggregate_dummy - _null_ ));
 DATA(insert OID = 2153 (  variance			PGNSP PGUID 12 t f f f i 1 1700 "1700" _null_ _null_ _null_ aggregate_dummy - _null_ ));
 
+DATA(insert OID = 2724 (  stddev_pop		PGNSP PGUID 12 t f f f i 1 1700 "20" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2725 (  stddev_pop		PGNSP PGUID 12 t f f f i 1 1700 "23" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2726 (  stddev_pop		PGNSP PGUID 12 t f f f i 1 1700 "21" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2727 (  stddev_pop		PGNSP PGUID 12 t f f f i 1 701 "700" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2728 (  stddev_pop		PGNSP PGUID 12 t f f f i 1 701 "701" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2729 (  stddev_pop		PGNSP PGUID 12 t f f f i 1 1700 "1700" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+
+DATA(insert OID = 2712 (  stddev_samp		PGNSP PGUID 12 t f f f i 1 1700 "20" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2713 (  stddev_samp		PGNSP PGUID 12 t f f f i 1 1700 "23" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2714 (  stddev_samp		PGNSP PGUID 12 t f f f i 1 1700 "21" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2715 (  stddev_samp		PGNSP PGUID 12 t f f f i 1 701 "700" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2716 (  stddev_samp		PGNSP PGUID 12 t f f f i 1 701 "701" _null_ _null_ _null_  aggregate_dummy - _null_ ));
+DATA(insert OID = 2717 (  stddev_samp		PGNSP PGUID 12 t f f f i 1 1700 "1700" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+
 DATA(insert OID = 2154 (  stddev			PGNSP PGUID 12 t f f f i 1 1700 "20" _null_ _null_ _null_  aggregate_dummy - _null_ ));
 DATA(insert OID = 2155 (  stddev			PGNSP PGUID 12 t f f f i 1 1700 "23" _null_ _null_ _null_  aggregate_dummy - _null_ ));
 DATA(insert OID = 2156 (  stddev			PGNSP PGUID 12 t f f f i 1 1700 "21" _null_ _null_ _null_  aggregate_dummy - _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 9de735d770d..3c484169740 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.276 2006/03/05 15:59:06 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.277 2006/03/10 20:15:27 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -340,8 +340,10 @@ extern Datum setseed(PG_FUNCTION_ARGS);
 extern Datum float8_accum(PG_FUNCTION_ARGS);
 extern Datum float4_accum(PG_FUNCTION_ARGS);
 extern Datum float8_avg(PG_FUNCTION_ARGS);
-extern Datum float8_variance(PG_FUNCTION_ARGS);
-extern Datum float8_stddev(PG_FUNCTION_ARGS);
+extern Datum float8_var_pop(PG_FUNCTION_ARGS);
+extern Datum float8_var_samp(PG_FUNCTION_ARGS);
+extern Datum float8_stddev_pop(PG_FUNCTION_ARGS);
+extern Datum float8_stddev_samp(PG_FUNCTION_ARGS);
 extern Datum float48pl(PG_FUNCTION_ARGS);
 extern Datum float48mi(PG_FUNCTION_ARGS);
 extern Datum float48mul(PG_FUNCTION_ARGS);
@@ -814,8 +816,10 @@ extern Datum int2_accum(PG_FUNCTION_ARGS);
 extern Datum int4_accum(PG_FUNCTION_ARGS);
 extern Datum int8_accum(PG_FUNCTION_ARGS);
 extern Datum numeric_avg(PG_FUNCTION_ARGS);
-extern Datum numeric_variance(PG_FUNCTION_ARGS);
-extern Datum numeric_stddev(PG_FUNCTION_ARGS);
+extern Datum numeric_var_pop(PG_FUNCTION_ARGS);
+extern Datum numeric_var_samp(PG_FUNCTION_ARGS);
+extern Datum numeric_stddev_pop(PG_FUNCTION_ARGS);
+extern Datum numeric_stddev_samp(PG_FUNCTION_ARGS);
 extern Datum int2_sum(PG_FUNCTION_ARGS);
 extern Datum int4_sum(PG_FUNCTION_ARGS);
 extern Datum int8_sum(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index 8aed1864034..518315b3c1a 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -75,6 +75,68 @@ SELECT max(student.gpa) AS max_3_7 FROM student;
      3.7
 (1 row)
 
+SELECT stddev_pop(b) FROM aggtest;
+   stddev_pop    
+-----------------
+ 131.10703231895
+(1 row)
+
+SELECT stddev_samp(b) FROM aggtest;
+   stddev_samp    
+------------------
+ 151.389360803998
+(1 row)
+
+SELECT var_pop(b) FROM aggtest;
+     var_pop      
+------------------
+ 17189.0539234823
+(1 row)
+
+SELECT var_samp(b) FROM aggtest;
+     var_samp     
+------------------
+ 22918.7385646431
+(1 row)
+
+SELECT stddev_pop(b::numeric) FROM aggtest;
+    stddev_pop    
+------------------
+ 151.389361431288
+(1 row)
+
+SELECT stddev_samp(b::numeric) FROM aggtest;
+   stddev_samp    
+------------------
+ 151.389361431288
+(1 row)
+
+SELECT var_pop(b::numeric) FROM aggtest;
+      var_pop       
+--------------------
+ 22918.738754573025
+(1 row)
+
+SELECT var_samp(b::numeric) FROM aggtest;
+      var_samp      
+--------------------
+ 22918.738754573025
+(1 row)
+
+-- population variance is defined for a single tuple, sample variance
+-- is not
+SELECT var_pop(1.0), var_samp(2.0);
+ var_pop | var_samp 
+---------+----------
+       0 |         
+(1 row)
+
+SELECT stddev_pop(3.0::numeric), stddev_samp(4.0::numeric);
+ stddev_pop | stddev_samp 
+------------+-------------
+          0 |            
+(1 row)
+
 SELECT count(four) AS cnt_1000 FROM onek;
  cnt_1000 
 ----------
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index b6aba0d66b7..a9429525cab 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -15,25 +15,31 @@ SELECT avg(gpa) AS avg_3_4 FROM ONLY student;
 
 
 SELECT sum(four) AS sum_1500 FROM onek;
-
 SELECT sum(a) AS sum_198 FROM aggtest;
-
 SELECT sum(b) AS avg_431_773 FROM aggtest;
-
 SELECT sum(gpa) AS avg_6_8 FROM ONLY student;
 
-
 SELECT max(four) AS max_3 FROM onek;
-
 SELECT max(a) AS max_100 FROM aggtest;
-
 SELECT max(aggtest.b) AS max_324_78 FROM aggtest;
-
 SELECT max(student.gpa) AS max_3_7 FROM student;
 
+SELECT stddev_pop(b) FROM aggtest;
+SELECT stddev_samp(b) FROM aggtest;
+SELECT var_pop(b) FROM aggtest;
+SELECT var_samp(b) FROM aggtest;
 
-SELECT count(four) AS cnt_1000 FROM onek;
+SELECT stddev_pop(b::numeric) FROM aggtest;
+SELECT stddev_samp(b::numeric) FROM aggtest;
+SELECT var_pop(b::numeric) FROM aggtest;
+SELECT var_samp(b::numeric) FROM aggtest;
+
+-- population variance is defined for a single tuple, sample variance
+-- is not
+SELECT var_pop(1.0), var_samp(2.0);
+SELECT stddev_pop(3.0::numeric), stddev_samp(4.0::numeric);
 
+SELECT count(four) AS cnt_1000 FROM onek;
 SELECT count(DISTINCT four) AS cnt_4 FROM onek;
 
 select ten, count(*), sum(four) from onek
@@ -44,9 +50,7 @@ group by ten order by ten;
 
 
 SELECT newavg(four) AS avg_1 FROM onek;
-
 SELECT newsum(four) AS sum_1500 FROM onek;
-
 SELECT newcnt(four) AS cnt_1000 FROM onek;