public inbox for [email protected]
help / color / mirror / Atom feedFrom: Joel Jacobson <[email protected]>
To: pgsql-hackers <[email protected]>
Subject: [PATCH] Add fx exchange support to money type
Date: Wed, 01 Apr 2026 09:00:15 +0200
Message-ID: <[email protected]> (raw)
Hi hackers,
During Nordic PGDay 2026, a group of us discussed various open problems,
and the money type was a heavily debated topic. We all agreed that
something needs to be done, since it's quite a terrible type for various
reasons. One idea that was suggested was to move it to contrib/ and try
to deprecate it. Instead of doing that, I suggest that we try to fix it.
One of the most frequent complaints about the money type is the lack of
currency exchange rate support.
PostgreSQL recently added a build option for libcurl, so adding support
for this now seems doable, and by caching the exchange rate per session,
users will not have to pay the cost for an FX lookup more than once per
session, per currency pair. This can smoothly be extended to support a
shared FX cache, with persistent storage on disk, to ensure FX rates
survive reconnections and restarts.
The freely available FX API from https://api.frankfurter.app/ is
currently hard-coded as a proof of concept, but this should of course
be made configurable via a new GUC, which should be a list of API URLs
for redundancy.
Here is an example that converts from USD to SEK:
joel@Mac postgresql % psql
psql (19devel)
Type "help" for help.
joel=# \timing
Timing is on.
joel=# SELECT 100::money AS usd;
usd
---------
$100.00
(1 row)
Time: 1.429 ms
joel=# SET money_source_currency = 'USD';
SET
Time: 0.530 ms
joel=# SET lc_monetary = 'sv_SE.UTF-8';
SET
Time: 0.906 ms
joel=# SELECT 100::money AS usd;
usd
-----------
949,67 kr
(1 row)
Time: 38.121 ms
joel=# SELECT 100::money AS usd;
usd
-----------
949,67 kr
(1 row)
Time: 0.324 ms
joel=#
As shown above, the FX lookup takes about 38 ms the first time, after
which the session cache makes subsequent lookups cheap.
/Joel
Attachments:
[application/octet-stream] 0001-Add-fx-exchange-support-to-money-type.patch (15.3K, 2-0001-Add-fx-exchange-support-to-money-type.patch)
download | inline diff:
From d93000ccc0e070a7a02f65eead6b4e9b08caf81e Mon Sep 17 00:00:00 2001
From: Joel Jacobson <[email protected]>
Date: Mon, 30 Mar 2026 22:05:45 +0200
Subject: [PATCH] Add fx exchange support to money type
The money type has long been criticized for being too tightly bound
to a single locale's currency. This commit fixes that limitation
by teaching cash_out() to automatically convert stored values into
the client's local currency using live exchange rates from the
European Central Bank, fetched via libcurl.
A new GUC, money_source_currency, specifies the ISO 4217 currency
code of the stored data (e.g. 'USD'). When this differs from the
currency implied by the session's lc_monetary setting, cash_out()
multiplies the stored value by the current exchange rate before
formatting. Rates are cached per-session with a one-hour TTL to
keep the ECB's API traffic to a minimum.
If the exchange rate service is unreachable, or if money_source_currency
is empty (the default), values are displayed unmodified --- so this
is fully backward-compatible, modulo the need for an internet
connection and a sense of humor.
The conversion is display-only: stored int64 values are never
touched. This is important because otherwise we'd need to worry
about rounding-induced wealth creation, which while tempting is
frowned upon by the accounting profession.
Requires libcurl. On builds without it, the feature compiles out
and money behaves exactly as before, which is to say, badly.
---
meson.build | 5 +
src/backend/utils/adt/cash.c | 12 +
src/backend/utils/adt/cash_exchange.c | 325 ++++++++++++++++++++++
src/backend/utils/adt/meson.build | 1 +
src/backend/utils/misc/guc_parameters.dat | 8 +
src/backend/utils/misc/guc_tables.c | 1 +
src/include/utils/cash_exchange.h | 22 ++
src/include/utils/guc_hooks.h | 1 +
8 files changed, 375 insertions(+)
create mode 100644 src/backend/utils/adt/cash_exchange.c
create mode 100644 src/include/utils/cash_exchange.h
diff --git a/meson.build b/meson.build
index ea31cbce9c0..acecab1a6b6 100644
--- a/meson.build
+++ b/meson.build
@@ -1091,6 +1091,11 @@ else
libcurl = not_found_dep
endif
+# Make libcurl available to the backend for FX rate fetching
+if libcurl.found()
+ backend_both_deps += libcurl
+endif
+
###############################################################
# Library: libnuma
diff --git a/src/backend/utils/adt/cash.c b/src/backend/utils/adt/cash.c
index f0487a60f00..b8c1288382f 100644
--- a/src/backend/utils/adt/cash.c
+++ b/src/backend/utils/adt/cash.c
@@ -29,6 +29,7 @@
#include "utils/cash.h"
#include "utils/float.h"
#include "utils/numeric.h"
+#include "utils/cash_exchange.h"
#include "utils/pg_locale.h"
@@ -388,6 +389,7 @@ Datum
cash_out(PG_FUNCTION_ARGS)
{
Cash value = PG_GETARG_CASH(0);
+ float8 fx_rate;
uint64 uvalue;
char *result;
char buf[128];
@@ -404,6 +406,16 @@ cash_out(PG_FUNCTION_ARGS)
sep_by_space;
struct lconv *lconvert = PGLC_localeconv();
+ /* Apply exchange rate conversion if configured */
+ fx_rate = cash_exchange_rate();
+ if (fx_rate != 1.0)
+ {
+ float8 converted = rint((float8) value * fx_rate);
+
+ if (!isnan(converted) && FLOAT8_FITS_IN_INT64(converted))
+ value = (Cash) converted;
+ }
+
/* see comments about frac_digits in cash_in() */
points = lconvert->frac_digits;
if (points < 0 || points > 10)
diff --git a/src/backend/utils/adt/cash_exchange.c b/src/backend/utils/adt/cash_exchange.c
new file mode 100644
index 00000000000..6bdce4f8ee1
--- /dev/null
+++ b/src/backend/utils/adt/cash_exchange.c
@@ -0,0 +1,325 @@
+/*
+ * cash_exchange.c
+ * Automatic exchange rate conversion for the money type.
+ *
+ * When money_source_currency is set to an ISO 4217 code (e.g. "USD")
+ * and the session's lc_monetary locale implies a different currency,
+ * cash_exchange_rate() returns a conversion factor that cash_out()
+ * applies at display time. Rates are fetched from the European Central
+ * Bank via the Frankfurter API and cached per-session for one hour.
+ *
+ * src/backend/utils/adt/cash_exchange.c
+ */
+#include "postgres.h"
+
+#include <ctype.h>
+#include <math.h>
+
+#include "common/jsonapi.h"
+#include "mb/pg_wchar.h"
+#include "utils/cash_exchange.h"
+#include "utils/guc.h"
+#include "utils/pg_locale.h"
+#include "utils/timestamp.h"
+
+#ifdef USE_LIBCURL
+#include <curl/curl.h>
+#endif
+
+/* GUC variable */
+char *money_source_currency = "";
+
+/*
+ * Session-local exchange rate cache. We keep only one currency pair
+ * cached, which is fine because a session typically uses one lc_monetary
+ * setting throughout.
+ */
+typedef struct FXRateCache
+{
+ char from_currency[4]; /* ISO 4217 source */
+ char to_currency[4]; /* ISO 4217 target */
+ float8 rate;
+ TimestampTz fetch_time;
+ bool valid;
+} FXRateCache;
+
+static FXRateCache fx_cache = {.valid = false};
+
+/* Cache TTL: 1 hour */
+#define FX_CACHE_TTL_USEC (INT64CONST(3600) * USECS_PER_SEC)
+
+/* ----------------------------------------------------------------
+ * GUC check hook
+ * ----------------------------------------------------------------
+ */
+bool
+check_money_source_currency(char **newval, void **extra, GucSource source)
+{
+ const char *val = *newval;
+
+ /* Empty string disables conversion */
+ if (val[0] == '\0')
+ return true;
+
+ /* Must be exactly 3 ASCII alphabetic characters */
+ if (strlen(val) != 3 ||
+ !isalpha((unsigned char) val[0]) ||
+ !isalpha((unsigned char) val[1]) ||
+ !isalpha((unsigned char) val[2]))
+ {
+ GUC_check_errdetail("Value must be a 3-letter ISO 4217 currency code.");
+ return false;
+ }
+
+ return true;
+}
+
+#ifdef USE_LIBCURL
+
+/* ----------------------------------------------------------------
+ * libcurl response buffer
+ * ----------------------------------------------------------------
+ */
+typedef struct CurlBuffer
+{
+ char *data;
+ size_t len;
+ size_t capacity;
+} CurlBuffer;
+
+#define CURL_BUF_INIT_SIZE 1024
+#define CURL_BUF_MAX_SIZE (64 * 1024)
+
+static size_t
+curl_write_cb(char *ptr, size_t size, size_t nmemb, void *userdata)
+{
+ CurlBuffer *buf = (CurlBuffer *) userdata;
+ size_t bytes = size * nmemb;
+
+ if (buf->len + bytes > CURL_BUF_MAX_SIZE)
+ return 0; /* signal error to curl */
+
+ if (buf->len + bytes >= buf->capacity)
+ {
+ buf->capacity = Max(buf->capacity * 2, buf->len + bytes + 1);
+ buf->data = repalloc(buf->data, buf->capacity);
+ }
+
+ memcpy(buf->data + buf->len, ptr, bytes);
+ buf->len += bytes;
+ buf->data[buf->len] = '\0';
+ return bytes;
+}
+
+/* ----------------------------------------------------------------
+ * JSON response parser
+ *
+ * We parse the Frankfurter API response which looks like:
+ * {"amount":1.0,"base":"USD","date":"2026-03-28","rates":{"EUR":0.92}}
+ *
+ * We track the current field name and look for a scalar value whose
+ * field name matches the target currency code.
+ * ----------------------------------------------------------------
+ */
+typedef struct FXParseState
+{
+ char last_field[8];
+ float8 rate;
+ bool found_rate;
+ const char *target_currency;
+ int depth; /* nesting depth inside "rates" object */
+ bool in_rates;
+} FXParseState;
+
+static JsonParseErrorType
+fx_object_field_start(void *state, char *fname, bool isnull)
+{
+ FXParseState *s = (FXParseState *) state;
+
+ if (fname)
+ {
+ strlcpy(s->last_field, fname, sizeof(s->last_field));
+
+ if (pg_strcasecmp(fname, "rates") == 0)
+ s->in_rates = true;
+ }
+
+ return JSON_SUCCESS;
+}
+
+static JsonParseErrorType
+fx_scalar(void *state, char *token, JsonTokenType tokentype)
+{
+ FXParseState *s = (FXParseState *) state;
+
+ if (s->in_rates && tokentype == JSON_TOKEN_NUMBER &&
+ pg_strcasecmp(s->last_field, s->target_currency) == 0)
+ {
+ char *endptr;
+
+ s->rate = strtod(token, &endptr);
+ if (endptr != token && s->rate > 0)
+ s->found_rate = true;
+ }
+
+ return JSON_SUCCESS;
+}
+
+static JsonParseErrorType
+fx_object_end(void *state)
+{
+ FXParseState *s = (FXParseState *) state;
+
+ /* Reset in_rates when leaving the rates object */
+ s->in_rates = false;
+ return JSON_SUCCESS;
+}
+
+/*
+ * Fetch exchange rate from the Frankfurter API.
+ * Returns true on success, storing the rate in *rate_out.
+ */
+static bool
+fetch_exchange_rate(const char *from, const char *to, float8 *rate_out)
+{
+ CURL *curl;
+ CURLcode res;
+ CurlBuffer buf;
+ char url[256];
+ JsonLexContext lex;
+ JsonSemAction sem;
+ FXParseState parse_state;
+ JsonParseErrorType json_error;
+
+ snprintf(url, sizeof(url),
+ "https://api.frankfurter.app/latest?from=%.3s&to=%.3s",
+ from, to);
+
+ /* Initialize response buffer */
+ buf.data = palloc(CURL_BUF_INIT_SIZE);
+ buf.data[0] = '\0';
+ buf.len = 0;
+ buf.capacity = CURL_BUF_INIT_SIZE;
+
+ curl = curl_easy_init();
+ if (!curl)
+ {
+ pfree(buf.data);
+ return false;
+ }
+
+ curl_easy_setopt(curl, CURLOPT_URL, url);
+ curl_easy_setopt(curl, CURLOPT_WRITEFUNCTION, curl_write_cb);
+ curl_easy_setopt(curl, CURLOPT_WRITEDATA, &buf);
+ curl_easy_setopt(curl, CURLOPT_TIMEOUT, 5L);
+ curl_easy_setopt(curl, CURLOPT_NOSIGNAL, 1L);
+ curl_easy_setopt(curl, CURLOPT_USERAGENT, "PostgreSQL");
+
+ res = curl_easy_perform(curl);
+ curl_easy_cleanup(curl);
+
+ if (res != CURLE_OK)
+ {
+ pfree(buf.data);
+ return false;
+ }
+
+ /* Parse JSON response */
+ memset(&sem, 0, sizeof(sem));
+ memset(&parse_state, 0, sizeof(parse_state));
+
+ parse_state.target_currency = to;
+ sem.semstate = &parse_state;
+ sem.object_field_start = fx_object_field_start;
+ sem.scalar = fx_scalar;
+ sem.object_end = fx_object_end;
+
+ makeJsonLexContextCstringLen(&lex, buf.data, buf.len,
+ PG_UTF8, true);
+
+ json_error = pg_parse_json(&lex, &sem);
+ freeJsonLexContext(&lex);
+ pfree(buf.data);
+
+ if (json_error != JSON_SUCCESS || !parse_state.found_rate)
+ return false;
+
+ *rate_out = parse_state.rate;
+ return true;
+}
+
+#endif /* USE_LIBCURL */
+
+/* ----------------------------------------------------------------
+ * Public API
+ * ----------------------------------------------------------------
+ */
+
+/*
+ * cash_exchange_rate
+ *
+ * Returns the exchange rate to multiply a stored money value by for
+ * display in the client's currency. Returns 1.0 if no conversion
+ * is needed or possible.
+ */
+float8
+cash_exchange_rate(void)
+{
+ struct lconv *lconvert;
+ char target[4];
+
+ /* No source currency configured? No conversion. */
+ if (money_source_currency[0] == '\0')
+ return 1.0;
+
+ /* Extract target currency from session's lc_monetary */
+ lconvert = PGLC_localeconv();
+ if (strlen(lconvert->int_curr_symbol) < 3)
+ return 1.0; /* locale has no currency info (e.g. "C") */
+
+ memcpy(target, lconvert->int_curr_symbol, 3);
+ target[3] = '\0';
+
+ /* Same currency? No conversion needed. */
+ if (pg_strcasecmp(money_source_currency, target) == 0)
+ return 1.0;
+
+ /* Check the session-local cache */
+ if (fx_cache.valid &&
+ pg_strcasecmp(fx_cache.from_currency, money_source_currency) == 0 &&
+ pg_strcasecmp(fx_cache.to_currency, target) == 0 &&
+ (GetCurrentTimestamp() - fx_cache.fetch_time) < FX_CACHE_TTL_USEC)
+ {
+ return fx_cache.rate;
+ }
+
+#ifdef USE_LIBCURL
+ {
+ float8 rate;
+
+ if (fetch_exchange_rate(money_source_currency, target, &rate))
+ {
+ strlcpy(fx_cache.from_currency, money_source_currency, 4);
+ strlcpy(fx_cache.to_currency, target, 4);
+ fx_cache.rate = rate;
+ fx_cache.fetch_time = GetCurrentTimestamp();
+ fx_cache.valid = true;
+
+ ereport(DEBUG1,
+ (errmsg("fetched exchange rate %s/%s = %g",
+ money_source_currency, target, rate)));
+
+ return rate;
+ }
+
+ ereport(WARNING,
+ (errcode(ERRCODE_FDW_UNABLE_TO_ESTABLISH_CONNECTION),
+ errmsg("could not fetch exchange rate from %s to %s",
+ money_source_currency, target),
+ errhint("The exchange rate service may be unavailable. "
+ "Displaying unconverted value.")));
+ }
+#endif /* USE_LIBCURL */
+
+ return 1.0;
+}
diff --git a/src/backend/utils/adt/meson.build b/src/backend/utils/adt/meson.build
index fb8294d7e4a..67017e032b8 100644
--- a/src/backend/utils/adt/meson.build
+++ b/src/backend/utils/adt/meson.build
@@ -24,6 +24,7 @@ backend_sources += files(
'bool.c',
'bytea.c',
'cash.c',
+ 'cash_exchange.c',
'char.c',
'cryptohashfuncs.c',
'date.c',
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index 0a862693fcd..83af30f65f7 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -2210,6 +2210,14 @@
max => 'MAX_KILOBYTES',
},
+{ name => 'money_source_currency', type => 'string', context => 'PGC_USERSET', group => 'CLIENT_CONN_LOCALE',
+ short_desc => 'Sets the ISO 4217 currency code for stored money values.',
+ long_desc => 'When set and different from the session lc_monetary currency, money values are automatically converted using live exchange rates at display time. An empty string disables conversion.',
+ variable => 'money_source_currency',
+ boot_val => '""',
+ check_hook => 'check_money_source_currency',
+},
+
{ name => 'multixact_member_buffers', type => 'int', context => 'PGC_POSTMASTER', group => 'RESOURCES_MEM',
short_desc => 'Sets the size of the dedicated buffer pool used for the MultiXact member cache.',
flags => 'GUC_UNIT_BLOCKS',
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 1e14b7b4af0..d52bf593e61 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -93,6 +93,7 @@
#include "tsearch/ts_cache.h"
#include "utils/builtins.h"
#include "utils/bytea.h"
+#include "utils/cash_exchange.h"
#include "utils/float.h"
#include "utils/guc_hooks.h"
#include "utils/guc_tables.h"
diff --git a/src/include/utils/cash_exchange.h b/src/include/utils/cash_exchange.h
new file mode 100644
index 00000000000..3fcabc2e176
--- /dev/null
+++ b/src/include/utils/cash_exchange.h
@@ -0,0 +1,22 @@
+/*
+ * cash_exchange.h
+ * Automatic exchange rate conversion for the money type.
+ *
+ * When money_source_currency is set to an ISO 4217 currency code and
+ * the session's lc_monetary implies a different currency, money values
+ * are transparently converted at display time using live exchange rates.
+ *
+ * src/include/utils/cash_exchange.h
+ */
+#ifndef CASH_EXCHANGE_H
+#define CASH_EXCHANGE_H
+
+#include "postgres.h"
+
+/* GUC variable: ISO 4217 code of stored money currency (e.g. "USD") */
+extern PGDLLIMPORT char *money_source_currency;
+
+/* Returns the exchange rate to apply in cash_out(); 1.0 means no conversion */
+extern float8 cash_exchange_rate(void);
+
+#endif /* CASH_EXCHANGE_H */
diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h
index b01697c1f60..047abdda2dd 100644
--- a/src/include/utils/guc_hooks.h
+++ b/src/include/utils/guc_hooks.h
@@ -70,6 +70,7 @@ extern bool check_locale_messages(char **newval, void **extra, GucSource source)
extern void assign_locale_messages(const char *newval, void *extra);
extern bool check_locale_monetary(char **newval, void **extra, GucSource source);
extern void assign_locale_monetary(const char *newval, void *extra);
+extern bool check_money_source_currency(char **newval, void **extra, GucSource source);
extern bool check_locale_numeric(char **newval, void **extra, GucSource source);
extern void assign_locale_numeric(const char *newval, void *extra);
extern bool check_locale_time(char **newval, void **extra, GucSource source);
--
2.52.0
view thread (2+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH] Add fx exchange support to money type
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox