3 * Written by D'Arcy J.M. Cain
5 * Functions to allow input and output of money normally but store
6 * and handle it as int4s
8 * A slightly modified version of this file and a discussion of the
9 * workings can be found in the book "Software Solutions in C" by
10 * Dale Schumacher, Academic Press, ISBN: 0-12-632360-7.
12 * $Header: /cvsroot/pgsql/src/backend/utils/adt/cash.c,v 1.44 2000/08/01 18:29:35 tgl Exp $
21 #include "miscadmin.h"
22 #include "utils/builtins.h"
23 #include "utils/cash.h"
25 static const char *num_word(Cash value);
27 /* when we go to 64 bit values we will have to modify this */
30 #define TERMINATOR (CASH_BUFSZ - 1)
31 #define LAST_PAREN (TERMINATOR - 1)
32 #define LAST_DIGIT (LAST_PAREN - 1)
35 static struct lconv *lconvert = NULL;
41 * Cash is a pass-by-ref SQL type, so we must pass and return pointers.
42 * These macros and support routine hide the pass-by-refness.
44 #define PG_GETARG_CASH(n) (* ((Cash *) PG_GETARG_POINTER(n)))
45 #define PG_RETURN_CASH(x) return CashGetDatum(x)
48 CashGetDatum(Cash value)
50 Cash *result = (Cash *) palloc(sizeof(Cash));
53 return PointerGetDatum(result);
58 * Convert a string to a cash data type.
59 * Format is [$]###[,]###[.##]
60 * Examples: 123.45 $123.45 $123,456.78
62 * This is currently implemented as a 32-bit integer.
63 * XXX HACK It looks as though some of the symbols for
64 * monetary values returned by localeconv() can be multiple
65 * bytes/characters. This code assumes one byte only. - tgl 97/04/14
66 * XXX UNHACK Allow the currency symbol to be multi-byte.
70 cash_in(const char *str)
88 setlocale(LC_ALL, "");
89 lconvert = localeconv();
92 lconvert = localeconv();
94 /* frac_digits in the C locale seems to return CHAR_MAX */
95 /* best guess is 2 in this case I think */
96 fpoint = ((lconvert->frac_digits != (char)CHAR_MAX) ? lconvert->frac_digits : 2); /* int_frac_digits? */
98 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
99 ssymbol = ((*lconvert->mon_thousands_sep != '\0') ? *lconvert->mon_thousands_sep : ',');
100 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
101 psymbol = ((*lconvert->positive_sign != '\0') ? *lconvert->positive_sign : '+');
102 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
113 printf("cashin- precision '%d'; decimal '%c'; thousands '%c'; currency '%s'; positive '%c'; negative '%s'\n",
114 fpoint, dsymbol, ssymbol, csymbol, psymbol, nsymbol);
117 /* we need to add all sorts of checking here. For now just */
118 /* strip all leading whitespace and any leading currency symbol */
119 while (isspace((int) *s))
121 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
122 s += strlen(csymbol);
125 printf("cashin- string is '%s'\n", s);
128 /* a leading minus or paren signifies a negative number */
129 /* again, better heuristics needed */
130 if (strncmp(s, nsymbol, strlen(nsymbol)) == 0)
133 s += strlen(nsymbol);
135 printf("cashin- negative symbol; string is '%s'\n", s);
144 else if (*s == psymbol)
148 printf("cashin- string is '%s'\n", s);
151 while (isspace((int) *s))
153 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
154 s += strlen(csymbol);
157 printf("cashin- string is '%s'\n", s);
162 /* we look for digits as int4 as we have less */
163 /* than the required number of decimal places */
164 if (isdigit((int) *s) && dec < fpoint)
166 value = (value * 10) + *s - '0';
171 /* decimal point? then start counting fractions... */
173 else if (*s == dsymbol && !seen_dot)
177 /* "thousands" separator? then skip... */
179 else if (*s == ssymbol)
186 if (isdigit((int) *s) && *s >= '5')
189 /* adjust for less than required decimal places */
190 for (; dec < fpoint; dec++)
197 while (isspace((int) *s) || *s == '0' || *s == ')')
201 elog(ERROR, "Bad money external representation %s", str);
203 if (!PointerIsValid(result = palloc(sizeof(Cash))))
204 elog(ERROR, "Memory allocation failed, can't input cash '%s'", str);
206 *result = (value * sgn);
209 printf("cashin- result is %d\n", *result);
217 * Function to convert cash to a dollars and cents representation.
218 * XXX HACK This code appears to assume US conventions for
219 * positive-valued amounts. - tgl 97/04/14
222 cash_out(Cash *in_value)
224 Cash value = *in_value;
226 char buf[CASH_BUFSZ];
228 int count = LAST_DIGIT;
230 int comma_position = 0;
240 if (lconvert == NULL)
241 lconvert = localeconv();
243 mon_group = *lconvert->mon_grouping;
244 comma = ((*lconvert->mon_thousands_sep != '\0') ? *lconvert->mon_thousands_sep : ',');
245 /* frac_digits in the C locale seems to return CHAR_MAX */
246 /* best guess is 2 in this case I think */
247 points = ((lconvert->frac_digits != (char)CHAR_MAX) ? lconvert->frac_digits : 2); /* int_frac_digits? */
248 convention = lconvert->n_sign_posn;
249 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
250 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
251 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
262 point_pos = LAST_DIGIT - points;
264 /* We're playing a little fast and loose with this. Shoot me. */
265 /* Not me, that was the other guy. Haven't fixed it yet - thomas */
266 if (!mon_group || mon_group == (char)CHAR_MAX)
269 /* allow more than three decimal points and separate them */
272 point_pos -= (points - 1) / mon_group;
273 comma_position = point_pos % (mon_group + 1);
276 /* we work with positive amounts and add the minus sign at the end */
283 /* allow for trailing negative strings */
284 MemSet(buf, ' ', CASH_BUFSZ);
285 buf[TERMINATOR] = buf[LAST_PAREN] = '\0';
287 while (value || count > (point_pos - 2))
289 if (points && count == point_pos)
290 buf[count--] = dsymbol;
291 else if (comma && count % (mon_group + 1) == comma_position)
292 buf[count--] = comma;
294 buf[count--] = (value % 10) + '0';
298 strncpy((buf + count - strlen(csymbol) + 1), csymbol, strlen(csymbol));
299 count -= strlen(csymbol) - 1;
301 if (buf[LAST_DIGIT] == ',')
302 buf[LAST_DIGIT] = buf[LAST_PAREN];
304 /* see if we need to signify negative amount */
307 if (!PointerIsValid(result = palloc(CASH_BUFSZ + 2 - count + strlen(nsymbol))))
308 elog(ERROR, "Memory allocation failed, can't output cash");
310 /* Position code of 0 means use parens */
312 sprintf(result, "(%s)", buf + count);
313 else if (convention == 2)
314 sprintf(result, "%s%s", buf + count, nsymbol);
316 sprintf(result, "%s%s", nsymbol, buf + count);
320 if (!PointerIsValid(result = palloc(CASH_BUFSZ + 2 - count)))
321 elog(ERROR, "Memory allocation failed, can't output cash");
323 strcpy(result, buf + count);
331 cash_eq(Cash *c1, Cash *c2)
333 if (!PointerIsValid(c1) || !PointerIsValid(c2))
340 cash_ne(Cash *c1, Cash *c2)
342 if (!PointerIsValid(c1) || !PointerIsValid(c2))
349 cash_lt(Cash *c1, Cash *c2)
351 if (!PointerIsValid(c1) || !PointerIsValid(c2))
358 cash_le(Cash *c1, Cash *c2)
360 if (!PointerIsValid(c1) || !PointerIsValid(c2))
367 cash_gt(Cash *c1, Cash *c2)
369 if (!PointerIsValid(c1) || !PointerIsValid(c2))
376 cash_ge(Cash *c1, Cash *c2)
378 if (!PointerIsValid(c1) || !PointerIsValid(c2))
386 * Add two cash values.
389 cash_pl(Cash *c1, Cash *c2)
393 if (!PointerIsValid(c1) || !PointerIsValid(c2))
396 if (!PointerIsValid(result = palloc(sizeof(Cash))))
397 elog(ERROR, "Memory allocation failed, can't add cash");
399 *result = (*c1 + *c2);
406 * Subtract two cash values.
409 cash_mi(Cash *c1, Cash *c2)
413 if (!PointerIsValid(c1) || !PointerIsValid(c2))
416 if (!PointerIsValid(result = palloc(sizeof(Cash))))
417 elog(ERROR, "Memory allocation failed, can't subtract cash");
419 *result = (*c1 - *c2);
426 * Multiply cash by float8.
429 cash_mul_flt8(PG_FUNCTION_ARGS)
431 Cash c = PG_GETARG_CASH(0);
432 float8 f = PG_GETARG_FLOAT8(1);
436 PG_RETURN_CASH(result);
441 * Multiply float8 by cash.
444 flt8_mul_cash(PG_FUNCTION_ARGS)
446 float8 f = PG_GETARG_FLOAT8(0);
447 Cash c = PG_GETARG_CASH(1);
451 PG_RETURN_CASH(result);
456 * Divide cash by float8.
458 * XXX Don't know if rounding or truncating is correct behavior.
459 * Round for now. - tgl 97/04/15
462 cash_div_flt8(PG_FUNCTION_ARGS)
464 Cash c = PG_GETARG_CASH(0);
465 float8 f = PG_GETARG_FLOAT8(1);
469 elog(ERROR, "cash_div: divide by 0.0 error");
471 result = rint(c / f);
472 PG_RETURN_CASH(result);
476 * Multiply cash by float4.
479 cash_mul_flt4(PG_FUNCTION_ARGS)
481 Cash c = PG_GETARG_CASH(0);
482 float4 f = PG_GETARG_FLOAT4(1);
486 PG_RETURN_CASH(result);
491 * Multiply float4 by cash.
494 flt4_mul_cash(PG_FUNCTION_ARGS)
496 float4 f = PG_GETARG_FLOAT4(0);
497 Cash c = PG_GETARG_CASH(1);
501 PG_RETURN_CASH(result);
506 * Divide cash by float4.
508 * XXX Don't know if rounding or truncating is correct behavior.
509 * Round for now. - tgl 97/04/15
512 cash_div_flt4(PG_FUNCTION_ARGS)
514 Cash c = PG_GETARG_CASH(0);
515 float4 f = PG_GETARG_FLOAT4(1);
519 elog(ERROR, "cash_div: divide by 0.0 error");
521 result = rint(c / f);
522 PG_RETURN_CASH(result);
527 * Multiply cash by int4.
530 cash_mul_int4(PG_FUNCTION_ARGS)
532 Cash c = PG_GETARG_CASH(0);
533 int32 i = PG_GETARG_INT32(1);
537 PG_RETURN_CASH(result);
542 * Multiply int4 by cash.
545 int4_mul_cash(PG_FUNCTION_ARGS)
547 int32 i = PG_GETARG_INT32(0);
548 Cash c = PG_GETARG_CASH(1);
552 PG_RETURN_CASH(result);
557 * Divide cash by 4-byte integer.
559 * XXX Don't know if rounding or truncating is correct behavior.
560 * Round for now. - tgl 97/04/15
563 cash_div_int4(PG_FUNCTION_ARGS)
565 Cash c = PG_GETARG_CASH(0);
566 int32 i = PG_GETARG_INT32(1);
570 elog(ERROR, "cash_div_int4: divide by 0 error");
572 result = rint(c / i);
574 PG_RETURN_CASH(result);
579 * Multiply cash by int2.
582 cash_mul_int2(PG_FUNCTION_ARGS)
584 Cash c = PG_GETARG_CASH(0);
585 int16 s = PG_GETARG_INT16(1);
589 PG_RETURN_CASH(result);
593 * Multiply int2 by cash.
596 int2_mul_cash(PG_FUNCTION_ARGS)
598 int16 s = PG_GETARG_INT16(0);
599 Cash c = PG_GETARG_CASH(1);
603 PG_RETURN_CASH(result);
607 * Divide cash by int2.
609 * XXX Don't know if rounding or truncating is correct behavior.
610 * Round for now. - tgl 97/04/15
613 cash_div_int2(PG_FUNCTION_ARGS)
615 Cash c = PG_GETARG_CASH(0);
616 int16 s = PG_GETARG_INT16(1);
620 elog(ERROR, "cash_div: divide by 0 error");
622 result = rint(c / s);
623 PG_RETURN_CASH(result);
627 * Return larger of two cash values.
630 cashlarger(Cash *c1, Cash *c2)
634 if (!PointerIsValid(c1) || !PointerIsValid(c2))
637 if (!PointerIsValid(result = palloc(sizeof(Cash))))
638 elog(ERROR, "Memory allocation failed, can't return larger cash");
640 *result = ((*c1 > *c2) ? *c1 : *c2);
647 * Return smaller of two cash values.
650 cashsmaller(Cash *c1, Cash *c2)
654 if (!PointerIsValid(c1) || !PointerIsValid(c2))
657 if (!PointerIsValid(result = palloc(sizeof(Cash))))
658 elog(ERROR, "Memory allocation failed, can't return smaller cash");
660 *result = ((*c1 < *c2) ? *c1 : *c2);
663 } /* cashsmaller() */
667 * This converts a int4 as well but to a representation using words
668 * Obviously way North American centric - sorry
671 cash_words(PG_FUNCTION_ARGS)
673 Cash value = PG_GETARG_CASH(0);
682 /* work with positive numbers */
686 strcpy(buf, "minus ");
692 m0 = value % 100; /* cents */
693 m1 = (value / 100) % 1000; /* hundreds */
694 m2 = (value / 100000) % 1000; /* thousands */
695 m3 = value / 100000000 % 1000; /* millions */
699 strcat(buf, num_word(m3));
700 strcat(buf, " million ");
705 strcat(buf, num_word(m2));
706 strcat(buf, " thousand ");
710 strcat(buf, num_word(m1));
715 strcat(buf, (int) (value / 100) == 1 ? " dollar and " : " dollars and ");
716 strcat(buf, num_word(m0));
717 strcat(buf, m0 == 1 ? " cent" : " cents");
719 /* capitalize output */
720 buf[0] = toupper(buf[0]);
722 /* make a text type for output */
723 result = (text *) palloc(strlen(buf) + VARHDRSZ);
724 VARATT_SIZEP(result) = strlen(buf) + VARHDRSZ;
725 memcpy(VARDATA(result), buf, strlen(buf));
727 PG_RETURN_TEXT_P(result);
731 /*************************************************************************
733 ************************************************************************/
738 static char buf[128];
739 static const char *small[] = {
740 "zero", "one", "two", "three", "four", "five", "six", "seven",
741 "eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen",
742 "fifteen", "sixteen", "seventeen", "eighteen", "nineteen", "twenty",
743 "thirty", "fourty", "fifty", "sixty", "seventy", "eighty", "ninety"
745 const char **big = small + 18;
746 int tu = value % 100;
748 /* deal with the simple cases first */
752 /* is it an even multiple of 100? */
755 sprintf(buf, "%s hundred", small[value / 100]);
762 /* is it an even multiple of 10 other than 10? */
763 if (value % 10 == 0 && tu > 10)
764 sprintf(buf, "%s hundred %s",
765 small[value / 100], big[tu / 10]);
767 sprintf(buf, "%s hundred and %s",
768 small[value / 100], small[tu]);
770 sprintf(buf, "%s hundred %s %s",
771 small[value / 100], big[tu / 10], small[tu % 10]);
776 /* is it an even multiple of 10 other than 10? */
777 if (value % 10 == 0 && tu > 10)
778 sprintf(buf, "%s", big[tu / 10]);
780 sprintf(buf, "%s", small[tu]);
782 sprintf(buf, "%s %s", big[tu / 10], small[tu % 10]);