3 * Written by D'Arcy J.M. Cain
5 * Functions to allow input and output of money normally but store
6 * and handle it as int4s
8 * A slightly modified version of this file and a discussion of the
9 * workings can be found in the book "Software Solutions in C" by
10 * Dale Schumacher, Academic Press, ISBN: 0-12-632360-7.
12 * $PostgreSQL: pgsql/src/backend/utils/adt/cash.c,v 1.68 2006/07/14 14:52:23 momjian Exp $
22 #include "libpq/pqformat.h"
23 #include "utils/cash.h"
24 #include "utils/pg_locale.h"
27 static const char *num_word(Cash value);
29 /* when we go to 64 bit values we will have to modify this */
32 #define TERMINATOR (CASH_BUFSZ - 1)
33 #define LAST_PAREN (TERMINATOR - 1)
34 #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)
45 CashGetDatum(Cash value)
47 Cash *result = (Cash *) palloc(sizeof(Cash));
50 return PointerGetDatum(result);
55 * Convert a string to a cash data type.
56 * Format is [$]###[,]###[.##]
57 * Examples: 123.45 $123.45 $123,456.78
59 * This is currently implemented as a 32-bit integer.
60 * XXX HACK It looks as though some of the symbols for
61 * monetary values returned by localeconv() can be multiple
62 * bytes/characters. This code assumes one byte only. - tgl 97/04/14
63 * XXX UNHACK Allow the currency symbol to be multibyte.
67 cash_in(PG_FUNCTION_ARGS)
69 char *str = PG_GETARG_CSTRING(0);
83 struct lconv *lconvert = PGLC_localeconv();
86 * frac_digits will be CHAR_MAX in some locales, notably C. However, just
87 * testing for == CHAR_MAX is risky, because of compilers like gcc that
88 * "helpfully" let you alter the platform-standard definition of whether
89 * char is signed or not. If we are so unfortunate as to get compiled
90 * with a nonstandard -fsigned-char or -funsigned-char switch, then our
91 * idea of CHAR_MAX will not agree with libc's. The safest course is not
92 * to test for CHAR_MAX at all, but to impose a range check for plausible
95 fpoint = lconvert->frac_digits;
96 if (fpoint < 0 || fpoint > 10)
97 fpoint = 2; /* best guess in this case, I think */
99 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
100 ssymbol = ((*lconvert->mon_thousands_sep != '\0') ? *lconvert->mon_thousands_sep : ',');
101 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
102 psymbol = ((*lconvert->positive_sign != '\0') ? *lconvert->positive_sign : '+');
103 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
106 printf("cashin- precision '%d'; decimal '%c'; thousands '%c'; currency '%s'; positive '%c'; negative '%s'\n",
107 fpoint, dsymbol, ssymbol, csymbol, psymbol, nsymbol);
110 /* we need to add all sorts of checking here. For now just */
111 /* strip all leading whitespace and any leading currency symbol */
112 while (isspace((unsigned char) *s))
114 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
115 s += strlen(csymbol);
118 printf("cashin- string is '%s'\n", s);
121 /* a leading minus or paren signifies a negative number */
122 /* again, better heuristics needed */
123 if (strncmp(s, nsymbol, strlen(nsymbol)) == 0)
126 s += strlen(nsymbol);
128 printf("cashin- negative symbol; string is '%s'\n", s);
137 else if (*s == psymbol)
141 printf("cashin- string is '%s'\n", s);
144 while (isspace((unsigned char) *s))
146 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
147 s += strlen(csymbol);
150 printf("cashin- string is '%s'\n", s);
155 /* we look for digits as int4 as we have less */
156 /* than the required number of decimal places */
157 if (isdigit((unsigned char) *s) && dec < fpoint)
159 value = (value * 10) + *s - '0';
164 /* decimal point? then start counting fractions... */
166 else if (*s == dsymbol && !seen_dot)
170 /* "thousands" separator? then skip... */
172 else if (*s == ssymbol)
179 if (isdigit((unsigned char) *s) && *s >= '5')
182 /* adjust for less than required decimal places */
183 for (; dec < fpoint; dec++)
190 while (isspace((unsigned char) *s) || *s == '0' || *s == ')')
195 (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
196 errmsg("invalid input syntax for type money: \"%s\"", str)));
198 result = value * sgn;
201 printf("cashin- result is %d\n", result);
204 PG_RETURN_CASH(result);
209 * Function to convert cash to a dollars and cents representation.
210 * XXX HACK This code appears to assume US conventions for
211 * positive-valued amounts. - tgl 97/04/14
214 cash_out(PG_FUNCTION_ARGS)
216 Cash value = PG_GETARG_CASH(0);
218 char buf[CASH_BUFSZ];
220 int count = LAST_DIGIT;
222 int comma_position = 0;
231 struct lconv *lconvert = PGLC_localeconv();
233 /* see comments about frac_digits in cash_in() */
234 points = lconvert->frac_digits;
235 if (points < 0 || points > 10)
236 points = 2; /* best guess in this case, I think */
239 * As with frac_digits, must apply a range check to mon_grouping to avoid
240 * being fooled by variant CHAR_MAX values.
242 mon_group = *lconvert->mon_grouping;
243 if (mon_group <= 0 || mon_group > 6)
246 comma = ((*lconvert->mon_thousands_sep != '\0') ? *lconvert->mon_thousands_sep : ',');
247 convention = lconvert->n_sign_posn;
248 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
249 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
250 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
252 point_pos = LAST_DIGIT - points;
254 /* allow more than three decimal points and separate them */
257 point_pos -= (points - 1) / mon_group;
258 comma_position = point_pos % (mon_group + 1);
261 /* we work with positive amounts and add the minus sign at the end */
268 /* allow for trailing negative strings */
269 MemSet(buf, ' ', CASH_BUFSZ);
270 buf[TERMINATOR] = buf[LAST_PAREN] = '\0';
272 while (value || count > (point_pos - 2))
274 if (points && count == point_pos)
275 buf[count--] = dsymbol;
276 else if (comma && count % (mon_group + 1) == comma_position)
277 buf[count--] = comma;
279 buf[count--] = ((unsigned int) value % 10) + '0';
280 value = ((unsigned int) value) / 10;
283 strncpy((buf + count - strlen(csymbol) + 1), csymbol, strlen(csymbol));
284 count -= strlen(csymbol) - 1;
286 if (buf[LAST_DIGIT] == ',')
287 buf[LAST_DIGIT] = buf[LAST_PAREN];
289 /* see if we need to signify negative amount */
292 result = palloc(CASH_BUFSZ + 2 - count + strlen(nsymbol));
294 /* Position code of 0 means use parens */
296 sprintf(result, "(%s)", buf + count);
297 else if (convention == 2)
298 sprintf(result, "%s%s", buf + count, nsymbol);
300 sprintf(result, "%s%s", nsymbol, buf + count);
304 result = palloc(CASH_BUFSZ + 2 - count);
305 strcpy(result, buf + count);
308 PG_RETURN_CSTRING(result);
312 * cash_recv - converts external binary format to cash
315 cash_recv(PG_FUNCTION_ARGS)
317 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
319 PG_RETURN_CASH((Cash) pq_getmsgint(buf, sizeof(Cash)));
323 * cash_send - converts cash to binary format
326 cash_send(PG_FUNCTION_ARGS)
328 Cash arg1 = PG_GETARG_CASH(0);
331 pq_begintypsend(&buf);
332 pq_sendint(&buf, arg1, sizeof(Cash));
333 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
337 * Comparison functions
341 cash_eq(PG_FUNCTION_ARGS)
343 Cash c1 = PG_GETARG_CASH(0);
344 Cash c2 = PG_GETARG_CASH(1);
346 PG_RETURN_BOOL(c1 == c2);
350 cash_ne(PG_FUNCTION_ARGS)
352 Cash c1 = PG_GETARG_CASH(0);
353 Cash c2 = PG_GETARG_CASH(1);
355 PG_RETURN_BOOL(c1 != c2);
359 cash_lt(PG_FUNCTION_ARGS)
361 Cash c1 = PG_GETARG_CASH(0);
362 Cash c2 = PG_GETARG_CASH(1);
364 PG_RETURN_BOOL(c1 < c2);
368 cash_le(PG_FUNCTION_ARGS)
370 Cash c1 = PG_GETARG_CASH(0);
371 Cash c2 = PG_GETARG_CASH(1);
373 PG_RETURN_BOOL(c1 <= c2);
377 cash_gt(PG_FUNCTION_ARGS)
379 Cash c1 = PG_GETARG_CASH(0);
380 Cash c2 = PG_GETARG_CASH(1);
382 PG_RETURN_BOOL(c1 > c2);
386 cash_ge(PG_FUNCTION_ARGS)
388 Cash c1 = PG_GETARG_CASH(0);
389 Cash c2 = PG_GETARG_CASH(1);
391 PG_RETURN_BOOL(c1 >= c2);
395 cash_cmp(PG_FUNCTION_ARGS)
397 Cash c1 = PG_GETARG_CASH(0);
398 Cash c2 = PG_GETARG_CASH(1);
410 * Add two cash values.
413 cash_pl(PG_FUNCTION_ARGS)
415 Cash c1 = PG_GETARG_CASH(0);
416 Cash c2 = PG_GETARG_CASH(1);
421 PG_RETURN_CASH(result);
426 * Subtract two cash values.
429 cash_mi(PG_FUNCTION_ARGS)
431 Cash c1 = PG_GETARG_CASH(0);
432 Cash c2 = PG_GETARG_CASH(1);
437 PG_RETURN_CASH(result);
442 * Multiply cash by float8.
445 cash_mul_flt8(PG_FUNCTION_ARGS)
447 Cash c = PG_GETARG_CASH(0);
448 float8 f = PG_GETARG_FLOAT8(1);
452 PG_RETURN_CASH(result);
457 * Multiply float8 by cash.
460 flt8_mul_cash(PG_FUNCTION_ARGS)
462 float8 f = PG_GETARG_FLOAT8(0);
463 Cash c = PG_GETARG_CASH(1);
467 PG_RETURN_CASH(result);
472 * Divide cash by float8.
474 * XXX Don't know if rounding or truncating is correct behavior.
475 * Round for now. - tgl 97/04/15
478 cash_div_flt8(PG_FUNCTION_ARGS)
480 Cash c = PG_GETARG_CASH(0);
481 float8 f = PG_GETARG_FLOAT8(1);
486 (errcode(ERRCODE_DIVISION_BY_ZERO),
487 errmsg("division by zero")));
489 result = rint(c / f);
490 PG_RETURN_CASH(result);
494 * Multiply cash by float4.
497 cash_mul_flt4(PG_FUNCTION_ARGS)
499 Cash c = PG_GETARG_CASH(0);
500 float4 f = PG_GETARG_FLOAT4(1);
504 PG_RETURN_CASH(result);
509 * Multiply float4 by cash.
512 flt4_mul_cash(PG_FUNCTION_ARGS)
514 float4 f = PG_GETARG_FLOAT4(0);
515 Cash c = PG_GETARG_CASH(1);
519 PG_RETURN_CASH(result);
524 * Divide cash by float4.
526 * XXX Don't know if rounding or truncating is correct behavior.
527 * Round for now. - tgl 97/04/15
530 cash_div_flt4(PG_FUNCTION_ARGS)
532 Cash c = PG_GETARG_CASH(0);
533 float4 f = PG_GETARG_FLOAT4(1);
538 (errcode(ERRCODE_DIVISION_BY_ZERO),
539 errmsg("division by zero")));
541 result = rint(c / f);
542 PG_RETURN_CASH(result);
547 * Multiply cash by int4.
550 cash_mul_int4(PG_FUNCTION_ARGS)
552 Cash c = PG_GETARG_CASH(0);
553 int32 i = PG_GETARG_INT32(1);
557 PG_RETURN_CASH(result);
562 * Multiply int4 by cash.
565 int4_mul_cash(PG_FUNCTION_ARGS)
567 int32 i = PG_GETARG_INT32(0);
568 Cash c = PG_GETARG_CASH(1);
572 PG_RETURN_CASH(result);
577 * Divide cash by 4-byte integer.
579 * XXX Don't know if rounding or truncating is correct behavior.
580 * Round for now. - tgl 97/04/15
583 cash_div_int4(PG_FUNCTION_ARGS)
585 Cash c = PG_GETARG_CASH(0);
586 int32 i = PG_GETARG_INT32(1);
591 (errcode(ERRCODE_DIVISION_BY_ZERO),
592 errmsg("division by zero")));
594 result = rint(c / i);
596 PG_RETURN_CASH(result);
601 * Multiply cash by int2.
604 cash_mul_int2(PG_FUNCTION_ARGS)
606 Cash c = PG_GETARG_CASH(0);
607 int16 s = PG_GETARG_INT16(1);
611 PG_RETURN_CASH(result);
615 * Multiply int2 by cash.
618 int2_mul_cash(PG_FUNCTION_ARGS)
620 int16 s = PG_GETARG_INT16(0);
621 Cash c = PG_GETARG_CASH(1);
625 PG_RETURN_CASH(result);
629 * Divide cash by int2.
631 * XXX Don't know if rounding or truncating is correct behavior.
632 * Round for now. - tgl 97/04/15
635 cash_div_int2(PG_FUNCTION_ARGS)
637 Cash c = PG_GETARG_CASH(0);
638 int16 s = PG_GETARG_INT16(1);
643 (errcode(ERRCODE_DIVISION_BY_ZERO),
644 errmsg("division by zero")));
646 result = rint(c / s);
647 PG_RETURN_CASH(result);
651 * Return larger of two cash values.
654 cashlarger(PG_FUNCTION_ARGS)
656 Cash c1 = PG_GETARG_CASH(0);
657 Cash c2 = PG_GETARG_CASH(1);
660 result = (c1 > c2) ? c1 : c2;
662 PG_RETURN_CASH(result);
666 * Return smaller of two cash values.
669 cashsmaller(PG_FUNCTION_ARGS)
671 Cash c1 = PG_GETARG_CASH(0);
672 Cash c2 = PG_GETARG_CASH(1);
675 result = (c1 < c2) ? c1 : c2;
677 PG_RETURN_CASH(result);
682 * This converts a int4 as well but to a representation using words
683 * Obviously way North American centric - sorry
686 cash_words(PG_FUNCTION_ARGS)
688 Cash value = PG_GETARG_CASH(0);
698 /* work with positive numbers */
702 strcpy(buf, "minus ");
708 /* Now treat as unsigned, to avoid trouble at INT_MIN */
709 val = (unsigned int) value;
711 m0 = val % 100; /* cents */
712 m1 = (val / 100) % 1000; /* hundreds */
713 m2 = (val / 100000) % 1000; /* thousands */
714 m3 = val / 100000000 % 1000; /* millions */
718 strcat(buf, num_word(m3));
719 strcat(buf, " million ");
724 strcat(buf, num_word(m2));
725 strcat(buf, " thousand ");
729 strcat(buf, num_word(m1));
734 strcat(buf, (val / 100) == 1 ? " dollar and " : " dollars and ");
735 strcat(buf, num_word(m0));
736 strcat(buf, m0 == 1 ? " cent" : " cents");
738 /* capitalize output */
739 buf[0] = pg_toupper((unsigned char) buf[0]);
741 /* make a text type for output */
742 result = (text *) palloc(strlen(buf) + VARHDRSZ);
743 VARATT_SIZEP(result) = strlen(buf) + VARHDRSZ;
744 memcpy(VARDATA(result), buf, strlen(buf));
746 PG_RETURN_TEXT_P(result);
750 /*************************************************************************
752 ************************************************************************/
757 static char buf[128];
758 static const char *small[] = {
759 "zero", "one", "two", "three", "four", "five", "six", "seven",
760 "eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen",
761 "fifteen", "sixteen", "seventeen", "eighteen", "nineteen", "twenty",
762 "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety"
764 const char **big = small + 18;
765 int tu = value % 100;
767 /* deal with the simple cases first */
771 /* is it an even multiple of 100? */
774 sprintf(buf, "%s hundred", small[value / 100]);
781 /* is it an even multiple of 10 other than 10? */
782 if (value % 10 == 0 && tu > 10)
783 sprintf(buf, "%s hundred %s",
784 small[value / 100], big[tu / 10]);
786 sprintf(buf, "%s hundred and %s",
787 small[value / 100], small[tu]);
789 sprintf(buf, "%s hundred %s %s",
790 small[value / 100], big[tu / 10], small[tu % 10]);
795 /* is it an even multiple of 10 other than 10? */
796 if (value % 10 == 0 && tu > 10)
797 sprintf(buf, "%s", big[tu / 10]);
799 sprintf(buf, "%s", small[tu]);
801 sprintf(buf, "%s %s", big[tu / 10], small[tu % 10]);