How to create a FUNCTION to format currency in POSTGRE?

Asked

Viewed 1,598 times

2

I created one, but it didn’t work out:

CREATE FUNCTION formatar_moeda(valor FLOAT) RETURNS VARCHAR(15) AS
$$
   DECLARE
   formatado VARCHAR(15);
BEGIN
  SET formatado = NUMBER(valor,2);
  SET formatado = REPLACE(formatado,'.','#');
  SET formatado = REPLACE(formatado,',','.');
  SET formatado = REPLACE(formatado,'#',',');
  RETURN CONCAT('R$', formatado);
END;
$$
LANGUAGE plpgsql;
postgresql

  • Take a look here: http://paposql.blogspot.com.br/2012/02/funcao-para-formatar-moeda-em-reais-no.html

3 answers

1

You can do it like this:

Select to_char(123.45, 'L9G999G990D99');

L - Currency symbol

D - Decimal point

9 - Numbers

G - Unit separator

I hope I’ve helped.

  • Thanks for the help... I’ll try.

0

if you want to keep the original idea, without changing the definition LC_CURRENCY to pt_BR you can use the code below

CREATE OR REPLACE FUNCTION formatar_moeda(valor REAL) RETURNS VARCHAR(100) AS
$$
   DECLARE
   formatado VARCHAR(100);
BEGIN
  SELECT  to_char(valor::float, '999G999G999D99') INTO formatado;
  SELECT REPLACE(formatado,'.','#') INTO formatado;
  SELECT REPLACE(formatado,',','.') INTO formatado;
  SELECT REPLACE(formatado,'#',',') INTO formatado;
  RETURN CONCAT('R$', formatado);
END;
$$
LANGUAGE plpgsql;

0

I believe this is what you wanted:

CREATE OR REPLACE FUNCTION formatar_moeda(valor FLOAT) 
  RETURNS VARCHAR(15) AS
$body$
DECLARE
--------parametros--------
    pValor        ALIAS FOR $1;
--------variaveis---------
    vValor        VARCHAR;
BEGIN
    SELECT to_char(pValor, 'L9G999G990D99') INTO vValor;
RETURN vValor;
END;
$body$
LANGUAGE plpgsql;

Hence you use passing the value by parameter like this:

SELECT formatar_moeda(123.45)

According to the Pedro Luzio explained in the other reply:

SELECT to_char(pValor, 'L9G999G990D99') INTO vValor;

L - Currency symbol

D - Decimal point

9 - Numbers

G - Unit separator

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.