Commits

intgr  committed 221c4f7

Fix window functions: xirr() OVER (...)

Finalize function may not change XirrState.

  • Participants
  • Parent commits b61fc43

Comments (0)

Files changed (3)

 
 extern Datum xirr_tstz_transfn(PG_FUNCTION_ARGS);
 extern Datum xirr_tstz_finalfn(PG_FUNCTION_ARGS);
-static double calculate_xirr(XirrState *state);
+static double calculate_xirr(XirrState *state, double guess);
 static double calculate_annualized_return(XirrState *state);
 
 /**** Implementation */
 /*
  * Aggregate finalize function. Takes the accumulated array and actually
  * calculates the result.
+ *
+ * May not change XirrState, otherwise it breaks window functions.
  */
 PG_FUNCTION_INFO_V1(xirr_tstz_finalfn);
 
 {
 	XirrState  *state;
 	double		ret;
+	double		guess;
 
 	/* no input rows */
 	if (PG_ARGISNULL(0))
 
 	/* Guess not provided as argument? */
 	if (isnan(state->guess))
-		state->guess = calculate_annualized_return(state);
+		guess = calculate_annualized_return(state);
+	else
+		guess = state->guess;
 
 	elog(DEBUG1, "Calculating XIRR over %d records, %ld MB memory, guess=%g",
 		 state->nelems, (long)((state->nelems*sizeof(XirrItem))/(1024*1024)),
-		 state->guess);
+		 guess);
 
-	ret = calculate_xirr(state);
+	ret = calculate_xirr(state, guess);
 
 	if (isnan(ret))
 		PG_RETURN_NULL();
 #endif
 
 static double
-calculate_xirr(XirrState *state)
+calculate_xirr(XirrState *state, double guess)
 {
 	int 		i, j;
-	double		guess = state->guess;
 	TimestampTz time0 = state->array[0].time;
 
 	/* Newton's method */

File test/expected/xirr.out

  -0.8
 (1 row)
 
+-- Window function
+SELECT xirr(amt, ts) OVER (ORDER BY ts) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (10, '2013-02-01'), (110, '2013-03-01')) x(amt, ts);
+     xirr     
+--------------
+             
+           -1
+ 2.2427441504
+(3 rows)
+

File test/sql/xirr.sql

 SELECT xirr(amt, ts, null) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (20, '2014-01-01')) x(amt, ts);
 SELECT xirr(amt, ts, 0.1) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (20, '2014-01-01')) x(amt, ts);
 SELECT xirr(amt, ts, -0.9999) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (20, '2014-01-01')) x(amt, ts);
+
+-- Window function
+SELECT xirr(amt, ts) OVER (ORDER BY ts) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (10, '2013-02-01'), (110, '2013-03-01')) x(amt, ts);