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.81 2009/06/10 16:31:32 tgl 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,
324 * the code above adds a trailing ssymbol on the far right,
327 if (buf[LAST_DIGIT] == ssymbol)
328 buf[LAST_DIGIT] = '\0';
330 /* see if we need to signify negative amount */
333 result = palloc(CASH_BUFSZ + 2 - count + strlen(nsymbol));
335 /* Position code of 0 means use parens */
337 sprintf(result, "(%s)", buf + count);
338 else if (convention == 2)
339 sprintf(result, "%s%s", buf + count, nsymbol);
341 sprintf(result, "%s%s", nsymbol, buf + count);
345 result = palloc(CASH_BUFSZ + 2 - count);
346 strcpy(result, buf + count);
349 PG_RETURN_CSTRING(result);
353 * cash_recv - converts external binary format to cash
356 cash_recv(PG_FUNCTION_ARGS)
358 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
360 PG_RETURN_CASH((Cash) pq_getmsgint64(buf));
364 * cash_send - converts cash to binary format
367 cash_send(PG_FUNCTION_ARGS)
369 Cash arg1 = PG_GETARG_CASH(0);
372 pq_begintypsend(&buf);
373 pq_sendint64(&buf, arg1);
374 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
378 * Comparison functions
382 cash_eq(PG_FUNCTION_ARGS)
384 Cash c1 = PG_GETARG_CASH(0);
385 Cash c2 = PG_GETARG_CASH(1);
387 PG_RETURN_BOOL(c1 == c2);
391 cash_ne(PG_FUNCTION_ARGS)
393 Cash c1 = PG_GETARG_CASH(0);
394 Cash c2 = PG_GETARG_CASH(1);
396 PG_RETURN_BOOL(c1 != c2);
400 cash_lt(PG_FUNCTION_ARGS)
402 Cash c1 = PG_GETARG_CASH(0);
403 Cash c2 = PG_GETARG_CASH(1);
405 PG_RETURN_BOOL(c1 < c2);
409 cash_le(PG_FUNCTION_ARGS)
411 Cash c1 = PG_GETARG_CASH(0);
412 Cash c2 = PG_GETARG_CASH(1);
414 PG_RETURN_BOOL(c1 <= c2);
418 cash_gt(PG_FUNCTION_ARGS)
420 Cash c1 = PG_GETARG_CASH(0);
421 Cash c2 = PG_GETARG_CASH(1);
423 PG_RETURN_BOOL(c1 > c2);
427 cash_ge(PG_FUNCTION_ARGS)
429 Cash c1 = PG_GETARG_CASH(0);
430 Cash c2 = PG_GETARG_CASH(1);
432 PG_RETURN_BOOL(c1 >= c2);
436 cash_cmp(PG_FUNCTION_ARGS)
438 Cash c1 = PG_GETARG_CASH(0);
439 Cash c2 = PG_GETARG_CASH(1);
451 * Add two cash values.
454 cash_pl(PG_FUNCTION_ARGS)
456 Cash c1 = PG_GETARG_CASH(0);
457 Cash c2 = PG_GETARG_CASH(1);
462 PG_RETURN_CASH(result);
467 * Subtract two cash values.
470 cash_mi(PG_FUNCTION_ARGS)
472 Cash c1 = PG_GETARG_CASH(0);
473 Cash c2 = PG_GETARG_CASH(1);
478 PG_RETURN_CASH(result);
483 * Multiply cash by float8.
486 cash_mul_flt8(PG_FUNCTION_ARGS)
488 Cash c = PG_GETARG_CASH(0);
489 float8 f = PG_GETARG_FLOAT8(1);
493 PG_RETURN_CASH(result);
498 * Multiply float8 by cash.
501 flt8_mul_cash(PG_FUNCTION_ARGS)
503 float8 f = PG_GETARG_FLOAT8(0);
504 Cash c = PG_GETARG_CASH(1);
508 PG_RETURN_CASH(result);
513 * Divide cash by float8.
516 cash_div_flt8(PG_FUNCTION_ARGS)
518 Cash c = PG_GETARG_CASH(0);
519 float8 f = PG_GETARG_FLOAT8(1);
524 (errcode(ERRCODE_DIVISION_BY_ZERO),
525 errmsg("division by zero")));
527 result = rint(c / f);
528 PG_RETURN_CASH(result);
533 * Multiply cash by float4.
536 cash_mul_flt4(PG_FUNCTION_ARGS)
538 Cash c = PG_GETARG_CASH(0);
539 float4 f = PG_GETARG_FLOAT4(1);
543 PG_RETURN_CASH(result);
548 * Multiply float4 by cash.
551 flt4_mul_cash(PG_FUNCTION_ARGS)
553 float4 f = PG_GETARG_FLOAT4(0);
554 Cash c = PG_GETARG_CASH(1);
558 PG_RETURN_CASH(result);
563 * Divide cash by float4.
567 cash_div_flt4(PG_FUNCTION_ARGS)
569 Cash c = PG_GETARG_CASH(0);
570 float4 f = PG_GETARG_FLOAT4(1);
575 (errcode(ERRCODE_DIVISION_BY_ZERO),
576 errmsg("division by zero")));
578 result = rint(c / f);
579 PG_RETURN_CASH(result);
584 * Multiply cash by int8.
587 cash_mul_int8(PG_FUNCTION_ARGS)
589 Cash c = PG_GETARG_CASH(0);
590 int64 i = PG_GETARG_INT64(1);
594 PG_RETURN_CASH(result);
599 * Multiply int8 by cash.
602 int8_mul_cash(PG_FUNCTION_ARGS)
604 int64 i = PG_GETARG_INT64(0);
605 Cash c = PG_GETARG_CASH(1);
609 PG_RETURN_CASH(result);
613 * Divide cash by 8-byte integer.
616 cash_div_int8(PG_FUNCTION_ARGS)
618 Cash c = PG_GETARG_CASH(0);
619 int64 i = PG_GETARG_INT64(1);
624 (errcode(ERRCODE_DIVISION_BY_ZERO),
625 errmsg("division by zero")));
627 result = rint(c / i);
629 PG_RETURN_CASH(result);
634 * Multiply cash by int4.
637 cash_mul_int4(PG_FUNCTION_ARGS)
639 Cash c = PG_GETARG_CASH(0);
640 int32 i = PG_GETARG_INT32(1);
644 PG_RETURN_CASH(result);
649 * Multiply int4 by cash.
652 int4_mul_cash(PG_FUNCTION_ARGS)
654 int32 i = PG_GETARG_INT32(0);
655 Cash c = PG_GETARG_CASH(1);
659 PG_RETURN_CASH(result);
664 * Divide cash by 4-byte integer.
668 cash_div_int4(PG_FUNCTION_ARGS)
670 Cash c = PG_GETARG_CASH(0);
671 int32 i = PG_GETARG_INT32(1);
676 (errcode(ERRCODE_DIVISION_BY_ZERO),
677 errmsg("division by zero")));
679 result = rint(c / i);
681 PG_RETURN_CASH(result);
686 * Multiply cash by int2.
689 cash_mul_int2(PG_FUNCTION_ARGS)
691 Cash c = PG_GETARG_CASH(0);
692 int16 s = PG_GETARG_INT16(1);
696 PG_RETURN_CASH(result);
700 * Multiply int2 by cash.
703 int2_mul_cash(PG_FUNCTION_ARGS)
705 int16 s = PG_GETARG_INT16(0);
706 Cash c = PG_GETARG_CASH(1);
710 PG_RETURN_CASH(result);
714 * Divide cash by int2.
718 cash_div_int2(PG_FUNCTION_ARGS)
720 Cash c = PG_GETARG_CASH(0);
721 int16 s = PG_GETARG_INT16(1);
726 (errcode(ERRCODE_DIVISION_BY_ZERO),
727 errmsg("division by zero")));
729 result = rint(c / s);
730 PG_RETURN_CASH(result);
734 * Return larger of two cash values.
737 cashlarger(PG_FUNCTION_ARGS)
739 Cash c1 = PG_GETARG_CASH(0);
740 Cash c2 = PG_GETARG_CASH(1);
743 result = (c1 > c2) ? c1 : c2;
745 PG_RETURN_CASH(result);
749 * Return smaller of two cash values.
752 cashsmaller(PG_FUNCTION_ARGS)
754 Cash c1 = PG_GETARG_CASH(0);
755 Cash c2 = PG_GETARG_CASH(1);
758 result = (c1 < c2) ? c1 : c2;
760 PG_RETURN_CASH(result);
764 * This converts a int4 as well but to a representation using words
765 * Obviously way North American centric - sorry
768 cash_words(PG_FUNCTION_ARGS)
770 Cash value = PG_GETARG_CASH(0);
782 /* work with positive numbers */
786 strcpy(buf, "minus ");
792 /* Now treat as unsigned, to avoid trouble at INT_MIN */
793 val = (uint64) value;
795 m0 = val % INT64CONST(100); /* cents */
796 m1 = (val / INT64CONST(100)) % 1000; /* hundreds */
797 m2 = (val / INT64CONST(100000)) % 1000; /* thousands */
798 m3 = (val / INT64CONST(100000000)) % 1000; /* millions */
799 m4 = (val / INT64CONST(100000000000)) % 1000; /* billions */
800 m5 = (val / INT64CONST(100000000000000)) % 1000; /* trillions */
801 m6 = (val / INT64CONST(100000000000000000)) % 1000; /* quadrillions */
805 strcat(buf, num_word(m6));
806 strcat(buf, " quadrillion ");
811 strcat(buf, num_word(m5));
812 strcat(buf, " trillion ");
817 strcat(buf, num_word(m4));
818 strcat(buf, " billion ");
823 strcat(buf, num_word(m3));
824 strcat(buf, " million ");
829 strcat(buf, num_word(m2));
830 strcat(buf, " thousand ");
834 strcat(buf, num_word(m1));
839 strcat(buf, (val / 100) == 1 ? " dollar and " : " dollars and ");
840 strcat(buf, num_word(m0));
841 strcat(buf, m0 == 1 ? " cent" : " cents");
843 /* capitalize output */
844 buf[0] = pg_toupper((unsigned char) buf[0]);
846 /* return as text datum */
847 PG_RETURN_TEXT_P(cstring_to_text(buf));