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.78 2008/03/25 22:42:43 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 * Cash is a pass-by-ref SQL type, so we must pass and return pointers.
39 * These macros and support routine hide the pass-by-refness.
41 #define PG_GETARG_CASH(n) (* ((Cash *) PG_GETARG_POINTER(n)))
42 #define PG_RETURN_CASH(x) return CashGetDatum(x)
46 /*************************************************************************
48 ************************************************************************/
54 static const char *small[] = {
55 "zero", "one", "two", "three", "four", "five", "six", "seven",
56 "eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen",
57 "fifteen", "sixteen", "seventeen", "eighteen", "nineteen", "twenty",
58 "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety"
60 const char **big = small + 18;
63 /* deal with the simple cases first */
67 /* is it an even multiple of 100? */
70 sprintf(buf, "%s hundred", small[value / 100]);
77 /* is it an even multiple of 10 other than 10? */
78 if (value % 10 == 0 && tu > 10)
79 sprintf(buf, "%s hundred %s",
80 small[value / 100], big[tu / 10]);
82 sprintf(buf, "%s hundred and %s",
83 small[value / 100], small[tu]);
85 sprintf(buf, "%s hundred %s %s",
86 small[value / 100], big[tu / 10], small[tu % 10]);
90 /* is it an even multiple of 10 other than 10? */
91 if (value % 10 == 0 && tu > 10)
92 sprintf(buf, "%s", big[tu / 10]);
94 sprintf(buf, "%s", small[tu]);
96 sprintf(buf, "%s %s", big[tu / 10], small[tu % 10]);
103 CashGetDatum(Cash value)
105 Cash *result = (Cash *) palloc(sizeof(Cash));
108 return PointerGetDatum(result);
113 * Convert a string to a cash data type.
114 * Format is [$]###[,]###[.##]
115 * Examples: 123.45 $123.45 $123,456.78
119 cash_in(PG_FUNCTION_ARGS)
121 char *str = PG_GETARG_CSTRING(0);
135 struct lconv *lconvert = PGLC_localeconv();
138 * frac_digits will be CHAR_MAX in some locales, notably C. However, just
139 * testing for == CHAR_MAX is risky, because of compilers like gcc that
140 * "helpfully" let you alter the platform-standard definition of whether
141 * char is signed or not. If we are so unfortunate as to get compiled
142 * with a nonstandard -fsigned-char or -funsigned-char switch, then our
143 * idea of CHAR_MAX will not agree with libc's. The safest course is not
144 * to test for CHAR_MAX at all, but to impose a range check for plausible
145 * frac_digits values.
147 fpoint = lconvert->frac_digits;
148 if (fpoint < 0 || fpoint > 10)
149 fpoint = 2; /* best guess in this case, I think */
151 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
152 if (*lconvert->mon_thousands_sep != '\0')
153 ssymbol = *lconvert->mon_thousands_sep;
155 /* ssymbol should not equal dsymbol */
156 ssymbol = (dsymbol != ',') ? ',' : '.';
157 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
158 psymbol = ((*lconvert->positive_sign != '\0') ? *lconvert->positive_sign : '+');
159 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
162 printf("cashin- precision '%d'; decimal '%c'; thousands '%c'; currency '%s'; positive '%c'; negative '%s'\n",
163 fpoint, dsymbol, ssymbol, csymbol, psymbol, nsymbol);
166 /* we need to add all sorts of checking here. For now just */
167 /* strip all leading whitespace and any leading currency symbol */
168 while (isspace((unsigned char) *s))
170 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
171 s += strlen(csymbol);
174 printf("cashin- string is '%s'\n", s);
177 /* a leading minus or paren signifies a negative number */
178 /* again, better heuristics needed */
179 /* XXX - doesn't properly check for balanced parens - djmc */
180 if (strncmp(s, nsymbol, strlen(nsymbol)) == 0)
183 s += strlen(nsymbol);
185 printf("cashin- negative symbol; string is '%s'\n", s);
193 else if (*s == psymbol)
197 printf("cashin- string is '%s'\n", s);
200 while (isspace((unsigned char) *s))
202 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
203 s += strlen(csymbol);
206 printf("cashin- string is '%s'\n", s);
211 /* we look for digits as int8 as we have less */
212 /* than the required number of decimal places */
213 if (isdigit((unsigned char) *s) && dec < fpoint)
215 value = (value * 10) + *s - '0';
221 /* decimal point? then start counting fractions... */
222 else if (*s == dsymbol && !seen_dot)
227 /* not "thousands" separator? */
228 else if (*s != ssymbol)
231 if (isdigit((unsigned char) *s) && *s >= '5')
234 /* adjust for less than required decimal places */
235 for (; dec < fpoint; dec++)
242 /* should only be trailing digits followed by whitespace or right paren */
243 while (isdigit((unsigned char) *s))
245 while (isspace((unsigned char) *s) || *s == ')')
250 (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
251 errmsg("invalid input syntax for type money: \"%s\"", str)));
253 result = value * sgn;
256 printf("cashin- result is %d\n", result);
259 PG_RETURN_CASH(result);
264 * Function to convert cash to a dollars and cents representation.
265 * XXX HACK This code appears to assume US conventions for
266 * positive-valued amounts. - tgl 97/04/14
269 cash_out(PG_FUNCTION_ARGS)
271 Cash value = PG_GETARG_CASH(0);
273 char buf[CASH_BUFSZ];
275 int count = LAST_DIGIT;
277 int ssymbol_position = 0;
286 struct lconv *lconvert = PGLC_localeconv();
288 /* see comments about frac_digits in cash_in() */
289 points = lconvert->frac_digits;
290 if (points < 0 || points > 10)
291 points = 2; /* best guess in this case, I think */
294 * As with frac_digits, must apply a range check to mon_grouping to avoid
295 * being fooled by variant CHAR_MAX values.
297 mon_group = *lconvert->mon_grouping;
298 if (mon_group <= 0 || mon_group > 6)
301 convention = lconvert->n_sign_posn;
302 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
303 if (*lconvert->mon_thousands_sep != '\0')
304 ssymbol = *lconvert->mon_thousands_sep;
306 /* ssymbol should not equal dsymbol */
307 ssymbol = (dsymbol != ',') ? ',' : '.';
308 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
309 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
311 point_pos = LAST_DIGIT - points;
313 point_pos -= (points - 1) / mon_group;
314 ssymbol_position = point_pos % (mon_group + 1);
316 /* we work with positive amounts and add the minus sign at the end */
323 /* allow for trailing negative strings */
324 MemSet(buf, ' ', CASH_BUFSZ);
325 buf[TERMINATOR] = buf[LAST_PAREN] = '\0';
327 while (value || count > (point_pos - 2))
329 if (points && count == point_pos)
330 buf[count--] = dsymbol;
331 else if (ssymbol && count % (mon_group + 1) == ssymbol_position)
332 buf[count--] = ssymbol;
334 buf[count--] = ((uint64) value % 10) + '0';
335 value = ((uint64) value) / 10;
338 strncpy((buf + count - strlen(csymbol) + 1), csymbol, strlen(csymbol));
339 count -= strlen(csymbol) - 1;
342 * If points == 0 and the number of digits % mon_group == 0,
343 * the code above adds a trailing ssymbol on the far right,
346 if (buf[LAST_DIGIT] == ssymbol)
347 buf[LAST_DIGIT] = '\0';
349 /* see if we need to signify negative amount */
352 result = palloc(CASH_BUFSZ + 2 - count + strlen(nsymbol));
354 /* Position code of 0 means use parens */
356 sprintf(result, "(%s)", buf + count);
357 else if (convention == 2)
358 sprintf(result, "%s%s", buf + count, nsymbol);
360 sprintf(result, "%s%s", nsymbol, buf + count);
364 result = palloc(CASH_BUFSZ + 2 - count);
365 strcpy(result, buf + count);
368 PG_RETURN_CSTRING(result);
372 * cash_recv - converts external binary format to cash
375 cash_recv(PG_FUNCTION_ARGS)
377 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
379 PG_RETURN_CASH((Cash) pq_getmsgint64(buf));
383 * cash_send - converts cash to binary format
386 cash_send(PG_FUNCTION_ARGS)
388 Cash arg1 = PG_GETARG_CASH(0);
391 pq_begintypsend(&buf);
392 pq_sendint64(&buf, arg1);
393 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
397 * Comparison functions
401 cash_eq(PG_FUNCTION_ARGS)
403 Cash c1 = PG_GETARG_CASH(0);
404 Cash c2 = PG_GETARG_CASH(1);
406 PG_RETURN_BOOL(c1 == c2);
410 cash_ne(PG_FUNCTION_ARGS)
412 Cash c1 = PG_GETARG_CASH(0);
413 Cash c2 = PG_GETARG_CASH(1);
415 PG_RETURN_BOOL(c1 != c2);
419 cash_lt(PG_FUNCTION_ARGS)
421 Cash c1 = PG_GETARG_CASH(0);
422 Cash c2 = PG_GETARG_CASH(1);
424 PG_RETURN_BOOL(c1 < c2);
428 cash_le(PG_FUNCTION_ARGS)
430 Cash c1 = PG_GETARG_CASH(0);
431 Cash c2 = PG_GETARG_CASH(1);
433 PG_RETURN_BOOL(c1 <= c2);
437 cash_gt(PG_FUNCTION_ARGS)
439 Cash c1 = PG_GETARG_CASH(0);
440 Cash c2 = PG_GETARG_CASH(1);
442 PG_RETURN_BOOL(c1 > c2);
446 cash_ge(PG_FUNCTION_ARGS)
448 Cash c1 = PG_GETARG_CASH(0);
449 Cash c2 = PG_GETARG_CASH(1);
451 PG_RETURN_BOOL(c1 >= c2);
455 cash_cmp(PG_FUNCTION_ARGS)
457 Cash c1 = PG_GETARG_CASH(0);
458 Cash c2 = PG_GETARG_CASH(1);
470 * Add two cash values.
473 cash_pl(PG_FUNCTION_ARGS)
475 Cash c1 = PG_GETARG_CASH(0);
476 Cash c2 = PG_GETARG_CASH(1);
481 PG_RETURN_CASH(result);
486 * Subtract two cash values.
489 cash_mi(PG_FUNCTION_ARGS)
491 Cash c1 = PG_GETARG_CASH(0);
492 Cash c2 = PG_GETARG_CASH(1);
497 PG_RETURN_CASH(result);
502 * Multiply cash by float8.
505 cash_mul_flt8(PG_FUNCTION_ARGS)
507 Cash c = PG_GETARG_CASH(0);
508 float8 f = PG_GETARG_FLOAT8(1);
512 PG_RETURN_CASH(result);
517 * Multiply float8 by cash.
520 flt8_mul_cash(PG_FUNCTION_ARGS)
522 float8 f = PG_GETARG_FLOAT8(0);
523 Cash c = PG_GETARG_CASH(1);
527 PG_RETURN_CASH(result);
532 * Divide cash by float8.
535 cash_div_flt8(PG_FUNCTION_ARGS)
537 Cash c = PG_GETARG_CASH(0);
538 float8 f = PG_GETARG_FLOAT8(1);
543 (errcode(ERRCODE_DIVISION_BY_ZERO),
544 errmsg("division by zero")));
546 result = rint(c / f);
547 PG_RETURN_CASH(result);
552 * Multiply cash by float4.
555 cash_mul_flt4(PG_FUNCTION_ARGS)
557 Cash c = PG_GETARG_CASH(0);
558 float4 f = PG_GETARG_FLOAT4(1);
562 PG_RETURN_CASH(result);
567 * Multiply float4 by cash.
570 flt4_mul_cash(PG_FUNCTION_ARGS)
572 float4 f = PG_GETARG_FLOAT4(0);
573 Cash c = PG_GETARG_CASH(1);
577 PG_RETURN_CASH(result);
582 * Divide cash by float4.
586 cash_div_flt4(PG_FUNCTION_ARGS)
588 Cash c = PG_GETARG_CASH(0);
589 float4 f = PG_GETARG_FLOAT4(1);
594 (errcode(ERRCODE_DIVISION_BY_ZERO),
595 errmsg("division by zero")));
597 result = rint(c / f);
598 PG_RETURN_CASH(result);
603 * Multiply cash by int8.
606 cash_mul_int8(PG_FUNCTION_ARGS)
608 Cash c = PG_GETARG_CASH(0);
609 int64 i = PG_GETARG_INT64(1);
613 PG_RETURN_CASH(result);
618 * Multiply int8 by cash.
621 int8_mul_cash(PG_FUNCTION_ARGS)
623 int64 i = PG_GETARG_INT64(0);
624 Cash c = PG_GETARG_CASH(1);
628 PG_RETURN_CASH(result);
632 * Divide cash by 8-byte integer.
635 cash_div_int8(PG_FUNCTION_ARGS)
637 Cash c = PG_GETARG_CASH(0);
638 int64 i = PG_GETARG_INT64(1);
643 (errcode(ERRCODE_DIVISION_BY_ZERO),
644 errmsg("division by zero")));
646 result = rint(c / i);
648 PG_RETURN_CASH(result);
653 * Multiply cash by int4.
656 cash_mul_int4(PG_FUNCTION_ARGS)
658 Cash c = PG_GETARG_CASH(0);
659 int32 i = PG_GETARG_INT32(1);
663 PG_RETURN_CASH(result);
668 * Multiply int4 by cash.
671 int4_mul_cash(PG_FUNCTION_ARGS)
673 int32 i = PG_GETARG_INT32(0);
674 Cash c = PG_GETARG_CASH(1);
678 PG_RETURN_CASH(result);
683 * Divide cash by 4-byte integer.
687 cash_div_int4(PG_FUNCTION_ARGS)
689 Cash c = PG_GETARG_CASH(0);
690 int32 i = PG_GETARG_INT32(1);
695 (errcode(ERRCODE_DIVISION_BY_ZERO),
696 errmsg("division by zero")));
698 result = rint(c / i);
700 PG_RETURN_CASH(result);
705 * Multiply cash by int2.
708 cash_mul_int2(PG_FUNCTION_ARGS)
710 Cash c = PG_GETARG_CASH(0);
711 int16 s = PG_GETARG_INT16(1);
715 PG_RETURN_CASH(result);
719 * Multiply int2 by cash.
722 int2_mul_cash(PG_FUNCTION_ARGS)
724 int16 s = PG_GETARG_INT16(0);
725 Cash c = PG_GETARG_CASH(1);
729 PG_RETURN_CASH(result);
733 * Divide cash by int2.
737 cash_div_int2(PG_FUNCTION_ARGS)
739 Cash c = PG_GETARG_CASH(0);
740 int16 s = PG_GETARG_INT16(1);
745 (errcode(ERRCODE_DIVISION_BY_ZERO),
746 errmsg("division by zero")));
748 result = rint(c / s);
749 PG_RETURN_CASH(result);
753 * Return larger of two cash values.
756 cashlarger(PG_FUNCTION_ARGS)
758 Cash c1 = PG_GETARG_CASH(0);
759 Cash c2 = PG_GETARG_CASH(1);
762 result = (c1 > c2) ? c1 : c2;
764 PG_RETURN_CASH(result);
768 * Return smaller of two cash values.
771 cashsmaller(PG_FUNCTION_ARGS)
773 Cash c1 = PG_GETARG_CASH(0);
774 Cash c2 = PG_GETARG_CASH(1);
777 result = (c1 < c2) ? c1 : c2;
779 PG_RETURN_CASH(result);
783 * This converts a int4 as well but to a representation using words
784 * Obviously way North American centric - sorry
787 cash_words(PG_FUNCTION_ARGS)
789 Cash value = PG_GETARG_CASH(0);
801 /* work with positive numbers */
805 strcpy(buf, "minus ");
811 /* Now treat as unsigned, to avoid trouble at INT_MIN */
812 val = (uint64) value;
814 m0 = val % 100ll; /* cents */
815 m1 = (val / 100ll) % 1000; /* hundreds */
816 m2 = (val / 100000ll) % 1000; /* thousands */
817 m3 = val / 100000000ll % 1000; /* millions */
818 m4 = val / 100000000000ll % 1000; /* billions */
819 m5 = val / 100000000000000ll % 1000; /* trillions */
820 m6 = val / 100000000000000000ll % 1000; /* quadrillions */
824 strcat(buf, num_word(m6));
825 strcat(buf, " quadrillion ");
830 strcat(buf, num_word(m5));
831 strcat(buf, " trillion ");
836 strcat(buf, num_word(m4));
837 strcat(buf, " billion ");
842 strcat(buf, num_word(m3));
843 strcat(buf, " million ");
848 strcat(buf, num_word(m2));
849 strcat(buf, " thousand ");
853 strcat(buf, num_word(m1));
858 strcat(buf, (val / 100) == 1 ? " dollar and " : " dollars and ");
859 strcat(buf, num_word(m0));
860 strcat(buf, m0 == 1 ? " cent" : " cents");
862 /* capitalize output */
863 buf[0] = pg_toupper((unsigned char) buf[0]);
865 /* return as text datum */
866 PG_RETURN_TEXT_P(cstring_to_text(buf));