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.72 2007/08/21 03:14:36 tgl Exp $
26 #include "libpq/pqformat.h"
27 #include "utils/cash.h"
28 #include "utils/pg_locale.h"
32 #define TERMINATOR (CASH_BUFSZ - 1)
33 #define LAST_PAREN (TERMINATOR - 1)
34 #define LAST_DIGIT (LAST_PAREN - 1)
37 * Cash is a pass-by-ref SQL type, so we must pass and return pointers.
38 * These macros and support routine hide the pass-by-refness.
40 #define PG_GETARG_CASH(n) (* ((Cash *) PG_GETARG_POINTER(n)))
41 #define PG_RETURN_CASH(x) return CashGetDatum(x)
45 /*************************************************************************
47 ************************************************************************/
53 static const char *small[] = {
54 "zero", "one", "two", "three", "four", "five", "six", "seven",
55 "eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen",
56 "fifteen", "sixteen", "seventeen", "eighteen", "nineteen", "twenty",
57 "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety"
59 const char **big = small + 18;
62 /* deal with the simple cases first */
66 /* is it an even multiple of 100? */
69 sprintf(buf, "%s hundred", small[value / 100]);
76 /* is it an even multiple of 10 other than 10? */
77 if (value % 10 == 0 && tu > 10)
78 sprintf(buf, "%s hundred %s",
79 small[value / 100], big[tu / 10]);
81 sprintf(buf, "%s hundred and %s",
82 small[value / 100], small[tu]);
84 sprintf(buf, "%s hundred %s %s",
85 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 ssymbol = ((*lconvert->mon_thousands_sep != '\0') ? *lconvert->mon_thousands_sep : ',');
153 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
154 psymbol = ((*lconvert->positive_sign != '\0') ? *lconvert->positive_sign : '+');
155 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
158 printf("cashin- precision '%d'; decimal '%c'; thousands '%c'; currency '%s'; positive '%c'; negative '%s'\n",
159 fpoint, dsymbol, ssymbol, csymbol, psymbol, nsymbol);
162 /* we need to add all sorts of checking here. For now just */
163 /* strip all leading whitespace and any leading currency symbol */
164 while (isspace((unsigned char) *s))
166 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
167 s += strlen(csymbol);
170 printf("cashin- string is '%s'\n", s);
173 /* a leading minus or paren signifies a negative number */
174 /* again, better heuristics needed */
175 /* XXX - doesn't properly check for balanced parens - djmc */
176 if (strncmp(s, nsymbol, strlen(nsymbol)) == 0)
179 s += strlen(nsymbol);
181 printf("cashin- negative symbol; string is '%s'\n", s);
190 else if (*s == psymbol)
194 printf("cashin- string is '%s'\n", s);
197 while (isspace((unsigned char) *s))
199 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
200 s += strlen(csymbol);
203 printf("cashin- string is '%s'\n", s);
208 /* we look for digits as int8 as we have less */
209 /* than the required number of decimal places */
210 if (isdigit((unsigned char) *s) && dec < fpoint)
212 value = (value * 10) + *s - '0';
218 /* decimal point? then start counting fractions... */
219 else if (*s == dsymbol && !seen_dot)
224 /* "thousands" separator? then skip... */
225 else if (*s == ssymbol)
232 if (isdigit((unsigned char) *s) && *s >= '5')
235 /* adjust for less than required decimal places */
236 for (; dec < fpoint; dec++)
243 /* should only be trailing digits followed by whitespace or right paren */
244 while (isdigit((unsigned char) *s))
246 while (isspace((unsigned char) *s) || *s == ')')
251 (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
252 errmsg("invalid input syntax for type money: \"%s\"", str)));
254 result = value * sgn;
257 printf("cashin- result is %d\n", result);
260 PG_RETURN_CASH(result);
265 * Function to convert cash to a dollars and cents representation.
266 * XXX HACK This code appears to assume US conventions for
267 * positive-valued amounts. - tgl 97/04/14
270 cash_out(PG_FUNCTION_ARGS)
272 Cash value = PG_GETARG_CASH(0);
274 char buf[CASH_BUFSZ];
276 int count = LAST_DIGIT;
278 int comma_position = 0;
287 struct lconv *lconvert = PGLC_localeconv();
289 /* see comments about frac_digits in cash_in() */
290 points = lconvert->frac_digits;
291 if (points < 0 || points > 10)
292 points = 2; /* best guess in this case, I think */
295 * As with frac_digits, must apply a range check to mon_grouping to avoid
296 * being fooled by variant CHAR_MAX values.
298 mon_group = *lconvert->mon_grouping;
299 if (mon_group <= 0 || mon_group > 6)
302 comma = ((*lconvert->mon_thousands_sep != '\0') ? *lconvert->mon_thousands_sep : ',');
303 convention = lconvert->n_sign_posn;
304 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
305 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
306 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
308 point_pos = LAST_DIGIT - points;
310 /* allow more than three decimal points and separate them */
313 point_pos -= (points - 1) / mon_group;
314 comma_position = point_pos % (mon_group + 1);
317 /* we work with positive amounts and add the minus sign at the end */
324 /* allow for trailing negative strings */
325 MemSet(buf, ' ', CASH_BUFSZ);
326 buf[TERMINATOR] = buf[LAST_PAREN] = '\0';
328 while (value || count > (point_pos - 2))
330 if (points && count == point_pos)
331 buf[count--] = dsymbol;
332 else if (comma && count % (mon_group + 1) == comma_position)
333 buf[count--] = comma;
335 buf[count--] = ((uint64) value % 10) + '0';
336 value = ((uint64) value) / 10;
339 strncpy((buf + count - strlen(csymbol) + 1), csymbol, strlen(csymbol));
340 count -= strlen(csymbol) - 1;
342 if (buf[LAST_DIGIT] == ',')
343 buf[LAST_DIGIT] = buf[LAST_PAREN];
345 /* see if we need to signify negative amount */
348 result = palloc(CASH_BUFSZ + 2 - count + strlen(nsymbol));
350 /* Position code of 0 means use parens */
352 sprintf(result, "(%s)", buf + count);
353 else if (convention == 2)
354 sprintf(result, "%s%s", buf + count, nsymbol);
356 sprintf(result, "%s%s", nsymbol, buf + count);
360 result = palloc(CASH_BUFSZ + 2 - count);
361 strcpy(result, buf + count);
364 PG_RETURN_CSTRING(result);
368 * cash_recv - converts external binary format to cash
371 cash_recv(PG_FUNCTION_ARGS)
373 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
375 PG_RETURN_CASH((Cash) pq_getmsgint64(buf));
379 * cash_send - converts cash to binary format
382 cash_send(PG_FUNCTION_ARGS)
384 Cash arg1 = PG_GETARG_CASH(0);
387 pq_begintypsend(&buf);
388 pq_sendint64(&buf, arg1);
389 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
393 * Comparison functions
397 cash_eq(PG_FUNCTION_ARGS)
399 Cash c1 = PG_GETARG_CASH(0);
400 Cash c2 = PG_GETARG_CASH(1);
402 PG_RETURN_BOOL(c1 == c2);
406 cash_ne(PG_FUNCTION_ARGS)
408 Cash c1 = PG_GETARG_CASH(0);
409 Cash c2 = PG_GETARG_CASH(1);
411 PG_RETURN_BOOL(c1 != c2);
415 cash_lt(PG_FUNCTION_ARGS)
417 Cash c1 = PG_GETARG_CASH(0);
418 Cash c2 = PG_GETARG_CASH(1);
420 PG_RETURN_BOOL(c1 < c2);
424 cash_le(PG_FUNCTION_ARGS)
426 Cash c1 = PG_GETARG_CASH(0);
427 Cash c2 = PG_GETARG_CASH(1);
429 PG_RETURN_BOOL(c1 <= c2);
433 cash_gt(PG_FUNCTION_ARGS)
435 Cash c1 = PG_GETARG_CASH(0);
436 Cash c2 = PG_GETARG_CASH(1);
438 PG_RETURN_BOOL(c1 > c2);
442 cash_ge(PG_FUNCTION_ARGS)
444 Cash c1 = PG_GETARG_CASH(0);
445 Cash c2 = PG_GETARG_CASH(1);
447 PG_RETURN_BOOL(c1 >= c2);
451 cash_cmp(PG_FUNCTION_ARGS)
453 Cash c1 = PG_GETARG_CASH(0);
454 Cash c2 = PG_GETARG_CASH(1);
466 * Add two cash values.
469 cash_pl(PG_FUNCTION_ARGS)
471 Cash c1 = PG_GETARG_CASH(0);
472 Cash c2 = PG_GETARG_CASH(1);
477 PG_RETURN_CASH(result);
482 * Subtract two cash values.
485 cash_mi(PG_FUNCTION_ARGS)
487 Cash c1 = PG_GETARG_CASH(0);
488 Cash c2 = PG_GETARG_CASH(1);
493 PG_RETURN_CASH(result);
498 * Multiply cash by float8.
501 cash_mul_flt8(PG_FUNCTION_ARGS)
503 Cash c = PG_GETARG_CASH(0);
504 float8 f = PG_GETARG_FLOAT8(1);
508 PG_RETURN_CASH(result);
513 * Multiply float8 by cash.
516 flt8_mul_cash(PG_FUNCTION_ARGS)
518 float8 f = PG_GETARG_FLOAT8(0);
519 Cash c = PG_GETARG_CASH(1);
523 PG_RETURN_CASH(result);
528 * Divide cash by float8.
531 cash_div_flt8(PG_FUNCTION_ARGS)
533 Cash c = PG_GETARG_CASH(0);
534 float8 f = PG_GETARG_FLOAT8(1);
539 (errcode(ERRCODE_DIVISION_BY_ZERO),
540 errmsg("division by zero")));
542 result = rint(c / f);
543 PG_RETURN_CASH(result);
548 * Multiply cash by float4.
551 cash_mul_flt4(PG_FUNCTION_ARGS)
553 Cash c = PG_GETARG_CASH(0);
554 float4 f = PG_GETARG_FLOAT4(1);
558 PG_RETURN_CASH(result);
563 * Multiply float4 by cash.
566 flt4_mul_cash(PG_FUNCTION_ARGS)
568 float4 f = PG_GETARG_FLOAT4(0);
569 Cash c = PG_GETARG_CASH(1);
573 PG_RETURN_CASH(result);
578 * Divide cash by float4.
582 cash_div_flt4(PG_FUNCTION_ARGS)
584 Cash c = PG_GETARG_CASH(0);
585 float4 f = PG_GETARG_FLOAT4(1);
590 (errcode(ERRCODE_DIVISION_BY_ZERO),
591 errmsg("division by zero")));
593 result = rint(c / f);
594 PG_RETURN_CASH(result);
599 * Multiply cash by int8.
602 cash_mul_int8(PG_FUNCTION_ARGS)
604 Cash c = PG_GETARG_CASH(0);
605 int64 i = PG_GETARG_INT64(1);
609 PG_RETURN_CASH(result);
614 * Multiply int8 by cash.
617 int8_mul_cash(PG_FUNCTION_ARGS)
619 int64 i = PG_GETARG_INT64(0);
620 Cash c = PG_GETARG_CASH(1);
624 PG_RETURN_CASH(result);
628 * Divide cash by 8-byte integer.
631 cash_div_int8(PG_FUNCTION_ARGS)
633 Cash c = PG_GETARG_CASH(0);
634 int64 i = PG_GETARG_INT64(1);
639 (errcode(ERRCODE_DIVISION_BY_ZERO),
640 errmsg("division by zero")));
642 result = rint(c / i);
644 PG_RETURN_CASH(result);
649 * Multiply cash by int4.
652 cash_mul_int4(PG_FUNCTION_ARGS)
654 Cash c = PG_GETARG_CASH(0);
655 int64 i = PG_GETARG_INT64(1);
659 PG_RETURN_CASH(result);
664 * Multiply int4 by cash.
667 int4_mul_cash(PG_FUNCTION_ARGS)
669 int32 i = PG_GETARG_INT32(0);
670 Cash c = PG_GETARG_CASH(1);
674 PG_RETURN_CASH(result);
679 * Divide cash by 4-byte integer.
683 cash_div_int4(PG_FUNCTION_ARGS)
685 Cash c = PG_GETARG_CASH(0);
686 int64 i = PG_GETARG_INT64(1);
691 (errcode(ERRCODE_DIVISION_BY_ZERO),
692 errmsg("division by zero")));
694 result = rint(c / i);
696 PG_RETURN_CASH(result);
701 * Multiply cash by int2.
704 cash_mul_int2(PG_FUNCTION_ARGS)
706 Cash c = PG_GETARG_CASH(0);
707 int16 s = PG_GETARG_INT16(1);
711 PG_RETURN_CASH(result);
715 * Multiply int2 by cash.
718 int2_mul_cash(PG_FUNCTION_ARGS)
720 int16 s = PG_GETARG_INT16(0);
721 Cash c = PG_GETARG_CASH(1);
725 PG_RETURN_CASH(result);
729 * Divide cash by int2.
733 cash_div_int2(PG_FUNCTION_ARGS)
735 Cash c = PG_GETARG_CASH(0);
736 int16 s = PG_GETARG_INT16(1);
741 (errcode(ERRCODE_DIVISION_BY_ZERO),
742 errmsg("division by zero")));
744 result = rint(c / s);
745 PG_RETURN_CASH(result);
749 * Return larger of two cash values.
752 cashlarger(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 * Return smaller of two cash values.
767 cashsmaller(PG_FUNCTION_ARGS)
769 Cash c1 = PG_GETARG_CASH(0);
770 Cash c2 = PG_GETARG_CASH(1);
773 result = (c1 < c2) ? c1 : c2;
775 PG_RETURN_CASH(result);
779 * This converts a int4 as well but to a representation using words
780 * Obviously way North American centric - sorry
783 cash_words(PG_FUNCTION_ARGS)
785 Cash value = PG_GETARG_CASH(0);
798 /* work with positive numbers */
802 strcpy(buf, "minus ");
808 /* Now treat as unsigned, to avoid trouble at INT_MIN */
809 val = (uint64) value;
811 m0 = val % 100ll; /* cents */
812 m1 = (val / 100ll) % 1000; /* hundreds */
813 m2 = (val / 100000ll) % 1000; /* thousands */
814 m3 = val / 100000000ll % 1000; /* millions */
815 m4 = val / 100000000000ll % 1000; /* billions */
816 m5 = val / 100000000000000ll % 1000; /* trillions */
817 m6 = val / 100000000000000000ll % 1000; /* quadrillions */
821 strcat(buf, num_word(m6));
822 strcat(buf, " quadrillion ");
827 strcat(buf, num_word(m5));
828 strcat(buf, " trillion ");
833 strcat(buf, num_word(m4));
834 strcat(buf, " billion ");
839 strcat(buf, num_word(m3));
840 strcat(buf, " million ");
845 strcat(buf, num_word(m2));
846 strcat(buf, " thousand ");
850 strcat(buf, num_word(m1));
855 strcat(buf, (val / 100) == 1 ? " dollar and " : " dollars and ");
856 strcat(buf, num_word(m0));
857 strcat(buf, m0 == 1 ? " cent" : " cents");
859 /* capitalize output */
860 buf[0] = pg_toupper((unsigned char) buf[0]);
862 /* make a text type for output */
863 result = (text *) palloc(strlen(buf) + VARHDRSZ);
864 SET_VARSIZE(result, strlen(buf) + VARHDRSZ);
865 memcpy(VARDATA(result), buf, strlen(buf));
867 PG_RETURN_TEXT_P(result);