Re: to_char with locale decimal separator

On Tue, Jul 30, 2013 at 4:42 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
> On 07/30/2013 03:03 AM, Ingmar Brouns wrote:
>>
>> On Mon, Jul 29, 2013 at 3:12 PM, Ingmar Brouns <swingi(at)gmail(dot)com> wrote:
>
>
>>>
>>
>> anyone? Giving a locale corresponding textual representation
>> of a numerical value keeping the exact nr of decimal digits
>> must be a fairly common use case. Would it be an idea to
>> implement a to_char function that does not take a formatting
>> pattern and has this behaviour?
>>
>
> Best I can do is a proof of concept in plpythonu for determining locale
> decimal point:
>
> test=# SHOW lc_numeric ;
> lc_numeric
> -------------
> en_US.UTF-8
> (1 row)
>
> test=# DO $$
> import locale
> rs = plpy.execute("SHOW lc_numeric")
> lc_n = rs[0]["lc_numeric"]
> locale.setlocale(locale.LC_NUMERIC, lc_n)
> d = locale.nl_langinfo(locale.RADIXCHAR)
> plpy.notice("Decimal point is " + d)
> $$ LANGUAGE plpythonu;
> NOTICE: Decimal point is .
> CONTEXT: PL/Python anonymous code block
> DO
>
>
> test=# set lc_numeric = 'nl_NL.utf8';
> SET
> test=# DO $$
> import locale
> rs = plpy.execute("SHOW lc_numeric")
> lc_n = rs[0]["lc_numeric"]
> locale.setlocale(locale.LC_NUMERIC, lc_n)
> d = locale.nl_langinfo(locale.RADIXCHAR)
> plpy.notice("Decimal point is " + d)
> $$ LANGUAGE plpythonu;
> NOTICE: Decimal point is ,
> CONTEXT: PL/Python anonymous code block
> DO
>

Thanks for your time, appreciate it! As a dirty alternative, you could also do
something like:

select translate(5.000::text,'.',substr(to_char(.0),2,1));

Not so nice, but would work. Though I still feel there should be a more
elegant of doing this...

>
>
>>>
>>>>
>>>> I would like to have '1,500' as the output, what is the best way
>>>> to achieve this?
>>>>
>>>> Thanks in advance,
>>>>
>>>> Ingmar
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com