Postgres: Convert numeric currency value to German bookkeeping standard
Postgres is nice and flexible in many ways, but when it comes to converting numbers to a format other than the US-American one, it can be a bit stubborn. For a direct SQL CSV export, I need the numbers in the format 999.999.999,99 instead of the standard 999,999,999.99. I tried to cast the numeric to monetary, which got ignored, even after setting the locale SET LC_MONETARY='de_DE.utf8';
. I assume the docker container I'm using for Postgres does not have the German locale installed and I do not want to create such a configuration dependency on the target system of the customer, so that's not a way to go. The generic way to deal with this is the to_char() function, but that comes with decimal and thousands separators which are again hard-wired with the locale. I couldn't find a way to change the separators as it would be possible in Oracle. I ended up with the following ugly workaround:
CREATE OR REPLACE FUNCTION format_bcs_money(input numeric)
RETURNS char AS $$
SELECT REPLACE(REPLACE(REPLACE(to_char(ROUND(input/100, 2), 'FM999G999G990D00'), '.', 'P'), ',', '.'), 'P', ',');
$$
LANGUAGE SQL;