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
* src/backend/utils/adt/cash.c
26
#include "libpq/pqformat.h"
27
#include "utils/builtins.h"
28
#include "utils/cash.h"
29
#include "utils/int8.h"
30
#include "utils/numeric.h"
31
#include "utils/pg_locale.h"
35
#define TERMINATOR (CASH_BUFSZ - 1)
36
#define LAST_PAREN (TERMINATOR - 1)
37
#define LAST_DIGIT (LAST_PAREN - 1)
40
/*************************************************************************
42
************************************************************************/
48
static const char *small[] = {
49
"zero", "one", "two", "three", "four", "five", "six", "seven",
50
"eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen",
51
"fifteen", "sixteen", "seventeen", "eighteen", "nineteen", "twenty",
52
"thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety"
54
const char **big = small + 18;
57
/* deal with the simple cases first */
61
/* is it an even multiple of 100? */
64
sprintf(buf, "%s hundred", small[value / 100]);
71
/* is it an even multiple of 10 other than 10? */
72
if (value % 10 == 0 && tu > 10)
73
sprintf(buf, "%s hundred %s",
74
small[value / 100], big[tu / 10]);
76
sprintf(buf, "%s hundred and %s",
77
small[value / 100], small[tu]);
79
sprintf(buf, "%s hundred %s %s",
80
small[value / 100], big[tu / 10], small[tu % 10]);
84
/* is it an even multiple of 10 other than 10? */
85
if (value % 10 == 0 && tu > 10)
86
sprintf(buf, "%s", big[tu / 10]);
88
sprintf(buf, "%s", small[tu]);
90
sprintf(buf, "%s %s", big[tu / 10], small[tu % 10]);
97
* Convert a string to a cash data type.
98
* Format is [$]###[,]###[.##]
99
* Examples: 123.45 $123.45 $123,456.78
103
cash_in(PG_FUNCTION_ARGS)
105
char *str = PG_GETARG_CSTRING(0);
118
struct lconv *lconvert = PGLC_localeconv();
121
* frac_digits will be CHAR_MAX in some locales, notably C. However, just
122
* testing for == CHAR_MAX is risky, because of compilers like gcc that
123
* "helpfully" let you alter the platform-standard definition of whether
124
* char is signed or not. If we are so unfortunate as to get compiled
125
* with a nonstandard -fsigned-char or -funsigned-char switch, then our
126
* idea of CHAR_MAX will not agree with libc's. The safest course is not
127
* to test for CHAR_MAX at all, but to impose a range check for plausible
128
* frac_digits values.
130
fpoint = lconvert->frac_digits;
131
if (fpoint < 0 || fpoint > 10)
132
fpoint = 2; /* best guess in this case, I think */
134
dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
135
if (*lconvert->mon_thousands_sep != '\0')
136
ssymbol = *lconvert->mon_thousands_sep;
138
/* ssymbol should not equal dsymbol */
139
ssymbol = (dsymbol != ',') ? ',' : '.';
140
csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
141
psymbol = ((*lconvert->positive_sign != '\0') ? *lconvert->positive_sign : '+');
142
nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
145
printf("cashin- precision '%d'; decimal '%c'; thousands '%c'; currency '%s'; positive '%c'; negative '%s'\n",
146
fpoint, dsymbol, ssymbol, csymbol, psymbol, nsymbol);
149
/* we need to add all sorts of checking here. For now just */
150
/* strip all leading whitespace and any leading currency symbol */
151
while (isspace((unsigned char) *s))
153
if (strncmp(s, csymbol, strlen(csymbol)) == 0)
154
s += strlen(csymbol);
157
printf("cashin- string is '%s'\n", s);
160
/* a leading minus or paren signifies a negative number */
161
/* again, better heuristics needed */
162
/* XXX - doesn't properly check for balanced parens - djmc */
163
if (strncmp(s, nsymbol, strlen(nsymbol)) == 0)
166
s += strlen(nsymbol);
168
printf("cashin- negative symbol; string is '%s'\n", s);
176
else if (*s == psymbol)
180
printf("cashin- string is '%s'\n", s);
183
while (isspace((unsigned char) *s))
185
if (strncmp(s, csymbol, strlen(csymbol)) == 0)
186
s += strlen(csymbol);
189
printf("cashin- string is '%s'\n", s);
194
/* we look for digits as long as we have found less */
195
/* than the required number of decimal places */
196
if (isdigit((unsigned char) *s) && (!seen_dot || dec < fpoint))
198
value = (value * 10) + (*s - '0');
203
/* decimal point? then start counting fractions... */
204
else if (*s == dsymbol && !seen_dot)
208
/* ignore if "thousands" separator, else we're done */
209
else if (*s != ssymbol)
212
if (isdigit((unsigned char) *s) && *s >= '5')
215
/* adjust for less than required decimal places */
216
for (; dec < fpoint; dec++)
223
/* should only be trailing digits followed by whitespace or right paren */
224
while (isdigit((unsigned char) *s))
226
while (isspace((unsigned char) *s) || *s == ')')
231
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
232
errmsg("invalid input syntax for type money: \"%s\"", str)));
234
result = value * sgn;
237
printf("cashin- result is " INT64_FORMAT "\n", result);
240
PG_RETURN_CASH(result);
245
* Function to convert cash to a dollars and cents representation.
246
* XXX HACK This code appears to assume US conventions for
247
* positive-valued amounts. - tgl 97/04/14
250
cash_out(PG_FUNCTION_ARGS)
252
Cash value = PG_GETARG_CASH(0);
254
char buf[CASH_BUFSZ];
256
int count = LAST_DIGIT;
258
int ssymbol_position = 0;
266
struct lconv *lconvert = PGLC_localeconv();
268
/* see comments about frac_digits in cash_in() */
269
points = lconvert->frac_digits;
270
if (points < 0 || points > 10)
271
points = 2; /* best guess in this case, I think */
274
* As with frac_digits, must apply a range check to mon_grouping to avoid
275
* being fooled by variant CHAR_MAX values.
277
mon_group = *lconvert->mon_grouping;
278
if (mon_group <= 0 || mon_group > 6)
281
convention = lconvert->n_sign_posn;
282
dsymbol = ((*lconvert->mon_decimal_point != '\0') ? *lconvert->mon_decimal_point : '.');
283
if (*lconvert->mon_thousands_sep != '\0')
284
ssymbol = *lconvert->mon_thousands_sep;
286
/* ssymbol should not equal dsymbol */
287
ssymbol = (dsymbol != ',') ? ',' : '.';
288
csymbol = ((*lconvert->currency_symbol != '\0') ? lconvert->currency_symbol : "$");
289
nsymbol = ((*lconvert->negative_sign != '\0') ? lconvert->negative_sign : "-");
291
point_pos = LAST_DIGIT - points;
293
point_pos -= (points - 1) / mon_group;
294
ssymbol_position = point_pos % (mon_group + 1);
296
/* we work with positive amounts and add the minus sign at the end */
303
/* allow for trailing negative strings */
304
MemSet(buf, ' ', CASH_BUFSZ);
305
buf[TERMINATOR] = buf[LAST_PAREN] = '\0';
307
while (value || count > (point_pos - 2))
309
if (points && count == point_pos)
310
buf[count--] = dsymbol;
311
else if (ssymbol && count % (mon_group + 1) == ssymbol_position)
312
buf[count--] = ssymbol;
314
buf[count--] = ((uint64) value % 10) + '0';
315
value = ((uint64) value) / 10;
318
strncpy((buf + count - strlen(csymbol) + 1), csymbol, strlen(csymbol));
319
count -= strlen(csymbol) - 1;
322
* If points == 0 and the number of digits % mon_group == 0, the code
323
* above adds a trailing ssymbol on the far right, so remove it.
325
if (buf[LAST_DIGIT] == ssymbol)
326
buf[LAST_DIGIT] = '\0';
328
/* see if we need to signify negative amount */
331
result = palloc(CASH_BUFSZ + 2 - count + strlen(nsymbol));
333
/* Position code of 0 means use parens */
335
sprintf(result, "(%s)", buf + count);
336
else if (convention == 2)
337
sprintf(result, "%s%s", buf + count, nsymbol);
339
sprintf(result, "%s%s", nsymbol, buf + count);
343
result = palloc(CASH_BUFSZ + 2 - count);
344
strcpy(result, buf + count);
347
PG_RETURN_CSTRING(result);
351
* cash_recv - converts external binary format to cash
354
cash_recv(PG_FUNCTION_ARGS)
356
StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
358
PG_RETURN_CASH((Cash) pq_getmsgint64(buf));
362
* cash_send - converts cash to binary format
365
cash_send(PG_FUNCTION_ARGS)
367
Cash arg1 = PG_GETARG_CASH(0);
370
pq_begintypsend(&buf);
371
pq_sendint64(&buf, arg1);
372
PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
376
* Comparison functions
380
cash_eq(PG_FUNCTION_ARGS)
382
Cash c1 = PG_GETARG_CASH(0);
383
Cash c2 = PG_GETARG_CASH(1);
385
PG_RETURN_BOOL(c1 == c2);
389
cash_ne(PG_FUNCTION_ARGS)
391
Cash c1 = PG_GETARG_CASH(0);
392
Cash c2 = PG_GETARG_CASH(1);
394
PG_RETURN_BOOL(c1 != c2);
398
cash_lt(PG_FUNCTION_ARGS)
400
Cash c1 = PG_GETARG_CASH(0);
401
Cash c2 = PG_GETARG_CASH(1);
403
PG_RETURN_BOOL(c1 < c2);
407
cash_le(PG_FUNCTION_ARGS)
409
Cash c1 = PG_GETARG_CASH(0);
410
Cash c2 = PG_GETARG_CASH(1);
412
PG_RETURN_BOOL(c1 <= c2);
416
cash_gt(PG_FUNCTION_ARGS)
418
Cash c1 = PG_GETARG_CASH(0);
419
Cash c2 = PG_GETARG_CASH(1);
421
PG_RETURN_BOOL(c1 > c2);
425
cash_ge(PG_FUNCTION_ARGS)
427
Cash c1 = PG_GETARG_CASH(0);
428
Cash c2 = PG_GETARG_CASH(1);
430
PG_RETURN_BOOL(c1 >= c2);
434
cash_cmp(PG_FUNCTION_ARGS)
436
Cash c1 = PG_GETARG_CASH(0);
437
Cash c2 = PG_GETARG_CASH(1);
449
* Add two cash values.
452
cash_pl(PG_FUNCTION_ARGS)
454
Cash c1 = PG_GETARG_CASH(0);
455
Cash c2 = PG_GETARG_CASH(1);
460
PG_RETURN_CASH(result);
465
* Subtract two cash values.
468
cash_mi(PG_FUNCTION_ARGS)
470
Cash c1 = PG_GETARG_CASH(0);
471
Cash c2 = PG_GETARG_CASH(1);
476
PG_RETURN_CASH(result);
481
* Divide cash by cash, returning float8.
484
cash_div_cash(PG_FUNCTION_ARGS)
486
Cash dividend = PG_GETARG_CASH(0);
487
Cash divisor = PG_GETARG_CASH(1);
492
(errcode(ERRCODE_DIVISION_BY_ZERO),
493
errmsg("division by zero")));
495
quotient = (float8) dividend / (float8) divisor;
496
PG_RETURN_FLOAT8(quotient);
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);
800
/* work with positive numbers */
804
strcpy(buf, "minus ");
810
/* Now treat as unsigned, to avoid trouble at INT_MIN */
811
val = (uint64) value;
813
m0 = val % INT64CONST(100); /* cents */
814
m1 = (val / INT64CONST(100)) % 1000; /* hundreds */
815
m2 = (val / INT64CONST(100000)) % 1000; /* thousands */
816
m3 = (val / INT64CONST(100000000)) % 1000; /* millions */
817
m4 = (val / INT64CONST(100000000000)) % 1000; /* billions */
818
m5 = (val / INT64CONST(100000000000000)) % 1000; /* trillions */
819
m6 = (val / INT64CONST(100000000000000000)) % 1000; /* quadrillions */
823
strcat(buf, num_word(m6));
824
strcat(buf, " quadrillion ");
829
strcat(buf, num_word(m5));
830
strcat(buf, " trillion ");
835
strcat(buf, num_word(m4));
836
strcat(buf, " billion ");
841
strcat(buf, num_word(m3));
842
strcat(buf, " million ");
847
strcat(buf, num_word(m2));
848
strcat(buf, " thousand ");
852
strcat(buf, num_word(m1));
857
strcat(buf, (val / 100) == 1 ? " dollar and " : " dollars and ");
858
strcat(buf, num_word(m0));
859
strcat(buf, m0 == 1 ? " cent" : " cents");
861
/* capitalize output */
862
buf[0] = pg_toupper((unsigned char) buf[0]);
864
/* return as text datum */
865
PG_RETURN_TEXT_P(cstring_to_text(buf));
870
* Convert cash to numeric.
873
cash_numeric(PG_FUNCTION_ARGS)
875
Cash money = PG_GETARG_CASH(0);
883
struct lconv *lconvert = PGLC_localeconv();
885
/* see comments about frac_digits in cash_in() */
886
fpoint = lconvert->frac_digits;
887
if (fpoint < 0 || fpoint > 10)
890
/* compute required scale factor */
892
for (i = 0; i < fpoint; i++)
895
/* form the result as money / scale */
896
amount = DirectFunctionCall1(int8_numeric, Int64GetDatum(money));
897
numeric_scale = DirectFunctionCall1(int8_numeric, Int64GetDatum(scale));
898
quotient = DirectFunctionCall2(numeric_div, amount, numeric_scale);
900
/* forcibly round to exactly the intended number of digits */
901
result = DatumGetNumeric(DirectFunctionCall2(numeric_round,
903
Int32GetDatum(fpoint)));
905
PG_RETURN_NUMERIC(result);
909
* Convert numeric to cash.
912
numeric_cash(PG_FUNCTION_ARGS)
914
Datum amount = PG_GETARG_DATUM(0);
920
struct lconv *lconvert = PGLC_localeconv();
922
/* see comments about frac_digits in cash_in() */
923
fpoint = lconvert->frac_digits;
924
if (fpoint < 0 || fpoint > 10)
927
/* compute required scale factor */
929
for (i = 0; i < fpoint; i++)
932
/* multiply the input amount by scale factor */
933
numeric_scale = DirectFunctionCall1(int8_numeric, Int64GetDatum(scale));
934
amount = DirectFunctionCall2(numeric_mul, amount, numeric_scale);
936
/* note that numeric_int8 will round to nearest integer for us */
937
result = DatumGetInt64(DirectFunctionCall1(numeric_int8, amount));
939
PG_RETURN_CASH(result);
943
* Convert int4 (int) to cash
946
int4_cash(PG_FUNCTION_ARGS)
948
int32 amount = PG_GETARG_INT32(0);
953
struct lconv *lconvert = PGLC_localeconv();
955
/* see comments about frac_digits in cash_in() */
956
fpoint = lconvert->frac_digits;
957
if (fpoint < 0 || fpoint > 10)
960
/* compute required scale factor */
962
for (i = 0; i < fpoint; i++)
965
/* compute amount * scale, checking for overflow */
966
result = DatumGetInt64(DirectFunctionCall2(int8mul, Int64GetDatum(amount),
967
Int64GetDatum(scale)));
969
PG_RETURN_CASH(result);
973
* Convert int8 (bigint) to cash
976
int8_cash(PG_FUNCTION_ARGS)
978
int64 amount = PG_GETARG_INT64(0);
983
struct lconv *lconvert = PGLC_localeconv();
985
/* see comments about frac_digits in cash_in() */
986
fpoint = lconvert->frac_digits;
987
if (fpoint < 0 || fpoint > 10)
990
/* compute required scale factor */
992
for (i = 0; i < fpoint; i++)
995
/* compute amount * scale, checking for overflow */
996
result = DatumGetInt64(DirectFunctionCall2(int8mul, Int64GetDatum(amount),
997
Int64GetDatum(scale)));
999
PG_RETURN_CASH(result);