wip: functions median and percentile

*** ./contrib/Makefile.orig	2010-06-14 18:17:56.000000000 +0200
--- ./contrib/Makefile	2010-09-21 19:24:59.211190814 +0200
***************
*** 23,28 ****
--- 23,29 ----
  		isn		\
  		lo		\
  		ltree		\
+ 		median		\
  		oid2name	\
  		pageinspect	\
  		passwordcheck	\
*** ./contrib/median/expected/median.out.orig	2010-09-21 22:03:24.749899797 +0200
--- ./contrib/median/expected/median.out	2010-09-21 22:01:46.000000000 +0200
***************
*** 0 ****
--- 1,30 ----
+ -- import library
+ SET client_min_messages = warning;
+ \set ECHO none
+ -- check result for numeric datatypes
+ create table median_test(
+ 	a int, 
+ 	b int8, 
+ 	c float, 
+ 	d numeric, 
+ 	e double precision
+ );
+ insert into median_test
+    select i,i,i,i,i
+       from generate_series(1,10) g(i);
+ select median(a), median(b), median(c), median(d), median(e) from median_test;
+        median       |       median       | median |       median       | median 
+ --------------------+--------------------+--------+--------------------+--------
+  5.5000000000000000 | 5.5000000000000000 |    5.5 | 5.5000000000000000 |    5.5
+ (1 row)
+ 
+ truncate table median_test;
+ insert into median_test
+    select i,i,i,i,i
+       from generate_series(1,11) g(i);
+ select median(a), median(b), median(c), median(d), median(e) from median_test;
+  median | median | median | median | median 
+ --------+--------+--------+--------+--------
+       6 |      6 |      6 |      6 |      6
+ (1 row)
+ 
*** ./contrib/median/Makefile.orig	2010-08-19 12:38:56.144777253 +0200
--- ./contrib/median/Makefile	2010-08-18 20:23:39.180156339 +0200
***************
*** 0 ****
--- 1,17 ----
+ # $PostgreSQL: pgsql/contrib/median/Makefile,v 1.1 2008/07/29 18:31:20 tgl Exp $
+ 
+ MODULES = median
+ DATA_built = median.sql
+ DATA = uninstall_median.sql
+ REGRESS = median
+ 
+ ifdef USE_PGXS
+ PG_CONFIG = pg_config
+ PGXS := $(shell $(PG_CONFIG) --pgxs)
+ include $(PGXS)
+ else
+ subdir = contrib/median
+ top_builddir = ../..
+ include $(top_builddir)/src/Makefile.global
+ include $(top_srcdir)/contrib/contrib-global.mk
+ endif
*** ./contrib/median/median.c.orig	2010-08-19 12:39:01.456650776 +0200
--- ./contrib/median/median.c	2010-09-21 22:22:09.745898468 +0200
***************
*** 0 ****
--- 1,287 ----
+ /*
+  * $PostgreSQL: pgsql/contrib/citext/citext.c,v 1.2 2009/06/11 14:48:50 momjian Exp $
+  */
+ #include "postgres.h"
+ 
+ #include "funcapi.h"
+ #include "miscadmin.h"
+ #include "catalog/pg_type.h"
+ #include "parser/parse_coerce.h"
+ #include "parser/parse_oper.h"
+ #include "utils/builtins.h"
+ #include "utils/numeric.h"
+ #include "utils/tuplesort.h"
+ 
+ Datum median_transfn(PG_FUNCTION_ARGS);
+ Datum median_finalfn_double(PG_FUNCTION_ARGS);
+ Datum median_finalfn_numeric(PG_FUNCTION_ARGS);
+ 
+ Datum percentile_transfn(PG_FUNCTION_ARGS);
+ Datum percentile_finalfn(PG_FUNCTION_ARGS);
+ 
+ 
+ #ifdef PG_MODULE_MAGIC
+ PG_MODULE_MAGIC;
+ #endif
+ 
+ PG_FUNCTION_INFO_V1(median_transfn);
+ PG_FUNCTION_INFO_V1(median_finalfn_double);
+ PG_FUNCTION_INFO_V1(median_finalfn_numeric);
+ PG_FUNCTION_INFO_V1(percentile_transfn);
+ PG_FUNCTION_INFO_V1(percentile_finalfn);
+ 
+ typedef struct
+ {
+ 	int	nelems;		/* number of valid entries */
+ 	Tuplesortstate *sortstate;
+ 	FmgrInfo	cast_func_finfo;
+ 	int	p;		/* nth for percentille */
+ 	MemoryContext	aggcontext;
+ } StatAggState;
+ 
+ static StatAggState *
+ makeStatAggState(FunctionCallInfo fcinfo)
+ {
+ 	MemoryContext oldctx;
+ 	MemoryContext aggcontext;
+ 	StatAggState *aggstate;
+ 	Oid	sortop,
+ 			castfunc;
+ 	Oid	   valtype;
+ 	Oid		targettype = InvalidOid;
+ 	CoercionPathType		pathtype;
+ 
+ 	/* 
+ 	 * A tuplesort creates a child Memory Context "TupleSort", but
+ 	 * a current implementation of window functions drops all child
+ 	 * context every iteration, and therefore this function cannot
+ 	 * be called as windows function for this moment.
+ 	 */
+ 	if (fcinfo->context && IsA(fcinfo->context, WindowAggState))
+ 	{
+ 		/* cannot be called inside like windowAggregates */
+ 		elog(ERROR, "median_transfn called in windows aggregate context");
+ 	}
+ 
+ 	if (!AggCheckCallContext(fcinfo, &aggcontext))
+ 	{
+ 		/* cannot be called directly because of internal-type argument */
+ 		elog(ERROR, "string_agg_transfn called in non-aggregate context");
+ 	}
+ 
+ 	oldctx = MemoryContextSwitchTo(aggcontext);
+ 	
+ 	aggstate = (StatAggState *) palloc0(sizeof(StatAggState));
+ 	aggstate->nelems = 0;
+ 	aggstate->aggcontext = aggcontext;
+ 	
+ 	valtype = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ 	get_sort_group_operators(valtype,
+ 						    true, false, false,
+ 						    &sortop, NULL, NULL);
+ 
+ 	aggstate->sortstate = tuplesort_begin_datum(valtype,
+ 							sortop,
+ 							SORTBY_NULLS_DEFAULT,
+ 							work_mem, false);
+ 
+ 	MemoryContextSwitchTo(oldctx);
+ 
+ 	/* 
+ 	 * The result type will be numeric for integer types and for numeric type.
+ 	 * For other types resulting type is double precission. This is consistent
+ 	 * with other aggregates like AVG,..
+ 	 */
+ 	switch (valtype)
+ 	{
+ 		case FLOAT8OID:
+ 		case FLOAT4OID:
+ 			targettype = FLOAT8OID;
+ 			break;
+ 		
+ 		case INT2OID:
+ 		case INT4OID:
+ 		case INT8OID:
+ 		case NUMERICOID:
+ 			targettype = NUMERICOID;
+ 			break;
+ 		
+ 		default:
+ 			elog(ERROR, "unsupported data type with oid %d", valtype);
+ 	}
+ 	if (valtype != targettype)
+ 	{
+ 		/* find a cast function */
+ 		pathtype = find_coercion_pathway(targettype, valtype,
+ 									COERCION_EXPLICIT,
+ 									&castfunc);
+ 		if (pathtype == COERCION_PATH_FUNC)
+ 		{
+ 			Assert(OidIsValid(castfunc));
+ 			fmgr_info_cxt(castfunc, &aggstate->cast_func_finfo,
+ 									    aggcontext);
+ 		} 
+ 		else if (pathtype == COERCION_PATH_RELABELTYPE)
+ 		{
+ 			aggstate->cast_func_finfo.fn_oid = InvalidOid;
+ 		}
+ 		else 
+ 			elog(ERROR, "no conversion function from %s %s",
+ 					 format_type_be(valtype),
+ 					 format_type_be(targettype));
+ 	}
+ 
+ 	return aggstate;
+ }
+ 
+ /*
+  *  append a non NULL value to tuplesort
+  */
+ Datum
+ median_transfn(PG_FUNCTION_ARGS)
+ {
+ 	StatAggState *aggstate;
+ 	
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (StatAggState *) PG_GETARG_POINTER(0);
+ 	
+ 	if (!PG_ARGISNULL(1))
+ 	{
+ 		if (aggstate == NULL)
+ 			aggstate = makeStatAggState(fcinfo);
+ 		
+ 		tuplesort_putdatum(aggstate->sortstate, PG_GETARG_DATUM(1), false);
+ 		aggstate->nelems++;
+ 	}
+ 
+ 	PG_RETURN_POINTER(aggstate);
+ }
+ 
+ static double 
+ to_double(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetFloat8(value);
+ 	
+ 	return DatumGetFloat8(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ Datum
+ median_finalfn_double(PG_FUNCTION_ARGS)
+ {
+ 	StatAggState *aggstate;
+ 	
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (StatAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   value;
+ 		bool	isNull;
+ 		int		i = 1;
+ 		double	result = 0;
+ 		
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		tuplesort_performsort(aggstate->sortstate);
+ 		
+ 		while (tuplesort_getdatum(aggstate->sortstate, 
+ 								true,
+ 								      &value, &isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_double(value, &aggstate->cast_func_finfo);
+ 				
+ 				if (lidx != hidx)
+ 				{
+ 					tuplesort_getdatum(aggstate->sortstate,
+ 										true,
+ 										      &value, &isNull);
+ 					result = (result + to_double(value, &aggstate->cast_func_finfo)) / 2.0;
+ 				}
+ 				break;
+ 			}
+ 		}
+ 		
+ 		tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_FLOAT8(result);
+ 	}
+ 	else
+ 		PG_RETURN_NULL();
+ }
+ 
+ static Numeric
+ to_numeric(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetNumeric(value);
+ 
+ 	return DatumGetNumeric(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ 
+ Datum
+ median_finalfn_numeric(PG_FUNCTION_ARGS)
+ {
+ 	StatAggState *aggstate;
+ 	
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (StatAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   value;
+ 		bool	isNull;
+ 		int		i = 1;
+ 		Numeric result = NULL;		/* be compiler quiet */
+ 		
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		tuplesort_performsort(aggstate->sortstate);
+ 		
+ 		while (tuplesort_getdatum(aggstate->sortstate, 
+ 								true,
+ 								      &value, &isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_numeric(value, &aggstate->cast_func_finfo);
+ 				
+ 				if (lidx != hidx)
+ 				{
+ 					Numeric stack;
+ 					
+ 					tuplesort_getdatum(aggstate->sortstate,
+ 										true,
+ 										      &value, &isNull);
+ 					stack = to_numeric(value, &aggstate->cast_func_finfo);
+ 					stack = DatumGetNumeric(DirectFunctionCall2(numeric_add,
+ 												NumericGetDatum(stack),
+ 												NumericGetDatum(result)));
+ 					result = DatumGetNumeric(DirectFunctionCall2(numeric_div,
+ 												NumericGetDatum(stack),
+ 												DirectFunctionCall1(float4_numeric,
+ 																    Float4GetDatum(2.0))));
+ 				}
+ 				break;
+ 			}
+ 		}
+ 		
+ 		tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_NUMERIC(result);
+ 	}
+ 	else
+ 		PG_RETURN_NULL();
+ }
*** ./contrib/median/median.sql.in.orig	2010-08-19 12:39:06.192775857 +0200
--- ./contrib/median/median.sql.in	2010-09-21 21:52:18.730897577 +0200
***************
*** 0 ****
--- 1,50 ----
+ CREATE OR REPLACE FUNCTION median_transfn(internal, anyelement)
+ RETURNS internal
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE;
+ 
+ CREATE OR REPLACE FUNCTION median_finalfn_double(internal)
+ RETURNS double precision
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE;
+ 
+ CREATE OR REPLACE FUNCTION median_finalfn_numeric(internal)
+ RETURNS numeric
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE;
+ 
+ CREATE AGGREGATE median(bigint) (
+     SFUNC=median_transfn,
+     STYPE=internal,
+     FINALFUNC=median_finalfn_numeric
+ );
+ 
+ CREATE AGGREGATE median(double precision) (
+     SFUNC=median_transfn,
+     STYPE=internal,
+     FINALFUNC=median_finalfn_double
+ );
+ 
+ CREATE AGGREGATE median(integer) (
+     SFUNC=median_transfn,
+     STYPE=internal,
+     FINALFUNC=median_finalfn_numeric
+ );
+ 
+ CREATE AGGREGATE median(numeric) (
+     SFUNC=median_transfn,
+     STYPE=internal,
+     FINALFUNC=median_finalfn_numeric
+ );
+ 
+ CREATE AGGREGATE median(real) (
+     SFUNC=median_transfn,
+     STYPE=internal,
+     FINALFUNC=median_finalfn_double
+ );
+ 
+ CREATE AGGREGATE median(smallint) (
+     SFUNC=median_transfn,
+     STYPE=internal,
+     FINALFUNC=median_finalfn_numeric
+ );
*** ./contrib/median/sql/median.sql.orig	2010-09-21 21:59:24.909024776 +0200
--- ./contrib/median/sql/median.sql	2010-09-21 22:01:35.996896570 +0200
***************
*** 0 ****
--- 1,32 ----
+ -- import library
+ 
+ SET client_min_messages = warning;
+ \set ECHO none
+ \i median.sql
+ RESET client_min_messages;
+ \set ECHO all
+ 
+ -- check result for numeric datatypes
+ 
+ create table median_test(
+ 	a int, 
+ 	b int8, 
+ 	c float, 
+ 	d numeric, 
+ 	e double precision
+ );
+ 
+ insert into median_test
+    select i,i,i,i,i
+       from generate_series(1,10) g(i);
+ 
+ select median(a), median(b), median(c), median(d), median(e) from median_test;
+ 
+ truncate table median_test;
+ 
+ insert into median_test
+    select i,i,i,i,i
+       from generate_series(1,11) g(i);
+ 
+ select median(a), median(b), median(c), median(d), median(e) from median_test;
+ 
*** ./contrib/median/uninstall_median.sql.orig	2010-08-19 12:39:11.712777158 +0200
--- ./contrib/median/uninstall_median.sql	2010-09-21 21:52:08.794896392 +0200
***************
*** 0 ****
--- 1,9 ----
+ DROP FUNCTION median_transfn(internal, anyelement);
+ DROP FUNCTION median_finalfn_double(internal);
+ DROP FUNCTION median_finalfn_numeric(internal);
+ DROP AGGREGATE median(bigint);
+ DROP AGGREGATE median(double precison);
+ DROP AGGREGATE median(integer);
+ DROP AGGREGATE median(numeric);
+ DROP AGGREGATE median(real);
+ DROP AGGREGATE median(smallint);

*** ./contrib/Makefile.orig	2010-06-14 18:17:56.000000000 +0200
--- ./contrib/Makefile	2010-09-21 19:24:59.211190814 +0200
***************
*** 23,28 ****
--- 23,29 ----
  		isn		\
  		lo		\
  		ltree		\
+ 		median		\
  		oid2name	\
  		pageinspect	\
  		passwordcheck	\
*** ./contrib/median/expected/median.out.orig	2010-09-21 22:03:24.749899797 +0200
--- ./contrib/median/expected/median.out	2010-09-21 23:01:31.162022861 +0200
***************
*** 0 ****
--- 1,30 ----
+ -- import library
+ SET client_min_messages = warning;
+ \set ECHO none
+ -- check result for numeric datatypes
+ create table median_test(
+ 	a int,
+ 	b int8,
+ 	c float,
+ 	d numeric,
+ 	e double precision
+ );
+ insert into median_test
+    select i,i,i,i,i
+       from generate_series(1,10) g(i);
+ select median(a), median(b), median(c), median(d), median(e) from median_test;
+        median       |       median       | median |       median       | median 
+ --------------------+--------------------+--------+--------------------+--------
+  5.5000000000000000 | 5.5000000000000000 |    5.5 | 5.5000000000000000 |    5.5
+ (1 row)
+ 
+ truncate table median_test;
+ insert into median_test
+    select i,i,i,i,i
+       from generate_series(1,11) g(i);
+ select median(a), median(b), median(c), median(d), median(e) from median_test;
+  median | median | median | median | median 
+ --------+--------+--------+--------+--------
+       6 |      6 |      6 |      6 |      6
+ (1 row)
+ 
*** ./contrib/median/Makefile.orig	2010-08-19 12:38:56.144777253 +0200
--- ./contrib/median/Makefile	2010-08-18 20:23:39.180156339 +0200
***************
*** 0 ****
--- 1,17 ----
+ # $PostgreSQL: pgsql/contrib/median/Makefile,v 1.1 2008/07/29 18:31:20 tgl Exp $
+ 
+ MODULES = median
+ DATA_built = median.sql
+ DATA = uninstall_median.sql
+ REGRESS = median
+ 
+ ifdef USE_PGXS
+ PG_CONFIG = pg_config
+ PGXS := $(shell $(PG_CONFIG) --pgxs)
+ include $(PGXS)
+ else
+ subdir = contrib/median
+ top_builddir = ../..
+ include $(top_builddir)/src/Makefile.global
+ include $(top_srcdir)/contrib/contrib-global.mk
+ endif
*** ./contrib/median/median.c.orig	2010-08-19 12:39:01.456650776 +0200
--- ./contrib/median/median.c	2010-09-21 23:00:17.962025215 +0200
***************
*** 0 ****
--- 1,283 ----
+ /*
+  * $PostgreSQL: pgsql/contrib/citext/citext.c,v 1.2 2009/06/11 14:48:50 momjian Exp $
+  */
+ #include "postgres.h"
+ 
+ #include "funcapi.h"
+ #include "miscadmin.h"
+ #include "catalog/pg_type.h"
+ #include "parser/parse_coerce.h"
+ #include "parser/parse_oper.h"
+ #include "utils/builtins.h"
+ #include "utils/numeric.h"
+ #include "utils/tuplesort.h"
+ 
+ Datum median_transfn(PG_FUNCTION_ARGS);
+ Datum median_finalfn_double(PG_FUNCTION_ARGS);
+ Datum median_finalfn_numeric(PG_FUNCTION_ARGS);
+ 
+ Datum percentile_transfn(PG_FUNCTION_ARGS);
+ Datum percentile_finalfn(PG_FUNCTION_ARGS);
+ 
+ #ifdef PG_MODULE_MAGIC
+ PG_MODULE_MAGIC;
+ #endif
+ 
+ PG_FUNCTION_INFO_V1(median_transfn);
+ PG_FUNCTION_INFO_V1(median_finalfn_double);
+ PG_FUNCTION_INFO_V1(median_finalfn_numeric);
+ 
+ typedef struct
+ {
+ 	int	nelems;		/* number of valid entries */
+ 	Tuplesortstate *sortstate;
+ 	FmgrInfo	cast_func_finfo;
+ 	int	p;		/* nth for percentille */
+ 	MemoryContext	aggcontext;
+ } StatAggState;
+ 
+ static StatAggState *
+ makeStatAggState(FunctionCallInfo fcinfo)
+ {
+ 	MemoryContext oldctx;
+ 	MemoryContext aggcontext;
+ 	StatAggState *aggstate;
+ 	Oid	sortop,
+ 			castfunc;
+ 	Oid	   valtype;
+ 	Oid		targettype = InvalidOid;
+ 	CoercionPathType		pathtype;
+ 
+ 	/*
+ 	 * A tuplesort creates a child Memory Context "TupleSort", but
+ 	 * a current implementation of window functions drops all child
+ 	 * context every iteration, and therefore this function cannot
+ 	 * be called as windows function for this moment.
+ 	 */
+ 	if (fcinfo->context && IsA(fcinfo->context, WindowAggState))
+ 	{
+ 		/* cannot be called inside like windowAggregates */
+ 		elog(ERROR, "median_transfn called in windows aggregate context");
+ 	}
+ 
+ 	if (!AggCheckCallContext(fcinfo, &aggcontext))
+ 	{
+ 		/* cannot be called directly because of internal-type argument */
+ 		elog(ERROR, "string_agg_transfn called in non-aggregate context");
+ 	}
+ 
+ 	oldctx = MemoryContextSwitchTo(aggcontext);
+ 	
+ 	aggstate = (StatAggState *) palloc0(sizeof(StatAggState));
+ 	aggstate->nelems = 0;
+ 	aggstate->aggcontext = aggcontext;
+ 	
+ 	valtype = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ 	get_sort_group_operators(valtype,
+ 						    true, false, false,
+ 						    &sortop, NULL, NULL);
+ 
+ 	aggstate->sortstate = tuplesort_begin_datum(valtype,
+ 							sortop,
+ 							SORTBY_NULLS_DEFAULT,
+ 							work_mem, false);
+ 
+ 	MemoryContextSwitchTo(oldctx);
+ 
+ 	/* 
+ 	 * The result type will be numeric for integer types and for numeric type.
+ 	 * For other types resulting type is double precission. This is consistent
+ 	 * with other aggregates like AVG,..
+ 	 */
+ 	switch (valtype)
+ 	{
+ 		case FLOAT8OID:
+ 		case FLOAT4OID:
+ 			targettype = FLOAT8OID;
+ 			break;
+ 
+ 		case INT2OID:
+ 		case INT4OID:
+ 		case INT8OID:
+ 		case NUMERICOID:
+ 			targettype = NUMERICOID;
+ 			break;
+ 
+ 		default:
+ 			elog(ERROR, "unsupported data type with oid %d", valtype);
+ 	}
+ 	if (valtype != targettype)
+ 	{
+ 		/* find a cast function */
+ 		pathtype = find_coercion_pathway(targettype, valtype,
+ 									COERCION_EXPLICIT,
+ 									&castfunc);
+ 		if (pathtype == COERCION_PATH_FUNC)
+ 		{
+ 			Assert(OidIsValid(castfunc));
+ 			fmgr_info_cxt(castfunc, &aggstate->cast_func_finfo,
+ 									    aggcontext);
+ 		} 
+ 		else if (pathtype == COERCION_PATH_RELABELTYPE)
+ 		{
+ 			aggstate->cast_func_finfo.fn_oid = InvalidOid;
+ 		}
+ 		else 
+ 			elog(ERROR, "no conversion function from %s %s",
+ 					 format_type_be(valtype),
+ 					 format_type_be(targettype));
+ 	}
+ 
+ 	return aggstate;
+ }
+ 
+ /*
+  *  append a non NULL value to tuplesort
+  */
+ Datum
+ median_transfn(PG_FUNCTION_ARGS)
+ {
+ 	StatAggState *aggstate;
+ 	
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (StatAggState *) PG_GETARG_POINTER(0);
+ 	
+ 	if (!PG_ARGISNULL(1))
+ 	{
+ 		if (aggstate == NULL)
+ 			aggstate = makeStatAggState(fcinfo);
+ 		
+ 		tuplesort_putdatum(aggstate->sortstate, PG_GETARG_DATUM(1), false);
+ 		aggstate->nelems++;
+ 	}
+ 
+ 	PG_RETURN_POINTER(aggstate);
+ }
+ 
+ static double 
+ to_double(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetFloat8(value);
+ 	
+ 	return DatumGetFloat8(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ Datum
+ median_finalfn_double(PG_FUNCTION_ARGS)
+ {
+ 	StatAggState *aggstate;
+ 
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (StatAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   value;
+ 		bool	isNull;
+ 		int		i = 1;
+ 		double	result = 0;
+ 
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		tuplesort_performsort(aggstate->sortstate);
+ 		
+ 		while (tuplesort_getdatum(aggstate->sortstate, 
+ 								true,
+ 								      &value, &isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_double(value, &aggstate->cast_func_finfo);
+ 				
+ 				if (lidx != hidx)
+ 				{
+ 					tuplesort_getdatum(aggstate->sortstate,
+ 										true,
+ 										      &value, &isNull);
+ 					result = (result + to_double(value, &aggstate->cast_func_finfo)) / 2.0;
+ 				}
+ 				break;
+ 			}
+ 		}
+ 
+ 		tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_FLOAT8(result);
+ 	}
+ 	else
+ 		PG_RETURN_NULL();
+ }
+ 
+ static Numeric
+ to_numeric(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetNumeric(value);
+ 
+ 	return DatumGetNumeric(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ Datum
+ median_finalfn_numeric(PG_FUNCTION_ARGS)
+ {
+ 	StatAggState *aggstate;
+ 
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (StatAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   value;
+ 		bool	isNull;
+ 		int		i = 1;
+ 		Numeric result = NULL;		/* be compiler quiet */
+ 		
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		tuplesort_performsort(aggstate->sortstate);
+ 		
+ 		while (tuplesort_getdatum(aggstate->sortstate, 
+ 								true,
+ 								      &value, &isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_numeric(value, &aggstate->cast_func_finfo);
+ 				
+ 				if (lidx != hidx)
+ 				{
+ 					Numeric stack;
+ 					
+ 					tuplesort_getdatum(aggstate->sortstate,
+ 										true,
+ 										      &value, &isNull);
+ 					stack = to_numeric(value, &aggstate->cast_func_finfo);
+ 					stack = DatumGetNumeric(DirectFunctionCall2(numeric_add,
+ 												NumericGetDatum(stack),
+ 												NumericGetDatum(result)));
+ 					result = DatumGetNumeric(DirectFunctionCall2(numeric_div,
+ 												NumericGetDatum(stack),
+ 												DirectFunctionCall1(float4_numeric,
+ 																    Float4GetDatum(2.0))));
+ 				}
+ 				break;
+ 			}
+ 		}
+ 
+ 		tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_NUMERIC(result);
+ 	}
+ 	else
+ 		PG_RETURN_NULL();
+ }
*** ./contrib/median/median.sql.in.orig	2010-08-19 12:39:06.192775857 +0200
--- ./contrib/median/median.sql.in	2010-09-21 21:52:18.730897577 +0200
***************
*** 0 ****
--- 1,50 ----
+ CREATE OR REPLACE FUNCTION median_transfn(internal, anyelement)
+ RETURNS internal
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE;
+ 
+ CREATE OR REPLACE FUNCTION median_finalfn_double(internal)
+ RETURNS double precision
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE;
+ 
+ CREATE OR REPLACE FUNCTION median_finalfn_numeric(internal)
+ RETURNS numeric
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE;
+ 
+ CREATE AGGREGATE median(bigint) (
+     SFUNC=median_transfn,
+     STYPE=internal,
+     FINALFUNC=median_finalfn_numeric
+ );
+ 
+ CREATE AGGREGATE median(double precision) (
+     SFUNC=median_transfn,
+     STYPE=internal,
+     FINALFUNC=median_finalfn_double
+ );
+ 
+ CREATE AGGREGATE median(integer) (
+     SFUNC=median_transfn,
+     STYPE=internal,
+     FINALFUNC=median_finalfn_numeric
+ );
+ 
+ CREATE AGGREGATE median(numeric) (
+     SFUNC=median_transfn,
+     STYPE=internal,
+     FINALFUNC=median_finalfn_numeric
+ );
+ 
+ CREATE AGGREGATE median(real) (
+     SFUNC=median_transfn,
+     STYPE=internal,
+     FINALFUNC=median_finalfn_double
+ );
+ 
+ CREATE AGGREGATE median(smallint) (
+     SFUNC=median_transfn,
+     STYPE=internal,
+     FINALFUNC=median_finalfn_numeric
+ );
*** ./contrib/median/sql/median.sql.orig	2010-09-21 21:59:24.909024776 +0200
--- ./contrib/median/sql/median.sql	2010-09-21 23:01:18.017899528 +0200
***************
*** 0 ****
--- 1,31 ----
+ -- import library
+ 
+ SET client_min_messages = warning;
+ \set ECHO none
+ \i median.sql
+ RESET client_min_messages;
+ \set ECHO all
+ 
+ -- check result for numeric datatypes
+ 
+ create table median_test(
+ 	a int,
+ 	b int8,
+ 	c float,
+ 	d numeric,
+ 	e double precision
+ );
+ 
+ insert into median_test
+    select i,i,i,i,i
+       from generate_series(1,10) g(i);
+ 
+ select median(a), median(b), median(c), median(d), median(e) from median_test;
+ 
+ truncate table median_test;
+ 
+ insert into median_test
+    select i,i,i,i,i
+       from generate_series(1,11) g(i);
+ 
+ select median(a), median(b), median(c), median(d), median(e) from median_test;
*** ./contrib/median/uninstall_median.sql.orig	2010-08-19 12:39:11.712777158 +0200
--- ./contrib/median/uninstall_median.sql	2010-09-21 21:52:08.794896392 +0200
***************
*** 0 ****
--- 1,9 ----
+ DROP FUNCTION median_transfn(internal, anyelement);
+ DROP FUNCTION median_finalfn_double(internal);
+ DROP FUNCTION median_finalfn_numeric(internal);
+ DROP AGGREGATE median(bigint);
+ DROP AGGREGATE median(double precison);
+ DROP AGGREGATE median(integer);
+ DROP AGGREGATE median(numeric);
+ DROP AGGREGATE median(real);
+ DROP AGGREGATE median(smallint);

*** ./contrib/Makefile.orig	2010-06-14 18:17:56.000000000 +0200
--- ./contrib/Makefile	2010-09-21 19:24:59.211190814 +0200
***************
*** 23,28 ****
--- 23,29 ----
  		isn		\
  		lo		\
  		ltree		\
+ 		median		\
  		oid2name	\
  		pageinspect	\
  		passwordcheck	\
*** ./contrib/median/expected/median.out.orig	2010-09-21 22:03:24.749899797 +0200
--- ./contrib/median/expected/median.out	2010-09-21 23:01:31.162022861 +0200
***************
*** 0 ****
--- 1,30 ----
+ -- import library
+ SET client_min_messages = warning;
+ \set ECHO none
+ -- check result for numeric datatypes
+ create table median_test(
+ 	a int,
+ 	b int8,
+ 	c float,
+ 	d numeric,
+ 	e double precision
+ );
+ insert into median_test
+    select i,i,i,i,i
+       from generate_series(1,10) g(i);
+ select median(a), median(b), median(c), median(d), median(e) from median_test;
+        median       |       median       | median |       median       | median 
+ --------------------+--------------------+--------+--------------------+--------
+  5.5000000000000000 | 5.5000000000000000 |    5.5 | 5.5000000000000000 |    5.5
+ (1 row)
+ 
+ truncate table median_test;
+ insert into median_test
+    select i,i,i,i,i
+       from generate_series(1,11) g(i);
+ select median(a), median(b), median(c), median(d), median(e) from median_test;
+  median | median | median | median | median 
+ --------+--------+--------+--------+--------
+       6 |      6 |      6 |      6 |      6
+ (1 row)
+ 
*** ./contrib/median/Makefile.orig	2010-08-19 12:38:56.144777253 +0200
--- ./contrib/median/Makefile	2010-08-18 20:23:39.180156339 +0200
***************
*** 0 ****
--- 1,17 ----
+ # $PostgreSQL: pgsql/contrib/median/Makefile,v 1.1 2008/07/29 18:31:20 tgl Exp $
+ 
+ MODULES = median
+ DATA_built = median.sql
+ DATA = uninstall_median.sql
+ REGRESS = median
+ 
+ ifdef USE_PGXS
+ PG_CONFIG = pg_config
+ PGXS := $(shell $(PG_CONFIG) --pgxs)
+ include $(PGXS)
+ else
+ subdir = contrib/median
+ top_builddir = ../..
+ include $(top_builddir)/src/Makefile.global
+ include $(top_srcdir)/contrib/contrib-global.mk
+ endif
*** ./contrib/median/median.c.orig	2010-08-19 12:39:01.456650776 +0200
--- ./contrib/median/median.c	2010-09-22 06:35:30.187056723 +0200
***************
*** 0 ****
--- 1,283 ----
+ /*
+  * $PostgreSQL: pgsql/contrib/citext/citext.c,v 1.2 2009/06/11 14:48:50 momjian Exp $
+  */
+ #include "postgres.h"
+ 
+ #include "funcapi.h"
+ #include "miscadmin.h"
+ #include "catalog/pg_type.h"
+ #include "parser/parse_coerce.h"
+ #include "parser/parse_oper.h"
+ #include "utils/builtins.h"
+ #include "utils/numeric.h"
+ #include "utils/tuplesort.h"
+ 
+ Datum median_transfn(PG_FUNCTION_ARGS);
+ Datum median_finalfn_double(PG_FUNCTION_ARGS);
+ Datum median_finalfn_numeric(PG_FUNCTION_ARGS);
+ 
+ Datum percentile_transfn(PG_FUNCTION_ARGS);
+ Datum percentile_finalfn(PG_FUNCTION_ARGS);
+ 
+ #ifdef PG_MODULE_MAGIC
+ PG_MODULE_MAGIC;
+ #endif
+ 
+ PG_FUNCTION_INFO_V1(median_transfn);
+ PG_FUNCTION_INFO_V1(median_finalfn_double);
+ PG_FUNCTION_INFO_V1(median_finalfn_numeric);
+ 
+ typedef struct
+ {
+ 	int	nelems;		/* number of valid entries */
+ 	Tuplesortstate *sortstate;
+ 	FmgrInfo	cast_func_finfo;
+ 	int	p;		/* nth for percentille */
+ 	MemoryContext	aggcontext;
+ } StatAggState;
+ 
+ static StatAggState *
+ makeStatAggState(FunctionCallInfo fcinfo)
+ {
+ 	MemoryContext oldctx;
+ 	MemoryContext aggcontext;
+ 	StatAggState *aggstate;
+ 	Oid	sortop,
+ 			castfunc;
+ 	Oid	   valtype;
+ 	Oid		targettype = InvalidOid;
+ 	CoercionPathType		pathtype;
+ 
+ 	/*
+ 	 * A tuplesort creates a child Memory Context "TupleSort", but
+ 	 * a current implementation of window functions drops all child
+ 	 * context every iteration, and therefore this function cannot
+ 	 * be called as windows function for this moment.
+ 	 */
+ 	if (fcinfo->context && IsA(fcinfo->context, WindowAggState))
+ 	{
+ 		/* cannot be called inside like windowAggregates */
+ 		elog(ERROR, "median_transfn called in windows aggregate context");
+ 	}
+ 
+ 	if (!AggCheckCallContext(fcinfo, &aggcontext))
+ 	{
+ 		/* cannot be called directly because of internal-type argument */
+ 		elog(ERROR, "string_agg_transfn called in non-aggregate context");
+ 	}
+ 
+ 	oldctx = MemoryContextSwitchTo(aggcontext);
+ 
+ 	aggstate = (StatAggState *) palloc0(sizeof(StatAggState));
+ 	aggstate->nelems = 0;
+ 	aggstate->aggcontext = aggcontext;
+ 
+ 	valtype = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ 	get_sort_group_operators(valtype,
+ 						    true, false, false,
+ 						    &sortop, NULL, NULL);
+ 
+ 	aggstate->sortstate = tuplesort_begin_datum(valtype,
+ 							sortop,
+ 							SORTBY_NULLS_DEFAULT,
+ 							work_mem, false);
+ 
+ 	MemoryContextSwitchTo(oldctx);
+ 
+ 	/* 
+ 	 * The result type will be numeric for integer types and for numeric type.
+ 	 * For other types resulting type is double precission. This is consistent
+ 	 * with other aggregates like AVG,..
+ 	 */
+ 	switch (valtype)
+ 	{
+ 		case FLOAT8OID:
+ 		case FLOAT4OID:
+ 			targettype = FLOAT8OID;
+ 			break;
+ 
+ 		case INT2OID:
+ 		case INT4OID:
+ 		case INT8OID:
+ 		case NUMERICOID:
+ 			targettype = NUMERICOID;
+ 			break;
+ 
+ 		default:
+ 			elog(ERROR, "unsupported data type with oid %d", valtype);
+ 	}
+ 	if (valtype != targettype)
+ 	{
+ 		/* find a cast function */
+ 		pathtype = find_coercion_pathway(targettype, valtype,
+ 									COERCION_EXPLICIT,
+ 									&castfunc);
+ 		if (pathtype == COERCION_PATH_FUNC)
+ 		{
+ 			Assert(OidIsValid(castfunc));
+ 			fmgr_info_cxt(castfunc, &aggstate->cast_func_finfo,
+ 									    aggcontext);
+ 		} 
+ 		else if (pathtype == COERCION_PATH_RELABELTYPE)
+ 		{
+ 			aggstate->cast_func_finfo.fn_oid = InvalidOid;
+ 		}
+ 		else 
+ 			elog(ERROR, "no conversion function from %s %s",
+ 					 format_type_be(valtype),
+ 					 format_type_be(targettype));
+ 	}
+ 
+ 	return aggstate;
+ }
+ 
+ /*
+  *  append a non NULL value to tuplesort
+  */
+ Datum
+ median_transfn(PG_FUNCTION_ARGS)
+ {
+ 	StatAggState *aggstate;
+ 	
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (StatAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (!PG_ARGISNULL(1))
+ 	{
+ 		if (aggstate == NULL)
+ 			aggstate = makeStatAggState(fcinfo);
+ 
+ 		tuplesort_putdatum(aggstate->sortstate, PG_GETARG_DATUM(1), false);
+ 		aggstate->nelems++;
+ 	}
+ 
+ 	PG_RETURN_POINTER(aggstate);
+ }
+ 
+ static double 
+ to_double(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetFloat8(value);
+ 
+ 	return DatumGetFloat8(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ Datum
+ median_finalfn_double(PG_FUNCTION_ARGS)
+ {
+ 	StatAggState *aggstate;
+ 
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (StatAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   value;
+ 		bool	isNull;
+ 		int		i = 1;
+ 		double	result = 0;
+ 
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		tuplesort_performsort(aggstate->sortstate);
+ 
+ 		while (tuplesort_getdatum(aggstate->sortstate, 
+ 								true,
+ 								      &value, &isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_double(value, &aggstate->cast_func_finfo);
+ 
+ 				if (lidx != hidx)
+ 				{
+ 					tuplesort_getdatum(aggstate->sortstate,
+ 										true,
+ 										      &value, &isNull);
+ 					result = (result + to_double(value, &aggstate->cast_func_finfo)) / 2.0;
+ 				}
+ 				break;
+ 			}
+ 		}
+ 
+ 		tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_FLOAT8(result);
+ 	}
+ 	else
+ 		PG_RETURN_NULL();
+ }
+ 
+ static Numeric
+ to_numeric(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetNumeric(value);
+ 
+ 	return DatumGetNumeric(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ Datum
+ median_finalfn_numeric(PG_FUNCTION_ARGS)
+ {
+ 	StatAggState *aggstate;
+ 
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (StatAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   value;
+ 		bool	isNull;
+ 		int		i = 1;
+ 		Numeric result = NULL;		/* be compiler quiet */
+ 
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		tuplesort_performsort(aggstate->sortstate);
+ 		
+ 		while (tuplesort_getdatum(aggstate->sortstate, 
+ 								true,
+ 								      &value, &isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_numeric(value, &aggstate->cast_func_finfo);
+ 
+ 				if (lidx != hidx)
+ 				{
+ 					Numeric stack;
+ 
+ 					tuplesort_getdatum(aggstate->sortstate,
+ 										true,
+ 										      &value, &isNull);
+ 					stack = to_numeric(value, &aggstate->cast_func_finfo);
+ 					stack = DatumGetNumeric(DirectFunctionCall2(numeric_add,
+ 												NumericGetDatum(stack),
+ 												NumericGetDatum(result)));
+ 					result = DatumGetNumeric(DirectFunctionCall2(numeric_div,
+ 												NumericGetDatum(stack),
+ 												DirectFunctionCall1(float4_numeric,
+ 																    Float4GetDatum(2.0))));
+ 				}
+ 				break;
+ 			}
+ 		}
+ 
+ 		tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_NUMERIC(result);
+ 	}
+ 	else
+ 		PG_RETURN_NULL();
+ }
*** ./contrib/median/median.sql.in.orig	2010-08-19 12:39:06.192775857 +0200
--- ./contrib/median/median.sql.in	2010-09-21 21:52:18.730897577 +0200
***************
*** 0 ****
--- 1,50 ----
+ CREATE OR REPLACE FUNCTION median_transfn(internal, anyelement)
+ RETURNS internal
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE;
+ 
+ CREATE OR REPLACE FUNCTION median_finalfn_double(internal)
+ RETURNS double precision
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE;
+ 
+ CREATE OR REPLACE FUNCTION median_finalfn_numeric(internal)
+ RETURNS numeric
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE;
+ 
+ CREATE AGGREGATE median(bigint) (
+     SFUNC=median_transfn,
+     STYPE=internal,
+     FINALFUNC=median_finalfn_numeric
+ );
+ 
+ CREATE AGGREGATE median(double precision) (
+     SFUNC=median_transfn,
+     STYPE=internal,
+     FINALFUNC=median_finalfn_double
+ );
+ 
+ CREATE AGGREGATE median(integer) (
+     SFUNC=median_transfn,
+     STYPE=internal,
+     FINALFUNC=median_finalfn_numeric
+ );
+ 
+ CREATE AGGREGATE median(numeric) (
+     SFUNC=median_transfn,
+     STYPE=internal,
+     FINALFUNC=median_finalfn_numeric
+ );
+ 
+ CREATE AGGREGATE median(real) (
+     SFUNC=median_transfn,
+     STYPE=internal,
+     FINALFUNC=median_finalfn_double
+ );
+ 
+ CREATE AGGREGATE median(smallint) (
+     SFUNC=median_transfn,
+     STYPE=internal,
+     FINALFUNC=median_finalfn_numeric
+ );
*** ./contrib/median/sql/median.sql.orig	2010-09-21 21:59:24.909024776 +0200
--- ./contrib/median/sql/median.sql	2010-09-21 23:01:18.017899528 +0200
***************
*** 0 ****
--- 1,31 ----
+ -- import library
+ 
+ SET client_min_messages = warning;
+ \set ECHO none
+ \i median.sql
+ RESET client_min_messages;
+ \set ECHO all
+ 
+ -- check result for numeric datatypes
+ 
+ create table median_test(
+ 	a int,
+ 	b int8,
+ 	c float,
+ 	d numeric,
+ 	e double precision
+ );
+ 
+ insert into median_test
+    select i,i,i,i,i
+       from generate_series(1,10) g(i);
+ 
+ select median(a), median(b), median(c), median(d), median(e) from median_test;
+ 
+ truncate table median_test;
+ 
+ insert into median_test
+    select i,i,i,i,i
+       from generate_series(1,11) g(i);
+ 
+ select median(a), median(b), median(c), median(d), median(e) from median_test;
*** ./contrib/median/uninstall_median.sql.orig	2010-08-19 12:39:11.712777158 +0200
--- ./contrib/median/uninstall_median.sql	2010-09-21 21:52:08.794896392 +0200
***************
*** 0 ****
--- 1,9 ----
+ DROP FUNCTION median_transfn(internal, anyelement);
+ DROP FUNCTION median_finalfn_double(internal);
+ DROP FUNCTION median_finalfn_numeric(internal);
+ DROP AGGREGATE median(bigint);
+ DROP AGGREGATE median(double precison);
+ DROP AGGREGATE median(integer);
+ DROP AGGREGATE median(numeric);
+ DROP AGGREGATE median(real);
+ DROP AGGREGATE median(smallint);

*** ./doc/src/sgml/func.sgml.orig	2010-08-17 06:37:20.000000000 +0200
--- ./doc/src/sgml/func.sgml	2010-09-23 15:03:10.021576906 +0200
***************
*** 10304,10309 ****
--- 10304,10329 ----
  
       <row>
        <entry>
+        <indexterm>
+         <primary>Arithmetic median</primary>
+         <secondary>median</secondary>
+        </indexterm>
+        <function>median(<replaceable class="parameter">expression</replaceable>)</function>
+       </entry>
+       <entry>
+        <type>smallint</type>, <type>int</type>,
+        <type>bigint</type>, <type>real</type>, <type>double
+        precision</type>, or <type>numeric</type>
+       </entry>
+       <entry>
+        <type>double precision</type> for floating-point arguments,
+        otherwise <type>numeric</type>
+       </entry>
+       <entry>arithmetic median</entry>
+      </row>
+ 
+      <row>
+       <entry>
         <function>regr_avgx(<replaceable class="parameter">Y</replaceable>, <replaceable class="parameter">X</replaceable>)</function>
        </entry>
        <entry>
*** ./src/backend/utils/adt/numeric.c.orig	2010-08-04 19:33:09.000000000 +0200
--- ./src/backend/utils/adt/numeric.c	2010-09-23 15:15:26.775451348 +0200
***************
*** 30,39 ****
--- 30,42 ----
  #include "catalog/pg_type.h"
  #include "libpq/pqformat.h"
  #include "miscadmin.h"
+ #include "parser/parse_coerce.h"
+ #include "parser/parse_oper.h"
  #include "utils/array.h"
  #include "utils/builtins.h"
  #include "utils/int8.h"
  #include "utils/numeric.h"
+ #include "utils/tuplesort.h"
  
  /* ----------
   * Uncomment the following to enable compilation of dump_numeric()
***************
*** 144,149 ****
--- 147,161 ----
  	union NumericChoice	choice;	/* choice of format */
  };
  
+ /*
+  * used as type of state variable median's function
+  */
+ typedef struct
+ {
+ 	int	nelems;		/* number of valid entries */
+ 	Tuplesortstate *sortstate;
+ 	FmgrInfo	cast_func_finfo;
+ } MedianAggState;
  
  /*
   * Interpretation of high bits.
***************
*** 6173,6175 ****
--- 6185,6490 ----
  	var->digits = digits;
  	var->ndigits = ndigits;
  }
+ 
+ static MedianAggState *
+ makeMedianAggState(FunctionCallInfo fcinfo, Oid valtype, Oid targetoid)
+ {
+ 	MemoryContext oldctx;
+ 	MemoryContext aggcontext;
+ 	MedianAggState *aggstate;
+ 	Oid	sortop,
+ 			castfunc;
+ 	Oid		targettype = InvalidOid;
+ 	CoercionPathType		pathtype;
+ 
+ 	/*
+ 	 * We cannot to allow a median function under WindowAgg State.
+ 	 * This content needs a repetetive a calling of final function,
+ 	 * what isn't possible now with using a tuplestore. 
+ 	 * tuplesort_performsort can be called only once, and some has
+ 	 * to call a tuplesort_end.
+ 	 */
+ 	if (fcinfo->context && IsA(fcinfo->context, WindowAggState))
+ 	{
+ 		/* cannot be called inside like windowAggregates */
+ 		elog(ERROR, "median_transfn called as windows aggregates");
+ 	}
+ 
+ 	if (!AggCheckCallContext(fcinfo, &aggcontext))
+ 	{
+ 		/* cannot be called directly because of internal-type argument */
+ 		elog(ERROR, "median_transfn called in non-aggregate context");
+ 	}
+ 
+ 	oldctx = MemoryContextSwitchTo(aggcontext);
+ 
+ 	aggstate = (MedianAggState *) palloc0(sizeof(MedianAggState));
+ 
+ 	valtype = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ 	get_sort_group_operators(valtype,
+ 						    true, false, false,
+ 						    &sortop, NULL, NULL);
+ 
+ 	aggstate->sortstate = tuplesort_begin_datum(valtype,
+ 							sortop,
+ 							SORTBY_NULLS_DEFAULT,
+ 							work_mem, true);
+ 
+ 	MemoryContextSwitchTo(oldctx);
+ 
+ 	/* 
+ 	 * The result type will be numeric for integer types and for numeric type.
+ 	 * For other types resulting type is double precission. This is consistent
+ 	 * with other aggregates like AVG,..
+ 	 */
+ 	switch (valtype)
+ 	{
+ 		case FLOAT8OID:
+ 		case FLOAT4OID:
+ 			targettype = FLOAT8OID;
+ 			break;
+ 
+ 		case INT2OID:
+ 		case INT4OID:
+ 		case INT8OID:
+ 		case NUMERICOID:
+ 			targettype = NUMERICOID;
+ 			break;
+ 
+ 		default:
+ 			elog(ERROR, "unsupported data type with oid %d", valtype);
+ 	}
+ 	if (valtype != targettype)
+ 	{
+ 		/* find a cast function */
+ 		pathtype = find_coercion_pathway(targettype, valtype,
+ 									COERCION_EXPLICIT,
+ 									&castfunc);
+ 		if (pathtype == COERCION_PATH_FUNC)
+ 		{
+ 			Assert(OidIsValid(castfunc));
+ 			fmgr_info_cxt(castfunc, &aggstate->cast_func_finfo,
+ 									    aggcontext);
+ 		} 
+ 		else if (pathtype == COERCION_PATH_RELABELTYPE)
+ 		{
+ 			aggstate->cast_func_finfo.fn_oid = InvalidOid;
+ 		}
+ 		else 
+ 			elog(ERROR, "no conversion function from %s %s",
+ 					 format_type_be(valtype),
+ 					 format_type_be(targettype));
+ 	}
+ 
+ 	return aggstate;
+ }
+ 
+ /*
+  *  append a non NULL value to tuplesort
+  */
+ static Datum
+ common_median_transfn(FunctionCallInfo fcinfo, Oid typoid, Oid targetoid)
+ {
+ 	MedianAggState *aggstate;
+ 	
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (!PG_ARGISNULL(1))
+ 	{
+ 		if (aggstate == NULL)
+ 			aggstate = makeMedianAggState(fcinfo, typoid, targetoid);
+ 
+ 		tuplesort_putdatum(aggstate->sortstate, PG_GETARG_DATUM(1), false);
+ 		aggstate->nelems++;
+ 	}
+ 
+ 	PG_RETURN_POINTER(aggstate);
+ }
+ 
+ /*
+  * just wrappers to be opr sanity checks happy
+  */
+ Datum
+ median_numeric_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						NUMERICOID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int8_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						INT8OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int4_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						INT4OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int2_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						INT2OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_double_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						FLOAT8OID, FLOAT8OID);
+ }
+ 
+ Datum
+ median_float_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						FLOAT4OID, FLOAT8OID);
+ }
+ 
+ /*
+  * Used for reading values from tuplesort. The value has to be
+  * double or cast function is defined (and used).
+  */
+ static double 
+ to_double(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetFloat8(value);
+ 
+ 	return DatumGetFloat8(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ /*
+  * Used as final function for median when result is double.
+  */
+ Datum
+ median_finalfn_double(PG_FUNCTION_ARGS)
+ {
+ 	MedianAggState *aggstate;
+ 
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   value;
+ 		bool	isNull;
+ 		int		i = 1;
+ 		double	result = 0;
+ 
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		tuplesort_performsort(aggstate->sortstate);
+ 
+ 		while (tuplesort_getdatum(aggstate->sortstate, 
+ 								true,
+ 								      &value, &isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_double(value, &aggstate->cast_func_finfo);
+ 
+ 				if (lidx != hidx)
+ 				{
+ 					tuplesort_getdatum(aggstate->sortstate,
+ 										true,
+ 										      &value, &isNull);
+ 					result = (result + to_double(value, &aggstate->cast_func_finfo)) / 2.0;
+ 				}
+ 				break;
+ 			}
+ 		}
+ 
+ 		tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_FLOAT8(result);
+ 	}
+ 	else
+ 		PG_RETURN_NULL();
+ }
+ 
+ /*
+  * Used for reading values from tuplesort. The value has to be
+  * Numeric or cast function is defined (and used).
+  */
+ static Numeric
+ to_numeric(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetNumeric(value);
+ 
+ 	return DatumGetNumeric(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ /*
+  * Used as final function for median when result is numeric.
+  */
+ Datum
+ median_finalfn_numeric(PG_FUNCTION_ARGS)
+ {
+ 	MedianAggState *aggstate;
+ 
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   value;
+ 		bool	isNull;
+ 		int		i = 1;
+ 		Numeric result = NULL;		/* be compiler quiet */
+ 
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		tuplesort_performsort(aggstate->sortstate);
+ 		
+ 		while (tuplesort_getdatum(aggstate->sortstate, 
+ 								true,
+ 								      &value, &isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_numeric(value, &aggstate->cast_func_finfo);
+ 
+ 				if (lidx != hidx)
+ 				{
+ 					Numeric stack;
+ 
+ 					tuplesort_getdatum(aggstate->sortstate,
+ 										true,
+ 										      &value, &isNull);
+ 					stack = to_numeric(value, &aggstate->cast_func_finfo);
+ 					stack = DatumGetNumeric(DirectFunctionCall2(numeric_add,
+ 												NumericGetDatum(stack),
+ 												NumericGetDatum(result)));
+ 					result = DatumGetNumeric(DirectFunctionCall2(numeric_div,
+ 												NumericGetDatum(stack),
+ 												DirectFunctionCall1(float4_numeric,
+ 																    Float4GetDatum(2.0))));
+ 				}
+ 				break;
+ 			}
+ 		}
+ 
+ 		tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_NUMERIC(result);
+ 	}
+ 	else
+ 		PG_RETURN_NULL();
+ }
*** ./src/include/catalog/pg_aggregate.h.orig	2010-08-05 20:21:17.000000000 +0200
--- ./src/include/catalog/pg_aggregate.h	2010-09-23 14:32:25.572576527 +0200
***************
*** 226,231 ****
--- 226,239 ----
  /* text */
  DATA(insert ( 3538	string_agg_transfn	string_agg_finalfn		0	2281	_null_ ));
  
+ /* median */
+ DATA(insert ( 3123	median_double_transfn	median_finalfn_double		0	2281	_null_ ));
+ DATA(insert ( 3124	median_float_transfn	median_finalfn_double		0	2281	_null_ ));
+ DATA(insert ( 3125	median_numeric_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3126	median_int8_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3127	median_int4_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3128	median_int2_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ 
  /*
   * prototypes for functions in pg_aggregate.c
   */
*** ./src/include/catalog/pg_proc.h.orig	2010-08-13 20:36:25.000000000 +0200
--- ./src/include/catalog/pg_proc.h	2010-09-23 14:36:55.381451421 +0200
***************
*** 4823,4828 ****
--- 4823,4857 ----
  DATA(insert OID = 3114 (  nth_value		PGNSP PGUID 12 1 0 0 f t f t f i 2 0 2283 "2283 23" _null_ _null_ _null_ _null_ window_nth_value _null_ _null_ _null_ ));
  DESCR("fetch the Nth row value");
  
+ /* median aggregate */
+ DATA(insert OID = 3115 ( median_numeric_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 1700" _null_ _null_ _null_ _null_	median_numeric_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for numeric");
+ DATA(insert OID = 3116 ( median_int8_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 20" _null_ _null_ _null_ _null_	median_int8_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for bigint");
+ DATA(insert OID = 3117 ( median_int4_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 23" _null_ _null_ _null_ _null_	median_int4_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for int");
+ DATA(insert OID = 3118 ( median_int2_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 21" _null_ _null_ _null_ _null_	median_int2_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for smallint");
+ DATA(insert OID = 3119 ( median_double_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 701" _null_ _null_ _null_ _null_	median_double_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for double precision");
+ DATA(insert OID = 3120 ( median_float_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 700" _null_ _null_ _null_ _null_	median_float_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for real");
+ DATA(insert OID = 3121 ( median_finalfn_double			PGNSP PGUID 12 1 0 0 f f f f f i 1 0 701 "2281" _null_ _null_ _null_ _null_	median_finalfn_double _null_ _null_ _null_ ));
+ DESCR("median final function with double precision result");
+ DATA(insert OID = 3122 ( median_finalfn_numeric			PGNSP PGUID 12 1 0 0 f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_	median_finalfn_numeric _null_ _null_ _null_ ));
+ DESCR("median final function with numeric result");
+ DATA(insert OID = 3123 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 701 "701" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3124 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 701 "700" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3125 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "1700" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3126 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "20" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3127 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "23" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3128 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "21" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
  
  /*
   * Symbolic values for provolatile column: these indicate whether the result
*** ./src/include/utils/builtins.h.orig	2010-08-10 23:51:00.000000000 +0200
--- ./src/include/utils/builtins.h	2010-09-23 14:22:16.929453299 +0200
***************
*** 924,929 ****
--- 924,937 ----
  extern Datum int8_avg(PG_FUNCTION_ARGS);
  extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
  extern Datum hash_numeric(PG_FUNCTION_ARGS);
+ extern Datum median_numeric_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int8_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int4_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int2_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_double_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_float_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_finalfn_double(PG_FUNCTION_ARGS);
+ extern Datum median_finalfn_numeric(PG_FUNCTION_ARGS);
  
  /* ri_triggers.c */
  extern Datum RI_FKey_check_ins(PG_FUNCTION_ARGS);
*** ./src/test/regress/expected/numeric.out.orig	2009-08-10 20:29:27.000000000 +0200
--- ./src/test/regress/expected/numeric.out	2010-09-23 14:44:18.000000000 +0200
***************
*** 1372,1374 ****
--- 1372,1402 ----
   12345678901234567890
  (1 row)
  
+ -- median test
+ create table median_test (a int, b bigint, c smallint, d numeric, e double precision, f real);
+ insert into median_test select i,i,i,i,i,i from generate_series(1,10) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+        median       |       median       |       median       |       median       | median | median 
+ --------------------+--------------------+--------------------+--------------------+--------+--------
+  5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 |    5.5 |    5.5
+ (1 row)
+ 
+ truncate table median_test;
+ insert into median_test select i,i,i,i,i,i from generate_series(1,11) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+  median | median | median | median | median | median 
+ --------+--------+--------+--------+--------+--------
+       6 |      6 |      6 |      6 |      6 |      6
+ (1 row)
+ 
+ drop table median_test;
*** ./src/test/regress/sql/numeric.sql.orig	2009-08-10 20:29:27.000000000 +0200
--- ./src/test/regress/sql/numeric.sql	2010-09-23 14:49:45.480455045 +0200
***************
*** 824,826 ****
--- 824,845 ----
  select 12345678901234567890 / 123;
  select div(12345678901234567890, 123);
  select div(12345678901234567890, 123) * 123 + 12345678901234567890 % 123;
+ 
+ -- median test
+ create table median_test (a int, b bigint, c smallint, d numeric, e double precision, f real);
+ insert into median_test select i,i,i,i,i,i from generate_series(1,10) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+ truncate table median_test;
+ insert into median_test select i,i,i,i,i,i from generate_series(1,11) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+ drop table median_test;

*** ./doc/src/sgml/func.sgml.orig	2010-08-17 06:37:20.000000000 +0200
--- ./doc/src/sgml/func.sgml	2010-09-23 15:03:10.021576906 +0200
***************
*** 10304,10309 ****
--- 10304,10329 ----
  
       <row>
        <entry>
+        <indexterm>
+         <primary>Arithmetic median</primary>
+         <secondary>median</secondary>
+        </indexterm>
+        <function>median(<replaceable class="parameter">expression</replaceable>)</function>
+       </entry>
+       <entry>
+        <type>smallint</type>, <type>int</type>,
+        <type>bigint</type>, <type>real</type>, <type>double
+        precision</type>, or <type>numeric</type>
+       </entry>
+       <entry>
+        <type>double precision</type> for floating-point arguments,
+        otherwise <type>numeric</type>
+       </entry>
+       <entry>arithmetic median</entry>
+      </row>
+ 
+      <row>
+       <entry>
         <function>regr_avgx(<replaceable class="parameter">Y</replaceable>, <replaceable class="parameter">X</replaceable>)</function>
        </entry>
        <entry>
*** ./src/backend/utils/adt/numeric.c.orig	2010-08-04 19:33:09.000000000 +0200
--- ./src/backend/utils/adt/numeric.c	2010-09-23 15:24:04.025453940 +0200
***************
*** 30,39 ****
--- 30,42 ----
  #include "catalog/pg_type.h"
  #include "libpq/pqformat.h"
  #include "miscadmin.h"
+ #include "parser/parse_coerce.h"
+ #include "parser/parse_oper.h"
  #include "utils/array.h"
  #include "utils/builtins.h"
  #include "utils/int8.h"
  #include "utils/numeric.h"
+ #include "utils/tuplesort.h"
  
  /* ----------
   * Uncomment the following to enable compilation of dump_numeric()
***************
*** 144,149 ****
--- 147,161 ----
  	union NumericChoice	choice;	/* choice of format */
  };
  
+ /*
+  * used as type of state variable median's function
+  */
+ typedef struct
+ {
+ 	int	nelems;		/* number of valid entries */
+ 	Tuplesortstate *sortstate;
+ 	FmgrInfo	cast_func_finfo;
+ } MedianAggState;
  
  /*
   * Interpretation of high bits.
***************
*** 6173,6175 ****
--- 6185,6467 ----
  	var->digits = digits;
  	var->ndigits = ndigits;
  }
+ 
+ static MedianAggState *
+ makeMedianAggState(FunctionCallInfo fcinfo, Oid valtype, Oid targettype)
+ {
+ 	MemoryContext oldctx;
+ 	MemoryContext aggcontext;
+ 	MedianAggState *aggstate;
+ 	Oid	sortop,
+ 			castfunc;
+ 	CoercionPathType		pathtype;
+ 
+ 	/*
+ 	 * We cannot to allow a median function under WindowAgg State.
+ 	 * This content needs a repetetive a calling of final function,
+ 	 * what isn't possible now with using a tuplestore. 
+ 	 * tuplesort_performsort can be called only once, and some has
+ 	 * to call a tuplesort_end.
+ 	 */
+ 	if (fcinfo->context && IsA(fcinfo->context, WindowAggState))
+ 	{
+ 		/* cannot be called inside like windowAggregates */
+ 		elog(ERROR, "median_transfn called as windows aggregates");
+ 	}
+ 
+ 	if (!AggCheckCallContext(fcinfo, &aggcontext))
+ 	{
+ 		/* cannot be called directly because of internal-type argument */
+ 		elog(ERROR, "median_transfn called in non-aggregate context");
+ 	}
+ 
+ 	oldctx = MemoryContextSwitchTo(aggcontext);
+ 
+ 	aggstate = (MedianAggState *) palloc0(sizeof(MedianAggState));
+ 
+ 	valtype = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ 	get_sort_group_operators(valtype,
+ 						    true, false, false,
+ 						    &sortop, NULL, NULL);
+ 
+ 	aggstate->sortstate = tuplesort_begin_datum(valtype,
+ 							sortop,
+ 							SORTBY_NULLS_DEFAULT,
+ 							work_mem, true);
+ 
+ 	MemoryContextSwitchTo(oldctx);
+ 
+ 	if (valtype != targettype)
+ 	{
+ 		/* find a cast function */
+ 		pathtype = find_coercion_pathway(targettype, valtype,
+ 									COERCION_EXPLICIT,
+ 									&castfunc);
+ 		if (pathtype == COERCION_PATH_FUNC)
+ 		{
+ 			Assert(OidIsValid(castfunc));
+ 			fmgr_info_cxt(castfunc, &aggstate->cast_func_finfo,
+ 									    aggcontext);
+ 		} 
+ 		else if (pathtype == COERCION_PATH_RELABELTYPE)
+ 		{
+ 			aggstate->cast_func_finfo.fn_oid = InvalidOid;
+ 		}
+ 		else 
+ 			elog(ERROR, "no conversion function from %s %s",
+ 					 format_type_be(valtype),
+ 					 format_type_be(targettype));
+ 	}
+ 
+ 	return aggstate;
+ }
+ 
+ /*
+  *  append a non NULL value to tuplesort
+  */
+ static Datum
+ common_median_transfn(FunctionCallInfo fcinfo, Oid typoid, Oid targetoid)
+ {
+ 	MedianAggState *aggstate;
+ 	
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (!PG_ARGISNULL(1))
+ 	{
+ 		if (aggstate == NULL)
+ 			aggstate = makeMedianAggState(fcinfo, typoid, targetoid);
+ 
+ 		tuplesort_putdatum(aggstate->sortstate, PG_GETARG_DATUM(1), false);
+ 		aggstate->nelems++;
+ 	}
+ 
+ 	PG_RETURN_POINTER(aggstate);
+ }
+ 
+ /*
+  * just wrappers to be opr sanity checks happy
+  */
+ Datum
+ median_numeric_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						NUMERICOID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int8_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						INT8OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int4_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						INT4OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int2_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						INT2OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_double_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						FLOAT8OID, FLOAT8OID);
+ }
+ 
+ Datum
+ median_float_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						FLOAT4OID, FLOAT8OID);
+ }
+ 
+ /*
+  * Used for reading values from tuplesort. The value has to be
+  * double or cast function is defined (and used).
+  */
+ static double 
+ to_double(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetFloat8(value);
+ 
+ 	return DatumGetFloat8(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ /*
+  * Used as final function for median when result is double.
+  */
+ Datum
+ median_finalfn_double(PG_FUNCTION_ARGS)
+ {
+ 	MedianAggState *aggstate;
+ 
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   value;
+ 		bool	isNull;
+ 		int		i = 1;
+ 		double	result = 0;
+ 
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		tuplesort_performsort(aggstate->sortstate);
+ 
+ 		while (tuplesort_getdatum(aggstate->sortstate, 
+ 								true,
+ 								      &value, &isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_double(value, &aggstate->cast_func_finfo);
+ 
+ 				if (lidx != hidx)
+ 				{
+ 					tuplesort_getdatum(aggstate->sortstate,
+ 										true,
+ 										      &value, &isNull);
+ 					result = (result + to_double(value, &aggstate->cast_func_finfo)) / 2.0;
+ 				}
+ 				break;
+ 			}
+ 		}
+ 
+ 		tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_FLOAT8(result);
+ 	}
+ 	else
+ 		PG_RETURN_NULL();
+ }
+ 
+ /*
+  * Used for reading values from tuplesort. The value has to be
+  * Numeric or cast function is defined (and used).
+  */
+ static Numeric
+ to_numeric(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetNumeric(value);
+ 
+ 	return DatumGetNumeric(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ /*
+  * Used as final function for median when result is numeric.
+  */
+ Datum
+ median_finalfn_numeric(PG_FUNCTION_ARGS)
+ {
+ 	MedianAggState *aggstate;
+ 
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   value;
+ 		bool	isNull;
+ 		int		i = 1;
+ 		Numeric result = NULL;		/* be compiler quiet */
+ 
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		tuplesort_performsort(aggstate->sortstate);
+ 		
+ 		while (tuplesort_getdatum(aggstate->sortstate, 
+ 								true,
+ 								      &value, &isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_numeric(value, &aggstate->cast_func_finfo);
+ 
+ 				if (lidx != hidx)
+ 				{
+ 					Numeric stack;
+ 
+ 					tuplesort_getdatum(aggstate->sortstate,
+ 										true,
+ 										      &value, &isNull);
+ 					stack = to_numeric(value, &aggstate->cast_func_finfo);
+ 					stack = DatumGetNumeric(DirectFunctionCall2(numeric_add,
+ 												NumericGetDatum(stack),
+ 												NumericGetDatum(result)));
+ 					result = DatumGetNumeric(DirectFunctionCall2(numeric_div,
+ 												NumericGetDatum(stack),
+ 												DirectFunctionCall1(float4_numeric,
+ 																    Float4GetDatum(2.0))));
+ 				}
+ 				break;
+ 			}
+ 		}
+ 
+ 		tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_NUMERIC(result);
+ 	}
+ 	else
+ 		PG_RETURN_NULL();
+ }
*** ./src/include/catalog/pg_aggregate.h.orig	2010-08-05 20:21:17.000000000 +0200
--- ./src/include/catalog/pg_aggregate.h	2010-09-23 14:32:25.572576527 +0200
***************
*** 226,231 ****
--- 226,239 ----
  /* text */
  DATA(insert ( 3538	string_agg_transfn	string_agg_finalfn		0	2281	_null_ ));
  
+ /* median */
+ DATA(insert ( 3123	median_double_transfn	median_finalfn_double		0	2281	_null_ ));
+ DATA(insert ( 3124	median_float_transfn	median_finalfn_double		0	2281	_null_ ));
+ DATA(insert ( 3125	median_numeric_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3126	median_int8_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3127	median_int4_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3128	median_int2_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ 
  /*
   * prototypes for functions in pg_aggregate.c
   */
*** ./src/include/catalog/pg_proc.h.orig	2010-08-13 20:36:25.000000000 +0200
--- ./src/include/catalog/pg_proc.h	2010-09-23 14:36:55.381451421 +0200
***************
*** 4823,4828 ****
--- 4823,4857 ----
  DATA(insert OID = 3114 (  nth_value		PGNSP PGUID 12 1 0 0 f t f t f i 2 0 2283 "2283 23" _null_ _null_ _null_ _null_ window_nth_value _null_ _null_ _null_ ));
  DESCR("fetch the Nth row value");
  
+ /* median aggregate */
+ DATA(insert OID = 3115 ( median_numeric_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 1700" _null_ _null_ _null_ _null_	median_numeric_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for numeric");
+ DATA(insert OID = 3116 ( median_int8_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 20" _null_ _null_ _null_ _null_	median_int8_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for bigint");
+ DATA(insert OID = 3117 ( median_int4_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 23" _null_ _null_ _null_ _null_	median_int4_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for int");
+ DATA(insert OID = 3118 ( median_int2_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 21" _null_ _null_ _null_ _null_	median_int2_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for smallint");
+ DATA(insert OID = 3119 ( median_double_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 701" _null_ _null_ _null_ _null_	median_double_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for double precision");
+ DATA(insert OID = 3120 ( median_float_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 700" _null_ _null_ _null_ _null_	median_float_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for real");
+ DATA(insert OID = 3121 ( median_finalfn_double			PGNSP PGUID 12 1 0 0 f f f f f i 1 0 701 "2281" _null_ _null_ _null_ _null_	median_finalfn_double _null_ _null_ _null_ ));
+ DESCR("median final function with double precision result");
+ DATA(insert OID = 3122 ( median_finalfn_numeric			PGNSP PGUID 12 1 0 0 f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_	median_finalfn_numeric _null_ _null_ _null_ ));
+ DESCR("median final function with numeric result");
+ DATA(insert OID = 3123 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 701 "701" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3124 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 701 "700" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3125 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "1700" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3126 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "20" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3127 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "23" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3128 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "21" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
  
  /*
   * Symbolic values for provolatile column: these indicate whether the result
*** ./src/include/utils/builtins.h.orig	2010-08-10 23:51:00.000000000 +0200
--- ./src/include/utils/builtins.h	2010-09-23 14:22:16.929453299 +0200
***************
*** 924,929 ****
--- 924,937 ----
  extern Datum int8_avg(PG_FUNCTION_ARGS);
  extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
  extern Datum hash_numeric(PG_FUNCTION_ARGS);
+ extern Datum median_numeric_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int8_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int4_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int2_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_double_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_float_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_finalfn_double(PG_FUNCTION_ARGS);
+ extern Datum median_finalfn_numeric(PG_FUNCTION_ARGS);
  
  /* ri_triggers.c */
  extern Datum RI_FKey_check_ins(PG_FUNCTION_ARGS);
*** ./src/test/regress/expected/numeric.out.orig	2009-08-10 20:29:27.000000000 +0200
--- ./src/test/regress/expected/numeric.out	2010-09-23 14:44:18.000000000 +0200
***************
*** 1372,1374 ****
--- 1372,1402 ----
   12345678901234567890
  (1 row)
  
+ -- median test
+ create table median_test (a int, b bigint, c smallint, d numeric, e double precision, f real);
+ insert into median_test select i,i,i,i,i,i from generate_series(1,10) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+        median       |       median       |       median       |       median       | median | median 
+ --------------------+--------------------+--------------------+--------------------+--------+--------
+  5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 |    5.5 |    5.5
+ (1 row)
+ 
+ truncate table median_test;
+ insert into median_test select i,i,i,i,i,i from generate_series(1,11) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+  median | median | median | median | median | median 
+ --------+--------+--------+--------+--------+--------
+       6 |      6 |      6 |      6 |      6 |      6
+ (1 row)
+ 
+ drop table median_test;
*** ./src/test/regress/sql/numeric.sql.orig	2009-08-10 20:29:27.000000000 +0200
--- ./src/test/regress/sql/numeric.sql	2010-09-23 14:49:45.480455045 +0200
***************
*** 824,826 ****
--- 824,845 ----
  select 12345678901234567890 / 123;
  select div(12345678901234567890, 123);
  select div(12345678901234567890, 123) * 123 + 12345678901234567890 % 123;
+ 
+ -- median test
+ create table median_test (a int, b bigint, c smallint, d numeric, e double precision, f real);
+ insert into median_test select i,i,i,i,i,i from generate_series(1,10) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+ truncate table median_test;
+ insert into median_test select i,i,i,i,i,i from generate_series(1,11) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+ drop table median_test;

*** ./doc/src/sgml/func.sgml.orig	2010-09-09 02:48:22.000000000 +0200
--- ./doc/src/sgml/func.sgml	2010-09-25 21:22:01.493460314 +0200
***************
*** 10386,10391 ****
--- 10386,10411 ----
  
       <row>
        <entry>
+        <indexterm>
+         <primary>Arithmetic median</primary>
+         <secondary>median</secondary>
+        </indexterm>
+        <function>median(<replaceable class="parameter">expression</replaceable>)</function>
+       </entry>
+       <entry>
+        <type>smallint</type>, <type>int</type>,
+        <type>bigint</type>, <type>real</type>, <type>double
+        precision</type>, or <type>numeric</type>
+       </entry>
+       <entry>
+        <type>double precision</type> for floating-point arguments,
+        otherwise <type>numeric</type>
+       </entry>
+       <entry>arithmetic median</entry>
+      </row>
+ 
+      <row>
+       <entry>
         <function>regr_avgx(<replaceable class="parameter">Y</replaceable>, <replaceable class="parameter">X</replaceable>)</function>
        </entry>
        <entry>
*** ./src/backend/utils/adt/median.c.orig	2010-09-25 22:23:01.000000000 +0200
--- ./src/backend/utils/adt/median.c	2010-09-26 12:16:40.798602994 +0200
***************
*** 0 ****
--- 1,649 ----
+ #include "postgres.h"
+ 
+ #include "fmgr.h"
+ #include "miscadmin.h"
+ #include <limits.h>
+ 
+ #include "access/nbtree.h"
+ #include "catalog/pg_type.h"
+ #include "catalog/pg_index.h"
+ #include "parser/parse_coerce.h"
+ #include "parser/parse_oper.h"
+ #include "utils/builtins.h"
+ #include "utils/datum.h"
+ #include "utils/lsyscache.h"
+ #include "utils/memutils.h"
+ #include "utils/numeric.h"
+ #include "utils/tuplesort.h"
+ 
+ /* 
+  * just memory storage - store a Datum to memory. Allows repetetive
+  * sort and append a new value.
+  */
+ typedef struct
+ {
+ 	void *tuple;
+ 	Datum	datum1;
+ 	bool	isnull1;
+ } SortTuple;
+ 
+ /*
+  * Simple sort storage - used just only Datums. 
+  */
+ typedef struct
+ {
+ 	long	allowedMem;
+ 	long	availMem;
+ 	MemoryContext	sortcontext;
+ 
+ 	SortTuple *memtuples;
+ 	int	memtupcount;
+ 	int	memtupsize;
+ 	int	current;
+ 
+ 	Oid		datumType;
+ 	FmgrInfo	sortOpFn;
+ 	int		sortFnFlags;
+ 	int	datumTypLen;
+ 	bool	datumTypByVal;
+ } Memsortstate;
+ 
+ /*
+  * used as type of state variable median's function. It uses a 
+  * tuplesort as safe and inteligent storage. But we cannot to use
+  * a tuplesort under window aggregate context. Then we have to use a 
+  * array of Datum. The API is similar to tuplesort API.
+  */
+ typedef struct
+ {
+ 	int	nelems;		/* number of valid entries */
+ 	bool	use_tuplesort;
+ 	Tuplesortstate *sortstate;
+ 	Memsortstate *memsortstate;
+ 	FmgrInfo	cast_func_finfo;
+ 	bool	datumTypByVal;
+ } MedianAggState;
+ 
+ #define USEMEM(state, amt)	((state)->availMem -= (amt))
+ #define LACKMEM(state)		((state)->availMem < 0)
+ #define FREEMEM(state, amt)	((state)->availMem += (amt))
+ 
+ static int compare_datum(const SortTuple *a, const SortTuple *b, Memsortstate *state);
+ 
+ 
+ static Memsortstate *
+ memsort_begin_datum(Oid datumType, 
+ 				    Oid sortOperator, bool nullsFirstFlag,
+ 				    int workMem)
+ {
+ 	MemoryContext sortcontext;
+ 	MemoryContext oldcontext;
+ 	bool	reverse;
+ 	Oid	sortFunction;
+ 	Memsortstate *state;
+ 	int16		typlen;
+ 	bool		typbyval;
+ 
+ 	/* 
+ 	 * Create a working memory context for this sort operation. All
+ 	 * data needed by the sort will live iside this context.
+ 	 */
+ 	sortcontext = AllocSetContextCreate(CurrentMemoryContext,
+ 								    "MemorySort",
+ 								    ALLOCSET_DEFAULT_MINSIZE,
+ 								    ALLOCSET_DEFAULT_INITSIZE,
+ 								    ALLOCSET_DEFAULT_MAXSIZE);
+ 	oldcontext = MemoryContextSwitchTo(sortcontext);
+ 	state = (Memsortstate *) palloc0(sizeof(Memsortstate));
+ 
+ 	state->allowedMem = workMem * 1024L;
+ 	state->availMem = state->allowedMem;
+ 	state->memtupcount = 0;
+ 	state->memtupsize = 1024;
+ 	state->memtuples = (SortTuple*) palloc(state->memtupsize * sizeof(SortTuple));
+ 	state->sortcontext = sortcontext;
+ 
+ 	USEMEM(state, GetMemoryChunkSpace(state->memtuples));
+ 
+ 	if (LACKMEM(state))
+ 		elog(ERROR, "insufficient memory allowed for sort");
+ 
+ 	state->datumType = datumType;
+ 	if (!get_compare_function_for_ordering_op(sortOperator, &sortFunction, &reverse))
+ 		elog(ERROR, "operator %u is not a valid ordering operator",
+ 			    sortOperator);
+ 	fmgr_info(sortFunction, &state->sortOpFn);
+ 
+ 	/* set ordering flags */
+ 	state->sortFnFlags = reverse ? SK_BT_DESC : 0;
+ 	if (nullsFirstFlag)
+ 		state->sortFnFlags |= SK_BT_NULLS_FIRST;
+ 	
+ 	/* lookup necessary attributies of the datum type */
+ 	get_typlenbyval(datumType, &typlen, &typbyval);
+ 	state->datumTypLen = typlen;
+ 	state->datumTypByVal = typbyval;
+ 
+ 	MemoryContextSwitchTo(oldcontext);
+ 
+ 	return state;
+ }
+ 
+ static void
+ freeDatum(Datum value, bool typByVal, bool isNull)
+ {
+ 	if (!typByVal && !isNull)
+ 	{
+ 		Pointer	s = DatumGetPointer(value);
+ 
+ 		pfree(s);
+ 	}
+ }
+ 
+ /*
+  * append a datum
+  */
+ static void
+ memsort_putdatum(Memsortstate *state, Datum val, bool isNull)
+ {
+ 	MemoryContext	oldcontext = MemoryContextSwitchTo(state->sortcontext);
+ 	SortTuple stup;
+ 
+ 	if (isNull || state->datumTypByVal)
+ 	{
+ 		stup.datum1 = val;
+ 		stup.isnull1 = isNull;
+ 		stup.tuple = NULL;
+ 	}
+ 	else
+ 	{
+ 		stup.datum1 = datumCopy(val, state->datumTypByVal, state->datumTypLen);
+ 		stup.isnull1 = false;
+ 		stup.tuple = DatumGetPointer(stup.datum1);
+ 		USEMEM(state, GetMemoryChunkSpace(stup.tuple));
+ 	}
+ 
+ 	if (state->memtupcount == state->memtupsize)
+ 	{
+ 		if (state->availMem <= (long) (state->memtupsize * sizeof(SortTuple)))
+ 		elog(ERROR, "unexpected out-of-memory situation during sort");
+ 		if ((Size) (state->memtupsize * 2) >= MaxAllocSize / sizeof(SortTuple))
+ 			elog(ERROR, "unexpected out-of-memory situation during sort");
+ 
+ 		FREEMEM(state, GetMemoryChunkSpace(state->memtuples));
+ 		state->memtupsize *= 2;
+ 		state->memtuples = (SortTuple *)
+ 			    repalloc(state->memtuples, state->memtupsize * sizeof(SortTuple));
+ 		USEMEM(state, GetMemoryChunkSpace(state->memtuples));
+ 		if (LACKMEM(state))
+ 			elog(ERROR, "unexpected out-of-memory situation during sort");
+ 	}
+ 
+ 	state->memtuples[state->memtupcount++] = stup;
+ 	MemoryContextSwitchTo(oldcontext);
+ }
+ 
+ /*
+  * sort a current stored tuples
+  */
+ static void
+ memsort_performsort(Memsortstate *state)
+ {
+ 	if (state->memtupcount > 1)
+ 		qsort_arg((void *) state->memtuples,
+ 				  state->memtupcount,
+ 				  sizeof(SortTuple),
+ 				  (qsort_arg_comparator) compare_datum,
+ 				  (void *) state);
+ 
+ 	state->current = 0;
+ }
+ 
+ static bool
+ memsort_getdatum(Memsortstate *state, 
+ 					Datum *val, bool *isNull)
+ {
+ 	SortTuple *stup;
+ 	MemoryContext	oldcontext = MemoryContextSwitchTo(state->sortcontext);
+ 
+ 	if (state->current < state->memtupcount)
+ 	{
+ 		stup = &state->memtuples[state->current++];
+ 		if (stup->isnull1 || state->datumTypByVal)
+ 		{
+ 			*val = stup->datum1;
+ 			*isNull = stup->isnull1;
+ 		}
+ 		else
+ 		{
+ 			*val = datumCopy(stup->datum1, false, state->datumTypLen);
+ 			*isNull = false;
+ 		}
+ 
+ 		MemoryContextSwitchTo(oldcontext);
+ 
+ 		return true;
+ 	}
+ 
+ 	MemoryContextSwitchTo(oldcontext);
+ 
+ 	return false;
+ }
+ 
+ /*
+  * Inline-able copy of FunctionCall2() to save some cycles in sorting.
+  */
+ static inline Datum
+ myFunctionCall2(FmgrInfo *flinfo, Datum arg1, Datum arg2)
+ {
+ 	FunctionCallInfoData fcinfo;
+ 	Datum		result;
+ 
+ 	InitFunctionCallInfoData(fcinfo, flinfo, 2, NULL, NULL);
+ 
+ 	fcinfo.arg[0] = arg1;
+ 	fcinfo.arg[1] = arg2;
+ 	fcinfo.argnull[0] = false;
+ 	fcinfo.argnull[1] = false;
+ 
+ 	result = FunctionCallInvoke(&fcinfo);
+ 
+ 	/* Check for null result, since caller is clearly not expecting one */
+ 	if (fcinfo.isnull)
+ 		elog(ERROR, "function %u returned NULL", fcinfo.flinfo->fn_oid);
+ 
+ 	return result;
+ }
+ 
+ /*
+  * Apply a sort function (by now converted to fmgr lookup form)
+  * and return a 3-way comparison result.  This takes care of handling
+  * reverse-sort and NULLs-ordering properly.  We assume that DESC and
+  * NULLS_FIRST options are encoded in sk_flags the same way btree does it.
+  */
+ static inline int32
+ inlineApplySortFunction(FmgrInfo *sortFunction, int sk_flags,
+ 						Datum datum1, bool isNull1,
+ 						Datum datum2, bool isNull2)
+ {
+ 	int32		compare;
+ 
+ 	if (isNull1)
+ 	{
+ 		if (isNull2)
+ 			compare = 0;		/* NULL "=" NULL */
+ 		else if (sk_flags & SK_BT_NULLS_FIRST)
+ 			compare = -1;		/* NULL "<" NOT_NULL */
+ 		else
+ 			compare = 1;		/* NULL ">" NOT_NULL */
+ 	}
+ 	else if (isNull2)
+ 	{
+ 		if (sk_flags & SK_BT_NULLS_FIRST)
+ 			compare = 1;		/* NOT_NULL ">" NULL */
+ 		else
+ 			compare = -1;		/* NOT_NULL "<" NULL */
+ 	}
+ 	else
+ 	{
+ 		compare = DatumGetInt32(myFunctionCall2(sortFunction,
+ 												datum1, datum2));
+ 
+ 		if (sk_flags & SK_BT_DESC)
+ 			compare = -compare;
+ 	}
+ 
+ 	return compare;
+ }
+ 
+ static int 
+ compare_datum(const SortTuple *a, const SortTuple *b, Memsortstate *state)
+ {
+ 	/* Allow interrupting long sorts */
+ 	CHECK_FOR_INTERRUPTS();
+ 
+ 	return inlineApplySortFunction(&state->sortOpFn, state->sortFnFlags,
+ 								    a->datum1, a->isnull1,
+ 								    b->datum1, b->isnull1);
+ }
+ 
+ static MedianAggState *
+ makeMedianAggState(FunctionCallInfo fcinfo, Oid valtype, Oid targettype)
+ {
+ 	MemoryContext oldctx;
+ 	MemoryContext aggcontext;
+ 	MedianAggState *aggstate;
+ 	Oid	sortop,
+ 			castfunc;
+ 	CoercionPathType		pathtype;
+ 	int16	typlen;
+ 	bool		typbyval;
+ 
+ 	if (!AggCheckCallContext(fcinfo, &aggcontext))
+ 	{
+ 		/* cannot be called directly because of internal-type argument */
+ 		elog(ERROR, "median_transfn called in non-aggregate context");
+ 	}
+ 
+ 	oldctx = MemoryContextSwitchTo(aggcontext);
+ 
+ 	aggstate = (MedianAggState *) palloc0(sizeof(MedianAggState));
+ 
+ 	valtype = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ 	get_sort_group_operators(valtype,
+ 						    true, false, false,
+ 						    &sortop, NULL, NULL);
+ 
+ 	/* lookup necessary attributies of the datum type, used for datumFree */
+ 	get_typlenbyval(valtype, &typlen, &typbyval);
+ 	aggstate->datumTypByVal = typbyval;
+ 
+ 	/*
+ 	 * use a Datum mem sort insted a Tuplesort under windows aggregates
+ 	 */
+ 	if (fcinfo->context && IsA(fcinfo->context, WindowAggState))
+ 	{
+ 		aggstate->memsortstate = memsort_begin_datum(valtype,
+ 							sortop,
+ 							SORTBY_NULLS_DEFAULT,
+ 							work_mem);
+ 		aggstate->use_tuplesort = false;
+ 	}
+ 	else
+ 	{
+ 		aggstate->sortstate = tuplesort_begin_datum(valtype,
+ 							sortop,
+ 							SORTBY_NULLS_DEFAULT,
+ 							work_mem, true);
+ 		aggstate->use_tuplesort = true;
+ 	}
+ 
+ 	MemoryContextSwitchTo(oldctx);
+ 
+ 	if (valtype != targettype)
+ 	{
+ 		/* find a cast function */
+ 		pathtype = find_coercion_pathway(targettype, valtype,
+ 									COERCION_EXPLICIT,
+ 									&castfunc);
+ 		if (pathtype == COERCION_PATH_FUNC)
+ 		{
+ 			Assert(OidIsValid(castfunc));
+ 			fmgr_info_cxt(castfunc, &aggstate->cast_func_finfo,
+ 									    aggcontext);
+ 		} 
+ 		else if (pathtype == COERCION_PATH_RELABELTYPE)
+ 		{
+ 			aggstate->cast_func_finfo.fn_oid = InvalidOid;
+ 		}
+ 		else 
+ 			elog(ERROR, "no conversion function from %s %s",
+ 					 format_type_be(valtype),
+ 					 format_type_be(targettype));
+ 	}
+ 
+ 	return aggstate;
+ }
+ 
+ static void
+ putdatum(MedianAggState *aggstate, Datum val)
+ {
+ 	if (aggstate->use_tuplesort)
+ 	{
+ 		Assert(aggstate->sortstate != NULL);
+ 		tuplesort_putdatum(aggstate->sortstate, val, false);
+ 	}
+ 	else
+ 	{
+ 		Assert(aggstate->memsortstate != NULL);
+ 		memsort_putdatum(aggstate->memsortstate, val, false);
+ 	}
+ }
+ 
+ static void
+ performsort(MedianAggState *aggstate)
+ {
+ 	if (aggstate->use_tuplesort)
+ 	{
+ 		Assert(aggstate->sortstate != NULL);
+ 		tuplesort_performsort(aggstate->sortstate);
+ 	}
+ 	else
+ 	{
+ 		Assert(aggstate->memsortstate != NULL);
+ 		memsort_performsort(aggstate->memsortstate);
+ 	}
+ }
+ 
+ static bool
+ getdatum(MedianAggState *aggstate,
+ 				    Datum *value, bool *isNull)
+ {
+ 	if (aggstate->use_tuplesort)
+ 	{
+ 		Assert(aggstate->sortstate != NULL);
+ 		return tuplesort_getdatum(aggstate->sortstate,
+ 							true,
+ 							      value, isNull);
+ 	}
+ 	else
+ 	{
+ 		Assert(aggstate->memsortstate != NULL);
+ 		return memsort_getdatum(aggstate->memsortstate,
+ 							value, isNull);
+ 	}
+ }
+ 
+ /*
+  *  append a non NULL value to tuplesort
+  */
+ static Datum
+ common_median_transfn(FunctionCallInfo fcinfo, Oid typoid, Oid targetoid)
+ {
+ 	MedianAggState *aggstate;
+ 	
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (!PG_ARGISNULL(1))
+ 	{
+ 		if (aggstate == NULL)
+ 			aggstate = makeMedianAggState(fcinfo, typoid, targetoid);
+ 
+ 		putdatum(aggstate, PG_GETARG_DATUM(1));
+ 		aggstate->nelems++;
+ 	}
+ 
+ 	PG_RETURN_POINTER(aggstate);
+ }
+ 
+ /*
+  * just wrappers to be opr sanity checks happy
+  */
+ Datum
+ median_numeric_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						NUMERICOID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int8_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						INT8OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int4_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						INT4OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int2_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						INT2OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_double_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						FLOAT8OID, FLOAT8OID);
+ }
+ 
+ Datum
+ median_float_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						FLOAT4OID, FLOAT8OID);
+ }
+ 
+ /*
+  * Used for reading values from tuplesort. The value has to be
+  * double or cast function is defined (and used).
+  */
+ static double 
+ to_double(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetFloat8(value);
+ 
+ 	return DatumGetFloat8(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ /*
+  * Used as final function for median when result is double.
+  */
+ Datum
+ median_finalfn_double(PG_FUNCTION_ARGS)
+ {
+ 	MedianAggState *aggstate;
+ 
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   value;
+ 		bool	isNull;
+ 		int		i = 1;
+ 		double	result = 0;
+ 
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		performsort(aggstate);
+ 
+ 		while (getdatum(aggstate, &value, &isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_double(value, &aggstate->cast_func_finfo);
+ 				freeDatum(value, aggstate->datumTypByVal, isNull);
+ 
+ 				if (lidx != hidx)
+ 				{
+ 					getdatum(aggstate, &value, &isNull);
+ 					result = (result + to_double(value, &aggstate->cast_func_finfo)) / 2.0;
+ 					freeDatum(value, aggstate->datumTypByVal, isNull);
+ 				}
+ 				break;
+ 			}
+ 		}
+ 
+ 		/* only for tuplesort call a end function */
+ 		if (aggstate->use_tuplesort)
+ 			tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_FLOAT8(result);
+ 	}
+ 	else
+ 		PG_RETURN_NULL();
+ }
+ 
+ /*
+  * Used for reading values from tuplesort. The value has to be
+  * Numeric or cast function is defined (and used).
+  */
+ static Numeric
+ to_numeric(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetNumeric(value);
+ 
+ 	return DatumGetNumeric(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ /*
+  * Used as final function for median when result is numeric.
+  */
+ Datum
+ median_finalfn_numeric(PG_FUNCTION_ARGS)
+ {
+ 	MedianAggState *aggstate;
+ 
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   a_value;
+ 		bool	a_isNull;
+ 		int		i = 1;
+ 		Numeric result = NULL;		/* be compiler quiet */
+ 
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		performsort(aggstate);
+ 
+ 		while (getdatum(aggstate, &a_value, &a_isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_numeric(a_value, &aggstate->cast_func_finfo);
+ 
+ 				if (lidx != hidx)
+ 				{
+ 					Datum	b_value;
+ 					bool	b_isNull;
+ 					Numeric stack;
+ 
+ 					getdatum(aggstate, &b_value, &b_isNull);
+ 					stack = to_numeric(b_value, &aggstate->cast_func_finfo);
+ 
+ 					stack = DatumGetNumeric(DirectFunctionCall2(numeric_add,
+ 												NumericGetDatum(stack),
+ 												NumericGetDatum(result)));
+ 					result = DatumGetNumeric(DirectFunctionCall2(numeric_div,
+ 												NumericGetDatum(stack),
+ 												DirectFunctionCall1(float4_numeric,
+ 																    Float4GetDatum(2.0))));
+ 					freeDatum(b_value, aggstate->datumTypByVal, b_isNull);
+ 				}
+ 				break;
+ 			}
+ 			else 
+ 				freeDatum(a_value, aggstate->datumTypByVal, a_isNull);
+ 		}
+ 
+ 		/* only for tuplesort call a end function */
+ 		if (aggstate->use_tuplesort)
+ 			tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_NUMERIC(result);
+ 	}
+ 	else
+ 		PG_RETURN_NULL();
+ }
*** ./src/include/catalog/pg_aggregate.h.orig	2010-09-25 21:21:11.081451603 +0200
--- ./src/include/catalog/pg_aggregate.h	2010-09-25 21:22:01.497461866 +0200
***************
*** 226,231 ****
--- 226,239 ----
  /* text */
  DATA(insert ( 3538	string_agg_transfn	string_agg_finalfn		0	2281	_null_ ));
  
+ /* median */
+ DATA(insert ( 3123	median_double_transfn	median_finalfn_double		0	2281	_null_ ));
+ DATA(insert ( 3124	median_float_transfn	median_finalfn_double		0	2281	_null_ ));
+ DATA(insert ( 3125	median_numeric_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3126	median_int8_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3127	median_int4_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3128	median_int2_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ 
  /*
   * prototypes for functions in pg_aggregate.c
   */
*** ./src/include/catalog/pg_proc.h.orig	2010-09-03 03:34:55.000000000 +0200
--- ./src/include/catalog/pg_proc.h	2010-09-25 21:22:01.500452259 +0200
***************
*** 4850,4855 ****
--- 4850,4884 ----
  DATA(insert OID = 3114 (  nth_value		PGNSP PGUID 12 1 0 0 f t f t f i 2 0 2283 "2283 23" _null_ _null_ _null_ _null_ window_nth_value _null_ _null_ _null_ ));
  DESCR("fetch the Nth row value");
  
+ /* median aggregate */
+ DATA(insert OID = 3115 ( median_numeric_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 1700" _null_ _null_ _null_ _null_	median_numeric_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for numeric");
+ DATA(insert OID = 3116 ( median_int8_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 20" _null_ _null_ _null_ _null_	median_int8_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for bigint");
+ DATA(insert OID = 3117 ( median_int4_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 23" _null_ _null_ _null_ _null_	median_int4_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for int");
+ DATA(insert OID = 3118 ( median_int2_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 21" _null_ _null_ _null_ _null_	median_int2_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for smallint");
+ DATA(insert OID = 3119 ( median_double_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 701" _null_ _null_ _null_ _null_	median_double_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for double precision");
+ DATA(insert OID = 3120 ( median_float_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 700" _null_ _null_ _null_ _null_	median_float_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for real");
+ DATA(insert OID = 3121 ( median_finalfn_double			PGNSP PGUID 12 1 0 0 f f f f f i 1 0 701 "2281" _null_ _null_ _null_ _null_	median_finalfn_double _null_ _null_ _null_ ));
+ DESCR("median final function with double precision result");
+ DATA(insert OID = 3122 ( median_finalfn_numeric			PGNSP PGUID 12 1 0 0 f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_	median_finalfn_numeric _null_ _null_ _null_ ));
+ DESCR("median final function with numeric result");
+ DATA(insert OID = 3123 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 701 "701" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3124 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 701 "700" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3125 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "1700" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3126 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "20" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3127 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "23" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3128 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "21" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
  
  /*
   * Symbolic values for provolatile column: these indicate whether the result
*** ./src/include/utils/builtins.h.orig	2010-09-03 03:34:55.000000000 +0200
--- ./src/include/utils/builtins.h	2010-09-25 21:22:01.530706224 +0200
***************
*** 934,939 ****
--- 934,947 ----
  extern Datum int8_avg(PG_FUNCTION_ARGS);
  extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
  extern Datum hash_numeric(PG_FUNCTION_ARGS);
+ extern Datum median_numeric_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int8_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int4_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int2_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_double_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_float_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_finalfn_double(PG_FUNCTION_ARGS);
+ extern Datum median_finalfn_numeric(PG_FUNCTION_ARGS);
  
  /* ri_triggers.c */
  extern Datum RI_FKey_check_ins(PG_FUNCTION_ARGS);
*** ./src/test/regress/expected/numeric.out.orig	2010-09-25 21:21:11.086476751 +0200
--- ./src/test/regress/expected/numeric.out	2010-09-26 12:27:45.000000000 +0200
***************
*** 1372,1374 ****
--- 1372,1423 ----
   12345678901234567890
  (1 row)
  
+ -- median test
+ create table median_test (a int, b bigint, c smallint, d numeric, e double precision, f real);
+ insert into median_test select i,i,i,i,i,i from generate_series(1,10) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+        median       |       median       |       median       |       median       | median | median 
+ --------------------+--------------------+--------------------+--------------------+--------+--------
+  5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 |    5.5 |    5.5
+ (1 row)
+ 
+ truncate table median_test;
+ insert into median_test select i,i,i,i,i,i from generate_series(1,11) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+  median | median | median | median | median | median 
+ --------+--------+--------+--------+--------+--------
+       6 |      6 |      6 |      6 |      6 |      6
+ (1 row)
+ 
+ select median(a) over (order by a),
+        median(b) over (order by a),
+        median(c) over (order by a),
+        median(d) over (order by a),
+        median(e) over (order by a),
+        median(f) over (order by a) from median_test;
+        median       |       median       |       median       |       median       | median | median 
+ --------------------+--------------------+--------------------+--------------------+--------+--------
+                   1 |                  1 |                  1 |                  1 |      1 |      1
+  1.5000000000000000 | 1.5000000000000000 | 1.5000000000000000 | 1.5000000000000000 |    1.5 |    1.5
+                   2 |                  2 |                  2 |                  2 |      2 |      2
+  2.5000000000000000 | 2.5000000000000000 | 2.5000000000000000 | 2.5000000000000000 |    2.5 |    2.5
+                   3 |                  3 |                  3 |                  3 |      3 |      3
+  3.5000000000000000 | 3.5000000000000000 | 3.5000000000000000 | 3.5000000000000000 |    3.5 |    3.5
+                   4 |                  4 |                  4 |                  4 |      4 |      4
+  4.5000000000000000 | 4.5000000000000000 | 4.5000000000000000 | 4.5000000000000000 |    4.5 |    4.5
+                   5 |                  5 |                  5 |                  5 |      5 |      5
+  5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 |    5.5 |    5.5
+                   6 |                  6 |                  6 |                  6 |      6 |      6
+ (11 rows)
+ 
+ drop table median_test;
*** ./src/test/regress/sql/numeric.sql.orig	2010-09-25 21:21:11.087459138 +0200
--- ./src/test/regress/sql/numeric.sql	2010-09-26 12:27:12.226352249 +0200
***************
*** 824,826 ****
--- 824,852 ----
  select 12345678901234567890 / 123;
  select div(12345678901234567890, 123);
  select div(12345678901234567890, 123) * 123 + 12345678901234567890 % 123;
+ 
+ -- median test
+ create table median_test (a int, b bigint, c smallint, d numeric, e double precision, f real);
+ insert into median_test select i,i,i,i,i,i from generate_series(1,10) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+ truncate table median_test;
+ insert into median_test select i,i,i,i,i,i from generate_series(1,11) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+ select median(a) over (order by a),
+        median(b) over (order by a),
+        median(c) over (order by a),
+        median(d) over (order by a),
+        median(e) over (order by a),
+        median(f) over (order by a) from median_test;
+ 
+ drop table median_test;

*** ./doc/src/sgml/func.sgml.orig	2010-09-09 02:48:22.000000000 +0200
--- ./doc/src/sgml/func.sgml	2010-10-01 21:01:03.605823970 +0200
***************
*** 10386,10391 ****
--- 10386,10411 ----
  
       <row>
        <entry>
+        <indexterm>
+         <primary>Arithmetic median</primary>
+         <secondary>median</secondary>
+        </indexterm>
+        <function>median(<replaceable class="parameter">expression</replaceable>)</function>
+       </entry>
+       <entry>
+        <type>smallint</type>, <type>int</type>,
+        <type>bigint</type>, <type>real</type>, <type>double
+        precision</type>, or <type>numeric</type>
+       </entry>
+       <entry>
+        <type>double precision</type> for floating-point arguments,
+        otherwise <type>numeric</type>
+       </entry>
+       <entry>arithmetic median - cannot be used as window aggregate</entry>
+      </row>
+ 
+      <row>
+       <entry>
         <function>regr_avgx(<replaceable class="parameter">Y</replaceable>, <replaceable class="parameter">X</replaceable>)</function>
        </entry>
        <entry>
*** ./src/backend/utils/adt/median.c.orig	2010-09-25 22:23:01.000000000 +0200
--- ./src/backend/utils/adt/median.c	2010-10-01 21:03:37.340823189 +0200
***************
*** 0 ****
--- 1,316 ----
+ #include "postgres.h"
+ 
+ #include "fmgr.h"
+ #include "miscadmin.h"
+ 
+ #include "catalog/pg_type.h"
+ #include "parser/parse_coerce.h"
+ #include "parser/parse_oper.h"
+ #include "utils/builtins.h"
+ #include "utils/lsyscache.h"
+ #include "utils/numeric.h"
+ #include "utils/tuplesort.h"
+ 
+ /*
+  * used as type of state variable median's function. It uses a 
+  * tuplesort as safe and inteligent storage. But we cannot to use
+  * a tuplesort under window aggregate context. 
+  */
+ typedef struct
+ {
+ 	int	nelems;		/* number of valid entries */
+ 	Tuplesortstate *sortstate;
+ 	FmgrInfo	cast_func_finfo;
+ 	bool	datumTypByVal;
+ } MedianAggState;
+ 
+ static void
+ freeDatum(Datum value, bool typByVal, bool isNull)
+ {
+ 	if (!typByVal && !isNull)
+ 	{
+ 		Pointer	s = DatumGetPointer(value);
+ 
+ 		pfree(s);
+ 	}
+ }
+ 
+ static MedianAggState *
+ makeMedianAggState(FunctionCallInfo fcinfo, Oid valtype, Oid targettype)
+ {
+ 	MemoryContext oldctx;
+ 	MemoryContext aggcontext;
+ 	MedianAggState *aggstate;
+ 	Oid	sortop,
+ 			castfunc;
+ 	CoercionPathType		pathtype;
+ 	int16	typlen;
+ 	bool		typbyval;
+ 
+ 	if (!AggCheckCallContext(fcinfo, &aggcontext))
+ 	{
+ 		/* cannot be called directly because of internal-type argument */
+ 		elog(ERROR, "median_transfn called in non-aggregate context");
+ 	}
+ 
+ 	if (fcinfo->context && IsA(fcinfo->context, WindowAggState))
+ 	{
+ 		elog(ERROR, "function median must not be used as window aggregate");
+ 	}
+ 
+ 	oldctx = MemoryContextSwitchTo(aggcontext);
+ 
+ 	aggstate = (MedianAggState *) palloc0(sizeof(MedianAggState));
+ 
+ 	valtype = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ 	get_sort_group_operators(valtype,
+ 						    true, false, false,
+ 						    &sortop, NULL, NULL);
+ 
+ 	/* lookup necessary attributies of the datum type, used for datumFree */
+ 	get_typlenbyval(valtype, &typlen, &typbyval);
+ 	aggstate->datumTypByVal = typbyval;
+ 
+ 	/* initialize a tuplesort */
+ 	aggstate->sortstate = tuplesort_begin_datum(valtype,
+ 							sortop,
+ 							SORTBY_NULLS_DEFAULT,
+ 							work_mem, true);
+ 
+ 	MemoryContextSwitchTo(oldctx);
+ 
+ 	if (valtype != targettype)
+ 	{
+ 		/* find a cast function */
+ 		pathtype = find_coercion_pathway(targettype, valtype,
+ 									COERCION_EXPLICIT,
+ 									&castfunc);
+ 		if (pathtype == COERCION_PATH_FUNC)
+ 		{
+ 			Assert(OidIsValid(castfunc));
+ 			fmgr_info_cxt(castfunc, &aggstate->cast_func_finfo,
+ 									    aggcontext);
+ 		} 
+ 		else if (pathtype == COERCION_PATH_RELABELTYPE)
+ 		{
+ 			aggstate->cast_func_finfo.fn_oid = InvalidOid;
+ 		}
+ 		else 
+ 			elog(ERROR, "no conversion function from %s %s",
+ 					 format_type_be(valtype),
+ 					 format_type_be(targettype));
+ 	}
+ 
+ 	return aggstate;
+ }
+ 
+ /*
+  *  append a non NULL value to tuplesort
+  */
+ static Datum
+ common_median_transfn(FunctionCallInfo fcinfo, Oid typoid, Oid targetoid)
+ {
+ 	MedianAggState *aggstate;
+ 	
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (!PG_ARGISNULL(1))
+ 	{
+ 		if (aggstate == NULL)
+ 			aggstate = makeMedianAggState(fcinfo, typoid, targetoid);
+ 
+ 		tuplesort_putdatum(aggstate->sortstate, PG_GETARG_DATUM(1), false);
+ 		aggstate->nelems++;
+ 	}
+ 
+ 	PG_RETURN_POINTER(aggstate);
+ }
+ 
+ /*
+  * just wrappers to be opr sanity checks happy
+  */
+ Datum
+ median_numeric_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						NUMERICOID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int8_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						INT8OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int4_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						INT4OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int2_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						INT2OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_double_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						FLOAT8OID, FLOAT8OID);
+ }
+ 
+ Datum
+ median_float_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						FLOAT4OID, FLOAT8OID);
+ }
+ 
+ /*
+  * Used for reading values from tuplesort. The value has to be
+  * double or cast function is defined (and used).
+  */
+ static double 
+ to_double(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetFloat8(value);
+ 
+ 	return DatumGetFloat8(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ /*
+  * Used as final function for median when result is double.
+  */
+ Datum
+ median_finalfn_double(PG_FUNCTION_ARGS)
+ {
+ 	MedianAggState *aggstate;
+ 
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   value;
+ 		bool	isNull;
+ 		int		i = 1;
+ 		double	result = 0;
+ 
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		tuplesort_performsort(aggstate->sortstate);
+ 
+ 		while (tuplesort_getdatum(aggstate->sortstate, true, &value, &isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_double(value, &aggstate->cast_func_finfo);
+ 				freeDatum(value, aggstate->datumTypByVal, isNull);
+ 
+ 				if (lidx != hidx)
+ 				{
+ 					tuplesort_getdatum(aggstate->sortstate, true, &value, &isNull);
+ 					result = (result + to_double(value, &aggstate->cast_func_finfo)) / 2.0;
+ 					freeDatum(value, aggstate->datumTypByVal, isNull);
+ 				}
+ 				break;
+ 			}
+ 		}
+ 
+ 		tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_FLOAT8(result);
+ 	}
+ 
+ 	PG_RETURN_NULL();
+ }
+ 
+ /*
+  * Used for reading values from tuplesort. The value has to be
+  * Numeric or cast function is defined (and used).
+  */
+ static Numeric
+ to_numeric(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetNumeric(value);
+ 
+ 	return DatumGetNumeric(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ /*
+  * Used as final function for median when result is numeric.
+  */
+ Datum
+ median_finalfn_numeric(PG_FUNCTION_ARGS)
+ {
+ 	MedianAggState *aggstate;
+ 
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   a_value;
+ 		bool	a_isNull;
+ 		int		i = 1;
+ 		Numeric result = NULL;		/* be compiler quiet */
+ 
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		tuplesort_performsort(aggstate->sortstate);
+ 
+ 		while (tuplesort_getdatum(aggstate->sortstate, true, &a_value, &a_isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_numeric(a_value, &aggstate->cast_func_finfo);
+ 
+ 				if (lidx != hidx)
+ 				{
+ 					Datum	b_value;
+ 					bool	b_isNull;
+ 					Numeric stack;
+ 
+ 					tuplesort_getdatum(aggstate->sortstate, true, &b_value, &b_isNull);
+ 
+ 					stack = to_numeric(b_value, &aggstate->cast_func_finfo);
+ 
+ 					stack = DatumGetNumeric(DirectFunctionCall2(numeric_add,
+ 												NumericGetDatum(stack),
+ 												NumericGetDatum(result)));
+ 					result = DatumGetNumeric(DirectFunctionCall2(numeric_div,
+ 												NumericGetDatum(stack),
+ 												DirectFunctionCall1(float4_numeric,
+ 																    Float4GetDatum(2.0))));
+ 					freeDatum(b_value, aggstate->datumTypByVal, b_isNull);
+ 				}
+ 				break;
+ 			}
+ 			else 
+ 				freeDatum(a_value, aggstate->datumTypByVal, a_isNull);
+ 		}
+ 
+ 		tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_NUMERIC(result);
+ 	}
+ 
+ 	PG_RETURN_NULL();
+ }
*** ./src/include/catalog/pg_aggregate.h.orig	2010-09-25 21:21:11.081451603 +0200
--- ./src/include/catalog/pg_aggregate.h	2010-09-25 21:22:01.497461866 +0200
***************
*** 226,231 ****
--- 226,239 ----
  /* text */
  DATA(insert ( 3538	string_agg_transfn	string_agg_finalfn		0	2281	_null_ ));
  
+ /* median */
+ DATA(insert ( 3123	median_double_transfn	median_finalfn_double		0	2281	_null_ ));
+ DATA(insert ( 3124	median_float_transfn	median_finalfn_double		0	2281	_null_ ));
+ DATA(insert ( 3125	median_numeric_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3126	median_int8_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3127	median_int4_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3128	median_int2_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ 
  /*
   * prototypes for functions in pg_aggregate.c
   */
*** ./src/include/catalog/pg_proc.h.orig	2010-09-03 03:34:55.000000000 +0200
--- ./src/include/catalog/pg_proc.h	2010-09-25 21:22:01.500452259 +0200
***************
*** 4850,4855 ****
--- 4850,4884 ----
  DATA(insert OID = 3114 (  nth_value		PGNSP PGUID 12 1 0 0 f t f t f i 2 0 2283 "2283 23" _null_ _null_ _null_ _null_ window_nth_value _null_ _null_ _null_ ));
  DESCR("fetch the Nth row value");
  
+ /* median aggregate */
+ DATA(insert OID = 3115 ( median_numeric_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 1700" _null_ _null_ _null_ _null_	median_numeric_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for numeric");
+ DATA(insert OID = 3116 ( median_int8_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 20" _null_ _null_ _null_ _null_	median_int8_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for bigint");
+ DATA(insert OID = 3117 ( median_int4_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 23" _null_ _null_ _null_ _null_	median_int4_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for int");
+ DATA(insert OID = 3118 ( median_int2_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 21" _null_ _null_ _null_ _null_	median_int2_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for smallint");
+ DATA(insert OID = 3119 ( median_double_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 701" _null_ _null_ _null_ _null_	median_double_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for double precision");
+ DATA(insert OID = 3120 ( median_float_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 700" _null_ _null_ _null_ _null_	median_float_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for real");
+ DATA(insert OID = 3121 ( median_finalfn_double			PGNSP PGUID 12 1 0 0 f f f f f i 1 0 701 "2281" _null_ _null_ _null_ _null_	median_finalfn_double _null_ _null_ _null_ ));
+ DESCR("median final function with double precision result");
+ DATA(insert OID = 3122 ( median_finalfn_numeric			PGNSP PGUID 12 1 0 0 f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_	median_finalfn_numeric _null_ _null_ _null_ ));
+ DESCR("median final function with numeric result");
+ DATA(insert OID = 3123 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 701 "701" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3124 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 701 "700" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3125 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "1700" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3126 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "20" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3127 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "23" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3128 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "21" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
  
  /*
   * Symbolic values for provolatile column: these indicate whether the result
*** ./src/include/utils/builtins.h.orig	2010-09-03 03:34:55.000000000 +0200
--- ./src/include/utils/builtins.h	2010-09-25 21:22:01.530706224 +0200
***************
*** 934,939 ****
--- 934,947 ----
  extern Datum int8_avg(PG_FUNCTION_ARGS);
  extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
  extern Datum hash_numeric(PG_FUNCTION_ARGS);
+ extern Datum median_numeric_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int8_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int4_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int2_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_double_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_float_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_finalfn_double(PG_FUNCTION_ARGS);
+ extern Datum median_finalfn_numeric(PG_FUNCTION_ARGS);
  
  /* ri_triggers.c */
  extern Datum RI_FKey_check_ins(PG_FUNCTION_ARGS);
*** ./src/test/regress/expected/numeric.out.orig	2010-09-25 21:21:11.086476751 +0200
--- ./src/test/regress/expected/numeric.out	2010-10-01 20:56:23.000000000 +0200
***************
*** 1372,1374 ****
--- 1372,1402 ----
   12345678901234567890
  (1 row)
  
+ -- median test
+ create table median_test (a int, b bigint, c smallint, d numeric, e double precision, f real);
+ insert into median_test select i,i,i,i,i,i from generate_series(1,10) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+        median       |       median       |       median       |       median       | median | median 
+ --------------------+--------------------+--------------------+--------------------+--------+--------
+  5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 |    5.5 |    5.5
+ (1 row)
+ 
+ truncate table median_test;
+ insert into median_test select i,i,i,i,i,i from generate_series(1,11) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+  median | median | median | median | median | median 
+ --------+--------+--------+--------+--------+--------
+       6 |      6 |      6 |      6 |      6 |      6
+ (1 row)
+ 
+ drop table median_test;
*** ./src/test/regress/sql/numeric.sql.orig	2010-09-25 21:21:11.087459138 +0200
--- ./src/test/regress/sql/numeric.sql	2010-10-01 20:56:06.145574285 +0200
***************
*** 824,826 ****
--- 824,846 ----
  select 12345678901234567890 / 123;
  select div(12345678901234567890, 123);
  select div(12345678901234567890, 123) * 123 + 12345678901234567890 % 123;
+ 
+ -- median test
+ create table median_test (a int, b bigint, c smallint, d numeric, e double precision, f real);
+ insert into median_test select i,i,i,i,i,i from generate_series(1,10) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+ truncate table median_test;
+ insert into median_test select i,i,i,i,i,i from generate_series(1,11) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+ 
+ drop table median_test;

*** ./doc/src/sgml/func.sgml.orig	2010-09-09 02:48:22.000000000 +0200
--- ./doc/src/sgml/func.sgml	2010-10-01 21:01:03.605823970 +0200
***************
*** 10386,10391 ****
--- 10386,10411 ----
  
       <row>
        <entry>
+        <indexterm>
+         <primary>Arithmetic median</primary>
+         <secondary>median</secondary>
+        </indexterm>
+        <function>median(<replaceable class="parameter">expression</replaceable>)</function>
+       </entry>
+       <entry>
+        <type>smallint</type>, <type>int</type>,
+        <type>bigint</type>, <type>real</type>, <type>double
+        precision</type>, or <type>numeric</type>
+       </entry>
+       <entry>
+        <type>double precision</type> for floating-point arguments,
+        otherwise <type>numeric</type>
+       </entry>
+       <entry>arithmetic median - cannot be used as window aggregate</entry>
+      </row>
+ 
+      <row>
+       <entry>
         <function>regr_avgx(<replaceable class="parameter">Y</replaceable>, <replaceable class="parameter">X</replaceable>)</function>
        </entry>
        <entry>
*** ./src/backend/utils/adt/Makefile.orig	2010-10-03 19:28:51.748961325 +0200
--- ./src/backend/utils/adt/Makefile	2010-09-26 00:34:57.231215085 +0200
***************
*** 19,25 ****
  	cash.o char.o date.o datetime.o datum.o domains.o \
  	enum.o float.o format_type.o \
  	geo_ops.o geo_selfuncs.o int.o int8.o like.o lockfuncs.o \
! 	misc.o nabstime.o name.o numeric.o numutils.o \
  	oid.o oracle_compat.o pseudotypes.o rowtypes.o \
  	regexp.o regproc.o ruleutils.o selfuncs.o \
  	tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \
--- 19,25 ----
  	cash.o char.o date.o datetime.o datum.o domains.o \
  	enum.o float.o format_type.o \
  	geo_ops.o geo_selfuncs.o int.o int8.o like.o lockfuncs.o \
! 	misc.o nabstime.o name.o numeric.o numutils.o median.o \
  	oid.o oracle_compat.o pseudotypes.o rowtypes.o \
  	regexp.o regproc.o ruleutils.o selfuncs.o \
  	tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \
*** ./src/backend/utils/adt/median.c.orig	2010-09-25 22:23:01.000000000 +0200
--- ./src/backend/utils/adt/median.c	2010-10-01 21:03:37.340823189 +0200
***************
*** 0 ****
--- 1,316 ----
+ #include "postgres.h"
+ 
+ #include "fmgr.h"
+ #include "miscadmin.h"
+ 
+ #include "catalog/pg_type.h"
+ #include "parser/parse_coerce.h"
+ #include "parser/parse_oper.h"
+ #include "utils/builtins.h"
+ #include "utils/lsyscache.h"
+ #include "utils/numeric.h"
+ #include "utils/tuplesort.h"
+ 
+ /*
+  * used as type of state variable median's function. It uses a 
+  * tuplesort as safe and inteligent storage. But we cannot to use
+  * a tuplesort under window aggregate context. 
+  */
+ typedef struct
+ {
+ 	int	nelems;		/* number of valid entries */
+ 	Tuplesortstate *sortstate;
+ 	FmgrInfo	cast_func_finfo;
+ 	bool	datumTypByVal;
+ } MedianAggState;
+ 
+ static void
+ freeDatum(Datum value, bool typByVal, bool isNull)
+ {
+ 	if (!typByVal && !isNull)
+ 	{
+ 		Pointer	s = DatumGetPointer(value);
+ 
+ 		pfree(s);
+ 	}
+ }
+ 
+ static MedianAggState *
+ makeMedianAggState(FunctionCallInfo fcinfo, Oid valtype, Oid targettype)
+ {
+ 	MemoryContext oldctx;
+ 	MemoryContext aggcontext;
+ 	MedianAggState *aggstate;
+ 	Oid	sortop,
+ 			castfunc;
+ 	CoercionPathType		pathtype;
+ 	int16	typlen;
+ 	bool		typbyval;
+ 
+ 	if (!AggCheckCallContext(fcinfo, &aggcontext))
+ 	{
+ 		/* cannot be called directly because of internal-type argument */
+ 		elog(ERROR, "median_transfn called in non-aggregate context");
+ 	}
+ 
+ 	if (fcinfo->context && IsA(fcinfo->context, WindowAggState))
+ 	{
+ 		elog(ERROR, "function median must not be used as window aggregate");
+ 	}
+ 
+ 	oldctx = MemoryContextSwitchTo(aggcontext);
+ 
+ 	aggstate = (MedianAggState *) palloc0(sizeof(MedianAggState));
+ 
+ 	valtype = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ 	get_sort_group_operators(valtype,
+ 						    true, false, false,
+ 						    &sortop, NULL, NULL);
+ 
+ 	/* lookup necessary attributies of the datum type, used for datumFree */
+ 	get_typlenbyval(valtype, &typlen, &typbyval);
+ 	aggstate->datumTypByVal = typbyval;
+ 
+ 	/* initialize a tuplesort */
+ 	aggstate->sortstate = tuplesort_begin_datum(valtype,
+ 							sortop,
+ 							SORTBY_NULLS_DEFAULT,
+ 							work_mem, true);
+ 
+ 	MemoryContextSwitchTo(oldctx);
+ 
+ 	if (valtype != targettype)
+ 	{
+ 		/* find a cast function */
+ 		pathtype = find_coercion_pathway(targettype, valtype,
+ 									COERCION_EXPLICIT,
+ 									&castfunc);
+ 		if (pathtype == COERCION_PATH_FUNC)
+ 		{
+ 			Assert(OidIsValid(castfunc));
+ 			fmgr_info_cxt(castfunc, &aggstate->cast_func_finfo,
+ 									    aggcontext);
+ 		} 
+ 		else if (pathtype == COERCION_PATH_RELABELTYPE)
+ 		{
+ 			aggstate->cast_func_finfo.fn_oid = InvalidOid;
+ 		}
+ 		else 
+ 			elog(ERROR, "no conversion function from %s %s",
+ 					 format_type_be(valtype),
+ 					 format_type_be(targettype));
+ 	}
+ 
+ 	return aggstate;
+ }
+ 
+ /*
+  *  append a non NULL value to tuplesort
+  */
+ static Datum
+ common_median_transfn(FunctionCallInfo fcinfo, Oid typoid, Oid targetoid)
+ {
+ 	MedianAggState *aggstate;
+ 	
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (!PG_ARGISNULL(1))
+ 	{
+ 		if (aggstate == NULL)
+ 			aggstate = makeMedianAggState(fcinfo, typoid, targetoid);
+ 
+ 		tuplesort_putdatum(aggstate->sortstate, PG_GETARG_DATUM(1), false);
+ 		aggstate->nelems++;
+ 	}
+ 
+ 	PG_RETURN_POINTER(aggstate);
+ }
+ 
+ /*
+  * just wrappers to be opr sanity checks happy
+  */
+ Datum
+ median_numeric_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						NUMERICOID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int8_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						INT8OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int4_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						INT4OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int2_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						INT2OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_double_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						FLOAT8OID, FLOAT8OID);
+ }
+ 
+ Datum
+ median_float_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						FLOAT4OID, FLOAT8OID);
+ }
+ 
+ /*
+  * Used for reading values from tuplesort. The value has to be
+  * double or cast function is defined (and used).
+  */
+ static double 
+ to_double(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetFloat8(value);
+ 
+ 	return DatumGetFloat8(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ /*
+  * Used as final function for median when result is double.
+  */
+ Datum
+ median_finalfn_double(PG_FUNCTION_ARGS)
+ {
+ 	MedianAggState *aggstate;
+ 
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   value;
+ 		bool	isNull;
+ 		int		i = 1;
+ 		double	result = 0;
+ 
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		tuplesort_performsort(aggstate->sortstate);
+ 
+ 		while (tuplesort_getdatum(aggstate->sortstate, true, &value, &isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_double(value, &aggstate->cast_func_finfo);
+ 				freeDatum(value, aggstate->datumTypByVal, isNull);
+ 
+ 				if (lidx != hidx)
+ 				{
+ 					tuplesort_getdatum(aggstate->sortstate, true, &value, &isNull);
+ 					result = (result + to_double(value, &aggstate->cast_func_finfo)) / 2.0;
+ 					freeDatum(value, aggstate->datumTypByVal, isNull);
+ 				}
+ 				break;
+ 			}
+ 		}
+ 
+ 		tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_FLOAT8(result);
+ 	}
+ 
+ 	PG_RETURN_NULL();
+ }
+ 
+ /*
+  * Used for reading values from tuplesort. The value has to be
+  * Numeric or cast function is defined (and used).
+  */
+ static Numeric
+ to_numeric(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetNumeric(value);
+ 
+ 	return DatumGetNumeric(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ /*
+  * Used as final function for median when result is numeric.
+  */
+ Datum
+ median_finalfn_numeric(PG_FUNCTION_ARGS)
+ {
+ 	MedianAggState *aggstate;
+ 
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   a_value;
+ 		bool	a_isNull;
+ 		int		i = 1;
+ 		Numeric result = NULL;		/* be compiler quiet */
+ 
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		tuplesort_performsort(aggstate->sortstate);
+ 
+ 		while (tuplesort_getdatum(aggstate->sortstate, true, &a_value, &a_isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_numeric(a_value, &aggstate->cast_func_finfo);
+ 
+ 				if (lidx != hidx)
+ 				{
+ 					Datum	b_value;
+ 					bool	b_isNull;
+ 					Numeric stack;
+ 
+ 					tuplesort_getdatum(aggstate->sortstate, true, &b_value, &b_isNull);
+ 
+ 					stack = to_numeric(b_value, &aggstate->cast_func_finfo);
+ 
+ 					stack = DatumGetNumeric(DirectFunctionCall2(numeric_add,
+ 												NumericGetDatum(stack),
+ 												NumericGetDatum(result)));
+ 					result = DatumGetNumeric(DirectFunctionCall2(numeric_div,
+ 												NumericGetDatum(stack),
+ 												DirectFunctionCall1(float4_numeric,
+ 																    Float4GetDatum(2.0))));
+ 					freeDatum(b_value, aggstate->datumTypByVal, b_isNull);
+ 				}
+ 				break;
+ 			}
+ 			else 
+ 				freeDatum(a_value, aggstate->datumTypByVal, a_isNull);
+ 		}
+ 
+ 		tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_NUMERIC(result);
+ 	}
+ 
+ 	PG_RETURN_NULL();
+ }
*** ./src/include/catalog/pg_aggregate.h.orig	2010-09-25 21:21:11.081451603 +0200
--- ./src/include/catalog/pg_aggregate.h	2010-09-25 21:22:01.497461866 +0200
***************
*** 226,231 ****
--- 226,239 ----
  /* text */
  DATA(insert ( 3538	string_agg_transfn	string_agg_finalfn		0	2281	_null_ ));
  
+ /* median */
+ DATA(insert ( 3123	median_double_transfn	median_finalfn_double		0	2281	_null_ ));
+ DATA(insert ( 3124	median_float_transfn	median_finalfn_double		0	2281	_null_ ));
+ DATA(insert ( 3125	median_numeric_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3126	median_int8_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3127	median_int4_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3128	median_int2_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ 
  /*
   * prototypes for functions in pg_aggregate.c
   */
*** ./src/include/catalog/pg_proc.h.orig	2010-09-03 03:34:55.000000000 +0200
--- ./src/include/catalog/pg_proc.h	2010-09-25 21:22:01.500452259 +0200
***************
*** 4850,4855 ****
--- 4850,4884 ----
  DATA(insert OID = 3114 (  nth_value		PGNSP PGUID 12 1 0 0 f t f t f i 2 0 2283 "2283 23" _null_ _null_ _null_ _null_ window_nth_value _null_ _null_ _null_ ));
  DESCR("fetch the Nth row value");
  
+ /* median aggregate */
+ DATA(insert OID = 3115 ( median_numeric_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 1700" _null_ _null_ _null_ _null_	median_numeric_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for numeric");
+ DATA(insert OID = 3116 ( median_int8_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 20" _null_ _null_ _null_ _null_	median_int8_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for bigint");
+ DATA(insert OID = 3117 ( median_int4_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 23" _null_ _null_ _null_ _null_	median_int4_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for int");
+ DATA(insert OID = 3118 ( median_int2_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 21" _null_ _null_ _null_ _null_	median_int2_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for smallint");
+ DATA(insert OID = 3119 ( median_double_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 701" _null_ _null_ _null_ _null_	median_double_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for double precision");
+ DATA(insert OID = 3120 ( median_float_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 700" _null_ _null_ _null_ _null_	median_float_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for real");
+ DATA(insert OID = 3121 ( median_finalfn_double			PGNSP PGUID 12 1 0 0 f f f f f i 1 0 701 "2281" _null_ _null_ _null_ _null_	median_finalfn_double _null_ _null_ _null_ ));
+ DESCR("median final function with double precision result");
+ DATA(insert OID = 3122 ( median_finalfn_numeric			PGNSP PGUID 12 1 0 0 f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_	median_finalfn_numeric _null_ _null_ _null_ ));
+ DESCR("median final function with numeric result");
+ DATA(insert OID = 3123 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 701 "701" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3124 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 701 "700" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3125 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "1700" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3126 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "20" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3127 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "23" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3128 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "21" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
  
  /*
   * Symbolic values for provolatile column: these indicate whether the result
*** ./src/include/utils/builtins.h.orig	2010-09-03 03:34:55.000000000 +0200
--- ./src/include/utils/builtins.h	2010-09-25 21:22:01.530706224 +0200
***************
*** 934,939 ****
--- 934,947 ----
  extern Datum int8_avg(PG_FUNCTION_ARGS);
  extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
  extern Datum hash_numeric(PG_FUNCTION_ARGS);
+ extern Datum median_numeric_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int8_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int4_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int2_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_double_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_float_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_finalfn_double(PG_FUNCTION_ARGS);
+ extern Datum median_finalfn_numeric(PG_FUNCTION_ARGS);
  
  /* ri_triggers.c */
  extern Datum RI_FKey_check_ins(PG_FUNCTION_ARGS);
*** ./src/test/regress/expected/numeric.out.orig	2010-09-25 21:21:11.086476751 +0200
--- ./src/test/regress/expected/numeric.out	2010-10-01 20:56:23.000000000 +0200
***************
*** 1372,1374 ****
--- 1372,1402 ----
   12345678901234567890
  (1 row)
  
+ -- median test
+ create table median_test (a int, b bigint, c smallint, d numeric, e double precision, f real);
+ insert into median_test select i,i,i,i,i,i from generate_series(1,10) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+        median       |       median       |       median       |       median       | median | median 
+ --------------------+--------------------+--------------------+--------------------+--------+--------
+  5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 |    5.5 |    5.5
+ (1 row)
+ 
+ truncate table median_test;
+ insert into median_test select i,i,i,i,i,i from generate_series(1,11) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+  median | median | median | median | median | median 
+ --------+--------+--------+--------+--------+--------
+       6 |      6 |      6 |      6 |      6 |      6
+ (1 row)
+ 
+ drop table median_test;
*** ./src/test/regress/sql/numeric.sql.orig	2010-09-25 21:21:11.087459138 +0200
--- ./src/test/regress/sql/numeric.sql	2010-10-01 20:56:06.145574285 +0200
***************
*** 824,826 ****
--- 824,846 ----
  select 12345678901234567890 / 123;
  select div(12345678901234567890, 123);
  select div(12345678901234567890, 123) * 123 + 12345678901234567890 % 123;
+ 
+ -- median test
+ create table median_test (a int, b bigint, c smallint, d numeric, e double precision, f real);
+ insert into median_test select i,i,i,i,i,i from generate_series(1,10) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+ truncate table median_test;
+ insert into median_test select i,i,i,i,i,i from generate_series(1,11) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+ 
+ drop table median_test;

*** ./doc/src/sgml/func.sgml.orig	2010-10-03 08:57:09.000000000 +0100
--- ./doc/src/sgml/func.sgml	2010-10-03 08:58:29.000000000 +0100
***************
*** 10386,10391 ****
--- 10386,10411 ----
  
       <row>
        <entry>
+        <indexterm>
+         <primary>Arithmetic median</primary>
+         <secondary>median</secondary>
+        </indexterm>
+        <function>median(<replaceable class="parameter">expression</replaceable>)</function>
+       </entry>
+       <entry>
+        <type>smallint</type>, <type>int</type>,
+        <type>bigint</type>, <type>real</type>, <type>double
+        precision</type>, or <type>numeric</type>
+       </entry>
+       <entry>
+        <type>double precision</type> for floating-point arguments,
+        otherwise <type>numeric</type>
+       </entry>
+       <entry>arithmetic median</entry>
+      </row>
+ 
+      <row>
+       <entry>
         <function>regr_avgx(<replaceable class="parameter">Y</replaceable>, <replaceable class="parameter">X</replaceable>)</function>
        </entry>
        <entry>
*** ./src/backend/utils/adt/Makefile.orig	2010-10-03 09:05:29.000000000 +0100
--- ./src/backend/utils/adt/Makefile	2010-10-03 09:05:57.000000000 +0100
***************
*** 19,25 ****
  	cash.o char.o date.o datetime.o datum.o domains.o \
  	enum.o float.o format_type.o \
  	geo_ops.o geo_selfuncs.o int.o int8.o like.o lockfuncs.o \
! 	misc.o nabstime.o name.o numeric.o numutils.o \
  	oid.o oracle_compat.o pseudotypes.o rowtypes.o \
  	regexp.o regproc.o ruleutils.o selfuncs.o \
  	tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \
--- 19,25 ----
  	cash.o char.o date.o datetime.o datum.o domains.o \
  	enum.o float.o format_type.o \
  	geo_ops.o geo_selfuncs.o int.o int8.o like.o lockfuncs.o \
! 	median.o misc.o nabstime.o name.o numeric.o numutils.o \
  	oid.o oracle_compat.o pseudotypes.o rowtypes.o \
  	regexp.o regproc.o ruleutils.o selfuncs.o \
  	tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \
*** ./src/backend/utils/adt/median.c.orig	2010-10-03 08:54:55.000000000 +0100
--- ./src/backend/utils/adt/median.c	2010-10-03 10:41:38.000000000 +0100
***************
*** 0 ****
--- 1,360 ----
+ #include "postgres.h"
+ 
+ #include "fmgr.h"
+ #include "miscadmin.h"
+ 
+ #include "catalog/pg_type.h"
+ #include "parser/parse_coerce.h"
+ #include "parser/parse_oper.h"
+ #include "utils/builtins.h"
+ #include "utils/lsyscache.h"
+ #include "utils/numeric.h"
+ #include "utils/tuplesort.h"
+ 
+ /*
+  * used as type of state variable median's function. It uses a 
+  * tuplesort as safe and inteligent storage. But we cannot to use
+  * a tuplesort under window aggregate context. 
+  */
+ typedef struct
+ {
+ 	int	nelems;		/* number of valid entries */
+ 	Tuplesortstate *sortstate;
+ 	bool	sorted;	/* true if the entries have been sorted */
+ 	FmgrInfo	cast_func_finfo;
+ 	bool	datumTypByVal;
+ } MedianAggState;
+ 
+ static void
+ freeDatum(Datum value, bool typByVal, bool isNull)
+ {
+ 	if (!typByVal && !isNull)
+ 	{
+ 		Pointer	s = DatumGetPointer(value);
+ 
+ 		pfree(s);
+ 	}
+ }
+ 
+ static MedianAggState *
+ makeMedianAggState(FunctionCallInfo fcinfo, Oid valtype, Oid targettype)
+ {
+ 	MemoryContext oldctx;
+ 	MemoryContext aggcontext;
+ 	MedianAggState *aggstate;
+ 	Oid	sortop,
+ 			castfunc;
+ 	CoercionPathType		pathtype;
+ 	int16	typlen;
+ 	bool		typbyval;
+ 
+ 	if (!AggCheckCallContext(fcinfo, &aggcontext))
+ 	{
+ 		/* cannot be called directly because of internal-type argument */
+ 		elog(ERROR, "median_transfn called in non-aggregate context");
+ 	}
+ 
+ 	oldctx = MemoryContextSwitchTo(aggcontext);
+ 
+ 	aggstate = (MedianAggState *) palloc0(sizeof(MedianAggState));
+ 
+ 	valtype = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ 	get_sort_group_operators(valtype,
+ 						    true, false, false,
+ 						    &sortop, NULL, NULL);
+ 
+ 	/* lookup necessary attributies of the datum type, used for datumFree */
+ 	get_typlenbyval(valtype, &typlen, &typbyval);
+ 	aggstate->datumTypByVal = typbyval;
+ 
+ 	/* initialize a tuplesort */
+ 	aggstate->sortstate = tuplesort_begin_datum(valtype,
+ 							sortop,
+ 							SORTBY_NULLS_DEFAULT,
+ 							work_mem, true);
+ 	aggstate->sorted = false;
+ 
+ 	MemoryContextSwitchTo(oldctx);
+ 
+ 	if (valtype != targettype)
+ 	{
+ 		/* find a cast function */
+ 		pathtype = find_coercion_pathway(targettype, valtype,
+ 									COERCION_EXPLICIT,
+ 									&castfunc);
+ 		if (pathtype == COERCION_PATH_FUNC)
+ 		{
+ 			Assert(OidIsValid(castfunc));
+ 			fmgr_info_cxt(castfunc, &aggstate->cast_func_finfo,
+ 									    aggcontext);
+ 		} 
+ 		else if (pathtype == COERCION_PATH_RELABELTYPE)
+ 		{
+ 			aggstate->cast_func_finfo.fn_oid = InvalidOid;
+ 		}
+ 		else 
+ 			elog(ERROR, "no conversion function from %s %s",
+ 					 format_type_be(valtype),
+ 					 format_type_be(targettype));
+ 	}
+ 
+ 	return aggstate;
+ }
+ 
+ /*
+  *  append a non NULL value to tuplesort
+  */
+ static Datum
+ common_median_transfn(FunctionCallInfo fcinfo, Oid typoid, Oid targetoid)
+ {
+ 	MedianAggState *aggstate;
+ 	
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (!PG_ARGISNULL(1))
+ 	{
+ 		if (aggstate == NULL)
+ 			aggstate = makeMedianAggState(fcinfo, typoid, targetoid);
+ 
+ 		if (aggstate->sorted)
+ 		{
+ 			MemoryContext aggcontext;
+ 			MemoryContext oldctx;
+ 			Oid		valtype;
+ 			Oid		sortop;
+ 			Tuplesortstate *sortstate;
+ 			Datum	value;
+ 			bool	isNull;
+ 
+ 			if (!AggCheckCallContext(fcinfo, &aggcontext))
+ 			{
+ 				/* cannot be called directly because of internal-type argument */
+ 				elog(ERROR, "median_transfn called in non-aggregate context");
+ 			}
+ 
+ 			oldctx = MemoryContextSwitchTo(aggcontext);
+ 
+ 			valtype = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ 			get_sort_group_operators(valtype,
+ 									 true, false, false,
+ 									 &sortop, NULL, NULL);
+ 
+ 			sortstate = tuplesort_begin_datum(valtype,
+ 											  sortop,
+ 											  SORTBY_NULLS_DEFAULT,
+ 											  work_mem, true);
+ 
+ 			MemoryContextSwitchTo(oldctx);
+ 
+ 			tuplesort_rescan(aggstate->sortstate);
+ 			while (tuplesort_getdatum(aggstate->sortstate, true, &value, &isNull))
+ 			{
+ 				tuplesort_putdatum(sortstate, value, isNull);
+ 				freeDatum(value, aggstate->datumTypByVal, isNull);
+ 			}
+ 
+ 			tuplesort_end(aggstate->sortstate);
+ 
+ 			aggstate->sortstate = sortstate;
+ 			aggstate->sorted = false;
+ 		}
+ 
+ 		tuplesort_putdatum(aggstate->sortstate, PG_GETARG_DATUM(1), false);
+ 		aggstate->nelems++;
+ 	}
+ 
+ 	PG_RETURN_POINTER(aggstate);
+ }
+ 
+ /*
+  * just wrappers to be opr sanity checks happy
+  */
+ Datum
+ median_numeric_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						NUMERICOID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int8_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						INT8OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int4_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						INT4OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int2_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						INT2OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_double_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						FLOAT8OID, FLOAT8OID);
+ }
+ 
+ Datum
+ median_float_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						FLOAT4OID, FLOAT8OID);
+ }
+ 
+ /*
+  * Used for reading values from tuplesort. The value has to be
+  * double or cast function is defined (and used).
+  */
+ static double 
+ to_double(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetFloat8(value);
+ 
+ 	return DatumGetFloat8(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ /*
+  * Used as final function for median when result is double.
+  */
+ Datum
+ median_finalfn_double(PG_FUNCTION_ARGS)
+ {
+ 	MedianAggState *aggstate;
+ 
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   value;
+ 		bool	isNull;
+ 		int		i = 1;
+ 		double	result = 0;
+ 
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		tuplesort_performsort(aggstate->sortstate);
+ 		aggstate->sorted = true;
+ 
+ 		while (tuplesort_getdatum(aggstate->sortstate, true, &value, &isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_double(value, &aggstate->cast_func_finfo);
+ 				freeDatum(value, aggstate->datumTypByVal, isNull);
+ 
+ 				if (lidx != hidx)
+ 				{
+ 					tuplesort_getdatum(aggstate->sortstate, true, &value, &isNull);
+ 					result = (result + to_double(value, &aggstate->cast_func_finfo)) / 2.0;
+ 					freeDatum(value, aggstate->datumTypByVal, isNull);
+ 				}
+ 				break;
+ 			}
+ 		}
+ 
+ 		if (fcinfo->context && IsA(fcinfo->context, AggState))
+ 			tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_FLOAT8(result);
+ 	}
+ 
+ 	PG_RETURN_NULL();
+ }
+ 
+ /*
+  * Used for reading values from tuplesort. The value has to be
+  * Numeric or cast function is defined (and used).
+  */
+ static Numeric
+ to_numeric(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetNumeric(value);
+ 
+ 	return DatumGetNumeric(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ /*
+  * Used as final function for median when result is numeric.
+  */
+ Datum
+ median_finalfn_numeric(PG_FUNCTION_ARGS)
+ {
+ 	MedianAggState *aggstate;
+ 
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   a_value;
+ 		bool	a_isNull;
+ 		int		i = 1;
+ 		Numeric result = NULL;		/* be compiler quiet */
+ 
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		tuplesort_performsort(aggstate->sortstate);
+ 		aggstate->sorted = true;
+ 
+ 		while (tuplesort_getdatum(aggstate->sortstate, true, &a_value, &a_isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_numeric(a_value, &aggstate->cast_func_finfo);
+ 
+ 				if (lidx != hidx)
+ 				{
+ 					Datum	b_value;
+ 					bool	b_isNull;
+ 					Numeric stack;
+ 
+ 					tuplesort_getdatum(aggstate->sortstate, true, &b_value, &b_isNull);
+ 
+ 					stack = to_numeric(b_value, &aggstate->cast_func_finfo);
+ 
+ 					stack = DatumGetNumeric(DirectFunctionCall2(numeric_add,
+ 												NumericGetDatum(stack),
+ 												NumericGetDatum(result)));
+ 					result = DatumGetNumeric(DirectFunctionCall2(numeric_div,
+ 												NumericGetDatum(stack),
+ 												DirectFunctionCall1(float4_numeric,
+ 																    Float4GetDatum(2.0))));
+ 					freeDatum(b_value, aggstate->datumTypByVal, b_isNull);
+ 				}
+ 				break;
+ 			}
+ 			else 
+ 				freeDatum(a_value, aggstate->datumTypByVal, a_isNull);
+ 		}
+ 
+ 		if (fcinfo->context && IsA(fcinfo->context, AggState))
+ 			tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_NUMERIC(result);
+ 	}
+ 
+ 	PG_RETURN_NULL();
+ }
*** ./src/include/catalog/pg_aggregate.h.orig	2010-10-03 08:57:09.000000000 +0100
--- ./src/include/catalog/pg_aggregate.h	2010-10-03 08:54:23.000000000 +0100
***************
*** 226,231 ****
--- 226,239 ----
  /* text */
  DATA(insert ( 3538	string_agg_transfn	string_agg_finalfn		0	2281	_null_ ));
  
+ /* median */
+ DATA(insert ( 3123	median_double_transfn	median_finalfn_double		0	2281	_null_ ));
+ DATA(insert ( 3124	median_float_transfn	median_finalfn_double		0	2281	_null_ ));
+ DATA(insert ( 3125	median_numeric_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3126	median_int8_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3127	median_int4_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3128	median_int2_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ 
  /*
   * prototypes for functions in pg_aggregate.c
   */
*** ./src/include/catalog/pg_proc.h.orig	2010-10-03 08:57:09.000000000 +0100
--- ./src/include/catalog/pg_proc.h	2010-10-03 08:54:23.000000000 +0100
***************
*** 4850,4855 ****
--- 4850,4884 ----
  DATA(insert OID = 3114 (  nth_value		PGNSP PGUID 12 1 0 0 f t f t f i 2 0 2283 "2283 23" _null_ _null_ _null_ _null_ window_nth_value _null_ _null_ _null_ ));
  DESCR("fetch the Nth row value");
  
+ /* median aggregate */
+ DATA(insert OID = 3115 ( median_numeric_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 1700" _null_ _null_ _null_ _null_	median_numeric_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for numeric");
+ DATA(insert OID = 3116 ( median_int8_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 20" _null_ _null_ _null_ _null_	median_int8_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for bigint");
+ DATA(insert OID = 3117 ( median_int4_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 23" _null_ _null_ _null_ _null_	median_int4_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for int");
+ DATA(insert OID = 3118 ( median_int2_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 21" _null_ _null_ _null_ _null_	median_int2_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for smallint");
+ DATA(insert OID = 3119 ( median_double_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 701" _null_ _null_ _null_ _null_	median_double_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for double precision");
+ DATA(insert OID = 3120 ( median_float_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 700" _null_ _null_ _null_ _null_	median_float_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for real");
+ DATA(insert OID = 3121 ( median_finalfn_double			PGNSP PGUID 12 1 0 0 f f f f f i 1 0 701 "2281" _null_ _null_ _null_ _null_	median_finalfn_double _null_ _null_ _null_ ));
+ DESCR("median final function with double precision result");
+ DATA(insert OID = 3122 ( median_finalfn_numeric			PGNSP PGUID 12 1 0 0 f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_	median_finalfn_numeric _null_ _null_ _null_ ));
+ DESCR("median final function with numeric result");
+ DATA(insert OID = 3123 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 701 "701" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3124 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 701 "700" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3125 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "1700" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3126 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "20" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3127 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "23" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3128 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "21" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
  
  /*
   * Symbolic values for provolatile column: these indicate whether the result
*** ./src/include/utils/builtins.h.orig	2010-10-03 08:57:09.000000000 +0100
--- ./src/include/utils/builtins.h	2010-10-03 08:54:23.000000000 +0100
***************
*** 934,939 ****
--- 934,947 ----
  extern Datum int8_avg(PG_FUNCTION_ARGS);
  extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
  extern Datum hash_numeric(PG_FUNCTION_ARGS);
+ extern Datum median_numeric_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int8_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int4_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int2_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_double_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_float_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_finalfn_double(PG_FUNCTION_ARGS);
+ extern Datum median_finalfn_numeric(PG_FUNCTION_ARGS);
  
  /* ri_triggers.c */
  extern Datum RI_FKey_check_ins(PG_FUNCTION_ARGS);
*** ./src/test/regress/expected/numeric.out.orig	2010-10-03 08:57:09.000000000 +0100
--- ./src/test/regress/expected/numeric.out	2010-10-03 09:01:36.000000000 +0100
***************
*** 1372,1374 ****
--- 1372,1423 ----
   12345678901234567890
  (1 row)
  
+ -- median test
+ create table median_test (a int, b bigint, c smallint, d numeric, e double precision, f real);
+ insert into median_test select i,i,i,i,i,i from generate_series(1,10) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+        median       |       median       |       median       |       median       | median | median 
+ --------------------+--------------------+--------------------+--------------------+--------+--------
+  5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 |    5.5 |    5.5
+ (1 row)
+ 
+ truncate table median_test;
+ insert into median_test select i,i,i,i,i,i from generate_series(1,11) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+  median | median | median | median | median | median 
+ --------+--------+--------+--------+--------+--------
+       6 |      6 |      6 |      6 |      6 |      6
+ (1 row)
+ 
+ select median(a) over (order by a),
+        median(b) over (order by a),
+        median(c) over (order by a),
+        median(d) over (order by a),
+        median(e) over (order by a),
+        median(f) over (order by a) from median_test;
+        median       |       median       |       median       |       median       | median | median 
+ --------------------+--------------------+--------------------+--------------------+--------+--------
+                   1 |                  1 |                  1 |                  1 |      1 |      1
+  1.5000000000000000 | 1.5000000000000000 | 1.5000000000000000 | 1.5000000000000000 |    1.5 |    1.5
+                   2 |                  2 |                  2 |                  2 |      2 |      2
+  2.5000000000000000 | 2.5000000000000000 | 2.5000000000000000 | 2.5000000000000000 |    2.5 |    2.5
+                   3 |                  3 |                  3 |                  3 |      3 |      3
+  3.5000000000000000 | 3.5000000000000000 | 3.5000000000000000 | 3.5000000000000000 |    3.5 |    3.5
+                   4 |                  4 |                  4 |                  4 |      4 |      4
+  4.5000000000000000 | 4.5000000000000000 | 4.5000000000000000 | 4.5000000000000000 |    4.5 |    4.5
+                   5 |                  5 |                  5 |                  5 |      5 |      5
+  5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 |    5.5 |    5.5
+                   6 |                  6 |                  6 |                  6 |      6 |      6
+ (11 rows)
+ 
+ drop table median_test;
*** ./src/test/regress/sql/numeric.sql.orig	2010-10-03 08:57:09.000000000 +0100
--- ./src/test/regress/sql/numeric.sql	2010-10-03 09:02:43.000000000 +0100
***************
*** 824,826 ****
--- 824,852 ----
  select 12345678901234567890 / 123;
  select div(12345678901234567890, 123);
  select div(12345678901234567890, 123) * 123 + 12345678901234567890 % 123;
+ 
+ -- median test
+ create table median_test (a int, b bigint, c smallint, d numeric, e double precision, f real);
+ insert into median_test select i,i,i,i,i,i from generate_series(1,10) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+ truncate table median_test;
+ insert into median_test select i,i,i,i,i,i from generate_series(1,11) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+ select median(a) over (order by a),
+        median(b) over (order by a),
+        median(c) over (order by a),
+        median(d) over (order by a),
+        median(e) over (order by a),
+        median(f) over (order by a) from median_test;
+ 
+ drop table median_test;

*** ./doc/src/sgml/func.sgml.orig	2010-10-03 08:57:09.000000000 +0100
--- ./doc/src/sgml/func.sgml	2010-10-03 08:58:29.000000000 +0100
***************
*** 10386,10391 ****
--- 10386,10411 ----
  
       <row>
        <entry>
+        <indexterm>
+         <primary>Arithmetic median</primary>
+         <secondary>median</secondary>
+        </indexterm>
+        <function>median(<replaceable class="parameter">expression</replaceable>)</function>
+       </entry>
+       <entry>
+        <type>smallint</type>, <type>int</type>,
+        <type>bigint</type>, <type>real</type>, <type>double
+        precision</type>, or <type>numeric</type>
+       </entry>
+       <entry>
+        <type>double precision</type> for floating-point arguments,
+        otherwise <type>numeric</type>
+       </entry>
+       <entry>arithmetic median</entry>
+      </row>
+ 
+      <row>
+       <entry>
         <function>regr_avgx(<replaceable class="parameter">Y</replaceable>, <replaceable class="parameter">X</replaceable>)</function>
        </entry>
        <entry>
*** ./src/backend/utils/adt/Makefile.orig	2010-10-03 09:05:29.000000000 +0100
--- ./src/backend/utils/adt/Makefile	2010-10-03 09:05:57.000000000 +0100
***************
*** 19,25 ****
  	cash.o char.o date.o datetime.o datum.o domains.o \
  	enum.o float.o format_type.o \
  	geo_ops.o geo_selfuncs.o int.o int8.o like.o lockfuncs.o \
! 	misc.o nabstime.o name.o numeric.o numutils.o \
  	oid.o oracle_compat.o pseudotypes.o rowtypes.o \
  	regexp.o regproc.o ruleutils.o selfuncs.o \
  	tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \
--- 19,25 ----
  	cash.o char.o date.o datetime.o datum.o domains.o \
  	enum.o float.o format_type.o \
  	geo_ops.o geo_selfuncs.o int.o int8.o like.o lockfuncs.o \
! 	median.o misc.o nabstime.o name.o numeric.o numutils.o \
  	oid.o oracle_compat.o pseudotypes.o rowtypes.o \
  	regexp.o regproc.o ruleutils.o selfuncs.o \
  	tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \
*** ./src/backend/utils/adt/median.c.orig	2010-10-03 08:54:55.000000000 +0100
--- ./src/backend/utils/adt/median.c	2010-10-05 10:16:41.000000000 +0100
***************
*** 0 ****
--- 1,387 ----
+ #include "postgres.h"
+ 
+ #include "fmgr.h"
+ #include "miscadmin.h"
+ 
+ #include "catalog/pg_type.h"
+ #include "parser/parse_coerce.h"
+ #include "parser/parse_oper.h"
+ #include "utils/builtins.h"
+ #include "utils/lsyscache.h"
+ #include "utils/numeric.h"
+ #include "utils/tuplesort.h"
+ 
+ /*
+  * used as type of state variable median's function. It uses a 
+  * tuplesort as safe and inteligent storage. But we cannot to use
+  * a tuplesort under window aggregate context. 
+  */
+ typedef struct
+ {
+ 	int	nelems;		/* number of valid entries */
+ 	Tuplesortstate *sortstate;
+ 	bool	sorted;	/* true if the entries have been sorted */
+ 	bool	free_tuplesort;	/* true if the tuplesort should be freed */
+ 	FmgrInfo	cast_func_finfo;
+ 	bool	datumTypByVal;
+ } MedianAggState;
+ 
+ static void
+ freeDatum(Datum value, bool typByVal, bool isNull)
+ {
+ 	if (!typByVal && !isNull)
+ 	{
+ 		Pointer	s = DatumGetPointer(value);
+ 
+ 		pfree(s);
+ 	}
+ }
+ 
+ static MedianAggState *
+ makeMedianAggState(FunctionCallInfo fcinfo, Oid valtype, Oid targettype)
+ {
+ 	MemoryContext oldctx;
+ 	MemoryContext aggcontext;
+ 	MedianAggState *aggstate;
+ 	Oid	sortop,
+ 			castfunc;
+ 	CoercionPathType		pathtype;
+ 	int16	typlen;
+ 	bool		typbyval;
+ 
+ 	if (!AggCheckCallContext(fcinfo, &aggcontext))
+ 	{
+ 		/* cannot be called directly because of internal-type argument */
+ 		elog(ERROR, "median_transfn called in non-aggregate context");
+ 	}
+ 
+ 	oldctx = MemoryContextSwitchTo(aggcontext);
+ 
+ 	aggstate = (MedianAggState *) palloc0(sizeof(MedianAggState));
+ 
+ 	valtype = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ 	get_sort_group_operators(valtype,
+ 						    true, false, false,
+ 						    &sortop, NULL, NULL);
+ 
+ 	/* lookup necessary attributies of the datum type, used for datumFree */
+ 	get_typlenbyval(valtype, &typlen, &typbyval);
+ 	aggstate->datumTypByVal = typbyval;
+ 
+ 	/* initialize a tuplesort */
+ 	aggstate->sortstate = tuplesort_begin_datum(valtype,
+ 							sortop,
+ 							SORTBY_NULLS_DEFAULT,
+ 							work_mem, true);
+ 	aggstate->sorted = false;
+ 	aggstate->free_tuplesort = false; /* set in transfn, if necessary */
+ 
+ 	MemoryContextSwitchTo(oldctx);
+ 
+ 	if (valtype != targettype)
+ 	{
+ 		/* find a cast function */
+ 		pathtype = find_coercion_pathway(targettype, valtype,
+ 									COERCION_EXPLICIT,
+ 									&castfunc);
+ 		if (pathtype == COERCION_PATH_FUNC)
+ 		{
+ 			Assert(OidIsValid(castfunc));
+ 			fmgr_info_cxt(castfunc, &aggstate->cast_func_finfo,
+ 									    aggcontext);
+ 		} 
+ 		else if (pathtype == COERCION_PATH_RELABELTYPE)
+ 		{
+ 			aggstate->cast_func_finfo.fn_oid = InvalidOid;
+ 		}
+ 		else 
+ 			elog(ERROR, "no conversion function from %s %s",
+ 					 format_type_be(valtype),
+ 					 format_type_be(targettype));
+ 	}
+ 
+ 	return aggstate;
+ }
+ 
+ /*
+  *  append a non NULL value to tuplesort
+  */
+ static Datum
+ common_median_transfn(FunctionCallInfo fcinfo, Oid typoid, Oid targetoid)
+ {
+ 	MedianAggState *aggstate;
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (!PG_ARGISNULL(1))
+ 	{
+ 		bool	is_running_median = false;
+ 
+ 		if (aggstate == NULL)
+ 			aggstate = makeMedianAggState(fcinfo, typoid, targetoid);
+ 
+ 		if (aggstate->sorted)
+ 		{
+ 			/*
+ 			 * We have already done a sort and computed a median value, so
+ 			 * this must be a running median computation, over a WINDOW with
+ 			 * an ORDER BY clause.
+ 			 *
+ 			 * Transfer all the data to a new tuplesort, so that we can sort
+ 			 * it again in the final function. This is very inefficient, and
+ 			 * should probably be replaced with a better algorithm one day.
+ 			 *
+ 			 * For now, this is the easiest solution. We might as well try
+ 			 * to keep the new tuplesort in memory, since it will use up
+ 			 * memory so slowly that it's unlikely to ever be a problem.
+ 			 */
+ 			MemoryContext aggcontext;
+ 			MemoryContext oldctx;
+ 			Oid		valtype;
+ 			Oid		sortop;
+ 			Tuplesortstate *sortstate;
+ 			Datum	value;
+ 			bool	isNull;
+ 
+ 			if (!AggCheckCallContext(fcinfo, &aggcontext))
+ 			{
+ 				/* cannot be called directly because of internal-type argument */
+ 				elog(ERROR, "median_transfn called in non-aggregate context");
+ 			}
+ 
+ 			oldctx = MemoryContextSwitchTo(aggcontext);
+ 
+ 			valtype = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ 			get_sort_group_operators(valtype,
+ 									 true, false, false,
+ 									 &sortop, NULL, NULL);
+ 
+ 			sortstate = tuplesort_begin_datum(valtype,
+ 											  sortop,
+ 											  SORTBY_NULLS_DEFAULT,
+ 											  2000000, true);
+ 
+ 			MemoryContextSwitchTo(oldctx);
+ 
+ 			tuplesort_rescan(aggstate->sortstate);
+ 			while (tuplesort_getdatum(aggstate->sortstate, true, &value, &isNull))
+ 			{
+ 				tuplesort_putdatum(sortstate, value, isNull);
+ 				freeDatum(value, aggstate->datumTypByVal, isNull);
+ 			}
+ 
+ 			tuplesort_end(aggstate->sortstate);
+ 
+ 			aggstate->sortstate = sortstate;
+ 			aggstate->sorted = false;
+ 			is_running_median = true;
+ 		}
+ 
+ 		/*
+ 		 * The final function should free the tuplesort unless this is a
+ 		 * running median computation. If this is the first valid value,
+ 		 * we don't actually know if it will be a running median, but it's
+ 		 * safe to not free in that case anyway, since the tuplesort won't
+ 		 * have used a temp file.
+ 		 */
+ 		aggstate->free_tuplesort = aggstate->nelems > 0 && !is_running_median;
+ 
+ 		tuplesort_putdatum(aggstate->sortstate, PG_GETARG_DATUM(1), false);
+ 		aggstate->nelems++;
+ 	}
+ 
+ 	PG_RETURN_POINTER(aggstate);
+ }
+ 
+ /*
+  * just wrappers to be opr sanity checks happy
+  */
+ Datum
+ median_numeric_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						NUMERICOID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int8_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						INT8OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int4_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo, 
+ 						INT4OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_int2_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						INT2OID, NUMERICOID);
+ }
+ 
+ Datum
+ median_double_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						FLOAT8OID, FLOAT8OID);
+ }
+ 
+ Datum
+ median_float_transfn(PG_FUNCTION_ARGS)
+ {
+ 	return common_median_transfn(fcinfo,
+ 						FLOAT4OID, FLOAT8OID);
+ }
+ 
+ /*
+  * Used for reading values from tuplesort. The value has to be
+  * double or cast function is defined (and used).
+  */
+ static double 
+ to_double(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetFloat8(value);
+ 
+ 	return DatumGetFloat8(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ /*
+  * Used as final function for median when result is double.
+  */
+ Datum
+ median_finalfn_double(PG_FUNCTION_ARGS)
+ {
+ 	MedianAggState *aggstate;
+ 
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   value;
+ 		bool	isNull;
+ 		int		i = 1;
+ 		double	result = 0;
+ 
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		tuplesort_performsort(aggstate->sortstate);
+ 		aggstate->sorted = true;
+ 
+ 		while (tuplesort_getdatum(aggstate->sortstate, true, &value, &isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_double(value, &aggstate->cast_func_finfo);
+ 				freeDatum(value, aggstate->datumTypByVal, isNull);
+ 
+ 				if (lidx != hidx)
+ 				{
+ 					tuplesort_getdatum(aggstate->sortstate, true, &value, &isNull);
+ 					result = (result + to_double(value, &aggstate->cast_func_finfo)) / 2.0;
+ 					freeDatum(value, aggstate->datumTypByVal, isNull);
+ 				}
+ 				break;
+ 			}
+ 		}
+ 
+ 		if (aggstate->free_tuplesort)
+ 			tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_FLOAT8(result);
+ 	}
+ 
+ 	PG_RETURN_NULL();
+ }
+ 
+ /*
+  * Used for reading values from tuplesort. The value has to be
+  * Numeric or cast function is defined (and used).
+  */
+ static Numeric
+ to_numeric(Datum value, FmgrInfo *cast_func_finfo)
+ {
+ 	/* when valtype is same as target type, returns directly */
+ 	if (cast_func_finfo->fn_oid == InvalidOid)
+ 		return DatumGetNumeric(value);
+ 
+ 	return DatumGetNumeric(FunctionCall1(cast_func_finfo, value));
+ }
+ 
+ /*
+  * Used as final function for median when result is numeric.
+  */
+ Datum
+ median_finalfn_numeric(PG_FUNCTION_ARGS)
+ {
+ 	MedianAggState *aggstate;
+ 
+ 	Assert(AggCheckCallContext(fcinfo, NULL));
+ 
+ 	aggstate = PG_ARGISNULL(0) ? NULL : (MedianAggState *) PG_GETARG_POINTER(0);
+ 
+ 	if (aggstate != NULL)
+ 	{
+ 		int	lidx;
+ 		int	hidx;
+ 		Datum	   a_value;
+ 		bool	a_isNull;
+ 		int		i = 1;
+ 		Numeric result = NULL;		/* be compiler quiet */
+ 
+ 		hidx = aggstate->nelems / 2 + 1;
+ 		lidx = (aggstate->nelems + 1) / 2;
+ 
+ 		tuplesort_performsort(aggstate->sortstate);
+ 		aggstate->sorted = true;
+ 
+ 		while (tuplesort_getdatum(aggstate->sortstate, true, &a_value, &a_isNull))
+ 		{
+ 			if (i++ == lidx)
+ 			{
+ 				result = to_numeric(a_value, &aggstate->cast_func_finfo);
+ 
+ 				if (lidx != hidx)
+ 				{
+ 					Datum	b_value;
+ 					bool	b_isNull;
+ 					Numeric stack;
+ 
+ 					tuplesort_getdatum(aggstate->sortstate, true, &b_value, &b_isNull);
+ 
+ 					stack = to_numeric(b_value, &aggstate->cast_func_finfo);
+ 
+ 					stack = DatumGetNumeric(DirectFunctionCall2(numeric_add,
+ 												NumericGetDatum(stack),
+ 												NumericGetDatum(result)));
+ 					result = DatumGetNumeric(DirectFunctionCall2(numeric_div,
+ 												NumericGetDatum(stack),
+ 												DirectFunctionCall1(float4_numeric,
+ 																    Float4GetDatum(2.0))));
+ 					freeDatum(b_value, aggstate->datumTypByVal, b_isNull);
+ 				}
+ 				break;
+ 			}
+ 			else 
+ 				freeDatum(a_value, aggstate->datumTypByVal, a_isNull);
+ 		}
+ 
+ 		if (aggstate->free_tuplesort)
+ 			tuplesort_end(aggstate->sortstate);
+ 
+ 		PG_RETURN_NUMERIC(result);
+ 	}
+ 
+ 	PG_RETURN_NULL();
+ }
*** ./src/include/catalog/pg_aggregate.h.orig	2010-10-03 08:57:09.000000000 +0100
--- ./src/include/catalog/pg_aggregate.h	2010-10-03 08:54:23.000000000 +0100
***************
*** 226,231 ****
--- 226,239 ----
  /* text */
  DATA(insert ( 3538	string_agg_transfn	string_agg_finalfn		0	2281	_null_ ));
  
+ /* median */
+ DATA(insert ( 3123	median_double_transfn	median_finalfn_double		0	2281	_null_ ));
+ DATA(insert ( 3124	median_float_transfn	median_finalfn_double		0	2281	_null_ ));
+ DATA(insert ( 3125	median_numeric_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3126	median_int8_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3127	median_int4_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ DATA(insert ( 3128	median_int2_transfn	median_finalfn_numeric		0	2281	_null_ ));
+ 
  /*
   * prototypes for functions in pg_aggregate.c
   */
*** ./src/include/catalog/pg_proc.h.orig	2010-10-03 08:57:09.000000000 +0100
--- ./src/include/catalog/pg_proc.h	2010-10-03 08:54:23.000000000 +0100
***************
*** 4850,4855 ****
--- 4850,4884 ----
  DATA(insert OID = 3114 (  nth_value		PGNSP PGUID 12 1 0 0 f t f t f i 2 0 2283 "2283 23" _null_ _null_ _null_ _null_ window_nth_value _null_ _null_ _null_ ));
  DESCR("fetch the Nth row value");
  
+ /* median aggregate */
+ DATA(insert OID = 3115 ( median_numeric_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 1700" _null_ _null_ _null_ _null_	median_numeric_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for numeric");
+ DATA(insert OID = 3116 ( median_int8_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 20" _null_ _null_ _null_ _null_	median_int8_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for bigint");
+ DATA(insert OID = 3117 ( median_int4_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 23" _null_ _null_ _null_ _null_	median_int4_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for int");
+ DATA(insert OID = 3118 ( median_int2_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 21" _null_ _null_ _null_ _null_	median_int2_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for smallint");
+ DATA(insert OID = 3119 ( median_double_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 701" _null_ _null_ _null_ _null_	median_double_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for double precision");
+ DATA(insert OID = 3120 ( median_float_transfn			PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 700" _null_ _null_ _null_ _null_	median_float_transfn _null_ _null_ _null_ ));
+ DESCR("median transident function for real");
+ DATA(insert OID = 3121 ( median_finalfn_double			PGNSP PGUID 12 1 0 0 f f f f f i 1 0 701 "2281" _null_ _null_ _null_ _null_	median_finalfn_double _null_ _null_ _null_ ));
+ DESCR("median final function with double precision result");
+ DATA(insert OID = 3122 ( median_finalfn_numeric			PGNSP PGUID 12 1 0 0 f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_	median_finalfn_numeric _null_ _null_ _null_ ));
+ DESCR("median final function with numeric result");
+ DATA(insert OID = 3123 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 701 "701" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3124 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 701 "700" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3125 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "1700" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3126 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "20" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3127 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "23" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
+ DATA(insert OID = 3128 ( median					   PGNSP PGUID 12 1 0 0 t f f f f i 1 0 1700 "21" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("median aggregate");
  
  /*
   * Symbolic values for provolatile column: these indicate whether the result
*** ./src/include/utils/builtins.h.orig	2010-10-03 08:57:09.000000000 +0100
--- ./src/include/utils/builtins.h	2010-10-03 08:54:23.000000000 +0100
***************
*** 934,939 ****
--- 934,947 ----
  extern Datum int8_avg(PG_FUNCTION_ARGS);
  extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
  extern Datum hash_numeric(PG_FUNCTION_ARGS);
+ extern Datum median_numeric_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int8_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int4_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_int2_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_double_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_float_transfn(PG_FUNCTION_ARGS);
+ extern Datum median_finalfn_double(PG_FUNCTION_ARGS);
+ extern Datum median_finalfn_numeric(PG_FUNCTION_ARGS);
  
  /* ri_triggers.c */
  extern Datum RI_FKey_check_ins(PG_FUNCTION_ARGS);
*** ./src/test/regress/expected/numeric.out.orig	2010-10-03 08:57:09.000000000 +0100
--- ./src/test/regress/expected/numeric.out	2010-10-03 09:01:36.000000000 +0100
***************
*** 1372,1374 ****
--- 1372,1423 ----
   12345678901234567890
  (1 row)
  
+ -- median test
+ create table median_test (a int, b bigint, c smallint, d numeric, e double precision, f real);
+ insert into median_test select i,i,i,i,i,i from generate_series(1,10) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+        median       |       median       |       median       |       median       | median | median 
+ --------------------+--------------------+--------------------+--------------------+--------+--------
+  5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 |    5.5 |    5.5
+ (1 row)
+ 
+ truncate table median_test;
+ insert into median_test select i,i,i,i,i,i from generate_series(1,11) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+  median | median | median | median | median | median 
+ --------+--------+--------+--------+--------+--------
+       6 |      6 |      6 |      6 |      6 |      6
+ (1 row)
+ 
+ select median(a) over (order by a),
+        median(b) over (order by a),
+        median(c) over (order by a),
+        median(d) over (order by a),
+        median(e) over (order by a),
+        median(f) over (order by a) from median_test;
+        median       |       median       |       median       |       median       | median | median 
+ --------------------+--------------------+--------------------+--------------------+--------+--------
+                   1 |                  1 |                  1 |                  1 |      1 |      1
+  1.5000000000000000 | 1.5000000000000000 | 1.5000000000000000 | 1.5000000000000000 |    1.5 |    1.5
+                   2 |                  2 |                  2 |                  2 |      2 |      2
+  2.5000000000000000 | 2.5000000000000000 | 2.5000000000000000 | 2.5000000000000000 |    2.5 |    2.5
+                   3 |                  3 |                  3 |                  3 |      3 |      3
+  3.5000000000000000 | 3.5000000000000000 | 3.5000000000000000 | 3.5000000000000000 |    3.5 |    3.5
+                   4 |                  4 |                  4 |                  4 |      4 |      4
+  4.5000000000000000 | 4.5000000000000000 | 4.5000000000000000 | 4.5000000000000000 |    4.5 |    4.5
+                   5 |                  5 |                  5 |                  5 |      5 |      5
+  5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 | 5.5000000000000000 |    5.5 |    5.5
+                   6 |                  6 |                  6 |                  6 |      6 |      6
+ (11 rows)
+ 
+ drop table median_test;
*** ./src/test/regress/sql/numeric.sql.orig	2010-10-03 08:57:09.000000000 +0100
--- ./src/test/regress/sql/numeric.sql	2010-10-03 09:02:43.000000000 +0100
***************
*** 824,826 ****
--- 824,852 ----
  select 12345678901234567890 / 123;
  select div(12345678901234567890, 123);
  select div(12345678901234567890, 123) * 123 + 12345678901234567890 % 123;
+ 
+ -- median test
+ create table median_test (a int, b bigint, c smallint, d numeric, e double precision, f real);
+ insert into median_test select i,i,i,i,i,i from generate_series(1,10) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+ truncate table median_test;
+ insert into median_test select i,i,i,i,i,i from generate_series(1,11) g(i);
+ select median(a),
+        median(b),
+        median(c),
+        median(d),
+        median(e),
+        median(f) from median_test;
+ select median(a) over (order by a),
+        median(b) over (order by a),
+        median(c) over (order by a),
+        median(d) over (order by a),
+        median(e) over (order by a),
+        median(f) over (order by a) from median_test;
+ 
+ drop table median_test;