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.58 2003/05/13 18:03:07 tgl Exp $
22 #include "libpq/pqformat.h"
23 #include "miscadmin.h"
24 #include "utils/builtins.h"
25 #include "utils/cash.h"
26 #include "utils/pg_locale.h"
29 static const char *num_word(Cash value);
31 /* when we go to 64 bit values we will have to modify this */
34 #define TERMINATOR (CASH_BUFSZ - 1)
35 #define LAST_PAREN (TERMINATOR - 1)
36 #define LAST_DIGIT (LAST_PAREN - 1)
40 * Cash is a pass-by-ref SQL type, so we must pass and return pointers.
41 * These macros and support routine hide the pass-by-refness.
43 #define PG_GETARG_CASH(n) (* ((Cash *) PG_GETARG_POINTER(n)))
44 #define PG_RETURN_CASH(x) return CashGetDatum(x)
47 CashGetDatum(Cash value)
49 Cash *result = (Cash *) palloc(sizeof(Cash));
52 return PointerGetDatum(result);
57 * Convert a string to a cash data type.
58 * Format is [$]###[,]###[.##]
59 * Examples: 123.45 $123.45 $123,456.78
61 * This is currently implemented as a 32-bit integer.
62 * XXX HACK It looks as though some of the symbols for
63 * monetary values returned by localeconv() can be multiple
64 * bytes/characters. This code assumes one byte only. - tgl 97/04/14
65 * XXX UNHACK Allow the currency symbol to be multibyte.
69 cash_in(PG_FUNCTION_ARGS)
71 char *str = PG_GETARG_CSTRING(0);
85 struct lconv *lconvert = PGLC_localeconv();
88 * frac_digits will be CHAR_MAX in some locales, notably C. However,
89 * just testing for == CHAR_MAX is risky, because of compilers like
90 * gcc that "helpfully" let you alter the platform-standard definition
91 * of whether char is signed or not. If we are so unfortunate as to
92 * get compiled with a nonstandard -fsigned-char or -funsigned-char
93 * switch, then our idea of CHAR_MAX will not agree with libc's. The
94 * safest course is not to test for CHAR_MAX at all, but to impose a
95 * range check for plausible frac_digits values.
97 fpoint = lconvert->frac_digits;
98 if (fpoint < 0 || fpoint > 10)
99 fpoint = 2; /* best guess in this case, I think */
101 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
102 ssymbol = ((*lconvert->mon_thousands_sep != '\0') ? *lconvert->mon_thousands_sep : ',');
103 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
104 psymbol = ((*lconvert->positive_sign != '\0') ? *lconvert->positive_sign : '+');
105 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
108 printf("cashin- precision '%d'; decimal '%c'; thousands '%c'; currency '%s'; positive '%c'; negative '%s'\n",
109 fpoint, dsymbol, ssymbol, csymbol, psymbol, nsymbol);
112 /* we need to add all sorts of checking here. For now just */
113 /* strip all leading whitespace and any leading currency symbol */
114 while (isspace((unsigned char) *s))
116 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
117 s += strlen(csymbol);
120 printf("cashin- string is '%s'\n", s);
123 /* a leading minus or paren signifies a negative number */
124 /* again, better heuristics needed */
125 if (strncmp(s, nsymbol, strlen(nsymbol)) == 0)
128 s += strlen(nsymbol);
130 printf("cashin- negative symbol; string is '%s'\n", s);
139 else if (*s == psymbol)
143 printf("cashin- string is '%s'\n", s);
146 while (isspace((unsigned char) *s))
148 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
149 s += strlen(csymbol);
152 printf("cashin- string is '%s'\n", s);
157 /* we look for digits as int4 as we have less */
158 /* than the required number of decimal places */
159 if (isdigit((unsigned char) *s) && dec < fpoint)
161 value = (value * 10) + *s - '0';
166 /* decimal point? then start counting fractions... */
168 else if (*s == dsymbol && !seen_dot)
172 /* "thousands" separator? then skip... */
174 else if (*s == ssymbol)
181 if (isdigit((unsigned char) *s) && *s >= '5')
184 /* adjust for less than required decimal places */
185 for (; dec < fpoint; dec++)
192 while (isspace((unsigned char) *s) || *s == '0' || *s == ')')
196 elog(ERROR, "Bad money external representation %s", str);
198 result = (value * sgn);
201 printf("cashin- result is %d\n", result);
204 PG_RETURN_CASH(result);
209 * Function to convert cash to a dollars and cents representation.
210 * XXX HACK This code appears to assume US conventions for
211 * positive-valued amounts. - tgl 97/04/14
214 cash_out(PG_FUNCTION_ARGS)
216 Cash value = PG_GETARG_CASH(0);
218 char buf[CASH_BUFSZ];
220 int count = LAST_DIGIT;
222 int comma_position = 0;
231 struct lconv *lconvert = PGLC_localeconv();
233 /* see comments about frac_digits in cash_in() */
234 points = lconvert->frac_digits;
235 if (points < 0 || points > 10)
236 points = 2; /* best guess in this case, I think */
239 * As with frac_digits, must apply a range check to mon_grouping to
240 * avoid being fooled by variant CHAR_MAX values.
242 mon_group = *lconvert->mon_grouping;
243 if (mon_group <= 0 || mon_group > 6)
246 comma = ((*lconvert->mon_thousands_sep != '\0') ? *lconvert->mon_thousands_sep : ',');
247 convention = lconvert->n_sign_posn;
248 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
249 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
250 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
252 point_pos = LAST_DIGIT - points;
254 /* allow more than three decimal points and separate them */
257 point_pos -= (points - 1) / mon_group;
258 comma_position = point_pos % (mon_group + 1);
261 /* we work with positive amounts and add the minus sign at the end */
268 /* allow for trailing negative strings */
269 MemSet(buf, ' ', CASH_BUFSZ);
270 buf[TERMINATOR] = buf[LAST_PAREN] = '\0';
272 while (value || count > (point_pos - 2))
274 if (points && count == point_pos)
275 buf[count--] = dsymbol;
276 else if (comma && count % (mon_group + 1) == comma_position)
277 buf[count--] = comma;
279 buf[count--] = ((unsigned int) value % 10) + '0';
280 value = ((unsigned int) value) / 10;
283 strncpy((buf + count - strlen(csymbol) + 1), csymbol, strlen(csymbol));
284 count -= strlen(csymbol) - 1;
286 if (buf[LAST_DIGIT] == ',')
287 buf[LAST_DIGIT] = buf[LAST_PAREN];
289 /* see if we need to signify negative amount */
292 if (!PointerIsValid(result = palloc(CASH_BUFSZ + 2 - count + strlen(nsymbol))))
293 elog(ERROR, "Memory allocation failed, can't output cash");
295 /* Position code of 0 means use parens */
297 sprintf(result, "(%s)", buf + count);
298 else if (convention == 2)
299 sprintf(result, "%s%s", buf + count, nsymbol);
301 sprintf(result, "%s%s", nsymbol, buf + count);
305 if (!PointerIsValid(result = palloc(CASH_BUFSZ + 2 - count)))
306 elog(ERROR, "Memory allocation failed, can't output cash");
308 strcpy(result, buf + count);
311 PG_RETURN_CSTRING(result);
315 * cash_recv - converts external binary format to cash
318 cash_recv(PG_FUNCTION_ARGS)
320 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
322 PG_RETURN_CASH((Cash) pq_getmsgint(buf, sizeof(Cash)));
326 * cash_send - converts cash to binary format
329 cash_send(PG_FUNCTION_ARGS)
331 Cash arg1 = PG_GETARG_CASH(0);
334 pq_begintypsend(&buf);
335 pq_sendint(&buf, arg1, sizeof(Cash));
336 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
341 cash_eq(PG_FUNCTION_ARGS)
343 Cash c1 = PG_GETARG_CASH(0);
344 Cash c2 = PG_GETARG_CASH(1);
346 PG_RETURN_BOOL(c1 == c2);
350 cash_ne(PG_FUNCTION_ARGS)
352 Cash c1 = PG_GETARG_CASH(0);
353 Cash c2 = PG_GETARG_CASH(1);
355 PG_RETURN_BOOL(c1 != c2);
359 cash_lt(PG_FUNCTION_ARGS)
361 Cash c1 = PG_GETARG_CASH(0);
362 Cash c2 = PG_GETARG_CASH(1);
364 PG_RETURN_BOOL(c1 < c2);
368 cash_le(PG_FUNCTION_ARGS)
370 Cash c1 = PG_GETARG_CASH(0);
371 Cash c2 = PG_GETARG_CASH(1);
373 PG_RETURN_BOOL(c1 <= c2);
377 cash_gt(PG_FUNCTION_ARGS)
379 Cash c1 = PG_GETARG_CASH(0);
380 Cash c2 = PG_GETARG_CASH(1);
382 PG_RETURN_BOOL(c1 > c2);
386 cash_ge(PG_FUNCTION_ARGS)
388 Cash c1 = PG_GETARG_CASH(0);
389 Cash c2 = PG_GETARG_CASH(1);
391 PG_RETURN_BOOL(c1 >= c2);
396 * Add two cash values.
399 cash_pl(PG_FUNCTION_ARGS)
401 Cash c1 = PG_GETARG_CASH(0);
402 Cash c2 = PG_GETARG_CASH(1);
407 PG_RETURN_CASH(result);
412 * Subtract two cash values.
415 cash_mi(PG_FUNCTION_ARGS)
417 Cash c1 = PG_GETARG_CASH(0);
418 Cash c2 = PG_GETARG_CASH(1);
423 PG_RETURN_CASH(result);
428 * Multiply cash by float8.
431 cash_mul_flt8(PG_FUNCTION_ARGS)
433 Cash c = PG_GETARG_CASH(0);
434 float8 f = PG_GETARG_FLOAT8(1);
438 PG_RETURN_CASH(result);
443 * Multiply float8 by cash.
446 flt8_mul_cash(PG_FUNCTION_ARGS)
448 float8 f = PG_GETARG_FLOAT8(0);
449 Cash c = PG_GETARG_CASH(1);
453 PG_RETURN_CASH(result);
458 * Divide cash by float8.
460 * XXX Don't know if rounding or truncating is correct behavior.
461 * Round for now. - tgl 97/04/15
464 cash_div_flt8(PG_FUNCTION_ARGS)
466 Cash c = PG_GETARG_CASH(0);
467 float8 f = PG_GETARG_FLOAT8(1);
471 elog(ERROR, "division by zero");
473 result = rint(c / f);
474 PG_RETURN_CASH(result);
478 * Multiply cash by float4.
481 cash_mul_flt4(PG_FUNCTION_ARGS)
483 Cash c = PG_GETARG_CASH(0);
484 float4 f = PG_GETARG_FLOAT4(1);
488 PG_RETURN_CASH(result);
493 * Multiply float4 by cash.
496 flt4_mul_cash(PG_FUNCTION_ARGS)
498 float4 f = PG_GETARG_FLOAT4(0);
499 Cash c = PG_GETARG_CASH(1);
503 PG_RETURN_CASH(result);
508 * Divide cash by float4.
510 * XXX Don't know if rounding or truncating is correct behavior.
511 * Round for now. - tgl 97/04/15
514 cash_div_flt4(PG_FUNCTION_ARGS)
516 Cash c = PG_GETARG_CASH(0);
517 float4 f = PG_GETARG_FLOAT4(1);
521 elog(ERROR, "division by zero");
523 result = rint(c / f);
524 PG_RETURN_CASH(result);
529 * Multiply cash by int4.
532 cash_mul_int4(PG_FUNCTION_ARGS)
534 Cash c = PG_GETARG_CASH(0);
535 int32 i = PG_GETARG_INT32(1);
539 PG_RETURN_CASH(result);
544 * Multiply int4 by cash.
547 int4_mul_cash(PG_FUNCTION_ARGS)
549 int32 i = PG_GETARG_INT32(0);
550 Cash c = PG_GETARG_CASH(1);
554 PG_RETURN_CASH(result);
559 * Divide cash by 4-byte integer.
561 * XXX Don't know if rounding or truncating is correct behavior.
562 * Round for now. - tgl 97/04/15
565 cash_div_int4(PG_FUNCTION_ARGS)
567 Cash c = PG_GETARG_CASH(0);
568 int32 i = PG_GETARG_INT32(1);
572 elog(ERROR, "division by zero");
574 result = rint(c / i);
576 PG_RETURN_CASH(result);
581 * Multiply cash by int2.
584 cash_mul_int2(PG_FUNCTION_ARGS)
586 Cash c = PG_GETARG_CASH(0);
587 int16 s = PG_GETARG_INT16(1);
591 PG_RETURN_CASH(result);
595 * Multiply int2 by cash.
598 int2_mul_cash(PG_FUNCTION_ARGS)
600 int16 s = PG_GETARG_INT16(0);
601 Cash c = PG_GETARG_CASH(1);
605 PG_RETURN_CASH(result);
609 * Divide cash by int2.
611 * XXX Don't know if rounding or truncating is correct behavior.
612 * Round for now. - tgl 97/04/15
615 cash_div_int2(PG_FUNCTION_ARGS)
617 Cash c = PG_GETARG_CASH(0);
618 int16 s = PG_GETARG_INT16(1);
622 elog(ERROR, "division by zero");
624 result = rint(c / s);
625 PG_RETURN_CASH(result);
629 * Return larger of two cash values.
632 cashlarger(PG_FUNCTION_ARGS)
634 Cash c1 = PG_GETARG_CASH(0);
635 Cash c2 = PG_GETARG_CASH(1);
638 result = (c1 > c2) ? c1 : c2;
640 PG_RETURN_CASH(result);
644 * Return smaller of two cash values.
647 cashsmaller(PG_FUNCTION_ARGS)
649 Cash c1 = PG_GETARG_CASH(0);
650 Cash c2 = PG_GETARG_CASH(1);
653 result = (c1 < c2) ? c1 : c2;
655 PG_RETURN_CASH(result);
660 * This converts a int4 as well but to a representation using words
661 * Obviously way North American centric - sorry
664 cash_words(PG_FUNCTION_ARGS)
666 Cash value = PG_GETARG_CASH(0);
676 /* work with positive numbers */
680 strcpy(buf, "minus ");
686 /* Now treat as unsigned, to avoid trouble at INT_MIN */
687 val = (unsigned int) value;
689 m0 = val % 100; /* cents */
690 m1 = (val / 100) % 1000; /* hundreds */
691 m2 = (val / 100000) % 1000; /* thousands */
692 m3 = val / 100000000 % 1000; /* millions */
696 strcat(buf, num_word(m3));
697 strcat(buf, " million ");
702 strcat(buf, num_word(m2));
703 strcat(buf, " thousand ");
707 strcat(buf, num_word(m1));
712 strcat(buf, (val / 100) == 1 ? " dollar and " : " dollars and ");
713 strcat(buf, num_word(m0));
714 strcat(buf, m0 == 1 ? " cent" : " cents");
716 /* capitalize output */
717 buf[0] = toupper((unsigned char) buf[0]);
719 /* make a text type for output */
720 result = (text *) palloc(strlen(buf) + VARHDRSZ);
721 VARATT_SIZEP(result) = strlen(buf) + VARHDRSZ;
722 memcpy(VARDATA(result), buf, strlen(buf));
724 PG_RETURN_TEXT_P(result);
728 /*************************************************************************
730 ************************************************************************/
735 static char buf[128];
736 static const char *small[] = {
737 "zero", "one", "two", "three", "four", "five", "six", "seven",
738 "eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen",
739 "fifteen", "sixteen", "seventeen", "eighteen", "nineteen", "twenty",
740 "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety"
742 const char **big = small + 18;
743 int tu = value % 100;
745 /* deal with the simple cases first */
749 /* is it an even multiple of 100? */
752 sprintf(buf, "%s hundred", small[value / 100]);
759 /* is it an even multiple of 10 other than 10? */
760 if (value % 10 == 0 && tu > 10)
761 sprintf(buf, "%s hundred %s",
762 small[value / 100], big[tu / 10]);
764 sprintf(buf, "%s hundred and %s",
765 small[value / 100], small[tu]);
767 sprintf(buf, "%s hundred %s %s",
768 small[value / 100], big[tu / 10], small[tu % 10]);
773 /* is it an even multiple of 10 other than 10? */
774 if (value % 10 == 0 && tu > 10)
775 sprintf(buf, "%s", big[tu / 10]);
777 sprintf(buf, "%s", small[tu]);
779 sprintf(buf, "%s %s", big[tu / 10], small[tu % 10]);