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.53 2002/04/03 05:39:29 petere Exp $
22 #include "miscadmin.h"
23 #include "utils/builtins.h"
24 #include "utils/cash.h"
25 #include "utils/pg_locale.h"
28 static const char *num_word(Cash value);
30 /* when we go to 64 bit values we will have to modify this */
33 #define TERMINATOR (CASH_BUFSZ - 1)
34 #define LAST_PAREN (TERMINATOR - 1)
35 #define LAST_DIGIT (LAST_PAREN - 1)
39 * Cash is a pass-by-ref SQL type, so we must pass and return pointers.
40 * These macros and support routine hide the pass-by-refness.
42 #define PG_GETARG_CASH(n) (* ((Cash *) PG_GETARG_POINTER(n)))
43 #define PG_RETURN_CASH(x) return CashGetDatum(x)
46 CashGetDatum(Cash value)
48 Cash *result = (Cash *) palloc(sizeof(Cash));
51 return PointerGetDatum(result);
56 * Convert a string to a cash data type.
57 * Format is [$]###[,]###[.##]
58 * Examples: 123.45 $123.45 $123,456.78
60 * This is currently implemented as a 32-bit integer.
61 * XXX HACK It looks as though some of the symbols for
62 * monetary values returned by localeconv() can be multiple
63 * bytes/characters. This code assumes one byte only. - tgl 97/04/14
64 * XXX UNHACK Allow the currency symbol to be multi-byte.
68 cash_in(PG_FUNCTION_ARGS)
70 char *str = PG_GETARG_CSTRING(0);
84 struct lconv *lconvert = PGLC_localeconv();
87 * frac_digits will be CHAR_MAX in some locales, notably C. However,
88 * just testing for == CHAR_MAX is risky, because of compilers like
89 * gcc that "helpfully" let you alter the platform-standard definition
90 * of whether char is signed or not. If we are so unfortunate as to
91 * get compiled with a nonstandard -fsigned-char or -funsigned-char
92 * switch, then our idea of CHAR_MAX will not agree with libc's. The
93 * safest course is not to test for CHAR_MAX at all, but to impose a
94 * range check for plausible frac_digits values.
96 fpoint = lconvert->frac_digits;
97 if (fpoint < 0 || fpoint > 10)
98 fpoint = 2; /* best guess in this case, I think */
100 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
101 ssymbol = ((*lconvert->mon_thousands_sep != '\0') ? *lconvert->mon_thousands_sep : ',');
102 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
103 psymbol = ((*lconvert->positive_sign != '\0') ? *lconvert->positive_sign : '+');
104 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
107 printf("cashin- precision '%d'; decimal '%c'; thousands '%c'; currency '%s'; positive '%c'; negative '%s'\n",
108 fpoint, dsymbol, ssymbol, csymbol, psymbol, nsymbol);
111 /* we need to add all sorts of checking here. For now just */
112 /* strip all leading whitespace and any leading currency symbol */
113 while (isspace((unsigned char) *s))
115 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
116 s += strlen(csymbol);
119 printf("cashin- string is '%s'\n", s);
122 /* a leading minus or paren signifies a negative number */
123 /* again, better heuristics needed */
124 if (strncmp(s, nsymbol, strlen(nsymbol)) == 0)
127 s += strlen(nsymbol);
129 printf("cashin- negative symbol; string is '%s'\n", s);
138 else if (*s == psymbol)
142 printf("cashin- string is '%s'\n", s);
145 while (isspace((unsigned char) *s))
147 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
148 s += strlen(csymbol);
151 printf("cashin- string is '%s'\n", s);
156 /* we look for digits as int4 as we have less */
157 /* than the required number of decimal places */
158 if (isdigit((unsigned char) *s) && dec < fpoint)
160 value = (value * 10) + *s - '0';
165 /* decimal point? then start counting fractions... */
167 else if (*s == dsymbol && !seen_dot)
171 /* "thousands" separator? then skip... */
173 else if (*s == ssymbol)
180 if (isdigit((unsigned char) *s) && *s >= '5')
183 /* adjust for less than required decimal places */
184 for (; dec < fpoint; dec++)
191 while (isspace((unsigned char) *s) || *s == '0' || *s == ')')
195 elog(ERROR, "Bad money external representation %s", str);
197 result = (value * sgn);
200 printf("cashin- result is %d\n", result);
203 PG_RETURN_CASH(result);
208 * Function to convert cash to a dollars and cents representation.
209 * XXX HACK This code appears to assume US conventions for
210 * positive-valued amounts. - tgl 97/04/14
213 cash_out(PG_FUNCTION_ARGS)
215 Cash value = PG_GETARG_CASH(0);
217 char buf[CASH_BUFSZ];
219 int count = LAST_DIGIT;
221 int comma_position = 0;
230 struct lconv *lconvert = PGLC_localeconv();
232 /* see comments about frac_digits in cash_in() */
233 points = lconvert->frac_digits;
234 if (points < 0 || points > 10)
235 points = 2; /* best guess in this case, I think */
238 * As with frac_digits, must apply a range check to mon_grouping to
239 * avoid being fooled by variant CHAR_MAX values.
241 mon_group = *lconvert->mon_grouping;
242 if (mon_group <= 0 || mon_group > 6)
245 comma = ((*lconvert->mon_thousands_sep != '\0') ? *lconvert->mon_thousands_sep : ',');
246 convention = lconvert->n_sign_posn;
247 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
248 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
249 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
251 point_pos = LAST_DIGIT - points;
253 /* allow more than three decimal points and separate them */
256 point_pos -= (points - 1) / mon_group;
257 comma_position = point_pos % (mon_group + 1);
260 /* we work with positive amounts and add the minus sign at the end */
267 /* allow for trailing negative strings */
268 MemSet(buf, ' ', CASH_BUFSZ);
269 buf[TERMINATOR] = buf[LAST_PAREN] = '\0';
271 while (value || count > (point_pos - 2))
273 if (points && count == point_pos)
274 buf[count--] = dsymbol;
275 else if (comma && count % (mon_group + 1) == comma_position)
276 buf[count--] = comma;
278 buf[count--] = ((unsigned int) value % 10) + '0';
279 value = ((unsigned int) value) / 10;
282 strncpy((buf + count - strlen(csymbol) + 1), csymbol, strlen(csymbol));
283 count -= strlen(csymbol) - 1;
285 if (buf[LAST_DIGIT] == ',')
286 buf[LAST_DIGIT] = buf[LAST_PAREN];
288 /* see if we need to signify negative amount */
291 if (!PointerIsValid(result = palloc(CASH_BUFSZ + 2 - count + strlen(nsymbol))))
292 elog(ERROR, "Memory allocation failed, can't output cash");
294 /* Position code of 0 means use parens */
296 sprintf(result, "(%s)", buf + count);
297 else if (convention == 2)
298 sprintf(result, "%s%s", buf + count, nsymbol);
300 sprintf(result, "%s%s", nsymbol, buf + count);
304 if (!PointerIsValid(result = palloc(CASH_BUFSZ + 2 - count)))
305 elog(ERROR, "Memory allocation failed, can't output cash");
307 strcpy(result, buf + count);
310 PG_RETURN_CSTRING(result);
315 cash_eq(PG_FUNCTION_ARGS)
317 Cash c1 = PG_GETARG_CASH(0);
318 Cash c2 = PG_GETARG_CASH(1);
320 PG_RETURN_BOOL(c1 == c2);
324 cash_ne(PG_FUNCTION_ARGS)
326 Cash c1 = PG_GETARG_CASH(0);
327 Cash c2 = PG_GETARG_CASH(1);
329 PG_RETURN_BOOL(c1 != c2);
333 cash_lt(PG_FUNCTION_ARGS)
335 Cash c1 = PG_GETARG_CASH(0);
336 Cash c2 = PG_GETARG_CASH(1);
338 PG_RETURN_BOOL(c1 < c2);
342 cash_le(PG_FUNCTION_ARGS)
344 Cash c1 = PG_GETARG_CASH(0);
345 Cash c2 = PG_GETARG_CASH(1);
347 PG_RETURN_BOOL(c1 <= c2);
351 cash_gt(PG_FUNCTION_ARGS)
353 Cash c1 = PG_GETARG_CASH(0);
354 Cash c2 = PG_GETARG_CASH(1);
356 PG_RETURN_BOOL(c1 > c2);
360 cash_ge(PG_FUNCTION_ARGS)
362 Cash c1 = PG_GETARG_CASH(0);
363 Cash c2 = PG_GETARG_CASH(1);
365 PG_RETURN_BOOL(c1 >= c2);
370 * Add two cash values.
373 cash_pl(PG_FUNCTION_ARGS)
375 Cash c1 = PG_GETARG_CASH(0);
376 Cash c2 = PG_GETARG_CASH(1);
381 PG_RETURN_CASH(result);
386 * Subtract two cash values.
389 cash_mi(PG_FUNCTION_ARGS)
391 Cash c1 = PG_GETARG_CASH(0);
392 Cash c2 = PG_GETARG_CASH(1);
397 PG_RETURN_CASH(result);
402 * Multiply cash by float8.
405 cash_mul_flt8(PG_FUNCTION_ARGS)
407 Cash c = PG_GETARG_CASH(0);
408 float8 f = PG_GETARG_FLOAT8(1);
412 PG_RETURN_CASH(result);
417 * Multiply float8 by cash.
420 flt8_mul_cash(PG_FUNCTION_ARGS)
422 float8 f = PG_GETARG_FLOAT8(0);
423 Cash c = PG_GETARG_CASH(1);
427 PG_RETURN_CASH(result);
432 * Divide cash by float8.
434 * XXX Don't know if rounding or truncating is correct behavior.
435 * Round for now. - tgl 97/04/15
438 cash_div_flt8(PG_FUNCTION_ARGS)
440 Cash c = PG_GETARG_CASH(0);
441 float8 f = PG_GETARG_FLOAT8(1);
445 elog(ERROR, "cash_div: divide by 0.0 error");
447 result = rint(c / f);
448 PG_RETURN_CASH(result);
452 * Multiply cash by float4.
455 cash_mul_flt4(PG_FUNCTION_ARGS)
457 Cash c = PG_GETARG_CASH(0);
458 float4 f = PG_GETARG_FLOAT4(1);
462 PG_RETURN_CASH(result);
467 * Multiply float4 by cash.
470 flt4_mul_cash(PG_FUNCTION_ARGS)
472 float4 f = PG_GETARG_FLOAT4(0);
473 Cash c = PG_GETARG_CASH(1);
477 PG_RETURN_CASH(result);
482 * Divide cash by float4.
484 * XXX Don't know if rounding or truncating is correct behavior.
485 * Round for now. - tgl 97/04/15
488 cash_div_flt4(PG_FUNCTION_ARGS)
490 Cash c = PG_GETARG_CASH(0);
491 float4 f = PG_GETARG_FLOAT4(1);
495 elog(ERROR, "cash_div: divide by 0.0 error");
497 result = rint(c / f);
498 PG_RETURN_CASH(result);
503 * Multiply cash by int4.
506 cash_mul_int4(PG_FUNCTION_ARGS)
508 Cash c = PG_GETARG_CASH(0);
509 int32 i = PG_GETARG_INT32(1);
513 PG_RETURN_CASH(result);
518 * Multiply int4 by cash.
521 int4_mul_cash(PG_FUNCTION_ARGS)
523 int32 i = PG_GETARG_INT32(0);
524 Cash c = PG_GETARG_CASH(1);
528 PG_RETURN_CASH(result);
533 * Divide cash by 4-byte integer.
535 * XXX Don't know if rounding or truncating is correct behavior.
536 * Round for now. - tgl 97/04/15
539 cash_div_int4(PG_FUNCTION_ARGS)
541 Cash c = PG_GETARG_CASH(0);
542 int32 i = PG_GETARG_INT32(1);
546 elog(ERROR, "cash_div_int4: divide by 0 error");
548 result = rint(c / i);
550 PG_RETURN_CASH(result);
555 * Multiply cash by int2.
558 cash_mul_int2(PG_FUNCTION_ARGS)
560 Cash c = PG_GETARG_CASH(0);
561 int16 s = PG_GETARG_INT16(1);
565 PG_RETURN_CASH(result);
569 * Multiply int2 by cash.
572 int2_mul_cash(PG_FUNCTION_ARGS)
574 int16 s = PG_GETARG_INT16(0);
575 Cash c = PG_GETARG_CASH(1);
579 PG_RETURN_CASH(result);
583 * Divide cash by int2.
585 * XXX Don't know if rounding or truncating is correct behavior.
586 * Round for now. - tgl 97/04/15
589 cash_div_int2(PG_FUNCTION_ARGS)
591 Cash c = PG_GETARG_CASH(0);
592 int16 s = PG_GETARG_INT16(1);
596 elog(ERROR, "cash_div: divide by 0 error");
598 result = rint(c / s);
599 PG_RETURN_CASH(result);
603 * Return larger of two cash values.
606 cashlarger(PG_FUNCTION_ARGS)
608 Cash c1 = PG_GETARG_CASH(0);
609 Cash c2 = PG_GETARG_CASH(1);
612 result = (c1 > c2) ? c1 : c2;
614 PG_RETURN_CASH(result);
618 * Return smaller of two cash values.
621 cashsmaller(PG_FUNCTION_ARGS)
623 Cash c1 = PG_GETARG_CASH(0);
624 Cash c2 = PG_GETARG_CASH(1);
627 result = (c1 < c2) ? c1 : c2;
629 PG_RETURN_CASH(result);
634 * This converts a int4 as well but to a representation using words
635 * Obviously way North American centric - sorry
638 cash_words(PG_FUNCTION_ARGS)
640 Cash value = PG_GETARG_CASH(0);
650 /* work with positive numbers */
654 strcpy(buf, "minus ");
660 /* Now treat as unsigned, to avoid trouble at INT_MIN */
661 val = (unsigned int) value;
663 m0 = val % 100; /* cents */
664 m1 = (val / 100) % 1000; /* hundreds */
665 m2 = (val / 100000) % 1000; /* thousands */
666 m3 = val / 100000000 % 1000; /* millions */
670 strcat(buf, num_word(m3));
671 strcat(buf, " million ");
676 strcat(buf, num_word(m2));
677 strcat(buf, " thousand ");
681 strcat(buf, num_word(m1));
686 strcat(buf, (val / 100) == 1 ? " dollar and " : " dollars and ");
687 strcat(buf, num_word(m0));
688 strcat(buf, m0 == 1 ? " cent" : " cents");
690 /* capitalize output */
691 buf[0] = toupper((unsigned char) buf[0]);
693 /* make a text type for output */
694 result = (text *) palloc(strlen(buf) + VARHDRSZ);
695 VARATT_SIZEP(result) = strlen(buf) + VARHDRSZ;
696 memcpy(VARDATA(result), buf, strlen(buf));
698 PG_RETURN_TEXT_P(result);
702 /*************************************************************************
704 ************************************************************************/
709 static char buf[128];
710 static const char *small[] = {
711 "zero", "one", "two", "three", "four", "five", "six", "seven",
712 "eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen",
713 "fifteen", "sixteen", "seventeen", "eighteen", "nineteen", "twenty",
714 "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety"
716 const char **big = small + 18;
717 int tu = value % 100;
719 /* deal with the simple cases first */
723 /* is it an even multiple of 100? */
726 sprintf(buf, "%s hundred", small[value / 100]);
733 /* is it an even multiple of 10 other than 10? */
734 if (value % 10 == 0 && tu > 10)
735 sprintf(buf, "%s hundred %s",
736 small[value / 100], big[tu / 10]);
738 sprintf(buf, "%s hundred and %s",
739 small[value / 100], small[tu]);
741 sprintf(buf, "%s hundred %s %s",
742 small[value / 100], big[tu / 10], small[tu % 10]);
747 /* is it an even multiple of 10 other than 10? */
748 if (value % 10 == 0 && tu > 10)
749 sprintf(buf, "%s", big[tu / 10]);
751 sprintf(buf, "%s", small[tu]);
753 sprintf(buf, "%s %s", big[tu / 10], small[tu % 10]);