Re: Numeric version of factorial() - Mailing list pgsql-patches

Patch attached and applied.  Thanks.

I adjusted the oid so prevent a duplicate, and adjusted the regression
test to remove comments on now non-existant functions.

I also tested the output and it looks good:

    test=> select numeric_fac(10);
     numeric_fac
    -------------
         3628800
    (1 row)

    test=> select numeric_fac(1);
     numeric_fac
    -------------
               1
    (1 row)

    test=> select numeric_fac(2);
     numeric_fac
    -------------
               2
    (1 row)

    test=> select numeric_fac(4);
     numeric_fac
    -------------
              24
    (1 row)

    test=> select numeric_fac(3);
     numeric_fac
    -------------
               6
    (1 row)

I will check the docs now.

---------------------------------------------------------------------------


Gavin Sherry wrote:
> Attached is a patch implementing factorial(), returning numeric. Points to
> note:
>
> 1) arttype is numeric. I thought this was the best way of allowing
> arbitarily large factorials, even though factorial(2^63) is a large
> number. Happy to change to integers if this is overkill.
> 2) since we're accepting numeric arguments, the patch tests for floats. If
> a numeric is passed with non-zero decimal portion, an error is raised
> since (from memory) they are undefined.
> 3) I have not removed factorial([int2|int4|int8]), not their operator
> counterparts since I didn't know what people would want done with these.
> 4) I haven't added any documentation but am happy to once I know if people
> want int or numeric arguments.

> Attached is a revised patch based on your Tom's comments. It removes
> int[248]fac(), modifies regression tests (which referenced int4fac()),
> and implements a much cleaned numeric_fac().

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: src/backend/utils/adt/int.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/int.c,v
retrieving revision 1.58
diff -c -c -r1.58 int.c
*** src/backend/utils/adt/int.c    29 Nov 2003 19:51:58 -0000    1.58
--- src/backend/utils/adt/int.c    1 Dec 2003 21:30:41 -0000
***************
*** 26,32 ****
   *         intpl, intmi, int4mul, intdiv
   *
   *        Arithmetic operators:
!  *         intmod, int4fac
   */

  #include "postgres.h"
--- 26,32 ----
   *         intpl, intmi, int4mul, intdiv
   *
   *        Arithmetic operators:
!  *         intmod
   */

  #include "postgres.h"
***************
*** 849,888 ****
      PG_RETURN_INT32(arg1 % arg2);
  }

- /* int[24]fac()
-  * Factorial
-  */
- Datum
- int4fac(PG_FUNCTION_ARGS)
- {
-     int32        arg1 = PG_GETARG_INT32(0);
-     int32        result;
-
-     if (arg1 == 0)
-         result = 1;
-     else if (arg1 < 0)
-         result = 0;
-     else
-         for (result = 1; arg1 > 0; --arg1)
-             result *= arg1;
-     PG_RETURN_INT32(result);
- }
-
- Datum
- int2fac(PG_FUNCTION_ARGS)
- {
-     int16        arg1 = PG_GETARG_INT16(0);
-     int32        result;
-
-     if (arg1 == 0)
-         result = 1;
-     else if (arg1 < 0)
-         result = 0;
-     else
-         for (result = 1; arg1 > 0; --arg1)
-             result *= arg1;
-     PG_RETURN_INT32(result);
- }

  /* int[24]abs()
   * Absolute value
--- 849,854 ----
Index: src/backend/utils/adt/int8.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/int8.c,v
retrieving revision 1.49
diff -c -c -r1.49 int8.c
*** src/backend/utils/adt/int8.c    29 Nov 2003 19:51:58 -0000    1.49
--- src/backend/utils/adt/int8.c    1 Dec 2003 21:30:41 -0000
***************
*** 561,586 ****
      PG_RETURN_INT64(result);
  }

- /* int8fac()
-  * Factorial
-  */
- Datum
- int8fac(PG_FUNCTION_ARGS)
- {
-     int64        arg1 = PG_GETARG_INT64(0);
-     int64        result;
-     int64        i;
-
-     if (arg1 == 0)
-         result = 1;
-     else if (arg1 < 1)
-         result = 0;
-     else
-         for (i = arg1, result = 1; i > 0; --i)
-             result *= i;
-
-     PG_RETURN_INT64(result);
- }

  Datum
  int8inc(PG_FUNCTION_ARGS)
--- 561,566 ----
Index: src/backend/utils/adt/numeric.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/numeric.c,v
retrieving revision 1.68
diff -c -c -r1.68 numeric.c
*** src/backend/utils/adt/numeric.c    29 Nov 2003 19:51:59 -0000    1.68
--- src/backend/utils/adt/numeric.c    1 Dec 2003 21:30:45 -0000
***************
*** 1288,1293 ****
--- 1288,1342 ----
   * ----------------------------------------------------------------------
   */

+ /*
+  * numeric_fac()
+  * Computer factorial
+  */
+
+ Datum
+ numeric_fac(PG_FUNCTION_ARGS)
+ {
+
+     int64        num = PG_GETARG_INT64(0);
+     NumericVar    count;
+     NumericVar    fact;
+     NumericVar    zerovar;
+     NumericVar    result;
+     Numeric        res;
+
+     if(num < 1) {
+         res = make_result(&const_one);
+         PG_RETURN_NUMERIC(res);
+     }
+
+
+     init_var(&fact);
+     init_var(&count);
+     init_var(&result);
+     init_var(&zerovar);
+     zero_var(&zerovar);
+
+     int8_to_numericvar((int64)num, &result);
+     set_var_from_var(&const_one, &count);
+
+     for(num = num - 1; num > 0; num--) {
+         set_var_from_var(&result,&count);
+
+         int8_to_numericvar((int64)num,&fact);
+
+         mul_var(&count, &fact, &result, count.dscale + fact.dscale);
+     }
+
+     res = make_result(&count);
+
+     free_var(&count);
+     free_var(&fact);
+     free_var(&result);
+     free_var(&zerovar);
+
+     PG_RETURN_NUMERIC(res);
+ }
+

  /*
   * numeric_sqrt() -
Index: src/include/catalog/pg_operator.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/catalog/pg_operator.h,v
retrieving revision 1.122
diff -c -c -r1.122 pg_operator.h
*** src/include/catalog/pg_operator.h    29 Nov 2003 22:40:58 -0000    1.122
--- src/include/catalog/pg_operator.h    1 Dec 2003 21:30:48 -0000
***************
*** 122,133 ****

  DATA(insert OID = 352 (  "="       PGNSP PGUID b t    28    28    16 352     0     0     0     0     0 xideq eqsel
eqjoinsel)); 
  DATA(insert OID = 353 (  "="       PGNSP PGUID b f    28    23    16     0     0     0     0     0     0 xideqint4
eqseleqjoinsel )); 
  DATA(insert OID = 385 (  "="       PGNSP PGUID b t    29    29    16 385     0     0     0     0     0 cideq eqsel
eqjoinsel)); 
  DATA(insert OID = 386 (  "="       PGNSP PGUID b t    22    22    16 386     0     0     0     0     0 int2vectoreq
eqseleqjoinsel )); 
  DATA(insert OID = 387 (  "="       PGNSP PGUID b f    27    27    16 387     0     0     0     0     0 tideq eqsel
eqjoinsel)); 
  #define TIDEqualOperator   387
- DATA(insert OID = 388 (  "!"       PGNSP PGUID r f    20     0    20     0     0     0     0     0     0 int8fac - -
));
- DATA(insert OID = 389 (  "!!"       PGNSP PGUID l f     0    20    20     0     0     0     0     0     0 int8fac - -
));

  DATA(insert OID = 410 ( "="           PGNSP PGUID b t    20    20    16 410 411 412 412 412 413 int8eq eqsel
eqjoinsel)); 
  DATA(insert OID = 411 ( "<>"       PGNSP PGUID b f    20    20    16 411 410 0 0 0 0 int8ne neqsel neqjoinsel ));
--- 122,133 ----

  DATA(insert OID = 352 (  "="       PGNSP PGUID b t    28    28    16 352     0     0     0     0     0 xideq eqsel
eqjoinsel)); 
  DATA(insert OID = 353 (  "="       PGNSP PGUID b f    28    23    16     0     0     0     0     0     0 xideqint4
eqseleqjoinsel )); 
+ DATA(insert OID = 388 (  "!"       PGNSP PGUID r f  20   0  1700   0   0   0   0  0   0 numeric_fac - - ));
+ DATA(insert OID = 389 (  "!!"       PGNSP PGUID l f   0  20  1700   0   0   0   0  0   0 numeric_fac - - ));
  DATA(insert OID = 385 (  "="       PGNSP PGUID b t    29    29    16 385     0     0     0     0     0 cideq eqsel
eqjoinsel)); 
  DATA(insert OID = 386 (  "="       PGNSP PGUID b t    22    22    16 386     0     0     0     0     0 int2vectoreq
eqseleqjoinsel )); 
  DATA(insert OID = 387 (  "="       PGNSP PGUID b f    27    27    16 387     0     0     0     0     0 tideq eqsel
eqjoinsel)); 
  #define TIDEqualOperator   387

  DATA(insert OID = 410 ( "="           PGNSP PGUID b t    20    20    16 410 411 412 412 412 413 int8eq eqsel
eqjoinsel)); 
  DATA(insert OID = 411 ( "<>"       PGNSP PGUID b f    20    20    16 411 410 0 0 0 0 int8ne neqsel neqjoinsel ));
***************
*** 176,183 ****
  DATA(insert OID = 512 (  "@"       PGNSP PGUID b f 600 602    16 755     0     0     0     0     0 on_ppath - - ));
  DATA(insert OID = 513 (  "@@"       PGNSP PGUID l f     0 603 600     0     0     0     0     0     0 box_center - -
));
  DATA(insert OID = 514 (  "*"       PGNSP PGUID b f    23    23    23 514     0     0     0     0     0 int4mul - -
));
- DATA(insert OID = 515 (  "!"       PGNSP PGUID r f    23     0    23     0     0     0     0     0     0 int4fac - -
));
- DATA(insert OID = 516 (  "!!"       PGNSP PGUID l f     0    23    23     0     0     0     0     0     0 int4fac - -
));
  DATA(insert OID = 517 (  "<->"       PGNSP PGUID b f 600 600 701 517     0     0     0     0     0 point_distance - -
));
  DATA(insert OID = 518 (  "<>"       PGNSP PGUID b f    23    23    16 518    96    0  0   0   0 int4ne neqsel
neqjoinsel)); 
  DATA(insert OID = 519 (  "<>"       PGNSP PGUID b f    21    21    16 519    94    0  0   0   0 int2ne neqsel
neqjoinsel)); 
--- 176,181 ----
***************
*** 507,514 ****
  DATA(insert OID = 1134 (  "<="        PGNSP PGUID b f  701    700  16 1125 1133  0 0 0 0 float84le scalarltsel
scalarltjoinsel)); 
  DATA(insert OID = 1135 (  ">="        PGNSP PGUID b f  701    700  16 1124 1132  0 0 0 0 float84ge scalargtsel
scalargtjoinsel)); 

- DATA(insert OID = 1158 (  "!"        PGNSP PGUID r f 21      0   23 0 0 0 0 0 0 int2fac - - ));
- DATA(insert OID = 1175 (  "!!"        PGNSP PGUID l f  0     21   23 0 0 0 0 0 0 int2fac - - ));

  /* LIKE hacks by Keith Parks. */
  DATA(insert OID = 1207 (  "~~"      PGNSP PGUID b f  19    25    16 0 1208 0 0 0 0 namelike likesel likejoinsel ));
--- 505,510 ----
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/catalog/pg_proc.h,v
retrieving revision 1.316
diff -c -c -r1.316 pg_proc.h
*** src/include/catalog/pg_proc.h    29 Nov 2003 22:40:58 -0000    1.316
--- src/include/catalog/pg_proc.h    1 Dec 2003 21:30:53 -0000
***************
*** 208,215 ****

  /* OIDS 100 - 199 */

- DATA(insert OID = 100 (  int8fac           PGNSP PGUID 12 f f t f i 1 20 "20"  int8fac - _null_ ));
- DESCR("factorial");
  DATA(insert OID = 101 (  eqsel               PGNSP PGUID 12 f f t f s 4 701 "2281 26 2281 23"  eqsel - _null_ ));
  DESCR("restriction selectivity of = and related operators");
  DATA(insert OID = 102 (  neqsel               PGNSP PGUID 12 f f t f s 4 701 "2281 26 2281 23"  neqsel - _null_ ));
--- 208,213 ----
***************
*** 231,237 ****
  DESCR("I/O");
  DATA(insert OID =  110 (  unknownout       PGNSP PGUID 12 f f t f i 1 2275    "705"    unknownout - _null_ ));
  DESCR("I/O");
!
  DATA(insert OID = 112 (  text               PGNSP PGUID 12 f f t f i 1  25 "23"    int4_text - _null_ ));
  DESCR("convert int4 to text");
  DATA(insert OID = 113 (  text               PGNSP PGUID 12 f f t f i 1  25 "21"    int2_text - _null_ ));
--- 229,235 ----
  DESCR("I/O");
  DATA(insert OID =  110 (  unknownout       PGNSP PGUID 12 f f t f i 1 2275    "705"    unknownout - _null_ ));
  DESCR("I/O");
! DATA(insert OID = 111 (  numeric_fac       PGNSP PGUID 12 f f t f i 1 1700 "20"  numeric_fac - _null_ ));
  DATA(insert OID = 112 (  text               PGNSP PGUID 12 f f t f i 1  25 "23"    int4_text - _null_ ));
  DESCR("convert int4 to text");
  DATA(insert OID = 113 (  text               PGNSP PGUID 12 f f t f i 1  25 "21"    int2_text - _null_ ));
***************
*** 294,301 ****
  DESCR("join selectivity for area-comparison operators");
  DATA(insert OID = 141 (  int4mul           PGNSP PGUID 12 f f t f i 2 23 "23 23"    int4mul - _null_ ));
  DESCR("multiply");
- DATA(insert OID = 142 (  int4fac           PGNSP PGUID 12 f f t f i 1 23 "23"  int4fac - _null_ ));
- DESCR("factorial");
  DATA(insert OID = 144 (  int4ne               PGNSP PGUID 12 f f t f i 2 16 "23 23"    int4ne - _null_ ));
  DESCR("not equal");
  DATA(insert OID = 145 (  int2ne               PGNSP PGUID 12 f f t f i 2 16 "21 21"    int2ne - _null_ ));
--- 292,297 ----
***************
*** 571,579 ****
  DATA(insert OID = 275 (  isfinite           PGNSP PGUID 12 f f t f i 1 16 "702"    abstime_finite - _null_ ));
  DESCR("finite abstime?");

- DATA(insert OID = 276 (  int2fac           PGNSP PGUID 12 f f t f i 1 23 "21"  int2fac - _null_ ));
- DESCR("factorial");
-
  DATA(insert OID = 277 (  inter_sl           PGNSP PGUID 12 f f t f i 2 16 "601 628"    inter_sl - _null_ ));
  DESCR("intersect?");
  DATA(insert OID = 278 (  inter_lb           PGNSP PGUID 12 f f t f i 2 16 "628 603"    inter_lb - _null_ ));
--- 567,572 ----
***************
*** 1758,1768 ****
  DESCR("finite interval?");


! DATA(insert OID = 1391 (  factorial           PGNSP PGUID 12 f f t f i 1 23 "21"  int2fac - _null_ ));
! DESCR("factorial");
! DATA(insert OID = 1392 (  factorial           PGNSP PGUID 12 f f t f i 1 23 "23"  int4fac - _null_ ));
! DESCR("factorial");
! DATA(insert OID = 1393 (  factorial           PGNSP PGUID 12 f f t f i 1 20 "20"  int8fac - _null_ ));
  DESCR("factorial");
  DATA(insert OID = 1394 (  abs               PGNSP PGUID 12 f f t f i 1 700 "700"  float4abs - _null_ ));
  DESCR("absolute value");
--- 1751,1757 ----
  DESCR("finite interval?");


! DATA(insert OID = 1376 (  factorial        PGNSP PGUID 12 f f t f i 1 1700 "20"  numeric_fac - _null_ ));
  DESCR("factorial");
  DATA(insert OID = 1394 (  abs               PGNSP PGUID 12 f f t f i 1 700 "700"  float4abs - _null_ ));
  DESCR("absolute value");
Index: src/include/utils/builtins.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/utils/builtins.h,v
retrieving revision 1.230
diff -c -c -r1.230 builtins.h
*** src/include/utils/builtins.h    29 Nov 2003 22:41:15 -0000    1.230
--- src/include/utils/builtins.h    1 Dec 2003 21:30:53 -0000
***************
*** 159,166 ****
  extern Datum int2mod(PG_FUNCTION_ARGS);
  extern Datum int24mod(PG_FUNCTION_ARGS);
  extern Datum int42mod(PG_FUNCTION_ARGS);
- extern Datum int4fac(PG_FUNCTION_ARGS);
- extern Datum int2fac(PG_FUNCTION_ARGS);
  extern Datum int2larger(PG_FUNCTION_ARGS);
  extern Datum int2smaller(PG_FUNCTION_ARGS);
  extern Datum int4larger(PG_FUNCTION_ARGS);
--- 159,164 ----
***************
*** 720,725 ****
--- 718,724 ----
  extern Datum numeric_inc(PG_FUNCTION_ARGS);
  extern Datum numeric_smaller(PG_FUNCTION_ARGS);
  extern Datum numeric_larger(PG_FUNCTION_ARGS);
+ extern Datum numeric_fac(PG_FUNCTION_ARGS);
  extern Datum numeric_sqrt(PG_FUNCTION_ARGS);
  extern Datum numeric_exp(PG_FUNCTION_ARGS);
  extern Datum numeric_ln(PG_FUNCTION_ARGS);
Index: src/include/utils/int8.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/utils/int8.h,v
retrieving revision 1.39
diff -c -c -r1.39 int8.h
*** src/include/utils/int8.h    29 Nov 2003 22:41:15 -0000    1.39
--- src/include/utils/int8.h    1 Dec 2003 21:30:53 -0000
***************
*** 72,78 ****
  extern Datum int8mul(PG_FUNCTION_ARGS);
  extern Datum int8div(PG_FUNCTION_ARGS);
  extern Datum int8abs(PG_FUNCTION_ARGS);
- extern Datum int8fac(PG_FUNCTION_ARGS);
  extern Datum int8mod(PG_FUNCTION_ARGS);
  extern Datum int8inc(PG_FUNCTION_ARGS);
  extern Datum int8larger(PG_FUNCTION_ARGS);
--- 72,77 ----
Index: src/test/regress/expected/create_operator.out
===================================================================
RCS file: /cvsroot/pgsql-server/src/test/regress/expected/create_operator.out,v
retrieving revision 1.5
diff -c -c -r1.5 create_operator.out
*** src/test/regress/expected/create_operator.out    21 Nov 2003 22:32:49 -0000    1.5
--- src/test/regress/expected/create_operator.out    1 Dec 2003 21:30:54 -0000
***************
*** 15,33 ****
     negator = >=%
  );
  CREATE OPERATOR @#@ (
!    rightarg = int4,        -- left unary
!    procedure = int4fac
  );
  CREATE OPERATOR #@# (
!    leftarg = int4,        -- right unary
!    procedure = int4fac
  );
  CREATE OPERATOR #%# (
!    leftarg = int4,        -- right unary
!    procedure = int4fac
  );
  -- Test comments
  COMMENT ON OPERATOR ###### (int4, NONE) IS 'bad right unary';
  ERROR:  operator does not exist: integer ######
- COMMENT ON OPERATOR #%# (int4, NONE) IS 'right unary';
- COMMENT ON OPERATOR #%# (int4, NONE) IS NULL;
--- 15,31 ----
     negator = >=%
  );
  CREATE OPERATOR @#@ (
!    rightarg = int8,        -- left unary
!    procedure = numeric_fac
  );
  CREATE OPERATOR #@# (
!    leftarg = int8,        -- right unary
!    procedure = numeric_fac
  );
  CREATE OPERATOR #%# (
!    leftarg = int8,        -- right unary
!    procedure = numeric_fac
  );
  -- Test comments
  COMMENT ON OPERATOR ###### (int4, NONE) IS 'bad right unary';
  ERROR:  operator does not exist: integer ######
Index: src/test/regress/sql/create_operator.sql
===================================================================
RCS file: /cvsroot/pgsql-server/src/test/regress/sql/create_operator.sql,v
retrieving revision 1.5
diff -c -c -r1.5 create_operator.sql
*** src/test/regress/sql/create_operator.sql    21 Nov 2003 22:32:49 -0000    1.5
--- src/test/regress/sql/create_operator.sql    1 Dec 2003 21:30:54 -0000
***************
*** 18,40 ****
  );

  CREATE OPERATOR @#@ (
!    rightarg = int4,        -- left unary
!    procedure = int4fac
  );

  CREATE OPERATOR #@# (
!    leftarg = int4,        -- right unary
!    procedure = int4fac
  );

  CREATE OPERATOR #%# (
!    leftarg = int4,        -- right unary
!    procedure = int4fac
  );

  -- Test comments
  COMMENT ON OPERATOR ###### (int4, NONE) IS 'bad right unary';
- COMMENT ON OPERATOR #%# (int4, NONE) IS 'right unary';
- COMMENT ON OPERATOR #%# (int4, NONE) IS NULL;


--- 18,38 ----
  );

  CREATE OPERATOR @#@ (
!    rightarg = int8,        -- left unary
!    procedure = numeric_fac
  );

  CREATE OPERATOR #@# (
!    leftarg = int8,        -- right unary
!    procedure = numeric_fac
  );

  CREATE OPERATOR #%# (
!    leftarg = int8,        -- right unary
!    procedure = numeric_fac
  );

  -- Test comments
  COMMENT ON OPERATOR ###### (int4, NONE) IS 'bad right unary';