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.63 2004/05/07 00:24:58 tgl Exp $
22 #include "libpq/pqformat.h"
23 #include "miscadmin.h"
24 #include "utils/builtins.h"
25 #include "utils/cash.h"
26 #include "utils/pg_locale.h"
29 static const char *num_word(Cash value);
31 /* when we go to 64 bit values we will have to modify this */
34 #define TERMINATOR (CASH_BUFSZ - 1)
35 #define LAST_PAREN (TERMINATOR - 1)
36 #define LAST_DIGIT (LAST_PAREN - 1)
40 * Cash is a pass-by-ref SQL type, so we must pass and return pointers.
41 * These macros and support routine hide the pass-by-refness.
43 #define PG_GETARG_CASH(n) (* ((Cash *) PG_GETARG_POINTER(n)))
44 #define PG_RETURN_CASH(x) return CashGetDatum(x)
47 CashGetDatum(Cash value)
49 Cash *result = (Cash *) palloc(sizeof(Cash));
52 return PointerGetDatum(result);
57 * Convert a string to a cash data type.
58 * Format is [$]###[,]###[.##]
59 * Examples: 123.45 $123.45 $123,456.78
61 * This is currently implemented as a 32-bit integer.
62 * XXX HACK It looks as though some of the symbols for
63 * monetary values returned by localeconv() can be multiple
64 * bytes/characters. This code assumes one byte only. - tgl 97/04/14
65 * XXX UNHACK Allow the currency symbol to be multibyte.
69 cash_in(PG_FUNCTION_ARGS)
71 char *str = PG_GETARG_CSTRING(0);
85 struct lconv *lconvert = PGLC_localeconv();
88 * frac_digits will be CHAR_MAX in some locales, notably C. However,
89 * just testing for == CHAR_MAX is risky, because of compilers like
90 * gcc that "helpfully" let you alter the platform-standard definition
91 * of whether char is signed or not. If we are so unfortunate as to
92 * get compiled with a nonstandard -fsigned-char or -funsigned-char
93 * switch, then our idea of CHAR_MAX will not agree with libc's. The
94 * safest course is not to test for CHAR_MAX at all, but to impose a
95 * range check for plausible frac_digits values.
97 fpoint = lconvert->frac_digits;
98 if (fpoint < 0 || fpoint > 10)
99 fpoint = 2; /* best guess in this case, I think */
101 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
102 ssymbol = ((*lconvert->mon_thousands_sep != '\0') ? *lconvert->mon_thousands_sep : ',');
103 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
104 psymbol = ((*lconvert->positive_sign != '\0') ? *lconvert->positive_sign : '+');
105 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
108 printf("cashin- precision '%d'; decimal '%c'; thousands '%c'; currency '%s'; positive '%c'; negative '%s'\n",
109 fpoint, dsymbol, ssymbol, csymbol, psymbol, nsymbol);
112 /* we need to add all sorts of checking here. For now just */
113 /* strip all leading whitespace and any leading currency symbol */
114 while (isspace((unsigned char) *s))
116 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
117 s += strlen(csymbol);
120 printf("cashin- string is '%s'\n", s);
123 /* a leading minus or paren signifies a negative number */
124 /* again, better heuristics needed */
125 if (strncmp(s, nsymbol, strlen(nsymbol)) == 0)
128 s += strlen(nsymbol);
130 printf("cashin- negative symbol; string is '%s'\n", s);
139 else if (*s == psymbol)
143 printf("cashin- string is '%s'\n", s);
146 while (isspace((unsigned char) *s))
148 if (strncmp(s, csymbol, strlen(csymbol)) == 0)
149 s += strlen(csymbol);
152 printf("cashin- string is '%s'\n", s);
157 /* we look for digits as int4 as we have less */
158 /* than the required number of decimal places */
159 if (isdigit((unsigned char) *s) && dec < fpoint)
161 value = (value * 10) + *s - '0';
166 /* decimal point? then start counting fractions... */
168 else if (*s == dsymbol && !seen_dot)
172 /* "thousands" separator? then skip... */
174 else if (*s == ssymbol)
181 if (isdigit((unsigned char) *s) && *s >= '5')
184 /* adjust for less than required decimal places */
185 for (; dec < fpoint; dec++)
192 while (isspace((unsigned char) *s) || *s == '0' || *s == ')')
197 (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
198 errmsg("invalid input syntax for type money: \"%s\"", str)));
200 result = (value * sgn);
203 printf("cashin- result is %d\n", result);
206 PG_RETURN_CASH(result);
211 * Function to convert cash to a dollars and cents representation.
212 * XXX HACK This code appears to assume US conventions for
213 * positive-valued amounts. - tgl 97/04/14
216 cash_out(PG_FUNCTION_ARGS)
218 Cash value = PG_GETARG_CASH(0);
220 char buf[CASH_BUFSZ];
222 int count = LAST_DIGIT;
224 int comma_position = 0;
233 struct lconv *lconvert = PGLC_localeconv();
235 /* see comments about frac_digits in cash_in() */
236 points = lconvert->frac_digits;
237 if (points < 0 || points > 10)
238 points = 2; /* best guess in this case, I think */
241 * As with frac_digits, must apply a range check to mon_grouping to
242 * avoid being fooled by variant CHAR_MAX values.
244 mon_group = *lconvert->mon_grouping;
245 if (mon_group <= 0 || mon_group > 6)
248 comma = ((*lconvert->mon_thousands_sep != '\0') ? *lconvert->mon_thousands_sep : ',');
249 convention = lconvert->n_sign_posn;
250 dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
251 csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
252 nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
254 point_pos = LAST_DIGIT - points;
256 /* allow more than three decimal points and separate them */
259 point_pos -= (points - 1) / mon_group;
260 comma_position = point_pos % (mon_group + 1);
263 /* we work with positive amounts and add the minus sign at the end */
270 /* allow for trailing negative strings */
271 MemSet(buf, ' ', CASH_BUFSZ);
272 buf[TERMINATOR] = buf[LAST_PAREN] = '\0';
274 while (value || count > (point_pos - 2))
276 if (points && count == point_pos)
277 buf[count--] = dsymbol;
278 else if (comma && count % (mon_group + 1) == comma_position)
279 buf[count--] = comma;
281 buf[count--] = ((unsigned int) value % 10) + '0';
282 value = ((unsigned int) value) / 10;
285 strncpy((buf + count - strlen(csymbol) + 1), csymbol, strlen(csymbol));
286 count -= strlen(csymbol) - 1;
288 if (buf[LAST_DIGIT] == ',')
289 buf[LAST_DIGIT] = buf[LAST_PAREN];
291 /* see if we need to signify negative amount */
294 if (!PointerIsValid(result = palloc(CASH_BUFSZ + 2 - count + strlen(nsymbol))))
296 (errcode(ERRCODE_OUT_OF_MEMORY),
297 errmsg("out of memory")));
299 /* Position code of 0 means use parens */
301 sprintf(result, "(%s)", buf + count);
302 else if (convention == 2)
303 sprintf(result, "%s%s", buf + count, nsymbol);
305 sprintf(result, "%s%s", nsymbol, buf + count);
309 if (!PointerIsValid(result = palloc(CASH_BUFSZ + 2 - count)))
311 (errcode(ERRCODE_OUT_OF_MEMORY),
312 errmsg("out of memory")));
314 strcpy(result, buf + count);
317 PG_RETURN_CSTRING(result);
321 * cash_recv - converts external binary format to cash
324 cash_recv(PG_FUNCTION_ARGS)
326 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
328 PG_RETURN_CASH((Cash) pq_getmsgint(buf, sizeof(Cash)));
332 * cash_send - converts cash to binary format
335 cash_send(PG_FUNCTION_ARGS)
337 Cash arg1 = PG_GETARG_CASH(0);
340 pq_begintypsend(&buf);
341 pq_sendint(&buf, arg1, sizeof(Cash));
342 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
346 * Comparison functions
350 cash_eq(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_ne(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_lt(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_le(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_gt(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_ge(PG_FUNCTION_ARGS)
397 Cash c1 = PG_GETARG_CASH(0);
398 Cash c2 = PG_GETARG_CASH(1);
400 PG_RETURN_BOOL(c1 >= c2);
404 cash_cmp(PG_FUNCTION_ARGS)
406 Cash c1 = PG_GETARG_CASH(0);
407 Cash c2 = PG_GETARG_CASH(1);
419 * Add two cash values.
422 cash_pl(PG_FUNCTION_ARGS)
424 Cash c1 = PG_GETARG_CASH(0);
425 Cash c2 = PG_GETARG_CASH(1);
430 PG_RETURN_CASH(result);
435 * Subtract two cash values.
438 cash_mi(PG_FUNCTION_ARGS)
440 Cash c1 = PG_GETARG_CASH(0);
441 Cash c2 = PG_GETARG_CASH(1);
446 PG_RETURN_CASH(result);
451 * Multiply cash by float8.
454 cash_mul_flt8(PG_FUNCTION_ARGS)
456 Cash c = PG_GETARG_CASH(0);
457 float8 f = PG_GETARG_FLOAT8(1);
461 PG_RETURN_CASH(result);
466 * Multiply float8 by cash.
469 flt8_mul_cash(PG_FUNCTION_ARGS)
471 float8 f = PG_GETARG_FLOAT8(0);
472 Cash c = PG_GETARG_CASH(1);
476 PG_RETURN_CASH(result);
481 * Divide cash by float8.
483 * XXX Don't know if rounding or truncating is correct behavior.
484 * Round for now. - tgl 97/04/15
487 cash_div_flt8(PG_FUNCTION_ARGS)
489 Cash c = PG_GETARG_CASH(0);
490 float8 f = PG_GETARG_FLOAT8(1);
495 (errcode(ERRCODE_DIVISION_BY_ZERO),
496 errmsg("division by zero")));
498 result = rint(c / f);
499 PG_RETURN_CASH(result);
503 * Multiply cash by float4.
506 cash_mul_flt4(PG_FUNCTION_ARGS)
508 Cash c = PG_GETARG_CASH(0);
509 float4 f = PG_GETARG_FLOAT4(1);
513 PG_RETURN_CASH(result);
518 * Multiply float4 by cash.
521 flt4_mul_cash(PG_FUNCTION_ARGS)
523 float4 f = PG_GETARG_FLOAT4(0);
524 Cash c = PG_GETARG_CASH(1);
528 PG_RETURN_CASH(result);
533 * Divide cash by float4.
535 * XXX Don't know if rounding or truncating is correct behavior.
536 * Round for now. - tgl 97/04/15
539 cash_div_flt4(PG_FUNCTION_ARGS)
541 Cash c = PG_GETARG_CASH(0);
542 float4 f = PG_GETARG_FLOAT4(1);
547 (errcode(ERRCODE_DIVISION_BY_ZERO),
548 errmsg("division by zero")));
550 result = rint(c / f);
551 PG_RETURN_CASH(result);
556 * Multiply cash by int4.
559 cash_mul_int4(PG_FUNCTION_ARGS)
561 Cash c = PG_GETARG_CASH(0);
562 int32 i = PG_GETARG_INT32(1);
566 PG_RETURN_CASH(result);
571 * Multiply int4 by cash.
574 int4_mul_cash(PG_FUNCTION_ARGS)
576 int32 i = PG_GETARG_INT32(0);
577 Cash c = PG_GETARG_CASH(1);
581 PG_RETURN_CASH(result);
586 * Divide cash by 4-byte integer.
588 * XXX Don't know if rounding or truncating is correct behavior.
589 * Round for now. - tgl 97/04/15
592 cash_div_int4(PG_FUNCTION_ARGS)
594 Cash c = PG_GETARG_CASH(0);
595 int32 i = PG_GETARG_INT32(1);
600 (errcode(ERRCODE_DIVISION_BY_ZERO),
601 errmsg("division by zero")));
603 result = rint(c / i);
605 PG_RETURN_CASH(result);
610 * Multiply cash by int2.
613 cash_mul_int2(PG_FUNCTION_ARGS)
615 Cash c = PG_GETARG_CASH(0);
616 int16 s = PG_GETARG_INT16(1);
620 PG_RETURN_CASH(result);
624 * Multiply int2 by cash.
627 int2_mul_cash(PG_FUNCTION_ARGS)
629 int16 s = PG_GETARG_INT16(0);
630 Cash c = PG_GETARG_CASH(1);
634 PG_RETURN_CASH(result);
638 * Divide cash by int2.
640 * XXX Don't know if rounding or truncating is correct behavior.
641 * Round for now. - tgl 97/04/15
644 cash_div_int2(PG_FUNCTION_ARGS)
646 Cash c = PG_GETARG_CASH(0);
647 int16 s = PG_GETARG_INT16(1);
652 (errcode(ERRCODE_DIVISION_BY_ZERO),
653 errmsg("division by zero")));
655 result = rint(c / s);
656 PG_RETURN_CASH(result);
660 * Return larger of two cash values.
663 cashlarger(PG_FUNCTION_ARGS)
665 Cash c1 = PG_GETARG_CASH(0);
666 Cash c2 = PG_GETARG_CASH(1);
669 result = (c1 > c2) ? c1 : c2;
671 PG_RETURN_CASH(result);
675 * Return smaller of two cash values.
678 cashsmaller(PG_FUNCTION_ARGS)
680 Cash c1 = PG_GETARG_CASH(0);
681 Cash c2 = PG_GETARG_CASH(1);
684 result = (c1 < c2) ? c1 : c2;
686 PG_RETURN_CASH(result);
691 * This converts a int4 as well but to a representation using words
692 * Obviously way North American centric - sorry
695 cash_words(PG_FUNCTION_ARGS)
697 Cash value = PG_GETARG_CASH(0);
707 /* work with positive numbers */
711 strcpy(buf, "minus ");
717 /* Now treat as unsigned, to avoid trouble at INT_MIN */
718 val = (unsigned int) value;
720 m0 = val % 100; /* cents */
721 m1 = (val / 100) % 1000; /* hundreds */
722 m2 = (val / 100000) % 1000; /* thousands */
723 m3 = val / 100000000 % 1000; /* millions */
727 strcat(buf, num_word(m3));
728 strcat(buf, " million ");
733 strcat(buf, num_word(m2));
734 strcat(buf, " thousand ");
738 strcat(buf, num_word(m1));
743 strcat(buf, (val / 100) == 1 ? " dollar and " : " dollars and ");
744 strcat(buf, num_word(m0));
745 strcat(buf, m0 == 1 ? " cent" : " cents");
747 /* capitalize output */
748 buf[0] = pg_toupper((unsigned char) buf[0]);
750 /* make a text type for output */
751 result = (text *) palloc(strlen(buf) + VARHDRSZ);
752 VARATT_SIZEP(result) = strlen(buf) + VARHDRSZ;
753 memcpy(VARDATA(result), buf, strlen(buf));
755 PG_RETURN_TEXT_P(result);
759 /*************************************************************************
761 ************************************************************************/
766 static char buf[128];
767 static const char *small[] = {
768 "zero", "one", "two", "three", "four", "five", "six", "seven",
769 "eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen",
770 "fifteen", "sixteen", "seventeen", "eighteen", "nineteen", "twenty",
771 "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety"
773 const char **big = small + 18;
774 int tu = value % 100;
776 /* deal with the simple cases first */
780 /* is it an even multiple of 100? */
783 sprintf(buf, "%s hundred", small[value / 100]);
790 /* is it an even multiple of 10 other than 10? */
791 if (value % 10 == 0 && tu > 10)
792 sprintf(buf, "%s hundred %s",
793 small[value / 100], big[tu / 10]);
795 sprintf(buf, "%s hundred and %s",
796 small[value / 100], small[tu]);
798 sprintf(buf, "%s hundred %s %s",
799 small[value / 100], big[tu / 10], small[tu % 10]);
804 /* is it an even multiple of 10 other than 10? */
805 if (value % 10 == 0 && tu > 10)
806 sprintf(buf, "%s", big[tu / 10]);
808 sprintf(buf, "%s", small[tu]);
810 sprintf(buf, "%s %s", big[tu / 10], small[tu % 10]);