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.83 2010/07/16 02:15:53 tgl Exp $
26 #include "libpq/pqformat.h"
27 #include "utils/builtins.h"
28 #include "utils/cash.h"
29 #include "utils/numeric.h"
30 #include "utils/pg_locale.h"
34 #define TERMINATOR (CASH_BUFSZ - 1)
35 #define LAST_PAREN (TERMINATOR - 1)
36 #define LAST_DIGIT (LAST_PAREN - 1)
39 /*************************************************************************
41 ************************************************************************/
47 static const char *small[] = {
48 "zero", "one", "two", "three", "four", "five", "six", "seven",
49 "eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen",
50 "fifteen", "sixteen", "seventeen", "eighteen", "nineteen", "twenty",
51 "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety"
53 const char **big = small + 18;
56 /* deal with the simple cases first */
60 /* is it an even multiple of 100? */
63 sprintf(buf, "%s hundred", small[value / 100]);
70 /* is it an even multiple of 10 other than 10? */
71 if (value % 10 == 0 && tu > 10)
72 sprintf(buf, "%s hundred %s",
73 small[value / 100], big[tu / 10]);
75 sprintf(buf, "%s hundred and %s",
76 small[value / 100], small[tu]);
78 sprintf(buf, "%s hundred %s %s",
79 small[value / 100], big[tu / 10], small[tu % 10]);
83 /* is it an even multiple of 10 other than 10? */
84 if (value % 10 == 0 && tu > 10)
85 sprintf(buf, "%s", big[tu / 10]);
87 sprintf(buf, "%s", small[tu]);
89 sprintf(buf, "%s %s", big[tu / 10], small[tu % 10]);
97 * Convert a string to a cash data type.
98 * Format is [$]###[,]###[.##]
99 * Examples: 123.45 $123.45 $123,456.78
103 cash_in(PG_FUNCTION_ARGS)
105 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;
266 struct lconv *lconvert = PGLC_localeconv();
268 /* see comments about frac_digits in cash_in() */
269 points = lconvert->frac_digits;
270 if (points < 0 || points > 10)
271 points = 2; /* best guess in this case, I think */
274 * As with frac_digits, must apply a range check to mon_grouping to avoid
275 * being fooled by variant CHAR_MAX values.
277 mon_group = *lconvert->mon_grouping;
278 if (mon_group <= 0 || mon_group > 6)
281 convention = lconvert->n_sign_posn;
282 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
283 if (*lconvert->mon_thousands_sep != '\0')
284 ssymbol = *lconvert->mon_thousands_sep;
286 /* ssymbol should not equal dsymbol */
287 ssymbol = (dsymbol != ',') ? ',' : '.';
288 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
289 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
291 point_pos = LAST_DIGIT - points;
293 point_pos -= (points - 1) / mon_group;
294 ssymbol_position = point_pos % (mon_group + 1);
296 /* we work with positive amounts and add the minus sign at the end */
303 /* allow for trailing negative strings */
304 MemSet(buf, ' ', CASH_BUFSZ);
305 buf[TERMINATOR] = buf[LAST_PAREN] = '\0';
307 while (value || count > (point_pos - 2))
309 if (points && count == point_pos)
310 buf[count--] = dsymbol;
311 else if (ssymbol && count % (mon_group + 1) == ssymbol_position)
312 buf[count--] = ssymbol;
314 buf[count--] = ((uint64) value % 10) + '0';
315 value = ((uint64) value) / 10;
318 strncpy((buf + count - strlen(csymbol) + 1), csymbol, strlen(csymbol));
319 count -= strlen(csymbol) - 1;
322 * If points == 0 and the number of digits % mon_group == 0, the code
323 * above adds a trailing ssymbol on the far right, so remove it.
325 if (buf[LAST_DIGIT] == ssymbol)
326 buf[LAST_DIGIT] = '\0';
328 /* see if we need to signify negative amount */
331 result = palloc(CASH_BUFSZ + 2 - count + strlen(nsymbol));
333 /* Position code of 0 means use parens */
335 sprintf(result, "(%s)", buf + count);
336 else if (convention == 2)
337 sprintf(result, "%s%s", buf + count, nsymbol);
339 sprintf(result, "%s%s", nsymbol, buf + count);
343 result = palloc(CASH_BUFSZ + 2 - count);
344 strcpy(result, buf + count);
347 PG_RETURN_CSTRING(result);
351 * cash_recv - converts external binary format to cash
354 cash_recv(PG_FUNCTION_ARGS)
356 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
358 PG_RETURN_CASH((Cash) pq_getmsgint64(buf));
362 * cash_send - converts cash to binary format
365 cash_send(PG_FUNCTION_ARGS)
367 Cash arg1 = PG_GETARG_CASH(0);
370 pq_begintypsend(&buf);
371 pq_sendint64(&buf, arg1);
372 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
376 * Comparison functions
380 cash_eq(PG_FUNCTION_ARGS)
382 Cash c1 = PG_GETARG_CASH(0);
383 Cash c2 = PG_GETARG_CASH(1);
385 PG_RETURN_BOOL(c1 == c2);
389 cash_ne(PG_FUNCTION_ARGS)
391 Cash c1 = PG_GETARG_CASH(0);
392 Cash c2 = PG_GETARG_CASH(1);
394 PG_RETURN_BOOL(c1 != c2);
398 cash_lt(PG_FUNCTION_ARGS)
400 Cash c1 = PG_GETARG_CASH(0);
401 Cash c2 = PG_GETARG_CASH(1);
403 PG_RETURN_BOOL(c1 < c2);
407 cash_le(PG_FUNCTION_ARGS)
409 Cash c1 = PG_GETARG_CASH(0);
410 Cash c2 = PG_GETARG_CASH(1);
412 PG_RETURN_BOOL(c1 <= c2);
416 cash_gt(PG_FUNCTION_ARGS)
418 Cash c1 = PG_GETARG_CASH(0);
419 Cash c2 = PG_GETARG_CASH(1);
421 PG_RETURN_BOOL(c1 > c2);
425 cash_ge(PG_FUNCTION_ARGS)
427 Cash c1 = PG_GETARG_CASH(0);
428 Cash c2 = PG_GETARG_CASH(1);
430 PG_RETURN_BOOL(c1 >= c2);
434 cash_cmp(PG_FUNCTION_ARGS)
436 Cash c1 = PG_GETARG_CASH(0);
437 Cash c2 = PG_GETARG_CASH(1);
449 * Add two cash values.
452 cash_pl(PG_FUNCTION_ARGS)
454 Cash c1 = PG_GETARG_CASH(0);
455 Cash c2 = PG_GETARG_CASH(1);
460 PG_RETURN_CASH(result);
465 * Subtract two cash values.
468 cash_mi(PG_FUNCTION_ARGS)
470 Cash c1 = PG_GETARG_CASH(0);
471 Cash c2 = PG_GETARG_CASH(1);
476 PG_RETURN_CASH(result);
481 * Divide cash by cash, returning float8.
484 cash_div_cash(PG_FUNCTION_ARGS)
486 Cash dividend = PG_GETARG_CASH(0);
487 Cash divisor = PG_GETARG_CASH(1);
492 (errcode(ERRCODE_DIVISION_BY_ZERO),
493 errmsg("division by zero")));
495 quotient = (float8) dividend / (float8) divisor;
496 PG_RETURN_FLOAT8(quotient);
501 * Multiply cash by float8.
504 cash_mul_flt8(PG_FUNCTION_ARGS)
506 Cash c = PG_GETARG_CASH(0);
507 float8 f = PG_GETARG_FLOAT8(1);
511 PG_RETURN_CASH(result);
516 * Multiply float8 by cash.
519 flt8_mul_cash(PG_FUNCTION_ARGS)
521 float8 f = PG_GETARG_FLOAT8(0);
522 Cash c = PG_GETARG_CASH(1);
526 PG_RETURN_CASH(result);
531 * Divide cash by float8.
534 cash_div_flt8(PG_FUNCTION_ARGS)
536 Cash c = PG_GETARG_CASH(0);
537 float8 f = PG_GETARG_FLOAT8(1);
542 (errcode(ERRCODE_DIVISION_BY_ZERO),
543 errmsg("division by zero")));
545 result = rint(c / f);
546 PG_RETURN_CASH(result);
551 * Multiply cash by float4.
554 cash_mul_flt4(PG_FUNCTION_ARGS)
556 Cash c = PG_GETARG_CASH(0);
557 float4 f = PG_GETARG_FLOAT4(1);
561 PG_RETURN_CASH(result);
566 * Multiply float4 by cash.
569 flt4_mul_cash(PG_FUNCTION_ARGS)
571 float4 f = PG_GETARG_FLOAT4(0);
572 Cash c = PG_GETARG_CASH(1);
576 PG_RETURN_CASH(result);
581 * Divide cash by float4.
585 cash_div_flt4(PG_FUNCTION_ARGS)
587 Cash c = PG_GETARG_CASH(0);
588 float4 f = PG_GETARG_FLOAT4(1);
593 (errcode(ERRCODE_DIVISION_BY_ZERO),
594 errmsg("division by zero")));
596 result = rint(c / f);
597 PG_RETURN_CASH(result);
602 * Multiply cash by int8.
605 cash_mul_int8(PG_FUNCTION_ARGS)
607 Cash c = PG_GETARG_CASH(0);
608 int64 i = PG_GETARG_INT64(1);
612 PG_RETURN_CASH(result);
617 * Multiply int8 by cash.
620 int8_mul_cash(PG_FUNCTION_ARGS)
622 int64 i = PG_GETARG_INT64(0);
623 Cash c = PG_GETARG_CASH(1);
627 PG_RETURN_CASH(result);
631 * Divide cash by 8-byte integer.
634 cash_div_int8(PG_FUNCTION_ARGS)
636 Cash c = PG_GETARG_CASH(0);
637 int64 i = PG_GETARG_INT64(1);
642 (errcode(ERRCODE_DIVISION_BY_ZERO),
643 errmsg("division by zero")));
645 result = rint(c / i);
647 PG_RETURN_CASH(result);
652 * Multiply cash by int4.
655 cash_mul_int4(PG_FUNCTION_ARGS)
657 Cash c = PG_GETARG_CASH(0);
658 int32 i = PG_GETARG_INT32(1);
662 PG_RETURN_CASH(result);
667 * Multiply int4 by cash.
670 int4_mul_cash(PG_FUNCTION_ARGS)
672 int32 i = PG_GETARG_INT32(0);
673 Cash c = PG_GETARG_CASH(1);
677 PG_RETURN_CASH(result);
682 * Divide cash by 4-byte integer.
686 cash_div_int4(PG_FUNCTION_ARGS)
688 Cash c = PG_GETARG_CASH(0);
689 int32 i = PG_GETARG_INT32(1);
694 (errcode(ERRCODE_DIVISION_BY_ZERO),
695 errmsg("division by zero")));
697 result = rint(c / i);
699 PG_RETURN_CASH(result);
704 * Multiply cash by int2.
707 cash_mul_int2(PG_FUNCTION_ARGS)
709 Cash c = PG_GETARG_CASH(0);
710 int16 s = PG_GETARG_INT16(1);
714 PG_RETURN_CASH(result);
718 * Multiply int2 by cash.
721 int2_mul_cash(PG_FUNCTION_ARGS)
723 int16 s = PG_GETARG_INT16(0);
724 Cash c = PG_GETARG_CASH(1);
728 PG_RETURN_CASH(result);
732 * Divide cash by int2.
736 cash_div_int2(PG_FUNCTION_ARGS)
738 Cash c = PG_GETARG_CASH(0);
739 int16 s = PG_GETARG_INT16(1);
744 (errcode(ERRCODE_DIVISION_BY_ZERO),
745 errmsg("division by zero")));
747 result = rint(c / s);
748 PG_RETURN_CASH(result);
752 * Return larger of two cash values.
755 cashlarger(PG_FUNCTION_ARGS)
757 Cash c1 = PG_GETARG_CASH(0);
758 Cash c2 = PG_GETARG_CASH(1);
761 result = (c1 > c2) ? c1 : c2;
763 PG_RETURN_CASH(result);
767 * Return smaller of two cash values.
770 cashsmaller(PG_FUNCTION_ARGS)
772 Cash c1 = PG_GETARG_CASH(0);
773 Cash c2 = PG_GETARG_CASH(1);
776 result = (c1 < c2) ? c1 : c2;
778 PG_RETURN_CASH(result);
782 * This converts a int4 as well but to a representation using words
783 * Obviously way North American centric - sorry
786 cash_words(PG_FUNCTION_ARGS)
788 Cash value = PG_GETARG_CASH(0);
800 /* work with positive numbers */
804 strcpy(buf, "minus ");
810 /* Now treat as unsigned, to avoid trouble at INT_MIN */
811 val = (uint64) value;
813 m0 = val % INT64CONST(100); /* cents */
814 m1 = (val / INT64CONST(100)) % 1000; /* hundreds */
815 m2 = (val / INT64CONST(100000)) % 1000; /* thousands */
816 m3 = (val / INT64CONST(100000000)) % 1000; /* millions */
817 m4 = (val / INT64CONST(100000000000)) % 1000; /* billions */
818 m5 = (val / INT64CONST(100000000000000)) % 1000; /* trillions */
819 m6 = (val / INT64CONST(100000000000000000)) % 1000; /* quadrillions */
823 strcat(buf, num_word(m6));
824 strcat(buf, " quadrillion ");
829 strcat(buf, num_word(m5));
830 strcat(buf, " trillion ");
835 strcat(buf, num_word(m4));
836 strcat(buf, " billion ");
841 strcat(buf, num_word(m3));
842 strcat(buf, " million ");
847 strcat(buf, num_word(m2));
848 strcat(buf, " thousand ");
852 strcat(buf, num_word(m1));
857 strcat(buf, (val / 100) == 1 ? " dollar and " : " dollars and ");
858 strcat(buf, num_word(m0));
859 strcat(buf, m0 == 1 ? " cent" : " cents");
861 /* capitalize output */
862 buf[0] = pg_toupper((unsigned char) buf[0]);
864 /* return as text datum */
865 PG_RETURN_TEXT_P(cstring_to_text(buf));
870 * Convert cash to numeric.
873 cash_numeric(PG_FUNCTION_ARGS)
875 Cash money = PG_GETARG_CASH(0);
883 struct lconv *lconvert = PGLC_localeconv();
885 /* see comments about frac_digits in cash_in() */
886 fpoint = lconvert->frac_digits;
887 if (fpoint < 0 || fpoint > 10)
890 /* compute required scale factor */
892 for (i = 0; i < fpoint; i++)
895 /* form the result as money / scale */
896 amount = DirectFunctionCall1(int8_numeric, Int64GetDatum(money));
897 numeric_scale = DirectFunctionCall1(int8_numeric, Int64GetDatum(scale));
898 quotient = DirectFunctionCall2(numeric_div, amount, numeric_scale);
900 /* forcibly round to exactly the intended number of digits */
901 result = DatumGetNumeric(DirectFunctionCall2(numeric_round,
903 Int32GetDatum(fpoint)));
905 PG_RETURN_NUMERIC(result);
909 * Convert numeric to cash.
912 numeric_cash(PG_FUNCTION_ARGS)
914 Datum amount = PG_GETARG_DATUM(0);
920 struct lconv *lconvert = PGLC_localeconv();
922 /* see comments about frac_digits in cash_in() */
923 fpoint = lconvert->frac_digits;
924 if (fpoint < 0 || fpoint > 10)
927 /* compute required scale factor */
929 for (i = 0; i < fpoint; i++)
932 /* multiply the input amount by scale factor */
933 numeric_scale = DirectFunctionCall1(int8_numeric, Int64GetDatum(scale));
934 amount = DirectFunctionCall2(numeric_mul, amount, numeric_scale);
936 /* note that numeric_int8 will round to nearest integer for us */
937 result = DatumGetInt64(DirectFunctionCall1(numeric_int8, amount));
939 PG_RETURN_CASH(result);