3 * Written by D'Arcy J.M. Cain
5 * http://www.druid.net/darcy/
7 * Functions to allow input and output of money normally but store
8 * and handle it as 64 bit ints
10 * A slightly modified version of this file and a discussion of the
11 * workings can be found in the book "Software Solutions in C" by
12 * Dale Schumacher, Academic Press, ISBN: 0-12-632360-7 except that
13 * this version handles 64 bit numbers and so can hold values up to
14 * $92,233,720,368,547,758.07.
16 * $PostgreSQL: pgsql/src/backend/utils/adt/cash.c,v 1.82 2009/06/11 14:49:03 momjian Exp $
26 #include "libpq/pqformat.h"
27 #include "utils/builtins.h"
28 #include "utils/cash.h"
29 #include "utils/pg_locale.h"
33 #define TERMINATOR (CASH_BUFSZ - 1)
34 #define LAST_PAREN (TERMINATOR - 1)
35 #define LAST_DIGIT (LAST_PAREN - 1)
38 /*************************************************************************
40 ************************************************************************/
46 static const char *small[] = {
47 "zero", "one", "two", "three", "four", "five", "six", "seven",
48 "eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen",
49 "fifteen", "sixteen", "seventeen", "eighteen", "nineteen", "twenty",
50 "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety"
52 const char **big = small + 18;
55 /* deal with the simple cases first */
59 /* is it an even multiple of 100? */
62 sprintf(buf, "%s hundred", small[value / 100]);
69 /* is it an even multiple of 10 other than 10? */
70 if (value % 10 == 0 && tu > 10)
71 sprintf(buf, "%s hundred %s",
72 small[value / 100], big[tu / 10]);
74 sprintf(buf, "%s hundred and %s",
75 small[value / 100], small[tu]);
77 sprintf(buf, "%s hundred %s %s",
78 small[value / 100], big[tu / 10], small[tu % 10]);
82 /* is it an even multiple of 10 other than 10? */
83 if (value % 10 == 0 && tu > 10)
84 sprintf(buf, "%s", big[tu / 10]);
86 sprintf(buf, "%s", small[tu]);
88 sprintf(buf, "%s %s", big[tu / 10], small[tu % 10]);
96 * Convert a string to a cash data type.
97 * Format is [$]###[,]###[.##]
98 * Examples: 123.45 $123.45 $123,456.78
102 cash_in(PG_FUNCTION_ARGS)
104 char *str = PG_GETARG_CSTRING(0);
118 struct lconv *lconvert = PGLC_localeconv();
121 * frac_digits will be CHAR_MAX in some locales, notably C. However, just
122 * testing for == CHAR_MAX is risky, because of compilers like gcc that
123 * "helpfully" let you alter the platform-standard definition of whether
124 * char is signed or not. If we are so unfortunate as to get compiled
125 * with a nonstandard -fsigned-char or -funsigned-char switch, then our
126 * idea of CHAR_MAX will not agree with libc's. The safest course is not
127 * to test for CHAR_MAX at all, but to impose a range check for plausible
128 * frac_digits values.
130 fpoint = lconvert->frac_digits;
131 if (fpoint < 0 || fpoint > 10)
132 fpoint = 2; /* best guess in this case, I think */
134 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
135 if (*lconvert->mon_thousands_sep != '\0')
136 ssymbol = *lconvert->mon_thousands_sep;
138 /* ssymbol should not equal dsymbol */
139 ssymbol = (dsymbol != ',') ? ',' : '.';
140 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
141 psymbol = ((*lconvert->positive_sign != '\0') ? *lconvert->positive_sign : '+');
142 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
145 printf("cashin- precision '%d'; decimal '%c'; thousands '%c'; currency '%s'; positive '%c'; negative '%s'\n",
146 fpoint, dsymbol, ssymbol, csymbol, psymbol, nsymbol);
149 /* we need to add all sorts of checking here. For now just */
150 /* strip all leading whitespace and any leading currency symbol */
151 while (isspace((unsigned char) *s))
153 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
154 s += strlen(csymbol);
157 printf("cashin- string is '%s'\n", s);
160 /* a leading minus or paren signifies a negative number */
161 /* again, better heuristics needed */
162 /* XXX - doesn't properly check for balanced parens - djmc */
163 if (strncmp(s, nsymbol, strlen(nsymbol)) == 0)
166 s += strlen(nsymbol);
168 printf("cashin- negative symbol; string is '%s'\n", s);
176 else if (*s == psymbol)
180 printf("cashin- string is '%s'\n", s);
183 while (isspace((unsigned char) *s))
185 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
186 s += strlen(csymbol);
189 printf("cashin- string is '%s'\n", s);
194 /* we look for digits as long as we have found less */
195 /* than the required number of decimal places */
196 if (isdigit((unsigned char) *s) && (!seen_dot || dec < fpoint))
198 value = (value * 10) + (*s - '0');
203 /* decimal point? then start counting fractions... */
204 else if (*s == dsymbol && !seen_dot)
208 /* ignore if "thousands" separator, else we're done */
209 else if (*s != ssymbol)
212 if (isdigit((unsigned char) *s) && *s >= '5')
215 /* adjust for less than required decimal places */
216 for (; dec < fpoint; dec++)
223 /* should only be trailing digits followed by whitespace or right paren */
224 while (isdigit((unsigned char) *s))
226 while (isspace((unsigned char) *s) || *s == ')')
231 (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
232 errmsg("invalid input syntax for type money: \"%s\"", str)));
234 result = value * sgn;
237 printf("cashin- result is " INT64_FORMAT "\n", result);
240 PG_RETURN_CASH(result);
245 * Function to convert cash to a dollars and cents representation.
246 * XXX HACK This code appears to assume US conventions for
247 * positive-valued amounts. - tgl 97/04/14
250 cash_out(PG_FUNCTION_ARGS)
252 Cash value = PG_GETARG_CASH(0);
254 char buf[CASH_BUFSZ];
256 int count = LAST_DIGIT;
258 int ssymbol_position = 0;
267 struct lconv *lconvert = PGLC_localeconv();
269 /* see comments about frac_digits in cash_in() */
270 points = lconvert->frac_digits;
271 if (points < 0 || points > 10)
272 points = 2; /* best guess in this case, I think */
275 * As with frac_digits, must apply a range check to mon_grouping to avoid
276 * being fooled by variant CHAR_MAX values.
278 mon_group = *lconvert->mon_grouping;
279 if (mon_group <= 0 || mon_group > 6)
282 convention = lconvert->n_sign_posn;
283 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
284 if (*lconvert->mon_thousands_sep != '\0')
285 ssymbol = *lconvert->mon_thousands_sep;
287 /* ssymbol should not equal dsymbol */
288 ssymbol = (dsymbol != ',') ? ',' : '.';
289 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
290 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
292 point_pos = LAST_DIGIT - points;
294 point_pos -= (points - 1) / mon_group;
295 ssymbol_position = point_pos % (mon_group + 1);
297 /* we work with positive amounts and add the minus sign at the end */
304 /* allow for trailing negative strings */
305 MemSet(buf, ' ', CASH_BUFSZ);
306 buf[TERMINATOR] = buf[LAST_PAREN] = '\0';
308 while (value || count > (point_pos - 2))
310 if (points && count == point_pos)
311 buf[count--] = dsymbol;
312 else if (ssymbol && count % (mon_group + 1) == ssymbol_position)
313 buf[count--] = ssymbol;
315 buf[count--] = ((uint64) value % 10) + '0';
316 value = ((uint64) value) / 10;
319 strncpy((buf + count - strlen(csymbol) + 1), csymbol, strlen(csymbol));
320 count -= strlen(csymbol) - 1;
323 * If points == 0 and the number of digits % mon_group == 0, the code
324 * above adds a trailing ssymbol on the far right, so remove it.
326 if (buf[LAST_DIGIT] == ssymbol)
327 buf[LAST_DIGIT] = '\0';
329 /* see if we need to signify negative amount */
332 result = palloc(CASH_BUFSZ + 2 - count + strlen(nsymbol));
334 /* Position code of 0 means use parens */
336 sprintf(result, "(%s)", buf + count);
337 else if (convention == 2)
338 sprintf(result, "%s%s", buf + count, nsymbol);
340 sprintf(result, "%s%s", nsymbol, buf + count);
344 result = palloc(CASH_BUFSZ + 2 - count);
345 strcpy(result, buf + count);
348 PG_RETURN_CSTRING(result);
352 * cash_recv - converts external binary format to cash
355 cash_recv(PG_FUNCTION_ARGS)
357 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
359 PG_RETURN_CASH((Cash) pq_getmsgint64(buf));
363 * cash_send - converts cash to binary format
366 cash_send(PG_FUNCTION_ARGS)
368 Cash arg1 = PG_GETARG_CASH(0);
371 pq_begintypsend(&buf);
372 pq_sendint64(&buf, arg1);
373 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
377 * Comparison functions
381 cash_eq(PG_FUNCTION_ARGS)
383 Cash c1 = PG_GETARG_CASH(0);
384 Cash c2 = PG_GETARG_CASH(1);
386 PG_RETURN_BOOL(c1 == c2);
390 cash_ne(PG_FUNCTION_ARGS)
392 Cash c1 = PG_GETARG_CASH(0);
393 Cash c2 = PG_GETARG_CASH(1);
395 PG_RETURN_BOOL(c1 != c2);
399 cash_lt(PG_FUNCTION_ARGS)
401 Cash c1 = PG_GETARG_CASH(0);
402 Cash c2 = PG_GETARG_CASH(1);
404 PG_RETURN_BOOL(c1 < c2);
408 cash_le(PG_FUNCTION_ARGS)
410 Cash c1 = PG_GETARG_CASH(0);
411 Cash c2 = PG_GETARG_CASH(1);
413 PG_RETURN_BOOL(c1 <= c2);
417 cash_gt(PG_FUNCTION_ARGS)
419 Cash c1 = PG_GETARG_CASH(0);
420 Cash c2 = PG_GETARG_CASH(1);
422 PG_RETURN_BOOL(c1 > c2);
426 cash_ge(PG_FUNCTION_ARGS)
428 Cash c1 = PG_GETARG_CASH(0);
429 Cash c2 = PG_GETARG_CASH(1);
431 PG_RETURN_BOOL(c1 >= c2);
435 cash_cmp(PG_FUNCTION_ARGS)
437 Cash c1 = PG_GETARG_CASH(0);
438 Cash c2 = PG_GETARG_CASH(1);
450 * Add two cash values.
453 cash_pl(PG_FUNCTION_ARGS)
455 Cash c1 = PG_GETARG_CASH(0);
456 Cash c2 = PG_GETARG_CASH(1);
461 PG_RETURN_CASH(result);
466 * Subtract two cash values.
469 cash_mi(PG_FUNCTION_ARGS)
471 Cash c1 = PG_GETARG_CASH(0);
472 Cash c2 = PG_GETARG_CASH(1);
477 PG_RETURN_CASH(result);
482 * Multiply cash by float8.
485 cash_mul_flt8(PG_FUNCTION_ARGS)
487 Cash c = PG_GETARG_CASH(0);
488 float8 f = PG_GETARG_FLOAT8(1);
492 PG_RETURN_CASH(result);
497 * Multiply float8 by cash.
500 flt8_mul_cash(PG_FUNCTION_ARGS)
502 float8 f = PG_GETARG_FLOAT8(0);
503 Cash c = PG_GETARG_CASH(1);
507 PG_RETURN_CASH(result);
512 * Divide cash by float8.
515 cash_div_flt8(PG_FUNCTION_ARGS)
517 Cash c = PG_GETARG_CASH(0);
518 float8 f = PG_GETARG_FLOAT8(1);
523 (errcode(ERRCODE_DIVISION_BY_ZERO),
524 errmsg("division by zero")));
526 result = rint(c / f);
527 PG_RETURN_CASH(result);
532 * Multiply cash by float4.
535 cash_mul_flt4(PG_FUNCTION_ARGS)
537 Cash c = PG_GETARG_CASH(0);
538 float4 f = PG_GETARG_FLOAT4(1);
542 PG_RETURN_CASH(result);
547 * Multiply float4 by cash.
550 flt4_mul_cash(PG_FUNCTION_ARGS)
552 float4 f = PG_GETARG_FLOAT4(0);
553 Cash c = PG_GETARG_CASH(1);
557 PG_RETURN_CASH(result);
562 * Divide cash by float4.
566 cash_div_flt4(PG_FUNCTION_ARGS)
568 Cash c = PG_GETARG_CASH(0);
569 float4 f = PG_GETARG_FLOAT4(1);
574 (errcode(ERRCODE_DIVISION_BY_ZERO),
575 errmsg("division by zero")));
577 result = rint(c / f);
578 PG_RETURN_CASH(result);
583 * Multiply cash by int8.
586 cash_mul_int8(PG_FUNCTION_ARGS)
588 Cash c = PG_GETARG_CASH(0);
589 int64 i = PG_GETARG_INT64(1);
593 PG_RETURN_CASH(result);
598 * Multiply int8 by cash.
601 int8_mul_cash(PG_FUNCTION_ARGS)
603 int64 i = PG_GETARG_INT64(0);
604 Cash c = PG_GETARG_CASH(1);
608 PG_RETURN_CASH(result);
612 * Divide cash by 8-byte integer.
615 cash_div_int8(PG_FUNCTION_ARGS)
617 Cash c = PG_GETARG_CASH(0);
618 int64 i = PG_GETARG_INT64(1);
623 (errcode(ERRCODE_DIVISION_BY_ZERO),
624 errmsg("division by zero")));
626 result = rint(c / i);
628 PG_RETURN_CASH(result);
633 * Multiply cash by int4.
636 cash_mul_int4(PG_FUNCTION_ARGS)
638 Cash c = PG_GETARG_CASH(0);
639 int32 i = PG_GETARG_INT32(1);
643 PG_RETURN_CASH(result);
648 * Multiply int4 by cash.
651 int4_mul_cash(PG_FUNCTION_ARGS)
653 int32 i = PG_GETARG_INT32(0);
654 Cash c = PG_GETARG_CASH(1);
658 PG_RETURN_CASH(result);
663 * Divide cash by 4-byte integer.
667 cash_div_int4(PG_FUNCTION_ARGS)
669 Cash c = PG_GETARG_CASH(0);
670 int32 i = PG_GETARG_INT32(1);
675 (errcode(ERRCODE_DIVISION_BY_ZERO),
676 errmsg("division by zero")));
678 result = rint(c / i);
680 PG_RETURN_CASH(result);
685 * Multiply cash by int2.
688 cash_mul_int2(PG_FUNCTION_ARGS)
690 Cash c = PG_GETARG_CASH(0);
691 int16 s = PG_GETARG_INT16(1);
695 PG_RETURN_CASH(result);
699 * Multiply int2 by cash.
702 int2_mul_cash(PG_FUNCTION_ARGS)
704 int16 s = PG_GETARG_INT16(0);
705 Cash c = PG_GETARG_CASH(1);
709 PG_RETURN_CASH(result);
713 * Divide cash by int2.
717 cash_div_int2(PG_FUNCTION_ARGS)
719 Cash c = PG_GETARG_CASH(0);
720 int16 s = PG_GETARG_INT16(1);
725 (errcode(ERRCODE_DIVISION_BY_ZERO),
726 errmsg("division by zero")));
728 result = rint(c / s);
729 PG_RETURN_CASH(result);
733 * Return larger of two cash values.
736 cashlarger(PG_FUNCTION_ARGS)
738 Cash c1 = PG_GETARG_CASH(0);
739 Cash c2 = PG_GETARG_CASH(1);
742 result = (c1 > c2) ? c1 : c2;
744 PG_RETURN_CASH(result);
748 * Return smaller of two cash values.
751 cashsmaller(PG_FUNCTION_ARGS)
753 Cash c1 = PG_GETARG_CASH(0);
754 Cash c2 = PG_GETARG_CASH(1);
757 result = (c1 < c2) ? c1 : c2;
759 PG_RETURN_CASH(result);
763 * This converts a int4 as well but to a representation using words
764 * Obviously way North American centric - sorry
767 cash_words(PG_FUNCTION_ARGS)
769 Cash value = PG_GETARG_CASH(0);
781 /* work with positive numbers */
785 strcpy(buf, "minus ");
791 /* Now treat as unsigned, to avoid trouble at INT_MIN */
792 val = (uint64) value;
794 m0 = val % INT64CONST(100); /* cents */
795 m1 = (val / INT64CONST(100)) % 1000; /* hundreds */
796 m2 = (val / INT64CONST(100000)) % 1000; /* thousands */
797 m3 = (val / INT64CONST(100000000)) % 1000; /* millions */
798 m4 = (val / INT64CONST(100000000000)) % 1000; /* billions */
799 m5 = (val / INT64CONST(100000000000000)) % 1000; /* trillions */
800 m6 = (val / INT64CONST(100000000000000000)) % 1000; /* quadrillions */
804 strcat(buf, num_word(m6));
805 strcat(buf, " quadrillion ");
810 strcat(buf, num_word(m5));
811 strcat(buf, " trillion ");
816 strcat(buf, num_word(m4));
817 strcat(buf, " billion ");
822 strcat(buf, num_word(m3));
823 strcat(buf, " million ");
828 strcat(buf, num_word(m2));
829 strcat(buf, " thousand ");
833 strcat(buf, num_word(m1));
838 strcat(buf, (val / 100) == 1 ? " dollar and " : " dollars and ");
839 strcat(buf, num_word(m0));
840 strcat(buf, m0 == 1 ? " cent" : " cents");
842 /* capitalize output */
843 buf[0] = pg_toupper((unsigned char) buf[0]);
845 /* return as text datum */
846 PG_RETURN_TEXT_P(cstring_to_text(buf));