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.48 2000/11/25 22:43:08 tgl Exp $
24 #include "miscadmin.h"
25 #include "utils/builtins.h"
26 #include "utils/cash.h"
27 #include "utils/pg_locale.h"
30 static const char *num_word(Cash value);
32 /* when we go to 64 bit values we will have to modify this */
35 #define TERMINATOR (CASH_BUFSZ - 1)
36 #define LAST_PAREN (TERMINATOR - 1)
37 #define LAST_DIGIT (LAST_PAREN - 1)
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(PG_FUNCTION_ARGS)
72 char *str = PG_GETARG_CSTRING(0);
86 struct lconv *lconvert = PGLC_localeconv();
91 * frac_digits will be CHAR_MAX in some locales, notably C. However,
92 * just testing for == CHAR_MAX is risky, because of compilers like
93 * gcc that "helpfully" let you alter the platform-standard definition
94 * of whether char is signed or not. If we are so unfortunate as to
95 * get compiled with a nonstandard -fsigned-char or -funsigned-char
96 * switch, then our idea of CHAR_MAX will not agree with libc's.
97 * The safest course is not to test for CHAR_MAX at all, but to impose
98 * a range check for plausible frac_digits values.
100 fpoint = lconvert->frac_digits;
101 if (fpoint < 0 || fpoint > 10)
102 fpoint = 2; /* best guess in this case, I think */
104 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
105 ssymbol = ((*lconvert->mon_thousands_sep != '\0') ? *lconvert->mon_thousands_sep : ',');
106 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
107 psymbol = ((*lconvert->positive_sign != '\0') ? *lconvert->positive_sign : '+');
108 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
119 printf("cashin- precision '%d'; decimal '%c'; thousands '%c'; currency '%s'; positive '%c'; negative '%s'\n",
120 fpoint, dsymbol, ssymbol, csymbol, psymbol, nsymbol);
123 /* we need to add all sorts of checking here. For now just */
124 /* strip all leading whitespace and any leading currency symbol */
125 while (isspace((int) *s))
127 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
128 s += strlen(csymbol);
131 printf("cashin- string is '%s'\n", s);
134 /* a leading minus or paren signifies a negative number */
135 /* again, better heuristics needed */
136 if (strncmp(s, nsymbol, strlen(nsymbol)) == 0)
139 s += strlen(nsymbol);
141 printf("cashin- negative symbol; string is '%s'\n", s);
150 else if (*s == psymbol)
154 printf("cashin- string is '%s'\n", s);
157 while (isspace((int) *s))
159 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
160 s += strlen(csymbol);
163 printf("cashin- string is '%s'\n", s);
168 /* we look for digits as int4 as we have less */
169 /* than the required number of decimal places */
170 if (isdigit((int) *s) && dec < fpoint)
172 value = (value * 10) + *s - '0';
177 /* decimal point? then start counting fractions... */
179 else if (*s == dsymbol && !seen_dot)
183 /* "thousands" separator? then skip... */
185 else if (*s == ssymbol)
192 if (isdigit((int) *s) && *s >= '5')
195 /* adjust for less than required decimal places */
196 for (; dec < fpoint; dec++)
203 while (isspace((int) *s) || *s == '0' || *s == ')')
207 elog(ERROR, "Bad money external representation %s", str);
209 result = (value * sgn);
212 printf("cashin- result is %d\n", result);
215 PG_RETURN_CASH(result);
220 * Function to convert cash to a dollars and cents representation.
221 * XXX HACK This code appears to assume US conventions for
222 * positive-valued amounts. - tgl 97/04/14
225 cash_out(PG_FUNCTION_ARGS)
227 Cash value = PG_GETARG_CASH(0);
229 char buf[CASH_BUFSZ];
231 int count = LAST_DIGIT;
233 int comma_position = 0;
242 struct lconv *lconvert = PGLC_localeconv();
246 /* see comments about frac_digits in cash_in() */
247 points = lconvert->frac_digits;
248 if (points < 0 || points > 10)
249 points = 2; /* best guess in this case, I think */
252 * As with frac_digits, must apply a range check to mon_grouping
253 * to avoid being fooled by variant CHAR_MAX values.
255 mon_group = *lconvert->mon_grouping;
256 if (mon_group <= 0 || mon_group > 6)
259 comma = ((*lconvert->mon_thousands_sep != '\0') ? *lconvert->mon_thousands_sep : ',');
260 convention = lconvert->n_sign_posn;
261 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
262 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
263 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
274 point_pos = LAST_DIGIT - points;
276 /* allow more than three decimal points and separate them */
279 point_pos -= (points - 1) / mon_group;
280 comma_position = point_pos % (mon_group + 1);
283 /* we work with positive amounts and add the minus sign at the end */
290 /* allow for trailing negative strings */
291 MemSet(buf, ' ', CASH_BUFSZ);
292 buf[TERMINATOR] = buf[LAST_PAREN] = '\0';
294 while (value || count > (point_pos - 2))
296 if (points && count == point_pos)
297 buf[count--] = dsymbol;
298 else if (comma && count % (mon_group + 1) == comma_position)
299 buf[count--] = comma;
301 buf[count--] = (value % 10) + '0';
305 strncpy((buf + count - strlen(csymbol) + 1), csymbol, strlen(csymbol));
306 count -= strlen(csymbol) - 1;
308 if (buf[LAST_DIGIT] == ',')
309 buf[LAST_DIGIT] = buf[LAST_PAREN];
311 /* see if we need to signify negative amount */
314 if (!PointerIsValid(result = palloc(CASH_BUFSZ + 2 - count + strlen(nsymbol))))
315 elog(ERROR, "Memory allocation failed, can't output cash");
317 /* Position code of 0 means use parens */
319 sprintf(result, "(%s)", buf + count);
320 else if (convention == 2)
321 sprintf(result, "%s%s", buf + count, nsymbol);
323 sprintf(result, "%s%s", nsymbol, buf + count);
327 if (!PointerIsValid(result = palloc(CASH_BUFSZ + 2 - count)))
328 elog(ERROR, "Memory allocation failed, can't output cash");
330 strcpy(result, buf + count);
333 PG_RETURN_CSTRING(result);
338 cash_eq(PG_FUNCTION_ARGS)
340 Cash c1 = PG_GETARG_CASH(0);
341 Cash c2 = PG_GETARG_CASH(1);
343 PG_RETURN_BOOL(c1 == c2);
347 cash_ne(PG_FUNCTION_ARGS)
349 Cash c1 = PG_GETARG_CASH(0);
350 Cash c2 = PG_GETARG_CASH(1);
352 PG_RETURN_BOOL(c1 != c2);
356 cash_lt(PG_FUNCTION_ARGS)
358 Cash c1 = PG_GETARG_CASH(0);
359 Cash c2 = PG_GETARG_CASH(1);
361 PG_RETURN_BOOL(c1 < c2);
365 cash_le(PG_FUNCTION_ARGS)
367 Cash c1 = PG_GETARG_CASH(0);
368 Cash c2 = PG_GETARG_CASH(1);
370 PG_RETURN_BOOL(c1 <= c2);
374 cash_gt(PG_FUNCTION_ARGS)
376 Cash c1 = PG_GETARG_CASH(0);
377 Cash c2 = PG_GETARG_CASH(1);
379 PG_RETURN_BOOL(c1 > c2);
383 cash_ge(PG_FUNCTION_ARGS)
385 Cash c1 = PG_GETARG_CASH(0);
386 Cash c2 = PG_GETARG_CASH(1);
388 PG_RETURN_BOOL(c1 >= c2);
393 * Add two cash values.
396 cash_pl(PG_FUNCTION_ARGS)
398 Cash c1 = PG_GETARG_CASH(0);
399 Cash c2 = PG_GETARG_CASH(1);
404 PG_RETURN_CASH(result);
409 * Subtract two cash values.
412 cash_mi(PG_FUNCTION_ARGS)
414 Cash c1 = PG_GETARG_CASH(0);
415 Cash c2 = PG_GETARG_CASH(1);
420 PG_RETURN_CASH(result);
425 * Multiply cash by float8.
428 cash_mul_flt8(PG_FUNCTION_ARGS)
430 Cash c = PG_GETARG_CASH(0);
431 float8 f = PG_GETARG_FLOAT8(1);
435 PG_RETURN_CASH(result);
440 * Multiply float8 by cash.
443 flt8_mul_cash(PG_FUNCTION_ARGS)
445 float8 f = PG_GETARG_FLOAT8(0);
446 Cash c = PG_GETARG_CASH(1);
450 PG_RETURN_CASH(result);
455 * Divide cash by float8.
457 * XXX Don't know if rounding or truncating is correct behavior.
458 * Round for now. - tgl 97/04/15
461 cash_div_flt8(PG_FUNCTION_ARGS)
463 Cash c = PG_GETARG_CASH(0);
464 float8 f = PG_GETARG_FLOAT8(1);
468 elog(ERROR, "cash_div: divide by 0.0 error");
470 result = rint(c / f);
471 PG_RETURN_CASH(result);
475 * Multiply cash by float4.
478 cash_mul_flt4(PG_FUNCTION_ARGS)
480 Cash c = PG_GETARG_CASH(0);
481 float4 f = PG_GETARG_FLOAT4(1);
485 PG_RETURN_CASH(result);
490 * Multiply float4 by cash.
493 flt4_mul_cash(PG_FUNCTION_ARGS)
495 float4 f = PG_GETARG_FLOAT4(0);
496 Cash c = PG_GETARG_CASH(1);
500 PG_RETURN_CASH(result);
505 * Divide cash by float4.
507 * XXX Don't know if rounding or truncating is correct behavior.
508 * Round for now. - tgl 97/04/15
511 cash_div_flt4(PG_FUNCTION_ARGS)
513 Cash c = PG_GETARG_CASH(0);
514 float4 f = PG_GETARG_FLOAT4(1);
518 elog(ERROR, "cash_div: divide by 0.0 error");
520 result = rint(c / f);
521 PG_RETURN_CASH(result);
526 * Multiply cash by int4.
529 cash_mul_int4(PG_FUNCTION_ARGS)
531 Cash c = PG_GETARG_CASH(0);
532 int32 i = PG_GETARG_INT32(1);
536 PG_RETURN_CASH(result);
541 * Multiply int4 by cash.
544 int4_mul_cash(PG_FUNCTION_ARGS)
546 int32 i = PG_GETARG_INT32(0);
547 Cash c = PG_GETARG_CASH(1);
551 PG_RETURN_CASH(result);
556 * Divide cash by 4-byte integer.
558 * XXX Don't know if rounding or truncating is correct behavior.
559 * Round for now. - tgl 97/04/15
562 cash_div_int4(PG_FUNCTION_ARGS)
564 Cash c = PG_GETARG_CASH(0);
565 int32 i = PG_GETARG_INT32(1);
569 elog(ERROR, "cash_div_int4: divide by 0 error");
571 result = rint(c / i);
573 PG_RETURN_CASH(result);
578 * Multiply cash by int2.
581 cash_mul_int2(PG_FUNCTION_ARGS)
583 Cash c = PG_GETARG_CASH(0);
584 int16 s = PG_GETARG_INT16(1);
588 PG_RETURN_CASH(result);
592 * Multiply int2 by cash.
595 int2_mul_cash(PG_FUNCTION_ARGS)
597 int16 s = PG_GETARG_INT16(0);
598 Cash c = PG_GETARG_CASH(1);
602 PG_RETURN_CASH(result);
606 * Divide cash by int2.
608 * XXX Don't know if rounding or truncating is correct behavior.
609 * Round for now. - tgl 97/04/15
612 cash_div_int2(PG_FUNCTION_ARGS)
614 Cash c = PG_GETARG_CASH(0);
615 int16 s = PG_GETARG_INT16(1);
619 elog(ERROR, "cash_div: divide by 0 error");
621 result = rint(c / s);
622 PG_RETURN_CASH(result);
626 * Return larger of two cash values.
629 cashlarger(PG_FUNCTION_ARGS)
631 Cash c1 = PG_GETARG_CASH(0);
632 Cash c2 = PG_GETARG_CASH(1);
635 result = (c1 > c2) ? c1 : c2;
637 PG_RETURN_CASH(result);
641 * Return smaller of two cash values.
644 cashsmaller(PG_FUNCTION_ARGS)
646 Cash c1 = PG_GETARG_CASH(0);
647 Cash c2 = PG_GETARG_CASH(1);
650 result = (c1 < c2) ? c1 : c2;
652 PG_RETURN_CASH(result);
657 * This converts a int4 as well but to a representation using words
658 * Obviously way North American centric - sorry
661 cash_words(PG_FUNCTION_ARGS)
663 Cash value = PG_GETARG_CASH(0);
672 /* work with positive numbers */
676 strcpy(buf, "minus ");
682 m0 = value % 100; /* cents */
683 m1 = (value / 100) % 1000; /* hundreds */
684 m2 = (value / 100000) % 1000; /* thousands */
685 m3 = value / 100000000 % 1000; /* millions */
689 strcat(buf, num_word(m3));
690 strcat(buf, " million ");
695 strcat(buf, num_word(m2));
696 strcat(buf, " thousand ");
700 strcat(buf, num_word(m1));
705 strcat(buf, (int) (value / 100) == 1 ? " dollar and " : " dollars and ");
706 strcat(buf, num_word(m0));
707 strcat(buf, m0 == 1 ? " cent" : " cents");
709 /* capitalize output */
710 buf[0] = toupper(buf[0]);
712 /* make a text type for output */
713 result = (text *) palloc(strlen(buf) + VARHDRSZ);
714 VARATT_SIZEP(result) = strlen(buf) + VARHDRSZ;
715 memcpy(VARDATA(result), buf, strlen(buf));
717 PG_RETURN_TEXT_P(result);
721 /*************************************************************************
723 ************************************************************************/
728 static char buf[128];
729 static const char *small[] = {
730 "zero", "one", "two", "three", "four", "five", "six", "seven",
731 "eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen",
732 "fifteen", "sixteen", "seventeen", "eighteen", "nineteen", "twenty",
733 "thirty", "fourty", "fifty", "sixty", "seventy", "eighty", "ninety"
735 const char **big = small + 18;
736 int tu = value % 100;
738 /* deal with the simple cases first */
742 /* is it an even multiple of 100? */
745 sprintf(buf, "%s hundred", small[value / 100]);
752 /* is it an even multiple of 10 other than 10? */
753 if (value % 10 == 0 && tu > 10)
754 sprintf(buf, "%s hundred %s",
755 small[value / 100], big[tu / 10]);
757 sprintf(buf, "%s hundred and %s",
758 small[value / 100], small[tu]);
760 sprintf(buf, "%s hundred %s %s",
761 small[value / 100], big[tu / 10], small[tu % 10]);
766 /* is it an even multiple of 10 other than 10? */
767 if (value % 10 == 0 && tu > 10)
768 sprintf(buf, "%s", big[tu / 10]);
770 sprintf(buf, "%s", small[tu]);
772 sprintf(buf, "%s %s", big[tu / 10], small[tu % 10]);