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.64 2004-08-29 05:06:49 momjian 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]);