Convert from cents to “price” and back
I do not want to use floats in the Go lang application, because it is
not supposed to do anything with these values other than to print and
retrieve them from the user; all computations will be performed by
PostgreSQL in cents.
That means i have to “convert” from the price format that users expect
to see (e.g., 1.234,56) to cents (e.g., 123456) and back when passing
data between Go and PostgreSQL, and that conversion depends on the
currency’s decimal places.
At first i did everything in Go, but saw that i would need to do it in
a loop when retrieving the list of products, and immediately knew it was
a mistake—i needed a PL/pgSQL function for that.
I still need to convert from string to float, however, when printing the
value to the user. Because the string representation is in C, but i
need to format it according to the locale with golang/x/text. That
package has the information of how to correctly format numbers, but it
is in an internal package that i can not use, and numbers.Digit only
accepts numeric types, not a string.
2023-02-05 12:55:12 +00:00
|
|
|
-- Deploy numerus:to_price to pg
|
|
|
|
|
-- requires: schema_numerus
|
|
|
|
|
|
|
|
|
|
begin;
|
|
|
|
|
|
|
|
|
|
set search_path to numerus, public;
|
|
|
|
|
|
|
|
|
|
create or replace function to_price(cents integer, decimal_digits integer) returns text as
|
|
|
|
|
$$
|
|
|
|
|
declare
|
|
|
|
|
result text;
|
|
|
|
|
scale integer := 10^decimal_digits;
|
2023-02-23 13:42:18 +00:00
|
|
|
sign text := '';
|
Convert from cents to “price” and back
I do not want to use floats in the Go lang application, because it is
not supposed to do anything with these values other than to print and
retrieve them from the user; all computations will be performed by
PostgreSQL in cents.
That means i have to “convert” from the price format that users expect
to see (e.g., 1.234,56) to cents (e.g., 123456) and back when passing
data between Go and PostgreSQL, and that conversion depends on the
currency’s decimal places.
At first i did everything in Go, but saw that i would need to do it in
a loop when retrieving the list of products, and immediately knew it was
a mistake—i needed a PL/pgSQL function for that.
I still need to convert from string to float, however, when printing the
value to the user. Because the string representation is in C, but i
need to format it according to the locale with golang/x/text. That
package has the information of how to correctly format numbers, but it
is in an internal package that i can not use, and numbers.Digit only
accepts numeric types, not a string.
2023-02-05 12:55:12 +00:00
|
|
|
begin
|
2023-02-23 13:42:18 +00:00
|
|
|
if cents < 0 then
|
|
|
|
|
sign := '-';
|
|
|
|
|
cents = -cents;
|
|
|
|
|
end if;
|
Convert from cents to “price” and back
I do not want to use floats in the Go lang application, because it is
not supposed to do anything with these values other than to print and
retrieve them from the user; all computations will be performed by
PostgreSQL in cents.
That means i have to “convert” from the price format that users expect
to see (e.g., 1.234,56) to cents (e.g., 123456) and back when passing
data between Go and PostgreSQL, and that conversion depends on the
currency’s decimal places.
At first i did everything in Go, but saw that i would need to do it in
a loop when retrieving the list of products, and immediately knew it was
a mistake—i needed a PL/pgSQL function for that.
I still need to convert from string to float, however, when printing the
value to the user. Because the string representation is in C, but i
need to format it according to the locale with golang/x/text. That
package has the information of how to correctly format numbers, but it
is in an internal package that i can not use, and numbers.Digit only
accepts numeric types, not a string.
2023-02-05 12:55:12 +00:00
|
|
|
result = cents::text;
|
2023-02-23 13:42:18 +00:00
|
|
|
return sign || (cents / scale)::text || '.' || to_char(mod(cents, scale), rpad('FM', decimal_digits + 2, '0'));
|
Convert from cents to “price” and back
I do not want to use floats in the Go lang application, because it is
not supposed to do anything with these values other than to print and
retrieve them from the user; all computations will be performed by
PostgreSQL in cents.
That means i have to “convert” from the price format that users expect
to see (e.g., 1.234,56) to cents (e.g., 123456) and back when passing
data between Go and PostgreSQL, and that conversion depends on the
currency’s decimal places.
At first i did everything in Go, but saw that i would need to do it in
a loop when retrieving the list of products, and immediately knew it was
a mistake—i needed a PL/pgSQL function for that.
I still need to convert from string to float, however, when printing the
value to the user. Because the string representation is in C, but i
need to format it according to the locale with golang/x/text. That
package has the information of how to correctly format numbers, but it
is in an internal package that i can not use, and numbers.Digit only
accepts numeric types, not a string.
2023-02-05 12:55:12 +00:00
|
|
|
end;
|
|
|
|
|
$$
|
|
|
|
|
language plpgsql
|
|
|
|
|
immutable;
|
|
|
|
|
|
|
|
|
|
comment on function to_price(integer, integer) is
|
|
|
|
|
'Converts the cents to a price representation, without currency and any other separater than decimal.';
|
|
|
|
|
|
|
|
|
|
revoke execute on function to_price(integer, integer) from public;
|
|
|
|
|
grant execute on function to_price(integer, integer) to invoicer;
|
|
|
|
|
grant execute on function to_price(integer, integer) to admin;
|
|
|
|
|
|
|
|
|
|
commit;
|