How to create a FUNCTION to format currency in POSTGRE?

Question

How to create a FUNCTION to format currency in POSTGRE?

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

I created one, but it did not look right there:

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

asked by anonymous 24.05.2016 / 02:54

2 answers

I need to get the Task Manager process description ADODataSet Delphi Edit

score 1 · Answer 1

1

You can do this:

Select to_char(123.45, 'L9G999G990D99');

L - Currency symbol

D - Decimal point

9 - Numbers

G - Drive tab

I hope I have helped.

24.05.2016 / 10:03

score 0 · Answer 2

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;

Then you use the parameter value like this:

SELECT formatar_moeda(123.45)

According to the Pedro Luzio explained in the other answer:

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

L - Currency symbol

D - Decimal point

9 - Numbers

G - Drive tab