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.

2 files changed, 76 insertions, 10 deletions

diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index 8aed186403..518315b3c1 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 b6aba0d66b..a9429525ca 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;