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.77 2007/11/24 16:18:48 momjian 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]);
89 /* is it an even multiple of 10 other than 10? */
90 if (value % 10 == 0 && tu > 10)
91 sprintf(buf, "%s", big[tu / 10]);
93 sprintf(buf, "%s", small[tu]);
95 sprintf(buf, "%s %s", big[tu / 10], small[tu % 10]);
102 CashGetDatum(Cash value)
104 Cash *result = (Cash *) palloc(sizeof(Cash));
107 return PointerGetDatum(result);
112 * Convert a string to a cash data type.
113 * Format is [$]###[,]###[.##]
114 * Examples: 123.45 $123.45 $123,456.78
118 cash_in(PG_FUNCTION_ARGS)
120 char *str = PG_GETARG_CSTRING(0);
134 struct lconv *lconvert = PGLC_localeconv();
137 * frac_digits will be CHAR_MAX in some locales, notably C. However, just
138 * testing for == CHAR_MAX is risky, because of compilers like gcc that
139 * "helpfully" let you alter the platform-standard definition of whether
140 * char is signed or not. If we are so unfortunate as to get compiled
141 * with a nonstandard -fsigned-char or -funsigned-char switch, then our
142 * idea of CHAR_MAX will not agree with libc's. The safest course is not
143 * to test for CHAR_MAX at all, but to impose a range check for plausible
144 * frac_digits values.
146 fpoint = lconvert->frac_digits;
147 if (fpoint < 0 || fpoint > 10)
148 fpoint = 2; /* best guess in this case, I think */
150 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
151 if (*lconvert->mon_thousands_sep != '\0')
152 ssymbol = *lconvert->mon_thousands_sep;
154 /* ssymbol should not equal dsymbol */
155 ssymbol = (dsymbol != ',') ? ',' : '.';
156 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
157 psymbol = ((*lconvert->positive_sign != '\0') ? *lconvert->positive_sign : '+');
158 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
161 printf("cashin- precision '%d'; decimal '%c'; thousands '%c'; currency '%s'; positive '%c'; negative '%s'\n",
162 fpoint, dsymbol, ssymbol, csymbol, psymbol, nsymbol);
165 /* we need to add all sorts of checking here. For now just */
166 /* strip all leading whitespace and any leading currency symbol */
167 while (isspace((unsigned char) *s))
169 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
170 s += strlen(csymbol);
173 printf("cashin- string is '%s'\n", s);
176 /* a leading minus or paren signifies a negative number */
177 /* again, better heuristics needed */
178 /* XXX - doesn't properly check for balanced parens - djmc */
179 if (strncmp(s, nsymbol, strlen(nsymbol)) == 0)
182 s += strlen(nsymbol);
184 printf("cashin- negative symbol; string is '%s'\n", s);
192 else if (*s == psymbol)
196 printf("cashin- string is '%s'\n", s);
199 while (isspace((unsigned char) *s))
201 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
202 s += strlen(csymbol);
205 printf("cashin- string is '%s'\n", s);
210 /* we look for digits as int8 as we have less */
211 /* than the required number of decimal places */
212 if (isdigit((unsigned char) *s) && dec < fpoint)
214 value = (value * 10) + *s - '0';
220 /* decimal point? then start counting fractions... */
221 else if (*s == dsymbol && !seen_dot)
226 /* not "thousands" separator? */
227 else if (*s != ssymbol)
230 if (isdigit((unsigned char) *s) && *s >= '5')
233 /* adjust for less than required decimal places */
234 for (; dec < fpoint; dec++)
241 /* should only be trailing digits followed by whitespace or right paren */
242 while (isdigit((unsigned char) *s))
244 while (isspace((unsigned char) *s) || *s == ')')
249 (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
250 errmsg("invalid input syntax for type money: \"%s\"", str)));
252 result = value * sgn;
255 printf("cashin- result is %d\n", result);
258 PG_RETURN_CASH(result);
263 * Function to convert cash to a dollars and cents representation.
264 * XXX HACK This code appears to assume US conventions for
265 * positive-valued amounts. - tgl 97/04/14
268 cash_out(PG_FUNCTION_ARGS)
270 Cash value = PG_GETARG_CASH(0);
272 char buf[CASH_BUFSZ];
274 int count = LAST_DIGIT;
276 int ssymbol_position = 0;
285 struct lconv *lconvert = PGLC_localeconv();
287 /* see comments about frac_digits in cash_in() */
288 points = lconvert->frac_digits;
289 if (points < 0 || points > 10)
290 points = 2; /* best guess in this case, I think */
293 * As with frac_digits, must apply a range check to mon_grouping to avoid
294 * being fooled by variant CHAR_MAX values.
296 mon_group = *lconvert->mon_grouping;
297 if (mon_group <= 0 || mon_group > 6)
300 convention = lconvert->n_sign_posn;
301 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
302 if (*lconvert->mon_thousands_sep != '\0')
303 ssymbol = *lconvert->mon_thousands_sep;
305 /* ssymbol should not equal dsymbol */
306 ssymbol = (dsymbol != ',') ? ',' : '.';
307 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
308 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
310 point_pos = LAST_DIGIT - points;
312 point_pos -= (points - 1) / mon_group;
313 ssymbol_position = point_pos % (mon_group + 1);
315 /* we work with positive amounts and add the minus sign at the end */
322 /* allow for trailing negative strings */
323 MemSet(buf, ' ', CASH_BUFSZ);
324 buf[TERMINATOR] = buf[LAST_PAREN] = '\0';
326 while (value || count > (point_pos - 2))
328 if (points && count == point_pos)
329 buf[count--] = dsymbol;
330 else if (ssymbol && count % (mon_group + 1) == ssymbol_position)
331 buf[count--] = ssymbol;
333 buf[count--] = ((uint64) value % 10) + '0';
334 value = ((uint64) value) / 10;
337 strncpy((buf + count - strlen(csymbol) + 1), csymbol, strlen(csymbol));
338 count -= strlen(csymbol) - 1;
341 * If points == 0 and the number of digits % mon_group == 0,
342 * the code above adds a trailing ssymbol on the far right,
345 if (buf[LAST_DIGIT] == ssymbol)
346 buf[LAST_DIGIT] = '\0';
348 /* see if we need to signify negative amount */
351 result = palloc(CASH_BUFSZ + 2 - count + strlen(nsymbol));
353 /* Position code of 0 means use parens */
355 sprintf(result, "(%s)", buf + count);
356 else if (convention == 2)
357 sprintf(result, "%s%s", buf + count, nsymbol);
359 sprintf(result, "%s%s", nsymbol, buf + count);
363 result = palloc(CASH_BUFSZ + 2 - count);
364 strcpy(result, buf + count);
367 PG_RETURN_CSTRING(result);
371 * cash_recv - converts external binary format to cash
374 cash_recv(PG_FUNCTION_ARGS)
376 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
378 PG_RETURN_CASH((Cash) pq_getmsgint64(buf));
382 * cash_send - converts cash to binary format
385 cash_send(PG_FUNCTION_ARGS)
387 Cash arg1 = PG_GETARG_CASH(0);
390 pq_begintypsend(&buf);
391 pq_sendint64(&buf, arg1);
392 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
396 * Comparison functions
400 cash_eq(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_ne(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_lt(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_le(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_gt(PG_FUNCTION_ARGS)
438 Cash c1 = PG_GETARG_CASH(0);
439 Cash c2 = PG_GETARG_CASH(1);
441 PG_RETURN_BOOL(c1 > c2);
445 cash_ge(PG_FUNCTION_ARGS)
447 Cash c1 = PG_GETARG_CASH(0);
448 Cash c2 = PG_GETARG_CASH(1);
450 PG_RETURN_BOOL(c1 >= c2);
454 cash_cmp(PG_FUNCTION_ARGS)
456 Cash c1 = PG_GETARG_CASH(0);
457 Cash c2 = PG_GETARG_CASH(1);
469 * Add two cash values.
472 cash_pl(PG_FUNCTION_ARGS)
474 Cash c1 = PG_GETARG_CASH(0);
475 Cash c2 = PG_GETARG_CASH(1);
480 PG_RETURN_CASH(result);
485 * Subtract two cash values.
488 cash_mi(PG_FUNCTION_ARGS)
490 Cash c1 = PG_GETARG_CASH(0);
491 Cash c2 = PG_GETARG_CASH(1);
496 PG_RETURN_CASH(result);
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);
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 /* make a text type for output */
866 result = (text *) palloc(strlen(buf) + VARHDRSZ);
867 SET_VARSIZE(result, strlen(buf) + VARHDRSZ);
868 memcpy(VARDATA(result), buf, strlen(buf));
870 PG_RETURN_TEXT_P(result);